Multivalued Dependency - PowerPoint PPT Presentation

1 / 51
About This Presentation
Title:

Multivalued Dependency

Description:

John enjoys Music. Jogging. Mary enjoys Reading. Tennis. Cycling. Jenny enjoys Music. STUDENT ... customer-name customer-street customer-city ... – PowerPoint PPT presentation

Number of Views:43
Avg rating:3.0/5.0
Slides: 52
Provided by: Lee144
Category:

less

Transcript and Presenter's Notes

Title: 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.
Write a Comment
User Comments (0)
About PowerShow.com