UNDER CONSTRUCTION: IGNORE UNTIL FURTHER NOTICE!

CS122A Fall 2021

Introduction to Data Management: The Hybrid Edition

Course Personnel

Instructor:

• Mike Carey
  E-Mail: ​mjcarey@ics.uci.edu
  Office hours: Piazza!

Discussion TAs:

• Nada Lahjouji
  E-mail: ​nlahjouj@uci.edu
  Office hours: Piazza!
• Kyle Li
  E-mail: ​haosongl@uci.edu
  Office hours: Piazza!

Assignment TAs:

• Qiushi Bai
  E-mail: ​qbai1@uci.edu
  Office hours: Piazza!
• Alekhya Pyla
  E-mail: ​apyla@uci.edu
  Office hours: Piazza!

Readers:

• GuangXue Zhang
  E-mail: ​gxzhang7@uci.edu
  
• Sriram Rao
  E-mail: ​srirar1@uci.edu
  

Meeting Times & Places

What	When	Where	Who
Lecture A	Mon/Wed/Fri 3-3:50 PM	SSLH 100	Mike
Discussion A1	Wed 5:00-5:50 PM	RH 184	TBD
Discussion A2	Wed 6:00-6:50 PM	RH 184	TBD
Discussion A3	Thu 7-7:50 PM	DBH 1300	TBD
Discussion A4	Thu 8-8:50 PM	DBH 1300	TBD
Lecture B	Mon/Wed/Fri 4-4:50 PM	SSLH 100	Mike
Discussion B1	Thu 5:00-5:50 PM	DBH 1300	TBD
Discussion B2	Thu 6:00-6:50 PM	DBH 1300	TBD
Discussion B3	Wed 7-7:50 PM	DBH 1300	TBD
Discussion B4	Wed 8-8:50 PM	DBH 1300	TBD

---

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 logical 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. It also covers indexing and physical database design, Students will gain exposure to how relational database management systems are used to manage an actual database. Time permitting, the course will also touch briefly on advanced database management topics such as semi-structured data management ("NoSQL") and transactions.

This course is aimed at database design and the use of database management systems in building 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 internal workings of database systems; that material is covered in the undergraduate course CS122C (or its graduate equivalent, CS222) and the 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/CS222 are independent, as each one requires only CS122A as their required database background. Also available as a new CS122A follow-on course is CS1222D, whose focus is on new post-relational data management technologies, i.e., "NoSQL" databases and Big Data management platforms.

Prerequisites

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

Required Textbooks

Database System Concepts (Seventh Edition) by A. Silberschatz, H. Korth, and S. Sudarshan. (Note: Older editions are not okay, as this book has just been chosen for its up-to-date content (as of 2020)!)

---

Topic Coverage and Exam Schedule

Syllabus

Topic	Reading	Cow Reading
Databases and DB Systems	Ch. 1	Ch. 1
Entity-Relationship (E-R) Data Model	Ch. 6.1-6.5, 6.8-6.9	Ch. 2.1-2.5, 2.8
Relational Data Model	Ch. 2.1-2.4, 3.1-3.2	Ch. 3.1-3.2
E-R to Relational Translation	Ch. 6.6-6.7	Ch. 3.5
Relational Design Theory	Ch. 7.1-7.4.1	Ch. 19.1-19.6, 20.8
Midterm Exam 1	Fri, Oct 30 (during lecture time)
Relational Algebra	Ch. 2.5-2.7	Ch. Ch. 4.1-4.2
Relational Calculus	​Wikipedia: Tuple relational calculus	Ch. 4.3-4.4
SQL Basics (SPJ and Nested Queries)	Ch. 3.3-3.5	Ch. 3.4, 5.1-5.3
SQL Analytics: Aggregation, Nulls, and Outer Joins	Ch. 3.6-3.9, 4.1	Ch. 5.4-5.6
Data Science 1: Advanced SQL Analytics	Ch. 5.5, 11.3	(na)
Data Science 2: Notebooks, Dataframes, and Python/Pandas	Lecture notes (and notebook)	(na)
Advanced SQL: Constraints, Triggers, Views, and Security	Ch. 4.2, 4.4-4.5, 4.7	Ch. 3.3, 3.6, 5.7-5.9, 21.1-21.3, 21.7
Midterm Exam 2	Mon, Nov 23 (during lecture time)
Storage	Ch. 12.1-12.4, 12.6-12.7	Ch. 4.7, Ch. 9.1, 8.1-8.3, 10.1-10.2
Indexing	Ch. 14.1-14.4, 14.5	Ch. 9.1, 8.1-8.3, 10.1-10.2, 10.3-10.8, 11.1
Physical DB Design	Ch. 14.6-14.7, 15.1-15.3, 15.5.3	Ch. 20.1-20.7
Semistructured Data Management (a.k.a. NoSQL)	Ch. 8.1, ​AsterixDB SQL++ Primer, ​Couchbase SQL++ Book	​AsterixDB SQL++ Primer, ​Couchbase SQL++ Book
Basics of Transactions	Ch. 4.3, Ch. 17	Ch. 16 and Lecture Notes
Endterm Exam	Wed, Dec 16 (during final exam time)

