Design%20Theory%20for%20Relational%20Databases - PowerPoint PPT Presentation

About This Presentation
Title:

Design%20Theory%20for%20Relational%20Databases

Description:

X - Y is an assertion about a relation R that whenever two tuples of R agree on ... of the fact that Anheuser-Busch manufactures Bud. 34. Boyce-Codd Normal Form ... – PowerPoint PPT presentation

Number of Views:39
Avg rating:3.0/5.0
Slides: 64
Provided by: jeff475
Learn more at: https://crab.rutgers.edu
Category:

less

Transcript and Presenter's Notes

Title: Design%20Theory%20for%20Relational%20Databases


1
Design Theory for Relational Databases
  • Functional Dependencies
  • Decompositions
  • Normal Forms

2
Functional Dependencies
  • X -gtY is an assertion about a relation R that
    whenever two tuples of R agree on all the
    attributes of X, then they must also agree on all
    attributes in set Y.
  • Say X -gtY holds in R.
  • Convention , X, Y, Z represent sets of
    attributes A, B, C, represent single
    attributes.
  • Convention no set formers in sets of attributes,
    just ABC, rather than A,B,C .

3
Splitting Right Sides of FDs
  • X-gtA1A2An holds for R exactly when each of
    X-gtA1, X-gtA2,, X-gtAn hold for R.
  • Example A-gtBC is equivalent to A-gtB and A-gtC.
  • There is no splitting rule for left sides.
  • Well generally express FDs with singleton right
    sides.

4
Example FDs
  • Drinkers(name, addr, beersLiked, manf, favBeer)
  • Reasonable FDs to assert
  • name -gt addr favBeer
  • Note this FD is the same as name -gt addr and name
    -gt favBeer.
  • beersLiked -gt manf

5
Example Possible Data
name addr beersLiked manf favBeer Janeway
Voyager Bud A.B. WickedAle Janeway Voyager
WickedAle Petes WickedAle Spock Enterprise
Bud A.B. Bud
6
Keys of Relations
  • K is a superkey for relation R if K
    functionally determines all of R.
  • K is a key for R if K is a superkey, but no
    proper subset of K is a superkey.

7
Example Superkey
  • Drinkers(name, addr, beersLiked, manf, favBeer)
  • name, beersLiked is a superkey because
    together these attributes determine all the other
    attributes.
  • name -gt addr favBeer
  • beersLiked -gt manf

8
Example Key
  • name, beersLiked is a key because neither
    name nor beersLiked is a superkey.
  • name doesnt -gt manf beersLiked doesnt -gt addr.
  • There are no other keys, but lots of superkeys.
  • Any superset of name, beersLiked.

9
Where Do Keys Come From?
  • Just assert a key K.
  • The only FDs are K -gt A for all attributes A.
  • Assert FDs and deduce the keys by systematic
    exploration.

10
More FDs From Physics
  • Example no two courses can meet in the same
    room at the same time tells us hour room -gt
    course.

11
Inferring FDs
  • We are given FDs X1 -gt A1, X2 -gt A2,, Xn -gt An
    , and we want to know whether an FD Y -gt B must
    hold in any relation that satisfies the given
    FDs.
  • Example If A -gt B and B -gt C hold, surely A -gt
    C holds, even if we dont say so.
  • Important for design of good relation schemas.

12
Inference Test
  • To test if Y -gt B, start by assuming two tuples
    agree in all attributes of Y.
  • Y
  • 0000000. . . 0
  • 00000?? . . . ?

13
Inference Test (2)
  • Use the given FDs to infer that these tuples
    must also agree in certain other attributes.
  • If B is one of these attributes, then Y -gt B is
    true.
  • Otherwise, the two tuples, with any forced
    equalities, form a two-tuple relation that proves
    Y -gt B does not follow from the given FDs.

14
Closure Test
  • An easier way to test is to compute the closure
    of Y, denoted Y .
  • Basis Y Y.
  • Induction Look for an FDs left side X that is a
    subset of the current Y . If the FD is X -gt A,
    add A to Y .

