Before going into the details about Fourth Normal Form (4NF), we must understand what multivalued dependency really is.
What is meant by Multivalued Dependency?
Multivalued dependency is the type of dependency in which we have three attributes which are in close dependence with each other. Here two of the attributes are closely dependent on each other whereas one attribute is independent of all the others.
Due to this reason, while inserting data into the relation, we have to insert multiple values for the attribute which is independent of other attributes. Hence, for this reason; multivalued dependency is also stated to be a tuple generating dependency.
Representing multivalued dependency
When representing a normal functional dependency, we make use of ->(arrow) symbol. But in order to show a multivalued dependency we use ->-> symbol.
For instance, A ->-> B defines a multivalued dependency between A and B. This example shows that attribute B has multiple values for a single value of attribute A.
Let us have a closer look at multivalued dependency with the help of an example.
For instance, let us consider a relation named TEACHER. Let this relation keep a record of teachers, languages they can speak fluently, and the subjects they can teach.
TEACHER(Name, Language, Subjects)
Now here will be have three attributes which are closely related to each other.
The dependency diagram for the above table will be as follows:
If we see carefully, then the language spoken by the teacher is dependent on the name of the teacher, the subjects taken up by the teacher are also dependent on their name, but the languages spoken by them and the subjects taught by them are independent of each other. That is,
Name ->-> Language and Name->-> Subjects
Hence, we can conclude that the table has multivalued dependency. For such relations we need to take special care when inserting records.
Inserting values in relation with multivalued dependency
When inserting values in a relation with multivalued dependency, we have to insert multiple values for attributes which are independent of each other.
For instance, in the above table when we insert a value for an employee who is efficient in English but teaches the subject computers and math’s. When we have to insert this record, multiple rows for the same have to be inserted.
Have a look at the table after inserting the new record.
How to correct Multivalued Dependency?
In order to eliminate this dependency, we can divide the relation into two or more sub tables. Dividing the relation will remove the multivalued dependence there by reducing redundancy to some extent in the relation.
We would do the same for the above relation. We will split the relation into two sub relations linked via the foreign key attribute.
Table 1: Teacher_Lang
Table 2: Teacher_Subjects
Now, the non key attributes are dependent fully on the key attribute in the relations. Hence, the tables are now independent of multivalued dependency.