Part II — Database Modeling and Design

Chapter 6. Relational Database Design

Chapter 5 produced an ER model; this chapter turns it into tables. The mapping is rules, not art: each ER construct has a standard relational realization, and applied to the university ERD the rules produce exactly the six canonical tables of Appendix H. This chapter also completes the design vocabulary that SQL will need in Chapter 9 — candidate keys, surrogate keys, foreign keys, and the referential actions (CASCADE, SET NULL, RESTRICT) that decide what happens when referenced rows disappear.

The promise of the mapping rules is the one Chapter 5 made: ER modeling is not a bureaucratic detour before writing SQL. It is a way of thinking that, followed mechanically, yields schemas that are already close to normalized — which is why Chapter 7's theory will then ratify, rather than rebuild, this chapter's output.

After studying this chapter you will be able to:

• Map entities, weak entities, and 1:1, 1:N, and M:N relationships to tables and foreign keys.
• Choose primary keys among candidate keys deliberately, weighing natural against surrogate keys.
• Decide when composite keys are right and what they cost.
• Declare foreign keys and predict referential-integrity violations.
• Translate entity, domain, and key rules into SQL constraints.
• Apply a set of schema design principles that survive real projects.
• Choose referential actions (CASCADE, SET NULL, RESTRICT, NO ACTION) with their consequences.
• Work through design case studies in retail, hospital, and library domains.

6.1 Mapping ER diagrams to relational schemas

The mapping rules, in the order you apply them:

1. Strong entity → its own table. Each attribute becomes a column; the entity's key becomes the primary key. DEPARTMENT → department; STUDENT → student; composite attributes flatten to their parts.
2. Weak entity → its own table with a composite key. The table takes the owner's key plus the discriminator as its primary key, plus a foreign key to the owner. MEETING → meeting(section_id, day, period, ...) with section_id referencing course_section.
3. 1:N relationship → a foreign key on the N side. DEPARTMENT-offers-COURSE puts dept_id in course. Total participation on the N side makes the column NOT NULL.
4. M:N relationship → a bridge (junction, associative) table. Its primary key is the pair of keys, and relationship attributes become its columns. STUDENT-enrolls-COURSE_SECTION → enrollment(student_id, section_id, grade) — the grade found its home.
5. 1:1 relationship → a foreign key on either side (choose the side with total participation, or where NULLs are more acceptable); rare; sometimes the two entities merge into one table.
6. Multivalued attribute → its own table keyed by (owner key, value): dept_phone(dept_id, phone).
7. Derived attributes → not stored (computed by views and queries, Chapter 13).
8. Specialization → one of three patterns: table per subtype (subtype tables with PK-FK to the supertype), single table with a type discriminator column, or supertype table plus subtype tables. Choose by how differently subtypes behave.

Applying rules 1, 3, and 4 to the university ERD:

DEPARTMENT, STUDENT, INSTRUCTOR, COURSE, COURSE_SECTION   → 5 tables (rule 1)
offers, majors, teaches, runs (all 1:N)                    → foreign keys
                                                          (rule 3)
enrolls (M:N, attribute grade)                             → enrollment
                                                          (rule 4)

Six tables, exactly the canonical schema. The dept_id foreign key appears in student (major), instructor (employment), and course (offering) — three 1:N relationships from one entity; course_section carries course_id and instructor_id; enrollment is the M:N resolution. When a mapping produces a table you did not draw an entity for, that is normal: bridge tables are the algebra of relationships, not things in the world.

6.2 Candidate keys, primary keys, and alternate keys

Chapter 2 defined the hierarchy: superkeys ⊇ candidate keys → primary key plus alternates. The design work is choosing, and three criteria govern the choice:

