Higher Normal Forms in DBMS – Understanding 4NF and 5NF with Examples

As your database design grows more complex, so do the relationships among attributes. Higher Normal Forms — Fourth Normal Form (4NF) and Fifth Normal Form (5NF) — help resolve advanced issues like multi-valued dependencies and join dependencies, ensuring data remains consistent, non-redundant, and easier to maintain.

🔁 Fourth Normal Form (4NF)

📘 Definition

A table is in 4NF if:

It is already in Boyce-Codd Normal Form (BCNF)
It contains no multi-valued dependencies

A multi-valued dependency (MVD) occurs when one attribute determines multiple independent values of another attribute.

🧪 Example: Student_Activities Table

Student_ID	Sport	Club
101	Soccer	Drama
101	Basketball	Drama
101	Soccer	Music
102	Tennis	Science

Dependencies:

Student_ID →→ Sport
Student_ID →→ Club

Both attributes vary independently, creating a multi-valued dependency, which violates 4NF.

🛠 Converting to 4NF

Split the table into two independent tables:

✅ Student_Sport Table

Student_ID	Sport
101	Soccer
101	Basketball
102	Tennis

✅ Student_Club Table

Student_ID	Club
101	Drama
101	Music
102	Science

These tables now eliminate multi-valued dependencies, meeting the requirements of 4NF.

🧩 Fifth Normal Form (5NF)

📘 Definition

A table is in 5NF (also known as Projection-Join Normal Form - PJNF) if:

It is in 4NF
It has no join dependencies, i.e., it cannot be further decomposed without loss of data

🧪 Example: Course_Instructor_Student Table

Course_ID	Instructor	Student_ID
C101	Dr. Smith	101
C101	Dr. Smith	102
C102	Dr. Brown	103
C102	Dr. Brown	104

Each course can have multiple students and multiple instructors. But instructors and students are not related to each other directly — only through the course.

🛠 Converting to 5NF

Break it down into three projections:

✅ Course_Instructor Table

Course_ID	Instructor
C101	Dr. Smith
C102	Dr. Brown

✅ Course_Student Table

Course_ID	Student_ID
C101	101
C101	102
C102	103
C102	104

✅ Instructor_Student Table

Instructor	Student_ID
Dr. Smith	101
Dr. Smith	102
Dr. Brown	103
Dr. Brown	104

Now, joining these three tables reconstructs the original data without redundancy, satisfying 5NF.

🧠 Why Higher Normal Forms Matter

Normal Form	Purpose	Eliminates
4NF	Resolves multi-valued dependencies	Independent relationships
5NF	Resolves join dependencies	Redundant combinations

✅ Final Thoughts

By applying 4NF and 5NF, your database becomes:

🔁 Free of complex redundancies
🧱 Structured for scalability
🔐 Resilient to anomalies

These higher normal forms are essential for enterprise-level systems with intricate data relationships and multiple many-to-many connections.

📚 What We’ve Covered in the Series

1NF – Atomicity, no repeating groups
2NF – Eliminate partial dependencies
3NF – Eliminate transitive dependencies
BCNF – Superkey-based dependencies
4NF & 5NF – Multi-valued and join dependencies

🎯 Summary Table

Normal Form	Focus	Removes
1NF	Atomic values	Repeating groups
2NF	Full functional dependency	Partial dependencies
3NF	Transitive dependency	Indirect attribute dependency
BCNF	Superkey dependency	Overlooked 3NF anomalies
4NF	Multi-valued dependency	Independent multivalues
5NF	Join dependency	Redundant joins

Table of Contents