MENU


Fourth and Fifth Normal Forms

4NF and 5NF go past BCNF to address redundancy that functional dependencies alone don't capture – cases where a table mixes together two or more facts that are independent of each other but both attached to the same entity.

Fourth Normal Form (4NF)

A table is in 4NF when it is in BCNF and contains no non-trivial multivalued dependency (MVD) other than one that is a full candidate key. An MVD, written X ↠ Y ("X multi-determines Y"), means: for a given value of X, the set of values of Y associated with it is independent of every other column in the row.

This shows up when a table has a composite key made of two or more genuinely independent multi-valued attributes of the same entity. Because relational tables store rows, not sets, representing two independent multi-valued facts in one table forces every combination of the two to be spelled out as a separate row – a cross product – even though the two facts have nothing to do with each other.

Worked Example

A consultants table records each consultant's skills and the languages they speak. A consultant's skills and the languages they speak are independent of one another – knowing a consultant does SQL says nothing about whether they speak Spanish.

consultant_id ↠ skillSkills don't depend on which language row happens to be paired with them.
consultant_id ↠ languageLanguages don't depend on which skill row happens to be paired with them.

Before — a consultant with 2 skills and 3 languages needs 6 rows just to record every skill/language pairing, none of which is a real fact (the consultant never actually paired "SQL" specifically with "Spanish" specifically – the table just has to enumerate the full cross product to stay row-shaped). Adding a third skill means adding 3 more rows, not 1.

After — split the two independent multi-valued attributes into two separate tables, each holding one fact per row. See the worked CREATE TABLE statements below.

Fifth Normal Form (5NF / PJNF)

Fifth Normal Form, also called Project-Join Normal Form (PJNF), is in 4NF and additionally contains no non-trivial join dependency that isn't implied by its candidate keys. A join dependency exists when a table can be losslessly reconstructed by joining three or more smaller projections of it, but cannot be losslessly reconstructed from any two of them alone – a strictly three-or-more-way constraint that pairwise decomposition (which is all 4NF checks for) doesn't catch.

The textbook case: agents, companies, and products, with the business rule "an agent sells a company's product only if the agent represents that company, the company makes that product, and the agent sells that product (for some company)." If that three-way rule genuinely holds, the table decomposes losslessly into three pairwise tables – agent_company, company_product, agent_product – and joining all three back together reproduces exactly the valid combinations, no more and no less. Decomposing into only two of the three would lose information or introduce combinations that were never actually true.

5NF is rarely pursued deliberately in application schemas – it mostly shows up as a theoretical ceiling that BCNF/4NF decomposition happens to already satisfy. Its practical value is mainly as a checklist: before assuming a many-to-many-to-many table decomposes cleanly into pairs, verify the three-way business rule actually holds; if it doesn't, the ternary relationship needs to stay a single table (or an associative table with all three foreign keys) rather than being split into pairs that would silently allow combinations nobody asked for.

See also Entity-Relationship Modeling for representing N:M and ternary relationships as junction tables in the first place, and Denormalization for when a small amount of the redundancy 4NF/5NF remove is deliberately reintroduced for read performance. 

-- BEFORE: violates 4NF. skill and language are independent multivalued
-- attributes of consultant_id, forced into one table as a cross product.
CREATE TABLE consultant_skills_languages (
    consultant_id INT UNSIGNED NOT NULL,
    skill         VARCHAR(50) NOT NULL,
    language      VARCHAR(50) NOT NULL,
    PRIMARY KEY (consultant_id, skill, language)
) ENGINE=InnoDB;

-- Consultant 1 knows SQL and Python, and speaks English and Spanish:
-- correct storage of these facts requires all 4 combinations, even though
-- no single combination is individually meaningful.
INSERT INTO consultant_skills_languages VALUES
    (1, 'SQL',    'English'),
    (1, 'SQL',    'Spanish'),
    (1, 'Python', 'English'),
    (1, 'Python', 'Spanish');

-- AFTER: 4NF-compliant. Each independent multivalued fact gets its own
-- table; no cross product, no redundancy.
CREATE TABLE consultant_skills (
    consultant_id INT UNSIGNED NOT NULL,
    skill         VARCHAR(50) NOT NULL,
    PRIMARY KEY (consultant_id, skill)
) ENGINE=InnoDB;

CREATE TABLE consultant_languages (
    consultant_id INT UNSIGNED NOT NULL,
    language      VARCHAR(50) NOT NULL,
    PRIMARY KEY (consultant_id, language)
) ENGINE=InnoDB;

INSERT INTO consultant_skills VALUES (1, 'SQL'), (1, 'Python');
INSERT INTO consultant_languages VALUES (1, 'English'), (1, 'Spanish');

-- Adding a third skill is a single INSERT, not 2 more rows per language:
INSERT INTO consultant_skills VALUES (1, 'MySQL Administration');

-- The full cross product, if a query genuinely needs it, is reconstructed
-- with an explicit JOIN rather than stored redundantly:
SELECT cs.consultant_id, cs.skill, cl.language
FROM consultant_skills cs
JOIN consultant_languages cl ON cl.consultant_id = cs.consultant_id;