Normalization, briefly

Why the bookshop is split into tables.

One fact, one place

Why not keep everything in one giant books table with the author's name and country repeated on every row? Because repetition causes anomalies: change an author's country and you must update every one of their books; an author with no book yet can't be recorded at all.

Normalization is the cure — split data so each fact lives in exactly one place, linked by keys:

1NF — no repeating groups; one value per cell (that's why order_items is its own table, not a comma-list in orders).
2NF — every non-key column depends on the whole key (unit_price belongs with the order_item, not the order).
3NF — non-key columns depend on nothing but the key (author country lives in authors, keyed by author, not copied into books).

A many-to-many relationship (orders ↔ books) is always resolved with a junction table — that's exactly what order_items is. The query below shows the join working — count how many distinct books each author has.

Everything you would want to ask is now hard: how many of each book, at what price, which orders contain Northern Light. 1NF says one value per cell — and splitting that cell into rows is exactly how order_items came to exist.

The key here is (order_id, book_id). Quantity needs both halves; title needs only book_id, so Northern Light is written once per order that contains it. That is the 2NF violation — a partial dependency — and the fix is to leave title in books.

title → author → country is a transitive dependency, and it is why the update anomaly bites: if Ursula Vane moves, every one of her books must be edited, and any row missed becomes a second, contradictory answer. 3NF puts country in authors, once.