Schemas and Constraints

Overview

---

In this part, we turn the conceptual design from the previous part into a concrete relational schema. The entities, attributes, and relationships from the ER diagram become tables, columns, keys, and constraints. The design rationale becomes the justification for the SQL choices. The expected queries become a checklist for verifying that the schema actually works.

While the previous part answered the question “what should the database remember?”, this part answers the question “how should that design appear as real database structure that an application can use?”

As in earlier parts, the course uses two recurring contexts. The course-platform example continues as the worked concept context. The study tracker project continues as the long-running project track. Between them, this part also brings back the recipe app and library cases from the previous part, so that schema implementation gets practiced on more than one domain.

The structure of this part is as follows:

• Planning the Schema from the Design Package turns the design package from the previous part into a concrete table plan before any SQL is written.
• Creating Tables with SQL introduces CREATE TABLE and the main data types used in the course.
• Constraints and Integrity Rules explains how the database itself can enforce design decisions through primary keys, foreign keys, and other constraints.
• Practicing Schema Implementation works through full DDL for the recipe app and library cases.
• Verifying the Schema walks the new schema through its expected queries and test inserts.
• Inserting, Updating, and Deleting Under Constraints revisits write operations in a schema that now has relationships and rules.
• Migrations and Seed Data connects schema work to reproducible setup in the project scaffold.
• Evolving a Schema Safely shows how to change a schema without breaking the data or the application.
• Project Checkpoint, Step 1: Designing Card Reviews walks one small feature through subject analysis, ER diagram extension, and migration writing.
• Project Checkpoint, Step 2: Building the Card Review Page implements the feature end-to-end, ending with the project zip as the part’s deliverable.

The last two chapters are the part’s project checkpoint, split into two steps: the first ends with a migration file, the second ends with the working application.

Like the previous part, this one treats design and implementation as one continuous arc, not two parallel treatments.

Recommended Order of Work

A useful rhythm for this part is:

1. read the regular chapters in order,
2. complete the chapter-level exercises as you go,
3. complete both steps of the project checkpoint,
4. and finish with the recap quiz and feedback form.

Finally, at the end of the part, there is a recap and feedback chapter that briefly summarizes the part and asks for feedback.