Fifth Normal Form (5NF)
Press Next → or use ← → arrow keys
The Supplier Catalog That Kept Lying
This table can already be in 4NF — no bad functional or multi-valued dependency. The redundancy comes from a join dependency: a three-way constraint that only 5NF can see.
Where 5NF Sits on the Ladder
BCNF removes functional-dependency redundancy. 4NF removes independent multi-valued redundancy. 5NF removes what's left: redundancy from a join dependency — the kind that needs three or more tables to eliminate.
5NF is also called Project–Join Normal Form (PJNF) — because it's about projecting a table into pieces and joining them back without loss.
What Is a Join Dependency?
A relation R has a join dependency ⋈(R1, R2, …, Rn) if R can be reconstructed exactly by projecting it onto R1…Rn and joining them back — no rows lost, no rows invented.
A multi-valued dependency (4NF) is just a join dependency with two components. A join dependency is the bigger idea: the redundancy may only dissolve when you split into three or more tables at once.
A non-trivial JD arises from a cyclic business rule among three attributes — "if these three pairwise facts hold, the triple must hold too." That cycle is exactly what lets a 3-way split rebuild the original cleanly.
SUPPLIES — 4NF, Yet Still Redundant
| Supplier | Part | Project |
|---|---|---|
| S1 | P1 | J2 |
| S1 | P2 | J1 |
| S2 | P1 | J1 |
| S1 | P1 | J1 |
The whole row is the key — no FD or MVD is violated, so it's already in 4NF.
If S1 supplies P1, P1 is used by J1, and S1 works on J1, then S1 must supply P1 to J1. That constraint links all three attributes at once — a functional/multi-valued lens can't express it.
A Two-Table Split Invents Fake Rows
Project onto just SP(Supplier, Part) and PJ(Part, Project), then join on Part:
The join produces a row (S2, P1, J2) that was never in the original. Rejoining two projections doesn't rebuild the truth — it fabricates a delivery. Two tables are not enough.
Decompose Into Three Binary Tables
Project onto all three pairs — supplier–part, part–project, and project–supplier:
| Supplier | Part |
|---|---|
| S1 | P1 |
| S1 | P2 |
| S2 | P1 |
| Part | Project |
|---|---|
| P1 | J2 |
| P2 | J1 |
| P1 | J1 |
| Project | Supplier |
|---|---|
| J2 | S1 |
| J1 | S1 |
| J1 | S2 |
The two-table split lost the Project–Supplier relationship. JS restores it — and it's precisely this third table that will veto the fake row.
The Three-Way Join Is Lossless
The fake (S2, P1, J2) survives SP ⋈ PJ — but joining with JS checks "does S2 work on J2?" It doesn't: (J2, S2) isn't in JS, so the fake row is filtered out. Only the true four rows remain.
Fifth Normal Form — The Definition
A relation is in 5NF (PJNF) if it is in 4NF and every non-trivial join dependency in it is implied by its candidate keys — i.e., each JD's components are all superkeys.
"If a table can be split into smaller pieces that rejoin with no loss and no fake rows, it should be." 5NF means it can't be split any further without losing information.
MVD vs JD · Trivial vs Non-Trivial
One projection is the whole relation. Harmless — nothing to split. 5NF ignores it.
Every projection is smaller than R, yet they rejoin exactly. This is the redundancy 5NF removes.
4NF vs 5NF — Side by Side
| Aspect | 4NF | 5NF (PJNF) |
|---|---|---|
| Dependency handled | Multi-valued X →→ Y | Join dependency ⋈(R1…Rn) |
| Split needed | Two tables | Three or more tables |
| Core rule | Every non-trivial MVD's left side is a superkey | Every non-trivial JD is implied by candidate keys |
| Redundancy removed | Independent multi-valued sets | Cyclic three-way constraints |
| Prerequisite | Must be in BCNF | Must be in 4NF |
| Generality | Special case (binary JD) | The general case |
4NF = "one key fans out into two unrelated sets." 5NF = "three attributes are locked in a cycle that only a three-way split can untangle."
Academic vs Industry Lenses
Academia proves 5NF is the ceiling of projection-join normalization; industry rarely needs to reach for it — but when a three-way cyclic rule appears, 5NF is the only clean answer.
When 5NF Matters — and When It's Overkill
1NF → 2NF → 3NF → BCNF → 4NF → 5NF. Beyond 5NF lie 6NF (temporal / irreducible relations) and DKNF (domain-key), but 5NF is the end of the classic projection-join road.
Golden Rules of 5NF
The Last Step of Normalization
5NF catches redundancy created by a join dependency — a cyclic three-way constraint that a two-table split can't honour without inventing fake rows. Project onto all three pairwise relationships and the original rejoins losslessly and spurious-free. When a table can't be split any further without losing information, it's in 5NF.
If two projections rejoin with fake rows but three rejoin cleanly, the table had a join dependency — and splitting it into all three is 5NF.
🧩 End of tutorial · Press ← to review, or click Restart