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 |