Session 12 — Schema design: constraints, normalisation, indexes

The last session turns you from query-writer into schema-designer. Constraints — PRIMARY KEY, FOREIGN KEY, NOT NULL, UNIQUE, CHECK — let the database enforce rules instead of the application. Normalisation (1NF/2NF/3NF) is the discipline of one fact, one place — the reason the bookshop is three tables. Indexes make filtered and joined reads fast at the price of write cost. Views wrap a query into a reusable object. You finish by designing a small schema from scratch and re-reading the bookshop as a design, not an accident.

Plan: Core ideas → worked schemas → Quiz.

Time: about 70–80 minutes if you write the CREATE TABLE statements before checking the reveals.

Session 12 — Schema design: constraints, normalisation, indexes

The one-paragraph story

Every query you have written ran against a schema someone designed. This session turns the tables: you design the schema. Constraints make the database enforce rules — PRIMARY KEY uniqueness, FOREIGN KEY referential integrity, NOT NULL, UNIQUE, CHECK. Normalisation is the discipline of splitting data so each fact lives once (the reason the bookshop is three tables and not one). Indexes make lookups fast at the price of write cost. Views wrap a query into a reusable object. By the end you will design a small schema from a messy spreadsheet — and understand why the bookshop looks the way it does.

---

Constraints: the database as the guard

Declare rules in CREATE TABLE so the engine — not the application — enforces them:

CREATE TABLE books (
    book_id        INTEGER PRIMARY KEY,
    title          TEXT NOT NULL,
    author_id      INTEGER NOT NULL REFERENCES authors(author_id),
    genre          TEXT NOT NULL,
    price          REAL CHECK (price >= 0),
    published_year INTEGER,
    UNIQUE (title, author_id)
);

• PRIMARY KEY — unique and not NULL; identifies each row. The engine rejects duplicate or missing key values.
• NOT NULL — the column must have a value. INSERT ... (NULL) is rejected.
• REFERENCES (foreign key) — every value must exist in the referenced table (or be NULL). Options decide how the engine reacts when the referenced row changes: ON DELETE CASCADE deletes the children with the parent, ON DELETE SET NULL leaves them with NULL, RESTRICT/NO ACTION (the default) blocks the deletion.
• UNIQUE — no two rows share this value (a natural key like an email, ISBN, or title+author pair).
• CHECK — an arbitrary per-row condition (price >= 0). Rejects any row that violates it.

Constraints convert "remember to check" into "impossible to break". The Session 11 foreign-key rejections you saw were exactly these rules firing.

---

Normalisation: one fact, one place

Normalisation is the discipline of removing redundancy and dependence from a schema. The two most important normal forms:

• First normal form (1NF): one value per cell, rows identified by a primary key — no lists inside a cell, no "colours: red, blue" in a single field.
• Second normal form (2NF): non-key columns depend on the whole key, not just part of it (matters for composite keys — the (order_id, line_no) tables of Session 6).
• Third normal form (3NF): non-key columns do not depend on other non-key columns — "author_country" does not belong in books; it belongs in authors, reachable by the author_id foreign key.

The bookshop is already normalised: authors.name/authors.country live only in authors; books stores the reference author_id, not a copy of the name. Fixing a typo once (Session 1) is 3NF in action.

When to stop: normalisation trades write-safety for join-work. A handful of deliberately denormalised columns (e.g. a cached total) is normal practice when reads dominate — the rule is know which normal form you are in and why.

---

Indexes: why some queries are fast

Without an index, WHERE price > 20 scans every row (a sequential scan). An index on price is a sorted structure the engine can use to jump straight to the matching rows — like a book's index versus reading every page.

CREATE INDEX idx_books_price ON books (price);
CREATE INDEX idx_sales_book ON sales (book_id);

Rules of thumb:

• Index the columns you filter (WHERE), join (ON), or sort (ORDER BY) — especially foreign keys, which are used in joins constantly.
• Indexes speed reads but slow writes (INSERT/UPDATE/DELETE must maintain them) and use storage.
• A primary key already creates an index; you do not add one for it.
• Multi-column indexes (CREATE INDEX ... ON books (genre, price)) help when a query filters both columns together.

In our tiny tables an index barely matters; in a real table of millions of rows it is the difference between milliseconds and minutes.

---

Views: stored queries

A view is a query saved under a name:

CREATE VIEW book_sales AS
SELECT b.title, a.name AS author, s.qty
FROM books b
JOIN authors a ON b.author_id = a.author_id
JOIN sales s   ON s.book_id = b.book_id;

-- then simply:
SELECT * FROM book_sales WHERE qty >= 2;

book_sales behaves like a table in queries but stores no data — it re-runs the underlying query each time. Use views for commonly reused joins or for exposing a safe subset of columns. A view is what a CTE (Session 8) becomes when you promote it to a persistent object.

---

Designing the bookshop from a messy source

Imagine the original data arrived as one giant spreadsheet: author names repeated in every book row, prices with stray spaces, a "tags" column listing several genres. Normalising it means exactly the steps you have learned:

