BUSBIS 1605 Sec 1210 – Database Management

Spring 2016

Instructor
Dave Eargle (contact)
Class
Tues/Thurs 12:30PM - 1:45PM
209 Mervis Hall
Office Hours
By appointment (Skype or Hangouts preferred)
Exam
TBA
Google Group
Subscribe
Visit the group
Use the group for asking questions to all class members – for instance, tech troubleshooting help questions.

Course Information

Course Description

This course provides a basic understanding of information management in organizations and specifically the design and use of database systems. Topics include data modeling, relational databases, and structured query language. The course is divided into four sections: (1) information management concepts and procedures for Web-based systems; (2) data modeling and database design; (3) data analytics; (4) data management.

The course syllabus is a general plan for the course; deviations announced to the class by the instructor may be necessary.

Objectives

Students completing this course will

  1. understand the organizational issues involved in data management
  2. be able to develop a valid data model for a business system of medium complexity
  3. be able to build and use a relational database
  4. be able to formulate complex relational database queries
  5. be able to use R for data analytics
  6. be aware of the issues related to managing “big data”
  7. be familiar with the principles of managing and exploiting organizational data

Topics

  1. The organizational perspective on data management
  2. Data modeling and SQL
  3. Relational DBMS
  4. Organizational intelligence technologies
  5. Introduction to R
  6. Data visualization
  7. HDFS and MapReduce
  8. Data structure and storage
  9. Data processing architectures
  10. Data integrity and data administration

Text

Watson, R. T. (2013). Data management: databases and organizations (6th ed.). Athens, GA: eGreen Press. I recommend the Kindle version. Textbook website.

Software

We will use MySQL Workbench for data modeling and SQL execution (available on Windows, Linux, and Mac OS X). Macintosh owners can also use Sequel Pro for SQL execution.

MySQL database

  1. Laptop for individual assignments and exams:
    Download and install MySQL Community Server (or, if you’re interested in Web Dev, WAMP or MAMP)
  2. Server for SQL exercises:
    Hostname: mysql.daveeargle.com. Username and password on CourseWeb. Default schema: Text.
  3. Server for group work
    Hostname: mysql.daveeargle.com. You will get a group id and password.

R

  1. Server connection parameters TBA

Policies

Group size

Groups should contain between three and four persons.

Communication

I will send messages via CourseWeb. Feel free to contact me outside of CourseWeb.

Academic honesty

Students in this course will be expected to comply with the University of Pittsburgh’s Policy on Academic Integrity. Any student suspected of violating this obligation for any reason during the semester will be required to participate in the procedural process, initiated at the instructor level, as outlined in the University Guidelines on Academic Integrity. This may include, but is not limited to, the confiscation of the examination of any individual suspected of violating University Policy. Furthermore, no student may bring any unauthorized materials to an exam, including dictionaries and programmable calculators.

Team work

In this class, you will work in teams. As a result, review a short report on team effectiveness and establish a team agreement (sample agreement). Give the instructor a copy of your team agreement by the end of the second week of classes.

Freeloader policy

It occasionally happens in class and enterprise settings that someone in a group is not prepared to do his/her share. In the case of my classes, I recommend that the team give the freeloader one warning and then fire that person from the team. That person will then do group assignments individually or find another team to join. The team should notify me of the change in team composition immediately. I distribute a form to assess team participation at the end of the semester. If a major disparity in team contribution is reported, I will adjust team project grades.

Laptop policy

Students are welcome to use laptops in class for note taking and completing class exercises, exclusively. If you plan to take notes, please advise and email a copy of the notes at the end of each class.

Attendance

Attendance and participation are required for this course. Excessive unexcused absences (i.e., greater than 4) will result in a failing grade according to University of Pittsburgh policies

Assignments

See the class schedule for the due date. The due time is 11:59pm on the due date.

Exercises (on your own)

Identifier Chapter Exercise
A1 Single Entity Data Modeling

A tennis store wants to keep track of its inventory. Each type of racquet has a manufacturer, model name, composition, color, price, quantity on hand, grip type, string tension, and a racquet code (e.g., N123D). Racquets are classified as junior, amateur or professional. Create the data model with MySQL workbench.

SQL
  • List each product's name and its MSRP increased by 10%.
  • List the product name and code of products with a code starting with S18.
A2 One-to-Many relationship Data modeling

Create a data model to record details of a firm's managers and the projects they supervise. A typical manager is assigned to supervise several projects. However, a project is assigned to only one manager. For each manager, the firm wants to know the person's name and email address. A project's name, final cost, and expected cost are required. You can assume a unique name for each project but not for each manager.

SQL
  • Report the number of employees in each country.
  • What is the total payment received from customers in Australia?
A3 Many-to-Many relationship Data modeling

A football fan has asked you to create a data model to record details of injuries. You need to capture details of teams, players, games, and injuries.

SQL
  • Report the value of each cancelled order for each customer. Sort by customer name and order number.
  • Report the total value ordered for each product line in January 2004 sorted by product line. : See MySQL date functions.
A4 One-to-One and Recursive Relationships Data modeling

At a local sport facility, a club can have many players, but a player can play only for one club. Each club has, at most, one player known as a "rising-star." For every club, record the name of the club, its address, and phone number. A player can be at one of three levels: beginner (B), intermediate (I), or advanced (A). Furthermore, each player can have, at the most, one other player as a mentor.

SQL
  • Which sales reps report to Bow?
  • Who is the boss of an employee called Larry or Barry? Report the full name of the boss and the employee and the employee's title.
