Part VIII — Laboratory Exercises and Projects

Chapter 30. Capstone Database Project

The capstone is the course in one project: a complete, working database system — requirements to deployment — designed, built, secured, tested, documented, and demonstrated by you. Every chapter contributes a phase: the design cycle of Part II, the SQL of Part III, the platform choice of Part IV, the correctness and operations of Part V, the programs and application of Part VI, the scale and frontier judgment of Part VII, and the evidence discipline of Chapters 28–29. The capstone's standard is the book's standard: predicted, verified, documented — a system whose every claim has a run behind it.

This chapter is the guide: eleven phases with deliverables, checklists, and gates — a phase is finished when its gate's questions answer yes from evidence, not intention. Team or solo, semester-paced or compressed, the gates are the grade.

After completing this capstone you will be able to:

• Select and scope a database project honestly (its data model is the test).
• Write a requirements specification that constraints can be derived from.
• Design conceptually and logically, then map to a defensible schema.
• Normalize to a stated form with defended redundancies.
• Implement on PostgreSQL or MySQL with reasons, not habits.
• Deliver queries, views, and stored programs as a tested layer.
• Build and integrate an application over the database.
• Test security and transactional correctness as features.
• Prove backup, recovery, and performance with evidence.
• Document, demonstrate, and deploy the system as a product.

30.1 Project selection and problem definition

