The Project | About Us | Contribute | Donations | License
HOME My courses Completed
◄ Previous
◄

Transforming ER Diagrams into Relational Schemas

Next ►
►

In the previous lesson we closed the ER diagram of the extended BiblioRed: twenty-one entities, their relationships with cardinality and participation, a total and disjoint material hierarchy, nine recorded decisions and a validation against the twelve business queries. It is a complete conceptual model and it still cannot be executed.

This lesson covers the bridge. Transforming an ER model into a relational schema is, unlike almost everything else in design, an algorithm: a set of mechanical rules that, applied in order, produce the tables. There are ten rules. Eight are deterministic —given the diagram, there is only one correct answer—, and two (rule 6, 1:1 relationships, and rule 10, hierarchies) present real alternatives you have to choose between with judgment. That is precisely why they are the two that take up the most space.

The deliverable is the complete CREATE TABLE script for the BiblioRed extension, fitting in with the seven existing tables, with its foreign keys and referential actions reasoned out according to what we learned in 02-06. A warning right away, because it is the hook for the next lesson: the data types we will use here are provisional. VARCHAR(200), INTEGER, NUMERIC set by default so that the script works. Lesson 04-04 reviews them one by one and adds the complete catalog of constraints.

Contents

  1. What the transformation algorithm is and what it does not solve
  2. Rule 1 — Strong entity → table
  3. Rule 2 — Composite attribute → simple columns
  4. Rule 3 — Multivalued attribute → a separate table
  5. Rule 4 — Derived attribute → not stored
  6. Rule 5 — 1:N relationship → foreign key on the N side
  7. Rule 6 — 1:1 relationship → three options
  8. Rule 7 — N:M relationship → junction table
  9. Rule 8 — Weak entity → composite primary key
  10. Rule 9 — Ternary relationship → table with three foreign keys
  11. Rule 10 — Generalization hierarchy → three strategies
  12. Deliverable: the complete script for the extended BiblioRed
  13. Post-review: validating the resulting schema
  14. Common Mistakes and Tips
  15. Exercises
  16. Conclusion

  1. What the transformation algorithm is and what it does not solve

The algorithm is applied in a specific order, and the order matters because each step depends on the previous one having created the tables to point at:

flowchart TD
    A["1. Strong entities -> tables with their PK"]
    B["2-4. Attributes: composite, multivalued, derived"]
    C["8. Weak entities -> composite PK with the owner's"]
    D["5. 1:N relationships -> FK on the N side"]
    E["6. 1:1 relationships -> choose between three options"]
    F["7. N:M relationships -> junction table"]
    G["9. Ternary relationships -> table with three FKs"]
    H["10. Hierarchies -> choose a strategy"]
    I["Review: queries, tables with no key, orphans"]
    A --> B --> C --> D --> E --> F --> G --> H --> I

What the algorithm guarantees: a correct relational schema, with no loss of information and no invented relationships. If the diagram was faithful to the domain, the schema will be too.

What the algorithm does NOT solve —worth being clear about so you do not expect magic—:

It does not solve Where it is solved
A badly drawn diagram. If the cardinality was wrong, the FK will end up on the wrong side By reviewing the diagram (04-02)
The choice of specific data types Lesson 04-04
Business rules that are not structural (BR1–BR10) Lesson 04-04
Redundancy anomalies left over in the design Module 5, normalization
Performance Lesson 06-03, indexes

An important nuance: the result of applying the algorithm to a well-made diagram is usually already in third normal form. Not by chance: thinking in terms of entities and relationships is, informally, applying the same principles that normalization formalizes. In module 5 we will verify it with proper instruments.

  1. Rule 1 — Strong entity → table

Rule 1. Each strong entity becomes a table. Its simple attributes become columns. Its identifier becomes the primary key.

It is the most direct rule and the one that creates the skeleton. Applied to rooms, event_types, events and speakers (no foreign keys yet; they arrive with rule 5):

CREATE TABLE event_types (
    event_type_id         INTEGER GENERATED BY DEFAULT AS IDENTITY,
    code                  VARCHAR(30)  NOT NULL,
    name                  VARCHAR(80)  NOT NULL,
    description           VARCHAR(500),
    standard_duration_min INTEGER,
    CONSTRAINT pk_event_types PRIMARY KEY (event_type_id),
    CONSTRAINT uq_event_types_code UNIQUE (code)
);

Three decisions already taken and applied here:

  1. Surrogate key event_type_id, following the rule we set in section 10 of 04-01: a surrogate PK in every strong entity.
  2. UNIQUE on the natural key code. This is what prevents two 'workshop' rows from existing. Without this UNIQUE, the surrogate key would have destroyed a guarantee that the conceptual model did give.
  3. Named constraints (pk_, uq_), for reasons we will see in 04-04 about error messages.

And speakers, with the same structure:

CREATE TABLE speakers (
    speaker_id INTEGER GENERATED BY DEFAULT AS IDENTITY,
    first_name VARCHAR(60)  NOT NULL,
    last_name  VARCHAR(80)  NOT NULL,
    email      VARCHAR(120),
    biography  VARCHAR(1000),
    external   BOOLEAN      NOT NULL DEFAULT TRUE,
    CONSTRAINT pk_speakers PRIMARY KEY (speaker_id),
    CONSTRAINT uq_speakers_email UNIQUE (email)
);

Notice that email is not NOT NULL but it is UNIQUE. It is a deliberate combination: there are external speakers for whom only a phone number is known, but two speakers cannot share an address. The fact that PostgreSQL allows several NULLs in a UNIQUE column is what makes this combination viable, and we will explain it in depth in 04-04.

  1. Rule 2 — Composite attribute → simple columns

Rule 2. A composite attribute is broken down: each component is a column. The composite attribute itself disappears; no trace of it is left in the schema.

R13 asked for the branch address to be broken down. The existing table has:

-- BEFORE
branches (branch_id, name, address, phone, opening_date)

The transformation:

ALTER TABLE branches ADD COLUMN addr_street      VARCHAR(120);
ALTER TABLE branches ADD COLUMN addr_number      VARCHAR(10);
ALTER TABLE branches ADD COLUMN addr_postal_code VARCHAR(5);
ALTER TABLE branches ADD COLUMN addr_city        VARCHAR(60);

And the migration of the existing data, which is the part nobody talks about and always costs you:

-- The four current values, migrated by hand: they are four rows.
UPDATE branches SET addr_street = 'Plaça Major',      addr_number = '3',
                    addr_postal_code = '08820', addr_city = 'Vallmar'
 WHERE branch_id = 1;
UPDATE branches SET addr_street = 'Avinguda del Nord', addr_number = '112',
                    addr_postal_code = '08821', addr_city = 'Vallmar'
 WHERE branch_id = 2;
-- ... branches 3 and 4

ALTER TABLE branches DROP COLUMN address;
UPDATE 1
UPDATE 1
ALTER TABLE

Practical tip: when the composite attribute already has production data as free text, automatic conversion with regular expressions fails more often than it succeeds. With four rows you do it by hand. With forty thousand, you do it in batches, review whatever does not fit the pattern and keep the original column renamed to address_original for a couple of months.

When NOT to break it down

The rule has an important exception and it is frequently misapplied out of excessive zeal. Do not break it down if nobody is going to search, filter, sort or aggregate by the parts.

Case Break it down? Reason
Branch address (R13) Yes It is searched by postal code, the city is displayed separately
Postal address of an external speaker No It is only printed on a letter; a VARCHAR is enough
First and last name of a member Yes (already done) Listings are sorted by last name
Notes on an event report No Free text by nature
hh:mm duration of a DVD No: it is a single number of minutes Splitting it into hours and minutes complicates every calculation

The cost of over-decomposing is real: four columns to fill in, validate and maintain, for a value that is only ever printed whole. The cost of under-decomposing is worse —you have to slice strings in every query—, and that is why when in doubt you decompose; but the doubt must exist.

  1. Rule 3 — Multivalued attribute → a separate table

Rule 3. A multivalued attribute becomes a new table with two parts: a foreign key to the owner entity and the value itself. The primary key is the combination of both.

It is the rule that makes the phone1, phone2, phone3 and subtitles = 'es,ca,en' anti-patterns we denounced in 04-01 disappear forever.

A member's phone numbers (R12)

CREATE TABLE member_phones (
    member_id INTEGER      NOT NULL,
    number    VARCHAR(20)  NOT NULL,
    type      VARCHAR(10)  NOT NULL,
    CONSTRAINT pk_member_phones PRIMARY KEY (member_id, number),
    CONSTRAINT fk_member_phones_member
        FOREIGN KEY (member_id) REFERENCES members (member_id)
        ON DELETE CASCADE ON UPDATE CASCADE
);

Analysis of the three decisions:

  • The PK is (member_id, number). The value is part of the key, and that is what prevents storing the same phone number twice for the same member. It is integrity for free: the rule "do not repeat a phone number" needs no code.
  • ON DELETE CASCADE: a phone number has no existence outside its member. If the member is deleted, their phone numbers must go with them. It is the textbook case for CASCADE according to the criteria in 02-06.
  • R12's limit of three phone numbers is not here. No key or table constraint expresses it. It is a pending business rule, and in 04-04 we will decide where it lives.

Let us test it:

INSERT INTO member_phones (member_id, number, type) VALUES
    (14, '600111222', 'mobile'),
    (14, '938880011', 'landline'),
    (15, '600333444', 'mobile');

INSERT INTO member_phones (member_id, number, type) VALUES (14, '600111222', 'work');
INSERT 0 3
ERROR:  duplicate key value violates unique constraint "pk_member_phones"
DETAIL:  Key (member_id, number)=(14, 600111222) already exists.

