CS122A Winter 2015: Introduction to Data Management

Course Personnel

Instructor:
Mike Carey
E-Mail: ​mjcarey@ics.uci.edu
Office: Bren Hall 2091
Office hours: Tue 4-5 PM, Thu 4-5 PM

TA:
Markus Dreseler
E-mail: ​dreselem@ics.uci.edu
Office: DBH 2068
Office hours: Wed 4-5 PM

Reader:
Taewoo Kim
E-mail: ​taewok2@ics.uci.edu
Office: DBH 2062
Reader hours: Mon 3-4 PM

Meeting Times & Places

Lecture:
Time: Tue/Thu 5-6:20 PM
Place: HH 178

Discussion 1:
Time: Thu 4-4:50 PM
Place: SSTR 100

Discussion 2:
Time: Wed 5-5:50 PM
Place: DBH 1300

Discussion 3:
Time: Wed 6-6:50 PM
Place: DBH 1300

---

Course Objectives

This course provides students with an introduction to the design of databases and the use of database management systems in support of applications. It covers the entity relationship (E/R) approach to database design. It then covers the relational data model, mapping of E/R designs to relations, relational database design principles, abstract query languages such as the relational algebra and relational calculus, and the industry-standard query language, SQL. Students will gain exposure to how relational database management systems are used to manage an actual database. Time permitting, the course will also touch lightly on advanced database management topics such as transactions and/or semi-structured data management.

This course is aimed at database design and the use of database management systems in implementing database applications. It feeds into a follow-on project course, CS122B, whose focus is data-centric Web applications. The CS122A/B course sequence does NOT cover the internals of database systems; that material is covered in the undergraduate course CS122C and its graduate-level follow-on course CS223. (The course textbook also delves further into that material for those students who are curious about what goes on under the hood.) Interested students are strongly encouraged to take one, two, or all of these courses; CS122B and CS122C are independent, and each requires only CS122A as their required database background.

Prerequisites

Students should have some experience programming in Java, C#, or C++.

Textbooks

Database Management Systems (3rd Edition) by Raghu Ramakrishnan and Johannes Gehrke, a.k.a. "the Cow book".

---

Topic Coverage and Exam Schedule

Syllabus

Date	Topic	Reading
Jan 6	Hello World and Introduction
Jan 8	Introduction (cont.)	Ch. 1
Jan 13	E-R Model I	Ch. 2.1-2.5, 2.8
Jan 15	E-R Model II and Intro to Relational	Ch. 3.1-3.2
Jan 20	E-R-to-Relational Translation	3.5
Jan 22	Relational Design I	Ch. 19.1-19.6, 20.8
Jan 27	Relational Design II	Ch. 19.1-19.6, 20.8
Jan 29	Relational Algebra I	Ch. 4.1-4.2
Feb 3	Relational Algebra II and Calculus I	Ch. 4.2-4.3
Feb 5	Relational Calculus II	Ch. 4.3
Feb 10	Midterm Exam	All Intro and Theory Material
Feb 12	SQL I (Basic SPJ and Nested Queries)	Ch. 3.4, 5.1-5.3
Feb 17	SQL II (Aggregation, Grouping, Nulls, Outer Joins)	Ch. 5.4-5.6
Feb 19	SQL III (Constraints, Triggers, Views, Security)	Ch. 3.3, 3.6, 5.7-5.9, 21.1-21.3, 21.7
Feb 24	Indexing I	Ch. 9.1, 8.1-8.3, 10.1-10.2
Feb 26	Indexing II	Ch. 10.3-10.8, 11.1
Mar 3	Physical DB Design	Ch. 8.5, 20.1-20.7
Mar 5	Semistructured Data (NoSQL)	AsterixDB Paper​
Mar 10	Basics of Transactions	Ch. 16 and Lecture Notes
Mar 12	Recap and Endterm Review	All Course Material
Mar 19	Final Exam	All Course Material (Practice Emphasis)

Midterm Exam

Time: Tue, Feb 10, 5-6:20 PM
Place: HH 178
(Example midterm)​
(Solution to example midterm)​
(Solution to our midterm)​

Final Exam

Time: Thu, Mar 19, 4-6 PM
Place: HH 178
(Example final)​
(Solution to example final)​
(Solution to our final)​

---

Exams, Assignments, and Grading

Grading Critera

Homework: 40%
Quizzes: 5%
Midterm: 25%
Final: 30%

Homework and Quizzes

Homework assignments must be turned in by the assigned due dates/times. Turning in an assignment will involve handing it to one of the course instructors at the start or end of class or office hours. The 40% of your grade attributed to homework will be based on your top 7 out of 8 homework scores. (You can spend the 8th one however you like, but if you decide to skip an assignment, do keep in mind that the material will still be on the exam and that the homework is intended to be a useful study/practice tool.) There will also be short weekly quizzes in the discussion sections to give you further practice with the course material; those will count very little towards your final grade (they are mostly a discussion section participation score), and they're really just there for your benefit (to give you another opportunity to check your understanding of things).

Grade Change Policy

For all of the graded assignments as well as the midterm exam, if you disagree with the grading, you may discuss your concerns with the relevant instructor (professor, TA, or reader) within two weeks after they are returned. After that, all grades will be considered final. Instructors' office hours are the best time/place to handle these issues (or you can make a separate appointment).

Collaboration Policy

All homework assignments are to be completed individually. It is okay to discuss the assignments with your peers, e.g., to clarify details of assignments or to compare thoughts on rough alternative approaches, but the bulk of your work is to be done by yourself. See ​http://www.ics.uci.edu/ugrad/policies/index.php#academic_honesty for a good discussion of what is/isn't considered honest collaboration. The exams are also to be done solo :-).