• Uniqueness that will hold. A candidate key must be unique not just today but under every future business scenario. dept_name is unique today; a renaming or a merger breaks it — which is why dept_id is primary even though dept_name keeps a UNIQUE constraint (the alternate key).
• Never NULL, rarely changing. Primary keys are referenced by foreign keys; a key that changes cascades through every referencing table. full_name fails twice: not unique and routinely changed.
• Meaningful or meaningless. Natural keys (course_id = 'CSE221') carry semantics users care about; surrogate keys (dept_id = 1..5) carry none but are stable and opaque.

The canonical schema's choices are deliberate: course_id is a natural key (department prefix + number is how people say courses); dept_id, student_id, instructor_id, section_id are surrogates (student IDs begin with the admission year — a natural flavor, but assigned by the university, not derived from stored facts). Declaring alternates as UNIQUE (dept_name, were it the case, in department) keeps both worlds: the surrogate for reference, the natural for lookup.

6.3 Composite and surrogate keys

Composite keys — keys of more than one column — arise from two places: weak entities (owner key + discriminator) and bridge tables (the two referencing keys). enrollment(student_id, section_id) is both: it is the enrollment fact's identity, and it encodes the business rule "one row per student per section" for free — a uniqueness constraint you did not have to write.

Composite keys cost more than single-column keys: every join on them compares multiple columns; every referencing foreign key must repeat all columns (course_section referencing a composite course key would have to carry it); and adding a column to the key later breaks every reference. The trade-off is classic: composite natural keys when the pair is short, stable, and meaningful; surrogate single-column keys when references will be numerous or the natural key long.

Surrogate keys are the default in industry tools — SERIAL/IDENTITY/AUTO_INCREMENT in Chapter 9, SEQUENCE in Chapter 15 — and this book's canonical schema uses them with one visible exception (course_id, enrollment's composite). Two rules keep surrogates honest: never expose meaning (the moment code branches on "IDs starting with 21 are 2021 admits," the key is natural and will bite), and keep the natural key with a UNIQUE constraint (surrogate dept_id plus UNIQUE dept_name — identity for references, semantics for users and for deduplication). A table with a surrogate key and no unique constraint on its natural identity permits silent duplicate facts — the canonical student with only dept_id-style identity could hold two rows for the same person.

6.4 Foreign keys and referential integrity

A foreign key declares that a column's values must match a key of a referenced table — referential integrity as a database guarantee rather than an application hope. The canonical schema declares seven:

student.major_dept_id       → department.dept_id
instructor.dept_id          → department.dept_id
course.dept_id              → department.dept_id
course_section.course_id    → course.course_id
course_section.instructor_id→ instructor.instructor_id
enrollment.student_id       → student.student_id
enrollment.section_id       → course_section.section_id

That is seven — every arrow on the ERD is one line of DDL. Foreign keys may also be self-referencing: an employees table's manager_id referencing its own employee_id; a course-prerequisite table referencing course twice. Both directions are enforced by the DBMS at insert, update, and delete time: inserting an enrollment for student 99999 fails; deleting a department that still owns courses fails (with the default action).

Two design decisions belong to you, not the DBMS. First, nullability: student.major_dept_id may be NULL (undeclared major allowed) while course.dept_id may not (a course must belong to a department) — the ERD's participation constraints, remembered. Second, what happens on delete and update — the referential actions of Section 6.7. Also remember the two-way street: a foreign key also constrains the referenced table (you cannot delete a referenced row), and an index on the referencing column makes the check fast (Chapter 19).

6.5 Entity, domain, and key constraints

The mapping ends by translating the data dictionary's rules into declared constraints — the theme being every rule the DBMS can enforce, the DBMS should enforce:

• Entity constraints: primary keys are unique and NOT NULL — identity guaranteed by PRIMARY KEY.
• Domain constraints: types plus CHECK rules. The canonical examples: CHECK (gpa BETWEEN 0.00 AND 4.00), CHECK (semester IN ('Fall','Spring','Summer')), CHECK (credits BETWEEN 1 AND 6), CHECK (budget >= 0), CHECK (salary > 0), and the grade scale CHECK (grade IN (...) OR grade IS NULL). A domain is a promise; the type is the weak version and the CHECK the strong version.
• Key constraints: UNIQUE for alternate and natural keys, PRIMARY KEY for identity, and composite keys for weak entities and bridges.

