Definition of DBMS Multivalued Dependency
DBMS Multivalued Dependency is a conditional property which states that when two table attributes are self-determining to each other although both be governed by a third attribute. In DBMS, Multivalued Dependency comprises of minimum two attributes that are reliant on a third attribute therefore it continuously needs three attributes. Hence, a Multivalued Dependency is stated as a whole constraint between two groups of attributes in a relationship but it needs definite tuples to be available in a relation. In 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 exists of one or more table rows in a database that implies one or multiple other table rows present in the identical table so that 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 as 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, 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 which derives from MVD’s is not transferrable by pushing the database schema in BCNF. There is a tougher normal form known as 4NF which delights MVD’s as FD’s if it derives to disintegration, but not when defining keys of the relation.
How Multivalued Dependency works 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.
In DBMS, a table is defined to hold Multivalued dependency when the succeeding conditions follow to be true:
- For a dependency XY, if for the only value of X, many values of B occurs, then the table may hold multivalued dependency.
- Besides, a table should include minimum 3 table columns for it to have 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.
Thus, when all these above conditions are satisfied for any relation in a table then it is defined to have a multivalued dependency.
In comparison to the functional dependency, the DBMS Multivalued Dependency needs few tuples to be existent in a relation. So, this Multivalued Dependency is said to be an exceptional instance of tuple-generating dependency and plays an important role in Database Normalization (4NF).
Multivalued Dependency is also a special example of a Join Dependency, having only two groups of values contained i.e. can be said as a binary join dependency.
A Multivalued dependency occurs when there exist a minimum of three attributes such as A, B, C in a relation 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 there exists a company for car manufacturing that generates two different colors of car models every year. These colors can be denoted as blue and black.
select * from ‘multivalue’
From the above table information, we can define that the columns Colour and Manuf_Year are reliant on Model_Num and at the same time self-governing of each other also. So, in this case, we can declare that the two columns are Multivalued 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 in 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. As you can the Functional Dependency is denoted by a single arrow whereas Multivalued Dependency is denoted by two arrows.
Also, using Functional Dependency a user can get the difference between decent and corrupt designs of a database 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
This is a guide to DBMS Multivalued Dependency. Here we discuss the definition, syntax, How Multivalued Dependency works in DBMS? along with the examples respectively. You may also have a look at the following articles to learn more –