Exactly the error we wanted: the same number cannot be recorded twice for the same member, not even by changing its type.

The subtitles of a DVD (R1)

CREATE TABLE dvd_subtitles (
    material_id INTEGER     NOT NULL,
    language    VARCHAR(5)  NOT NULL,
    CONSTRAINT pk_dvd_subtitles PRIMARY KEY (material_id, language),
    CONSTRAINT fk_dvd_subtitles_material
        FOREIGN KEY (material_id) REFERENCES materials_dvd (material_id)
        ON DELETE CASCADE ON UPDATE CASCADE
);

Note the detail: the foreign key points at materials_dvd, not at materials. It is the sub-entity that has subtitles, not just any material. An audiobook cannot have them, and the schema makes it impossible. That precision is one of the advantages of the hierarchy strategy we will choose in rule 10.

And now query Q9 ("DVDs with Catalan subtitles") is answered with a normal JOIN instead of with a LIKE '%ca%' that returned garbage:

SELECT m.material_id, m.title
  FROM materials m
  JOIN dvd_subtitles s ON s.material_id = m.material_id
 WHERE s.language = 'ca'
 ORDER BY m.title;
 material_id |             title
-------------+-------------------------------
        1204 | El bosc de les ombres
        1187 | La ciutat dels prodigis
(2 rows)

  1. Rule 4 — Derived attribute → not stored

Rule 4. A derived attribute generates no column. It is computed at the moment you query it.

Of the five derived attributes we noted down in 04-02, let us look at the most used one: the free seats of an event (Q2).

SELECT e.event_id,
       e.title,
       e.offered_seats,
       e.offered_seats - COALESCE(SUM(1 + r.companions), 0) AS free_seats
  FROM events e
  LEFT JOIN registrations r
         ON r.event_id = e.event_id
        AND r.status = 'confirmed'
 WHERE e.event_id = 47
 GROUP BY e.event_id, e.title, e.offered_seats;
 event_id |          title           | offered_seats | free_seats
----------+--------------------------+---------------+------------
       47 | Book club: crime fiction |            20 |          6
(1 row)

Three pieces worth pointing out, all of them familiar from earlier modules: the LEFT JOIN keeps the events with no registrations at all (02-04), the COALESCE turns the NULL from an empty SUM into 0 (02-01, three-valued logic) and 1 + companions implements the decision that a member with two companions takes three seats (an ambiguity resolved in 04-01).

The convenient form: a view

Repeating that query in twenty places is an invitation for it to be written wrongly in one of them. A view encapsulates it, and it is also a pure example of the ANSI/SPARC external level from 01-04:

CREATE VIEW v_events_occupancy AS
SELECT e.event_id,
       e.title,
       e.start_time,
       e.offered_seats,
       COALESCE(SUM(1 + r.companions) FILTER (WHERE r.status = 'confirmed'), 0) AS occupied_seats,
       e.offered_seats
         - COALESCE(SUM(1 + r.companions) FILTER (WHERE r.status = 'confirmed'), 0) AS free_seats
  FROM events e
  LEFT JOIN registrations r ON r.event_id = e.event_id
 GROUP BY e.event_id, e.title, e.start_time, e.offered_seats;

The view stores nothing: it is recomputed on every query. It still complies with rule 4.

When it does get stored

There are two situations in which a derived value ends up being stored, and both have a name:

  1. For performance. If the public agenda (Q1) shows the free seats of forty events and each one means aggregating thousands of registrations, it may pay off to maintain an occupied_seats column updated by a trigger. This is deliberate denormalization, it has a known cost (the risk of the stored value and the real one diverging) and it is the topic of lesson 05-04. Do not do it without measuring first.
  2. Because the value must be frozen. This case is different and gets confused with the previous one. A fine's amount is computed today at €0.20/day, but if the ordinance raises the rate tomorrow, yesterday's fines must not be recomputed. That amount is not a derived value: it is a historical fact that is stored because its value depends on a context that no longer exists. The same logic that justifies storing the sale price on an invoice.

In BiblioRed, fines.amount is of the second kind and that is why it is a column. free_seats is of the first kind and that is why it is not.

  1. Rule 5 — 1:N relationship → foreign key on the N side

Rule 5. In a 1:N relationship, the table on the N side receives a foreign key pointing at the primary key of the 1 side. The relationship's own attributes, if any, also go to the N side.

It is the rule applied most often in any schema. In BiblioRed:

CREATE TABLE rooms (
    room_id     INTEGER GENERATED BY DEFAULT AS IDENTITY,
    branch_id   INTEGER      NOT NULL,
    name        VARCHAR(80)  NOT NULL,
    capacity    INTEGER      NOT NULL,
    floor       INTEGER      NOT NULL DEFAULT 0,
    accessible  BOOLEAN      NOT NULL DEFAULT TRUE,
    CONSTRAINT pk_rooms PRIMARY KEY (room_id),
    CONSTRAINT uq_rooms_branch_name UNIQUE (branch_id, name),
    CONSTRAINT fk_rooms_branch
        FOREIGN KEY (branch_id) REFERENCES branches (branch_id)
        ON DELETE RESTRICT ON UPDATE CASCADE
);

Four things derived directly from the diagram:

  • branch_id goes in rooms, the N side. Never the other way round.
  • NOT NULL because the participation of ROOM was total: every room is in a branch (04-02, section 6).
  • UNIQUE (branch_id, name) implements R3's statement: the name is unique only within the branch. This is what remains of ROOM being conceptually weak; we will come back to it in rule 8.
  • ON DELETE RESTRICT: a branch that has rooms is not deleted. Consistent with the decision already taken for members.branch_id and copies.branch_id in 02-06.

Why never the other way round

The question is legitimate: why not put a room_id column in branches? Because a branch has several rooms, and only one value fits in a column. The only two ways of forcing it are the two anti-patterns from 04-01:

Attempt What it is Why it fails
branches.room1_id, room2_id, ... room6_id Numbered columns R3 says "between 1 and 6" today; the seventh arrives as soon as they refurbish a building. Massive NULL. Querying "which branch is room 12 in?" requires checking six columns
branches.rooms = '4,7,12' Comma-separated list No foreign key, no types, no JOIN, and a LIKE that returns false positives

The FK always goes on the side that holds at most one value. That side is the N side.

The case of the event with no room

events.room_id is different because decision D4 in 04-02 made the room optional:

    room_id INTEGER NULL,          -- partial participation: outdoor events
    CONSTRAINT fk_events_room
        FOREIGN KEY (room_id) REFERENCES rooms (room_id)
        ON DELETE RESTRICT ON UPDATE CASCADE

The participation in the diagram translates literally into NOT NULL or its absence. It is the most direct conversion in the whole algorithm, and that is why it is worth having asked that question properly in 04-02.

  1. Rule 6 — 1:1 relationship → three options

Rule 6. A 1:1 relationship admits three solutions: merge the two entities into one table, put the foreign key on one of the two sides with UNIQUE, or create an intermediate table. The choice depends on the participation and on the access pattern.

BiblioRed's case is events — event_reports (R9): an event has at most one report, a report belongs to one event.

Option A — Merge into one table

-- Option A: the report fields inside events
ALTER TABLE events ADD COLUMN actual_attendees INTEGER;
ALTER TABLE events ADD COLUMN average_rating   NUMERIC(3,2);
ALTER TABLE events ADD COLUMN notes            VARCHAR(2000);
ALTER TABLE events ADD COLUMN report_date      DATE;

In favor: no JOIN, a single row to read, maximum simplicity. Against: out of the 1,200 events forecast over three years, maybe 700 will have a report; the other 500 will carry four columns at NULL. And there is a worse problem than the waste: you cannot tell "report not written yet" from "report written with zero attendees". Both cases give actual_attendees IS NULL or = 0 depending on how it is filled in, and query Q12 ("events with no report") becomes ambiguous.

Option B — Foreign key with UNIQUE on one of the sides

-- Option B, chosen for BiblioRed
CREATE TABLE event_reports (
    event_id         INTEGER      NOT NULL,
    actual_attendees INTEGER      NOT NULL,
    average_rating   NUMERIC(3,2),
    notes            VARCHAR(2000),
    report_date      DATE         NOT NULL,
    CONSTRAINT pk_event_reports PRIMARY KEY (event_id),
    CONSTRAINT fk_event_reports_event
        FOREIGN KEY (event_id) REFERENCES events (event_id)
        ON DELETE CASCADE ON UPDATE CASCADE
);

Here the foreign key is the primary key. That is what makes the relationship 1:1: if event_id is the PK of event_reports, it cannot be repeated, and therefore an event cannot have two reports. No additional UNIQUE is needed: the primary key already is one.

In favor: zero NULLs, the existence of the row is the answer to "does it have a report?", and Q12 is solved with a clean anti-join:

SELECT e.event_id, e.title, e.start_time
  FROM events e
  LEFT JOIN event_reports r ON r.event_id = e.event_id
 WHERE e.status = 'held'
   AND e.start_time < CURRENT_DATE - INTERVAL '15 days'
   AND r.event_id IS NULL
 ORDER BY e.start_time;
 event_id |             title             |       start_time
----------+-------------------------------+------------------------
       31 | Creative writing workshop     | 2026-06-18 18:00:00+02
       38 | Book launch: "The Long Days"  | 2026-07-02 19:30:00+02
(2 rows)

Against: one JOIN when you need both together. In this case it does not matter: the report is consulted on a different screen from the agenda.

Option C — Intermediate table