The payoff appears immediately in Chapter 9's DDL, where the entire Appendix H schema is exactly these constraints in SQL — and later in Chapter 20, where the same declarations are what an attacker cannot bypass by talking to a different application. Constraints also document: a newcomer reading CHECK (grade IN ('A+','A',...)) has learned the grading scale without reading a manual.

6.6 Database schema design principles

The rules so far are mechanical; these principles are judgment, and they are the ones that survive contact with real projects:

1. One fact in one place. Store each fact once and reference it — redundancy is where consistency goes to die (Chapter 1; formalized in Chapter 7).
2. Store what changes with what it describes. The room changes with the section, the grade with the enrollment, the title with the course — attribute placement follows its owner, which is what normalization formalizes.
3. Constrain as much as the DBMS allows. NOT NULL by default; CHECK for domains; UNIQUE for natural identity; FKs for references. An enforced rule cannot be forgotten by the third developer.
4. Name deliberately. lower_snake_case tables named for the entity (singular), columns named for the attribute; the same name means the same thing everywhere (student_id everywhere, never sid here and stid there) — joins write themselves when names line up.
5. Design for the queries you will run, after normalizing. First make the schema truthful (Chapter 7); then consider the workload — indexes (Chapter 19) and, consciously and rarely, denormalization (Chapter 25) adapt the physical layer without lying in the logical one.
6. Decide NULLs, never drift into them. Every nullable column should have a stated meaning of absence ("grade not yet awarded"), and nothing else.
7. Keep the data dictionary alive. The schema is documentation; comment tables and columns in the DDL (Chapter 9 shows SQL comments) and record why, not just what.
8. Evolve with migrations, not edits. Schema change is a controlled, scripted, versioned process (Chapter 24), never a live ALTER TABLE on a hunch.

6.7 Referential actions: CASCADE, SET NULL, and RESTRICT

What should happen to enrollments when a student is deleted? The foreign key declaration answers with an action, for ON DELETE and ON UPDATE separately:

• RESTRICT / NO ACTION (the default): refuse the delete while references exist. Deleting student 21100001 fails while three enrollment rows cite them.
• CASCADE: propagate the change — delete the referencing rows too (or update their keys on key change).
• SET NULL: make the referencing column NULL (allowed only where the column is nullable).
• SET DEFAULT: restore the column's default value (rare; must still satisfy the FK).

Choosing is a business decision about the meaning of references:

-- Enrollments exist only as facts about a student:
CREATE TABLE enrollment (
    student_id  INTEGER NOT NULL REFERENCES student(student_id)
                                ON DELETE CASCADE,
    section_id  INTEGER NOT NULL REFERENCES course_section(section_id)
                                ON DELETE RESTRICT,
    grade       CHAR(2),
    PRIMARY KEY (student_id, section_id)
);

