DS3Lab @ Zurich Data Modeling and Database – DS3Lab @ Zurich

ETH Zurich | 252-0063-00L

Data Modeling and Databases

Information

Who: Ce Zhang
When: Wednesday 13-15, Friday 8-10
Where: ML D 28
Textbook: Database Systems: The Complete Book
Discussion Forum: https://moodle-app2.let.ethz.ch/mod/forum/view.php?id=420759
Exercise on Moodle: https://moodle-app2.let.ethz.ch/course/view.php?id=12481
Video Recording: https://video.ethz.ch/lectures/d-infk/2020/spring.html
Head TA: Ingo Müller


Your First Taste of Databases

Database systems are the backbone of the modern data-intensive applications and information systems which impact every single corner of our world and day-to-day lives. While most modern data-intensive applications are constructed using a diverse range of techniques such as data mining and machine learning, combined with more traditional data processing operations, a database almost always fill the role of making the data available and accessible in an efficient and robust way.

In this course, we will cover the basics of modeling, querying, and managing data using a relational database system. Throughout the semester, the students will “play” at two “roles” — (1) as a user of a relational database system, and (2) as a developer of a relational database engine.

When acting as users of a relational database system, we will cover how to use the system to build an application. The topics covered will include: the entity-relationship model, relational modeling, the relational data model, relational data modeling theory (normal forms), SQL, and referential integrity.

When acting as developers of a relational database engine, we will cover how a textbook relational database engine works to support a database user. The topics covered will include: query processing, query optimization, transactions, concurrency control, recovery, distributed and parallel query processing, replication, and distributed concurrency control.

Lectures

#DateSegmentTopicMaterials
119.021 Get ExcitedApollo, the Moon, & a Database[link]
221.022 Use DBKnowledge, Logic, & Relational Model, Relational Calculus, Relational Algebra [link]
326.022 Use DBSimple SQL Queries & DB in Action[link]
428.022 Use DBKnown Unknowns & Views[link]
04.03Class Canceled
506.032 Use DBSQL Hands-on 1[link]
611.032 Use DBSQL Hands-on 2[link]
713.033 Abuse DBEntity-Relationship Model[link]
818.033 Abuse DBER to Relational Model[link]
920.033 Abuse DBIntegrity Constraints and Recursion[link]
1025.033 Abuse DBFunctional & Multi-value Dependency[link]
1127.033 Abuse DBNormal Forms I [link]
1201.043 Abuse DBNormal Forms II[link]
1303.04/Review 1/2 [link]
1408.04/Fun with Analytics[link]
🐇🐇🐇🐇🐇🐇🐇🐇🐇🐇🐇🐇🐇🐇🐇🐇[link]
1522.044 Build DBDB System Overview[link]
1624.044 Build DBAccess Methods [link]
1729.044 Build DBOperator Execution[link]
1806.054 Build DBQuery Optimization[link]
1908.055 Share DBThe Art of e-Bank Robing & ACID[link]
2013.055 Share DBIsolation with Locks[link]
2115.055 Share DBIsolation beyond Locks[link]
2220.055 Share DBRecovery [link]
2322.055 Share DBDistributed Transactions & Key-value Store[link]
2427.05/Research Topic[link]
2529.05/Review 2/2[link]

Learning Materials

Lecture 1. Apollo, the Moon, & a Database


Lecture 2. Knowledge, Logic, & Relational Model, Relational Calculus, Relational Algebra


Lecture 3. DB in Action & Simple SQL Queries


Lecture 4. Known Unknowns & Views


Lecture 5. SQL Hands-on 1


Lecture 6. SQL Hands-on 2


Lecture 7. Entity Relationship Model


Lecture 8. ER to Relational Model


Lecture 9. Integrity Constraints and Recursion


Lecture 10. Functional Dependency


Lecture 11. Normal Form


Lecture 12. Normal Form


Lecture 13. Exam Review 1/2


Lecture 14. Fun with Analytics


Lecture 15. DB System Overview


Lecture 16. Buffer Manager and Access Methods


Lecture 17. Operator Execution


Lecture 18. Cardinality Estimation and Cost Model


Lecture 19. Transactions & ACID


Lecture 20. Isolation with Locking


Lecture 21. Isolation beyond Locking


Lecture 22. Recovery


Lecture 23. Distributed Transactions & Key-value Stores


Lecture 24. Research Topic


Lecture 25. Exam Review 2/2