Multivalued Dependency

1
Multivalued Dependency
CS157A Lecture 18

• Prof. Sin-Min Lee
• Department of Computer Science

2
(No Transcript)
3
(No Transcript)
4
(No Transcript)
5
(No Transcript)
6
(No Transcript)
7
(No Transcript)
8
(No Transcript)
9
(No Transcript)
10
(No Transcript)
11
(No Transcript)
12
(No Transcript)
13
(No Transcript)
14
(No Transcript)
15
(No Transcript)
16
(No Transcript)
17
(No Transcript)
18
(No Transcript)
19
HIGHER NORMAL FORMS
BCNF
5NF
1NF
2NF
3NF
4NF
functional dependencies
multivalued dependencies
join dependencies
20
(No Transcript)
21
(No Transcript)
22
(No Transcript)
23
(No Transcript)
24
(No Transcript)
25
(No Transcript)
26
STUDENT learns MODULE
STUDENT enjoys HOBBY
John learns Pascal Databases
Java
John enjoys Music Jogging
Mary enjoys Reading Tennis
Cycling
Mary learns C
Jenny learns C Databases
Jenny enjoys Music
27
PROFILE is in BCNF but exhibits redundancy and I,
D ad U anomalies
multivalued dependency X ??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
29
(No Transcript)
30
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

31
Example

• Drinkers(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.

32
MVD Rules

• 1. Every FD is an MVD.
• Because if X ?Y, then swapping Ys between tuples
  that agree on X doesnt 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 Doesnt 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 Petes
• Sue 408 555-9999 Bud A.B.
• Sue 408 555-9999 Wicked Ale Petes
• name ?? areaCode phones holds, but neither
  name ?? areaCode nor name ?? phones do.

34
4NF

• Eliminate redundancy due to multiplicative effect
  of MVDs.
• Roughly treat MVDs 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 ? (RY).

R
Y
X
35
Example

• Drinkers(name, addr, phones, beersLiked)
• FD name ? addr
• Nontrivial MVDs 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

a b R - a - b
t1 a1ai ai1aj aj1an
t2 a1ai bi1bj bj1bn
t3 a1ai ai1aj bj1bn
t4 a1ai bi1bj aj1an
39
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
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 Smiths 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)

loan-number customer-name customer-street customer-city
L-23 Smith North Rye
L-27 Smith Main Manchester
42
(No Transcript)
43
(No Transcript)
44
(No Transcript)
45
(No Transcript)
46
(No Transcript)
47
(No Transcript)
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

1. Reflexivity rule. If a is a set attributes, and b
   C a, then a ? b holds.
2. Augmentation rule. If a ? b holds, and c is a set
   of attributes, then ca ? cb holds.
3. Transitivity rule. If a ? b holds, and b ? c
   holds, then a ? c holds.
4. 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.

51
Theory of Multivalued Dependencies (cont)

1. Multivalued union rule. If a ?? b holds, and a ??
   c holds, then a ?? bc holds.
2. Intersection rule. If a ?? b holds, and a ?? c
   holds, then a ?? b 3 c holds.
3. Difference rule. If a ?? b holds, and a ?? c
   holds, then a ?? b - c holds and a ?? c - b holds.