Midterm Exam 1

Time: Fri, Oct 30, 4-4:50 PM
Place: Gradescope
Midterm 1​
Midterm 1 Solution​

Midterm Exam 2

Time: Mon, Nov 23, 4-4:50 PM
Place: Gradescope
Midterm 2​
Midterm 2 Solution​

Endterm Exam

Time: Wed, Dec 16, 4-4:50 PM
Place: Gradescope
Endterm
Endterm Solution

Helpful Exam Resources

Example Midterm 1
Solution to example Midterm 1
Example Midterm 2
Solution to example Midterm 2
Example Endterm​
Solution to example Endterm​

---

Exams, Assignments, and Grading

Grading Criteria

Exams: 39% (3 x 13%)
Homework: 56% (7 x 8%)
Quizzes: 3%
Piazza: 2%

Homework and Participation

Homework assignments must be turned in by the assigned due dates/times. Details of how to turn in a given assignment will be included in each assignment's handout. The 56% 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 do decide to skip an assignment, keep in mind that the material will still be on the exams and that the homework is a useful study/practice tool for those -- so you will still be responsible for mastering the "skipped" material.) There will also be short weekly quizzes associated with the discussion sections to give you further practice with the course material; those will count very little towards your final grade (taking them will contribute 3%), and they're just there for your benefit (to give you a chance to check your understanding of things and see what questions you might want to ask the TA).

Grade Change Policy

For all of the graded assignments as well as the two midterm exams, if you disagree with the grading, you may raise 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. Gradescope's regrading request feature will be the place to handle grading issues. Do not wait until the end of the term to raise issues, as two weeks means two weeks, and no regrading requests will be entertained once final grades have been posted.

Collaboration Policy

Homework assignments are to be completed individually, but you are strongly encouraged to pair up with a fellow student -- your brainstorming buddy -- for the duration of the quarter. It is okay to discuss assignments with other peers as well, e.g., to clarify the interpretation of a question or to compare thoughts on very rough approaches, but discussions of the details of your work are to stay within your team of two. You should pick this brainstorming partner at the start of the term and then stay with that same person for the remainder of the quarter. 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 to be done solo, but will be open book, open notes, and open manual. :-) Note that UCI's academic honesty policy applies both to person-to-person and to social media interactions (more on this below).

Late Policy

Due dates will be clearly indicated on all HW assignments. Assignments will still be accepted for up to one day (24 hours) after the due date, but you will lose 10 points (out of the 100-point total per assignment) for lateness. We will not accept assignments in any form whatsoever after that time. "Stuff happens" sometimes, especially in this tumultuous year (!), so you should anticipate that reality and 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 for what might otherwise be late homework assignments; that fallback will work once, but obviously just once.) We will release an official solution for most assignments 24 hours after its due date/time; as a result, we must stop accepting your solutions at that time.

Exam Timing Policy

The exam dates are provided at the start of the term. These dates are not flexible and makeup exams will not be offered, so please avoid scheduling interviews, mini-vacations, or any other activities in ways that will interfere with exam-taking. All students will be asked to take the exam on the same date in the same time window(s) in order to ensure that we can give, grade, and fairly curve the entire class on a single exam. The exams will be online this quarter, and will be given using Gradescope; we may end up offering two time windows for each exam in order to accommodate students in remote timezones. (We will see what the demand is for that.)

