Databases

After this lesson, you can:

• Find the entities and relationships in a written description
• Choose a primary key and justify it
• Normalize a table to third normal form

• Underline the nouns in the description
• A noun with facts of its own is an entity
• A verb between two is a relationship, and may hide an entity
• Name entities in the singular

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

• member
• date
• due date

Any column set that identifies a row uniquely, such as isbn.

The candidate you choose to identify rows. It never changes.

A column that holds another table's primary key.

CREATE TABLE book (
  isbn  CHAR(13) PRIMARY KEY,
  title TEXT NOT NULL
);

A reissued ISBN breaks every loan that points at it:

CREATE TABLE book (
  id   BIGINT PRIMARY KEY,
  isbn CHAR(13) NOT NULL UNIQUE,
  title TEXT NOT NULL
);

Loans point at the id, which never changes:

The member table is keyed on email, and every loan points at it:

Ana changes her email. What happens to her two loans?

One value per cell: no lists packed into a column.

Every column depends on the whole key, not part of it.

No column depends on another non-key column.

CREATE TABLE book (
  id      BIGINT PRIMARY KEY,
  authors TEXT
);

Two authors in one cell: no join can find either.

CREATE TABLE book_author (
  book_id   BIGINT
    REFERENCES book(id),
  author_id BIGINT
    REFERENCES author(id),
  PRIMARY KEY (book_id,
               author_id)
);

loan(id, book_id, book_title, due_date), with three of its rows:

Which normal form does it break, and which column is the clue?

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