The Project | About Us | Contribute | Donations | License

HOME

In the previous lesson you were querying a schema that already existed. Here there is no schema: there is a client telling you what they need, in the disorder with which clients tell it, and your job is to turn that story into tables, keys and constraints that cannot be broken.

How to work through this lesson. Each exercise is a complete case, not a one-minute question. Read the whole task, take paper —or a text editor— and do the four steps on your own before looking at anything:

Identify entities and relationships: underline the nouns in the task, decide which ones are entities and which are attributes, and note the cardinality of each relationship.
Draw the ER diagram with crow's foot notation (you can use mermaid, paper or any tool).
Write the CREATE TABLE applying the ten transformation rules from lesson 04-03, with their foreign keys and their ON DELETE / ON UPDATE actions.
Add the constraints —NOT NULL, UNIQUE, CHECK, DEFAULT, domains, EXCLUDE— that encode the business rules of the task.

Only afterwards compare with the proposed solution. It is normal for your schema not to be identical: in design there is almost never a single correct answer, but rather defensible answers and answers that break a requirement. That is why each solution ends with a section on debatable decisions, explaining which alternatives would also be valid and what is gained and lost with each one. And at the end of the lesson there is a self-assessment rubric you can use to score your own design.

In this lesson we do not do formal analysis of functional dependencies and we do not cite normal forms: that is exactly the subject matter of 07-03. Here we design well from the start; there we diagnose and fix designs that have already gone wrong.

Before You Begin

You do not need any dataset loaded: four of the five cases are new domains, and the fifth builds on the BiblioRed schema you already know. What you do need at hand is:

The ten ER → relational transformation rules (lesson 04-03), especially the ones for N:M relationships, weak entities, generalization hierarchies and ternary relationships.
The constraint catalog from lesson 04-04: CHECK, UNIQUE over several columns, partial UNIQUE by means of an index, domains (CREATE DOMAIN), generated columns and EXCLUDE with btree_gist.
A psql session to run your CREATE TABLEs. Do not accept as good a schema you have not executed: half of the design mistakes are caught by the engine itself when creating the tables.

For exercise 2 it helps to keep the BiblioRed schema in mind, in particular branches, materials, copies, members and loans.

If you are going to use EXCLUDE constraints, enable the extension once per database:

CREATE EXTENSION IF NOT EXISTS btree_gist;

SQLite. It supports neither CREATE DOMAIN, nor EXCLUDE, nor ALTER TABLE ADD CONSTRAINT, and it only enforces foreign keys if you turn on PRAGMA foreign_keys = ON. CHECKs do work. Wherever a solution uses something exclusive to PostgreSQL, the portable alternative is pointed out.

Exercise 1 — Basic: neighborhood video rental store
Exercise 2 — Intermediate: interlibrary loan in BiblioRed
Exercise 3 — Intermediate: online course platform
Exercise 4 — Advanced: auto repair shop with a hierarchy and a ternary relationship
Exercise 5 — Advanced: fares with time-bounded validity
Common mistakes and tips
Reinforcement exercises
Self-assessment rubric

Exercise 1: Neighborhood video rental store

Difficulty: Basic

Task. "Cinema Vallmar" is a video rental store that survives by renting out movies on physical media. Its owner tells you the following:

"I have about 3,000 movies. For each one I keep the title, the year, the running time in minutes and the age rating. Each movie belongs to a genre —drama, comedy, documentary...— although there are some that belong to two, and I would like to be able to search for them under both. Of each movie I have between one and six physical copies; each copy has a label stuck on it with a code, a format (DVD or Blu-ray) and a status, because some are scratched and I do not rent those out. Customers become members with a name, a phone number and an email; the email cannot be repeated. When somebody rents a copy I write down the date, the expected return date and, when they bring it back, the real one. The same member can have several copies rented at once, but a copy can only be rented to one person. I would also like to be able to search by actor: each movie has several actors and each actor appears in several movies, and I care about knowing which character they played."

He needs to be able to answer: which copies of a movie are available right now, what a member has out on rental, which movies there are of a genre, which movies an actor has worked on and which rentals are overdue.

Hint. There are two N:M relationships in the task, and one of them has an attribute of its own.

Solution

Step 1 — Entities and relationships

Entity	Justification
`movies`	It has attributes of its own and is referenced from several places
`genres`	A genre is an entity, not free text: you have to search by it
`actors`	It has an identity of its own and repeats across movies
`copies`	The physical object that is rented; it is not the same as the movie
`members`	Customers
`rentals`	The fact of a copy leaving the store

Relationships:

movies N:M genres → junction table movies_genres.
movies N:M actors, with attribute character_name → junction table cast_members.
movies 1:N copies (a movie has between 1 and 6 copies).
members 1:N rentals, copies 1:N rentals.

The key distinction in this case is movie versus copy. The movie is the work; the copy is the plastic disc with a label. The member does not rent "Casablanca": they rent copy CV-0412. Confusing the two is the most frequent design mistake in this domain, and it is exactly the same distinction there is in BiblioRed between materials and copies.

Step 2 — ER diagram

