Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
What is multivalued dependency in DBMS?
Multivalued dependency (MVD) is having the presence of one or more rows in a table. It implies the presence of one or more other rows in that same table. A multivalued dependency prevents fourth normal form. A multivalued dependency involves at least three attributes of a table.
It is represented with a symbol "->->" in DBMS.
X->Y relates one value of X to one value of Y.
X->->Y (read as X multidetermines Y) relates one value of X to many values of Y.
A Nontrivial MVD occurs when X->->Y and X->->z where Y and Z are not dependent are independent to each other. Non-trivial MVD produces redundancy.
We use multivalued conditions in two different ways −
To test relations to decide if they are lawful under a given arrangement of practical and multivalued dependencies.
To determine limitations on the arrangement of lawful relations. We will concern ourselves just with relations that fulfill a given arrangement of practical and multivalued dependencies.
MVD transitive rule
If A ->B holds, and B ->C holds, then A ->B −>C holds.
Example
Given FD set is as follows −
ISBN--> TITLE,PUBLISHER
ISBN,NO -->AUTHOR
PUBLISHER -->PU_URL
We need to prove the rule. Consider A=ISBN,B=PUBLISHER,C=PU_URL. To find the Transitive rule is implied, find the cover of A+ and compute.
Now start with x={ISBN}
The FD ISBN--> TITLE, PUBLISHER has LHS which is completely contained in current attribute set x.
Extend x by FD RHS attribute set, giving x={ISBN,TITLE,PUBLISHER}
Now FD:PUBLISHER -->PU_URL is applicable
Add RHS attribute set of FD to current attribute SET x, giving x={ISBN,TITLE,PUBLISHER,PU_URL}
Here we can conclude that ISBN-->PU_URL
The 4th Normal Form can cause the Multivalued Dependencies. If a relation is in Boyce codee Normal form, it has to remove the multivalued Dependencies.
Explanation − The multivalued dependencies is that, if there is a dependency or relation in a table, then one value has multiple dependencies occur.
Let us consider an example as given below. Consider the following table −
| id | department | shift |
|---|---|---|
| 1 | coding | day |
| 2 | Hr | day |
| 3 | Network | night |
In the above table, id 2 has two departments Hr and Network. And shift timing day and night.
When we select the details with the id 2, then it will result the table as follows −
| id | department | shift |
|---|---|---|
| 2 | Hr | day |
| 2 | Network | night |
| 2 | Hr | night |
| 2 | Network | day |
This means there exist multivalued dependencies. In this, the relation between department and shift is nothing.
This can be rectified by removing the multivalued dependency as, making this data in to two tables as below −
Table 1
| id | department |
|---|---|
| 1 | coding |
| 2 | Hr |
| 2 | network |
Table 2
| id | shift |
|---|---|
| 1 | day |
| 2 | day |
| 2 | night |
The 4th normal form is applied to remove the multivalued dependencies in the data table.
The fourth normal form thus defines the multivalued dependencies.
8K+ Views
