Executable relational contracts

This required 22–32-hour package makes the existing schema, adversarial-query and transaction labs executable. Allocate 6–8 hours to lessons and predictions, 10–16 to implementation and experiments, and 6–8 to assessment and repair. Count this as the implementation core of guided Labs 1, 2 and 4, not a second copy of them. Their domain modelling and written analysis remain required; Lab 3 PostgreSQL plans, normalization, migration and multi-session work also remain required. see the reconciled semester workload. Estimates await learner pilots.

Prerequisite bridge

From Semester 05, distinguish decoded request, durable state and client-observed response. Here the first question is narrower: what database rows are legal, what exact rows should a query return, and which statement effects belong together? Review sets versus multisets, predicates, integer units and independent test oracles. A successful rollback experiment does not settle crash durability or concurrent isolation.

Learning sequence

1. Multiplicity and missing rows: predict outputs before query execution.
2. Nulls and enforced invariants: separate unknown values, guest relationships and invalid records.
3. Transaction ownership and failure cuts: preserve committed state while rejecting partial new operations.
4. Prediction practice, then guided labs and assessment.

Runnable contract

Download relational-contracts.zip. Python's standard-library SQLite binding is sufficient; SQLite 3.25 or later is required for window functions. The README defines each result shape, tie rule and transaction boundary. Implement starter.py before consulting reference.py. Run python3 -B check.py starter; use python3 -B check.py reference only to calibrate the environment or after the independent attempt.

The checker creates fresh in-memory databases and compares results with a Python list oracle over 256 datasets. It exercises foreign-key enforcement, nullability, integer cents, invalid status, duplicate keys, two failure cuts and successful commit. These are scoped checks, not exhaustive SQL correctness. Add three cases of your own and explain why each distinguishes a plausible incorrect implementation.

Evidence and limits

Keep schema, functional dependencies, predicted rows, queries, observed rows, failure cuts and corrected misconceptions. Record Python and SQLite versions. Translate the important claims to the module's PostgreSQL environment and record dialect differences, especially typing and constraint setup. SQLite passing results cannot replace PostgreSQL execution plans or two-session observations. Keep actual plans and timing separate from semantic correctness; no speedup is promised by this lab.