A third table with two foreign keys, both UNIQUE. It only makes sense when both sides have partial participation and the relationship itself is a fact that can appear and disappear (for example, "which employee has which fleet vehicle assigned"). For the report it would be absurd: a report with no event does not exist.

Decision table

Criterion Option A: merge Option B: FK + UNIQUE Option C: intermediate table
Participation of both sides Total on both Total on one, partial on the other Partial on both
Nulls generated Many if one side is optional None None
JOINs needed None One Two
Telling "does not exist" from "equals zero" No Yes Yes
Large or rarely queried columns Penalizes every read Isolates the weight Isolates the weight
Complexity Minimal Low High

Practical rule: if both sides have total participation and are always queried together, merge (option A) —in fact, if that is your case, reconsider whether they really were two entities—. If one side is optional, FK on the optional side with the shared PK (option B). Option C is rare and has to be justified.

BiblioRed's decision: option B. The optional side is the report, and that is where the table goes.

  1. Rule 7 — N:M relationship → junction table

Rule 7. An N:M relationship becomes a junction table with two foreign keys, one to each entity. The relationship's own attributes become columns of that table. The primary key is, by default, the combination of the two foreign keys.

It is the rule most often applied wrongly, almost always by forgetting the second sentence.

The main case: registrations (R6)

CREATE TABLE registrations (
    event_id          INTEGER      NOT NULL,
    member_id         INTEGER      NOT NULL,
    registration_date TIMESTAMPTZ  NOT NULL DEFAULT now(),
    status            VARCHAR(15)  NOT NULL DEFAULT 'confirmed',
    companions        INTEGER      NOT NULL DEFAULT 0,
    CONSTRAINT pk_registrations PRIMARY KEY (event_id, member_id),
    CONSTRAINT fk_registrations_event
        FOREIGN KEY (event_id) REFERENCES events (event_id)
        ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_registrations_member
        FOREIGN KEY (member_id) REFERENCES members (member_id)
        ON DELETE RESTRICT ON UPDATE CASCADE
);

The three attributes of its own —date, status, companions— live here and nowhere else. It is the application of the rule we stated in 04-02: if an attribute needs both keys in order to have a value, it belongs to the relationship.

The composite primary key implements R6's rule for free:

INSERT INTO registrations (event_id, member_id) VALUES (47, 14);
INSERT INTO registrations (event_id, member_id, companions) VALUES (47, 14, 2);
INSERT 0 1
ERROR:  duplicate key value violates unique constraint "pk_registrations"
DETAIL:  Key (event_id, member_id)=(47, 14) already exists.

"A member cannot register twice for the same event", says R6. There it is, without a line of application code.

The two referential actions, reasoned out

This is a good moment to apply the criterion from 02-06, because the two foreign keys of the same table carry different actions and that puzzles a lot of people:

Foreign key Action Reason
event_id ON DELETE CASCADE A registration means nothing without its event. If a scheduled event is removed from the system, its registrations must disappear. It is the same criterion we applied to reservations.book_id
member_id ON DELETE RESTRICT The registration is a historical fact with value (attendance, statistics for Q5). Deleting a member must not delete the record that they attended twelve events. Same criterion as loans.member_id

Compare it with reservations.member_id, which is CASCADE: a reservation is ephemeral and has no historical value; a registration does. The referential action follows from the value of the data, not from the shape of the relationship.

Composite key or surrogate key?

It is the decision you have to make in every junction table:

Composite PK (event_id, member_id) Surrogate PK registration_id + UNIQUE (event_id, member_id)
Prevents duplicates Yes, directly Yes, with the UNIQUE (if you remember to add it)
Referenceable from another table With a composite FK, heavier With a single column, more convenient
Semantics The key is the business rule The key means nothing
Size 8 bytes 4 bytes + the UNIQUE index
ORM compatible Some ORMs handle it badly Universal

BiblioRed's decision: composite PK in registrations, participations and events_materials. No other table needs to reference them, and the composite key expresses the business rule directly.

The other two N:M relationships

events_materials (R8) is the simplest case in the schema, an N:M with a single attribute of its own:

CREATE TABLE events_materials (
    event_id    INTEGER      NOT NULL,
    material_id INTEGER      NOT NULL,
    role        VARCHAR(15)  NOT NULL DEFAULT 'recommended',
    CONSTRAINT pk_events_materials PRIMARY KEY (event_id, material_id),
    CONSTRAINT fk_events_materials_event
        FOREIGN KEY (event_id) REFERENCES events (event_id)
        ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_events_materials_material
        FOREIGN KEY (material_id) REFERENCES materials (material_id)
        ON DELETE CASCADE ON UPDATE CASCADE
);

And participations (R7) is really rule 9; it arrives in two sections' time.

  1. Rule 8 — Weak entity → composite primary key

Rule 8. A weak entity becomes a table whose primary key is the combination of the owner entity's primary key and its partial identifier. The foreign key to the owner is part of the primary key and is, necessarily, NOT NULL.

We have already applied it three times without saying so: member_phones (rule 3), event_reports (rule 6) and registrations (rule 7). The pure case is that of copies with respect to the material.

The orthodox version

-- Literal application of rule 8
CREATE TABLE copies (
    material_id   INTEGER     NOT NULL,
    copy_number   INTEGER     NOT NULL,     -- partial identifier
    code          VARCHAR(15) NOT NULL,
    branch_id     INTEGER     NOT NULL,
    status        VARCHAR(20) NOT NULL,
    acquisition_date DATE,
    CONSTRAINT pk_copies PRIMARY KEY (material_id, copy_number),
    ...
);

It is correct and expresses exactly the semantics: "copy 3 of The Map of Time".

Why BiblioRed does not use it

There is a specific and decisive reason: loans references copies. With the composite PK, loans would need two columns and a composite foreign key:

-- What the orthodox version would imply
CREATE TABLE loans (
    loan_id      INTEGER GENERATED BY DEFAULT AS IDENTITY,
    member_id    INTEGER NOT NULL,
    material_id  INTEGER NOT NULL,     -- two columns
    copy_number  INTEGER NOT NULL,     -- to identify one copy
    ...
    CONSTRAINT fk_loans_copy
        FOREIGN KEY (material_id, copy_number)
        REFERENCES copies (material_id, copy_number)
);

And besides, loans already exists with 4,312 rows and an FK to copy_id. Changing it would be a considerable migration for no gain at all.

BiblioRed's decision: surrogate key + UNIQUE on the weak key. It is the usual pattern and it preserves all the integrity:

CREATE TABLE copies (
    copy_id          INTEGER GENERATED BY DEFAULT AS IDENTITY,
    code             VARCHAR(15) NOT NULL,
    material_id      INTEGER     NOT NULL,
    copy_number      INTEGER     NOT NULL,
    branch_id        INTEGER     NOT NULL,
    status           VARCHAR(20) NOT NULL DEFAULT 'available',
    acquisition_date DATE,
    CONSTRAINT pk_copies PRIMARY KEY (copy_id),
    CONSTRAINT uq_copies_code UNIQUE (code),
    CONSTRAINT uq_copies_material_num UNIQUE (material_id, copy_number),
    CONSTRAINT fk_copies_material
        FOREIGN KEY (material_id) REFERENCES materials (material_id)
        ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_copies_branch
        FOREIGN KEY (branch_id) REFERENCES branches (branch_id)
        ON DELETE RESTRICT ON UPDATE CASCADE
);

The combination is: copy_id as the primary key (convenient for referencing), uq_copies_material_num as the natural candidate key that preserves the weak-entity semantics, and uq_copies_code for the code printed on the label. Nothing has been lost.

The general principle: in the logical phase, a weak entity may carry a surrogate key without ceasing to be weak, provided its composite natural key is declared UNIQUE. What is not negotiable is the uniqueness; the shape of the primary key is.

The case of rooms is identical: PK room_id, UNIQUE (branch_id, name). And so is that of payments: PK payment_id, with the peculiarity that there is not even a natural partial identifier there (two payments on the same fine for the same amount on the same day are two different payments), which is an additional argument for the surrogate.

  1. Rule 9 — Ternary relationship → table with three foreign keys

Rule 9. A degree-3 relationship becomes a table with three foreign keys, one to each participating entity, plus the relationship's own attributes. The primary key is, by default, the combination of the three.

And a warning that is as important as the rule: before applying it, check that the relationship really is ternary.

The decomposition test

A ternary relationship R(A, B, C) is genuine if the existence of a triple cannot be deduced from three binary relationships. The test consists of asking whether R(A,B,C) is equivalent to R1(A,B) ∧ R2(B,C) ∧ R3(A,C).

A classic and mistaken counterexample: "a member borrows a copy at a branch" looks ternary, but it is not: the branch is deduced from the copy. It is a binary relationship with a derived value. Modeling it as ternary introduces redundancy and the possibility that the branch recorded on the loan contradicts the copy's.

A genuinely ternary case: "a supplier supplies a component for a specific project". That supplier A supplies component X, that X is used in project P and that A works with P does not imply that A supplies X for P. The information would be lost by decomposing it.

BiblioRed's case: participations (R7)

In 04-02 (decision D5) we analyzed EVENT — SPEAKER — ROLE. The conclusion was that ROLE is not an entity: it is a small, stable set with no attributes of its own. What is left is a binary N:M relationship whose identifier includes an attribute:

