ECS 165A: Database Systems

Instructor: Mohammad Sadoghi
E-mail: [email protected]
Office: Kemper 3055
Office Hours: Thursdays 2:00-4:00pm

Teaching Fellow: Sarmishta Burujupalli
Email: [email protected]
Office: Kemper 47
Office Hours: Wednesdays 4:00-6:00pm

Teaching Fellow: Giritheja Sreenivasulu
Email: [email protected]
Office: Kemper 55
Office Hours: Mondays 3:00-4:00pm

Lecture Time: TueThu 4:40 pm - 6:00 pm
Location: The Grove (Surge III) 1309

Discussion Time: Wed: 9:00 am - 9:50 am
Location: The Grove (Surge III) 1309


Overview

This is an innovative data management course that provides an introduction to the design and development of fundamental concepts in relational database management systems (DBMS). You will learn the theory behind database systems, the issues that affect their functionality and performance, and most importantly, what it takes to effectively utilize modern databases in practice.

The course has completely been redesigned, all students are expected to work in a group of four on an exciting, open-ended, data-oriented, quarter-long project, in a sense, operating and simulating startup environments. Needless to say, this rewarding experience is accompanied by a significant development effort that spans hands-on experience on a variety of technologies ranging from front-end (e.g., React, React Native, Swift, Objective-C, Java) to the back-end (e.g., SQLite, Firebase, Cassandra, MongoDB, PostgreSQL) in order to build a product around data, namely, building an Instagram App in this offering of the course. It involves studying and building all stage of data life-cycle including but not limited to capturing, aggregating, and visualizing it.

The course work is complementary to the classical well-formed, prescriptive model of assignments/projects that are indeed effective and invaluable in practice. Instead by design, the project is intended to be open-ended, namely, minimal instructions and requirements will be provided, as such it rewards and values research & development, taking risks, above all, it is aimed to foster and tap into the creativity of each individual.

The quarter-long project is broken into a set of four milestones, all milestones will be graded orally, where the progress is presented by all four group members, and each group member must be ready to answer questions about any aspect of the project; the latter is utmost importance to ensure comprehensive learning experience and fair division of work among all members. Furthermore, in each milestone, a bonus of up to 20% can be gained to further encourge taking a risk, going the extra mile, and to just be curious & creative.

A fact of life, when there is group work, whether at school or in society, there are occasional conflicts; and it is crucial to learn how to resolve our differences and be receptive, open, and kind to one another. In kindness and reflection, we shall aim to resolve all conflicts. It is the group responsibility to handle all internal affairs, and only when absolutely necessary involving the instructor. But note, only under very rare exceptional circumstances, a group re-structuring would be granted because once the group is formed, at least for 10 weeks, we must learn how to work with each other in harmony.

For each group, it is recommended that each member lead one aspect of the project while contributing and learning about other parts; roughly, the four main components are (1) front-end, UI, and data visualization, (2) app logic and data aggregation/querying, (3) data model and indexing, and (4) database management and tunning.

As for the lectures, the list of topics covered would include but not limited to:

  • DBMS Concepts and Architecture
  • Database Design, the E-R Model, Normalization, and Tuning
  • Relational Databases, Relational Algebra and Calculus
  • Query Languages (SQL)
  • Storage and Indexing
  • Query Evaluation and Optimization
  • Management, Concurrency Control, and Recovery
  • Database Security, Blockchain

Syllabus

Textbooks:

Required:
  • "Database Management Systems", 3rd Edition. Raghu Ramakrishnan and J. Gehrke. McGraw Hill, 2003, ISBN 0-07-246563-8.
  • Additionally, a list of research papers will be added later as part of the optional reading for the enthusiastic students.
Optional:
  • "Readings in Database Systems", 4th Edition. Joseph M. Hellerstein, Michael Stonebraker. 2005
  • "The Elements of Style", 4th Edition. William Strunk Jr. and E. B. White. 1999
  • "Style: Toward Clarity and Grace". Joseph M. Williams. 1995

Workload:

The key component of the course is the quarter-long group project that is broken into a set of four milestones. All milestones will be graded orally, where the progress is presented by all four group members, and each group member must be ready to answer questions about any aspect of the project; the latter is utmost importance to ensure comprehensive learning experience and fair division of work among all members. Therefore, for each milestone, a portion of the grade is devoted to the presented project as a whole on which all members receive the same grade (70% of the grade), but the remaining portion is individualized (30% of the grade), so for each milestone, not all group members may receive the same grade. For each milestone, a bonus of up to 20% can be granted.

Additionally, there will be a pre-milestone individual assignment, for which each student is expected to think about and explore all aspects of the project from frontend to the backend. In other words, each student for his/herself, need to think about what it is required to make an app and document their finding in a few pages. Of course, it is highly recommended to search the web for tutorials and other useful materials. As for part this pre-milestone phase, (1) the group must be formed, namely, when each student submit their report, the name of their group members must be submitted; and (2) each student must decide which aspect of the project he/she wishes to lead based on the research. If you need help to form your group and look for partners, kindly contact the TAs. Note any group assignment by TAs will be random.

The mid-term and final will cover materials from the project and lectures.

Grading:

The final grade will be based upon the following components:

