Case Study 1 — Turning the Registration Model into a Schema

A direct continuation of Chapter 17's course-registration ER model. We apply the mapping rules in order and watch a correct, complete schema fall out — almost mechanically. This is the payoff of modeling first: the DDL writes itself.

The model (from Chapter 17)

Recall the entities and relationships:

  • Entities: student, course, section, instructor, room, term, enrollment, prerequisite
  • 1:N: course→section, instructor→section, room→section, term→section
  • M:N: student↔section (carrying grade), course↔course (prerequisites, self-referencing)

Applying the rules

Rule 1 (entities → tables): each strong entity becomes a table with a surrogate PK.

CREATE TABLE students (
    student_id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name       text NOT NULL,
    email      text NOT NULL UNIQUE,
    major      text
);
CREATE TABLE courses (
    course_id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    code      text NOT NULL UNIQUE,           -- 'CS 200' (natural key, protected)
    title     text NOT NULL,
    credits   integer NOT NULL CHECK (credits > 0)
);
CREATE TABLE instructors (
    instructor_id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name text NOT NULL, department text NOT NULL
);
CREATE TABLE rooms (
    room_id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    building text NOT NULL, number text NOT NULL, seats integer NOT NULL CHECK (seats > 0),
    UNIQUE (building, number)
);
CREATE TABLE terms (
    term_id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name text NOT NULL, start_date date NOT NULL, end_date date NOT NULL,
    CHECK (end_date > start_date)
);

Rule 4 (1:N → FK on the many side): section is the "many" for course, instructor, room, and term — so it carries four foreign keys. Participation is total (a section must have all four), so they're NOT NULL.

CREATE TABLE sections (
    section_id    integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    course_id     integer NOT NULL REFERENCES courses (course_id),
    instructor_id integer NOT NULL REFERENCES instructors (instructor_id),
    room_id       integer NOT NULL REFERENCES rooms (room_id),
    term_id       integer NOT NULL REFERENCES terms (term_id),
    schedule      text NOT NULL,
    capacity      integer NOT NULL CHECK (capacity > 0)
);

Rule 5 (M:N → junction with relationship attributes): student↔section becomes enrollments, and grade (which describes the pairing) lives there. Composite PK prevents enrolling the same student in the same section twice.

CREATE TABLE enrollments (
    student_id  integer NOT NULL REFERENCES students (student_id) ON DELETE CASCADE,
    section_id  integer NOT NULL REFERENCES sections (section_id) ON DELETE CASCADE,
    enrolled_at timestamptz NOT NULL DEFAULT now(),
    grade       text CHECK (grade IN ('A','B','C','D','F','W','I')),   -- nullable: ungraded yet
    PRIMARY KEY (student_id, section_id)
);

Self-referencing M:N (prerequisites): course↔course becomes a junction referencing courses twice, with a CHECK to forbid a course being its own prerequisite.

CREATE TABLE prerequisites (
    course_id        integer NOT NULL REFERENCES courses (course_id) ON DELETE CASCADE,
    prereq_course_id integer NOT NULL REFERENCES courses (course_id),
    PRIMARY KEY (course_id, prereq_course_id),
    CHECK (course_id <> prereq_course_id)
);

The result

Every table, key, and foreign key traces directly to a rule applied to the model. And the schema supports every query the registrar needs, cleanly:

  • A student's transcript: join enrollments → sections → courses for one student, with grades.
  • A section's roster: join enrollments → students for one section.
  • Is a section full? COUNT(*) of enrollments vs. sections.capacity.
  • Course prerequisites (to any depth): a recursive CTE over prerequisites (Chapter 11).
  • An instructor's teaching load: count sections per instructor per term.

None of these require contortions, because the model was right and the mapping was faithful.

The analysis

  1. Faithful mapping = a schema that fits the questions. Because every relationship was mapped by its rule (FK on the many side, junction for M:N, attributes on the junction), the queries the registrar needs are natural joins. A good model maps to a good schema maps to easy SQL — theme #1 paying off across three chapters.

  2. Relationship attributes find their home automatically. grade "wanted" to be on student or section, but the rules put it on enrollments — exactly right, because a grade exists only for a specific student-in-a-section.

  3. Constraints come along for free. Mapping surfaced natural constraints: UNIQUE(code), CHECK(credits > 0), CHECK(end_date > start_date), the prerequisite self-reference guard. Each is a bug that can now never happen.

  4. The composite PK on junctions enforces real rules. PRIMARY KEY (student_id, section_id) means "a student can't enroll in the same section twice" — a business rule enforced by structure, not application code.

  5. Mechanical, not creative. Once the model is right, the DDL is rule-application, not invention. That's the point of this chapter: design is where judgment lives; mapping is where discipline lives.

Discussion questions

  1. For each relationship in the model, name the rule applied and where the FK/junction landed.
  2. Why is grade on enrollments rather than on students or sections?
  3. Why is enrollments.grade nullable but its FKs NOT NULL?
  4. What rule does PRIMARY KEY (student_id, section_id) enforce, in business terms?
  5. ⭐ Add "a section can be team-taught by several instructors." Which table/relationship changes, and what new junction appears? Write its DDL.