Multivalued dependency

Formal definition

The formal definition is given as follows.

Let R be a relational schema and let α ⊆ R and β ⊆ R (subsets). The multivalued dependency
α ↠ β
(which can be read as α multidetermines β ) holds on R if, in any legal relation r ( R ) , for all pairs of tuples t 1 and t 2 in r such that t 1 [ α ] = t 2 [ α ] , there exist tuples t 3 and t 4 in r such that
t 1 [ α ] = t 2 [ α ] = t 3 [ α ] = t 4 [ α ]
t 3 [ β ] = t 1 [ β ]
t 3 [ R − β ] = t 2 [ R − β ]
t 4 [ β ] = t 2 [ β ]
t 4 [ R − β ] = t 1 [ R − β ]

In more simple words the above condition can be expressed as follows: if we denote by ( x , y , z ) the tuple having values for α , β , R − α − β collectively equal to x , y , z , correspondingly, then whenever the tuples ( a , b , c ) and ( a , d , e ) exist in r , the tuples ( a , b , e ) and ( a , d , c ) should also exist in r .

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:

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}  ↠  {lecturer}.
Databases with multivalued dependencies thus exhibit redundancy. In database normalization, fourth normal form requires that either every multivalued dependency X  ↠  Y is trivial or for every nontrivial multivalued dependency X  ↠  Y, X is a superkey. A multivalued dependency X ↠ Y is trivial if Y is a subset of X, or if X ∪ Y is the whole set of attributes of the relation.

Properties

If α ↠ β , Then α ↠ R − β

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

Definitions

full constraint

A constraint which expresses something about all attributes in a database. (In contrast to an embedded constraint.) That a multivalued dependency is a full constraint follows from its definition,as where it says something about the attributes R − β .

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. R = α ∪ β . A trivial multivalued dependency implies, for tuples t 1 and t 2 , tuples t 3 and t 4 which are equal to t 1 and t 2 .

trivial multivalued dependency 2

A multivalued dependency for which β ⊆ α .