According to 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 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.
holds on if, in any legal relation, for all pairs of tuples and in such that, there exist tuples and in such that
In more simple words the above condition can be expressed as follows: if we denote by the tuple having values for collectively equal to correspondingly, then whenever the tuples and exist in, the tuples and should also exist in.
Example
Consider this example of a relation of university courses, the books recommended for the course, and the lecturers who will be teaching the course:
Course
Book
Lecturer
AHA
Silberschatz
John D
AHA
Nederpelt
John D
AHA
Silberschatz
William M
AHA
Nederpelt
William M
AHA
Silberschatz
Christian G
AHA
Nederpelt
Christian G
OSO
Silberschatz
John D
OSO
Silberschatz
William M
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: and equivalently . Databases with multivalued dependencies thus exhibit redundancy. In database normalization, fourth normal form requires that for every nontrivial multivalued dependency XY, X is a superkey. A multivalued dependency XY is trivial if Y is a subset of X, or if is the whole set of attributes of the relation.
Properties
If, Then
If and, Then
If and, then
The following also involve functional dependencies:
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 :
*Coalescence: If X Y and W s.t. W Y =, W Z, and Z Y, then X Z
Definitions
; full constraint: A constraint which expresses something about all attributes in a database. That a multivalued dependency is a full constraintfollows from its definition,as where it says something about the attributes. ; tuple-generating dependency: A dependency which explicitly requires certain tuples to be present in the relation. ; trivial multivalued dependency 1: A multivalued dependency which involves all the attributes of a relation i.e.. A trivial multivalued dependency implies, for tuples and, tuples and which are equal to and. ; trivial multivalued dependency 2: A multivalued dependency for which.
