DBMS- Multivalued Dependency

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.

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.

Name Language Subjects
Anuj English E-Commerce
Anuj Hindi Accounts
Deepak Hindi Economics
Manoj Punjabi Accounts
Manoj English Accounts

Dependency Diagram

The dependency diagram for the above table will be as follows:

Multivalued dependency Name, Language
name subjects

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.

Name Language Subjects
Anuj English E-Commerce
Anuj Hindi Accounts
Deepak Hindi Economics
Manoj French Accounts
Manoj English Accounts
Sanjana English Computers
Sanjana English Maths

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

Name Language
Anuj English
Anuj Hindi
Deepak Hindi
Manoj French
Manoj English
Sanjana English

Table 2: Teacher_Subjects

Name Subjects
Anuj E-Commerce
Anuj Accounts
Deepak Economics
Manoj Accounts
Sanjana Computers
Sanjana Maths

Now, the non key attributes are dependent fully on the key attribute in the relations. Hence, the tables are now independent of multivalued dependency.

Be First to Comment

Leave a Reply

Your email address will not be published.