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 Abuse DB	Entity-Relationship Model	[link]
8	18.03	3 Abuse DB	ER to Relational Model	[link]
9	20.03	3 Abuse DB	Integrity Constraints and Recursion	[link]
10	25.03	3 Abuse DB	Functional & Multi-value Dependency	[link]
11	27.03	3 Abuse DB	Normal Forms I	[link]
12	01.04	3 Abuse DB	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

• Slides: https://1drv.ms/p/s!AoH59ceswCf9iU3MFnXHrzD-kd0u?e=1T0bUs

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

• Slides: https://1drv.ms/p/s!AoH59ceswCf9iWlp3dw8x44ozXmV?e=MFtrdC

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

Lecture 23. Distributed Transactions & Key-value Stores

• Slides: https://www.dropbox.com/s/bm4ffz3thbthl9x/dmdb-2019-lecture-24.pdf?dl=0

Lecture 24. Research Topic

Lecture 25. Exam Review 2/2

• Slides: https://1drv.ms/p/s!AoH59ceswCf9ihZKPLYy65sRGkWB?e=hBba7u