Skip to content
FTM BSc

CS202 · Learning materials

Eleven chapters. Each is self-contained: outcome, primary reading, teaching, two misconceptions, worked example, self-check, problem set, solutions. Chapter cross-references are links — if a chapter says it assumes another, the link is there to go back to.

Every measurement in this module was taken on a seeded copy of the FTM booking schema (300k enquiries, 400k enquiry-band rows, 1.2M messages) on PostgreSQL 18, not on staging. Rerun them on a copy before quoting a number at anyone.

Primary sources

  • UW CSE 414 — the application-facing course: SQL, schema, transactions, tuning. Week numbers and Database Systems: The Complete Book 2e sections are given per chapter.
  • CMU 15-445 — used for the index, planning and concurrency lectures; lecture numbers and Database System Concepts 7e chapters are given per chapter.
  • SQL Performance Explained (Winand) for index selection; SQL Antipatterns (Karwin) for schema design.

Assessment: problem sets 20 marks per chapter (formative, 220 total); coursework labs per the module page; the examination paper is a separate page.

Chapters

Section titled “Chapters”
  1. The relational model, keys and constraints
  2. Joins and aggregates
  3. Subqueries, CTEs, window functions
  4. Schema design: modelling and normalisation
  5. Constraints as design
  6. Hash tables and B-trees
  7. Index selection
  8. EXPLAIN and query planning
  9. Transactions and serializability
  10. Isolation levels, MVCC, deadlock
  11. Migrations on live tables, and Postgres operations