Updated July 1, 2023
Definition of DBMS Multivalued Dependency
DBMS Multivalued Dependency is a conditional property that states that two table attributes are self-determining to each other, although a third attribute governs both. In DBMS, Multivalued Dependency comprises a minimum of two attributes that rely on a third attribute; therefore, it continuously needs three attributes. In the DBMS Normalization process, the Fourth Normal Form (4NF) rule should not contain any Multivalued Dependency present in a single table to satisfy its conditions. Because due to multivalued dependency, it may lead to the un-essential recurrence of data and other variances as well.
Syntax:
When there exist one or more table rows in a database, that implies one or multiple other table rows are present in the identical table. Here the Multivalued dependencies occur.
If the table consists of A, B, and C attributes, then B and C will be denoted as multi-valued facts of A. It is signified by the double arrow shown below:
For illustration,
A B
A C
In this type of case, the DBMS Multivalued Dependency occurs only if B and C are independent attributes.
But a table having multivalued dependency will violate the rule of 4NF. Here, 4NF is a kind of Normalization: Normalization is denoted as a technique to organize the data in the server database that supports a user to escape data redundancy and operations like insertion, deletion, and update inconsistency. This is a method of analyzing the relation schemas on the basis of their diverse functional dependencies and the primary key.
Therefore, Normalization is integral to relational database theory. It may hold the consequence of duplicating the identical data within the server database that may affect in the creation of extra tables.
4NF should hold BCNF i.e. Boyce-Codd Normal Form. The redundancy derived from MVDs is not transferrable by pushing the database schema in BCNF.
How does Multivalued Dependency Work in DBMS?
In DBMS, MVD or, say, Multivalued Dependency defines that for one value of attribute ‘x’, multiple values of attribute ‘y’ be existent. We will write this as:
X Y
This is read as; x is multi-valued dependent on b.
- For a dependency XY, if for the only value of X, many values of B occur, then the table may hold a multivalued dependency.
- Besides, a table should include at least 3 table columns for a multivalued dependency.
- Finally, for a relation R(X, Y, Z), if there exists a multivalued dependency between X and Y, so Y and Z should have to be independent of each other.
In comparison to the functional dependency, the DBMS Multivalued Dependency needs few tuples to be existent in a relation.
A Multivalued dependency occurs when there exist a minimum of three attributes, such as A, B, and C, in a relationship where for a value of A, there is a well-stated group of values of B and a well-stated group of values of C. Still, the set of values of B is independent of set C and follows vice versa.
Examples
Let us study the DBMS Multivalued Dependency with certain examples for reference explained below:
Suppose a company for car manufacturing generates two different colors of car models every year.
select * from 'multivalue'
From the above table information, we can define that the columns Colour and Manuf_Year rely on Model_Num and, at the same time, are self-governing of each other. So, in this case, we can declare that the two columns are Multivalued and Dependent on the column named Model_Num.
We can view the illustration of these dependencies below:
Model_Num Manuf_Year
Model_Num Colour
The above presentation can be defined as Model_Num multi-determined Manuf_Year and Model_Num multi-determined Colour.
But this condition or situation violates the 4NF rule in the DBMS Normalization process.
Also, you can see that Model_Num 205 has more than one color model in the year 2015, where color and manuf_year are independent but dependent on model_num.
For correcting it and removing multivalued dependency, we can split it into two distinct tables and break the dependency. One table will include Model_Num and Colour, and the other will contain Model_Num and Manuf_Year.
This process will break the multivalued dependency, and so now we can view two functional dependencies:
Model_Num Colour
Model_Num Manuf_Year
Functional Dependency in DBMS defines the relation of an attribute to another attribute in a database schema which thus provides benefit to support the quality of data records in the server.
Also, using Functional Dependency a user can get the difference between decent and corrupt database designs in DBMS.
Conclusion
A multivalued dependency on a relation R, suppose AB, concludes that if the two tuples of R approve on entire attributes of A, formerly their components in B might be exchanged, and thus the outcome will be two tuples which are in the relation also. We can now say that for every value of A, the values of B are independent of the values of R-A-B.
Recommended Articles
We hope that this EDUCBA information on “DBMS Multivalued Dependency” was beneficial to you. You can view EDUCBA’s recommended articles for more information.
