CS202 · Databases, SQL & Indexes
Learning materials — eleven chapters, each with a cited reading, a worked example and a 20-mark problem set. Examination paper — 2 h 30 · 100 marks · grade descriptors · every question mapped to a chapter.
Credits 8 · Hours 80 · Prereqs CS101 · Semester 2
Source courses
Section titled “Source courses”UW CSE 414 — Introduction to Data Management (Autumn 2025). The application-facing course: SQL, schema, transactions, tuning — no buffer pools. https://sites.google.com/cs.washington.edu/cse414-25au
CMU 15-445/645 — Intro to Database Systems (Fall 2025), used only for the index, query-planning and concurrency lectures. https://15445.courses.cs.cmu.edu/fall2025/schedule.html
Texts: CSE 414 names Database Systems: The Complete Book 2e, Garcia-Molina, Ullman & Widom (2008). 15-445 names Database System Concepts 7e, Silberschatz, Korth & Sudarshan (2019), and its reading column is chapter-numbered against it. Caveat noted by the CSE 414 site itself: several weeks’ chapter numbers track Ramakrishnan & Gehrke’s 3rd edition, so check readings against whichever copy is in hand.
Objectives
Section titled “Objectives”- Design a schema that cannot hold a wrong state.
- Write any query she can describe in English.
- Read an
EXPLAINplan and fix the slow one. - State what a transaction isolates and which race it still permits.
Weekly map
Section titled “Weekly map”| Wk | Source | Topic |
|---|---|---|
| 1 | CSE414 wk1 (2.1–3, 6.1–6.2) | relational model, tables, keys, SQL basics |
| 2 | CSE414 wk2 (6.1–6.2, 6.4) | joins, aggregates, GROUP BY |
| 3 | CSE414 wk3 (6.3) | subqueries, CTEs |
| 4 | CSE414 wk4–5 (2.1–2.3, 3.4–3.6) | ER modelling, functional dependencies, why normalise |
| 5 | — | constraints as design: NOT NULL, CHECK, UNIQUE, FK, partial indexes |
| 6 | 15-445 #07–09 (14.1–14.5) | hash tables and B+trees — what an index physically is |
| 7 | 15-445 #08–09 (14.1–14.4) | index selection: composite order, GIN, GiST, partial |
| 8 | 15-445 #15–16 (ch. 16) | query planning and optimisation; reading EXPLAIN (ANALYZE, BUFFERS) |
| 9 | CSE414 wk6 (18.1–18.3) | serializability, 2PL, lock granularity |
| 10 | 15-445 #17, #20 (ch. 18) | isolation levels, MVCC, deadlock, lost updates |
| 11 | — | schema evolution and migrations on live tables; Postgres ops: roles, RLS, replication lag, backup/PITR |
| 12 | — | Reading week |
FTM labs
Section titled “FTM labs”- L1 ten analytical queries against staging, each with its plan.
- L2 find and fix one missing index on a real slow query; measure before and after.
- L3 reproduce a lost update in
payments/, fix it with the right constraint or isolation level. - L4 map FTM’s Postgres roles (
ftm,hermes,ftm_ai_reader) and prove empirically what each can and cannot do.
Project
Section titled “Project”Schema report on FTM: three missing constraints, two missing indexes, one query rewritten — all with measurements.
Reading list
Section titled “Reading list”| Book | Author, year | Use | Free |
|---|---|---|---|
| SQL Performance Explained | Markus Winand, 2012 | The index-and-plan book; per-DBMS; use-the-index-luke.com | Yes |
| SQL Antipatterns | Bill Karwin, 2010 | Schema design mistakes and their fixes; the fastest way to stop making them | No |
| Database Systems: The Complete Book, 2nd ed. | Garcia-Molina, Ullman, Widom, 2008 | CSE 414’s text; chapters 2–3, 6, 18 | No |
| Database System Concepts, 7th ed. | Silberschatz, Korth, Sudarshan, 2019 | 15-445’s text; chapters 14, 16, 18 as reference | No |
| Designing Data-Intensive Applications | Martin Kleppmann, 2017 | Only if the module ever leaves single-node; ch. 5–7 | No |
Assessment
Section titled “Assessment”Coursework 45% (L1–L4) · Project 35% · Check-off viva 10% · Exam 10%.
Deferred: relational algebra proofs, storage internals, recovery logging — 15-445 #03–06 and #21–22, on the trigger “owns Postgres performance”.