SQL / DATA MODELLING AND NORMALISATION
Third normal form and transitive dependencies
Spot transitive dependencies in a table, prove them with a grouping query, and split the offending columns into their own table without losing information.
What you will learn
- Spot a transitive dependency: key -> non-key column -> a second non-key column
- Move the determinant and its dependents into a new table keyed on the determinant
- Prove a suspected dependency with GROUP BY plus COUNT(DISTINCT) on the dependent
- Tell a duplicated fact apart from a deliberate point-in-time snapshot
Understanding Third normal form and transitive dependencies
A table reaches third normal form when every non-key column depends on the key directly, with no middle step. In staff_assignment(staff_id, staff_name, dept_code, dept_name, dept_floor), staff_id fixes dept_code and dept_code fixes dept_name, so dept_name depends on staff_id only through that second hop; that two-hop chain is the transitive dependency. Second normal form asks whether a column depends on only part of a composite key, while third normal form asks a different question: is this column determined by something in the row that is not the key at all? The plain-language tell is that dept_name and dept_floor answer questions about a department, not about a person.
The damage follows from repetition. Because dept_code appears once per member of staff, everything dept_code determines is copied along with it, and SQL has no declarative way to say those copies must agree. A rename that reaches three of four rows leaves the database holding two different names for the same department, and no query can decide which one is true. The useful mental model is that one row should be a set of statements about exactly one thing, the thing its key names; here the row is quietly describing two things at once.
The repair is mechanical. The determinant becomes the primary key of a new table, the columns it determines move with it, and the original table keeps the determinant as a foreign key so each row still points at its department. Nothing is lost, because a JOIN rebuilds the wide row exactly, and that is also the test for whether you should split at all: if a JOIN cannot reproduce the column, it was never determined by the other column and it has to stay where it is.
-- One table trying to describe both a person and a department
CREATE TABLE staff_assignment (
staff_id INTEGER PRIMARY KEY,
staff_name TEXT NOT NULL,
dept_code TEXT NOT NULL,
dept_name TEXT NOT NULL,
dept_floor INTEGER NOT NULL
);
INSERT INTO staff_assignment VALUES
(1, 'Ada', 'RAD', 'Radiology', 2),
(2, 'Bruno', 'RAD', 'Radiology', 2),
(3, 'Cira', 'CAR', 'Cardiology', 4),
(4, 'Dov', 'RAD', 'Radiology', 2);
-- The department is renamed, but the update only reaches one row
UPDATE staff_assignment
SET dept_name = 'Diagnostic Imaging'
WHERE staff_id = 1;
-- So what is RAD called now?
SELECT dept_code, dept_name, COUNT(*) AS rows_saying_this
FROM staff_assignment
GROUP BY dept_code, dept_name
ORDER BY dept_code, dept_name;If a non-key column is determined by another non-key column, it is a fact about that other column's entity and belongs in a table keyed by it.
Worked examples
Splitting out the determinant
The same rename applied to a 3NF design touches one row and stays consistent for everybody.
CREATE TABLE department (
dept_code TEXT PRIMARY KEY,
dept_name TEXT NOT NULL,
dept_floor INTEGER NOT NULL
);
CREATE TABLE staff (
staff_id INTEGER PRIMARY KEY,
staff_name TEXT NOT NULL,
dept_code TEXT NOT NULL REFERENCES department(dept_code)
);
INSERT INTO department VALUES
('RAD', 'Radiology', 2),
('CAR', 'Cardiology', 4);
INSERT INTO staff VALUES
(1, 'Ada', 'RAD'),
(2, 'Bruno', 'RAD'),
(3, 'Cira', 'CAR'),
(4, 'Dov', 'RAD');
UPDATE department SET dept_name = 'Diagnostic Imaging' WHERE dept_code = 'RAD';
SELECT s.staff_name, d.dept_name, d.dept_floor
FROM staff s
JOIN department d ON d.dept_code = s.dept_code
ORDER BY s.staff_id;Example explained
Line 1dept_code TEXT PRIMARY KEY turns the determinant into a key, so dept_name now depends on a key directly.
Line 2staff carries only dept_code, so a staff row physically cannot hold a stale department name.
Line 3The UPDATE changes exactly one row; there is no second copy left to forget.
Line 4The JOIN reproduces the original five columns, which shows the split threw away no information.
Detecting a hidden dependency in existing data
A grouping query finds columns that are supposed to be determined by another column but already disagree.
CREATE TABLE product_import (
sku TEXT PRIMARY KEY,
title TEXT NOT NULL,
brand_id INTEGER NOT NULL,
brand_name TEXT NOT NULL,
brand_country TEXT NOT NULL
);
INSERT INTO product_import VALUES
('A1', 'Kettle', 10, 'Norlys', 'NO'),
('A2', 'Toaster', 10, 'Norlys', 'NO'),
('A3', 'Blender', 10, 'Norlys', 'SE'),
('B1', 'Lamp', 22, 'Vega', 'DK');
SELECT brand_id,
COUNT(DISTINCT brand_name) AS names,
COUNT(DISTINCT brand_country) AS countries
FROM product_import
GROUP BY brand_id
HAVING COUNT(DISTINCT brand_name) > 1
OR COUNT(DISTINCT brand_country) > 1;Example explained
Line 1GROUP BY brand_id collects the rows by the column you suspect is the determinant.
Line 2COUNT(DISTINCT brand_country) asks how many different answers a single brand_id gives.
Line 3Brand 10 reports countries = 2, so the intended dependency is already broken in the stored data.
Line 4An empty result would not mean the table is safe, only that nothing has contradicted itself yet.
A snapshot is not a transitive dependency
A recorded historical value looks redundant but is not determined by the other table's key, so it must stay.
CREATE TABLE product (
product_id INTEGER PRIMARY KEY,
list_price_cents INTEGER NOT NULL
);
CREATE TABLE order_line (
order_id INTEGER,
product_id INTEGER NOT NULL REFERENCES product(product_id),
qty INTEGER NOT NULL,
price_paid_cents INTEGER NOT NULL,
PRIMARY KEY (order_id, product_id)
);
INSERT INTO product VALUES (7, 1200);
INSERT INTO order_line VALUES (100, 7, 2, 950);
UPDATE product SET list_price_cents = 1500 WHERE product_id = 7;
SELECT ol.order_id, ol.price_paid_cents, p.list_price_cents
FROM order_line ol
JOIN product p ON p.product_id = ol.product_id;Example explained
Line 1price_paid_cents describes this order line, not product 7, so product_id does not determine it.
Line 2Two lines for the same product may legitimately hold different paid prices, which is exactly what a functional dependency forbids.
Line 3The UPDATE moves the list price to 1500 and leaves the recorded 950 untouched.
Line 4Dropping price_paid_cents in the name of 3NF would delete history, not redundancy.
Important notes
3NF also tolerates the dependency when the dependent column is itself part of some candidate key; that loophole is what BCNF closes, and it only appears in tables with overlapping candidate keys.
No single-table constraint can state 'dept_code determines dept_name' - UNIQUE (dept_code) would be wrong, since a department has many staff - so the rule becomes enforceable only once dept_code is the primary key of its own table.
Common mistakes
Assuming a single-column surrogate key makes a table safe: it does rule out partial dependencies, but dept_name sitting beside dept_code is still one fact copied into every staff row.
Keeping dept_name in the child table as well 'so the join is optional': the update anomaly returns immediately, and now two tables can disagree about the same department.
Splitting on a column that is already a candidate key, such as a UNIQUE email that determines display_name: you get a pointless one-to-one table and an extra join with no redundancy removed.
Try it yourself
Change, predict, then run
Create booking(booking_id, guest_name, room_no, room_type, room_rate_cents) with four bookings spread over two rooms, then run the GROUP BY room_no with COUNT(DISTINCT room_type) check. Split the room facts into a room table keyed on room_no and reproduce the original five columns with a JOIN.
Open the SQL workspaceCheck your understanding
A table is defined as invoice(invoice_id PRIMARY KEY, customer_id, customer_email, currency_code, currency_symbol), where customer_email is that customer's current email and currency_symbol is fixed by the currency code. What has to change for the table to be in third normal form?
- Nothing: invoice_id is a single-column primary key, so no column can depend on part of the key.
- Add UNIQUE (currency_code, currency_symbol) to invoice so the two columns can never disagree.
- Move currency_symbol into a currency table keyed by currency_code, and customer_email into a customer table keyed by customer_id.
- Make (invoice_id, customer_id, currency_code) the primary key so every remaining column depends on the whole key.
Show answer
Inside invoice, customer_id and currency_code are ordinary non-key columns, and each one determines another non-key column, so the email and the symbol are two-hop facts about a customer and a currency; giving each determinant its own table makes them one-hop facts about a key. The first option is tempting because a single-column key genuinely does eliminate partial dependencies, but that is second normal form; third normal form is about dependencies between non-key columns, which a narrow key does nothing to prevent. The UNIQUE option is worse than insufficient: it would forbid two invoices ever sharing a currency.