Multivalued Dependency

1. CS157A Lecture 18 Multivalued Dependency Prof. Sin-Min Lee Department of Computer Science

19. HIGHER NORMAL FORMS BCNF 5NF 2NF 4NF 3NF 1NF functional dependencies multivalued dependencies join dependencies

26. STUDENT MODULE HOBBY Pascal Music John Databases Jogging Java C++ Reading Mary Tennis Cycling C++ Music Jenny Databases STUDENT enjoysHOBBY STUDENT learnsMODULE John learnsPascal Databases Java John enjoys Music Jogging Mary enjoys Reading Tennis Cycling Mary learnsC++ Jenny learnsC++ Databases Jenny enjoys Music

27. PROFILE 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 is in BCNF but exhibits redundancy and I, D ad U anomalies multivalued dependencyX Y holds in R if: whenever two tuples of R agree in value of X, their image sets in pR(X,Y) are the same; X, Y, Z - pairwise disjoint subsets of R (X,Y,Z) STUDENT  MODULE STUDENT  HOBBY mutually independent

28. Fourth Normal Form R(X, Y, Z) is in 4NF if, whenever a multivalued dependency XY holds for R, so does the functional dependency XA for all attributes A in R preventing conjunction of unrelated facts 4NF: every MVD is FD

30. Multivalued Dependencies The multivalued dependencyX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

31. Example Drinkers(name,addr,phones,beersLiked) with MVD Namephones. If Drinkers has the two tuples: name addr phones beersLiked sue ap1b1 sue ap2b2 it must also have the same tuples with phones components swapped: name addr phones beersLiked sue ap2b1 sue ap1b2 Note: we must check this condition for all pairs of tuples that agree on name, not just one pair.

32. MVD Rules 1. 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phonesholds in Drinkers, so doesnameaddr beersLiked.

33. Splitting Doesn’t Hold Sometimes 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 Ale Pete’s Sue 408 555-9999 Bud A.B. Sue 408 555-9999 Wicked Ale Pete’s • nameareaCode phones holds, but neithernameareaCode nor namephones do.

34. 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 MVDX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 stringentthan BCNF. • Decompose R, using4NF violation XY,into XY and X  (R—Y). R Y X

35. 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)

36. Multivalued Dependencies • Multivalued dependencies are referred to as tuple-generating dependencies. • Let R be a relation schema and let a R and b R. The multivalued dependency is a  b holds on R if, in any legal relation r( R ), for all pairs of tuples t1 and t2 in r such that t1[ a ] = t2[ a ], there exist tuples t3 and t4 in r such that

37. Multivalued Dependencies (cont) • t1[ a ] = t2[ a ] = t3[ a ] = t4[ a ] t3[ b ] = t1[ b ] t3[ R - b ] = t2[ R - b ] t4[ b ] = t2[ b ] t4[ R - b ] = t1[ R - b ] • The multivalued dependency a b says that the relationship between a and b is independent of the relationship between a and R - b.

38. Multivalued Dependencies (cont) • If the multivalued dependency a b is satisfied by all relations on schema R, then a b is a trivial multivalued dependency on schema R. • Thus, a b is trivial if b a or b a = R Tabular representation of a b

39. Multivalued Dependencies (cont) • To illustrate the difference between functional and multivalued dependencies, we consider again the BC-schema. Graph 1

40. 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.

41. 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)

48. 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.

49. Theory of Multivalued Dependencies • Reflexivity rule. If a is a set attributes, and b C a, then a  b holds. • Augmentation rule. If a  b holds, and c is a set of attributes, then ca  cb holds. • Transitivity rule. If a  b holds, and b  c holds, then a  c holds. • Complementation rule. If a  b holds, then a  R – b – a holds.

50. Theory of Multivalued Dependencies 5. Multivalued augmentation rule. If a  b holds, and c R and d C c, then ca  db holds. 6. Multivalued transitivity rule. If a  b holds, and b  c holds, then a  c – b holds. 7. Replication rule. If a  b holds, then a  b. 8. Coalescence rule. If a  b holds, and c C b, and there is a d such that d C R, and d 3 b = w, and d  c, then a  c holds.