Home » DBMS 5NF

Fifth normal form (5NF)

  • A relation is in 5NF if it is in 4NF and not contains any join dependency and joining should be lossless.
  • 5NF is satisfied when all the tables are broken into as many tables as possible in order to avoid redundancy.
  • 5NF is also known as Project-join normal form (PJ/NF).

Example

SUBJECT LECTURER SEMESTER
Computer Anshika Semester 1
Computer John Semester 1
Math John Semester 1
Math Akash Semester 2
Chemistry Praveen Semester 1

In the above table, John takes both Computer and Math class for Semester 1 but he doesn’t take Math class for Semester 2. In this case, combination of all these fields required to identify a valid data.

Suppose we add a new Semester as Semester 3 but do not know about the subject and who will be taking that subject so we leave Lecturer and Subject as NULL. But all three columns together acts as a primary key, so we can’t leave other two columns blank.

So to make the above table into 5NF, we can decompose it into three relations P1, P2 & P3:

P1

SEMESTER SUBJECT
Semester 1 Computer
Semester 1 Math
Semester 1 Chemistry
Semester 2 Math

P2

SUBJECT LECTURER
Computer Anshika
Computer John
Math John
Math Akash
Chemistry Praveen

P3

SEMSTER LECTURER
Semester 1 Anshika
Semester 1 John
Semester 1 John
Semester 2 Akash
Semester 1 Praveen

You may also like