15
Y
16
Finding All Implied FDs
  • Motivation normalization, the process where we
    break a relation schema into two or more schemas.
  • Example ABCD with FDs AB -gtC, C -gtD,
    and D -gtA.
  • Decompose into ABC, AD. What FDs hold in ABC ?
  • Not only AB -gtC, but also C -gtA !

17
Why?
ABCD
ABC
a1b1c
a2b2c
Thus, tuples in the projection with equal Cs
have equal As C -gt A.
18
Basic Idea
  • Start with given FDs and find all nontrivial
    FDs that follow from the given FDs.
  • Nontrivial right side not contained in the
    left.
  • Restrict to those FDs that involve only
    attributes of the projected schema.

19
Simple, Exponential Algorithm
  • For each set of attributes X, compute X .
  • Add X -gtA for all A in X - X.
  • However, drop XY -gtA whenever we discover X -gtA.
  • Because XY -gtA follows from X -gtA in any
    projection.
  • Finally, use only FDs involving projected
    attributes.

20
A Few Tricks
  • No need to compute the closure of the empty set
    or of the set of all attributes.
  • If we find X all attributes, so is the
    closure of any superset of X.

21
Example Projecting FDs
  • ABC with FDs A -gtB and B -gtC. Project onto
    AC.
  • A ABC yields A -gtB, A -gtC.
  • We do not need to compute AB or AC .
  • B BC yields B -gtC.
  • C C yields nothing.
  • BC BC yields nothing.

22
Example -- Continued
  • Resulting FDs A -gtB, A -gtC, and B -gtC.
  • Projection onto AC A -gtC.
  • Only FD that involves a subset of A,C .

23
A Geometric View of FDs
  • Imagine the set of all instances of a particular
    relation.
  • That is, all finite sets of tuples that have the
    proper number of components.
  • Each instance is a point in this space.

24
Example R(A,B)
(1,2), (3,4)
(5,1)

(1,2), (3,4), (1,3)
25
An FD is a Subset of Instances
  • For each FD X -gt A there is a subset of all
    instances that satisfy the FD.
  • We can represent an FD by a region in the space.
  • Trivial FD an FD that is represented by the
    entire space.
  • Example A -gt A.

26
Example A -gt B for R(A,B)
A -gt B
(1,2), (3,4)
(5,1)

(1,2), (3,4), (1,3)
27
Representing Sets of FDs
  • If each FD is a set of relation instances, then a
    collection of FDs corresponds to the
    intersection of those sets.
  • Intersection all instances that satisfy all of
    the FDs.

28
Example
A-gtB
B-gtC
CD-gtA
29
Implication of FDs
  • If an FD Y -gt B follows from FDs X1 -gt
    A1,,Xn -gt An , then the region in the space of
    instances for Y -gt B must include the
    intersection of the regions for the FDs Xi -gt Ai
    .
  • That is, every instance satisfying all the FDs
    Xi -gt Ai surely satisfies Y -gt B.
  • But an instance could satisfy Y -gt B, yet not be
    in this intersection.

30
Example
B-gtC
A-gtC
A-gtB
31
Relational Schema Design
  • Goal of relational schema design is to avoid
    anomalies and redundancy.
  • Update anomaly one occurrence of a fact is
    changed, but not all occurrences.
  • Deletion anomaly valid fact is lost when a
    tuple is deleted.

32
Example of Bad Design
Drinkers(name, addr, beersLiked, manf,
favBeer) name addr beersLiked manf favBeer Jane
way Voyager Bud A.B. WickedAle Janeway ??? Wicke
dAle Petes ??? Spock Enterprise Bud ??? Bud
Data is redundant, because each of the ???s can
be figured out by using the FDs name -gt addr
favBeer and beersLiked -gt manf.
33
This Bad Design AlsoExhibits Anomalies
name addr beersLiked manf favBeer Janeway Voya
ger Bud A.B. WickedAle Janeway Voyager WickedAle
Petes WickedAle Spock Enterprise Bud A.B. Bud
  • Update anomaly if Janeway is transferred to
    Intrepid,
  • will we remember to change each of her tuples?
  • Deletion anomaly If nobody likes Bud, we lose
    track
  • of the fact that Anheuser-Busch manufactures
    Bud.

