Databases — Lecture Note

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.

Module Map

How to Study This Module

1. In the session, follow the slides and answer every check question before the reveal.
2. After it, read the lesson's topic below, then do the lab.
3. Before the theory exam or an interview, reread the summary and answer the review questions before opening each answer. A question you miss names the topic to reread.

The examples build one domain, so a lesson you skip leaves a gap in the next one.

Environment Setup

Create a scratch database you can drop and recreate freely:

createdb library_scratch

The Running Example

A small library catalogue: authors write books, a book can have several authors, and books are lent to members. Every example and lab in the module uses it.

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.

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"

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.

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.

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

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. The natural value stays, as a unique column.

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, such as a book's title stored in a loan row.

4. 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.

5. 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.

6. 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.

7. 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.

8. 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.

9. 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.

10. 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.

Glossary

Normalization

Organising columns so each fact is stored once.

Surrogate key

A key with no meaning outside the database.

Full Syllabus Map

• Relational Modelling → Relational Modelling
• Querying → Querying

Further Reading

• PostgreSQL 16 documentation
• PostgreSQL 16: Constraints
• PostgreSQL 16: Table Expressions