Communication:

For communication with the instructor, please use email ([email protected]) and not Canvas. The instructor will not check/reply to Canvas messages.

For group communication (monitored by TAs) and other students, we will rely on Slack. Please contact the TAs to add you to our Slack channel: ECS165A Slack.

Course Policy:

In this class, we adopt the UC Davis Code of Academic Conduct available here.

In addition, late submission will result in a 10% penalty for each day late. Students are strongly advised that any act of cheating will result in a score of 0 for the entire milestone (or course) and offences will be reported to the Office of the Dean of Students. You are encouraged to discuss problems and ideas but the final solution or code must be your own. In the event of a major campus emergency, course requirements, deadlines, and grading percentages are subject to changes that may be necessitated by a revised semester calendar. If such unusual circumstances arise, students may determine any such changes by contacting the instructor.


Contents

Class Schedule:

  1. Introduction to Database Systems (Chapter 1)
    • The Entity-Relationship Model (Chapter 2)
    • The Relational Model (Chapter 3)
    • Optional Advanced Topics: Overview of Modern Distributed Data Processing Systems (Hadoop, Spark)
  2. Database Query Languages
  3. Database Design & Tuning
  4. Storage and Indexing (tentative)
  5. Query Evaluation (tentative)
  6. Transaction Management (tentative)
    • Transaction Properties (Chapter 18)
    • Concurrency Control (Chapter 19)
    • Crash Recovery (Chapter 20)
    • Optional Advanced Topics: In-memory Databases (Microsoft Hekaton: Paper)
    • Optional Advanced Topics: Deterministic Concurrency Control (QueCC: Paper)
    • Optional Advanced Topics: Multi-version Optimistic Concurrency Control (2VCC: Paper)
    • Optional Advanced Topics: Distributed Non-blocking Transactions (EasyCommit: Paper, Slides)
    • Optional Advanced Topics: Distributed Deterministic Transactions (Calvin: Paper, Distributed Workflow: Paper)
    • Optional Advanced Topics: Avoiding Distributed Coordination Using Partitioning & Replication (Schism: Paper)
  7. Index & Storage Management (tentative)
    • Optional Advanced Topics: Index Maintenance (Indirection: Paper)
    • Optional Advanced Topics: Resilient Distributed Datasets (RDDs: Paper, Slides)
    • Optional Advanced Topics: Lineage-based Storage Architecture (L-Store: Paper, Slides)
  8. Secure Transactions & Blockchain (tentative)
    • Optional Advanced Topics: Blockchain Landscape and AI Renaissance: The Bright Path Forward. [Tutorial: Part 1, Part 2]
    • Optional Advanced Topics: Blockchain Transaction Processing. [Encyclopedia: Preprint]


Announcements

March 18, 2019: It is my pleasure to announce the top Instagram App presentations and demo. Needless to say, everyone did a wonderful job, exceeding my expectation by far. February 27, 2019: Milestone #4 is out. It is due on Tuesday, March 12 through Canvas. There will be a 10% penalty for each late day. After three late days, the assignment will not be accepted.
February 13, 2019: Milestone #3 is out. It is due on Tuesday, February 26 through Canvas. There will be a 10% penalty for each late day. After three late days, the assignment will not be accepted.
February 13, 2019: An excellent guide for "creating beautiful presentations" by Ryan Johnson (University of Toronto).
January 31, 2019: It is my great pleasure to announce that we will have invited design lecture by Aroosha Sarrafi, entitled "What Else Speaks Human".

Aroosha Sarrafi graduated from Boston University with MFA degree in graphic design and has over 20 years of experience creating, designing for advertising campaigns, building multiple brands, and innovating digital experiences. Aroosha taught design for four years at Connecticut State University. At McGraw Hill Education, she currently provides visual direction, and leadership to UX Designers across company. She delivers UI design solution for various products and working closely with Product Managers, UX Designers and DEV teams to generate creative ideas, solved highly complex issues; filtering creative ideas to identify the most appropriate design for the situation and audience. She defined and implemented visual design vision, guidelines, consistency and quality in MHE products and on brand by collaborating with designers, researchers, product management, engineers, and other stakeholders. As a creative leader, she has over 10 years of experience mentoring design teams within cultures that foster innovation and leadership. Coached UX Designers to seek new insights and view opportunities from different perspectives. Encouraged designers to support a culture of creativity by experimenting, taking visual strategy risks and learning from mistakes.

January 31, 2019: Milestone #2 is out. It is due on Tuesday, February 12 through Canvas. There will be a 10% penalty for each late day. After three late days, the assignment will not be accepted.
January 28, 2019: Here you will find two app templates within React Native framework backed by Firebase and SQLite.
January 20, 2019: Milestone #1 is out. It is due on Tuesday, January 29 through Canvas. There will be a 10% penalty for each late day. After three late days, the assignment will not be accepted.
January 9, 2019: Pre-milestone Homework is out. It is due on Saturday, January 19 through Canvas. There will be a 10% penalty for each late day. After three late days, the assignment will not be accepted.
January 9, 2019: Slides for Discussion session is available here.
January 8, 2019: Welcome to ECS 165A.

Handouts

Course materials/grades will be made available on your Canvas account.