erDiagram
    MOVIES ||--o{ COPIES : "exists as"
    MOVIES ||--o{ MOVIES_GENRES : ""
    GENRES ||--o{ MOVIES_GENRES : ""
    MOVIES ||--o{ CAST_MEMBERS : ""
    ACTORS ||--o{ CAST_MEMBERS : ""
    COPIES ||--o{ RENTALS : "is rented in"
    MEMBERS ||--o{ RENTALS : "makes"

    MOVIES {
        int  movie_id PK
        text title
        int  year_
        int  duration_min
        text rating
    }
    GENRES {
        int  genre_id PK
        text name UK
    }
    MOVIES_GENRES {
        int movie_id PK_FK
        int genre_id PK_FK
    }
    ACTORS {
        int  actor_id PK
        text first_name
        text last_name
    }
    CAST_MEMBERS {
        int  movie_id PK_FK
        int  actor_id PK_FK
        text character_name PK
    }
    COPIES {
        int  copy_id PK
        text code UK
        int  movie_id FK
        text format
        text status
    }
    MEMBERS {
        int  member_id PK
        text name
        text email UK
        text phone
        bool active
    }
    RENTALS {
        int  rental_id PK
        int  copy_id FK
        int  member_id FK
        date rental_date
        date due_date
        date return_date
    }

Step 3 — CREATE TABLE

CREATE TABLE genres (
    genre_id SERIAL PRIMARY KEY,
    name     VARCHAR(40) NOT NULL UNIQUE
);

CREATE TABLE movies (
    movie_id     SERIAL PRIMARY KEY,
    title        VARCHAR(200) NOT NULL,
    year_        SMALLINT     NOT NULL,
    duration_min SMALLINT     NOT NULL,
    rating       VARCHAR(5)   NOT NULL
);

CREATE TABLE movies_genres (
    movie_id INTEGER NOT NULL REFERENCES movies(movie_id) ON DELETE CASCADE,
    genre_id INTEGER NOT NULL REFERENCES genres(genre_id) ON DELETE RESTRICT,
    PRIMARY KEY (movie_id, genre_id)
);

CREATE TABLE actors (
    actor_id   SERIAL PRIMARY KEY,
    first_name VARCHAR(60) NOT NULL,
    last_name  VARCHAR(80) NOT NULL
);

CREATE TABLE cast_members (
    movie_id       INTEGER NOT NULL REFERENCES movies(movie_id) ON DELETE CASCADE,
    actor_id       INTEGER NOT NULL REFERENCES actors(actor_id) ON DELETE RESTRICT,
    character_name VARCHAR(80) NOT NULL,
    PRIMARY KEY (movie_id, actor_id, character_name)
);

CREATE TABLE copies (
    copy_id  SERIAL PRIMARY KEY,
    code     VARCHAR(12) NOT NULL UNIQUE,
    movie_id INTEGER     NOT NULL REFERENCES movies(movie_id) ON DELETE RESTRICT,
    format   VARCHAR(10) NOT NULL,
    status   VARCHAR(12) NOT NULL DEFAULT 'available'
);

CREATE TABLE members (
    member_id SERIAL PRIMARY KEY,
    name      VARCHAR(100) NOT NULL,
    email     VARCHAR(120) NOT NULL UNIQUE,
    phone     VARCHAR(15),
    active    BOOLEAN NOT NULL DEFAULT TRUE
);

CREATE TABLE rentals (
    rental_id   SERIAL PRIMARY KEY,
    copy_id     INTEGER NOT NULL REFERENCES copies(copy_id)   ON DELETE RESTRICT,
    member_id   INTEGER NOT NULL REFERENCES members(member_id) ON DELETE RESTRICT,
    rental_date DATE NOT NULL DEFAULT CURRENT_DATE,
    due_date    DATE NOT NULL,
    return_date DATE
);

Step 4 — Constraints that encode the business rules

ALTER TABLE movies
    ADD CONSTRAINT ck_movies_year     CHECK (year_ BETWEEN 1888 AND 2100),
    ADD CONSTRAINT ck_movies_duration CHECK (duration_min BETWEEN 1 AND 600),
    ADD CONSTRAINT ck_movies_rating   CHECK (rating IN ('TP','7','12','16','18'));

ALTER TABLE copies
    ADD CONSTRAINT ck_copies_format CHECK (format IN ('DVD','Blu-ray')),
    ADD CONSTRAINT ck_copies_status CHECK (status IN ('available','rented','damaged','withdrawn'));

ALTER TABLE rentals
    ADD CONSTRAINT ck_rent_due    CHECK (due_date > rental_date),
    ADD CONSTRAINT ck_rent_return CHECK (return_date IS NULL
                                      OR return_date >= rental_date);

-- Hard rule: a copy cannot be rented to two people at once.
-- A PARTIAL UNIQUE index over the open rentals guarantees it.
CREATE UNIQUE INDEX uq_open_rental
    ON rentals (copy_id)
    WHERE return_date IS NULL;

-- The counter's most frequent query: available copies of a movie
CREATE INDEX idx_copies_movie ON copies (movie_id) WHERE status = 'available';

Expected result

The schema must answer the five queries of the task. Quick check:

Client's question	Query that answers it
Available copies of a movie	`SELECT ... FROM copies WHERE movie_id = ? AND status='available'`
What a member has out on rental	`rentals JOIN copies JOIN movies WHERE member_id=? AND return_date IS NULL`
Movies of a genre	`movies JOIN movies_genres JOIN genres WHERE g.name=?`
Movies of an actor	`cast_members JOIN movies WHERE actor_id=?`
Overdue rentals	`WHERE return_date IS NULL AND due_date < CURRENT_DATE`

Explanation and debatable decisions

Why genres is a table and not a text column. The owner said "there are some that belong to two genres". That rules out a genre VARCHAR(40) column outright and rules out even more forcefully the anti-pattern of the comma-separated list ('drama,comedy'), which we already saw in 04-01: it makes indexing impossible, JOINing impossible and guaranteeing that the text is spelled right impossible. A genres table with UNIQUE (name) also prevents "Documentary", "documentary" and "documentaries" from living side by side.

The primary key of cast_members includes character_name. It is a debatable decision and it is worth understanding why. With PRIMARY KEY (movie_id, actor_id), an actor playing two parts in the same movie —twins, double roles— would be impossible to record. Including character_name in the key allows it. The equally valid alternative is to put a surrogate key cast_member_id SERIAL and a UNIQUE (movie_id, actor_id, character_name); the effect is the same and the foreign keys towards cast_members come out shorter should they ever be needed.

The referential actions are not all the same, and that is deliberate:

Foreign key	Action	Reason
`movies_genres → movies`	`ON DELETE CASCADE`	If the movie disappears, its genre classification means nothing
`movies_genres → genres`	`ON DELETE RESTRICT`	Deleting the genre "drama" must not cascade-delete its assignment to 400 movies: they have to be reclassified first
`rentals → members`	`ON DELETE RESTRICT`	The rental history is accounting information. A member who leaves is marked `active = FALSE`, not deleted
`copies → movies`	`ON DELETE RESTRICT`	If there are physical copies on the shelf, the movie cannot disappear from the catalog

The partial unique index is the most interesting piece of the design. The rule "a copy can only be rented to one person" cannot be expressed with a UNIQUE (copy_id) on rentals, because then a copy could only be rented once in its entire history. What has to be restricted is that there is at most one open rental per copy, and that is exactly what CREATE UNIQUE INDEX ... WHERE return_date IS NULL does. It is a PostgreSQL constraint; in SQLite the same partial index exists, in MySQL 8 it does not, and there you would have to solve it with a trigger or by trusting the application (worse).

A reasonable alternative I did not take: a copies.status column redundant with the existence of an open rental. I have kept it because the owner distinguishes statuses that do not depend on the rental (damaged, withdrawn), but that introduces a possibility of inconsistency: an available copy with an open rental. If the volume were larger, it would be worth maintaining it with a trigger; with 3,000 movies, the application can take care of it.

Exercise 2: Interlibrary loan in BiblioRed

Difficulty: Intermediate

Task. BiblioRed wants to launch an interlibrary loan service. Management describes it like this:

"If a member from South wants a book that is only at North, right now they have to travel. We want them to be able to request it from their own branch and have the copy travel instead. We also want to be able to request from libraries in other cities with which we have an agreement —they have a name, a city, a contact person and an email—, and to lend to them. Each request is made by a member at a branch, on a specific material (not on a copy: we do not care which one arrives). The request goes through statuses: requested, accepted, in transit, ready for pickup, on loan, returned, rejected or cancelled, and for each status change we want to know when it happened and who did it. When the request is accepted, a specific copy is assigned to it. The shipping has a cost paid by the requesting library, and requests to external libraries have a maximum term different from the internal one."

Additional constraints: a request is directed either to another BiblioRed branch or to an external library, never to both and never to neither. A member cannot have more than three active requests at the same time (you may leave this one to the application, but say so).

Hint. The history of status changes is a weak entity that depends on the request.

Solution

Step 1 — Entities and relationships

New entities (the existing BiblioRed ones are not touched):

Entity	Type	Justification
`external_libraries`	Strong	It has an identity and attributes of its own
`interlibrary_requests`	Strong	The central fact of the service
`request_status_history`	Weak	It does not exist without its request; its key includes the request's

Relationships:

members 1:N interlibrary_requests (who asks).
branches 1:N interlibrary_requests as origin (where it is picked up).
branches 1:N interlibrary_requests as optional destination (who it is asked of).
external_libraries 1:N interlibrary_requests as optional destination.
materials 1:N interlibrary_requests (what is asked for).
copies 1:N interlibrary_requests (which copy was assigned, null until acceptance).
interlibrary_requests 1:N request_status_history (identifying, weak entity).

Step 2 — ER diagram

erDiagram
    MEMBERS               ||--o{ INTERLIBRARY_REQUESTS : requests
    BRANCHES              ||--o{ INTERLIBRARY_REQUESTS : "origin / destination"
    EXTERNAL_LIBRARIES    ||--o{ INTERLIBRARY_REQUESTS : "external destination"
    MATERIALS             ||--o{ INTERLIBRARY_REQUESTS : "is asked for"
    COPIES                ||--o{ INTERLIBRARY_REQUESTS : "is assigned"
    INTERLIBRARY_REQUESTS ||--|{ REQUEST_STATUS_HISTORY : "records"

    EXTERNAL_LIBRARIES {
        int  library_id PK
        text name
        text city
        text contact_name
        text contact_email
        date agreement_since
        bool active
    }
    INTERLIBRARY_REQUESTS {
        int     request_id PK
        int     member_id FK
        int     origin_branch_id FK
        int     destination_branch_id FK "null if external"
        int     external_library_id FK "null if internal"
        int     material_id FK
        int     copy_id FK "null until accepted"
        date    request_date
        date    deadline
        numeric shipping_cost
        text    current_status
    }
    REQUEST_STATUS_HISTORY {
        int       request_id PK_FK
        int       sequence PK
        text      status
        timestamp occurred_at
        text      user_name
        text      notes
    }

Step 3 — CREATE TABLE

CREATE TABLE external_libraries (
    library_id      SERIAL PRIMARY KEY,
    name            VARCHAR(120) NOT NULL,
    city            VARCHAR(60)  NOT NULL,
    contact_name    VARCHAR(100),
    contact_email   VARCHAR(120),
    agreement_since DATE NOT NULL,
    active          BOOLEAN NOT NULL DEFAULT TRUE,
    CONSTRAINT uq_extlib_name_city UNIQUE (name, city)
);

CREATE TABLE interlibrary_requests (
    request_id            SERIAL PRIMARY KEY,
    member_id             INTEGER NOT NULL REFERENCES members(member_id)      ON DELETE RESTRICT,
    origin_branch_id      INTEGER NOT NULL REFERENCES branches(branch_id)     ON DELETE RESTRICT,
    destination_branch_id INTEGER          REFERENCES branches(branch_id)     ON DELETE RESTRICT,
    external_library_id   INTEGER          REFERENCES external_libraries(library_id) ON DELETE RESTRICT,
    material_id           INTEGER NOT NULL REFERENCES materials(material_id)  ON DELETE RESTRICT,
    copy_id               INTEGER          REFERENCES copies(copy_id)         ON DELETE SET NULL,
    request_date          DATE NOT NULL DEFAULT CURRENT_DATE,
    deadline              DATE NOT NULL,
    shipping_cost         NUMERIC(6,2) NOT NULL DEFAULT 0,
    current_status        VARCHAR(22)  NOT NULL DEFAULT 'requested'
);

-- Weak entity: its primary key drags along the request's
CREATE TABLE request_status_history (
    request_id  INTEGER   NOT NULL REFERENCES interlibrary_requests(request_id) ON DELETE CASCADE,
    sequence    SMALLINT  NOT NULL,
    status      VARCHAR(22) NOT NULL,
    occurred_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    user_name   VARCHAR(60) NOT NULL,
    notes       TEXT,
    PRIMARY KEY (request_id, sequence)
);

Step 4 — Constraints

-- A reusable domain for the set of statuses (PostgreSQL)
CREATE DOMAIN interlibrary_status AS VARCHAR(22)
    CHECK (VALUE IN ('requested','accepted','in_transit','ready_for_pickup',
                     'on_loan','returned','rejected','cancelled'));

ALTER TABLE interlibrary_requests
    ALTER COLUMN current_status TYPE interlibrary_status;
ALTER TABLE request_status_history
    ALTER COLUMN status TYPE interlibrary_status;

ALTER TABLE interlibrary_requests
    -- EITHER an internal destination OR an external one, exactly one of the two
    ADD CONSTRAINT ck_req_exclusive_destination CHECK (
        (destination_branch_id IS NOT NULL AND external_library_id IS NULL)
     OR (destination_branch_id IS NULL     AND external_library_id IS NOT NULL)
    ),
    -- It makes no sense to ask your own branch for a material
    ADD CONSTRAINT ck_req_different_origin CHECK (
        destination_branch_id IS NULL OR destination_branch_id <> origin_branch_id
    ),
    ADD CONSTRAINT ck_req_deadline CHECK (deadline > request_date),
    ADD CONSTRAINT ck_req_cost     CHECK (shipping_cost >= 0),
    -- A copy can only be assigned from 'accepted' onwards
    ADD CONSTRAINT ck_req_copy_status CHECK (
        copy_id IS NOT NULL
     OR current_status IN ('requested','rejected','cancelled')
    );

-- A member cannot ask twice for the same material while the request is still alive
CREATE UNIQUE INDEX uq_live_request
    ON interlibrary_requests (member_id, material_id)
    WHERE current_status NOT IN ('returned','rejected','cancelled');

CREATE INDEX idx_req_status ON interlibrary_requests (current_status, deadline);
CREATE INDEX idx_req_member ON interlibrary_requests (member_id, request_date DESC);

Expected result

Three new tables, zero modifications to BiblioRed's existing tables. Example of a consistent load:

INSERT INTO external_libraries (name, city, contact_name, contact_email, agreement_since)
VALUES ('Port Alt Public Library','Port Alt','Lidia Serna','[email protected]','2025-04-01');

INSERT INTO interlibrary_requests
  (member_id, origin_branch_id, destination_branch_id, material_id, deadline)
VALUES (16, 3, 2, 904, DATE '2026-08-20');          -- Nuria Bastos (South) asks North

INSERT INTO request_status_history (request_id, sequence, status, user_name)
VALUES (1, 1, 'requested', 'desk.south');

And the check that the exclusive-destination rule works:

INSERT INTO interlibrary_requests
  (member_id, origin_branch_id, destination_branch_id, external_library_id, material_id, deadline)
VALUES (16, 3, 2, 1, 904, DATE '2026-08-20');
-- ERROR: new row violates check constraint "ck_req_exclusive_destination"

Explanation and debatable decisions

Designing over an existing schema changes the rules of the game. The strongest implicit requirement of this exercise is that you cannot break anything. That is why the solution adds no columns to loans or to copies: any query, report or index that already existed in BiblioRed keeps working exactly the same after the service is installed.

The request points at materials and not at copies, because the member said "we do not care which one arrives". copy_id is null at the beginning and is filled in on acceptance. That NULL is not a shortcoming of the design: it is information ("it has not been assigned yet"), and the CHECK ck_req_copy_status ties it to the status so that there cannot be a request "in transit" with no copy.

Two foreign keys towards the same table. origin_branch_id and destination_branch_id both point at branches. It is perfectly legal and very common; the only thing it demands is column names that state their role, because a plain branch_id would be ambiguous. It is the same pattern that already appears in BiblioRed between members.branch_id and copies.branch_id.

The exclusive destination: three alternatives.

Option	How	Advantage	Drawback
The chosen one: two nullable columns + `CHECK`	A single `CHECK` with `IS NULL`/`IS NOT NULL`	Simple, readable, integrity guaranteed by the engine	Two columns where conceptually there is one
Hierarchy: a `destinations` table with subtypes	Generic `destinations` + `destinations_branch` and `destinations_external`	Extensible to a third kind of destination	One more `JOIN` in every query; oversized for two cases
Generic `destination_type` + `destination_id` column	A single "polymorphic" foreign key	Compact	Impossible to declare the foreign key. Anti-pattern; the engine stops protecting integrity

The third is the one usually proposed by whoever comes from an ORM and is the only clearly incorrect one.

The history as a weak entity. request_status_history has primary key (request_id, sequence): the sequence number only makes sense inside its request. It is the textbook case of a weak entity with an identifying relationship, and that is why it carries ON DELETE CASCADE: if the request disappears, its history means nothing.

There is a deliberate redundancy here: current_status in the header duplicates the last status of the history. It could always be computed with ORDER BY sequence DESC LIMIT 1, but it is the most frequent query in the system (each branch's inbox panel) and the column makes it indexable. It is the controlled denormalization of lesson 05-04: it is accepted in exchange for having to keep both things in sync, ideally with a trigger.

The limit of three active requests per member. It cannot be expressed with a CHECK, because a CHECK only sees the row being inserted and this rule involves counting rows of the table. The real options are a BEFORE INSERT trigger that counts and raises an exception, or application logic inside the transaction with a SELECT ... FOR UPDATE on the member. What matters is saying it in the design, not leaving it implicit: a business rule with no constraint is a rule that will be broken some day.

Exercise 3: Online course platform

Difficulty: Intermediate

Task. A training platform wants its database:

"We have courses, and each course is divided into modules, and each module into lessons. Lessons are numbered within their module and modules within their course. A lesson has a title, a type (video, text or quiz) and an estimated duration. Courses can have prerequisites: to take 'Advanced SQL' you have to have taken 'Basic SQL' first, and a course can have several prerequisites. Students enroll in courses; of the enrollment we keep the date, the price paid and whether it is active, completed or dropped. We want to know, for each student and each lesson, whether they have finished it and when; also the percentage of progress through the course. Quiz-type lessons have questions with several options, of which one is correct, and each attempt by a student stores the score and the date. A student may attempt a quiz several times."

Hint. "Prerequisites" is an N:M relationship of a table with itself.

Solution

Step 1 — Entities and relationships

Content hierarchy: courses 1:N modules 1:N lessons. All three are strong entities with a surrogate key, but modules and lessons also carry an alternate key reflecting their numbering within the parent.

Self-reference: courses N:M courses through course_prerequisites (course_id, prerequisite_id).

N:M with attributes: students N:M courses through enrollments, which has a date, a price and a status of its own.

Progress: enrollments N:M lessons through lesson_progress. Note that the progress hangs off the enrollment, not off the student: if somebody enrolls twice in the same course, each enrollment has its own progress.

Quizzes: lessons 1:N questions 1:N options; enrollments 1:N attempts (over a quiz-type lesson).

Step 2 — ER diagram

erDiagram
    COURSES   ||--o{ MODULES    : contains
    MODULES   ||--o{ LESSONS    : contains
    COURSES   ||--o{ COURSE_PREREQUISITES : "requires"
    COURSES   ||--o{ COURSE_PREREQUISITES : "is prerequisite of"
    STUDENTS  ||--o{ ENROLLMENTS : makes
    COURSES   ||--o{ ENROLLMENTS : receives
    ENROLLMENTS||--o{ LESSON_PROGRESS : advances
    LESSONS   ||--o{ LESSON_PROGRESS : "is completed in"
    LESSONS   ||--o{ QUESTIONS  : "quiz of"
    QUESTIONS ||--|{ OPTIONS    : offers
    ENROLLMENTS||--o{ ATTEMPTS  : generates
    LESSONS   ||--o{ ATTEMPTS   : "is graded in"

    COURSES   { int course_id PK
                text title
                text level
                bool published }
    MODULES   { int module_id PK
                int course_id FK
                int order_num
                text title }
    LESSONS   { int lesson_id PK
                int module_id FK
                int order_num
                text title
                text type
                int duration_min }
    COURSE_PREREQUISITES { int course_id PK_FK
                           int prerequisite_id PK_FK }
    STUDENTS  { int student_id PK
                text email UK
                text name }
    ENROLLMENTS{ int enrollment_id PK
                int student_id FK
                int course_id FK
                date enrollment_date
                numeric price_paid
                text status }
    LESSON_PROGRESS { int enrollment_id PK_FK
                      int lesson_id PK_FK
                      timestamp completed_at }
    QUESTIONS { int question_id PK
                int lesson_id FK
                int order_num
                text prompt }
    OPTIONS   { int option_id PK
                int question_id FK
                text content
                bool is_correct }
    ATTEMPTS  { int attempt_id PK
                int enrollment_id FK
                int lesson_id FK
                timestamp taken_at
                numeric score }

Step 3 — CREATE TABLE

CREATE TABLE courses (
    course_id SERIAL PRIMARY KEY,
    title     VARCHAR(150) NOT NULL,
    level     VARCHAR(15)  NOT NULL,
    published BOOLEAN NOT NULL DEFAULT FALSE
);

CREATE TABLE modules (
    module_id SERIAL PRIMARY KEY,
    course_id INTEGER NOT NULL REFERENCES courses(course_id) ON DELETE CASCADE,
    order_num SMALLINT NOT NULL,
    title     VARCHAR(150) NOT NULL,
    CONSTRAINT uq_module_order UNIQUE (course_id, order_num)
);

CREATE TABLE lessons (
    lesson_id    SERIAL PRIMARY KEY,
    module_id    INTEGER NOT NULL REFERENCES modules(module_id) ON DELETE CASCADE,
    order_num    SMALLINT NOT NULL,
    title        VARCHAR(150) NOT NULL,
    type         VARCHAR(12)  NOT NULL,
    duration_min SMALLINT,
    CONSTRAINT uq_lesson_order UNIQUE (module_id, order_num)
);

CREATE TABLE course_prerequisites (
    course_id       INTEGER NOT NULL REFERENCES courses(course_id) ON DELETE CASCADE,
    prerequisite_id INTEGER NOT NULL REFERENCES courses(course_id) ON DELETE RESTRICT,
    PRIMARY KEY (course_id, prerequisite_id),
    CONSTRAINT ck_prereq_not_self CHECK (course_id <> prerequisite_id)
);

CREATE TABLE students (
    student_id SERIAL PRIMARY KEY,
    email      VARCHAR(120) NOT NULL UNIQUE,
    name       VARCHAR(100) NOT NULL,
    join_date  DATE NOT NULL DEFAULT CURRENT_DATE
);

CREATE TABLE enrollments (
    enrollment_id   SERIAL PRIMARY KEY,
    student_id      INTEGER NOT NULL REFERENCES students(student_id) ON DELETE RESTRICT,
    course_id       INTEGER NOT NULL REFERENCES courses(course_id)   ON DELETE RESTRICT,
    enrollment_date DATE NOT NULL DEFAULT CURRENT_DATE,
    price_paid      NUMERIC(8,2) NOT NULL,
    status          VARCHAR(12) NOT NULL DEFAULT 'active'
);

CREATE TABLE lesson_progress (
    enrollment_id INTEGER NOT NULL REFERENCES enrollments(enrollment_id) ON DELETE CASCADE,
    lesson_id     INTEGER NOT NULL REFERENCES lessons(lesson_id)         ON DELETE CASCADE,
    completed_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
    PRIMARY KEY (enrollment_id, lesson_id)
);

CREATE TABLE questions (
    question_id SERIAL PRIMARY KEY,
    lesson_id   INTEGER NOT NULL REFERENCES lessons(lesson_id) ON DELETE CASCADE,
    order_num   SMALLINT NOT NULL,
    prompt      TEXT NOT NULL,
    CONSTRAINT uq_question_order UNIQUE (lesson_id, order_num)
);

CREATE TABLE options (
    option_id   SERIAL PRIMARY KEY,
    question_id INTEGER NOT NULL REFERENCES questions(question_id) ON DELETE CASCADE,
    content     TEXT NOT NULL,
    is_correct  BOOLEAN NOT NULL DEFAULT FALSE
);

CREATE TABLE attempts (
    attempt_id    SERIAL PRIMARY KEY,
    enrollment_id INTEGER NOT NULL REFERENCES enrollments(enrollment_id) ON DELETE CASCADE,
    lesson_id     INTEGER NOT NULL REFERENCES lessons(lesson_id)         ON DELETE CASCADE,
    taken_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
    score         NUMERIC(5,2) NOT NULL
);

Step 4 — Constraints

ALTER TABLE courses
    ADD CONSTRAINT ck_courses_level CHECK (level IN ('beginner','intermediate','advanced'));

ALTER TABLE lessons
    ADD CONSTRAINT ck_lesson_type  CHECK (type IN ('video','text','quiz')),
    ADD CONSTRAINT ck_lesson_dur   CHECK (duration_min IS NULL OR duration_min > 0),
    ADD CONSTRAINT ck_lesson_order CHECK (order_num > 0);

ALTER TABLE enrollments
    ADD CONSTRAINT ck_enr_status CHECK (status IN ('active','completed','dropped')),
    ADD CONSTRAINT ck_enr_price  CHECK (price_paid >= 0);

-- A student cannot have two active enrollments in the same course,
-- but can re-enroll after dropping out
CREATE UNIQUE INDEX uq_active_enrollment
    ON enrollments (student_id, course_id)
    WHERE status = 'active';

ALTER TABLE attempts
    ADD CONSTRAINT ck_attempt_score CHECK (score BETWEEN 0 AND 10);

-- Each question must have exactly one correct option
CREATE UNIQUE INDEX uq_correct_option
    ON options (question_id)
    WHERE is_correct;

Expected result

Eleven tables. The progress-percentage query, which is the reason half the platform exists, comes out straight away:

SELECT e.enrollment_id,
       count(lp.lesson_id) AS completed,
       (SELECT count(*) FROM lessons l
          JOIN modules mo ON mo.module_id = l.module_id
         WHERE mo.course_id = e.course_id) AS total,
       round(100.0 * count(lp.lesson_id) /
             NULLIF((SELECT count(*) FROM lessons l
                       JOIN modules mo ON mo.module_id = l.module_id
                      WHERE mo.course_id = e.course_id), 0), 1) AS pct
FROM enrollments e
LEFT JOIN lesson_progress lp ON lp.enrollment_id = e.enrollment_id
WHERE e.enrollment_id = 1
GROUP BY e.enrollment_id, e.course_id;

Explanation and debatable decisions

Progress hangs off the enrollment, not off the student. It is the most important decision in the design and the easiest to get wrong. If lesson_progress had (student_id, lesson_id), a student who drops a course and enrolls again would drag along all their previous progress, and there would be no way of knowing which enrollment each completed lesson belonged to. With (enrollment_id, lesson_id), each attempt at taking the course has its own history. The price is one more JOIN to get from the student to the progress; it is a low price.

Numbering within the parent: surrogate key + composite UNIQUE. modules has module_id as primary key and UNIQUE (course_id, order_num) as alternate key. The alternative —a composite primary key (course_id, order_num)— is also defensible and is "purer", but it has two practical drawbacks: reordering the modules forces an update of the primary key (and cascading it to every lesson), and the foreign keys towards lessons would end up being three columns wide. With a surrogate key, reordering is an UPDATE of the order_num column and nothing else.

The N:M self-reference. course_prerequisites is a junction table whose two foreign keys point at the same table. The CHECK (course_id <> prerequisite_id) prevents the trivial case of a course being a prerequisite of itself. What no declarative constraint can prevent is a longer cycle: A requires B, B requires C, C requires A. Detecting that requires traversing the graph, and it is done with a recursive CTE —you will see it in 07-04— or with a trigger that runs one before inserting.

uq_correct_option guarantees at most one correct option, not exactly one. The partial unique index prevents two options marked as correct in the same question, but it does not prevent there being none. That second half of the rule —"at least one"— is a set constraint that relational databases do not express well declaratively; it is solved with an AFTER trigger or with a check when the course is published. Recognizing the limit is part of the design.

A reasonable alternative I did not take: storing in attempts the specific answers to each question (attempt_answers). The task only asked for the score, and adding that table without anybody asking for it is over-design. But if tomorrow they want statistics on "which question most people get wrong", it would be needed, and the extension would be clean: (attempt_id, question_id, option_id).

Exercise 4: Auto repair shop with a hierarchy and a ternary relationship

Difficulty: Advanced

Task. An auto repair shop in Vallmar:

"We service vehicles: cars, motorcycles and vans. For all of them we keep the plate, the make, the model, the year and the owning customer. For cars we also care about the number of seats and the fuel type; for motorcycles, the engine displacement; and for vans, the maximum load in kilos and whether they have a tachograph. A customer can have several vehicles and a vehicle has a single owner. When a vehicle comes in we open a repair order with the entry date, the mileage and a description of the problem. On one order several interventions are carried out; each intervention is performed by one specific mechanic on the order, applying a job type from the catalog (oil change, alignment, diagnostics...), and we note down the hours spent. The same mechanic can do several job types on the same order, and the same job type can be done by different mechanics on the same order on different days. We also record the parts used in each intervention, with the quantity and the unit price applied that day."

Hint. "Intervention" relates three entities at once. And a generalization hierarchy can be transformed in three different ways: choose one and justify it.

Solution

Step 1 — Entities and relationships

The hierarchy: vehicles is the superentity, with cars, motorcycles and vans as subentities. The generalization is total (every vehicle is one of the three types) and disjoint (none is two things at once).

The ternary relationship: interventions relates repair_orders × mechanics × job_types. Since the task explicitly says that the same mechanic can repeat a job type on the same order on different days, the ternary cannot be identified by the triple: it needs a surrogate key or the inclusion of the date.

parts N:M interventions with attributes (quantity, unit_price).

Step 2 — ER diagram

erDiagram
    CUSTOMERS ||--o{ VEHICLES : owns
    VEHICLES  ||--o| CARS        : "is a"
    VEHICLES  ||--o| MOTORCYCLES : "is a"
    VEHICLES  ||--o| VANS        : "is a"
    VEHICLES  ||--o{ REPAIR_ORDERS : generates
    REPAIR_ORDERS ||--o{ INTERVENTIONS : includes
    MECHANICS ||--o{ INTERVENTIONS : performs
    JOB_TYPES ||--o{ INTERVENTIONS : "is applied in"
    INTERVENTIONS ||--o{ INTERVENTION_PARTS : consumes
    PARTS         ||--o{ INTERVENTION_PARTS : "is used in"

    CUSTOMERS { int customer_id PK
                text name
                text tax_id UK
                text phone }
    VEHICLES  { int  vehicle_id PK
                text plate UK
                text type
                int  customer_id FK
                text make
                text model
                int  year_ }
    CARS        { int vehicle_id PK_FK
                  int seats
                  text fuel }
    MOTORCYCLES { int vehicle_id PK_FK
                  int engine_cc }
    VANS        { int vehicle_id PK_FK
                  int max_load_kg
                  bool tachograph }
    REPAIR_ORDERS { int  order_id PK
                    int  vehicle_id FK
                    date entry_date
                    int  mileage
                    text description
                    text status }
    MECHANICS { int mechanic_id PK
                text name
                text specialty }
    JOB_TYPES { int job_type_id PK
                text code UK
                text name
                numeric hourly_rate }
    INTERVENTIONS { int  intervention_id PK
                    int  order_id FK
                    int  mechanic_id FK
                    int  job_type_id FK
                    date work_date
                    numeric hours }
    PARTS     { int part_id PK
                text reference UK
                text description
                numeric current_price }
    INTERVENTION_PARTS { int intervention_id PK_FK
                         int part_id PK_FK
                         int quantity
                         numeric unit_price }

Step 3 — CREATE TABLE

CREATE TABLE customers (
    customer_id SERIAL PRIMARY KEY,
    name        VARCHAR(120) NOT NULL,
    tax_id      VARCHAR(12) NOT NULL UNIQUE,
    phone       VARCHAR(15)
);

CREATE TABLE vehicles (
    vehicle_id  SERIAL PRIMARY KEY,
    plate       VARCHAR(10) NOT NULL UNIQUE,
    type        VARCHAR(10) NOT NULL,         -- discriminator
    customer_id INTEGER NOT NULL REFERENCES customers(customer_id) ON DELETE RESTRICT,
    make        VARCHAR(40) NOT NULL,
    model       VARCHAR(60) NOT NULL,
    year_       SMALLINT NOT NULL,
    CONSTRAINT ck_veh_type CHECK (type IN ('car','motorcycle','van')),
    -- Trick so that the subtable can only link with its own type:
    CONSTRAINT uq_veh_type UNIQUE (vehicle_id, type)
);

CREATE TABLE cars (
    vehicle_id INTEGER PRIMARY KEY,
    type       VARCHAR(10) NOT NULL DEFAULT 'car',
    seats      SMALLINT NOT NULL,
    fuel       VARCHAR(12) NOT NULL,
    CONSTRAINT ck_cars_type CHECK (type = 'car'),
    CONSTRAINT fk_cars_veh FOREIGN KEY (vehicle_id, type)
        REFERENCES vehicles (vehicle_id, type) ON DELETE CASCADE,
    CONSTRAINT ck_cars_seats CHECK (seats BETWEEN 1 AND 9),
    CONSTRAINT ck_cars_fuel CHECK (fuel IN ('gasoline','diesel','hybrid','electric','lpg'))
);

CREATE TABLE motorcycles (
    vehicle_id INTEGER PRIMARY KEY,
    type       VARCHAR(10) NOT NULL DEFAULT 'motorcycle',
    engine_cc  SMALLINT NOT NULL,
    CONSTRAINT ck_moto_type CHECK (type = 'motorcycle'),
    CONSTRAINT fk_moto_veh FOREIGN KEY (vehicle_id, type)
        REFERENCES vehicles (vehicle_id, type) ON DELETE CASCADE,
    CONSTRAINT ck_moto_cc CHECK (engine_cc BETWEEN 49 AND 2500)
);

CREATE TABLE vans (
    vehicle_id  INTEGER PRIMARY KEY,
    type        VARCHAR(10) NOT NULL DEFAULT 'van',
    max_load_kg INTEGER NOT NULL,
    tachograph  BOOLEAN NOT NULL DEFAULT FALSE,
    CONSTRAINT ck_van_type CHECK (type = 'van'),
    CONSTRAINT fk_van_veh FOREIGN KEY (vehicle_id, type)
        REFERENCES vehicles (vehicle_id, type) ON DELETE CASCADE,
    CONSTRAINT ck_van_load CHECK (max_load_kg BETWEEN 100 AND 5000)
);

CREATE TABLE repair_orders (
    order_id    SERIAL PRIMARY KEY,
    vehicle_id  INTEGER NOT NULL REFERENCES vehicles(vehicle_id) ON DELETE RESTRICT,
    entry_date  DATE NOT NULL DEFAULT CURRENT_DATE,
    exit_date   DATE,
    mileage     INTEGER NOT NULL,
    description TEXT NOT NULL,
    status      VARCHAR(12) NOT NULL DEFAULT 'open',
    CONSTRAINT ck_ord_status CHECK (status IN ('open','in_progress','closed','invoiced')),
    CONSTRAINT ck_ord_mileage CHECK (mileage >= 0),
    CONSTRAINT ck_ord_exit   CHECK (exit_date IS NULL OR exit_date >= entry_date)
);

CREATE TABLE mechanics (
    mechanic_id SERIAL PRIMARY KEY,
    name        VARCHAR(100) NOT NULL,
    specialty   VARCHAR(40),
    active      BOOLEAN NOT NULL DEFAULT TRUE
);

CREATE TABLE job_types (
    job_type_id SERIAL PRIMARY KEY,
    code        VARCHAR(10) NOT NULL UNIQUE,
    name        VARCHAR(80) NOT NULL,
    hourly_rate NUMERIC(7,2) NOT NULL CHECK (hourly_rate > 0)
);

-- The ternary relationship, with a surrogate key
CREATE TABLE interventions (
    intervention_id SERIAL PRIMARY KEY,
    order_id        INTEGER NOT NULL REFERENCES repair_orders(order_id)       ON DELETE CASCADE,
    mechanic_id     INTEGER NOT NULL REFERENCES mechanics(mechanic_id)        ON DELETE RESTRICT,
    job_type_id     INTEGER NOT NULL REFERENCES job_types(job_type_id)        ON DELETE RESTRICT,
    work_date       DATE NOT NULL DEFAULT CURRENT_DATE,
    hours           NUMERIC(5,2) NOT NULL,
    CONSTRAINT ck_int_hours CHECK (hours > 0 AND hours <= 24),
    CONSTRAINT uq_intervention UNIQUE (order_id, mechanic_id, job_type_id, work_date)
);

CREATE TABLE parts (
    part_id       SERIAL PRIMARY KEY,
    reference     VARCHAR(30) NOT NULL UNIQUE,
    description   VARCHAR(150) NOT NULL,
    current_price NUMERIC(8,2) NOT NULL CHECK (current_price >= 0)
);

CREATE TABLE intervention_parts (
    intervention_id INTEGER NOT NULL REFERENCES interventions(intervention_id) ON DELETE CASCADE,
    part_id         INTEGER NOT NULL REFERENCES parts(part_id)                 ON DELETE RESTRICT,
    quantity        SMALLINT NOT NULL CHECK (quantity > 0),
    unit_price      NUMERIC(8,2) NOT NULL CHECK (unit_price >= 0),
    PRIMARY KEY (intervention_id, part_id)
);

Step 4 — The missing constraint

-- Mileage consistency: a later order cannot have fewer km
-- (set rule: cannot be expressed with CHECK; trigger or application)

-- This one is not declarative either: the order cannot be closed with no intervention
-- → also not a CHECK. It is documented and implemented with an AFTER trigger.

-- What the engine does guarantee:
--  * a vehicle belongs to exactly one subtype (through the composite FK)
--  * there are no two identical interventions on the same day (uq_intervention)
--  * prices and hours are positive

Expected result

Twelve tables. Check that the hierarchy is properly closed:

INSERT INTO customers (name, tax_id) VALUES ('Nerea Solans','44112233X');
INSERT INTO vehicles (plate, type, customer_id, make, model, year_)
VALUES ('4471 KLM','motorcycle',1,'Yamaha','MT-07',2021);

-- Correct: the motorcycle goes to its subtable
INSERT INTO motorcycles (vehicle_id, engine_cc) VALUES (1, 689);

-- Incorrect: trying to put that same motorcycle in as a car
INSERT INTO cars (vehicle_id, seats, fuel) VALUES (1, 5, 'gasoline');
-- ERROR: insert or update on table "cars" violates foreign key constraint "fk_cars_veh"

Explanation and debatable decisions

The three ways of transforming a hierarchy, and why I chose this one. Lesson 04-03 gave three options:

Option	How	When it suits	Here
Single table	One `vehicles` table with all the columns of the three subtypes, most of them null	Few specific attributes, queries always over the whole set	Discarded: 5 nullable columns and no `NOT NULL` possible on `engine_cc`
Table per subtype (the chosen one)	Superentity + one table per subtype with `vehicle_id` as PK and FK	Specific attributes that must be mandatory; total, disjoint generalization	Chosen
Subtypes only	Three independent tables with no superentity	The subtypes share no relationships	Discarded: `repair_orders` needs to point at any vehicle, and with three tables a polymorphic FK would be needed

The third option is the one that breaks the design as soon as repair_orders shows up: you have to be able to reference "a vehicle" without knowing its type.

The trick of the composite foreign key (vehicle_id, type) deserves attention because it is elegant and little known. The problem it solves: with a normal foreign key cars.vehicle_id → vehicles.vehicle_id, nothing stops you putting into cars a vehicle whose type is 'motorcycle'. The solution has three pieces that only work together:

UNIQUE (vehicle_id, type) on vehicles —redundant with the primary key, but necessary so that the composite FK has something to point at.
A type column in the subtable, with CHECK (type = 'car') and a DEFAULT.
The composite foreign key over both columns.

The result is that the engine prevents putting a motorcycle into the cars table. Without the trick, that rule would be left in the hands of the application. What is still not guaranteed is the totality of the generalization (that every vehicle has a row in some subtable): that requires deferred constraints or a trigger.

The ternary: why the triple as a key is not enough. The temptation is PRIMARY KEY (order_id, mechanic_id, job_type_id). The task dismantles it: "the same job type can be done by different mechanics on the same order on different days". And in fact the same mechanic can repeat the same job two days in a row. That is why the solution uses a surrogate key and adds UNIQUE (order_id, mechanic_id, job_type_id, work_date), which is the real alternate key. If tomorrow they allow two identical interventions on the same day (morning and afternoon), you would have to replace work_date with occurred_at TIMESTAMPTZ or drop the UNIQUE.

The surrogate key also has a decisive advantage: intervention_parts needs to point at the intervention, and with a four-column composite key the parts table would have six key columns.

unit_price duplicates parts.current_price, and that is fine. It is the most justified denormalization there is: the price of a part changes over time and an order already invoiced must keep the price that was applied. parts.current_price is today's price, intervention_parts.unit_price is that day's price. They are not the same datum, even though they coincide at the moment of insertion. Exercise 5 takes this idea all the way.

A reasonable alternative: modeling interventions without job_types, putting the name of the job in as free text. It would be simpler and it would be a mistake: the task speaks of a "catalog", and the hourly rate lives in it.

Exercise 5: Fares with time-bounded validity

Difficulty: Advanced

Task. A municipal parking company in Vallmar:

"We have four parking lots and each one applies fares that change over time. A fare says: for this parking lot and this type of user (resident, general, commercial), the price of the first hour, that of each additional hour and the daily maximum. Fares are approved in council session and come into force on a specific date; the previous one stops applying that same day. We need to keep every historical fare, because there are claims from three years ago and we have to be able to say which price was in force on March 14, 2024. Sometimes a fare is approved months in advance, so there may be future fares already loaded. There can never be two fares in force at once for the same parking lot and type of user. We also record the stays: entry, exit, plate and the amount charged."

Hint. Think of the key as "what + since when", and look for the PostgreSQL constraint that prevents two intervals from overlapping.

Solution

Step 1 — Entities and relationships

parking_lots: strong entity, stable.
user_types: a small catalog.
fares: the temporal entity. Its identity is not "the parking lot and the type", but "the parking lot, the type and the validity period".
stays: the facts. Each stay is charged with the fare in force at its moment.

The key relationship: parking_lots × user_types 1:N fares, where the discriminator completing the identity is the validity interval.

Step 2 — ER diagram

erDiagram
    PARKING_LOTS ||--o{ FARES : "applies"
    USER_TYPES   ||--o{ FARES : "for"
    PARKING_LOTS ||--o{ STAYS : "hosts"
    USER_TYPES   ||--o{ STAYS : "classifies"
    FARES        ||--o{ STAYS : "charges by"

    PARKING_LOTS { int parking_lot_id PK
                   text name UK
                   text address
                   int  spaces }
    USER_TYPES   { int user_type_id PK
                   text code UK
                   text name }
    FARES { int     fare_id PK
            int     parking_lot_id FK
            int     user_type_id FK
            date    valid_from
            date    valid_to "null = in force"
            numeric first_hour_price
            numeric extra_hour_price
            numeric daily_max
            text    council_resolution }
    STAYS { int       stay_id PK
            int       parking_lot_id FK
            int       user_type_id FK
            int       fare_id FK
            text      plate
            timestamp entry_time
            timestamp exit_time
            numeric   amount }

Step 3 — CREATE TABLE

CREATE EXTENSION IF NOT EXISTS btree_gist;

CREATE TABLE parking_lots (
    parking_lot_id SERIAL PRIMARY KEY,
    name           VARCHAR(60) NOT NULL UNIQUE,
    address        VARCHAR(120) NOT NULL,
    spaces         SMALLINT NOT NULL CHECK (spaces > 0)
);

CREATE TABLE user_types (
    user_type_id SERIAL PRIMARY KEY,
    code         VARCHAR(12) NOT NULL UNIQUE,
    name         VARCHAR(40) NOT NULL
);

CREATE TABLE fares (
    fare_id            SERIAL PRIMARY KEY,
    parking_lot_id     INTEGER NOT NULL REFERENCES parking_lots(parking_lot_id) ON DELETE RESTRICT,
    user_type_id       INTEGER NOT NULL REFERENCES user_types(user_type_id)     ON DELETE RESTRICT,
    valid_from         DATE NOT NULL,
    valid_to           DATE,                    -- NULL = in force indefinitely
    first_hour_price   NUMERIC(6,2) NOT NULL,
    extra_hour_price   NUMERIC(6,2) NOT NULL,
    daily_max          NUMERIC(6,2) NOT NULL,
    council_resolution VARCHAR(40),
    CONSTRAINT ck_fare_period CHECK (valid_to IS NULL OR valid_to > valid_from),
    CONSTRAINT ck_fare_prices CHECK (first_hour_price >= 0
                                 AND extra_hour_price >= 0
                                 AND daily_max        >= first_hour_price)
);

CREATE TABLE stays (
    stay_id        SERIAL PRIMARY KEY,
    parking_lot_id INTEGER NOT NULL REFERENCES parking_lots(parking_lot_id) ON DELETE RESTRICT,
    user_type_id   INTEGER NOT NULL REFERENCES user_types(user_type_id)     ON DELETE RESTRICT,
    fare_id        INTEGER NOT NULL REFERENCES fares(fare_id)               ON DELETE RESTRICT,
    plate          VARCHAR(10) NOT NULL,
    entry_time     TIMESTAMPTZ NOT NULL,
    exit_time      TIMESTAMPTZ,
    amount         NUMERIC(8,2),
    CONSTRAINT ck_stay_exit CHECK (exit_time IS NULL OR exit_time > entry_time),
    CONSTRAINT ck_stay_amount CHECK (amount IS NULL OR amount >= 0)
);

Step 4 — The non-overlap constraint

-- The rule from the task: never two fares in force at once
-- for the same parking lot and type of user.
ALTER TABLE fares ADD CONSTRAINT ex_fares_no_overlap
    EXCLUDE USING gist (
        parking_lot_id WITH =,
        user_type_id WITH =,
        daterange(valid_from, valid_to, '[)') WITH &&
    );

-- Index for the most frequent query: which fare applied on day X?
CREATE INDEX idx_fares_validity
    ON fares (parking_lot_id, user_type_id, valid_from DESC);

Expected result

Sample load with two historical periods and one future one:

INSERT INTO parking_lots (name, address, spaces)
VALUES ('P1 Plaza Mayor','Plaza Mayor s/n',240);
INSERT INTO user_types (code, name) VALUES ('RES','Resident'),('GEN','General');

INSERT INTO fares (parking_lot_id, user_type_id, valid_from, valid_to,
                   first_hour_price, extra_hour_price, daily_max, council_resolution)
VALUES (1,2,'2023-01-01','2024-07-01', 1.80, 1.20, 14.00, 'PLE-2022/114'),
       (1,2,'2024-07-01','2026-01-01', 2.00, 1.35, 16.00, 'PLE-2024/037'),
       (1,2,'2026-01-01', NULL,        2.20, 1.50, 18.00, 'PLE-2025/206');

Claim query: "which price applied on March 14, 2024?"

SELECT first_hour_price, extra_hour_price, daily_max, council_resolution
FROM fares
WHERE parking_lot_id = 1
  AND user_type_id = 2
  AND valid_from <= DATE '2024-03-14'
  AND (valid_to IS NULL OR valid_to > DATE '2024-03-14');

first_hour_price	extra_hour_price	daily_max	council_resolution
1.80	1.20	14.00	PLE-2022/114

And the check that the engine blocks an overlap:

INSERT INTO fares (parking_lot_id, user_type_id, valid_from, valid_to,
                   first_hour_price, extra_hour_price, daily_max)
VALUES (1, 2, '2025-06-01', '2025-12-01', 2.10, 1.40, 17.00);
-- ERROR: conflicting key value violates exclusion constraint "ex_fares_no_overlap"

Explanation and debatable decisions

The central decision: there is no UPDATE of prices, there are new rows. A beginner's instinct is UPDATE fares SET first_hour_price = 2.20 WHERE .... That UPDATE destroys the information the task asks to keep: as soon as it runs, there is no way of answering the 2024 claim. When a requirement says "historical", the business operation "change the price" translates into an INSERT, not into an UPDATE.

Closed-open interval [from, to). The task says that the new fare comes into force on "a specific date" and the previous one stops applying "that same day". That is exactly an interval closed on the left and open on the right: the old fare is valid up to and including June 30 and the new one from July 1, and both are written with 2024-07-01 as the boundary. The alternative —valid_to = '2024-06-30' with an interval closed at both ends— also works, but it forces date arithmetic every time a period is chained on and it breaks as soon as the granularity goes from days to hours. Closed-open is the convention to adopt by default in temporal data.

valid_to IS NULL means "still in force". It is a legitimate use of the null and it fits daterange(from, to), which interprets the upper NULL as infinity. The alternative is to put '9999-12-31'; it simplifies the queries (BETWEEN works without OR ... IS NULL) in exchange for introducing a magic date that some day somebody will show on screen.

Three ways of guaranteeing non-overlap:

Option	Portability	Strength
`EXCLUDE USING gist` with `daterange`	PostgreSQL only	Total: the engine guarantees it, even under concurrency
Partial unique index `(parking_lot_id, user_type_id) WHERE valid_to IS NULL`	PostgreSQL and SQLite	Partial: it guarantees a single open fare, but does not prevent overlaps between closed periods
Trigger that queries before inserting	Any	Depends on the isolation level; with `READ COMMITTED` two simultaneous sessions can slip through

The first is superior and that is why it is the one in the solution; the second is a good runner-up and is worth knowing because it covers 90% of cases with standard syntax.

stays.fare_id stores the fare applied, and it is essential. It could be deduced from entry_time and the fares table, but pinning it down in the stay has two advantages: the invoice stays immutable even if somebody later corrects a badly loaded validity date, and the billing query does not need the range JOIN, which is much more expensive than an equality JOIN. It is the same logic as intervention_parts.unit_price in exercise 4.

A reasonable alternative I did not take: two tables, current_fares and fares_history. It is a very widespread pattern and it has one clear advantage (the hot table stays tiny) and two serious drawbacks: the queries that cross the boundary need a UNION, and moving from one table to the other is an operation that has to be written correctly and that can fail. With a single table and an appropriate index, PostgreSQL handles millions of periods without breaking a sweat.

Common Mistakes and Tips

1. Confusing the work with the physical object. Movie/copy, material/copy, model/unit. If two things can be in different places and in different statuses, they are two entities.

2. Hidden multivalued attributes. "Sometimes there are two genres", "the phone numbers", "the subtitle languages". Every time the client says "sometimes there are several", there is a table there.

3. Polymorphic foreign keys. An object_type + object_id column that points at different tables depending on the type. The engine cannot declare that foreign key, so integrity is left unprotected. Use mutually exclusive nullable columns with a CHECK, or a hierarchy.

4. Putting ON DELETE CASCADE on every foreign key "just in case". CASCADE is right for what does not exist without its parent (the lines of an order, the history of a request). For accounting history —loans, rentals, invoices— the correct action is almost always RESTRICT, and deregistration is done with an active column.

5. Forgetting that a CHECK only sees its own row. "At most three active requests", "at least one correct option", "the sum of the lines must match the total": none of them is expressible with a CHECK. Acknowledge it in the design and decide where it goes (trigger, application or deferred constraint).

6. Composite primary keys in entities that are going to be referenced a lot. It is technically correct and practically awkward: each child table drags all the columns along. Surrogate key + UNIQUE on the natural key is almost always a better compromise.

7. Prices and fares not frozen. An invoiced amount read from the current price table changes on its own when the prices change. Copy the applied price into the document line.

8. Inconsistent naming. movieID, id_genre, MemberId in the same schema. Choose a convention (snake_case, singular or plural, _id suffix) and do not break it. It is the first thing whoever inherits your schema sees.

9. Designing for queries nobody has asked for. Every table you add is a table that has to be maintained. If the task does not mention a need, note it down as a possible extension, do not build it.

A method tip. Before writing the first CREATE TABLE, write the list of questions the schema must answer and keep it in sight. When you finish, check one by one that you can write the query. That is the "done" criterion; the CREATE TABLE running without an error is not.

Exercises

No hints. Each one asks for all four steps.

Exercise A: Municipal gym

Design the schema of a gym: members with a monthly fee, instructor-led classes with a fixed weekly schedule (day of the week and time), instructors, rooms with a capacity, and bookings of a seat by members for a specific session of a class on a specific date. A member cannot book the same session twice, and a session cannot take more bookings than the capacity of its room. Besides, two classes cannot occupy the same room at the same time.

Exercise B: BiblioRed extension — donations and withdrawals

Extend BiblioRed to manage the origin and end of life of copies: where each copy came from (a purchase from a supplier with an invoice, a donation from an individual or an organization, or an exchange with another library) and, when it is withdrawn, for what reason, on what date and what its destination was (charity sale, recycling, transfer). You cannot modify the copies table except to add columns, and no existing query may break.

Solutions

Solution A — Gym

Entities: members, rooms, instructors, classes (the definition: name, level, duration), sessions (a specific occurrence: class + date + time + room + instructor), bookings (member × session).

The key distinction is class versus session: "Tuesday Pilates at 19:00" is the class; "the Pilates of Tuesday August 4, 2026 at 19:00 in Room 2 with Aitor" is the session. Bookings are made on sessions.

CREATE TABLE rooms (
    room_id  SERIAL PRIMARY KEY,
    name     VARCHAR(40) NOT NULL UNIQUE,
    capacity SMALLINT NOT NULL CHECK (capacity > 0)
);

CREATE TABLE instructors (
    instructor_id SERIAL PRIMARY KEY,
    name          VARCHAR(100) NOT NULL,
    active        BOOLEAN NOT NULL DEFAULT TRUE
);

CREATE TABLE classes (
    class_id     SERIAL PRIMARY KEY,
    name         VARCHAR(60) NOT NULL UNIQUE,
    level        VARCHAR(15) NOT NULL,
    duration_min SMALLINT NOT NULL CHECK (duration_min BETWEEN 15 AND 180)
);

CREATE TABLE members (
    member_id   SERIAL PRIMARY KEY,
    name        VARCHAR(100) NOT NULL,
    email       VARCHAR(120) NOT NULL UNIQUE,
    monthly_fee NUMERIC(6,2) NOT NULL CHECK (monthly_fee >= 0),
    active      BOOLEAN NOT NULL DEFAULT TRUE
);

CREATE TABLE sessions (
    session_id    SERIAL PRIMARY KEY,
    class_id      INTEGER NOT NULL REFERENCES classes(class_id)         ON DELETE RESTRICT,
    room_id       INTEGER NOT NULL REFERENCES rooms(room_id)            ON DELETE RESTRICT,
    instructor_id INTEGER NOT NULL REFERENCES instructors(instructor_id)ON DELETE RESTRICT,
    start_time    TIMESTAMPTZ NOT NULL,
    end_time      TIMESTAMPTZ NOT NULL,
    seats         SMALLINT NOT NULL CHECK (seats > 0),
    CONSTRAINT ck_session_time CHECK (end_time > start_time)
);

CREATE TABLE bookings (
    session_id INTEGER NOT NULL REFERENCES sessions(session_id) ON DELETE CASCADE,
    member_id  INTEGER NOT NULL REFERENCES members(member_id)   ON DELETE RESTRICT,
    booked_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
    status     VARCHAR(12) NOT NULL DEFAULT 'confirmed'
                 CHECK (status IN ('confirmed','cancelled','attended')),
    PRIMARY KEY (session_id, member_id)   -- one member, one booking per session
);

-- Two classes cannot occupy the same room at the same time
CREATE EXTENSION IF NOT EXISTS btree_gist;
ALTER TABLE sessions ADD CONSTRAINT ex_room_busy
    EXCLUDE USING gist (room_id WITH =, tstzrange(start_time, end_time) WITH &&);

-- An instructor cannot be in two places at once either
ALTER TABLE sessions ADD CONSTRAINT ex_instructor_busy
    EXCLUDE USING gist (instructor_id WITH =, tstzrange(start_time, end_time) WITH &&);

Two comments on the decisions:

PRIMARY KEY (session_id, member_id) solves for free the rule "a member cannot book the same session twice". It is the ideal case: a business rule that turns into the primary key.
Capacity is not a CHECK. "No more bookings than seats" requires counting rows, and a CHECK cannot. sessions.seats copies the room's capacity at the moment of scheduling it (so that fewer seats than the real capacity can be offered), and the control is done in the booking transaction with SELECT ... FOR UPDATE on the session —exactly the last-seat problem you will solve in 07-04.
The two EXCLUDE constraints are the same pattern as exercise 5 applied to time intervals instead of to validity periods.

Solution B — Donations and withdrawals in BiblioRed

CREATE TABLE suppliers (
    supplier_id SERIAL PRIMARY KEY,
    name        VARCHAR(120) NOT NULL,
    tax_id      VARCHAR(12) UNIQUE,
    contact     VARCHAR(120)
);

CREATE TABLE donors (
    donor_id  SERIAL PRIMARY KEY,
    type      VARCHAR(12) NOT NULL CHECK (type IN ('individual','organization')),
    name      VARCHAR(150) NOT NULL,
    email     VARCHAR(120),
    anonymous BOOLEAN NOT NULL DEFAULT FALSE
);

CREATE TABLE acquisitions (
    acquisition_id   SERIAL PRIMARY KEY,
    copy_id          INTEGER NOT NULL UNIQUE REFERENCES copies(copy_id) ON DELETE CASCADE,
    channel          VARCHAR(12) NOT NULL,
    acquisition_date DATE NOT NULL,
    supplier_id      INTEGER REFERENCES suppliers(supplier_id) ON DELETE RESTRICT,
    donor_id         INTEGER REFERENCES donors(donor_id)       ON DELETE RESTRICT,
    source_library   VARCHAR(150),
    invoice_number   VARCHAR(30),
    cost             NUMERIC(8,2),
    CONSTRAINT ck_acq_channel CHECK (channel IN ('purchase','donation','exchange')),
    CONSTRAINT ck_acq_consistency CHECK (
        (channel = 'purchase' AND supplier_id IS NOT NULL AND donor_id IS NULL
                              AND invoice_number IS NOT NULL AND cost IS NOT NULL)
     OR (channel = 'donation' AND donor_id IS NOT NULL AND supplier_id IS NULL)
     OR (channel = 'exchange' AND source_library IS NOT NULL
                              AND supplier_id IS NULL AND donor_id IS NULL)
    )
);

CREATE TABLE withdrawals (
    withdrawal_id   SERIAL PRIMARY KEY,
    copy_id         INTEGER NOT NULL UNIQUE REFERENCES copies(copy_id) ON DELETE RESTRICT,
    withdrawal_date DATE NOT NULL DEFAULT CURRENT_DATE,
    reason          VARCHAR(20) NOT NULL
                      CHECK (reason IN ('damage','obsolete','duplicate','loss','low_demand')),
    destination     VARCHAR(20) NOT NULL
                      CHECK (destination IN ('charity_sale','recycling','transfer','destruction')),
    authorized_by   VARCHAR(80) NOT NULL,
    notes           TEXT
);

CREATE INDEX idx_acq_channel ON acquisitions (channel, acquisition_date);
CREATE INDEX idx_wdr_date    ON withdrawals (withdrawal_date DESC);

The decisions you have to be able to defend:

UNIQUE (copy_id) in both tables turns the relationship into an optional 1:1: a copy has at most one recorded origin and at most one withdrawal. Without that UNIQUE it would be 1:N and a copy could appear as donated twice.
The per-channel consistency CHECK is the most valuable piece of design: it makes a purchase with no invoice or a donation with a supplier impossible. The alternative —three child tables acquisitions_purchase, acquisitions_donation, acquisitions_exchange with the hierarchy pattern of exercise 4— is conceptually cleaner and heavier to query. With three subtypes of two or three columns each, the CHECK wins.
withdrawals → copies is RESTRICT and acquisitions → copies is CASCADE. It is not an inconsistency: if the copy is deleted from the database, its provenance stops mattering, but a withdrawal is an administrative act that must survive and prevent the deletion.
copies is not modified. The withdrawn status already existed; all the withdrawal adds is the documentation of why. Any earlier query keeps working word for word.

Self-Assessment Rubric

Score your design of each exercise with this list. A correct design meets the first eight points; the last two separate a correct design from a good one.

#	Criterion	How to check it
1	Every question in the task can be answered	Write the query for each question. If any of them needs a datum that is in no table, the design is incomplete
2	No multivalued attribute	Look for columns that may contain "several" values: comma-separated lists, `phone1`/`phone2`, text fields with separators
3	Every table has a primary key	No exceptions, including the N:M junction tables
4	Every relationship is materialized with a declared foreign key	"The application takes care of it" is not good enough. If the engine does not declare it, it is not guaranteed
5	Every foreign key has an `ON DELETE` action decided consciously	Go through the list and justify them one by one: `CASCADE`, `RESTRICT`, `SET NULL`, `SET DEFAULT` or `NO ACTION`
6	Every business rule of the task has its constraint... or its note	Make the list of rules in the task and mark next to each one: `CHECK`, `UNIQUE`, partial index, `EXCLUDE`, trigger or "application's responsibility"
7	Closed sets of values are constrained	Every `status`, `type` or `reason` must have a `CHECK IN (...)`, a domain or a catalog table
8	Consistent naming	Same language, same number (singular/plural), same `_id` suffix style, same constraint-name style
9	Historical data cannot be destroyed with an `UPDATE`	Prices, fares and invoiced amounts frozen in the row that used them; validity periods as new rows, not as modifications
10	There are indexes for the frequent queries of the task	At least the foreign keys used in `JOIN`s and the columns of the usual filters

How to score. If you fail point 1, go back to the task: the design is no use. If you fail 2, 3 or 4, you have a structural problem. Points 5 to 8 are the ones that separate a student's schema from a production one. Points 9 and 10 apply only to the tasks that mention them.

Conclusion

You have designed five complete schemas from scratch: a video rental store with two N:M relationships —one of them with an attribute of its own—, a BiblioRed extension that had to fit a production schema without touching it, a course platform with a content hierarchy and a self-reference, an auto repair shop with a generalization hierarchy and a ternary relationship, and a fare system where the key includes time. In all five, the real work was not in the CREATE TABLE but in the two decisions preceding it: what is an entity and what is an attribute, and which business rule the engine can guarantee and which it cannot.

Go over the list of tools you have used to encode rules: single-column and multi-column CHECKs, composite UNIQUE, partial unique index (one rented copy, one active enrollment, one correct option), composite foreign key with a discriminator to close a hierarchy, EXCLUDE USING gist for intervals that must not overlap, and domains for reusable sets of values. And remember the three rules none of them could express —the maximum of active requests, the capacity of a session and the totality of a hierarchy—, because knowing where the declarative ends is as important as knowing how to use it.

In 07-03, Normalization Exercises, the work is inverted. Instead of starting from requirements and arriving at tables, you will start from tables that already exist and that are wrong: flat listings with repeated data, histories with the member's name copied into every row, tables where changing a phone number forces you to touch fourteen records. You will have to write out their functional dependencies, compute closures, find every candidate key, say which normal form they are in and why, and decompose them step by step with the SQL that performs the migration. No requirements: just tables, data and the formal analysis that reveals what they are hiding.

Schema Design Exercises

Before You Begin

Contents

Exercise 1: Neighborhood video rental store

Solution

Expected result

Explanation and debatable decisions

Exercise 2: Interlibrary loan in BiblioRed

Solution

Expected result

Explanation and debatable decisions

Exercise 3: Online course platform

Solution

Expected result

Explanation and debatable decisions

Exercise 4: Auto repair shop with a hierarchy and a ternary relationship

Solution

Expected result

Explanation and debatable decisions

Exercise 5: Fares with time-bounded validity

Solution

Expected result

Explanation and debatable decisions

Common Mistakes and Tips

Exercises

Exercise A: Municipal gym

Exercise B: BiblioRed extension — donations and withdrawals

Solutions

Solution A — Gym

Solution B — Donations and withdrawals in BiblioRed

Self-Assessment Rubric

Conclusion

Database Fundamentals

Module 1: Introduction to Databases

Module 2: Relational Databases

Module 3: Non-Relational Databases

Module 4: Schema Design

Module 5: Normalization

Module 6: Transactions, Performance, and Security

Module 7: Practical Exercises

Module 8: Case Studies

Module 9: Additional Resources