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.
- The relation needs to be in BCNF.
- The relation should be free of multivalued dependencies.
Now let us consider an 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
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.
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.
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
Table 2: Sub_Taught
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.