34
Boyce-Codd Normal Form
  • We say a relation R is in BCNF if whenever X
    -gtY is a nontrivial FD that holds in R, X is a
    superkey.
  • Remember nontrivial means Y is not contained
    in X.
  • Remember, a superkey is any superset of a key
    (not necessarily a proper superset).

35
Example
  • Drinkers(name, addr, beersLiked, manf, favBeer)
  • FDs name-gtaddr favBeer, beersLiked-gtmanf
  • Only key is name, beersLiked.
  • In each FD, the left side is not a superkey.
  • Any one of these FDs shows Drinkers is not in
    BCNF

36
Another Example
  • Beers(name, manf, manfAddr)
  • FDs name-gtmanf, manf-gtmanfAddr
  • Only key is name .
  • name-gtmanf does not violate BCNF, but
    manf-gtmanfAddr does.

37
Decomposition into BCNF
  • Given relation R with FDs F.
  • Look among the given FDs for a BCNF violation X
    -gtY.
  • If any FD following from F violates BCNF, then
    there will surely be an FD in F itself that
    violates BCNF.
  • Compute X .
  • Not all attributes, or else X is a superkey.

38
Decompose R Using X -gt Y
  • Replace R by relations with schemas
  • R1 X .
  • R2 R (X X ).
  • Project given FDs F onto the two new relations.

39
Decomposition Picture
R1
R-X
X
X -X
R2
R
40
Example BCNF Decomposition
  • Drinkers(name, addr, beersLiked, manf, favBeer)
  • F name-gtaddr, name -gt favBeer, beersLiked-gtman
    f
  • Pick BCNF violation name-gtaddr.
  • Close the left side name name, addr,
    favBeer.
  • Decomposed relations
  • Drinkers1(name, addr, favBeer)
  • Drinkers2(name, beersLiked, manf)

41
Example -- Continued
  • We are not done we need to check Drinkers1 and
    Drinkers2 for BCNF.
  • Projecting FDs is easy here.
  • For Drinkers1(name, addr, favBeer), relevant FDs
    are name-gtaddr and name-gtfavBeer.
  • Thus, name is the only key and Drinkers1 is in
    BCNF.

42
Example -- Continued
  • For Drinkers2(name, beersLiked, manf), the only
    FD is beersLiked-gtmanf, and the only key is
    name, beersLiked.
  • Violation of BCNF.
  • beersLiked beersLiked, manf, so we decompose
    Drinkers2 into
  • Drinkers3(beersLiked, manf)
  • Drinkers4(name, beersLiked)

43
Example -- Concluded
  • The resulting decomposition of Drinkers
  • Drinkers1(name, addr, favBeer)
  • Drinkers3(beersLiked, manf)
  • Drinkers4(name, beersLiked)
  • Notice Drinkers1 tells us about drinkers,
    Drinkers3 tells us about beers, and Drinkers4
    tells us the relationship between drinkers and
    the beers they like.

44
Third Normal Form -- Motivation
  • There is one structure of FDs that causes
    trouble when we decompose.
  • AB -gtC and C -gtB.
  • Example A street address, B city, C
    zip code.
  • There are two keys, A,B and A,C .
  • C -gtB is a BCNF violation, so we must decompose
    into AC, BC.

45
We Cannot Enforce FDs
  • The problem is that if we use AC and BC as our
    database schema, we cannot enforce the FD AB -gtC
    by checking FDs in these decomposed relations.
  • Example with A street, B city, and C zip on
    the next slide.

46
An Unenforceable FD
street zip 545 Tech Sq. 02138 545 Tech
Sq. 02139
city zip Cambridge 02138 Cambridge 02139
Although no FDs were violated in the decomposed
relations, FD street city -gt zip is violated by
the database as a whole.
47
3NF Lets Us Avoid This Problem
  • 3rd Normal Form (3NF) modifies the BCNF condition
    so we do not have to decompose in this problem
    situation.
  • An attribute is prime if it is a member of any
    key.
  • X -gtA violates 3NF if and only if X is not a
    superkey, and also A is not prime.

