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
# | Date | Segment | Topic | Materials |
1 | 19.02 | 1 Get Excited | Apollo, the Moon, & a Database | [link] |
2 | 21.02 | 2 Use DB | Knowledge, Logic, & Relational Model, Relational Calculus, Relational Algebra | [link] |
3 | 26.02 | 2 Use DB | Simple SQL Queries & DB in Action | [link] |
4 | 28.02 | 2 Use DB | Known Unknowns & Views | [link] |
04.03 | Class Canceled | |||
5 | 06.03 | 2 Use DB | SQL Hands-on 1 | [link] |
6 | 11.03 | 2 Use DB | SQL Hands-on 2 | [link] |
7 | 13.03 | 3 | Entity-Relationship Model | [link] |
8 | 18.03 | 3 | ER to Relational Model | [link] |
9 | 20.03 | 3 | Integrity Constraints and Recursion | [link] |
10 | 25.03 | 3 | Functional & Multi-value Dependency | [link] |
11 | 27.03 | 3 | Normal Forms I | [link] |
12 | 01.04 | 3 | Normal Forms II | [link] |
13 | 03.04 | / | Review 1/2 | [link] |
14 | 08.04 | / | Fun with Analytics | [link] |
[link] | ||||
15 | 22.04 | 4 Build DB | DB System Overview | [link] |
16 | 24.04 | 4 Build DB | Access Methods | [link] |
17 | 29.04 | 4 Build DB | Operator Execution | [link] |
18 | 06.05 | 4 Build DB | Query Optimization | [link] |
19 | 08.05 | 5 Share DB | The Art of e-Bank Robing & ACID | [link] |
20 | 13.05 | 5 Share DB | Isolation with Locks | [link] |
21 | 15.05 | 5 Share DB | Isolation beyond Locks | [link] |
22 | 20.05 | 5 Share DB | Recovery | [link] |
23 | 22.05 | 5 Share DB | Distributed Transactions & Key-value Store | [link] |
24 | 27.05 | / | Research Topic | [link] |
25 | 29.05 | / | Review 2/2 | [link] |
Learning Materials
Lecture 1. Apollo, the Moon, & a Database
Lecture 2. Knowledge, Logic, & Relational Model, Relational Calculus, Relational Algebra
- Textbook Chapter: Chapter 2
- Slides: https://1drv.ms/p/s!AoH59ceswCf9iU6NtU9ymR28wroB?e=dYLcGh
- Jupyter Notebook (Relational Algebra in PANDAS) [Won’t be in Exam]: https://github.com/zhangce/DBCourseMaterial/blob/master/Relational%20Algebra%20in%20Action.ipynb
Lecture 3. DB in Action & Simple SQL Queries
- Textbook Chapter: Chapter 6
- Slides: https://1drv.ms/p/s!AoH59ceswCf9iVLT7fDClA0KTC81?e=DaYVp1
- Katacoda Tutorial: https://www.katacoda.com/zhangce/scenarios/sql1
- Tutorial without Katacoda: https://github.com/zhangce/DBCourseMaterial/tree/master/SQL1 (Use this if you are not comfortable registering Katacoda — Read through intro.md, step1.md, …, step6.md; Should work in most Ubuntu 16.04 installation)
Lecture 4. Known Unknowns & Views
- Textbook Chapter: Chapter 6, Chapter 8
- Slides: https://1drv.ms/p/s!AoH59ceswCf9iVQ0Wn1PwkwQ5OGw?e=IDOa9t
Lecture 5. SQL Hands-on 1
- Katacoda Tutorial: https://katacoda.com/dkoutsou/scenarios/sql-interactive-part-1
- Textbook Chapter: Chapter 6
Lecture 6. SQL Hands-on 2
- Katacoda Tutorial: https://katacoda.com/dkoutsou/scenarios/sql-interactive-part-2
- Textbook Chapter: Chapter 6
Lecture 7. Entity Relationship Model
- Slides: https://1drv.ms/p/s!AoH59ceswCf9iVh-p498HruKaCn1?e=UCkhEV
- Textbook Chapter: Chapter 4
Lecture 8. ER to Relational Model
- Slides: https://1drv.ms/p/s!AoH59ceswCf9iV1QXD2Fl8nNTl6t?e=9gam2m
- Textbook Chapter: Chapter 4
Lecture 9. Integrity Constraints and Recursion
- Slides: https://1drv.ms/p/s!AoH59ceswCf9iVcN9nGsc5yBtEzu?e=nN7srP
- Textbook Chapter: Chapter 7
Lecture 10. Functional Dependency
- Slides: https://drive.google.com/file/d/1Tktr9Ni8XEXSKk-PSZrK69wK3DpXWAjJ/view?usp=sharing
- Textbook Chapter: Chapter 3
Lecture 11. Normal Form
- Slides: https://1drv.ms/p/s!AoH59ceswCf9iWG88c4VTRio6dGV?e=zG16ci
- Textbook Chapter: Chapter 3
Lecture 12. Normal Form
- Slides: https://1drv.ms/p/s!AoH59ceswCf9iW3_kjBU10zc104h?e=fD6awA
- Textbook Chapter: Chapter 3
Lecture 13. Exam Review 1/2
Lecture 14. Fun with Analytics
- Slides: https://1drv.ms/p/s!AoH59ceswCf9iXQkzRQzHHxkx7-A?e=64atng
- Textbook Chapter: Chapter 22
Lecture 15. DB System Overview
- Slides: https://1drv.ms/p/s!AoH59ceswCf9iXcNebqAYIzqUp72?e=1riJs2
- Textbook Chapter: Chapter 13
Lecture 16. Buffer Manager and Access Methods
- Slides: https://www.dropbox.com/s/fmkuwslhmxgckim/dmdb2019-lecture-10.pdf?dl=0
- Textbook Chapter: Chapter 14
Lecture 17. Operator Execution
- Slides: https://www.dropbox.com/s/jxrqi7q38slgmiu/dmdb2019-lecture-11.pdf?dl=0
- Textbook Chapter: Chapter 15
Lecture 18. Cardinality Estimation and Cost Model
- Slides: https://www.dropbox.com/s/ajf0jxtyobta7ry/dmdb2019-lecture-12.pdf?dl=0
- Textbook Chapter: Chapter 16
Lecture 19. Transactions & ACID
- Slides: https://1drv.ms/p/s!AoH59ceswCf9iXku4SwN2kezGqXp
- Textbook Chapter: Chapter 18, 19
Lecture 20. Isolation with Locking
- Slides: https://1drv.ms/p/s!AoH59ceswCf9iXvd0QY6FR7Wo1Dg?e=ZmhAEe
- Textbook Chapter: Chapter 18, 19
Lecture 21. Isolation beyond Locking
- Slides: https://1drv.ms/p/s!AoH59ceswCf9iX3g7TBEru0YUZeK?e=4XLb8z
- Textbook Chapter: Chapter 18, 19
Lecture 22. Recovery
- Slides: https://1drv.ms/p/s!AoH59ceswCf9iX9OKHFcsJY04TxQ?e=1xNZhZ
- Textbook Chapter: Chapter 17