Fourth Normal Form (4NF)

Sub-Page 5: Eliminating Non-Trivial Multi-Valued Dependencies

What is 4NF?

Fourth Normal Form (4NF) builds on BCNF by eliminating non-trivial multi-valued dependencies (MVDs) not implied by candidate keys. An MVD A ↠ B means that for each value of A, there is a set of values for B independent of other attributes.

A table is in 4NF if it is in BCNF and has no non-trivial MVDs. This reduces data redundancy in cases of independent multi-valued relationships.

Example Table (in BCNF):

Course	Instructor	Textbook
CS101	Dr. Smith	Database Systems
CS101	Dr. Smith	SQL Guide
CS101	Dr. Jones	Database Systems
CS101	Dr. Jones	SQL Guide

Multi-valued dependency: Course ↠ Instructor, Course ↠ Textbook. Instructors and Textbooks are independent, causing redundancy.

4NF Real-World Scenarios

Scenario 1: University Course Materials

Course	Instructor	Textbook
CS101	Dr. Smith	Database Systems
CS101	Dr. Smith	SQL Guide
CS101	Dr. Jones	Database Systems
CS101	Dr. Jones	SQL Guide

Multi-valued dependency: Course ↠ Instructor, Course ↠ Textbook. Each course has multiple instructors and textbooks, independently assigned, causing redundancy in the table.

4NF Solution

Split into CourseInstructors and CourseTextbooks tables to eliminate MVDs.

CourseInstructors(Course, Instructor)

CourseTextbooks(Course, Textbook)

Scenario 2: Employee Skills and Projects

EmployeeID	Skill	Project
E1	Python	ProjA
E1	Python	ProjB
E1	Java	ProjA
E1	Java	ProjB

Multi-valued dependency: EmployeeID ↠ Skill, EmployeeID ↠ Project. Skills and projects are independent, leading to redundant rows in project management systems.

4NF Solution

Split into EmployeeSkills and EmployeeProjects tables.

EmployeeSkills(EmployeeID, Skill)

EmployeeProjects(EmployeeID, Project)

SQL for 4NF

-- SQL for Scenario 1: University Course Materials
CREATE TABLE CourseInstructors (
    Course VARCHAR(10),
    Instructor VARCHAR(50),
    PRIMARY KEY (Course, Instructor)
);
CREATE TABLE CourseTextbooks (
    Course VARCHAR(10),
    Textbook VARCHAR(100),
    PRIMARY KEY (Course, Textbook)
);

-- SQL for Scenario 2: Employee Skills and Projects
CREATE TABLE EmployeeSkills (
    EmployeeID VARCHAR(10),
    Skill VARCHAR(50),
    PRIMARY KEY (EmployeeID, Skill)
);
CREATE TABLE EmployeeProjects (
    EmployeeID VARCHAR(10),
    Project VARCHAR(10),
    PRIMARY KEY (EmployeeID, Project)
);

These schemas eliminate non-trivial multi-valued dependencies by separating independent multi-valued relationships into distinct tables.

Key Takeaways

Multi-Valued Dependencies

4NF removes non-trivial MVDs not implied by candidate keys.

Reduces Redundancy

Separates independent multi-valued relationships to eliminate redundancy.

Foundation for 5NF

4NF sets the stage for handling join dependencies in 5NF.

Back to BCNF Next: 5NF