CREATE TABLE participations (
    event_id    INTEGER       NOT NULL,
    speaker_id  INTEGER       NOT NULL,
    role        VARCHAR(25)   NOT NULL,
    fee         NUMERIC(8,2)  NOT NULL DEFAULT 0,
    CONSTRAINT pk_participations PRIMARY KEY (event_id, speaker_id, role),
    CONSTRAINT fk_participations_event
        FOREIGN KEY (event_id) REFERENCES events (event_id)
        ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_participations_speaker
        FOREIGN KEY (speaker_id) REFERENCES speakers (speaker_id)
        ON DELETE RESTRICT ON UPDATE CASCADE
);

The three-column primary key is what allows Elena Roig to be both moderator and workshop leader at the same event —R7 required it— and at the same time prevents her from appearing twice as moderator of the same event.

INSERT INTO participations (event_id, speaker_id, role, fee) VALUES
    (47, 8, 'moderator',    0),
    (47, 8, 'workshop_leader', 180.00),
    (47, 9, 'guest_author', 250.00);
INSERT 0 3
INSERT INTO participations (event_id, speaker_id, role, fee)
VALUES (47, 8, 'moderator', 50.00);
ERROR:  duplicate key value violates unique constraint "pk_participations"
DETAIL:  Key (event_id, speaker_id, role)=(47, 8, moderator) already exists.

speaker_id is RESTRICT and not CASCADE for a very specific reason: fees are a record with financial implications. Deleting a speaker cannot delete the trace of what was paid to them.

Tip: when a degree-3 relationship shows up in a design, spend five minutes on the decomposition test. In practical experience, four out of five turn out to be a binary relationship with an attribute, a binary relationship with a derived value, or two independent binary relationships.

  1. Rule 10 — Generalization hierarchy → three strategies

Rule 10. A generalization hierarchy is taken into tables with one of three strategies: single table, table per subclass or table per concrete class. The choice depends on disjointness, totality, the number of specific attributes and the query pattern.

It is the most important decision in the whole BiblioRed extension, because it affects the entire catalog and the tables that already exist.

Strategy 1 — Single table (single table inheritance)

A single table with all the attributes of all the subclasses, plus a discriminant column.

CREATE TABLE materials (
    material_id     INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    material_type   VARCHAR(15) NOT NULL,
    title           VARCHAR(200) NOT NULL,
    -- ... common attributes ...
    isbn            VARCHAR(13),   -- books only
    page_count      INTEGER,       -- books only
    binding         VARCHAR(20),   -- books only
    duration_min    INTEGER,       -- DVDs and audiobooks
    video_format    VARCHAR(15),   -- DVDs only
    region_code     INTEGER,       -- DVDs only
    issn            VARCHAR(9),    -- magazines only
    number          VARCHAR(20),   -- magazines only
    frequency       VARCHAR(20),   -- magazines only
    narrator        VARCHAR(120),  -- audiobooks only
    audio_format    VARCHAR(15)    -- audiobooks only
);

Fast to query (no JOIN) and disastrous to validate: it is impossible to declare isbn NOT NULL even though R1 requires it for books, because DVDs would have it at NULL. The only way out is conditional CHECKs along the lines of CHECK (material_type <> 'book' OR isbn IS NOT NULL), one per mandatory attribute, which multiply rapidly.

Strategy 2 — Table per subclass (class table inheritance)

One table for the superclass with the common part, and one table per subclass with the specific part, joined by the shared primary key.

CREATE TABLE materials (
    material_id   INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    material_type VARCHAR(15)  NOT NULL,
    title         VARCHAR(200) NOT NULL,
    author_id     INTEGER,
    publisher     VARCHAR(120),
    publication_year INTEGER,
    language      VARCHAR(5)   NOT NULL,
    added_date    DATE         NOT NULL DEFAULT CURRENT_DATE
);

CREATE TABLE materials_book (
    material_id INTEGER     NOT NULL PRIMARY KEY REFERENCES materials (material_id) ON DELETE CASCADE,
    isbn        VARCHAR(13) NOT NULL UNIQUE,          -- now it can be required
    page_count  INTEGER,
    binding     VARCHAR(20)
);

Zero unnecessary nulls, specific constraints that can be enforced, and the foreign key of copies points at materials, which is what Q10, loans and reservations needed. Its cost is a JOIN when you need the subtype's details.

Strategy 3 — Table per concrete class (concrete table inheritance)

A complete, independent table for each subclass, with no superclass table: books, dvds, magazines, audiobooks, each with all the attributes, common and specific.

It is the worst option for BiblioRed and it is worth understanding exactly why: copies would have nothing to point at. It would need four columns (book_id, dvd_id, magazine_id, audiobook_id) with three at NULL in every row, or a (type, id) pair with no possible foreign key —an anti-pattern known as a polymorphic reference, which gives up referential integrity—. And query Q10 would require a UNION of four branches. It is only viable when the subclasses are not referenced from outside, and here they are referenced from three places.

Comparison table

Criterion Single table Table per subclass Table per concrete class
No. of tables 1 1 + n n
Nulls Many None None
NOT NULL on subclass attributes Impossible (only conditional CHECK) Direct Direct
Querying all materials Trivial Trivial (the superclass) UNION of n branches
Querying one subtype with its details Trivial One JOIN Trivial
Referencing from outside (copies, reservations) Trivial Trivial (to the superclass) Impossible without giving up the FK
Adding a new subtype ALTER TABLE on the big table A new table, nothing existing changes A new table + review every UNION
Discriminant ↔ subtype consistency Automatic Has to be guaranteed Not applicable
Requires a disjoint hierarchy No No Yes
Requires a total hierarchy No No Yes
When to choose it Few specific attributes (2-3), subtypes that barely differ, absolute priority on reads Many specific attributes, subtypes referenced from outside, integrity matters Subtypes with almost nothing in common and that nobody references

BiblioRed's decision: table per subclass

Four reasons, in order of weight:

  1. copies, loans and reservations need a common entity to point at. That rules out strategy 3 on its own.
  2. Each subtype has 3-4 mandatory specific attributes. With a single table that would be eleven mostly null columns and eleven conditional CHECKs.
  3. R1 requires isbn NOT NULL UNIQUE for books and issn for magazines. Only strategy 2 allows that directly.
  4. Adding "maps" or "sheet music" in the future means creating a table, without touching anything existing. It is exactly goal 5 from 04-01, evolution without traumatic migrations.

The price: keeping the discriminant consistent

Strategy 2 has a known hole and it has to be acknowledged: nothing on its own prevents a material with material_type = 'book' from having a row in materials_dvd, or from having no row at all in materials_book (violating the totality of the hierarchy).

It is closed with an elegant technique: include the discriminant in the foreign key. You declare UNIQUE (material_id, material_type) on materials and the subtable references that pair with its own material_type fixed by a CHECK. It is a direct application of the composite foreign keys from 02-06:

ALTER TABLE materials
    ADD CONSTRAINT uq_materials_id_type UNIQUE (material_id, material_type);

CREATE TABLE materials_book (
    material_id   INTEGER     NOT NULL,
    material_type VARCHAR(15) NOT NULL DEFAULT 'book',
    isbn          VARCHAR(13) NOT NULL,
    page_count    INTEGER,
    binding       VARCHAR(20),
    CONSTRAINT pk_materials_book PRIMARY KEY (material_id),
    CONSTRAINT uq_materials_book_isbn UNIQUE (isbn),
    CONSTRAINT chk_materials_book_type CHECK (material_type = 'book'),
    CONSTRAINT fk_materials_book_material
        FOREIGN KEY (material_id, material_type)
        REFERENCES materials (material_id, material_type)
        ON DELETE CASCADE ON UPDATE CASCADE
);

Now it is physically impossible for a DVD to have a row in materials_book:

-- material 1204 is a DVD
INSERT INTO materials_book (material_id, isbn) VALUES (1204, '9788401339097');
ERROR:  insert or update on table "materials_book" violates foreign key constraint "fk_materials_book_material"
DETAIL:  Key (material_id, material_type)=(1204, book) is not present in table "materials".

The other half of the hierarchy —that every material has a row in some subtable, the totality— cannot be guaranteed with declarative constraints. It is the same limitation we saw in 04-02 with "every branch has at least one room". It is solved with a trigger, with a transaction that inserts both rows together, or by accepting the risk and checking it periodically. In 04-04 we settle the criterion.

Compatibility: books becomes a view

All the existing code and all the existing reports query books. Rewriting them all is unnecessary:

CREATE VIEW books AS
SELECT m.material_id AS book_id,
       b.isbn,
       m.title,
       m.author_id,
       m.publisher,
       m.publication_year,
       m.language
  FROM materials m
  JOIN materials_book b ON b.material_id = m.material_id;

The old queries keep working without a single change. It is the clearest example of logical independence in the whole course: we have changed the conceptual level and the external level has absorbed the change, exactly as ANSI/SPARC described in 01-04.

  1. Deliverable: the complete script for the extended BiblioRed

Here is the result of applying the ten rules. Remember that the types are provisional: VARCHAR(n) sizes set by eye, INTEGER for everything whole, constraints limited to the structural ones. Lesson 04-04 reviews every type and adds the complete catalog of CHECK, DEFAULT, domains and generated columns.

-- =====================================================================
-- BiblioRed · Migration V004: events, materials and fines extension
-- PROVISIONAL types. Reviewed in 04-04.
-- =====================================================================

-- ---------------------------------------------------------------------
-- BLOCK 1 · Rule 2: composite attribute (R13)
-- ---------------------------------------------------------------------
ALTER TABLE branches ADD COLUMN addr_street      VARCHAR(120);
ALTER TABLE branches ADD COLUMN addr_number      VARCHAR(10);
ALTER TABLE branches ADD COLUMN addr_postal_code VARCHAR(5);
ALTER TABLE branches ADD COLUMN addr_city        VARCHAR(60);
-- (data migration and DROP COLUMN address: see section 3)