The selection test. A project is right for this capstone when its heart is a data model: several entity types (6–15 tables in the final schema), real relationships including at least one M:N bridge, at least one rule that needs a trigger or procedure (Chapter 22's ladder), history worth keeping (a Type-2 or append-only decision), and users with questions reports answer. Domains that pass: event registration, clinic scheduling, league management, rental/inventory systems, ticketing, logistics. Domains that fail: a CRUD shell over two tables, a chat app whose state is the UI, anything whose hard part is the frontend.

Problem definition. One page: the domain and users, the three core workflows (the transactions), the five core questions (the reports), the two obvious rules (a constraint and a choreography), and the scope boundary — what is explicitly out. Scope discipline is the project's first risk: out-of-scope statements are requirements' load-bearing walls.

Gate 1: Could you draw the ERD's six central entities right now from this page? Is anything in it not derivable from the page?

30.2 Requirements specification

The document that makes everything downstream derivable (Chapter 5's method, formalized): entities with attributes and owners; business rules stated as rules ("a student may hold at most one active booking per resource" — Chapter 5's state the rules as rules); workflows as transaction descriptions (inputs, steps, failure meanings — Chapter 18's units); reports as questions with their columns and grain (Chapter 25's vocabulary arrives early); non-functional requirements — expected volumes (rows per year — they decide Chapter 19's index work), concurrent users (Chapter 18's isolation choices), privacy class (Chapter 27's governance set).

*Deliverable: REQUIREMENTS.md (4–6 pages). Gate 2: for every rule, name where it will be enforced (constraint / trigger / procedure / application — Chapter 22's ladder). Any rule with no home fails the gate.*

30.3 Conceptual and logical database design

Conceptual first (the ERD of Chapter 5): entities, attributes (typed and classified — simple/composite/multivalued/derived), relationships with cardinality and participation, weak entities and subtypes where they exist, no implementation vocabulary. Review aloud — every relationship reads as a sentence.

Logical second (Chapter 6's mapping): the mapping worksheet — every ER construct mapped, every key chosen with its rationale (natural/surrogate, the UNIQUE companion), every referential action justified by ownership versus audit, every constraint the rules demand placed on the ladder.

*Deliverables: erdiagram (any tool or notation, printed), mapping_worksheet.md. Gate 3: read the diagram aloud end to end; someone who never saw it can restate the domain's rules from the reading.*

30.4 ER diagram and relational schema

The consolidated schema document: the final relational schema — every table with columns, types, nullability (each nullable column's stated absence meaning), keys, foreign keys with actions, and named constraints. This is the bridge artifact between design and DDL, and it is where the two platform drafts first appear (the Chapter 17 type map, applied forward).

*Deliverable: SCHEMA.md. Gate 4: a partner can write the DDL from your schema document alone, without questions — if they must ask, the document, not the partner, is missing something.*

30.5 Normalization and integrity constraints

The theory check, run as a review (Chapter 7's machinery as an audit): FDs stated from the rules; the candidate-key derivation; the form each table reaches (3NF minimum, BCNF where dependencies survive); the defended redundancies — any deliberate denormalization (a historical price, an audited balance) with its rule, its refresh discipline, and its acceptance paragraph; and the constraint inventory — every requirement rule placed (CHECK, UNIQUE, FK action, trigger, procedure, RLS where row scope is real).

*Deliverable: NORMALIZATION.md with the defense paragraphs. Gate 5: for every redundancy, the failure mode it invites is named along with its control; for every rule, one sentence on why its ladder rung is the highest that states it.*

30.6 PostgreSQL or MySQL implementation

The platform decision, made with evidence (Chapter 17's dossier method, scaled to the project): the five percent that matters for this schema — deferrable choreography? partial indexes? RLS? the events scheduler? the JSON containment model? — argued in one page and decided. Then the implementation: migration-numbered DDL (Chapter 24's discipline from day one — 001_schema.sql), the deterministic seed data with documented counts (the Chapter 28/29 pattern), and the load-and-verify script.

*Deliverables: PLATFORM.md (the one-page decision), migrations/, seed.sql with counts. Gate 6: from bare server to verified data in one command, on a fresh machine — the migration runner, the seed, the six-count-style verification, zero manual steps.*

30.7 SQL queries, views, and stored programs

The database's own product layer: the query library — the five core questions as named, parameterized queries with predicted outputs (Chapter 12's queries.sql discipline); the views — user-facing perspectives with the output policy (rendered in-progress states, hidden sensitive columns — Chapter 24's shaping rules); the stored programs — the choreographies (the workflows of Phase 2 as procedures: validation, guarded checks, inserts — Chapter 22's enroll_student pattern), the audit trigger(s), and the rule-ladder decisions documented per program.

*Deliverables: queries.sql, views.sql, programs.sql with contracts. Gate 7: every workflow runs through its procedure (never raw inserts); every report query has its prediction matched by its run — the predict-run-verify evidence, now a habit.*

30.8 Application development and integration

The tier over the database (Chapters 23–24): repositories with all SQL parameterized; the transaction manager with the retry loop (40001/40P01 — the capstone's most-graded single feature); the error mapper (business SQLSTATEs to clean messages); authentication with slow hashes and role-guarded routes; keyset pagination and allowlisted filtering on the lists; the ORM used knowingly (query log on, eager where N+1 lurked) or the raw layer kept honest.

Integration is deliberately modest in scope but complete in discipline: an API or minimal web UI covering the three workflows and two reports — enough surface for the security and correctness tests of the next phase to bite.

*Deliverables: the application source plus APP.md (architecture diagram, layer responsibilities). Gate 8: a user can complete all three workflows through the application; every failure path renders a mapped message — never SQL, never a stack trace.*

30.9 Security and transaction testing

Security as a test suite, not a checklist feeling (Chapter 20's audit, executed): the account inventory (application on least privilege, grants exactly matching its operations); the injection attempts against every input surface (all parameterized — Chapter 23's law, now verified by attack); the row-scope test if RLS or security views are in play; the audit-log check for the three stated questions; and the password/connection posture (hashes, TLS).

Transactions as a test suite (Chapter 18's experiments, applied): the lost-update attack against the guarded workflow (fails correctly); the concurrent same-resource contention (both platforms' behaviors stated in isolation names); the deadlock replay with the retry observed; the savepoint choreography of the longest workflow; and the constraint rejections surfacing as mapped messages (the 23505/45000 paths).

*Deliverables: security_test.md, transaction_test.md with executed evidence. Gate 9: every attack has its observed rejection; every concurrency scenario has its observed behavior and its explanation in level names.*

30.10 Backup, recovery, and performance evaluation

Operations proven, not asserted (Chapter 21): the backup plan executed (logical dump; physical plus archived logs if PITR is in scope); the restore drill — performed, timed, verified by counts and a report query; the failure rehearsal — one deliberately destroyed table recovered via the runbook (or the documented, honest scope decision for small projects); and the performance evaluation — the load test (Chapter 24's 100-concurrent pattern or a bulk-data simulation), the slowest endpoint's EXPLAIN before and after its Chapter 19 fix, and the write-side ledger for the indexes chosen.

*Deliverables: RUNBOOK.md, the drill logs, PERFORMANCE.md with before/after plans and ratios. Gate 10: the restore drill's timeline, executed and timed; the performance report's every claim backed by a pasted plan.*

30.11 Documentation, demonstration, and deployment

The closing phase produces the product's public face. Documentation: the README (what it is, how to run it — the Phase 6 one-command standard), the phase documents as the design trail, the portfolio evidence (Chapter 28's scripts, the two case studies, the parity report) as appendices, and the honest limitations section — what is out of scope, what is simplified, what a production system would add (the professional document names its own edges). Deployment: the Chapter 24 checklist — migrations on deploy, health checks with real queries, configuration from the environment, secrets from their manager, the rollback stated and once rehearsed; hosted anywhere honest (a VM, a container, a managed service tier) with the deployment recorded. Demonstration: the walkthrough script — three workflows, two reports, one failure handled well (a mapped error, recovered), one operation (the restore, or the failover if built) — fifteen minutes, every claim backed by the running system.

The rubric (weights for a solo semester project; teams scale the application and operations phases):

The capstone's final gate is the book's first principle, returned: the system's every claim is checkable — by you, by a grader, by the next engineer. A database that enforces its own rules, reports its own answers, survives its own failures, and documents its own limits is not just a passing project; it is the profession's definition of done.

Chapter Summary

• Selection: the project's heart must be a data model (6–15 tables, a bridge, a trigger-worthy rule, history, questions); scope walls are load-bearing.
• Requirements make everything derivable; every rule gets a home on the enforcement ladder — the gate is placement, not completeness.
• Conceptual (ERD) then logical (mapping worksheet) designs gate on readability: a stranger restates the domain from the diagram.
• The schema document gates on writeability: a partner drafts the DDL without questions.
• Normalization is an audit: stated forms, defended redundancies, and the constraint inventory complete.
• Implementation: platform decided by the 5% that matters, migration-numbered DDL, deterministic seed — and one command from bare server to verified data.
• The query library, views with output policy, and contracted procedures are the database's product; workflows never bypass the procedures.
• The application is deliberately modest in scope, complete in discipline: repositories, retry loop, error mapping, auth, keyset lists.
• Security and transactions are executed test suites: observed rejections, stated isolation behaviors.
• Operations are proven by drills: timed restores, rehearsed failures, plans with ratios.
• Documentation, deployment, and demonstration close it out — the rubric weights the evidence, and the final gate is checkability itself.

Key Terms

Review Questions and Exercises

1. State the selection test in one sentence and give one passing and one failing domain. The domain's hard part must be a data model — passes: league management (fixtures, results, standings); fails: a chat app whose state is the UI.
2. Why do requirements need "out of scope" as explicitly as features? Scope creep is the top project risk — the walls are requirements' load-bearing structure, checked at every later gate.
3. What does the Gate 2 rule-placement exercise prevent? Rules discovered during implementation with no enforcement home — the forgotten-constraint bug class, moved to the cheapest phase.
4. Which two artifacts must agree exactly at Gate 4, and what does disagreement cost? The ERD/mapping worksheet and SCHEMA.md — disagreement means the DDL will encode one of two designs, and the tests will grade the other.
5. What are the two required components of every defended redundancy? The business rule that justifies it and the control (refresh discipline) that keeps it honest — plus the acceptance paragraph.
6. Why must Gate 6 be a single command on a fresh machine? The rebuild is the project's own restore drill — any manual step is a bug in the deployment, caught at the cheapest moment.
7. Why do workflows go through procedures, never raw inserts? The choreography (validation, guards, atomicity) lives in the procedure; direct writes bypass the database's rules — the Chapter 22 rule, graded here.
8. Name the four concurrency scenarios of Phase 9 and each one's evidence. Lost-update attempt (rejection or guard), same-resource contention (stated isolation behavior), deadlock replay (retry observed), constraint races (mapped messages).
9. What makes a performance claim gradable, in one sentence? A pasted before/after EXPLAIN with measured ratio — the claim is the evidence.
10. What belongs in the honest limitations section, and why does a professional document include it? Simplifications, out-of-scope edges, and what production would add — naming the edges is how the next engineer trusts the rest.
11. Weight the rubric differently for a team project and defend the shift. Scale up the application and operations dimensions (more surface, real failover/HA work possible) and keep correctness undiminished — teams fail on coordination of exactly those phases.
12. State the capstone's final gate and connect it to Chapter 1. Checkability — every claim verifiable; it is Chapter 1's centralization-of-truth promise realized: the system holds its own answers.

Mini-Project

The capstone is the mini-project — but its final deliverable deserves its own name: the project dossier, DOSSIER.md — the single document a grader, employer, or your future self reads first: the abstract (what the system is, in five sentences); the phase-by-phase trail with each gate's evidence linked; the demo script as written prose (the fifteen minutes, narrated); the rubric self-assessment (each dimension, your evidence, your honest score); the limitations page; and the closing page — the book's arc told through your project: which chapter's discipline saved you, which one you under-used, and what you would build next. The dossier is the last thing you write in this course, and the first thing you hand anyone who asks what you can do.