Module 1: Relational Databases & SQL: Guided Labs

Use PostgreSQL or another relational database that exposes constraints, transactions, and query plans. Save schema, seed data, queries, plans, and conclusions in the semester repository.

Lab 1: Model and Constrain an Order Domain

Start from a deliberately denormalized order spreadsheet. Identify functional dependencies, decompose it to at least third normal form, and implement primary, foreign, unique, check, and nullability constraints. Seed one valid and five invalid cases. Record which layer rejects each invalid case and why.

Evidence: ER diagram, migration, seed script, and captured constraint failures.

Lab 2: Query Semantics Under Adversarial Data

Write reports using inner/outer joins, grouping, a window function, and a correlated or CTE-based subquery. Seed duplicates, missing children, tied ranks, and NULLs. Predict results before execution, then reconcile every mismatch.

Evidence: query file plus a prediction/actual table explaining at least three surprises.

Lab 3: Plan-Guided Indexing

Generate enough skewed data for plan choices to matter. Capture EXPLAIN (ANALYZE, BUFFERS) for three representative queries before indexing. Add only indexes justified by equality/range/order predicates, rerun, and compare rows estimated versus actual, buffers, sort operations, and elapsed time.

Evidence: before/after plans and one rejected index proposal.

Lab 4: Transaction Boundary Review

Implement one multi-statement business operation. Inject failure after each statement and determine which partial states survive with and without a transaction. Add the minimum transaction boundary and constraints needed to preserve the invariant.

Exit check: another learner can recreate the database and reproduce every claim from scripts.