DBMS 5NF

A relation is said to be in 5NF, if it satisfies the following conditions:

• It is in 4NF.
• It cannot be further broken down to smaller tables.
• The decomposed tables join operation must be lossless, which means the decomposed tables joined using natural join should produce original relation without loosing any information.

5NF Example

Consider this table, a same course can be assigned to different teachers.

Stu_Name	Stu_Course	Stu_Professor
Ram	C++	Smith
Ram	Java	Paul
Shyam	Java	Paul
Shyam	DBMS	Tom
Shyam	C++	Robert

The problem with this relation is that all three columns combine together form a primary key. If you want to add a new course to the table, the Stu_Name and Stu_Professor fields are empty (null), which is not acceptable as they are part of primary key.

This relation is not in 5NF as it contains redundant data and it can be broken down to smaller tables.

Let’s break it down to three tables like this:

Table 1: Course

Stu_Name	Stu_Course
Ram	C++
Ram	Java
Shyam	C++
Shyam	DBMS
Shyam	Java

Table 2: Professor

Stu_Course	Stu_Professor
C++	Smith
C++	Robert
DBMS	Tom
Java	Paul

Table 3: Teaches

Stu_Name	Stu_Professor
Ram	Smith
Ram	Paul
Shyam	Tom
Shyam	Paul
Shyam	Robert