Academic Honesty Policy

Cheating is the one area where the mostly empathetic instructor for this course has zero patience or sympathy. You are here at UCI to learn, and cheating totally defeats that purpose. If you cheat in real life, on the job, you could actually bring down a company - so I do my part to avoid graduating students who would do that. All students are expected to adhere to the UCI/ICS Academic Honesty policies (see ​https://aisc.uci.edu/policies/academic-integrity/index.php 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 may be academically prosecuted to the maximum extent possible: you could potentially fail the course in its entirety. (Ask around - I've done that before.) Just say no to cheating!!! By the way, it is entirely fine if you look at old CS122A exams, old assignments, old quizzes, old solutions, etc. (Such materials will help you practice and learn!) Again, your goal here should be to learn - and in this course we will ask you to please be on the honor system and behave accordingly! Note that with the test-like way that many companies interview prospective employees these days (which I'm not a fan of), you simply won't make it past the door if you don't know your stuff.

---

DBMS Platform

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

We will tell you when it's time to set up and start to use MySQL. For info on how to do so at that time, please see: [MySQL Installation Guide for Mac], [MySQL Installation Guide for Windows], [MySQL Installation Guide for Linux], [Sample SQL Script used in the guide]. You should grab the latest version (8.0 or above) to ensure that you have all the features available that we will make use of.
For info on how to use MySQL's 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 additional info on how to use the MySQL RDBMS, you should try looking here: ​https://dev.mysql.com/doc/. You will also find that Google searches are a great source of MySQL information and issues/solutions, as there are frequently other MySQL users out there in the Googlesphere having the same problem that you're having.

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., PostgreSQL), you may e-mail an instructor with a request for permission to do so. However, if you opt to do this, you will then be totally 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, and you will have to figure out how some of the advanced features work that are different in PostgreSQL than in MySQL).

---

CS122A Online? For Real?

