Multivalued Dependencies - Database Design - Lecture Slides, Slides of Database Management Systems (DBMS)

Download Multivalued Dependencies - Database Design - Lecture Slides and more Slides Database Management Systems (DBMS) in PDF only on Docsity!

Multivalued Dependencies

Functional Dependency

b

c

4

Q

$

Domain (X) (^) Range (Y)

a

Multivalued Dependency

Smith

Jones

Cooper

Anna John

Lila

Elsa Chris Employee (X) Dependent (Y)

Definition of MVD

• A multivalued dependency is a full constraint between two sets of attributes in a relation.
• In contrast to the functional independency , the multivalued dependency requires that certain tuples be present in a relation. Therefore, a multivalued dependency is also referred as a tuple-generating dependency. The multivalued dependency also plays a role in 4NF normalization.

A Formal Definition

Let R be a relation schema and let and. The

multivalued dependency α - >> β 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 − β]

Definition of MVD (cont.)

• A multivalued dependency on R , X ->> Y, says that if

two tuples of R agree on all the attributes of X , then their components in Y may be swapped, and the result will be two tuples that are also in the relation.

• i.e., for each value of X , the values of Y are

independent of the values of R - X - Y.

Example

Here is possible data satisfying these MVD’s:

name areaCode phone beersLiked manf Sue 650 555-1111 Bud A.B. Sue 650 555-1111 WickedAle Pete’s Sue 415 555-9999 Bud A.B. Sue 415 555-9999 WickedAle Pete’s

But we cannot swap area codes or phones by themselves. That is, neither name->->areaCode nor name->->phone holds for this relation.

Properties of MVD

• f α - >> β, Then α ->> R − β
• If α - >> β and δ ⊆ γ , Then αδ - >> βγ
• If α - >> β and If β - >> γ, then α - >> γ - β

The following also involve functional dependencies:

• If α - >> β , then α - >> β
• If α - > β and β - > γ, then α - > γ – β
• 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.

Example of lossy decomposition

A B C

A B C

A B

A C

Original table Decomposition

Reconstruction