A5 SQL Write a procedure to report the total amount paid in a specified month and year. Format with zero decimal places.
A6 Introduction to R A file contains details of CO2 emissions per capita for the four largest economies in North America. Write an R script to read the file, convert it into a format suitable for use with R and report the average CO2 per capita for each country in descending order.
A7 Data visualization Graph CO2 emissions per capita by year for each of the four countries in the prior exercise. Show separate lines for each country. Hint: For a tidier x-axis, see the "Tick sizes and padding" section of the ggvis axes documentation).
A8 HDFS and MapReduce Using Delta’s performance data for February 2013 do the following:
  1. Using regular R commands, compute the minimum, average, and maximum departure delay in minutes (DepDelayMinutes) for each origin airport. Use head() to show the first six rows.
  2. Use MapReduce to undertake the same computations. Use head() to show the first six rows.

State of the art presentations (group) (there will be two per class).

A presentation is required from each group on a data management technology, with a particular concentration on open source products.

Some suggested topics are listed below (assigned topics have a date of presentation), and you can propose other topics. You should submit your bid for a topic via e-mail. When submitting a bid, also specify your team’s name. Those who bid early present early.

Topic Group Date
Drones Fly Boyz Class 6
1/24
3D printing Team Avocado Class 7
1/26
Bitcoin Antoons Class 8
1/31
Autonomous vehicles Über Ëverywhere Class 9
2/2
Virtual reality Nebachenezzer Class 10
2/7
Internet of things Group 1 Class 11
2/9
Augmented reality Purple People Eaters Class 12
2/14
Proximity marketing (iBeacon and Eddystone) The Lonely Two Class 13
2/16
NFC (payments, etc)
IBM's Watson
Computer-brain interfaces
Smart cities

Database design and implementation project #1 (group)

Cell phones have replaced landlines in many situations, except hotels, yet the guests of most hotels have a cell phone. Design the database for an app to replace a room’s landline and the various paper documentation you find in a hotel room. Convert the data model to a relational database and write 10 queries to access the database. The queries should demonstrate your breadth of understanding of SQL (i.e., 10 simple queries will not score as well as say 4 simple queries and 6 nontrivial queries).

Follow the guidelines for database project submissions.

Data modeling is difficult and to facilitate learning an initial model must be submitted previous to the due date (see the schedule).

Database design and implementation project #2 (group)

Develop a data model for an application of your choice. The problem must be at least of the same order of magnitude as for project #1. Please discuss your proposed project with the instructor before making substantial progress. You must file a brief written outline of the project’s scope. You might find an examplar project and associated data useful for this assignment.

An initial model must be submitted previous to the due date (see the schedule).

Follow the guidelines for database project submissions .

Grading

Item Points
Assignments (A1–A8) 20
State of the art report 10
Database design and implementation project #1 15
Database design and implementation project #2 15
Mid semester exam (sample from UGA) 15
Final exam (sample from UGA) 25
Total 100
If you are unable to complete an exercise on time or take an exam at the specified time, please advise the instructor as soon as possible so that alternative arrangements can be made.

Team evaluation

This form should be submitted before the final exam.

Quick Links

Schedule

Class Day Date Topic (chapter) Activities
Before Class In Class Due by 11:59pm
1 Thursday 1/5 Syllabus, introduction In-class survey  
2 Tuesday 1/10 Data (1) Acquire the Textbook In-class survey  
3 Thursday 1/12 Information (2)    
4 Tuesday 1/17 Single Entity (3)  
5 Thursday 1/19 Single Entity (3)   In-class survey Team agreement
6 Tuesday 1/24 One-to-Many relationship (4)   In-class survey A1
7 Thursday 1/26 One-to-Many relationship (4)   In-class survey  
8 Tuesday 1/31 Many-to-Many relationship (5)   In-class survey A2
9 Thursday 2/2 Many-to-Many relationship (5)   In-class survey  
10 Tuesday 2/7 Recursive Relationships (6)   In-class survey A3
11 Thursday 2/9 Recursive Relationships (6)   In-class survey Initial model of Project #1
12 Tuesday 2/14 Data modeling (7) In-class survey   A4
13 Thursday 2/16 Data modeling (7)   In-class survey  
14 Tuesday 2/21 SQL (10)   In-class survey Project #1
15 Thursday 2/23 SQL (10)   In-class survey  
16 Tuesday 2/28 Mid-term exam   A5
17 Thursday 3/2 Mid-term exam      
18 Tuesday 3/7 Spring break      
19 Thursday 3/9 Spring break      
20 Tuesday 3/14 Spatial & Temporal data (11)   In-class survey  
21 Thursday 3/16 Organizational Intelligence (13) & Pivot Tables   In-class survey  
22 Tuesday 3/21 Introduction to R (14)   In-class survey  
23 Thursday 3/23 Introduction to R (14)    
24 Tuesday 3/28 Data visualization (15)   In-class survey A6
25 Thursday 3/30 Data visualization (15)   In-class survey  
26 Tuesday 4/4 HDFS and MapReduce (17)   A7
27 Thursday 4/6 HDFS and MapReduce (17)   In-class survey  
28 Tuesday 4/11 Data structure and storage (19)   In-class survey Initial model of Project #2
29 Thursday 4/13 Data structure and storage (19)   In-class survey A8
30 Tuesday 4/18 Data processing architectures (20)   In-class survey  
31 Thursday 4/20 Data integrity (22)   In-class survey Project #2
32 Monday – Thursday 4/24 – 4/27 Final Exam   The Final Exam Submit Team Evaluation form before the final.

Syllabus Versions

revision date notes
12/24/2016 Original version posted
4/17/2017 Moved back due date of Project #2



This syllabus is a fork of one created by Rick Watson for his Data Management class at UGA. Many thanks to Rick.