Course contentsShow
← Computer Science
Lesson 1643 of 2,87237. Database Design and NormalizationFree lesson

Fifth Normal Form (5NF) Overview

Understanding join dependencies and when tables should be decomposed to eliminate redundancy through projection.

Fifth Normal Form (5NF) Overview

What you'll learn: You'll understand when to decompose tables into multiple projections to eliminate redundancy caused by complex multi-valued dependencies.

What is Fifth Normal Form?

Fifth Normal Form (5NF), also called Projection-Join Normal Form (PJ/NF), addresses a subtle form of redundancy that occurs when a table contains join dependencies. A table is in 5NF when it cannot be decomposed into smaller tables without losing information, unless that decomposition eliminates redundancy through projection.

Think of it this way: imagine you're tracking which salespeople can sell which products in which regions. If every valid combination can be reconstructed by joining smaller tables (salesperson-product, salesperson-region, product-region), you have a join dependency. Storing all three together creates redundancy.

Join Dependencies Explained

A join dependency exists when a table can be split into multiple smaller tables, and the original can be perfectly reconstructed by joining them back together. Unlike functional dependencies (which involve one attribute determining another), join dependencies involve multiple relationships that interact.

When Should You Decompose?

Decompose to 5NF when:

  • A table is already in 4NF (no multi-valued dependencies)
  • The table contains redundant combinations that can be expressed as intersections of multiple relationships
  • You can split it into three or more projections that, when joined, reproduce the original without spurious tuples

Warning: 5NF is rarely needed in practice. Most real-world databases stop at 3NF or BCNF because 5NF decomposition can create overly complex schemas with many small tables and expensive joins.

Example Context

Suppose you have Salesperson_Product_Region(Salesperson, Product, Region). If the rule is "a salesperson can sell a product in a region only if all three relationships independently exist," you have a join dependency requiring decomposition.

Key Takeaway: Fifth Normal Form eliminates redundancy caused by join dependencies by decomposing tables into the smallest projections that can be losslessly rejoined, but it's only necessary for complex multi-way relationships.