-- ---------------------------------------------------------------------
-- BLOCK 2 · Rule 10: material hierarchy (R1)
-- ---------------------------------------------------------------------
CREATE TABLE materials (
    material_id      INTEGER GENERATED BY DEFAULT AS IDENTITY,
    material_type    VARCHAR(15)  NOT NULL,
    title            VARCHAR(200) NOT NULL,
    author_id        INTEGER,
    publisher        VARCHAR(120),
    publication_year INTEGER,
    language         VARCHAR(5)   NOT NULL,
    added_date       DATE         NOT NULL DEFAULT CURRENT_DATE,
    CONSTRAINT pk_materials       PRIMARY KEY (material_id),
    CONSTRAINT uq_materials_id_type UNIQUE (material_id, material_type),
    CONSTRAINT chk_materials_type
        CHECK (material_type IN ('book','dvd','magazine','audiobook')),
    CONSTRAINT fk_materials_author
        FOREIGN KEY (author_id) REFERENCES authors (author_id)
        ON DELETE SET NULL ON UPDATE CASCADE      -- same as books.author_id
);

CREATE TABLE materials_book (
    material_id   INTEGER     NOT NULL,
    material_type VARCHAR(15) NOT NULL DEFAULT 'book',
    isbn          VARCHAR(13) NOT NULL,
    page_count    INTEGER,
    binding       VARCHAR(20),
    CONSTRAINT pk_materials_book   PRIMARY KEY (material_id),
    CONSTRAINT uq_materials_book_isbn UNIQUE (isbn),
    CONSTRAINT chk_materials_book_type CHECK (material_type = 'book'),
    CONSTRAINT fk_materials_book_material
        FOREIGN KEY (material_id, material_type)
        REFERENCES materials (material_id, material_type)
        ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE materials_dvd (
    material_id   INTEGER     NOT NULL,
    material_type VARCHAR(15) NOT NULL DEFAULT 'dvd',
    duration_min  INTEGER     NOT NULL,
    video_format  VARCHAR(15),
    region_code   INTEGER,
    CONSTRAINT pk_materials_dvd PRIMARY KEY (material_id),
    CONSTRAINT chk_materials_dvd_type CHECK (material_type = 'dvd'),
    CONSTRAINT fk_materials_dvd_material
        FOREIGN KEY (material_id, material_type)
        REFERENCES materials (material_id, material_type)
        ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE materials_magazine (
    material_id   INTEGER     NOT NULL,
    material_type VARCHAR(15) NOT NULL DEFAULT 'magazine',
    issn          VARCHAR(9)  NOT NULL,
    number        VARCHAR(20) NOT NULL,
    frequency     VARCHAR(20),
    CONSTRAINT pk_materials_magazine PRIMARY KEY (material_id),
    CONSTRAINT uq_materials_magazine_issn_num UNIQUE (issn, number),   -- BR9
    CONSTRAINT chk_materials_magazine_type CHECK (material_type = 'magazine'),
    CONSTRAINT fk_materials_magazine_material
        FOREIGN KEY (material_id, material_type)
        REFERENCES materials (material_id, material_type)
        ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE materials_audiobook (
    material_id   INTEGER      NOT NULL,
    material_type VARCHAR(15)  NOT NULL DEFAULT 'audiobook',
    duration_min  INTEGER      NOT NULL,
    narrator      VARCHAR(120),
    audio_format  VARCHAR(15),
    CONSTRAINT pk_materials_audiobook PRIMARY KEY (material_id),
    CONSTRAINT chk_materials_audiobook_type CHECK (material_type = 'audiobook'),
    CONSTRAINT fk_materials_audiobook_material
        FOREIGN KEY (material_id, material_type)
        REFERENCES materials (material_id, material_type)
        ON DELETE CASCADE ON UPDATE CASCADE
);

-- Rule 3: multivalued attribute (R1)
CREATE TABLE dvd_subtitles (
    material_id INTEGER    NOT NULL,
    language    VARCHAR(5) NOT NULL,
    CONSTRAINT pk_dvd_subtitles PRIMARY KEY (material_id, language),
    CONSTRAINT fk_dvd_subtitles_material
        FOREIGN KEY (material_id) REFERENCES materials_dvd (material_id)
        ON DELETE CASCADE ON UPDATE CASCADE
);

-- Rule 3: multivalued attribute (R12)
CREATE TABLE member_phones (
    member_id INTEGER     NOT NULL,
    number    VARCHAR(20) NOT NULL,
    type      VARCHAR(10) NOT NULL DEFAULT 'mobile',
    CONSTRAINT pk_member_phones PRIMARY KEY (member_id, number),
    CONSTRAINT chk_member_phones_type CHECK (type IN ('mobile','landline','work')),
    CONSTRAINT fk_member_phones_member
        FOREIGN KEY (member_id) REFERENCES members (member_id)
        ON DELETE CASCADE ON UPDATE CASCADE
);

-- ---------------------------------------------------------------------
-- BLOCK 3 · Rooms and events (R3, R4, R5)
-- ---------------------------------------------------------------------
CREATE TABLE rooms (
    room_id     INTEGER GENERATED BY DEFAULT AS IDENTITY,
    branch_id   INTEGER     NOT NULL,
    name        VARCHAR(80) NOT NULL,
    capacity    INTEGER     NOT NULL,
    floor       INTEGER     NOT NULL DEFAULT 0,
    accessible  BOOLEAN     NOT NULL DEFAULT TRUE,
    CONSTRAINT pk_rooms PRIMARY KEY (room_id),
    CONSTRAINT uq_rooms_branch_name UNIQUE (branch_id, name),   -- R3, rule 8
    CONSTRAINT fk_rooms_branch
        FOREIGN KEY (branch_id) REFERENCES branches (branch_id)
        ON DELETE RESTRICT ON UPDATE CASCADE
);

CREATE TABLE event_types (
    event_type_id         INTEGER GENERATED BY DEFAULT AS IDENTITY,
    code                  VARCHAR(30)  NOT NULL,
    name                  VARCHAR(80)  NOT NULL,
    description           VARCHAR(500),
    standard_duration_min INTEGER,
    CONSTRAINT pk_event_types PRIMARY KEY (event_type_id),
    CONSTRAINT uq_event_types_code UNIQUE (code)
);

CREATE TABLE events (
    event_id       INTEGER GENERATED BY DEFAULT AS IDENTITY,
    title          VARCHAR(200) NOT NULL,
    description    VARCHAR(2000),
    event_type_id  INTEGER      NOT NULL,
    room_id        INTEGER,                         -- optional: decision D4
    start_time     TIMESTAMPTZ  NOT NULL,
    end_time       TIMESTAMPTZ  NOT NULL,
    offered_seats  INTEGER      NOT NULL,
    status         VARCHAR(15)  NOT NULL DEFAULT 'scheduled',
    published      BOOLEAN      NOT NULL DEFAULT FALSE,
    CONSTRAINT pk_events PRIMARY KEY (event_id),
    CONSTRAINT fk_events_type
        FOREIGN KEY (event_type_id) REFERENCES event_types (event_type_id)
        ON DELETE RESTRICT ON UPDATE CASCADE,
    CONSTRAINT fk_events_room
        FOREIGN KEY (room_id) REFERENCES rooms (room_id)
        ON DELETE RESTRICT ON UPDATE CASCADE
);

CREATE TABLE speakers (
    speaker_id INTEGER GENERATED BY DEFAULT AS IDENTITY,
    first_name VARCHAR(60)  NOT NULL,
    last_name  VARCHAR(80)  NOT NULL,
    email      VARCHAR(120),
    biography  VARCHAR(1000),
    external   BOOLEAN      NOT NULL DEFAULT TRUE,
    CONSTRAINT pk_speakers PRIMARY KEY (speaker_id),
    CONSTRAINT uq_speakers_email UNIQUE (email)
);

-- ---------------------------------------------------------------------
-- BLOCK 4 · Rules 6, 7 and 9: registrations, reports, participations
-- ---------------------------------------------------------------------
CREATE TABLE registrations (                          -- Rule 7 (N:M with attributes)
    event_id          INTEGER     NOT NULL,
    member_id         INTEGER     NOT NULL,
    registration_date TIMESTAMPTZ NOT NULL DEFAULT now(),
    status            VARCHAR(15) NOT NULL DEFAULT 'confirmed',
    companions        INTEGER     NOT NULL DEFAULT 0,
    CONSTRAINT pk_registrations PRIMARY KEY (event_id, member_id),      -- R6
    CONSTRAINT fk_registrations_event
        FOREIGN KEY (event_id) REFERENCES events (event_id)
        ON DELETE CASCADE  ON UPDATE CASCADE,
    CONSTRAINT fk_registrations_member
        FOREIGN KEY (member_id) REFERENCES members (member_id)
        ON DELETE RESTRICT ON UPDATE CASCADE
);

CREATE TABLE event_reports (                          -- Rule 6 (1:1, option B)
    event_id         INTEGER      NOT NULL,
    actual_attendees INTEGER      NOT NULL,
    average_rating   NUMERIC(3,2),
    notes            VARCHAR(2000),
    report_date      DATE         NOT NULL DEFAULT CURRENT_DATE,
    CONSTRAINT pk_event_reports PRIMARY KEY (event_id),
    CONSTRAINT fk_event_reports_event
        FOREIGN KEY (event_id) REFERENCES events (event_id)
        ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE participations (                         -- Rule 9 (ternary resolved)
    event_id   INTEGER      NOT NULL,
    speaker_id INTEGER      NOT NULL,
    role       VARCHAR(25)  NOT NULL,
    fee        NUMERIC(8,2) NOT NULL DEFAULT 0,
    CONSTRAINT pk_participations PRIMARY KEY (event_id, speaker_id, role),
    CONSTRAINT fk_participations_event
        FOREIGN KEY (event_id) REFERENCES events (event_id)
        ON DELETE CASCADE  ON UPDATE CASCADE,
    CONSTRAINT fk_participations_speaker
        FOREIGN KEY (speaker_id) REFERENCES speakers (speaker_id)
        ON DELETE RESTRICT ON UPDATE CASCADE
);

CREATE TABLE events_materials (                       -- Rule 7 (simple N:M)
    event_id    INTEGER     NOT NULL,
    material_id INTEGER     NOT NULL,
    role        VARCHAR(15) NOT NULL DEFAULT 'recommended',
    CONSTRAINT pk_events_materials PRIMARY KEY (event_id, material_id),
    CONSTRAINT fk_events_materials_event
        FOREIGN KEY (event_id) REFERENCES events (event_id)
        ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_events_materials_material
        FOREIGN KEY (material_id) REFERENCES materials (material_id)
        ON DELETE CASCADE ON UPDATE CASCADE
);

-- ---------------------------------------------------------------------
-- BLOCK 5 · Fines and payments (R10, R11)
-- ---------------------------------------------------------------------
CREATE TABLE fines (
    fine_id    INTEGER GENERATED BY DEFAULT AS IDENTITY,
    member_id  INTEGER      NOT NULL,
    loan_id    INTEGER,                     -- optional: decision D6
    reason     VARCHAR(15)  NOT NULL,
    amount     NUMERIC(6,2) NOT NULL,
    issue_date DATE         NOT NULL DEFAULT CURRENT_DATE,
    status     VARCHAR(15)  NOT NULL DEFAULT 'pending',
    CONSTRAINT pk_fines PRIMARY KEY (fine_id),
    CONSTRAINT uq_fines_loan_reason UNIQUE (loan_id, reason),           -- R10
    CONSTRAINT fk_fines_member
        FOREIGN KEY (member_id) REFERENCES members (member_id)
        ON DELETE RESTRICT ON UPDATE CASCADE,
    CONSTRAINT fk_fines_loan
        FOREIGN KEY (loan_id) REFERENCES loans (loan_id)
        ON DELETE RESTRICT ON UPDATE CASCADE
);

CREATE TABLE payments (                               -- Rule 8 (weak entity)
    payment_id   INTEGER GENERATED BY DEFAULT AS IDENTITY,
    fine_id      INTEGER      NOT NULL,
    payment_date TIMESTAMPTZ  NOT NULL DEFAULT now(),
    amount       NUMERIC(6,2) NOT NULL,
    method       VARCHAR(15)  NOT NULL,
    reference    VARCHAR(50),
    CONSTRAINT pk_payments PRIMARY KEY (payment_id),
    CONSTRAINT fk_payments_fine
        FOREIGN KEY (fine_id) REFERENCES fines (fine_id)
        ON DELETE RESTRICT ON UPDATE CASCADE       -- accounting record: no cascade
);

-- ---------------------------------------------------------------------
-- BLOCK 6 · Adapting the existing tables to the hierarchy
-- ---------------------------------------------------------------------
ALTER TABLE copies ADD COLUMN material_id INTEGER;
ALTER TABLE copies ADD COLUMN copy_number INTEGER;
-- (migration: materials inherits the books; copies.material_id = old book_id)
ALTER TABLE copies DROP CONSTRAINT fk_copies_book;
ALTER TABLE copies DROP COLUMN book_id;
ALTER TABLE copies ALTER COLUMN material_id SET NOT NULL;
ALTER TABLE copies
    ADD CONSTRAINT uq_copies_material_num UNIQUE (material_id, copy_number),
    ADD CONSTRAINT fk_copies_material
        FOREIGN KEY (material_id) REFERENCES materials (material_id)
        ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE reservations ADD COLUMN material_id INTEGER;
ALTER TABLE reservations DROP CONSTRAINT fk_reservations_book;
ALTER TABLE reservations DROP COLUMN book_id;
ALTER TABLE reservations ALTER COLUMN material_id SET NOT NULL;
ALTER TABLE reservations
    ADD CONSTRAINT fk_reservations_material
        FOREIGN KEY (material_id) REFERENCES materials (material_id)
        ON DELETE CASCADE ON UPDATE CASCADE;

DROP TABLE books;

CREATE VIEW books AS
SELECT m.material_id AS book_id, b.isbn, m.title, m.author_id,
       m.publisher, m.publication_year, m.language
  FROM materials m
  JOIN materials_book b ON b.material_id = m.material_id;

Summary of the referential actions

Foreign key ON DELETE Reason
rooms.branch_id RESTRICT Consistent with members and copies; a branch in use is not deleted
materials.author_id SET NULL Same as the old books.author_id: the material outlives the author
materials_*.material_id CASCADE The subtables do not exist without the superclass
dvd_subtitles.material_id CASCADE Multivalued attribute of the DVD
member_phones.member_id CASCADE Multivalued attribute of the member
copies.material_id CASCADE Same as the old copies.book_id
events.event_type_id RESTRICT A type with historical events is not deleted
events.room_id RESTRICT Losing the room of a past event destroys Q5
registrations.event_id CASCADE The registration does not exist without its event
registrations.member_id RESTRICT Historical fact with statistical value (Q5)
event_reports.event_id CASCADE The report does not exist without its event
participations.event_id CASCADE Same
participations.speaker_id RESTRICT Record with financial implications
events_materials.* CASCADE Auxiliary relationship with no value of its own
fines.member_id RESTRICT Financial record
fines.loan_id RESTRICT Financial record
payments.fine_id RESTRICT A weak entity, but an accounting one: never cascade

The last row deserves attention because it goes against intuition: payments is a weak entity of fines and even so it is not CASCADE. The shape of the relationship suggests the action; the value of the data decides it.

  1. Post-review: validating the resulting schema

Applying ten rules and sitting back happily is a mistake. There are three checks that are always done.

Check 1 — Every query in the requirements can be answered

We walk through the twelve queries from 04-01 and write the FROM ... JOIN for each one. An extract of the three least obvious ones:

Q5 — Average occupancy of each room by branch and quarter:

SELECT b.name AS branch,
       r.name AS room,
       DATE_TRUNC('quarter', e.start_time) AS quarter,
       ROUND(AVG(oc.occupied_seats::numeric / NULLIF(r.capacity, 0)) * 100, 1) AS occupancy_pct
  FROM events e
  JOIN rooms r        ON r.room_id = e.room_id
  JOIN branches b     ON b.branch_id = r.branch_id
  JOIN v_events_occupancy oc ON oc.event_id = e.event_id
 WHERE e.status = 'held'
 GROUP BY b.name, r.name, DATE_TRUNC('quarter', e.start_time)
 ORDER BY b.name, r.name, quarter;
 branch  |       room        |        quarter         | occupancy_pct
---------+-------------------+------------------------+---------------
 Central | Multipurpose Room | 2026-04-01 00:00:00+02 |          72.4
 Central | Multipurpose Room | 2026-07-01 00:00:00+02 |          61.0
 North   | Children's Room   | 2026-04-01 00:00:00+02 |          88.7
(3 rows)

Q7 — Members with outstanding debt above €20 (rule 4: the outstanding amount is derived):

SELECT m.member_id,
       m.first_name || ' ' || m.last_name AS member,
       SUM(f.amount - COALESCE(p.paid_amount, 0)) AS debt
  FROM members m
  JOIN fines f ON f.member_id = m.member_id AND f.status = 'pending'
  LEFT JOIN (SELECT fine_id, SUM(amount) AS paid_amount
               FROM payments GROUP BY fine_id) p ON p.fine_id = f.fine_id
 GROUP BY m.member_id, m.first_name, m.last_name
HAVING SUM(f.amount - COALESCE(p.paid_amount, 0)) > 20
 ORDER BY debt DESC;
 member_id |    member    | debt
-----------+--------------+-------
        15 | Iván Pereda  | 27.40
        16 | Nuria Bastos | 22.00
(2 rows)

Q10 — The ten most borrowed materials by type (this is the one the hierarchy strategy had to make possible):

SELECT m.material_type, m.title, COUNT(*) AS loan_count
  FROM loans l
  JOIN copies c    ON c.copy_id = l.copy_id
  JOIN materials m ON m.material_id = c.material_id
 GROUP BY m.material_type, m.material_id, m.title
 ORDER BY loan_count DESC
 LIMIT 10;

A single JOIN to materials, with no UNION of four branches. It is exactly the benefit we were after when we discarded strategy 3.

Check 2 — No table without a primary key

SELECT t.table_name
  FROM information_schema.tables t
  LEFT JOIN information_schema.table_constraints c
         ON c.table_name = t.table_name
        AND c.constraint_type = 'PRIMARY KEY'
 WHERE t.table_schema = 'public'
   AND t.table_type = 'BASE TABLE'
   AND c.constraint_name IS NULL;
 table_name
------------
(0 rows)

Zero rows is the correct answer. A table with no primary key admits exact duplicates, cannot be referenced and cannot be updated row by row safely.

Check 3 — No orphan foreign key and no relationship without an FK

Two queries. The first, from the catalog, lists every declared foreign key so you can contrast them with the diagram:

SELECT conrelid::regclass  AS table_name,
       conname             AS constraint_name,
       confrelid::regclass AS references_table
  FROM pg_constraint
 WHERE contype = 'f'
 ORDER BY 1, 2;

The second looks for columns that look like a foreign key by their name but do not have one declared, which is the most common oversight:

SELECT c.table_name, c.column_name
  FROM information_schema.columns c
 WHERE c.table_schema = 'public'
   AND c.column_name LIKE '%\_id'
   AND NOT EXISTS (
        SELECT 1 FROM information_schema.key_column_usage k
          JOIN information_schema.table_constraints t
            ON t.constraint_name = k.constraint_name
         WHERE k.table_name = c.table_name
           AND k.column_name = c.column_name
           AND t.constraint_type IN ('FOREIGN KEY','PRIMARY KEY'))
 ORDER BY 1, 2;
 table_name | column_name
------------+-------------
(0 rows)

Final checklist

# Check Passed?
1 The 12 queries in the requirements can be answered Yes
2 Every table has a primary key Yes
3 Every *_id column is a declared PK or FK Yes
4 Every entity in the diagram has a table Yes, 21 of 21
5 Every relationship in the diagram is represented Yes
6 Multivalued attributes are in tables of their own Yes (2)
7 Derived attributes are NOT columns Yes (5, in views)
8 Natural keys have UNIQUE Yes (isbn, issn+number, code, email, room name)
9 Every referential action is reasoned out Yes (table in section 12)
10 Rules BR1–BR10 are assigned Pending → 04-04

Nine out of ten. The tenth is the next lesson.

Common Mistakes and Tips

Putting the foreign key on the 1 side. The most serious structural mistake and the easiest to detect: if the FK would need to hold several values, it is on the wrong side. Reread the cardinality in the diagram and ask yourself "how many values do I need to store here?".

Creating a junction table for a 1:N relationship. It works, but it allows states the business forbids: if events_rooms were a junction table, nothing would stop two rows for the same event, that is, one event in two rooms. The structure must make what is forbidden impossible, not merely allow what is correct.

Forgetting the attributes of the N:M relationship. You create registrations (event_id, member_id) and call it done. The date, the status and the companions have nowhere to go, and they end up appearing in members or in events, where they mean nothing.

Storing a derived value without realizing it. events.branch_id "to avoid the JOIN" creates the possibility of an event claiming to be in Central while its room is in North. If a value can be deduced from the schema, storing it means creating a potential contradiction.

Applying CASCADE for convenience. ON DELETE CASCADE on every foreign key avoids errors while developing and deletes half the schema in production. A DELETE FROM members WHERE member_id = 14 with cascades everywhere takes loans, registrations and fines down with it. Each action is decided separately, with the criterion from 02-06.

Decomposing composite attributes that nobody is going to query by parts. Four address columns for an external speaker whose address is only printed on an envelope is permanent work in exchange for nothing.

Choosing a single table for a hierarchy with many specific attributes. It is the temptation of simplicity, and it produces eleven null columns and eleven conditional CHECKs that nobody maintains. Count the specific attributes before deciding: with more than two or three per subtype, table per subclass almost always wins.

Tip: apply the rules in order and do not improvise. The algorithm works precisely because each step builds on the previous one. Skipping rule 1 and starting with the relationships produces FKs to tables that do not exist yet.

Tip: write down next to each table which rule generated it. The script in section 12 carries those comments. When somebody asks why dvd_subtitles is a separate table, the answer is written down: rule 3, multivalued attribute, R1.

Tip: create the schema in an empty database and run it end to end before calling it good. Creation-order errors, duplicate constraint names and incompatible types in the FKs show up in seconds. A schema script nobody has run is a hypothesis.

Exercises

Exercise 1 — Applying the rules to a new fragment

BiblioRed incorporates the following fragment in v1.1:

R16 — Pickup points. As well as at the four branches, reserved materials can be collected at pickup points (automated lockers) installed in community centers. Each point has a code, an address (street, number, postal code), a number of lockers and the branch that supplies it. A member, when making a reservation, chooses where to collect it: at a branch or at a pickup point. Each point also has opening hours per day of the week (Monday to Sunday, with an opening and a closing time; some days it is closed).

Apply the corresponding rules and write the SQL. Explicitly state which rule you apply at each step, how you resolve the "a branch or a pickup point" part and which referential actions you choose, with your reason.

Exercise 2 — Choosing a strategy for a hierarchy

BiblioRed wants to model the notifications it sends to members. There are three types:

  • Return reminder: it carries the associated loan and the number of days remaining.
  • Reservation available notice: it carries the associated reservation and the collection deadline.
  • Event reminder: it carries the associated event and the number of hours remaining.

They all share: recipient (member), channel (email, sms, push), send date, status (pending, sent, failed) and message text. About 3,000 are sent per month, they are almost always queried all together ("this member's notifications, sorted by date") and they are purged after six months.

  1. Determine whether the hierarchy is disjoint/overlapping and total/partial.
  2. Choose one of the three strategies and justify it with at least three arguments from the comparison table.
  3. Write the SQL for the chosen strategy.

Exercise 3 — Spotting transformation errors

A colleague hands in this part of the schema. Find at least five transformation errors, state which rule has been broken and write the corrected version.

CREATE TABLE events (
    event_id      SERIAL PRIMARY KEY,
    title         VARCHAR(200),
    room_id       INTEGER REFERENCES rooms(room_id),
    room_capacity INTEGER,
    branch_id     INTEGER REFERENCES branches(branch_id),
    speaker1_id   INTEGER REFERENCES speakers(speaker_id),
    speaker2_id   INTEGER REFERENCES speakers(speaker_id),
    start_time    TIMESTAMPTZ,
    offered_seats INTEGER,
    free_seats    INTEGER,
    materials     VARCHAR(300)
);

CREATE TABLE registrations (
    event_id  INTEGER REFERENCES events(event_id)   ON DELETE CASCADE,
    member_id INTEGER REFERENCES members(member_id) ON DELETE CASCADE
);

Solutions

Solution to Exercise 1

Rule 1 (strong entity) + Rule 2 (composite attribute):

CREATE TABLE pickup_points (
    pickup_point_id   INTEGER GENERATED BY DEFAULT AS IDENTITY,
    code              VARCHAR(15) NOT NULL,
    addr_street       VARCHAR(120) NOT NULL,
    addr_number       VARCHAR(10),
    addr_postal_code  VARCHAR(5)  NOT NULL,
    locker_count      INTEGER     NOT NULL,
    branch_id         INTEGER     NOT NULL,         -- Rule 5: 1:N
    CONSTRAINT pk_pickup_points PRIMARY KEY (pickup_point_id),
    CONSTRAINT uq_pickup_points_code UNIQUE (code),
    CONSTRAINT fk_pickup_points_branch
        FOREIGN KEY (branch_id) REFERENCES branches (branch_id)
        ON DELETE RESTRICT ON UPDATE CASCADE
);

The address is broken down (rule 2) because the statement lists it in pieces and because the website already searches by postal code (R13). branch_id goes here (rule 5, N side) and it is RESTRICT: a branch with points it supplies is not deleted.

Rule 3 (multivalued attribute): the opening hours. They are multivalued —seven simultaneous values— and composite —opening and closing. They become a table, with the day as the partial identifier:

CREATE TABLE point_hours (
    pickup_point_id INTEGER NOT NULL,
    day_of_week     INTEGER NOT NULL,     -- 1 = Monday ... 7 = Sunday
    opening_time    TIME,                 -- NULL = closed that day
    closing_time    TIME,
    CONSTRAINT pk_point_hours PRIMARY KEY (pickup_point_id, day_of_week),
    CONSTRAINT chk_point_hours_day CHECK (day_of_week BETWEEN 1 AND 7),
    CONSTRAINT fk_point_hours_point
        FOREIGN KEY (pickup_point_id) REFERENCES pickup_points (pickup_point_id)
        ON DELETE CASCADE ON UPDATE CASCADE
);

CASCADE because the opening hours do not exist outside their point.

The "a branch or a pickup point": an exclusive relationship. It is the interesting case in the exercise and it admits two solutions:

Option 1 — two nullable foreign keys with a mutual-exclusion CHECK:

ALTER TABLE reservations ADD COLUMN pickup_branch_id INTEGER;
ALTER TABLE reservations ADD COLUMN pickup_point_id  INTEGER;
ALTER TABLE reservations
  ADD CONSTRAINT fk_reservations_pickup_branch
      FOREIGN KEY (pickup_branch_id) REFERENCES branches (branch_id)
      ON DELETE RESTRICT,
  ADD CONSTRAINT fk_reservations_pickup_point
      FOREIGN KEY (pickup_point_id) REFERENCES pickup_points (pickup_point_id)
      ON DELETE RESTRICT,
  ADD CONSTRAINT chk_reservations_pickup_exclusive
      CHECK ((pickup_branch_id IS NOT NULL AND pickup_point_id IS NULL)
          OR (pickup_branch_id IS NULL AND pickup_point_id IS NOT NULL));

Option 2 — generalize: create a service_points entity of which branches and pickup points are subtypes (rule 10), and have reservations reference it with a single FK.

Option 1 is simpler and sufficient with two alternatives. Option 2 is preferable if more collection places appear tomorrow (mobile library, post office). For v1.1 option 1 is chosen, and it is noted in the decision log that generalization is the plan if a third type shows up.

Solution to Exercise 2

1. Nature of the hierarchy. Disjoint: a notification is of a single type. Total: every notification is one of the three types; generic notifications do not exist.

2. Chosen strategy: single table. Arguments from the comparison table:

  • Few specific attributes: each subtype contributes two columns (an FK and a number). With table per subclass there would be four tables to store six columns in total, and three of those tables would be almost trivial.
  • The dominant query pattern is over the superclass: "this member's notifications sorted by date" does not need the subtype details. With table per subclass that query, by far the most frequent one, would require a triple LEFT JOIN or three queries.
  • Volume and life cycle: 3,000 a month with a six-month purge is about 18,000 live rows. Wasting two null columns per row is irrelevant, and the purge is a single DELETE on one table instead of four coordinated ones.
  • Nobody references the notifications from outside, so the decisive argument from the materials case does not apply here.

The known price is that you cannot require NOT NULL on the specific FKs and you have to make up for it with conditional CHECKs. With three subtypes and one mandatory column each, that is three perfectly manageable CHECKs.

3. SQL:

CREATE TABLE notifications (
    notification_id INTEGER GENERATED BY DEFAULT AS IDENTITY,
    member_id       INTEGER      NOT NULL,
    type            VARCHAR(20)  NOT NULL,
    channel         VARCHAR(10)  NOT NULL,
    send_date       TIMESTAMPTZ  NOT NULL DEFAULT now(),
    status          VARCHAR(12)  NOT NULL DEFAULT 'pending',
    message         VARCHAR(500) NOT NULL,
    -- specific attributes per subtype
    loan_id         INTEGER,
    days_remaining  INTEGER,
    reservation_id  INTEGER,
    deadline_date   DATE,
    event_id        INTEGER,
    hours_remaining INTEGER,
    CONSTRAINT pk_notifications PRIMARY KEY (notification_id),
    CONSTRAINT chk_notifications_type
        CHECK (type IN ('return_reminder','reservation_notice','event_reminder')),
    CONSTRAINT chk_notifications_channel CHECK (channel IN ('email','sms','push')),
    CONSTRAINT chk_notifications_status
        CHECK (status IN ('pending','sent','failed')),
    -- consistency between the discriminant and the specific attributes
    CONSTRAINT chk_notif_return
        CHECK (type <> 'return_reminder' OR loan_id IS NOT NULL),
    CONSTRAINT chk_notif_reservation
        CHECK (type <> 'reservation_notice' OR reservation_id IS NOT NULL),
    CONSTRAINT chk_notif_event
        CHECK (type <> 'event_reminder' OR event_id IS NOT NULL),
    CONSTRAINT fk_notifications_member
        FOREIGN KEY (member_id) REFERENCES members (member_id) ON DELETE CASCADE,
    CONSTRAINT fk_notifications_loan
        FOREIGN KEY (loan_id) REFERENCES loans (loan_id) ON DELETE CASCADE,
    CONSTRAINT fk_notifications_reservation
        FOREIGN KEY (reservation_id) REFERENCES reservations (reservation_id) ON DELETE CASCADE,
    CONSTRAINT fk_notifications_event
        FOREIGN KEY (event_id) REFERENCES events (event_id) ON DELETE CASCADE
);

Note the contrast with the materials decision: the same question, a different answer, and both correct. The strategy does not depend on the theory of the hierarchy, but on the number of specific attributes, on the query pattern and on whether anybody references the subtypes.

Solution to Exercise 3

# Error Rule broken Concrete damage
1 room_capacity copied into events Rule 4 (derived) and "one thing, one place" When a room is refurbished, 300 events are left with the old capacity; BR1 is validated against a stale value
2 branch_id in events Rule 4 (derived) The branch is deduced via room_id. You can create an event claiming to be in Central with a room in North: a cycle in the diagram (04-02)
3 speaker1_id, speaker2_id Rule 7 (N:M) R7 says "several speakers" and "several roles per person". Two columns leave no room for the third, there is nowhere to put the role or the fee, and "how many events did Elena Roig take part in?" needs a UNION
4 free_seats as a column Rule 4 (derived) It goes out of sync as soon as somebody cancels; Q2 starts lying
5 materials VARCHAR(300) Rule 7 and the comma-separated-list anti-pattern No FK, no integrity, no way to answer "which events has this book been discussed at?"
6 registrations with no primary key Rule 7 and check 2 A member can register infinitely many times for the same event, violating R6
7 registrations with no attributes of its own Rule 7 There is nowhere to store the date, the status or the companions (R6)
8 registrations.member_id ON DELETE CASCADE Criterion from 02-06 Deleting a member destroys the attendance history and falsifies Q5
9 Missing NOT NULL on title, start_time, offered_seats Rule 1 + participation in the diagram Events can be created with no title and no date

Corrected version:

CREATE TABLE events (
    event_id      INTEGER GENERATED BY DEFAULT AS IDENTITY,
    title         VARCHAR(200) NOT NULL,
    event_type_id INTEGER      NOT NULL,
    room_id       INTEGER,
    start_time    TIMESTAMPTZ  NOT NULL,
    end_time      TIMESTAMPTZ  NOT NULL,
    offered_seats INTEGER      NOT NULL,
    status        VARCHAR(15)  NOT NULL DEFAULT 'scheduled',
    published     BOOLEAN      NOT NULL DEFAULT FALSE,
    CONSTRAINT pk_events PRIMARY KEY (event_id),
    CONSTRAINT fk_events_type FOREIGN KEY (event_type_id)
        REFERENCES event_types (event_type_id) ON DELETE RESTRICT,
    CONSTRAINT fk_events_room FOREIGN KEY (room_id)
        REFERENCES rooms (room_id) ON DELETE RESTRICT
);
-- room_capacity, branch_id and free_seats: removed (derived, view v_events_occupancy)
-- speaker1_id / speaker2_id: replaced by the participations table
-- materials: replaced by the events_materials table

CREATE TABLE registrations (
    event_id          INTEGER     NOT NULL,
    member_id         INTEGER     NOT NULL,
    registration_date TIMESTAMPTZ NOT NULL DEFAULT now(),
    status            VARCHAR(15) NOT NULL DEFAULT 'confirmed',
    companions        INTEGER     NOT NULL DEFAULT 0,
    CONSTRAINT pk_registrations PRIMARY KEY (event_id, member_id),
    CONSTRAINT fk_registrations_event FOREIGN KEY (event_id)
        REFERENCES events (event_id) ON DELETE CASCADE,
    CONSTRAINT fk_registrations_member FOREIGN KEY (member_id)
        REFERENCES members (member_id) ON DELETE RESTRICT
);

Conclusion

This lesson has turned a diagram into an executable schema by means of a ten-rule algorithm.

  • Rules 1 to 4 (attributes): every strong entity is a table with its surrogate PK and UNIQUE on the natural key; composite attributes are broken down into columns —unless nobody queries the parts—; multivalued ones always become a separate table, which eliminates the numbered-column and comma-separated-list anti-patterns at the root; and derived ones are not stored, with two named exceptions: performance (05-04) and historical facts that must be frozen.
  • Rule 5 (1:N): the foreign key always goes on the N side, because it is the only one that holds a single value. The participation in the diagram translates literally into NOT NULL or its absence.
  • Rule 6 (1:1): three options. Merge if both sides are total; FK with a shared PK on the optional side if one is partial —what we chose for event_reports, because it tells "not written" from "zero attendees"—; an intermediate table only if both are partial.
  • Rule 7 (N:M): a junction table with two FKs and, above all, with the relationship's own attributes, which is what gets forgotten most. The composite PK implements the business rule without a line of code.
  • Rule 8 (weak entity): a composite PK with the owner's. In practice, a weak entity may carry a surrogate key provided its composite natural key is declared UNIQUE: what is not negotiable is the uniqueness.
  • Rule 9 (ternary): three FKs, but the decomposition test first. Four out of five apparent ternary relationships are something else; BiblioRed's turned out to be an N:M with the role in the key.
  • Rule 10 (hierarchy): three strategies with a comparison table. BiblioRed chose table per subclass because copies, loans and reservations need a common entity, because each subtype has mandatory attributes of its own and because adding a new type touches nothing existing. Discriminant consistency is closed with a composite foreign key (material_id, material_type).
  • Compatibility with the existing code was solved by turning books into a view: the cleanest example of logical independence in the whole course.
  • The referential actions were reasoned out one by one, and the conclusion is that the shape of the relationship suggests the action, but the value of the data decides it: registrations.member_id is RESTRICT while reservations.member_id is CASCADE, and payments is a weak entity that must never cascade because it is an accounting record.
  • The post-review —twelve queries answered, no table without a primary key, no *_id column without a declared FK— closed nine of the ten points on the checklist.

The tenth is left, and it is the most important one: the ten business rules BR1–BR10 are still nowhere in the schema. Nothing today prevents a fine of −€40, an event that ends before it starts, a capacity of zero or a status of 'confimed' with a typo. And the data types are provisional: there are INTEGERs where SMALLINT would do, VARCHAR(15) sizes set by eye, and the fine amount is in NUMERIC for good reasons we have not yet explained.

In the next lesson, 04-04 Data Types and Constraints, we review the schema column by column: which integer type to choose and when it falls short, why money never goes in floating point —with a demonstration that comes as a surprise—, TIMESTAMP versus TIMESTAMPTZ and the time zone problem, ENUM versus lookup table versus CHECK, the collation that decides whether "Àngels" comes before or after "Angel" when searching titles, and the complete catalog of constraints: NOT NULL, DEFAULT, UNIQUE with its surprising behavior in the face of NULL, single- and multi-column CHECK, generated columns, reusable domains and how to add constraints to a table that already has data without locking it. The result will be the definitive, hardened version of the BiblioRed schema.

◄ Previous
◄
Next ►
►
The Project | About Us | Contribute | Donations | License
© Copyright 2026. All rights reserved