DBMS – Fourth Normal Form (4NF)

Once we have normalized relation to Third Normal Form or BCNF, it is still possible that at certain times anomalies like Insert, Update or Delete may still arise. This usually happens when there are multivalued dependencies in the table.

As discussed in the previous post multivalued dependencies occur in a table when multiple values need to be added for independent attributes in the relation.

Constraints to be followed in Fourth Normal Form

For a relation to be in its Fourth Normal Form the following constraints need to be followed.

These are:

  1. The relation needs to be in BCNF.
  2. The relation should be free of multivalued dependencies.

Now let us consider an example.

Example

Let us assume a relation TEACHERS. This relation stores information regarding different teachers in a School working in different departments hence teaching different subjects.

Table Name: TEACHERS

Department T_Name Subject
Computer Anuj Visual Basic
Computer Suresh IT
Computer Mandeep IT
Computer Kriti Visual Basic
Maths Anu Statistics
Maths Anu Maths

This relation has three attributes. The first attribute stores the information regarding Department in which the teacher works. The second attribute stores the names of the teachers. And the third attribute stores the subjects the teachers teach.

Now, let us have a look at the dependency in the relation.

Dependencies

Two of the attributes of the table are functionally dependent on one another. But there are two such columns which are not related to each other that is they are independent attributes.

Fourth Normal Form

If we have a closer look at this table, we will be able to find that the table suffers from multivalued dependency as follows:

  • T_Name ->-> Department
  • Subject ->-> Department

Since the relation is suffering from multivalued dependency, it is not in its fourth normal form.

How to normalize the relation to Fourth Normal Form?

In order to normalize the relation, we need to split the relation into sub relations such that the attributes which are independent of each other are parts of different relations. This will help in removing the multivalued dependency from the relation and hence normalizing it to the fourth normal form.

Let us do this step in our example. On splitting the relation into two sub relations, we get the following result:

Table 1: Department

Department T_Name
Computer Anuj
Computer Suresh
Computer Mandeep
Computer Kriti
Maths Anu

Table 2: Sub_Taught

Department Subject
Computer Visual Basic
Computer IT
Maths Statistics
Maths Maths

Now, the relations are in its Fourth Normal Form.

Advantages of Fourth Normal Form (4NF)

Splitting the relation into two or more sub relations helps in the following advantages:

  • Eliminates multivalued dependencies
  • Reduces redundancy to a certain extent.
  • Increased efficiency.

Be First to Comment

Leave a Reply

Your email address will not be published. Required fields are marked *