Schema Refinement-Databases-Lecture 06 Slides-Computer Science, Slides of Database Management Systems (DBMS)

Download Schema Refinement-Databases-Lecture 06 Slides-Computer Science and more Slides Database Management Systems (DBMS) in PDF only on Docsity!

Lecture 6: Schema refinement: Functional dependencies

www.cl.cam.ac.uk/Teaching/current/Databases/

Recall: Database design

lifecycle

• Requirements analysis
  • User needs; what must database do?
• Conceptual design
  • High-level description; often using E/R model
• Logical design
  • Translate E/R model into relational schema
• Schema refinement
  • Check schema for redundancies and anomalies
• Physical design/tuning
  • Consider typical workloads, and further optimise

Next two lectures

Not all designs are

equally good

• Why is this design bad? Data(sid,sname,address,cid,cname,grade)
• Why is this one preferable?

Student(sid,sname,address) Course(cid,cname) Enrolled(sid,cid,grade)

An instance of our bad

design

sid sname addres s

ci d

cname grade

124 Britney USA 206 Database A++ 204 Victoria Essex 202 Semantics C 124 Britney USA 201 S/Eng I A+ 206 Emma London 206 Database B- 124 Britney USA 202 Semantics B+

Decomposition

• We remove anomalies by replacing the schema Data(sid,sname,address,cid,cname,grade) with Student(sid,sname,address) Course(cid,cname) Enrolled(sid,cid,grade)
• Note the implicit extra cost here
• Two immediate questions:
  1. Do we need to decompose a relation?
  2. What problems might result from a decomposition?

Functional dependencies

• Recall:
  • A key is a set of fields where if a pair of tuples agree on a key, they agree everywhere
• In our bad design, if two tuples agree on sid, then they also agree on address, even though the rest of the tuples may not agree

10

Functional dependencies

cont.

• We’d expect the following functional dependencies to hold in our Student database - sid sname,address - cid cname - sid,cid grade
• A functional dependency X Y is simply a pair of sets (of field names) - Note: the sloppy notation A,B C,D rather than {A,B} {C,D}

Formalities

• Given a relation R=R(A 1 : 1 , …, An: (^) n), and X, Y ( {A 1 , …, An}), an instance r of R satisfies X Y , if - For any two tuples t 1 , t 2 in R, if t 1 .X=t 2 .X then t 1 .Y=t 2 .Y
• Note: This is a semantic assertion. We can not look at an instance to determine which FDs hold (although we can tell if the instance does not satisfy an FD!)

Closure of a set of FDs

• Which of the following are in the closure of our Student FDs? - address address - cid cname - cid cname,sname - cid,sid cname,sname

Candidate keys and FDs

• If R=R(A 1 : 1 , …, An: (^) n) with FDs F and X {A 1 , …, An}, then X is a candidate key for R if - X A 1 , …,An F+ - For no proper subset Y X is Y A 1 , …,An F+

Consequences of

Armstrong’s axioms

• Union : If F \ X Y and F \ X Z then F \ X Y,Z
• Pseudo-transitivity : If F \ X Y and F \ W,Y Z then F \ X,W Z
• Decomposition : If F \ X Y and Z Y then F \ X Z

Exercise: Prove that these are consequences of Armstrong’s axioms

Proof of Union Rule

Suppose that F \ X Y and F \ X Z.

By augmentation we have

F \ X X,Y

since X U X = X. Also by augmentation

F \ X,Y Z,Y

Therefore, by transitivity we have

F \ X Z,Y QED

Proof of Heath’s Rule

A , C ( R )

A

First show that

Suppose then and

Since

we have A , C ( R )

Proof of Heath’s Rule (cont.)

A

In the other direction, we must show that

Suppose Then there must exist records

and There must also exist

Therefore, we have

so that

QED

But the functional dependency tells us that