DBMS -Fifth Normal Form or 5NF

Before going into depth about what the Fifth Normal Form while normalizing databases is, we should have knowledge about a type of dependency known as Join Dependency.

What actually a Join Dependency is?

Join dependency is a state of database where a relation is divided into two or more smaller relations. When it is going through various steps of normalization and is not capable of being recombined to form the original relation. Such a state is known as a Join Dependency.

A join dependency is a situation which arises due to a lossy decomposition of relations during the course of normalization.

But the decomposition should be lossless so that the original schema of the relations is maintained. This is where the Fifth Normal Form (or 5NF) comes to the rescue.

What is Fifth Normal Form (5NF)?

A relation is said to be in the Fifth Normal Form, if it has a loss-less decomposition of relation into any number of smaller relations.The Fifth Normal Form of normalizing is more of a theoretical concept and is of little practical use for the database designers.

The Fifth Normal Form is also known as project join normal form (PJNF).

Constraints to be followed in Fifth Normal Form

A relation is said to be in the Fifth Normal Form if it follows the following constraints:

1. The relation should be in its Fourth Normal Form (4NF).
2. The Join Dependency must be implied by the candidate keys.

To explain the concept further let us take up an example.

Example

Let us consider a relation named TEACHERS. This relation is made up of three components namely Subject, T_name, and Class. Let us assume that this relation stores information about different teachers taking up different subjects for different classes.

Have a look at the data in the table

Relation name: TEACHERS

This table is in 4th normal form as there are no multivalued dependencies here.

Now, let us split the relation into 2 sub relations say R1 and R2. Let R1 comprises of (Subject, T_Name) and R2 comprises of (T_Name, Class).

Relation 1 (R1):

Relation 2 (R2):

Now, if we have a look at the relations, we may note that the redundancy in the tables has been eliminated. But, relevant information like English being taught by Anju to class 10^th has been lost.

Now, let us create a natural join over the column T_Name for the above relations R1 and R2. Let us name the newly formed relation as R4. The data in the resultant relation (R4) will be as follows:

Relation formed after natural join (R4):

Here, as a result of the join, we will find an extra tuple being created which did not exist in the original relation R1. This leads to inconsistency in data in relations when we recombine the relations.

How to solve the problem of Spurious Tuple?

In order to solve the inconsistency created in data, we need to maintain another relation (say R3 here) which comprises of the combination of the remaining two columns of the original relation.

Hence, we create a relation R3 with Subject, Class as its attributes. Here is the resultant relation.

Relation: R3

Now, the next step is to create a natural join over the columnsClass, Subjects for the above relations R3 and the previously joined relationR4. Name this resulting relation R5. Have a look at the resulting tuples now.

Relation: R5

Hence, it is now that we have the original relation back again.

Conclusion

So, join dependency states that a relation once decomposed into two or more sub relations, then it must be capable of joined back to the original relation. This can only be achieved when we maintain proper combinations of sub relations which when joined form the original relation in a lossless manner.

Anupreet Kalsi