1. 1NF — split the multi-valued cells into their own rows.
2. Identify the key — book_id identifies books; author_id identifies authors.
3. 2NF/3NF — move author facts (name, country) out of books into authors, keep author_id as the link.
4. Clean types — numeric columns numeric, dates as dates (Session 1's pitfall list).
5. Add constraints — primary keys, foreign keys, NOT NULL, CHECK (price >= 0).
6. Index the hot paths — sales(book_id) for the join, books(genre) for genre filters.

That is the whole course applied to a design problem: keys (S1), types and constraints, queries (S2–10) validated against the result, and writes (S11) guarded by integrity rules.

---

Pitfalls to watch for

• Redundancy is the enemy — a fact stored twice can drift; normalise it out.
• Indexes are not free — each one taxes writes; index the hot reads, not everything.
• Constraints > application checks — the engine enforcing CHECK (price >= 0) beats the app "remembering" to.
• Views are not materialised by default — they re-query on every use; they are definitions, not snapshots.

Session 12 — Worked examples

The bookshop data (as throughout the course):

authors: (1, Alina Voss, Germany), (2, Mateo Rios, Argentina), (3, Priya Anand, India), (4, Jonas Weber, Germany)

books: (10, The Glass Harbour, 1, fiction, 24.99, 2019), (11, Static Echoes, 1, fiction, 19.50, 2021), (12, River Clock, 2, mystery, 22.00, 2018), (13, Salt Roads, 3, history, 31.00, 2020), (14, North of Noon, 3, history, 27.50, 2017), (15, Prairie Circuit, 4, mystery, 18.00, 2022)

sales: (100, 10, 2), (101, 12, 1), (102, 10, 3), (103, 14, 1), (104, 13, 2)

Example A — A constrained schema from scratch

CREATE TABLE books (
    book_id        INTEGER PRIMARY KEY,
    title          TEXT NOT NULL,
    author_id      INTEGER NOT NULL REFERENCES authors(author_id),
    genre          TEXT NOT NULL CHECK (genre IN ('fiction', 'mystery', 'history')),
    price          REAL NOT NULL CHECK (price >= 0),
    published_year INTEGER
);

Now the engine rejects: a NULL title, a NULL author, a genre outside the list, a negative price — each with an error. These checks are what turn "please remember" into "cannot happen".

Trace discipline: for every rejected insert, name the violated constraint and the row that would have broken it.

---

Example B — ON DELETE options

CREATE TABLE books_cascade (
    book_id   INTEGER PRIMARY KEY,
    author_id INTEGER REFERENCES authors(author_id) ON DELETE CASCADE
);

DELETE FROM authors WHERE author_id = 3; now succeeds and deletes books 13 and 14 with her. With ON DELETE SET NULL, the books survive with author_id = NULL. With the default (restrict), the delete is rejected. Choose per the business rule: orphan the children, delete them too, or refuse.

---

Example C — Denormalisation, knowingly

A report that runs every minute computes "copies sold per book" by scanning all of sales:

SELECT book_id, SUM(qty) FROM sales GROUP BY book_id;

If reads dominate and writes are rare, adding a cached total_sold column to books (kept up to date by UPDATE/triggers) is a deliberate, documented denormalisation — a 3NF violation chosen for speed. The rule is not "never denormalise"; it is "know you are doing it".

---

Example D — Indexing the hot paths

CREATE INDEX idx_books_genre  ON books (genre);
CREATE INDEX idx_sales_book   ON sales (book_id);
CREATE INDEX idx_sales_date   ON sales (sale_date);

WHERE genre = 'mystery', JOIN ... ON s.book_id = b.book_id, and WHERE sale_date >= ... each get a sorted lookup structure. The primary keys book_id and sale_id already have implicit indexes — no extra work needed there. Notice we did not index qty: nothing filters on it.

---

Example E — A view for the three-table join

CREATE VIEW book_sales AS
SELECT b.title, a.name AS author, s.qty, s.sale_date
FROM books b
JOIN authors a ON b.author_id = a.author_id
JOIN sales s   ON s.book_id = b.book_id;

SELECT * FROM book_sales ORDER BY qty DESC; reuses the join without rewriting it. The view holds no data — the engine re-runs the three-way join each time it is referenced.

---

Example F — Capstone: design a small schema

A food-truck app needs: trucks, their menus, and orders. Sketch the design:

CREATE TABLE trucks (
    truck_id INTEGER PRIMARY KEY,
    name     TEXT NOT NULL,
    city     TEXT NOT NULL
);

CREATE TABLE menu_items (
    item_id  INTEGER PRIMARY KEY,
    truck_id INTEGER NOT NULL REFERENCES trucks(truck_id),
    name     TEXT NOT NULL,
    price    REAL NOT NULL CHECK (price > 0)
);

CREATE TABLE orders (
    order_id INTEGER PRIMARY KEY,
    item_id  INTEGER NOT NULL REFERENCES menu_items(item_id),
    qty      INTEGER NOT NULL CHECK (qty > 0),
    order_date DATE NOT NULL
);

Why three tables? Trucks own menu items; orders reference items. truck_id appears only in menu_items (3NF); an order never repeats the truck's city. Queries: "revenue per truck" = join all three and SUM(qty * price) GROUP BY trucks.truck_id.