Temple University CIS Dept CIS 331 Principles of

  • Slides: 51
Download presentation
Temple University – CIS Dept. CIS 331– Principles of Database Systems V. Megalooikonomou Functional

Temple University – CIS Dept. CIS 331– Principles of Database Systems V. Megalooikonomou Functional Dependencies (based on notes by Silberchatz, Korth, and Sudarshan and notes by C. Faloutsos at CMU)

General Overview n Formal query languages n n Commercial query languages n n n

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

Overview n n Domain; Ref. Integrity constraints Assertions and Triggers Security Functional dependencies n

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

Functional dependencies n motivation: ‘good’ tables takes 1 (ssn, c-id, grade, name, address) ‘good’

Functional dependencies n motivation: ‘good’ tables takes 1 (ssn, c-id, grade, name, address) ‘good’ or ‘bad’?

Functional dependencies takes 1 (ssn, c-id, grade, name, address)

Functional dependencies takes 1 (ssn, c-id, grade, name, address)

Functional dependencies ‘Bad’ - why?

Functional dependencies ‘Bad’ - why?

Functional Dependencies n Redundancy n n space inconsistencies insertion/deletion anomalies (later…) What caused the

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

Functional dependencies n n … ‘name’ depends on ‘ssn’ define ‘depends’

Functional dependencies n n … ‘name’ depends on ‘ssn’ define ‘depends’

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

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

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

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

Functional dependencies formally: if two tuples agree on the ‘X’ attribute, they *must* agree

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

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

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

Functional dependencies n n ssn -> name, address ssn, c-id -> grade

Functional dependencies n n ssn -> name, address ssn, c-id -> grade

Functional dependencies K is a superkey for relation R iff K -> R K

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

Functional dependencies Closure of a set of FD: all implied FDs – e. g.

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

FDs - Armstrong’s axioms Closure of a set of FD: all implied FDs –

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?

FDs - Armstrong’s axioms “Armstrong’s axioms” guarantee soundness and completeness: n Reflexivity: e. g.

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

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

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

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

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

FDs – finding the closure F+ F+ = F repeat for each functional dependency

FDs – finding the closure F+ F+ = F repeat for each functional dependency f in F+ apply reflexivity and augmentation rules on f add the resulting functional dependencies to F+ for each pair of functional dependencies f 1 and f 2 in F+ if f 1 and f 2 can be combined using transitivity then add the resulting functional dependency to F+ until F+ does not change any further n We can further simplify manual computation of F+ by using the following additional rules

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

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

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

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

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

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

FDs - Armstrong’s axioms Prove Pseudo-transitivity:

FDs - Armstrong’s axioms Prove Pseudo-transitivity:

FDs - Armstrong’s axioms Prove Decomposition

FDs - Armstrong’s axioms Prove Decomposition

FDs - Closure F+ Given a set F of FD (on a schema) F+

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 F ssn-> name, address }

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

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

FDs - Closure F+ R=(A, B, C, G, H, I) F= { A->B A->C

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

FDs - Closure A+ Given a set F of FD (on a schema) A+

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 F ssn-> name, address } {ssn}+ =? ?

FDs - Closure A+ takes(ssn, c-id, grade, name, address) ssn, c-id -> grade F

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

FDs - Closure A+ takes(ssn, c-id, grade, name, address) ssn, c-id -> grade F

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

FDs - Closure A+ takes(ssn, c-id, grade, name, address) ssn, c-id -> grade F

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

FDs - Closure A+ if A+ = {all attributes of table} then ‘A’ is

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

FDs - Closure A+ n Algorithm to compute a+, the closure of a under

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

FDs - Closure A+ (example) n n n R = (A, B, C, G,

FDs - Closure A+ (example) n n n 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) n Is AG a candidate key? 1. 2. Is AG a super key? 1. Does AG R? Is any subset of AG a superkey? + R? 1. Does A + R? 2. Does G

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

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

FDs - ‘canonical cover’ Fc Given a set F of FD (on a schema)

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 F ssn, name-> name, address ssn, c-id-> grade, name

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

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

FDs - ‘canonical cover’ Fc n n n why do we need it? define

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

FDs - ‘canonical cover’ Fc n why do we need it? n n n

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

FDs - ‘canonical cover’ Fc n define it properly - three properties n n

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

FDs - ‘canonical cover’ Fc ‘extraneous’ attribute: n n if the closure is the

FDs - ‘canonical cover’ Fc ‘extraneous’ attribute: n n 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 ssn, c-id -> grade ssn-> name, address ssn, name->

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

FDs - ‘canonical cover’ Fc n n n Algorithm: examine each FD; drop extraneous

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

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

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

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

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

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

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)

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

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)

FDs - ‘canonical cover’ Fc B->C (1’) A->B (2’) B->C (3) A->B B->C (1’)

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

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

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

Overview - conclusions n n n Domain; Ref. Integrity constraints Assertions and Triggers Functional

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