Temple University – CIS Dept. CIS616– Principles of Data Management

1. Temple University – CIS Dept.CIS616– Principles of Data Management V. Megalooikonomou Functional Dependencies (based on notes by Silberchatz,Korth, and Sudarshan and notes by C. Faloutsos at CMU)

2. General Overview • Formal query languages • rel algebra and calculi • Commercial query languages • SQL • QBE, (QUEL) • Integrity constraints • Functional Dependencies • Normalization - ‘good’ DB design

3. Overview • Domain; Ref. Integrity constraints • Assertions and Triggers • Security • Functional dependencies • why • definition • Armstrong’s “axioms” • closure and cover

4. Functional dependencies motivation: ‘good’ tables takes1 (ssn, c-id, grade, name, address) ‘good’ or ‘bad’?

5. Functional dependencies takes1 (ssn, c-id, grade, name, address)

6. Functional dependencies ‘Bad’ - why?

7. Functional Dependencies • Redundancy • space • inconsistencies • insertion/deletion anomalies (later…) • What caused the problem?

8. Functional dependencies … ‘name’ depends on ‘ssn’ define ‘depends’

9. Functional dependencies Definition: ‘a’ functionally determines ‘b’

10. Functional dependencies Informally: ‘if you know ‘a’, there is only one ‘b’ to match’

11. Functional dependencies formally: if two tuples agree on the ‘X’ attribute, they *must* agree on the ‘Y’ attribute, too (e.g., if ssn is the same, so should address) … a functional dependency is a generalization of the notion of a key

12. Functional dependencies ‘X’, ‘Y’ can be sets of attributes other examples??

13. Functional dependencies ssn -> name, address ssn, c-id -> grade

14. Functional dependencies K is a superkey for relation R iff K -> R K is a candidate key for relation R iff: K -> R for no a  K, a -> R

15. Functional dependencies Closure of a set of FD: all implied FDs – e.g.: ssn -> name, address ssn, c-id -> grade imply ssn, c-id -> grade, name, address ssn, c-id -> ssn

16. FDs - Armstrong’s axioms Closure of a set of FD: all implied FDs – e.g.: ssn -> name, address ssn, c-id -> grade how to find all the implied ones, systematically?

17. FDs - Armstrong’s axioms “Armstrong’s axioms” guarantee soundness and completeness: Reflexivity: e.g., ssn, name -> ssn Augmentation e.g., ssn->name then ssn,grade-> ssn,grade

18. FDs - Armstrong’s axioms Transitivity ssn->address address-> county-tax-rate THEN: ssn-> county-tax-rate

19. FDs - Armstrong’s axioms Reflexivity: Augmentation: Transitivity: ‘sound’ and ‘complete’

20. FDs – finding the closure F+ F+ = F repeatfor 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 We can further simplify manual computation of F+ by using the following additional rules 

21. FDs - Armstrong’s axioms Additional rules: Union Decomposition Pseudo-transitivity

22. FDs - Armstrong’s axioms Prove ‘Union’ from the three axioms:

23. FDs - Armstrong’s axioms Prove ‘Union’ from the three axioms:

24. FDs - Armstrong’s axioms Prove Pseudo-transitivity:

25. FDs - Armstrong’s axioms Prove Decomposition

26. FDs - Closure F+ Given a set F of FD (on a schema) F+ is the set of all implied FD. E.g., takes(ssn, c-id, grade, name, address) ssn, c-id -> grade ssn-> name, address }F

27. FDs - Closure F+ ssn, c-id -> grade ssn-> name, address ssn-> ssn ssn, c-id-> address c-id, address-> c-id ... F+

28. FDs - Closure F+ R=(A,B,C,G,H,I) F= { A->B A->C CG->H CG->I B->H} Some members of F+: A->H AG->I CG->HI

29. FDs - Closure A+ Given a set F of FD (on a schema) A+ is the set of all attributes determined by A: takes(ssn, c-id, grade, name, address) ssn, c-id -> grade ssn-> name, address {ssn}+ =?? }F

30. FDs - Closure A+ takes(ssn, c-id, grade, name, address) ssn, c-id -> grade ssn-> name, address {ssn}+ ={ssn, name, address } }F

31. FDs - Closure A+ takes(ssn, c-id, grade, name, address) ssn, c-id -> grade ssn-> name, address {c-id}+ = ?? }F

32. FDs - Closure A+ takes(ssn, c-id, grade, name, address) ssn, c-id -> grade ssn-> name, address {c-id, ssn}+ = ?? }F

33. FDs - Closure A+ if A+ = {all attributes of table} then ‘A’ is a candidate key

34. FDs - Closure A+ 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

35. FDs - Closure A+ (example) R = (A, B, C, G, H, I) F = {A B, A C, CG H, CG I, B 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 any subset of AG a superkey? Does A+R? Does G+R?

36. FDs - A+ closure C A B Diagrams AB->C (1) A->BC (2) B->C (3) A->B (4)

37. FDs - ‘canonical cover’ Fc Given a set F of FD (on a schema) Fc is a minimal set of equivalent FD. E.g., takes(ssn, c-id, grade, name, address) ssn, c-id -> grade ssn-> name, address ssn,name-> name, address ssn, c-id-> grade, name F

38. FDs - ‘canonical cover’ Fc ssn, c-id -> grade ssn-> name, address ssn,name-> name, address ssn, c-id-> grade, name Fc F

39. FDs - ‘canonical cover’ Fc why do we need it? define it properly compute it efficiently

40. FDs - ‘canonical cover’ Fc why do we need it? easier to compute candidate keys define it properly compute it efficiently

41. FDs - ‘canonical cover’ Fc define it properly - three properties every FD a->b has no extraneous attributes on the RHS same for the LHS all LHS parts are unique

42. ‘extraneous’ attribute: if the closure is the same, before and after its elimination or if F-before implies F-after and vice-versa FDs - ‘canonical cover’ Fc

43. FDs - ‘canonical cover’ Fc ssn, c-id -> grade ssn-> name, address ssn,name-> name, address ssn, c-id-> grade, name F

44. FDs - ‘canonical cover’ Fc Algorithm: examine each FD; drop extraneous LHS or RHS attributes merge FDs with same LHS repeat until no change

45. FDs - ‘canonical cover’ Fc Trace algo for AB->C (1) A->BC (2) B->C (3) A->B (4)

46. FDs - ‘canonical cover’ Fc Trace algo for AB->C (1) A->BC (2) B->C (3) A->B (4) (4) and (2) merge: AB->C (1) A->BC (2) B->C (3)

47. FDs - ‘canonical cover’ Fc AB->C (1) A->BC (2) B->C (3) in (2): ‘C’ is extr. AB->C (1) A->B (2’) B->C (3)

48. FDs - ‘canonical cover’ Fc AB->C (1) A->B (2’) B->C (3) in (1): ‘A’ is extr. B->C (1’) A->B (2’) B->C (3)

49. FDs - ‘canonical cover’ Fc B->C (1’) A->B (2’) B->C (3) (1’) and (3) merge A->B (2’) B->C (3) nothing is extraneous: ‘canonical cover’

50. FDs - ‘canonical cover’ Fc BEFORE AB->C (1) A->BC (2) B->C (3) A->B (4) AFTER A->B (2’) B->C (3)