Chapter 39 — Exercises (The Capstone)

These exercises are the capstone — guided tasks to assemble, review, and present your project. Work them on your own domain (library / clinic / university / e-commerce / your choice). There are no "answers in the appendix" — the deliverable is your project. ⭐ = stretch / portfolio polish.

Group A — Assemble

39.1 Finalize schema.sql: every entity a table, every relationship a correct FK (with ON DELETE), every rule a constraint, in 3NF (denormalizations noted). Make it idempotent (drops in dependency order, like Mercado's).

39.2 Populate the database: a small deterministic seed and a generator for volume (enough to make indexing meaningful — Chapter 31).

39.3 Write your core queries (one per requirement question from Chapter 1), commented and grouped (operational / reporting / analytical).

39.4 ⭐ Optimize: run your hot queries through EXPLAIN ANALYZE, add justified indexes, and document one before/after improvement (your own slow-query story).

Group B — Build out

39.5 Add a Python data-access layer (repository) for your core entities — all parameterized — and wrap one multi-step operation in a transaction.

39.6 Add at least one view (simplify/secure a join) and, if warranted, a function or audit trigger.

39.7 Create least-privilege roles; ensure all application access is parameterized; protect any sensitive data.

39.8 ⭐ Back up your database and test-restore it into a scratch database; write the restore steps in your runbook.

Group C — Review (the whole-book checklist)

39.9 Review your design: 3NF? proper FKs/ON DELETE? constraints for every rule? no unmodeled M:N or comma-separated lists?

39.10 Review correctness, performance, integrity/concurrency, and security against the chapter's checklist. Fix what fails.

39.11 ⭐ Have someone else (or future-you) try to set up and run your project from your docs alone. What's unclear?

Group D — Document & present

39.12 Write a README: what it is, the domain, setup steps (createdb → schema → data), how to run queries. Aim for "clone to running in minutes."

39.13 Write a data dictionary (each table/column) and include your ER diagram.

39.14 Write a design-decisions document: key choices and trade-offs (normalization, indexes, why PostgreSQL, what you'd change at scale). This is the differentiator.

39.15 ⭐ Put it in a public repository. Prepare a 3-minute walkthrough you could give in an interview (a design decision + a slow-query fix + a scaling thought).

Group E — Reflect

39.16 Which chapter's concept did your capstone rely on most? Which was hardest to apply?

39.17 ⭐ Write the one-paragraph "elevator pitch" for your project: what it demonstrates about your database skills.

Self-check. If your project passes the whole-book review checklist, sets up from your README in minutes, and is accompanied by a design-decisions document you can defend — you have a portfolio piece that demonstrates real database engineering. That's the capstone.