Course Information 95–703 E: Database Management 12 Unit Course Fall 2018 Instructor: Janusz Szczypula Office: Hamburg Hall (HbH) 3036 Phone: 268 – 6096 E-mail: [email protected] Office hours: Wednesday, 9 a.m. – 12 noon and by appointment Teaching Assistants:* TBD Meeting Times: Monday & Wednesday, 3:00 p.m. – 4:20 p.m., HbH 2008 Class Website: www.cmu.edu/canvas Prerequisites There are no prerequisites for this course for students in “Data Analytics”, in MISM, and in MSIT Programs in the Heinz College. For other Heinz College students, 90728 (Introduction to Database Management) is a prerequisite. Description Databases systems are central to most organizations’ information systems strategies. At any organizational level, users can expect to have frequent contact with database systems. Therefore, skill in using such systems – understanding their capabilities and limitations, knowing how to access data directly or through technical specialists, knowing how to effectively use the information such systems can provide, and skills in designing new systems and related applications – is a distinct advantage and necessity today. The Relational Database Management System (RDBMS) is one type of database systems that is most often used these days and is the primary focus of this course. Further, to provide students with opportunity to apply the knowledge they learn from the lectures, various homework assignments, SQL assignments, and a database implementation project will be given. * All the TAs will be helping students from any section of the Database Management class. Complete list of TAs and their office hours will be posted on the class website.2 Course Materials Lecture Notes: Lecture notes will be provided for each class. They can be used during the semester you take the class. They cannot be shared after the class is concluded without permission of the instructor. Textbook: Casteel, J., “Oracle 11g: SQL,” Course Technology, Cengage Learning, 2014 Suggested Books: Connolly, T. and C. Begg, “Database Systems: A Practical Approach to Design, Implementation, and Management,” 6th edition, Addison-Wesley, 2015 Coronel, C. and S. Morris, “Database Systems: Design, Implementation, & Management,” 12th edition, Cengage Learning, 2017 Hoffer, J. A., R. Venkataraman, and Heikki Topi, “Modern Database Management,” 11th edition, Prentice Hall, 2012 Price, J., “Oracle Database 11g: SQL,” McGraw Hill, 2008 Software: We will use Oracle Database 11g Express Edition for Windows. No other components of Oracle Software will be used in this class. Course Objectives Objective How Assessed Gain good understanding of relational data model in terms of data structure, data integrity, and data manipulation. Homework Assignment, Final Exam Understand and create conceptual database models utilizing entity-relationship modeling. Homework Assignment, Final Exam Design data structures that will limit redundancy and enforce data integrity while conforming to organizational requirements utilizing normalization methodology Homework Assignment, Final Exam Understand the theory behind the relational data model as it applies to interactions with current database management systems Homework Assignment, Final Exam Read and interpret a given data model to query the database and transform the data into information using Structured Query Language (SQL) SQL Assignment, Project Implement a data model in a current relational database management system Project Create reports, based on transactional data, including elements such as data groupings and summary values. SQL Assignment, Project3 Evaluation Method Student’s performance in the class will be evaluated based on the following components: Homework Assignments: The homework assignments require students to employ holistic critical thinking to design database models applying the concepts learnt in the lectures. The focus is on understanding business requirement and designing data models to capture good quality data. The range of topics covered in the assignments includes: conceptual and logical database modeling, normalization, and relational algebra. The relational algebra assignment is a foundation for learning the Structured Query Language (SQL). SQL Assignments: The SQL assignments are hands-on assignments that require students to create and execute various SQL statements and queries using Oracle Database 11g Express System. The submitted assignments are required to be well formatted and readable. Project: Based on the logical model of a small database, the project consists of implementing the data structure, performing specific queries, data manipulation tasks, and querying system catalog to retrieve metadata. The project will integrate and apply the concepts you have learned in class. Detailed guidelines for the project assignment will be distributed and discussed in a later part of the semester. The project deliverables are due at the beginning of the last class meeting (see the course outline below). Exam: The exam will be a comprehensive closed book, closed notes exam. The exam is to be completed by you individually without help of any other student. The date for the exam is specified in the class schedule. The exam will be graded by the Instructor and class TA. Final grades will be posted in the official Student Information System that can be accessed by students directly through the Internet. Homework Assignments 20% SQL Assignments 15% Project 15% Final Exam 50% 100% Grading Scale A+ 97% – 100 % B+ 85% – 88.99 % C+ 73% – 76.99 % A 93% – 96.99 % B 81% – 84.99 % C 69% – 72.99 % A– 89% – 92.99 % B– 77% – 80.99 % C– 65% – 68.99 % Scores below 65% equate to a failing grade (R)4 Course Outline Week 1: August 27 (M) & August 29 (W) Topic (M) Introduction to Database Models (W) Relational Model Suggested Readings CM: Ch. 1-2 (pp.2 – 48); CB: Ch. 4 (pp.101 – 115) Week 2: September 3 (M) & September 5 (W) Topic (M) Labor Day – No Class (W) Entity Relationship (ER) Modeling Suggested Readings CB: Ch. 12 (pp.357 – 381) Assignment HW#1 handed out Due: September 12 Week 3: September 10 (M) & September 12 (W) Topic (M) Extended ER Modeling (W) Logical Database Design Suggested Readings HVT: Ch. 4 (pp.153 – 175); CB: Ch. 17 (pp.479