Fifth Normal Form 5NF

A relation R is in Fifth Normal Form 5NF — also called Project-Join Normal Form PJ/NF — if, for every non-trivial join dependency R1, R2, ..., Rn in R:

Definition

A relation R is in Fifth Normal Form (5NF) — also called Project-Join Normal Form (PJ/NF) — if, for every non-trivial join dependency *(R1, R2, ..., Rn)* in R:

Every Ri is a superkey of R

5NF eliminates redundancy caused by join dependencies that cannot be detected by functional or multivalued dependencies alone.

Join Dependency

A join dependency *(R1, R2, ..., Rn)* holds on relation R if R can be reconstructed (without loss) by taking the natural join of its projections onto R1, R2, ..., Rn.

R = π_R1(R) ⋈ π_R2(R) ⋈ ... ⋈ π_Rn(R)

Every MVD is a special case of a join dependency (with n=2).
A relation in 4NF may still have cyclic join dependencies that cause anomalies.

Motivating Example

Supplier	Project	Part
S1	P1	Pa
S1	P2	Pa
S2	P1	Pa

Business rules (cyclic constraint)

ONLY IF

Instance

With the cyclic constraint

Join Dependency Diagram

SupplierProjectPart can be decomposed into THREE binary relations:

SupplierProject(Supplier, Project)

ProjectPart(Project, Part)

SupplierPart(Supplier, Part)

The original can be reconstructed:

SupplierProject ⋈ ProjectPart ⋈ SupplierPart = SupplierProjectPart

Join Dependency: *(SupplierProject, ProjectPart, SupplierPart)

For 5NF, each projection must be on a superkey of the relation.

Checking 5NF

PK	(Supplier, Project, Part) — no single or pair is PK
Join Dependency	*(SP, PP, SupPart) where:
SP⁺ = {Supplier, Project} ≠ all attributes	NOT a superkey
PP⁺ = {Project, Part}	NOT a superkey
SupPart⁺ = {Supplier, Part}	NOT a superkey
Decompose into 3 binary relations	each is in 5NF ✓

5NF vs 4NF

4NF handles binary join dependencies (MVDs): R(A, B, C) with A →→ B and A →→ C → Decompose into (A,B) and (A,C) 5NF handles n-ary join dependencies (n ≥ 3): R(A, B, C) where no single MVD explains the constraint but the triple join dependency *(AB, BC, AC) holds → Decompose into (A,B), (B,C), (A,C)

Practical Notes

5NF is rarely applied in practice because:
Join dependencies are hard to identify without deep business rule analysis
The decomposition into many small tables makes queries complex
Most DBMS designs stop at BCNF or 3NF

5NF is mainly of theoretical importance
Also called Projection-Join Normal Form (PJ/NF)

Normal Forms — Complete Summary

│ 1NF	Remove non-atomic attributes │
│ └─ 2NF	Remove partial FDs │
│ └─ 3NF	Remove transitive FDs │
│ └─ BCNF	All FD determinants are superkeys │
│ └─ 4NF	All MVD determinants superkeys │
│ └─ 5NF	All JD components superkey│
1NF	Non-atomic values, repeating groups
2NF	Partial functional dependencies
3NF	Transitive functional dependencies
BCNF	FD where LHS is not a superkey
4NF	Non-trivial multivalued dependencies
5NF	Non-trivial join dependencies

Definition

A relation R is in Fifth Normal Form (5NF) — also called Project-Join Normal Form (PJ/NF) — if, for every non-trivial join dependency *(R1, R2, ..., Rn)* in R:

Every Ri is a superkey of R

5NF eliminates redundancy caused by join dependencies that cannot be detected by functional or multivalued dependencies alone.

Join Dependency

A join dependency *(R1, R2, ..., Rn)* holds on relation R if R can be reconstructed (without loss) by taking the natural join of its projections onto R1, R2, ..., Rn.

R = π_R1(R) ⋈ π_R2(R) ⋈ ... ⋈ π_Rn(R)

Every MVD is a special case of a join dependency (with n=2).
A relation in 4NF may still have cyclic join dependencies that cause anomalies.

Motivating Example

Supplier	Project	Part
S1	P1	Pa
S1	P2	Pa
S2	P1	Pa

Business rules (cyclic constraint)

ONLY IF

Instance

With the cyclic constraint

Join Dependency Diagram

SupplierProjectPart can be decomposed into THREE binary relations:

SupplierProject(Supplier, Project)

ProjectPart(Project, Part)

SupplierPart(Supplier, Part)

The original can be reconstructed:

SupplierProject ⋈ ProjectPart ⋈ SupplierPart = SupplierProjectPart

Join Dependency: *(SupplierProject, ProjectPart, SupplierPart)

For 5NF, each projection must be on a superkey of the relation.

Checking 5NF

PK	(Supplier, Project, Part) — no single or pair is PK
Join Dependency	*(SP, PP, SupPart) where:
SP⁺ = {Supplier, Project} ≠ all attributes	NOT a superkey
PP⁺ = {Project, Part}	NOT a superkey
SupPart⁺ = {Supplier, Part}	NOT a superkey
Decompose into 3 binary relations	each is in 5NF ✓

5NF vs 4NF

Practical Notes

5NF is rarely applied in practice because:
Join dependencies are hard to identify without deep business rule analysis
The decomposition into many small tables makes queries complex
Most DBMS designs stop at BCNF or 3NF

5NF is mainly of theoretical importance
Also called Projection-Join Normal Form (PJ/NF)

Normal Forms — Complete Summary

│ 1NF	Remove non-atomic attributes │
│ └─ 2NF	Remove partial FDs │
│ └─ 3NF	Remove transitive FDs │
│ └─ BCNF	All FD determinants are superkeys │
│ └─ 4NF	All MVD determinants superkeys │
│ └─ 5NF	All JD components superkey│
1NF	Non-atomic values, repeating groups
2NF	Partial functional dependencies
3NF	Transitive functional dependencies
BCNF	FD where LHS is not a superkey
4NF	Non-trivial multivalued dependencies
5NF	Non-trivial join dependencies

Fifth Normal Form 5NF

Definition

Join Dependency

Motivating Example

Join Dependency Diagram

Checking 5NF

5NF vs 4NF

Practical Notes

Normal Forms — Complete Summary

Continue learning this concept

Fifth Normal Form 5NF

Definition

Join Dependency

Motivating Example

Join Dependency Diagram

Checking 5NF

5NF vs 4NF

Practical Notes

Normal Forms — Complete Summary

Continue learning this concept