Fifth Normal Form (5NF)

Sub-Page 6: Eliminating Non-Trivial Join Dependencies

What is 5NF?

Fifth Normal Form (5NF), also known as Project-Join Normal Form (PJNF), ensures a table has no non-trivial join dependencies. A join dependency exists when a table can be decomposed into projections that, when joined, reconstruct the original table losslessly.

A table is in 5NF if it is in 4NF and cannot be further decomposed into smaller tables without losing data. This eliminates redundancy from complex relationships.

Example Table (in 4NF):

Supplier	Part	Project
S1	P1	J1
S1	P1	J2
S2	P1	J1

Join dependency: The table can be decomposed into Supplier-Part, Part-Project, and Supplier-Project projections, which when joined reconstruct the table. This causes redundancy if not managed.

5NF Real-World Scenarios

Scenario 1: Supply Chain Management

Supplier	Part	Project
S1	P1	J1
S1	P1	J2
S2	P1	J1
S2	P2	J2

Join dependency: The table can be decomposed into Supplier-Part, Part-Project, and Supplier-Project projections. Adding a new supplier-part-project combination may require multiple rows, increasing redundancy in supply chain databases.

5NF Solution

Decompose into SupplierParts, PartProjects, and SupplierProjects tables.

SupplierParts(Supplier, Part)

PartProjects(Part, Project)

SupplierProjects(Supplier, Project)

Scenario 2: University Scheduling

Course	Room	TimeSlot
CS101	R101	MW9
CS101	R101	TR10
CS101	R102	MW9
MATH201	R102	TR10

Join dependency: The table can be decomposed into Course-Room, Room-TimeSlot, and Course-TimeSlot projections. Scheduling a new course-room-time combination requires multiple rows, causing redundancy.

5NF Solution

Decompose into CourseRooms, RoomTimeSlots, and CourseTimeSlots tables.

CourseRooms(Course, Room)

RoomTimeSlots(Room, TimeSlot)

CourseTimeSlots(Course, TimeSlot)

SQL for 5NF

-- SQL for Scenario 1: Supply Chain Management
CREATE TABLE SupplierParts (
    Supplier VARCHAR(10),
    Part VARCHAR(10),
    PRIMARY KEY (Supplier, Part)
);
CREATE TABLE PartProjects (
    Part VARCHAR(10),
    Project VARCHAR(10),
    PRIMARY KEY (Part, Project)
);
CREATE TABLE SupplierProjects (
    Supplier VARCHAR(10),
    Project VARCHAR(10),
    PRIMARY KEY (Supplier, Project)
);

-- SQL for Scenario 2: University Scheduling
CREATE TABLE CourseRooms (
    Course VARCHAR(10),
    Room VARCHAR(10),
    PRIMARY KEY (Course, Room)
);
CREATE TABLE RoomTimeSlots (
    Room VARCHAR(10),
    TimeSlot VARCHAR(10),
    PRIMARY KEY (Room, TimeSlot)
);
CREATE TABLE CourseTimeSlots (
    Course VARCHAR(10),
    TimeSlot VARCHAR(10),
    PRIMARY KEY (Course, TimeSlot)
);

These schemas eliminate non-trivial join dependencies by decomposing tables into projections that can be joined losslessly.

Key Takeaways

Join Dependencies

5NF eliminates non-trivial join dependencies to ensure lossless decomposition.

Minimizes Redundancy

Decomposes tables to remove redundancy from complex relationships.

Highest Normalization

5NF is typically the final normalization step for most databases.

Back to 4NF