Topics to be discusses

1. Topics to be discusses • Functional Dependency • Key • Closure of Function Dependency

2. Functional Dependency • A functional dependency (FD) is a constraint between two sets of attributes in a relation from a database. • Require that the value for a certain set of attributes determines uniquely the value for another set of attributes. • A functional dependency is a generalization of the notion of a key. • Notation: • α→β (α determines β) • (α→β may take the form AB→C, A→BC, etc.)

3. Let R be a relation schema   R and   R • The functional dependency  holds on R if and only if for any legal relations r(R), whenever any two tuples t1and t2 of r agree on the attributes , they also agree on the attributes . That is, t1[] = t2 []  t1[ ] = t2 [ ] • Example: Consider r(A,B ) with the following instance of r. • On this instance, AB does NOT hold, but BA does hold. • 4 • 1 5 • 3 7

4. A->B C->B AC->B

5. A->C • AB->C • AB->D • AD->B • AD->C

6. Example Drinkers(name, addr, beersLiked, manf, favBeer) • Reasonable FD’s to assert: • name -> addr • name -> favBeer • beersLiked -> manf

7. Because name -> favBeer Because name -> addr Because beersLiked -> manf Example Data name addr beersLiked manf favBeer Janeway Voyager Bud A.B. WickedAle Janeway Voyager WickedAle Pete’s WickedAle Spock Enterprise Bud A.B. Bud

8. FD’s With Multiple Attributes • No need for FD’s with > 1 attribute on right. • But sometimes convenient to combine FD’s as a shorthand. • Example: name -> addr and name -> favBeer become name -> addr favBeer • > 1 attribute on left may be essential. • Example: bar beer -> price

9. Keys of Relations • K is a superkey for relation R if K functionally determines all of R. • K is a Candidatekey for R if K is a superkey, but no proper subset of K is a superkey.

10. Example 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

11. Example, Cont. • {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}.

12. A->B C->B AC->B CANDIDATE KEY AC->B PRIMARY KEY CAN BE ANY ONE

13. Find the super key ,candidate key and primary key • R(A,B,C,D) • A->C • AB->C • AB->D • AD->B • AD->C • Super key AB,AD,ABC,ABD,ABCD, • ABD,ACB,ABCD • Candidate key AB,AD • Primary key can be AB or AD

14. Example Data name addr beersLiked manf favBeer Janeway Voyager Bud A.B. WickedAle Janeway Voyager WickedAle Pete’s WickedAle Spock Enterprise Bud A.B. Bud Relational key = {name beersLiked} But in E/R, name is a key for Drinkers, and beersLiked is a key for Beers. Note: 2 tuples for Janeway entity and 2 tuples for Bud entity.

15. CLOUSRE OF A SET OF FUNCTIONAL DEPENDENCY • Armstrong’s Axioms: • Reflexivity rule: • if α is a set of attributes and β is contained in α then α→β • i.e. given AB→C, then A→B • Augmentation rule: • given α→β and another set of attributes γ, then γα→γβ • Transitivity rule: • if α→β and β→γ , then α→γ

16. Other Rules: • Union rule: • if α→β and α→γ , then α→βγ • Decomposition rule: • if α→βγ , then α→β and α→γ • Pseudotransitivity rule: • if α→β and γβ→δ , then γα→δ

17. Example • R=(A,B,C,G,H,I) • A->B • A->C • CG->H • CG->I • B->H Examp

18. Closure (F+) • A->H Since A->B and B->H holds, we apply the transitivity rule. • CG->HI. since cg->h and CG->I the union rule • AG->I. since A->C and CG->I pseudo transitivity rule

19. To compute the closure of a set of functional dependencies F: F + = Frepeatfor each functional dependency f in F+ apply reflexivity and augmentation rules on fadd the resulting functional dependencies to F +for each pair of functional dependencies f1and f2 in F +iff1 and f2 can be combined using transitivitythen add the resulting functional dependency to F +until F + does not change any further

20. We can further simplify manual computation of F+ by using the following additional rules. • If   holds and  holds, then    holds (union) • If    holds, then   holds and  holds (decomposition) • If   holds and   holds, then   holds (pseudotransitivity)

21. Closure of Attribute Sets • To test a set a is super key or not • Given a set of attributes a, define the closure of a under F (denoted by a+) as the set of attributes that are functionally determined by a under F • Algorithm to compute a+, the closure of a under F result := a;while (changes to result) do for each in F do begin if  result then result := result end

22. Example of Attribute Set Closure • R = (A, B, C, G, H, I) • F = {A BA C CG HCG IB H} • (AG)+ 1. result = AG 2. result = ABCG (A C and A  B) 3. result = ABCGH (CG H and CG  AGBC) 4. result = ABCGHI (CG I and CG  AGBCH) • Is AG a candidate key? • Is AG a super key? • Does AG R? == Is (AG)+  R • Is any subset of AG a superkey? • Does AR? == Is (A)+  R • Does GR? == Is (G)+  R

23. Uses of Attribute Closure There are several uses of the attribute closure algorithm: • Testing for superkey: • To test if  is a superkey, we compute +, and check if + contains all attributes of R. • Testing functional dependencies • To check if a functional dependency    holds (or, in other words, is in F+), just check if   +. • That is, we compute + by using attribute closure, and then check if it contains . • Is a simple and cheap test, and very useful • Computing closure of F • For each   R, we find the closure +, and for each S  +, we output a functional dependency   S.

24. Excercises

25. Excercise

26. Solution • StudentID → Semester. • {StudentID, Lecture} → TA • {StudentID, Lecture} → {TA, Semester}

27. Find the super key of given function dependency R(A,B,C,D) • A->D • D->B • B->C • E->B AE is super key

28. Find the super key of given functional dependency R(A,B,C,D) AB->C C->B C->D A+ =A AB+ = ABCD AC+=ABCD