BUSBIS 1605 Sec 1210 – Database Management
- Dave Eargle (contact)
- Tues/Thurs 12:30PM - 1:45PM
- 209 Mervis Hall
- Office Hours
- By appointment (Skype or Hangouts preferred)
- Google Group
- Visit the group
- Use the group for asking questions to all class members – for instance, tech troubleshooting help questions.
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.
Students completing this course will
- understand the organizational issues involved in data management
- be able to develop a valid data model for a business system of medium complexity
- be able to build and use a relational database
- be able to formulate complex relational database queries
- be able to use R for data analytics
- be aware of the issues related to managing “big data”
- be familiar with the principles of managing and exploiting organizational data
- The organizational perspective on data management
- Data modeling and SQL
- Relational DBMS
- Organizational intelligence technologies
- Introduction to R
- Data visualization
- HDFS and MapReduce
- Data structure and storage
- Data processing architectures
- Data integrity and data administration
- Laptop for individual assignments and exams:
Download and install MySQL Community Server (or, if you’re interested in Web Dev, WAMP or MAMP)
- Server for SQL exercises:
Hostname: mysql.daveeargle.com. Username and password on CourseWeb. Default schema: Text.
- Server for group work
Hostname: mysql.daveeargle.com. You will get a group id and password.
- Server connection parameters TBA
Groups should contain between three and four persons.
I will send messages via CourseWeb. Feel free to contact me outside of CourseWeb.
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.
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.
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.
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 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
See the class schedule for the due date. The due time is 11:59pm on the due date.
Exercises (on your own)
- SQL exercises are based on the ClassicModels database.
- Please use the assignment identifier so I can keep track of your submissions. As I have many assignments to grade, help me to be efficient.
- Resubmission: If you receive less than full points for an exercise, you can make one resubmission up to one week after the intital due date for the first three assignments.
- Please submit via CourseWeb and follow the specificied assignment guidelines.
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
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
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
|A4||One-to-One and Recursive Relationships||
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
|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:
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.
- You will give a 10 minute class presentation directed at your fellow class members.
- Points will be deducted for exceeding 10 minutes.
- The presentation should focus on the applications of the selected technology and the business opportunities it provides.
- Spend no more than 3 minutes, if you judge it necessary, describing or demonstrating how the technology works.
- A video should not be a substitute for your presentation but should augment it to visualize a point you cannot present otherwise.
- No handouts (save the trees), but you can share a link to your presentation with me (the professor), and I’ll share it with the class.
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.
|3D printing||Team Avocado||
|Autonomous vehicles||Über Ëverywhere||
|Internet of things||Group 1||
|Augmented reality||Purple People Eaters||
|Proximity marketing (iBeacon and Eddystone)||The Lonely Two||
|NFC (payments, etc)|
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 .
|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|
This form should be submitted before the final exam.
- The I'm Done Quiz
- Textbook homepage
|Before Class||In Class||Due by 11:59pm|
|2||Tuesday||1/10||Data (1)||Acquire the Textbook||In-class survey|
|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|
|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.|
|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.