In database theory, a multivalued dependency is a full constraint between two sets of attributes in a relation.
In contrast to the functional dependency, the multivalued dependency requires that certain tuples be present in a relation. Therefore, a multivalued dependency is a special case of tuple-generating dependency. The multivalued dependency plays a role in the 4NF database normalization.
A multivalued dependency is a special case of a join dependency, with only two sets of values involved, i.e. it is a binary join dependency.
A multivalued dependency exists when there are at least three attributes (like X,Y and Z) in a relation and for a value of X there is a well defined set of values of Y and a well defined set of values of Z. However, the set of values of Y is independent of set Z and vice versa.
Formal definition
The formal definition is as follows:[1]
Let be a relation and let and be sets of attributes. The multivalued dependency (" multidetermines ") holds on if, for any legal relation and all pairs of tuples and in such that , there exist tuples and in such that:
Informally, if one denotes by the tuple having values for collectively equal to , then whenever the tuples and exist in , the tuples and should also exist in .
The multivalued dependency can be schematically depicted as shown below:
Properties
- If , Then
- If and , Then
- If and , then
The following also involve functional dependencies:
- If , then
- If and , then
The above rules are sound and complete.
- A decomposition of R into (X, Y) and (X, R − Y) is a lossless-join decomposition if and only if X Y holds in R.
- Every FD is an MVD because if X Y, then swapping Y's between tuples that agree on X doesn't create new tuples.
- Splitting Doesn't Hold. Like FD's, we cannot generally split the left side of an MVD.But unlike FD's, we cannot split the right side either, sometimes you have to leave several attributes on the right side.
- Closure of a set of MVDs is the set of all MVDs that can be inferred using the following rules (Armstrong's axioms):
- Complementation: If X Y, then X R - Y
- Augmentation: If X Y and Z W, then XW YZ
- Transitivity: If X Y and Y Z, then X Z - Y
- Replication: If X Y, then X Y
- Coalescence: If X Y and W s.t. W Y = , W Z, and Z Y, then X Z