lecture 7 n.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
Lecture 7 PowerPoint Presentation
Download Presentation
Lecture 7

Loading in 2 Seconds...

play fullscreen
1 / 30

Lecture 7 - PowerPoint PPT Presentation


  • 152 Views
  • Uploaded on

CS4416 Database Systems CS5122 Development of IS 2. Lecture 7. Design Theory for Relational Databases (part 1). Slides based on http://infolab.stanford.edu/~ullman/fcdb/aut07/index.html#lecture. Relational Schema Design.

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'Lecture 7' - beyla


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
lecture 7

CS4416 Database SystemsCS5122 Development of IS 2

Lecture 7

Design Theory for Relational Databases

(part 1)

Slides based on http://infolab.stanford.edu/~ullman/fcdb/aut07/index.html#lecture

relational schema design
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.
functional dependencies
Functional Dependencies
  • XY 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Y holds in R.” or “X functionally determines Y 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 }.
splitting right sides of fd s
Splitting Right Sides of FD’s
  • XA1A2…An holds for R exactly when each of X A1, X A2,…, X An hold for R.
  • Example: A BC is equivalent to AB and AC.
  • There is no splitting rule for left sides.
  • We’ll generally express FD’s with singleton right sides.
example fd s

Because name  favBeer

Because name  addr

Because beersLiked  manf

Example: FD's

Drinkers(name, addr, beersLiked, manf, favBeer)

name addr beersLiked manf favBeer

Janeway Voyager Bud A.B. WickedAle

Janeway Voyager WickedAle Pete’s WickedAle

Spock Enterprise Bud A.B. Bud

example fd s contd
Example: FD’s (contd.)

Drinkers(name, addr, beersLiked, manf, favBeer)

  • Reasonable FD’s to assert:
    • name  addr favBeer
      • This is the same as name  addr and name  favBeer.
    • beersLiked  manf
example of bad design
Example of Bad Design

Drinkers(name, addr, beersLiked, manf, favBeer)

name addr beersLiked manf favBeer

Janeway Voyager Bud A.B. WickedAle

Janeway ??? WickedAle Pete’s ???

Spock Enterprise Bud ??? Bud

Data is redundant, because each of the ???’s can be figured

out by using the FD’s name  addr favBeer and

beersLiked  manf.

this bad design also exhibits anomalies
This Bad Design AlsoExhibits Anomalies

name addr beersLiked manf favBeer

Janeway Voyager Bud A.B. WickedAle

Janeway Voyager WickedAle Pete’s 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.
keys of relations
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.
example superkey
Example: Superkey

Drinkers(name, addr, beersLiked, manf, favBeer)

  • {name, beersLiked} is a superkey because together these attributes determine all the other attributes.
    • name  addr favBeer
    • beersLiked  manf
example key
Example: Key
  • {name, beersLiked} is a key because neither {name} nor {beersLiked} is a superkey.
    • name doesn’t  manf;
    • beersLiked doesn’t  addr.
  • There are no other keys, but lots of superkeys.
    • Any superset of {name, beersLiked}.
where do keys come from
Where Do Keys Come From?
  • Just assert a key K (usually some sort of id).
    • The only FD’s are KA for all attributes A.
  • Assert FD’s and deduce the keys by systematic exploration.
    • Important for normalization (see next lecture).
inferring fd s1
Inferring FD’s
  • We are given FD’s X1A1, X2A2,…, XnAn, and we want to know whether an FD YB must hold in any relation that satisfies the given FD’s.
    • Example: If AB and BC hold, surely AC holds, even if we don’t say so.
closure test
Closure Test
  • An easier way to test is to compute the closure of Y, denoted Y +, and check if B is an element of in Y +.
  • Step 1: Start with Y+ = Y.
  • Step 2: Look for an FD’s XA whose left side X is in the current Y+.
    • If there is no such FD stop.
    • If there is such an FD, A to Y+ and repeat Step 2.
slide18

X

A

new Y+

Y+

finding all implied fd s simple exponential algorithm
Finding All Implied FD’s Simple, Exponential Algorithm
  • For each set of attributes X, compute X+.
  • Add XA for all A in X +̶X.
  • However, drop XYA whenever we discover XA.
    • Because XYA follows from XAin any projection.
a few tricks
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.
example
Example
  • ABC with FD’s AB and BC. Find all implied FDs.
    • A +=ABC ; yields AB, AC.
      • We do not need to compute AB + or AC +.
    • B +=BC ; yields BC.
    • C +=C ; yields nothing.
    • BC +=BC ; yields nothing.
  • Resulting FDs: AB, AC, and BC.
visual representation of fd s1
Visual Representation of FD's
  • 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.
example r a b c
Example: R(A,B,C)

{(1,2,3), (3,4,5)}

{}

{(5,1,1)}

{(1,2,3), (1,2,4)}

{(1,2,3), (3,4,5), (1,3,5)}

an fd is a subset of instances
An FD is a Subset of Instances
  • For each FD X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A.
example a b for r a b
Example: A -> B for R(A,B)

A  B

{(1,2,3), (3,4,5)}

{(5,1,1)}

{}

{(1,2,3), (1,2,4)}

{(1,2,3), (3,4,5), (1,3,5)}

representing sets of fd s
Representing Sets of FD’s
  • If each FD is a set of relation instances, then a collection of FD’s corresponds to the intersection of those sets.
    • Intersection = all instances that satisfy all of the FD’s.
example1

Instances satisfying

AB, BC, and

CDA

Example

AB

BC

CDA

implication of fd s
Implication of FD’s
  • If an FD YB follows from FD’s X1A1,…,XnAn, then the region in the space of instances for YB must include the intersection of the regions for the FD’s XiAi.
    • That is, every instance satisfying all the FD’s XiAisurely satisfies YB.
    • But an instance could satisfy YB, yet not be in this intersection.
example2
Example

B->C

A->C

A->B