48
Example 3NF
  • In our problem situation with FDs AB -gtC
    and C -gtB, we have keys AB and AC.
  • Thus A, B, and C are each prime.
  • Although C -gtB violates BCNF, it does not
    violate 3NF.

49
What 3NF and BCNF Give You
  • There are two important properties of a
    decomposition
  • Lossless Join it should be possible to project
    the original relations onto the decomposed
    schema, and then reconstruct the original.
  • Dependency Preservation it should be possible
    to check in the projected relations whether all
    the given FDs are satisfied.

50
3NF and BCNF -- Continued
  • We can get (1) with a BCNF decomposition.
  • We can get both (1) and (2) with a 3NF
    decomposition.
  • But we cant always get (1) and (2) with a BCNF
    decomposition.
  • street-city-zip is an example.

51
Testing for a Lossless Join
  • If we project R onto R1, R2,, Rk , can we
    recover R by rejoining?
  • Any tuple in R can be recovered from its
    projected fragments.
  • So the only question is when we rejoin, do we
    ever get back something we didnt have originally?

52
The Chase Test
  • Suppose tuple t comes back in the join.
  • Then t is the join of projections of some tuples
    of R, one for each Ri of the decomposition.
  • Can we use the given FDs to show that one of
    these tuples must be t ?

53
The Chase (2)
  • Start by assuming t abc .
  • For each i, there is a tuple si of R that has a,
    b, c, in the attributes of Ri.
  • si can have any values in other attributes.
  • Well use the same letter as in t, but with a
    subscript, for these components.

54
Example The Chase
  • Let R ABCD, and the decomposition be AB, BC,
    and CD.
  • Let the given FDs be C-gtD and B -gtA.
  • Suppose the tuple t abcd is the join of tuples
    projected onto AB, BC, CD.

55
The Tableau
  • A B C D
  • a b c1 d1
  • a2 b c d2
  • a3 b3 c d

56
Summary of the Chase
  1. If two rows agree in the left side of a FD, make
    their right sides agree too.
  2. Always replace a subscripted symbol by the
    corresponding unsubscripted one, if possible.
  3. If we ever get an unsubscripted row, we know any
    tuple in the project-join is in the original (the
    join is lossless).
  4. Otherwise, the final tableau is a counterexample.

57
Example Lossy Join
  • Same relation R ABCD and same decomposition.
  • But with only the FD C-gtD.

58
The Tableau
  • A B C D
  • a b c1 d1
  • a2 b c d2
  • a3 b3 c d

These three tuples are an example R that shows
the join lossy. abcd is not in R, but we can
project and rejoin to get abcd.
59
3NF Synthesis Algorithm
  • We can always construct a decomposition into 3NF
    relations with a lossless join and dependency
    preservation.
  • Need minimal basis for the FDs
  • Right sides are single attributes.
  • No FD can be removed.
  • No attribute can be removed from a left side.

60
Constructing a Minimal Basis
  1. Split right sides.
  2. Repeatedly try to remove an FD and see if the
    remaining FDs are equivalent to the original.
  3. Repeatedly try to remove an attribute from a left
    side and see if the resulting FDs are equivalent
    to the original.

61
3NF Synthesis (2)
  • One relation for each FD in the minimal basis.
  • Schema is the union of the left and right sides.
  • If no key is contained in an FD, then add one
    relation whose schema is some key.

62
Example 3NF Synthesis
  • Relation R ABCD.
  • FDs A-gtB and A-gtC.
  • Decomposition AB and AC from the FDs, plus AD
    for a key.

63
Why It Works
  • Preserves dependencies each FD from a minimal
    basis is contained in a relation, thus preserved.
  • Lossless Join use the chase to show that the row
    for the relation that contains a key can be made
    all-unsubscripted variables.
  • 3NF hard part a property of minimal bases.
Write a Comment
User Comments (0)
About PowerShow.com