Databases — Lecture Note

The module's theory in one place: keep it as a reference, and revise from it before the theory exam or an interview. The slides teach each lesson; the study guide says how to work through them.

Objectives

After this module, 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.
Join tables and say which rows a join keeps and which it drops.
Group rows and aggregate them correctly.

Relational Modelling

A model decides where each fact is stored. A good one stores every fact once, so there is nowhere for two copies to disagree. Slides: lesson 1.

Entities and relationships

An entity is a thing the domain keeps facts about; a relationship is how two entities connect. In a description, the nouns with facts of their own are entities and the verbs between them are relationships. A noun that is only a fact about something else — a book's title — is an attribute, not an entity.

Author, book, book_author and loan, each relationship labelled 1-n — The catalogue. A many-to-many (book–author) becomes a table of its own.

Keys

A primary key identifies a row uniquely and never changes. Any minimal column set that identifies a row is a candidate key; the primary key is the one chosen. Prefer a surrogate key, with no meaning outside the database, when the natural value can change, and keep the natural value UNIQUE. A foreign key makes the database refuse a reference to a row that does not exist.

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
);

Normalization

Normalizing moves each fact to the one table it describes. Each normal form removes one kind of redundancy.

Form	Removes	Ask
1NF	repeating groups	Is there a list in a cell?
2NF	partial dependencies	Does this column need the whole key?
3NF	transitive dependencies	Does this column describe another non-key column?

Querying

A query is a claim about which rows answer a question. Say which rows you expect before you run it: a join or a count done the wrong way returns a confident, wrong answer.

Inner and left joins

An inner join keeps only the rows that match on both sides. A left join keeps every row on the left and fills the right side with NULL where nothing matches — use it when the absence is part of the answer, and make every later join left too, or it drops the rows the first one kept.

SELECT b.title, a.name
FROM book b
LEFT JOIN book_author ba ON ba.book_id = b.id
LEFT JOIN author a       ON a.id = ba.author_id;   -- books with no author stay

Grouping and aggregation

GROUP BY folds the rows that share a value into one, and an aggregate summarises each fold. WHERE filters rows before grouping; HAVING filters groups after, so an aggregate can only appear in HAVING.

	WHERE	HAVING
Filters	rows	groups
Runs	before `GROUP BY`	after `GROUP BY`
Can use an aggregate	no	yes

Common Misconceptions

"A natural key is better because it means something"

Meaning is what makes it change: a reissued ISBN or a new email breaks every row that references it. Reference a surrogate; keep the natural value unique.

"COUNT(*) counts the matches"

After a left join it also counts the unmatched row, filled with NULL. Count a column from the joined side — COUNT(l.id) — to count matches.

Summary

Entities are nouns with facts of their own; relationships are the verbs between them; a many-to-many gets its own table.
A primary key never changes: prefer a surrogate and keep natural values unique; foreign keys hold references to the rows they name.
Normalize until each fact is stored once: no lists, no partial and no transitive dependencies.
Inner joins drop unmatched rows; left joins keep the left side and fill the gaps with NULL.
WHERE filters rows before grouping, HAVING filters groups after; count a column, not *, after a left join.

Review Questions

What makes a column set a candidate key?

Answer
It identifies every row uniquely, and no smaller set of its columns does.
Why prefer a surrogate key to a natural one?

Answer
A surrogate never changes, so nothing that references it has to change either. The natural value stays, as a unique column.
What does third normal form remove that second does not?

Answer
Transitive dependencies: a non-key column that depends on another non-key column, such as a book's title stored in a loan row.
How is a many-to-many relationship stored, and why not as a list in a column?

Answer
As a table of pairs, one row per link, keyed on both foreign keys. A list in a cell breaks first normal form and cannot be joined or constrained.
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.
Which rows does an inner join drop, and which does a left join keep?

Answer
An inner join drops rows with no match on the other side; a left join keeps every left row, with NULLs where nothing matched.
When does a left join change a COUNT(*)?

Answer
When a left row has no match: it still produces one row, which COUNT(*) counts. Count a column from the right side instead.
What is the difference between WHERE and HAVING?

Answer
WHERE filters rows before grouping; HAVING filters groups after, and is the only one that can use an aggregate.
How would you find the authors who have never been borrowed?

Answer
Left-join author through book_author to loan, group by author, and keep HAVING COUNT(l.id) = 0.
How would you check a join has not multiplied rows?

Answer
Compare its row count with the count before the join; a one-to-many join repeats the one side once per match.