Yep! The Great Online Database Adventure begins...! The good news is that the Spring offering of CS122A was online too, so many of the kinks have been worked out. Also, with the sizes of the CS122A classes in recent years, much of what we do was already largely happening online anyway. The biggest change this time around will be that the lectures will be prerecorded (using Zoom and distributed via Yuja and Canvas). They will be available for viewing starting at the appointed lecture time, but will remain available for later viewing or viewing in other timezones thereafter. (We will also make last term's lectures available in case you want to view any of those.) The instructor will hang out online on Piazza during the official lecture times, watching for questions that might pop up in real-time. The course's three exams will be offered and taken online using Gradescope's timed online assignment feature. The discussion sessions will be held live on Zoom, but they will also be recorded for viewing later and/or in other timezones. (More details on Discussion sessions will follow once we get some sense of where around the globe folks will be joining from.) PDF lecture slides and quiz solutions will be made available rapidly after the relevant sessions for later reference as well.

For all of this to work, you will need a laptop capable of running MySQL, workable Internet access, and the ability to access (or forward) UCI e-mail, Piazza, Gradescope, and Canvas+Yuja. If this is not you, this unfortunately won't be a good time for you to take this class, as we won't be able to provide workarounds for those dependencies. (Sorry!) You should verify your ability to meet these requirements during the first week of class, before committing to taking it for sure. (There is usually a waiting list for this class, so it's important to make space for those who are will be able to fully participate online.)

Note: This will not be like a real, well-designed, smoothly orchestrated "we meant to do this online" class. This will be us doing our best to give you the best possible CS122A educational experience during an unprecedented time when we cannot safely meet in person. Please be patient and set your expectations accordingly! Don't be surprised if the lecture quality is "earthy" or if my self-appointed "emotional support cat" wanders through the lecture background at some point... (I am often fighting a losing battle for at-home desktop real estate with this particular cat...) That being said, however, I am highly optimistic - I do think that we will indeed be able to deliver a full-quality version of CS122A this way! Spring 2020 went surprisingly well, and I'm hoping that Fall 2020 will too.

---

Discussion Forums for All Things CS122A

We will be using Piazza (heavily!!!) for online class discussions. Piazza aims to get you the help you need fast and efficiently from classmates, the TAs, the Reader, and the Instructor. Rather than emailing your course or HW content questions to the teaching staff, you will be expected to post those questions on Piazza. (If you encounter any problems, or have feedback for the Piazza developers, email ​team@piazza.com.) We've used Piazza for this class many times before, and it works remarkably well. You can find our class Piazza page at ​http://piazza.com/uci/fall2020/cs122a/home. With roughly 400 students enrolled in the course, even when it is offered face-to-face, Piazza is essentially the only hope for managing the class and getting everyone's questions answered -- office hours become hopeless at this scale, unfortunately. Your Piazza activity will also contribute 2% to your overall grade -- in particular, total non-involvement or misbehavior on Piazza may lead to loss of those points.

When using Piazza, there are a few things to keep in mind. Firstly, despite an unfortunate national trend towards accepting nastiness in social media, hurtful Piazza behavior will not be tolerated - so be kind to your classmates. Secondly, Piazza is a wonderful resource for asking and answering questions - as long as it is used thoughtfully. Please avoid re-asking questions that have already been asked and answered - you are responsible for reading others' questions and not re-asking them. With 400+ eyes on each message, lazy question-asking on Piazza is costly and inconsiderate, and such repeated behavior may lead to a loss of Piazza points. Finally, Piazza is not a place to discuss the details of answers to HW problems - i.e., it is not a place to post, request, or compare answers to specific assigned problems! Doing so would actually risk your violating the Academic Honesty Policy, as everyone is expected to ultimately do their own work. (Piazza is a fine place to chat about answers to old sample exams, however.)

As you are aware, you have a weekly Discussion session meeting, and attendance of those is highly encouraged. This encouragement will be provided via the quiz portion of your final grade (3%), as quizzes will be linked to the Discussion sessions. If for some reason you have to miss your session during one week, you can request permission (in advance!) to attend a different one - but please try to avoid that for Zoom load-balancing reasons. You will be held fully responsible for any and all material covered in your Discussion sessions, so if you do opt out or miss one, be sure to watch the Zoom recording of the missed session. The Discussion sessions will also serve (importantly!) as "group office hours" - we will try to leave ample time for questions most weeks, so you may want to come armed with things you'd like to have clarified or expanded on.

---

HW Due Dates and Links

Homework Assignments

Due Date	Topic	Assignment	Solution	Special Notes
Wed, Oct 14 (4:00 PM PDT)	E-R Modeling	HW1 Assignment ​Submission Template Diagram Template	HW1 Solution
Wed, Oct 21 (4:00 PM PDT)	E-R to Relational Translation	HW2 Assignment ​Submission Template	HW2 Solution
Wed, Oct 29 (4:00 PM PDT)	Relational Design Theory	HW3 Assignment ​Submission Template	HW3 Solution	Deadline extended (no 24-hour late period now)
Fri, Nov 6 (4:00 PM PDT)	Relational Algebra	HW4 Assignment RelaX Instructions ​Submission Template	HW4 Solution
Fri, Nov 13 (4:00 PM PDT)	SQL (Hands-on)	HW5 Assignment Load Instructions ​Submission Template	HW5 Solution
Fri, Nov 20 (4:00 PM PDT)	More SQL (Hands-on)	HW6 Assignment ​Submission Template	HW6 Solution
Sun, Dec 6 (6:00 PM PDT)	SQL Design and Indexing	HW7 Assignment ​Submission Template	HW7 Solution
Sun, Dec 13 (6:00 PM PDT)	NoSQL (Hands-on)	HW8 Assignment AsterixDB Instructions ​Submission Template	HW8 Solution

Discussion Section Quizzes

Week	Topic	Quiz	Solution
Week 1	Academic Honesty
Week 2	E-R Modeling
Week 3	E-R to Relational Translation
Week 4	Relational DB Design Theory
Week 5	Relational Algebra
Week 6	SQL & TRC
Week 7	More SQL
Week 8	ISAM & Indexing
Week 9	Indexing & Physical DB design
Week 10	Physical DB design, NoSQL, Transactions