Multivalued dependency in DBMS

DBMSDatabaseMySQL

What is Multi-valued dependency?

When existence of one or more rows in a table implies one or more other rows in the same table, then the Multi-valued dependencies occur.

If a table has attributes P, Q and R, then Q and R are multi-valued facts of P.

It is represented by double arrow −

->->


For our example:

P->->Q
P->->R


In the above case, Multivalued Dependency exists only if Q and R are independent attributes.

A table with multivalued dependency violates the 4NF.

Example

Let us see an example &mins;

<Student>

StudentName
CourseDiscipline
Activities
Amit
Mathematics
Singing
Amit
Mathematics
Dancing
Yuvraj
Computers
Cricket
Akash
Literature
Dancing
Akash
Literature
Cricket
Akash
Literature
Singing


In the above table, we can see Students Amit and Akash have interest in more than one activity.

This is multivalued dependency because CourseDiscipline of a student are independent of Activities, but are dependent on the student.

Therefore, multivalued dependency −

StudentName ->-> CourseDiscipline
StudentName ->-> Activities


The above relation violates Fourth Normal Form in Normalization.

To correct it, divide the table into two separate tables and break Multivalued Dependency −

<StudentCourse>

StudentName
CourseDiscipline
Amit
Mathematics
Amit
Mathematics
Yuvraj
Computers
Akash
Literature
Akash
Literature
Akash
Literature


<StudentActivities>

StudentName
Activities
Amit
Singing
Amit
Dancing
Yuvraj
Cricket
Akash
Dancing
Akash
Cricket
Akash
Singing


This breaks the multivalued dependency and now we have two functional dependencies −

StudentName -> CourseDiscipline
StudentName - > Activities

raja
Published on 04-Jul-2018 07:58:50
Advertisements