4NF, 5NF, Lossless Decomposition and Dependency Preservation

In one line

4NF removes independent multivalued facts stored in one table (a person's skills and languages); 5NF removes join dependencies that can only be reconstructed from three or more projections. Every decomposition must be lossless (joining back gives exactly the original) and ideally dependency-preserving (rules still checkable per table).

0/5 · 0%

Think of it like this

A CV table with one row per (person, skill, language). If Asha knows 3 skills and 2 languages you need 6 rows of every combination, and adding a language means adding a row for every skill. Skills and languages are independent facts crammed together.

Key ideas

01
Multivalued dependency X ↠ Y: for each X, the set of Y values is independent of the other attributes. 4NF: no non-trivial MVD unless X is a super key. Fix: split into person_skill and person_language.
02
Join dependency / 5NF: a table can be split into three projections and rebuilt only by joining all three, e.g. (agent, company, product) where "if an agent sells for a company and sells a product type, and the company makes that product, then the agent sells it for that company". Rare in practice; recognise it rather than hunt for it.
03
Lossless decomposition: splitting R into R1 and R2 is lossless if the common attributes form a key of R1 or R2. Otherwise joining back creates spurious rows.
04
Dependency preservation: every FD can still be enforced inside a single table after the split. 3NF decompositions can always preserve dependencies; BCNF sometimes can't, and then you choose 3NF or add a check elsewhere.

Code & diagrams

4nf.sqlsql

-- Violates 4NF: independent facts multiplied together
-- person_skill_language(person_id, skill, language)

CREATE TABLE person_skill    (person_id bigint, skill text,    PRIMARY KEY (person_id, skill));
CREATE TABLE person_language (person_id bigint, language text, PRIMARY KEY (person_id, language));

-- Lossy split example (don't do this):
-- R(order_id, product_id, warehouse_id) split into (order_id, warehouse_id) and (product_id, warehouse_id)
-- joining back on warehouse_id invents order/product pairs that never existed.

Interview problem

The problem

Decide whether to split

restaurant_offering(restaurant_id, cuisine, delivery_area), where a restaurant's cuisines and delivery areas are independent. Should it be split? What if delivery areas differed by cuisine?

When it breaks

Lossy decomposition in a refactor

What you see

After splitting a table on a non-key column, reports join back and return phantom combinations, e.g. customers "ordering" products they never bought.

Fix & prevent

Split only when the shared columns are a key of one side; verify by comparing count(*) of the original and the re-joined result on real data.

Explain it without notes

What makes a decomposition lossless?

Practice

Explain why BCNF decomposition can lose dependency preservation, using the teaching example.

Trade-offs

↔
4NF/5NF remove rare but real redundancy; over-applying them fragments the model with little benefit.

Done when you can

I can recognise MVDs and join dependencies and verify a decomposition is lossless.

Command Palette