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.
The formal definition is as follows:[1] Let
be a relation schema and let
be sets of attributes.
The multivalued dependency
α ↠ β
if, for any legal relation
and all pairs of tuples
, there exist tuples
∖ ( α ∪ β ) ] =
∖ ( α ∪ β ) ]
Informally, if one denotes by
the tuple having values for
collectively equal to
The multivalued dependency can be schematically depicted as shown below:
Consider this example of a relation of university courses, the books recommended for the course, and the lecturers who will be teaching the course: Because the lecturers attached to the course and the books attached to the course are independent of each other, this database design has a multivalued dependency; if we were to add a new book to the AHA course, we would have to add one record for each of the lecturers on that course, and vice versa.
Put formally, there are two multivalued dependencies in this relation: {course}
{book} and equivalently {course}
Databases with multivalued dependencies thus exhibit redundancy.
In database normalization, fourth normal form requires that for every nontrivial multivalued dependency X
A multivalued dependency X
Y is trivial if Y is a subset of X, or if
is the whole set of attributes of the relation.
The following also involve functional dependencies: The above rules are sound and complete.