Chapter 03 · Keys, Relationships, and Integrity
Designing a Small Relational Schema from Requirements
Apply the complete Chapter 3 method to turn ambiguous requirements into a coherent, testable relational schema.
Learning outcomes
Schema design begins before CREATE TABLE. Requirements must be decomposed into occurrences, identifiers, facts, relationships, optionality, lifecycle rules, and queries. This workshop demonstrates that process for a small course-delivery system.
Extract candidate entities, attributes, keys, relationships, and constraints from written requirements.
Separate entities from labels, repeated groups, and relationship attributes.
Create a coherent SQLite schema with primary, alternate, foreign, and composite keys.
Validate the design using seed data, integrity checks, and representative queries.
Requirements for the workshop
The academy publishes reusable courses.Each course has a stable course code and title.A course can be offered many times in different terms.Each offering belongs to exactly one course and has one instructor.An instructor can teach many offerings.Learners register with a unique email address.A learner may enroll in many offerings.An offering may contain many learners.A learner can enroll in the same offering only once.Enrollment records the enrollment date and current status.Each offering has a positive capacity.Enrollment status is active, completed, or withdrawn.Before writing SQL, underline nouns, identifiers, relationship verbs, quantities, and words such as each, may, exactly one, and only once.
Step 1: identify entity occurrences
| Candidate | Why it is an entity or association |
|---|---|
| Course | A reusable definition with its own stable code and title |
| Offering | A scheduled occurrence of one course in a term |
| Instructor | An independently identified person who can teach several offerings |
| Learner | An independently identified participant with a unique email |
| Enrollment | An association occurrence between learner and offering with its own date and status |
Term could become its own entity in a larger system, but the current requirements need only a validated term code. Avoid creating entities without a present identity, lifecycle, or relationship need.
Step 2: choose keys
| Table | Primary key | Alternate or composite uniqueness |
|---|---|---|
| course | course_id surrogate | course_code natural alternate key |
| instructor | instructor_id surrogate | email alternate key |
| learner | learner_id surrogate | email alternate key |
| offering | offering_id surrogate | (course_id, term_code, section_no) alternate key |
| enrollment | (learner_id, offering_id) composite | The pair implements “only once” |
The design keeps internal references compact while preserving domain uniqueness. The offering’s alternate key prevents duplicate sections of the same course in the same term.
Step 3: map relationships and optionality
Foreign keys implement the one-to-many edges; enrollment resolves the many-to-many edge.
| Requirement | Relational implementation |
|---|---|
| Offering belongs to exactly one course | offering.course_id NOT NULL foreign key |
| Offering has one instructor | offering.instructor_id NOT NULL foreign key |
| Instructor teaches many offerings | Many offering rows may repeat one instructor key |
| Learner enrolls in many offerings | Enrollment junction stores learner and offering foreign keys |
| Same learner only once per offering | Composite primary key on enrollment |
| Course may exist before being offered | No child offering row is required by the course table |
Step 4: define row-local business rules
- course codes and emails are nonblank and unique;
- section numbers and capacity are positive;
- enrollment status belongs to a controlled set;
- the enrollment date is required;
- foreign keys prevent unknown courses, instructors, learners, and offerings;
- deleting an offering removes its enrollment rows, but deleting a course or instructor with scheduled offerings is restricted.
The requirement “active enrollment count must not exceed capacity” is a cross-row aggregate rule. We will detect it with a query and discuss enforcement rather than pretend a row-local CHECK can solve it.
Step 5: create the schema
PRAGMA foreign_keys = ON;DROP TABLE IF EXISTS enrollment;DROP TABLE IF EXISTS offering;DROP TABLE IF EXISTS learner;DROP TABLE IF EXISTS instructor;DROP TABLE IF EXISTS course;CREATE TABLE course ( course_id INTEGER PRIMARY KEY, course_code TEXT NOT NULL UNIQUE CHECK (length(trim(course_code)) BETWEEN 3 AND 20), title TEXT NOT NULL CHECK (length(trim(title)) > 0)) STRICT;CREATE TABLE instructor ( instructor_id INTEGER PRIMARY KEY, email TEXT NOT NULL UNIQUE CHECK (length(trim(email)) > 3), full_name TEXT NOT NULL CHECK (length(trim(full_name)) > 0)) STRICT;CREATE TABLE learner ( learner_id INTEGER PRIMARY KEY, email TEXT NOT NULL UNIQUE CHECK (length(trim(email)) > 3), full_name TEXT NOT NULL CHECK (length(trim(full_name)) > 0)) STRICT;CREATE TABLE offering ( offering_id INTEGER PRIMARY KEY, course_id INTEGER NOT NULL, instructor_id INTEGER NOT NULL, term_code TEXT NOT NULL, section_no INTEGER NOT NULL CHECK (section_no > 0), capacity INTEGER NOT NULL CHECK (capacity > 0), starts_on TEXT NOT NULL, UNIQUE (course_id, term_code, section_no), FOREIGN KEY (course_id) REFERENCES course (course_id) ON DELETE RESTRICT, FOREIGN KEY (instructor_id) REFERENCES instructor (instructor_id) ON DELETE RESTRICT) STRICT;CREATE TABLE enrollment ( learner_id INTEGER NOT NULL, offering_id INTEGER NOT NULL, enrolled_at TEXT NOT NULL, status TEXT NOT NULL DEFAULT 'active' CHECK (status IN ('active', 'completed', 'withdrawn')), PRIMARY KEY (learner_id, offering_id), FOREIGN KEY (learner_id) REFERENCES learner (learner_id) ON DELETE CASCADE, FOREIGN KEY (offering_id) REFERENCES offering (offering_id) ON DELETE CASCADE) WITHOUT ROWID;CREATE INDEX idx_offering_course ON offering (course_id);CREATE INDEX idx_offering_instructor ON offering (instructor_id);CREATE INDEX idx_enrollment_offering_status ON enrollment (offering_id, status);The schema is ordered so referenced parent tables exist before child tables. Drop order is reversed for the same dependency reason.
Step 6: seed representative data
INSERT INTO course (course_id, course_code, title)VALUES (1, 'SQL-101', 'SQL and Database Fundamentals'), (2, 'DM-201', 'Data Modeling and Database Design');INSERT INTO instructor (instructor_id, email, full_name)VALUES (10, 'ava@example.com', 'Ava Morgan'), (11, 'reza@example.com', 'Reza Farahani');INSERT INTO learner (learner_id, email, full_name)VALUES (100, 'nadia@example.com', 'Nadia Rahimi'), (101, 'omar@example.com', 'Omar Haddad'), (102, 'lina@example.com', 'Lina Chen');INSERT INTO offering (offering_id, course_id, instructor_id, term_code, section_no, capacity, starts_on)VALUES (1000, 1, 10, '2026-FALL', 1, 2, '2026-09-01'), (1001, 2, 11, '2026-FALL', 1, 30, '2026-10-01');INSERT INTO enrollment (learner_id, offering_id, enrolled_at, status)VALUES (100, 1000, '2026-08-05', 'active'), (101, 1000, '2026-08-05', 'active'), (102, 1001, '2026-08-06', 'active');The first offering is intentionally at capacity so the validation query has a visible boundary case.
Step 7: validate structure and meaning
PRAGMA integrity_check;PRAGMA foreign_key_check;SELECT o.offering_id, c.course_code, c.title, i.full_name AS instructor, o.term_code, o.section_no, o.capacity, COUNT(CASE WHEN e.status = 'active' THEN 1 END) AS active_countFROM offering AS oJOIN course AS c ON c.course_id = o.course_idJOIN instructor AS i ON i.instructor_id = o.instructor_idLEFT JOIN enrollment AS e ON e.offering_id = o.offering_idGROUP BY o.offering_id, c.course_code, c.title, i.full_name, o.term_code, o.section_no, o.capacityORDER BY o.offering_id;SELECT o.offering_id, o.capacity, COUNT(*) AS active_enrollmentsFROM offering AS oJOIN enrollment AS e ON e.offering_id = o.offering_idWHERE e.status = 'active'GROUP BY o.offering_id, o.capacityHAVING COUNT(*) > o.capacity;An empty result means no current violation. Preventing two concurrent transactions from exceeding capacity requires a transaction strategy, trigger, or controlled enrollment service. The schema still provides the identities and references that such logic depends on.
Step 8: test negative cases
-- Duplicate course business key.INSERT INTO course (course_code, title)VALUES ('SQL-101', 'Duplicate SQL Course');-- Unknown instructor.INSERT INTO offering (course_id, instructor_id, term_code, section_no, capacity, starts_on)VALUES (1, 999, '2026-FALL', 2, 25, '2026-09-02');-- Duplicate learner-offering association.INSERT INTO enrollment (learner_id, offering_id, enrolled_at, status)VALUES (100, 1000, '2026-08-07', 'active');-- Invalid status domain.INSERT INTO enrollment (learner_id, offering_id, enrolled_at, status)VALUES (102, 1000, '2026-08-07', 'pending');Each failure should map directly to a named requirement. If a test fails for an unrelated reason, improve the fixture or constraint design until the evidence is clear.
Design review checklist
Before accepting a schema
- Does every table have a precise row meaning and a stable primary key?
- Are all natural candidate keys still enforced as UNIQUE and, when required, NOT NULL?
- Do foreign keys represent every stored reference, with deliberate delete and update actions?
- Are one-to-one relationships actually unique and many-to-many relationships represented by junction tables?
- Do types, nullability, checks, and defaults preserve the intended domains?
- Which rules remain cross-row, cross-table, temporal, or external, and where will they be enforced?
- Can representative valid data be inserted and every important invalid case be rejected?
- Do names and constraints communicate the design to a reader without relying on application code?
Common design mistakes
Turning every noun into a table
Tables represent independently meaningful occurrences or associations, not every word in a requirement.
Starting from screens instead of facts
A form layout reflects one workflow. The schema must support all legitimate writers and queries.
Storing comma-separated foreign keys
Repeated identifiers inside text bypass referential integrity and make joins, updates, and uniqueness difficult. Use child or junction rows.
Encoding current calculations as mutable facts
Counts and totals derived from detailed rows can drift. Store them only when the performance and consistency strategy is explicit.
Chapter 3 summary
You can now move from requirements to relational identity and integrity:
- candidate keys identify every minimal uniqueness rule;
- primary and alternate keys preserve row identity and business uniqueness;
- foreign keys enforce valid references and lifecycle behavior;
- cardinality determines foreign-key placement and junction tables;
- constraints protect entity, domain, referential, and row-local business integrity;
- representative positive and negative tests verify the schema contract.
Chapter 4 begins practical data retrieval with SELECT, result sets, aliases, expressions, and readable query conventions.