1 / 28

Functional Dependencies

Functional Dependencies. Review. Relational data model (schema & instances) Physical and conceptual data independence ACID properties of transactions Integrity constraint Key constraint Foreign key constraint Inclusion dependency

sasson
Download Presentation

Functional Dependencies

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. Functional Dependencies

  2. Review • Relational data model (schema & instances) • Physical and conceptual data independence • ACID properties of transactions • Integrity constraint • Key constraint • Foreign key constraint • Inclusion dependency • SQL (DDL) – create tables, views, domains, intra and inter-relational constraints, assertions, etc.

  3. Review: ER Modeling • ER Model – domains, attributes, entities, roles, relationships • Translating from ER to relational model • Key and participating constraints • Type hierarchies (disjoint & covering)

  4. Review: Relational Algebra & SQL • Relational algebra: select, project, set ops, cross product, joins (diff types), division • SQL (query sublanguage): algebra to SQL • SELECT—FROM—WHERE—GROUP BY—HAVING—ORDER BY • Query evaluation strategy • Correlated, nested queries, division • Views in SQL • SQL (DML) – insert, delete, update

  5. Review: TRC, DRC, FDs • Tuple relational calculus • Domain relational calculus • Bound and free variables • Relational algebra, SQL, TRC and DRC • Types of anomalies: update, deletion, insertion • Functional dependencies • Armstrong’s axioms • Entailment: attribute closure

  6. Functional Dependencies • A functional dependency is a constraint between two sets of attributes in a relational database. • If X and Y are two sets of attributes in the same relation T, then X  Y means that X functionally determines Y so that • the values of the attributes in X uniquely determine the values of the attributes in Y • for any two tuples t1 and t2 in T, t1[X] = t2[X] implies that t1[Y] = t2[Y] • if two tuples in T agree in their X column(s), then their Y column(s) should also be the same.

  7. Functional Dependencies • Dependencies for this relation: • A  B • A  D • B,C  E,F • Do they all hold in this instance of the relation R? • Functional dependencies are specified by the database programmer based on the intended meaning of the attributes.

  8. Functional Dependencies • What are the functional dependencies in: COMPANIES(company_name, company_address, date_founded, owner_name, owner_title, #shares ) • company_name  company_address • company_name  date_founded • company_name, owner_id  owner_title • company_name, owner_id  #shares • company_name, owner_title  owner_id • owner_id  owner_name

  9. FD and Keys • Key constraint is a special kind of functional dependency • Key is on LHS, all attributes are on RHS • SSN  SSN, Name, Address • For a key, no two rows share the same values, thus by default, when ever a tuple agrees on LHS it agrees on the RHS.

  10. Armstrong’s Axioms of FDs • Reflexivity: If Y  X then X  Y (trivial FD) • Name, Address Name • Augmentation: If X  Y then X Z YZ • If Town  Zip then Town, Name  Zip, Name • Transitivity: If X  Y and Y  Z then X  Z

  11. Other derived rules • Union: If X  Y and X  Z, then X  YZ • X  YX (augment), YX YZ (augment) • thusX YZ (transitive) • Decomposition:If X  YZ, then X  Y and X  Z • YZ  Y (reflexive), thus X  Y (transitive) • Pseudotransitivity:If X  Y and WY  Z, then XW  Z • Accumulation rule: If X  YZ and Z  W, then X  YZW

  12. Derivation of Accumulation Ruleusing the Axioms • Accumulation rule: • If X  YZ and Z  W, then X  YZW • Proof: from Z  W, augment with YZ to get • YZZ  YZW or YZ  YZW • By transitivity, we get X  YZW

  13. Mathematical Properties of FDs • Definition: If F is a set of FDs on schema R and f is another FD on R, then F entails f if every instance r of R that satisfies F also satisfies f • Ex: F = {A  B, B C} and f is A  C • If Streetaddr Town and Town  Zip then Streetaddr  Zip • Definition: The closure of F, denoted F+, is the set of all FDs entailed by F • Definition: F and G are equivalent if F entails G and G entails F

  14. Soundness • Axioms are sound: If an expression f: X Y can be derived from a set of FDs F using the axioms, then f is a FD. We say F entailsf. • Example: Given X Y and X Z then • Union rule: we can take the union of FDs that have the same LHS X  XY Augmentation YX  YZ Augmentation X  YZ Transitivity

  15. Completeness • Axioms are complete: If F entails f , then f can be derived from F using the axioms • As a result, to determine if F entails f, use the axioms in all possible ways to generate F+(the set of possible FD’s is finite so this can be done) and see if f is in F+

  16. Generating F+ F AB C AB BCD A D AB BD AB BCDE AB CDE D E BCD  BCDE union decomp aug trans aug Thus, AB BD, AB  BCD, AB  BCDE, and AB  CDE are all elements of F+

  17. Attribute Closure • Calculating attribute closure is a more efficient way of checking entailment • The attribute closure of a set of attributes, X, with respect to a set of functional dependencies, F, (denoted X+F) is the set of all attributes, A, such that X  A • X +F1 is not necessarily the same asX +F2 • Checking entailment: Given a set of FDs, F, then X  Y if and only if X+F  Y (by union & decomposition rule)

  18. Example - Computing Attribute Closure X XF+ A {A, D, E} AB {A, B, C, D, E} (Hence AB is a key) B {B} D {D, E} F: AB  C A  D D  E AC  B Is AB  E a FD? Yes Is D C a FD? No Result: XF+ allows us to determine FDs entailed by F of the form X  Y

  19. Computation of Attribute Closure X+F closure := X; --since X  X+F repeat old := closure; if there is an FD Z  V in F such that Z  closure thenclosure := closure V untilold = closure -- If T  closure then X  T is entailed by F

  20. Computation of Attribute Closure Example Problem: Compute the attribute closure of AB with respect to the set of FDs : AB  C (a) A  D (b) D  E (c) AC  B (d) Solution: Initially closure = {AB} Using (a) closure = {ABC} Using (b) closure = {ABCD} Using (c) closure = {ABCDE}

  21. Normal Forms • Each normal form is a set of conditions on a schema that guarantees certain properties (relating to redundancy and update anomalies) • The two commonly used normal forms are third normal form (3NF) and Boyce-Codd normal form (BCNF)

  22. BCNF • Definition: A relation schema R is in BCNF if for every FD X Y associated with R either • Y  X(i.e., the FD is trivial) or • X is a superkey of R • Example: Person1(SSN, Name, Address) • The only FD is SSN Name, Address • Since SSN is a key, Person1 is in BCNF

  23. BCNF - Examples • Person (SSN, Name, Address, Hobby) • The FD SSN  Name, Address does not satisfy requirements of BCNF (since the key is (SSN, Hobby)) • HasAccount (AccountNumber, ClientId, OfficeId) • The FD AcctNum OfficeId does not satisfy BCNF requirements (since keys are (ClientId, OfficeId) and (AcctNum, ClientId))

  24. Redundancy • Suppose R has a FD A  B. If an instance has 2 rows with same value in A, they must also have same value in B (=> redundancy) • If A is a superkey, there cannot be two rows with same value of A • Hence, BCNF eliminates redundancy SSN  Name, Address SSN Name Address Hobby 1111 Joe 123 Main stamps 1111 Joe 123 Main coins

  25. Third Normal Form • A relational schema R is in 3NF if for every FD X A associated with R either: • A  X(i.e., the FD is trivial) or • X is a superkey of R or • A is part of some key • 3NF weaker than BCNF (every schema that is in BCNF is also in 3NF)

  26. 3NF Example • HasAccount (AcctNum, ClientId, OfficeId) • ClientId, OfficeId  AcctNum • OK since LHS contains a key • AcctNum  OfficeId • OK since RHS is part of a key • HasAccount is in 3NF but it might still contain redundant information

  27. 3NF Example ClientId OfficeId AcctNum 1111 Stony Brook 28315 2222 Stony Brook 28315 3333 Stony Brook 28315 • HasAccount might store redundant data: 3NF: OfficeId part of key FD: AcctNum  OfficeId Decompose to eliminate redundancy (not always possible): ClientId AcctNum 1111 28315 2222 28315 3333 28315 BCNF (only trivial FDs) OfficeId AcctNum Stony Brook 28315 BCNF: AcctNum is key FD: AcctNum OfficeId

  28. 3NF (Non) Example • Person (SSN, Name, Address, Hobby) • (SSN, Hobby) is the only key. • SSN Name violates 3NF conditions since Name is not part of a key and SSN is not a superkey

More Related