Late Policy

The due date will be clearly indicated on all problem sets and projects. Assignments will still be accepted for up to two days after the due date, but you will lose 20 points per day (from the 100-point total per assignment) for any lateness. We will not accept assignments in any form after that time. "Stuff happens" sometimes, as we all know, so you should anticipate that fact and try to avoid working up until the very last minute. Assignments MUST be turned in per the assignment's instructions by the indicated deadline date/time in order to get full credit. (Note: The best-7-out-of-8 grading policy is another fallback plan for what would otherwise be late homework assignments; that will work once, but only once.)

Academic Honesty Policy

Cheating is one area where the instructor for this course has zero patience or sympathy. You are here to learn, and cheating defeats that purpose. All students will be expected to adhere to the UCI and ICS Academic Honesty policies (see ​http://www.editor.uci.edu/catalogue/appx/appx.2.htm#academic and ​http://www.ics.uci.edu/ugrad/policies/index.php#academic_honesty to read their details). Any student found to be involved in cheating or aiding others in doing so will be academically prosecuted to the maximum extent possible: that means you will fail this course. (Ask around.) Just say no to cheating!

---

DBMS Platform

This class will use an industrial-strength relational database management system (RDBMS) for the hands-on homework assignments. In past terms we have sometimes allowed students to choose between MySQL (an open source DBMS), DB2 (from IBM), and/or other RDBMSs of their choosing. This quarter we will be suggesting that everyone use the same system, namely MySQL.

For information on how to set up and use MySQL, please see: [MySQL Installation Guide for Mac]​, [MySQL Installation Guide for Windows]​, [Sample SQL Script used in the guide]​.
For information on how to use MySQL Command-Line Tool, please see: [MySQL Command-Line Tool Guide for Mac]​, [MySQL Command-Line Tool Guide for Windows]​ [Sample SQL Script used in the guide]​.
For further information on how to use the DBMS, please go here: ​https://dev.mysql.com/doc/refman/5.7/en/index.html.

If you have a strong preference for using another database system for the course project, e.g., because you are working elsewhere on- or off-campus with a different RDBMS (e.g., SQL Server), you may e-mail the Instructor with a request for permission to do so. If you opt to do this, you will then be on your own to make sure everything works right within the prescribed timeframes (i.e., DBMS difficulties will not be accepted as a lateness excuse).

---

Discussion Forum for All Things CS122A

This term we will be using Piazza for online class discussions. The system is highly catered to getting you the help you need fast and efficiently from classmates, the TAs, the Reader, and the instructor. Rather than emailing course or HW content questions to the teaching staff, you will be expected to post your questions on Piazza. (If you have any problems or feedback for the Piazza developers, email ​team@piazza.com.) We've used Piazza for this class multiple times before, and it seems to work remarkably well.

You can find our class Piazza page at: ​https://piazza.com/uci/winter2015/cs122a/home.

---

HW Due Dates & Links

Homework Assignments

Due Date	Topic	Assignment	Solution	Grading Notes
Tue, Jan 20 (5 PM)	E/R Modeling	[HW 1]​ [Template]​	[HW 1 Solution]​	[HW 1 Grading Notes]​
Tue, Jan 27 (5 PM)	E/R-to-Relational Translation	[HW 2]​ [Template]​	[HW 2 Solution]​	[HW 2 Grading Notes]​
Tue, Feb 3 (5 PM)	Relational Design Theory	[HW 3]​ [Template]​	[HW 3 Solution]​	[HW 3 Grading Notes]
Tue, Feb 10 (5 PM)	Relational Algebra and Calculus	[HW 4]​ [Template]​	[HW 4 Solution]​	[HW 4 Grading Notes]​
Tue, Feb 24 (5 PM)	SQL (Hands-on)	[HW 5]​ [SQL]​ [Template]​	[HW 5 Solution]​	[HW 5 Grading Notes]
Tue, Mar 3 (5 PM)	More SQL (Hands-on)	[HW 6]​ [SQL]​ [Example]​	[HW 6 Solution]​	[HW 6 Grading Notes]​
Tue, Mar 10 (5 PM)	SQL Design and Indexing	[HW 7]​ [SQL]​ [Template]​	[HW 7 Solution]​	[HW 7 Grading Notes]
Tue, Mar 17 (5 PM)	NoSQL (Hands-on)	[HW 8]​ [AQL]​ [Template]​	[HW 8 Solution]​	[HW 8 Grading Notes]​

Discussion Section Quizzes

Week	Topic	Quiz	Solution
Week 1	Academic Honesty	[Quiz 1]​	[Quiz 1 Solution]​
Week 2	ER Diagrams	[Quiz 2]​	[Quiz 2 Solution]​
Week 3	E/R-to-Relational Translation, SQL	[Quiz 3]​	[Quiz 3 Solution]​
Week 4	Functional Dependencies and Normal Forms	[Quiz 4]​	[Quiz 4 Solution]​
Week 5	Relational Algebra	[Quiz 5]​	[Quiz 5 Solution]​
Week 6	SQL I	[Quiz 6]​	[Quiz 6 Solution]​
Week 7	SQL II	[Quiz 7]​	[Quiz 7 Solution]​ [Quiz 7 SQL]​*
Week 8	Indexing	[Quiz 8]​	[Quiz 8 Solution]​
Week 9	Physical DB Design	[Quiz 9]​	[Quiz 9 Solution]​

*) This SQL script drops existing tables, so you might want to execute it on a second database.