An Example of Database Normalization (Cont.)

---

Fifth Normal Form (Cont.)
Assume that the tuples are formed based on the following scenario;

• Whenever Lecturer L1 teaches Subject S1,
• and Course C1 has Subject S1,
• and Lecturer L1 teaches at least one subject in Course C1,
• then Lecturer L1 will be teaching Subject S1 in Course C1.

Note that we have this scenario for explaining the 5NF, otherwise you will not see it properly. Now if I try to decompose this relation into two relations for minimizing redundant data, I will be having these two tables (Sequences are added for understanding joins only).

Now, if I need to rejoin these with natural join, this will be the result. See the highlighted one. It is the result of join dependency. It is a spurious tuple which is not valid.

In order to avoid it, in order to make the relations as 5NF relation, let’s introduce another relation:

Now, if we rejoin all three, we will not see any issue. Therefore, in order to minimize redundant data and make sure no join dependency, make sure relations are formed as 5NF relations.