Relational Modelling

The reading behind the lesson 1 slides. Start with the study guide if you have not set up your database yet.

Objectives

After this unit, learners can:

  • Find the entities and relationships in a written domain description.
  • Choose a primary key and justify the choice.
  • Normalize a table to third normal form and say what each step removed.

Why This Matters

The library you will model all module lends books to members. If the same author's name is typed into every book row, one typo makes "Le Van A" and "Le Van A" two different people, and a report of loans per author is quietly wrong. A good model stores each fact once, so there is nowhere for two copies to disagree.

Entities and Relationships

An entity is a thing the domain talks about and keeps facts about. A relationship is how two entities are connected. To find them, read the description and underline the nouns: the ones with facts of their own are entities, and the verbs between them are relationships.

erDiagram
    AUTHOR ||--o{ BOOK_AUTHOR : "1-n writes"
    BOOK ||--o{ BOOK_AUTHOR : "1-n is written by"
    BOOK ||--o{ LOAN : "1-n is lent as"
Author, book, book_author and loan, each relationship labelled 1-n
The catalogue. Each label says the cardinality in words first.

A member borrows a book on a date and returns it by a due date. List the entities.

Answer

Member, book and loan. The verb "borrows" hides the loan, and the two dates are facts about it.

Keys

A primary key identifies a row uniquely and never changes. Any column set that could identify a row is a candidate; the primary key is the candidate you choose. Prefer a surrogate key, a number with no meaning outside the database, when the natural value can change.

CREATE TABLE book (
    isbn  CHAR(13) PRIMARY KEY,   -- a publisher can reissue it
    title TEXT NOT NULL
);
CREATE TABLE book (
    id    BIGINT PRIMARY KEY,     -- never changes, means nothing else
    isbn  CHAR(13) NOT NULL UNIQUE,
    title TEXT NOT NULL
);

A member table uses the email address as its primary key. What breaks when a member changes their email?

Answer

Every loan row that points at the old address. Keep the email UNIQUE and add a surrogate id for other tables to reference.

Normalization

Normalizing is moving each fact to the one table it describes. Three questions take a table to third normal form: is there a list packed into a cell (1NF), does every column need the whole key (2NF), and does any column really describe another non-key column (3NF)?

FormRemovesAsk
1NFrepeating groupsIs there a list in a cell?
2NFpartial dependenciesDoes this column need the whole key?
3NFtransitive dependenciesDoes this column describe another column?
CREATE TABLE book (id BIGINT PRIMARY KEY, authors TEXT);  -- "Ana, Binh"
CREATE TABLE book_author (
    book_id   BIGINT REFERENCES book(id),
    author_id BIGINT REFERENCES author(id),
    PRIMARY KEY (book_id, author_id)
);

Which normal form does loan(id, book_id, book_title, due_date) break?

Answer

Third: book_title depends on book_id, not on the loan. It belongs in book.

Worked Example

The problem. Model the catalogue: authors write books, a book can have several authors, and books are lent to members.

The approach. One table per entity, each with a surrogate key; the many-to-many between book and author gets a table of its own; every reference is a declared foreign key.

CREATE TABLE author (id BIGINT PRIMARY KEY, name TEXT NOT NULL);
CREATE TABLE book   (id BIGINT PRIMARY KEY, isbn CHAR(13) NOT NULL UNIQUE, title TEXT NOT NULL);
CREATE TABLE book_author (
    book_id   BIGINT NOT NULL REFERENCES book(id),
    author_id BIGINT NOT NULL REFERENCES author(id),
    PRIMARY KEY (book_id, author_id)
);

What it gives you. An author's name lives in one row, so a correction is one update; a book with three authors is three rows in book_author, never a list.

Common Problems

A key that changes

An ISBN is reissued and every referencing row is wrong. Use a surrogate key and keep the ISBN as a unique column.

ERROR: insert or update on table "loan" violates foreign key constraint

The loan points at a book that does not exist. Insert the book first, or check the id you are using.

Summary

  • Entities are nouns with facts of their own; relationships are the verbs between them.
  • A primary key never changes: prefer a surrogate and keep natural values unique.
  • Normalize until each fact is stored once: no lists, no partial or indirect dependencies.

Knowledge Check

  1. What makes a column set a candidate key?

    Answer

    It identifies every row uniquely, and no smaller set of its columns does.

  2. Why prefer a surrogate key to a natural one?

    Answer

    A surrogate never changes, so nothing that references it has to change either.

  3. What does third normal form remove that second does not?

    Answer

    Transitive dependencies: a non-key column that depends on another non-key column.

  4. Where should referential integrity be enforced, and why?

    Answer

    In the database, with foreign keys, so every program that writes to it is held to the same rule.

  5. A book has three authors. How many rows does it add to book_author?

    Answer

    Three, one per author.

Further Reading