Multivalued Dependency

Prof. Sin-Min Lee

Department of Computer Science

1NF 2NF 3NF BCNF 4NF 5NF

functional dependencies

multivalueddependencies

joindependencies

HIGHER NORMAL FORMS

MaryC++ Reading

TennisCycling

Jenny C++ MusicDatabases

JohnPascal MusicDatabases JoggingJava

STUDENT MODULE HOBBY

STUDENT learns MODULE STUDENT enjoys HOBBY

John learns Pascal Databases Java

Mary learns C++

Jenny learns C++ Databases

John enjoys Music Jogging

Mary enjoys Reading Tennis Cycling

Jenny enjoys Music

STUDENT MODULE HOBBY

John Pascal Music

John Pascal Jogging

John Databases Music

John Databases Jogging

John Java Music

John Java Jogging

Mary C++ Reading

Mary C++ Tennis

Mary C++ Cycling

Jenny C++ Music

Jenny Databases Music

PROFILE

multivalued dependency X Y holds in R if:

whenever two tuples of R agree in value of X,

their image sets in R(X,Y) are the same;

X, Y, Z - pairwise disjoint subsets of R (X,Y,Z)

STUDENT MODULESTUDENT HOBBYmutually independent

PROFILE is in BCNF but exhibitsredundancy and I, D ad U anomalies

Fourth Normal Form

R(X, Y, Z) is in 4NF if,

whenever a multivalued dependency XY holds for R,

so does the functional dependency XA for all attributes A in R

R is in 4NF

x1 x2 … xn y1 y2 … yn z1 z2 … zn

X Y Z

if

then fd

mvd

preventing conjunction of unrelated facts

4NF: every MVD is FD

STUDENT MODULE STUDENT HOBBY

John Pascal John Music

John Databases John Jogging

John Java Mary Reading

Mary C++ Mary Tennis

Jenny C++ Mary Cycling

Jenny Databases Jenny Music

PROFILE

LEISUREACADEMIC

Multivalued Dependencies

The multivalued dependency X Y holds in a relation R if whenever we have two tuples of R that agree in all the attributes of X, then we can swap their Y components and get two new tuples that are also in R.

X Y others

ExampleDrinkers(name, addr, phones, beersLiked)

with MVD Name phones. If Drinkers has the two tuples:

name addr phones beersLiked sue a p1 b1

sue a p2 b2

it must also have the same tuples with phones components swapped:

name addr phones beersLiked sue a p2 b1

sue a p1 b2

Note: we must check this condition for all pairs of tuples that agree on name, not just one pair.

MVD Rules1.Every FD is an MVD.

– Because if X Y, then swapping Y’s between tuples that agree on X doesn’t create new tuples.

– Example, in Drinkers: name addr.

2.Complementation: if X Y, then X Z, where Z is all attributes not in X or Y.– Example: since name phones

holds in Drinkers, so doesname addr beersLiked.

Splitting Doesn’t HoldSometimes you need to have several attributes on the right of an

MVD. For example:

Drinkers(name, areaCode, phones, beersLiked, beerManf)name areaCode phones beersLiked beerManf

Sue 831 555-1111 Bud A.B.

Sue 831 555-1111 Wicked AlePete’s

Sue 408 555-9999 Bud A.B.

Sue 408 555-9999 Wicked AlePete’s

• name areaCode phones holds, but neither name areaCode nor name phones do.

4NF

Eliminate redundancy due to multiplicative effect of MVD’s.• Roughly: treat MVD’s as FD's for decomposition, but not

for finding keys.• Formally: R is in Fourth Normal Form if whenever MVD

X Y is nontrivial (Y is not a subset of X, and X Y is not all attributes), then X is a superkey.– Remember, X Y implies X Y, so 4NF is more stringent

than BCNF.

• Decompose R, using4NF violation X Y,into XY and X (R—Y).

R Y X

Example

Drinkers(name, addr, phones, beersLiked)• FD: name addr• Nontrivial MVD’s: name phones andname beersLiked.

• Only key: {name, phones, beersLiked}

• All three dependencies above violate 4NF.

• Successive decomposition yields 4NF relations:D1(name, addr)D2(name, phones)D3(name, beersLiked)

Multivalued Dependencies

• Multivalued dependencies are referred to as tuple-generating dependencies.

• Let R be a relation schema and let R and R. The multivalued dependency is holds on R if, in any legal relation r( R ), for all pairs of tuples t1 and t2 in r such that t1[] = t2[ ], there exist tuples t3 and t4 in r such that

Multivalued Dependencies (cont)

• t1[ ] = t2[ ] = t3[ ] = t4[ ]t3[ ] = t1[ ]t3[ R - ] = t2[ R - ]t4[ ] = t2[ ]t4[ R - ] = t1[ R - ]

• The multivalued dependency says that the relationship between and is independent of the relationship between and R - .

Multivalued Dependencies (cont)• If the multivalued dependency is satisfied by all

relations on schema R, then is a trivial multivalued dependency on schema R.

• Thus, is trivial if or = RTabular representation of

R - -

t1 a1…ai ai+1…aj aj+1…an

t2 a1…ai bi+1…bj bj+1…bn

t3 a1…ai ai+1…aj bj+1…bn

t4 a1…ai bi+1…bj aj+1…an

Multivalued Dependencies (cont)

• To illustrate the difference between functional and multivalued dependencies, we consider again the BC-

schema.

Graph 1

loan-number customer-name customer-street customer-city

L-23 Smith North Rye

L-23 Smith Main Manchester

L-93 Curry Lake Horseneck

Multivalued Dependencies (cont)

• On graph 1, we must repeat the loan number once for each address a customer has, and we must repeat the address for each loan a customer has. This repetition is unnecessary, since the relationship between that customer and his address is independent of the relationship between that customer and a loan.

• If a customer (say, Smith) has a loan (say, loan number L-23), we want that loan to be associated with all Smith’s addresses.

Multivalued Dependencies (cont)• The relation on graph 2 is illegal, therefore to make this

relation legal, we need to add the tuples (L-23, Smith, Main, Manchester) and (L-27, Smith, North, Rye) to the bc relation of graph 2.

Graph 2 (an illegal bc relation)

loan-number customer-name customer-street customer-city

L-23 Smith North Rye

L-27 Smith Main Manchester

Multivalued Dependencies (cont)

• Comparing the preceding example with our definition of multivalued dependency, we see that we want the multivalued dependency to hold.

customer-name customer-street customer-city

• As was the case for functional dependencies, we shall use multivalued dependencies in two ways:1. To test relations to determine whether they are legal under a given set of functional and multivalued dependencies.2. To specify constraints on the set of legal relations; we shall thus concern ourselves with only those relations that specify a given set of functional and multivalued dependencies.

Theory of Multivalued Dependencies

1. Reflexivity rule. If is a set attributes, and , then holds.

2. Augmentation rule. If holds, and is a set of attributes, then holds.

3. Transitivity rule. If holds, and holds, then holds.

4. Complementation rule. If holds, then R – – holds.

Theory of Multivalued Dependencies

5. Multivalued augmentation rule. If holds, and R and , then holds.6. Multivalued transitivity rule. If holds, and holds, then – holds.7. Replication rule. If holds, then .8. Coalescence rule. If holds, and , and there is a such that R, and = , and , then holds.

Theory of Multivalued Dependencies (cont)

1. Multivalued union rule. If holds, and holds, then holds.

2. Intersection rule. If holds, and holds, then holds.

3. Difference rule. If holds, and holds, then - holds and - holds.