Deleting a student wipes their enrollments (an audit-sensitive choice — see below); deleting a section that has enrollments is refused, forcing the registrar to resolve grades first. A defensible alternative pairs CASCADE with an archive policy: financial and medical records are usually RESTRICT plus explicit archival, precisely because "cascade away the evidence" is a phrase no auditor enjoys. Heuristic: CASCADE down ownership lines (a section's meetings vanish with the section), RESTRICT across audit lines (payments, grades, orders), and SET NULL where the reference is optional (course_section.instructor_id could set NULL on instructor deletion — "unstaffed again" — if staffing is pending).

6.8 Database design case studies

Three worked mini-designs exercise the rules; Chapter 29 develops eight complete ones.

Retail orders. Entities: CUSTOMER, PRODUCT, ORDER, plus ORDER_LINE. Mapping: ORDER_LINE is the M:N bridge (ORDER–PRODUCT) with attributes quantity, unit_price — note the design point: unit_price is stored on the line, deliberately, because the catalog price changes and the historical order must not (a controlled, justified redundancy). ORDER references CUSTOMER (1:N); ORDER_LINE cascades with its ORDER (ownership line).

Hospital visits. Entities: PATIENT, DOCTOR (both PERSON subtypes in a fuller model), VISIT, and PRESCRIPTION — the last a weak-ish entity keyed by its own surrogate, referencing VISIT and DRUG. The audit principle says PRESCRIPTION→VISIT is RESTRICT, not CASCADE: deleting a visit must never silently erase prescriptions.

Library loans. The classic composite-key showcase: BOOK (natural key ISBN — a rare good natural key), COPY (weak under BOOK: key = (book_id, copy_no)), MEMBER, LOAN (bridge MEMBER–COPY with due/return dates; key = (member_id, copy_id) or surrogate). The overdue report is one join across all four.

Each case shows the same shape: entities and 1:N relationships map to tables and foreign keys; every M:N becomes a bridge table whose attributes find their true home; every weak entity becomes a composite key; and the referential actions encode the business's memory policy.

Chapter Summary

• ER-to-relational mapping is rules: strong entities to tables, weak entities to composite-key tables, 1:N to foreign keys on the N side, M:N to bridge tables, multivalued attributes to tables, derived attributes to nothing.
• Applied to the university ERD, the rules produce the six canonical tables with seven foreign keys.
• Primary keys are chosen among candidates for stability and non-nullness; alternates stay as UNIQUE; surrogates pair with UNIQUE natural keys.
• Composite keys arise from weak entities and bridges; they encode uniqueness for free but cost joins and future change.
• Entity, domain, and key rules become PRIMARY KEY, NOT NULL, CHECK, UNIQUE, and FOREIGN KEY declarations.
• Design principles: one fact one place, attributes with their owners, maximal constraint enforcement, consistent naming, NULL decisions made explicitly, living documentation, migrations not edits.
• Referential actions are business policy: CASCADE on ownership, RESTRICT on audit, SET NULL on optional references.
• Case studies (retail, hospital, library) replay the rules until they are reflexes.

Key Terms

Laboratory Exercises

1. Map your Chapter 5 mini-project EERD (prerequisites, meetings, waitlist, TAs) to tables: list every table, its primary key, its foreign keys with nullability, and the referential action you would choose for each FK, with a one-line reason. Expected result: prerequisites(course_id, prereq_course_id, min_grade) with composite PK and two self-table FKs; meeting(section_id, day, start_period, end_period) weak-keyed, ON DELETE CASCADE; waitlist(student_id, section_id, position, entered_at), PK (student, section), FK cascade with section, restrict with student.
2. In the canonical university database, try DELETE FROM department WHERE dept_id = 1; and record the error. Explain the failure from the FKs of Section 6.4. Expected result: foreign-key violation — student, instructor, and course rows reference dept 1.
3. Add a dept_phone table for multivalued department phones, with sample rows, and query it for department 1. Expected result: dept_phone(dept_id, phone) with PK (dept_id, phone) and FK to department.
4. Design the hospital PRESCRIPTION→VISIT reference twice: once CASCADE, once RESTRICT. Write one paragraph arguing which the audit office requires. Expected result: RESTRICT (prescriptions are legal records; deleting a visit must not erase them); a maintained audit argument accepted.
5. Demonstrate the natural-key failure mode: temporarily drop the UNIQUE on department.dept_name (or in a scratch table), insert two 'Computer Science and Engineering' rows, and show the duplicate-fact query result. Expected result: two indistinguishable rows; restore the UNIQUE constraint afterwards.
6. Map the retail case of Section 6.8 into a full DDL sketch (paper or SQL) with all keys and FKs, and state which two facts are deliberately redundant and why. Expected result: order_line stores unit_price (price at sale time) — the classic justified redundancy; possibly denormalized customer_name for report history, argued either way.

Review Questions and Exercises

1. State the mapping rule for each: 1:N relationship; M:N relationship; weak entity; multivalued attribute. FK on the N side; bridge table with composite key; table with (owner key + discriminator) PK and FK to owner; own table keyed by (owner key, value).
2. Why does the bridge table for STUDENT–COURSE_SECTION carry the grade, and what does its composite key guarantee? The grade belongs to the pair; (student_id, section_id) uniqueness encodes "one enrollment per student per section."
3. Give two reasons dept_name is a poor primary key but a good alternate key. It changes with renaming and can collide in mergers (unstable), but it is unique today and semantically useful for lookups — hence UNIQUE, not PK.
4. What two rules keep surrogate keys honest, and what silent failure does breaking the second cause? Never expose meaning; always keep a UNIQUE natural key. Without it, duplicate real-world facts are indistinguishable.
5. Explain the difference between RESTRICT and CASCADE for ON DELETE, with one enrollment example of each's meaning. RESTRICT — the delete is refused while children exist (delete a referenced section); CASCADE — children are deleted with the parent (delete a student, their enrollments vanish).
6. course_section.instructor_id is nullable. Which referential action is available to it that is unavailable to enrollment.student_id, and what would it mean? SET NULL — deleting an instructor could mark their sections unstaffed; enrollment.student_id is NOT NULL, so SET NULL is not usable there.
7. Why is unit_price stored on ORDER_LINE rather than referenced from PRODUCT — isn't that redundancy? Controlled, justified redundancy: the order must record the historical price; the catalog price changes and must not rewrite history (temporal correctness beats non-redundancy).
8. A table has surrogate PK id and no UNIQUE constraint. Name the failure and the fix. Duplicate natural facts are insertable undetected; add a UNIQUE constraint on the natural identity columns.
9. Which mapping rule handles a specialization, and what drives the choice among its three options? Rule 8: table per subtype / single table with type column / supertype+subtype tables; driven by how differently subtypes behave (attributes and relationships).
10. Write the DDL for the weak-entity MEETING table of Laboratory 1.

    CREATE TABLE meeting (
        section_id   INTEGER     NOT NULL REFERENCES course_section(section_id)
                                  ON DELETE CASCADE,
        day          VARCHAR(9)  NOT NULL CHECK (day IN
                                  ('Mon','Tue','Wed','Thu','Fri','Sat','Sun')),
        start_period SMALLINT    NOT NULL CHECK (start_period BETWEEN 1 AND 8),
        end_period   SMALLINT    NOT NULL CHECK (end_period BETWEEN 1 AND 8),
        PRIMARY KEY (section_id, day, start_period),
        CHECK (end_period >= start_period)
    );

11. Why does the hospital case use RESTRICT for PRESCRIPTION→VISIT while the retail case cascades ORDER→ORDER_LINE? Prescriptions are audit/legal records that must survive their context's deletion; order lines are meaningless without their order — ownership line vs. audit line.
12. Give the participation-to-nullability rule and check it against two canonical FKs. Total participation on the FK side → NOT NULL; partial → nullable. course.dept_id is NOT NULL (a course must belong to a department); student.major_dept_id is nullable (undeclared majors allowed).

Mini-Project

Take the movie-collection model from Chapter 2's mini-project — or redesign it if you skipped that one — and carry it through this chapter end to end. Deliver: (1) the EERD, redrawn with everything you have learned since; (2) the full relational mapping: every table with columns, types, NOT NULL decisions with stated absence meanings, candidate keys, the chosen primary key with a two-line justification, alternate keys as UNIQUE, foreign keys with referential actions argued in one line each; (3) a DDL sketch that a partner could type without asking you a single question; and (4) a "break my schema" section — five illegal inserts or deletes, each predicted to fail with the constraint that will catch it. Trade with a classmate and let them try the five breaks on your schema and you on theirs. The design carries into Chapter 7, where normalization will grade it.