1 / 59

Dept. of CIS, Temple Univ. CIS616/661 – Principles of Data Management

Dept. of CIS, Temple Univ. CIS616/661 – Principles of Data Management. V. Megalooikonomou Integrity Constraints (based on slides by C. Faloutsos at CMU). Constraints:. Integrity constraints in the E-R Model: Key cardinalities of a Relationship. Overview. Domain; Ref. Integrity constraints

forbes
Download Presentation

Dept. of CIS, Temple Univ. CIS616/661 – Principles of Data Management

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. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  1. Dept. of CIS, Temple Univ.CIS616/661 – Principles of Data Management V. Megalooikonomou Integrity Constraints (based on slides by C. Faloutsos at CMU)

  2. Constraints: Integrity constraints in the E-R Model: • Key • cardinalities of a Relationship

  3. Overview • Domain; Ref. Integrity constraints • Assertions and Triggers • Functional dependencies

  4. Domain Constraints • Domain Types, eg, SQL • Fixed Length characters • Int; float; (date) • null values eg., • create table student( ssn char(9) not null, ...)

  5. Referential Integrity constraints ‘foreign keys’ - eg: create table takes( ssn char(9) notnull, c-id char(5) notnull, grade integer, primarykey(ssn, c-id), foreignkey ssn references student, foreignkey c-id references class)

  6. Referential Integrity constraints … foreignkey ssn references student, foreignkey c-id references class) Effect: • expects that ssn exists in ‘student’ table • blocks ops that violate that - how?? • insertion? • deletion/update?

  7. Referential Integrity constraints … foreignkey ssn references student on delete cascade on update cascade, ... • -> eliminate all student enrollments • other options (set to null, to default etc)

  8. Weapons for IC: • assertions • create assertion <assertion-name> check <predicate> • triggers (~ assertions with ‘teeth’) • on operation, if condition, then action

  9. Triggers - example definetrigger zerograde onupdate takes (if new takes.grade < 0 then takes.grade = 0)

  10. Triggers - discussion more complicated: “managers have higher salaries than their subordinates” - a trigger can automatically boost mgrs salaries triggers: tricky (infinite loops…)

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

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

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

  14. Functional dependencies ‘Bad’ - why?

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

  16. Functional dependencies ‘name’ depends on the ‘ssn’ define ‘depends’

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

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

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

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

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

  22. 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

  23. Functional dependencies Closure of a set of FD: all implied FDs - eg.: ssn -> name, address ssn, c-id -> grade imply ssn, c-id -> grade, name, address ssn, c-id -> ssn

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

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

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

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

  28. 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.

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

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

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

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

  33. FDs - Armstrong’s axioms Prove Decomposition

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

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

  36. 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

  37. 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

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

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

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

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

  42. 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

  43. 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?

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

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

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

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

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

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

  50. ‘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

More Related