1 / 59

3.1 Functional Dependencies

3.1 Functional Dependencies. Anomalies: caused in some relation schemas by the presence of certain dependencies Functional Dependencies Multivalued dependencies Normalization 3.1.1 Definition of Functional Dependency

belmonte
Download Presentation

3.1 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. 3.1 Functional Dependencies • Anomalies: caused in some relation schemas by the presence of certain dependencies • Functional Dependencies • Multivalued dependencies • Normalization 3.1.1 Definition of Functional Dependency • If two tuples of R agree on all of the attributes A1, A2, …, An, then they must also agree on all of another list of attributes B1, B2, …, Bm. A1 A2 … An → B1 B2 … Bm (See Fig. 3.1) Database Systems

  2. 3.1 Functional Dependencies Database Systems

  3. 3.1 Functional Dependencies • (See Fig. 3.2) • title year → length genre studioName V • title year → starName X • 3.1.2 Keys of Relations • A key for a relation R: {A1, A2, …, An} • 1) Those attributes functionally determine all other • attributes of the relation. • 2) A key must be minimal. • Key: {title, year, starName} • Primary key Database Systems

  4. 3.1 Functional Dependencies Database Systems

  5. 3.1 Functional Dependencies • 3.1.3 Superkeys • A superkey need not satisfy the second condition: minimality. • Superkey: {title, year, starName, length, studioName} Database Systems

  6. 3.2 Rules About Functional Dependencies • Deducing certain other FD‘s e.g., A → B, B → C ⇒ A → C 3.2.1 Reasoning about Functional Dependency • Two sets of FD's S and T are equivalent if the set of relation instances satisfying S is exactly the same as the set of relation instances satisfying T. • A set of FD's S follows from a set of FD's T if every relation instance that satisfies all the FD's in T also satisfies all the FD's in S. Database Systems

  7. 3.2 Rules About Functional Dependencies • 3.2.2 The Splitting/Combining Rule • The splitting rule • A1 A2 … An → B1 B2 … Bm • A1 A2 … An → Bi for i=1, 2, …, m • The combining rule • A1 A2 … An → Bi for i=1, 2, …, m • A1 A2 … An → B1 B2 … Bm • There is no splitting rule for left sides. • title year → length • title → length X • year → length X Database Systems

  8. 3.2 Rules About Functional Dependencies • 3.2.3 Trivial Functional Dependencies • A trivial FD has a right side that is a subset of its left side. • e.g., title year → title • Trivial-dependency rule: • A1 A2 … An → B1 B2 … Bm is equivalent to A1 A2 … An • → C1 C2 … Ck where the C’s are all those B’s that are not • also A’s. • (See Fig. 3.3) Database Systems

  9. 3.2 Rules About Functional Dependencies Database Systems

  10. 3.2 Rules About Functional Dependencies • 3.2.4 Computing the Closure of Attributes • The closure of {A1, A2, …, An} under the FD's in S is the set of attributes B such that every relation that satisfies all the FD's in set S also satisfies A1 A2 … An → B. • {A1, A2, …, An}+ • Algorithm 3.7: closure of a set of attributes • 1) Split the FD’s of S, so each FD in S has a single • attribute on the right. • 2) Initialize X to be {A1, A2, …, An}. • 3) Repeatedly search for some FD B1 B2 … Bm→ C such • that all of B1, B2, …, Bm are in X, but C is not. Add C • to X. Database Systems

  11. 3.2 Rules About Functional Dependencies 4) X , after no more attributes can be added to it, is the correct value of {A1, A2, …, An}+. (See Fig. 3.4) • A relation {A, B, C, D, E, F} FD’s: AB → C, BC → AD, D → E, CF → B {A, B} + = {A, B, C, D, E} • Deduced FD’s AB → D, E But, no FD such as D → A is deduced. Database Systems

  12. 3.2 Rules About Functional Dependencies Database Systems

  13. 3.2 Rules About Functional Dependencies 3.2.5 Why the Closure Algorithm Works • The closure algorithm claims only true FD's. • The closure algorithm discovers all true FD's. 3.2.6 The Transitive Rule • If A1 A2 … An → B1 B2 … Bm and B1 B2 … Bm → C1 C2 … Ck hold in relation R, then A1 A2 … An → C1 C2 … Ck also holds in R. • title year → studioName studioName → studioAddr title year → studioAddr Database Systems

  14. 3.2 Rules About Functional Dependencies 3.2.7 Closing Sets of Functional Dependencies • To represent the full set of FD's for a relation • A basis for a relation • A minimal basis B: 1) All the FD’s in B have singleton right sides. 2) If any FD is removed from B, the result is no longer a basis. 3) If for any FD in B we remove one or more attributes from the left side of F, the result is no longer a basis. Database Systems

  15. 3.2 Rules About Functional Dependencies • the full set of derived FD's: A → B, A → C, B → A, B → C, C → A, and C → B minimal bases {A → B, B → A, B → C, C → B}, {A → B, B → C, C → A} 3.2.8 Projecting Functional Dependencies • Suppose we have a relation R with set of FD’s S, and we project R by computing R1= πL(R), for some list of attributes R. What FD's hold in R1? Database Systems

  16. 3.2 Rules About Functional Dependencies • Computing the projection of functional dependencies S 1) Follow from S 2) Involve only attributes of R1 • However, in general, the calculation of the FD's for R1 is exponential in the number of attributes of R1. • Algorithm 3.12: projecting a set of functional dependencies 1) Initially, T is empty. 2) For each set of attributes X that is a subset of the attributes of R1, compute X+. Add to T all nontrivial FD’s X → A such that A is both in X+ and an attribute of R1. Database Systems

  17. 3.2 Rules About Functional Dependencies 3) Constructing a minimal basis by modifying T: a. If there is an FD F in T that follows from the other FD’s in T, remove F from T. b. Let Y → B be an FD in T, with at least two attributes in Y, and let Z be Y with one of its attributes removed. If Z → B follows from the FD’s in T, then replace Y → B by Z → B. c. Repeat the above steps in all possible ways until no more changes to T can be made. Database Systems

  18. 3.2 Rules About Functional Dependencies • Some obvious simplifications: 1) Closing the empty set and the set of all attributes cannot yield a nontrivial FD. 2) If we already know that the closure of some set X is all attributes, then we cannot discover any new FD's by closing supersets of X. • R(A, B, C, D) with FD’s A → B, B → C, and C → D R1(A, C, D) ? {A}+ = {A, B, C, D} ⇒ A → C, A → D {C}+ = {C, D} ⇒ C → D, {D}+ = {D} ⇒ no more FD's {C, D}+ = {C, D} ⇒ no more FD's Transitive rule ⇒ A → C, C → D Database Systems

  19. 3.3 Design of Relational Database Schema • The repetition of the information is redundant. (See Fig. 3.6) 3.3.1 Anomalies • Anomalies: 1) Redundancy 2) Update anomalies 3) Deletion anomalies 3.3.2 Decomposing Relations • The accepted way to eliminate these anomalies is to decompose relations. Database Systems

  20. 3.3 Design of Relational Database Schema Database Systems

  21. 3.3 Design of Relational Database Schema • {title, year, length, genre, studioName, starName} {title, year, length, genre, studioName} {title, year, starName} (See Fig. 3.7) 3.3.3 Boyce-Codd Normal Form • A relation R is in BCNF if and only if: whenever there is a nontrivial FD A1 A2 … An → B1 B2 … Bm for R, it is the case that {A1, A2, …, An} is a superkey for R. • title year → length genre studioName But {title year} is not a superkey. Database Systems

  22. 3.3 Design of Relational Database Schema Database Systems

  23. 3.3 Design of Relational Database Schema • Fig. 3.7(a) is in BCNF • Any two-attribute relation {A, B} is in BCNF 1) There are no nontrivial FD. Key: {A, B} 2) A → B holds, but B → A does not hold. Key: {A} 3) B → A holds, but A → B does not hold. Key: {B} 4) Both A → B and B → A hold. Key: {A}, {B} Database Systems

  24. 3.3 Design of Relational Database Schema 3.3.4 Decomposition into BCNF • The properties: 1) The subsets are the schemas of relations in BCNF. 2) We need to be able to reconstruct the original relation instance exactly from the decomposed relation instances. • For two overlapping relation schemas, one is all the attributes involved in the violating FD, and the other is the left side of the FD plus all the attributes not involved in the FD. (See Fig. 3.8) Database Systems

  25. 3.3 Design of Relational Database Schema Database Systems

  26. 3.3 Design of Relational Database Schema • title year → length genre studioName {title, year, length, genre, studioName} {title, year, starName} • A relation {title, year, studioName, president, preAddr} title year → studioName studioName → president president → presAddr {title, year, studioName} {studioName, president} {president, presAddr} Database Systems

  27. 3.3 Design of Relational Database Schema • Algorithm 3.20: BCNF decomposition algorithm 1) Check whether R is in BCNF. 2) If there are BCNF violations, let one be X → Y. Use Algorithm 3.7 to compute X+. Choose R1 =X+ as one relation schema and let R2 have attributes X and those attributes of R that are not in X+. 3) Using Algorithm 3.12 to compute the sets of FD’s for R1 and R2; let these be S1 and S2, respectively. 4) Recursively decompose R1 and R2 using this algorithm. Database Systems

  28. 3.4 Decomposition: The Good, Bad, and Ugly • Three properties: 1) Elimination of Anomalies 2) Recoverability of Information 3) Preservation of Dependencies • The BCNF decomposition of Algorithm 3.20 gives us (1) and (2), but does not necessarily give us all three. 3.4.1 Recovering Information from a Decomposition • Lossless join: the projections of the original tuples can be joined again to produce all and only the original tuples. Database Systems

  29. 3.4 Decomposition: The Good, Bad, and Ugly e.g., R(A, B, C) B → C (BCNF violation) {A, B}, {B, C} t=(a, b, c) and v=(d, b, e) u=(a, b) and w=(b, e) after join x=(a, b, e) in fact x=t=(a, b, c) • If we decompose a relation according to Algorithm 3.20, then the original relation can be recovered exactly by the nature join. Database Systems

  30. 3.4 Decomposition: The Good, Bad, and Ugly e.g., Neither of the FD’s B → A nor B → C holds A B A B C A B C 1 2 1 2 3 1 2 3 ⇒ 4 2 ⇒ 1 2 5 4 2 5 B C 4 2 3 2 3 4 2 5 2 5 Database Systems

  31. 3.4 Decomposition: The Good, Bad, and Ugly 3.4.2 The Chase Test for Lossless Join • Three important things to remember are: 1) The natural join is associative and commutative. 2) Any tuple t in R is surely in πS1(R)∞πS2(R)∞…∞πSk(R). 3) As a consequence, πS1(R)∞πS2(R)∞…∞πSk(R)=R when the FD’s in F hold for R if and only if every tuple in the join is also in R. • Remember that our goal is to use the given set of FD’s F to prove that t is really in R. • Example (See Fig. 3.9) Database Systems

  32. 3.4 Decomposition: The Good, Bad, and Ugly Database Systems

  33. 3.4 Decomposition: The Good, Bad, and Ugly • Example the FD’s A → B, B → C and CD → A A B C D A B C D A B C D a b1 c1 d ⇒ a b1 c d ⇒ a b1 c d a b1 c d2 a b1 c d2 a b1 c d2 a3 b c d a3 b c d a b c d 3.4.3 Why the Chase Works • Two issues to address: 1) When the chase results in a row that matches the tuple t, why must the join be lossless? Database Systems

  34. 3.4 Decomposition: The Good, Bad, and Ugly 2) When, after applying FD’s whenever we can, we still find no row of all unsubscripted variables, why must the join not be lossless? • Example the FD’s B → AD and {A, B}, {B, C}, {C, D} A B C D A B C D a b c1 d1 ⇒ a b c1 d1 a2 b c d2 a b c d1 a3 b3 c d a3 b3 c d Database Systems

  35. 3.4 Decomposition: The Good, Bad, and Ugly 3.4.4 Dependency Preservation • Example Bookings(title, theater, city) theater → city, title city → theater No single attribute is a key. Keys: {title, city}, {theater, title} theater → city: a BCNF violation {theater, city}, {theater, title} After join, the results violate title city → theater. Database Systems

  36. 3.5 Third Normal Form • The solution to the problem illustrated above is to relax BCNF requirement slightly, in order to allow the occasional relation schema that can not be decomposed into BCNF relations without losing the ability to check the FD’s. • The relaxed condition called third normal form. 3.5.1 Definition of Third Normal Formation • A relation R is in 3NF if: whenever A1 A2 … An → B1 B2 … Bm is a nontrivial FD, either {A1, A2, …, An} is a superkey, or those of B1, B2, …, Bm that are not among the A’s, are each a member of some key. Database Systems

  37. 3.5 Third Normal Form 3.5.2 The Synthesis Algorithm for 3NF Schemas • Justification: 1) The relations of the decomposition are all in 3NF. 2) The decomposition has a lossless join. 3) The decomposition has the dependency-preservation property. • Algorithm 3.26: Synthesis of 3NF relations with a lossless join and dependency preservation 1) Find a minimal basis for F, say G. 2) For each functional dependency X → A in G, use XA as the schema of one of the relations in the decomposition. Database Systems

  38. 3.5 Third Normal Form 3) If none of the sets of relations from Step 2 is a superkey for R, add another relation whose schema is a key for R. • Example R(A, B, C, D, E) with FD’s AB → C, C → B and A → D ⇒ S1(A, B, C), S3(A, D), S4(A, B, E) 3.5.3 Why the 3NF Synthesis Algorithm Works • Three things: 1) Lossless join(the chase test) 2) Dependency preservation 3) 3NF(a minimal basis ⇒ 3NF) Database Systems

  39. 3.6 Multivalued Dependencies • A generalization of the notion of a functional dependency. 3.6.1 Attribute Independence and Its Consequent Redundancy • The most common source of redundancy in BCNF schemas is an attempt to put two or more set-valued properties of the key into a single relation. • Example (See Fig. 3.10) There are no nontrivial FD's, it follows that all five attributes form the only key and that there are no BCNF violations. Database Systems

  40. 3.6 Multivalued Dependencies Database Systems

  41. 3.6 Multivalued Dependencies 3.6.2 Definition of Multivalued Dependencies • The MVD A1 A2 … An →→ B1 B2 … Bm holds if For each pair of tuple t and u of relation R that agree on all the A’s, we can find in R some tuple v that agrees: 1) With both t and u on the A’s 2) With t on the B’s, and 3) With u on all attributes of R that are not among A’s or B’s (See Fig. 3.11) • Example name →→ street city Database Systems

  42. 3.6 Multivalued Dependencies Database Systems

  43. 3.6 Multivalued Dependencies 3.6.3 Reasoning About Multivalued Dependencies • The trivial MVD’s The MVD A1 A2 … An →→ B1 B2 … Bm holds in any relation if {B1, B2, …, Bm}  {A1, A2, …, An}. • The transitive rule If A1 A2 … An →→ B1 B2 … Bm and B1 B2 … Bm →→ C1 C2 … Ck hold for some relation, then so does A1 A2 … An →→ C1 C2 … Ck . • MVD's do not obey the splitting part of the splitting/combining rule. name →→ street city ≠〉 name →→ street • FD promotion: Every FD is an MVD. Database Systems

  44. 3.6 Multivalued Dependencies • Complementation rule If A1 A2 … An →→ B1 B2 … Bm is an MVD for relation R, then R also satisfies A1 A2 … An →→ C1 C2 … Ck, where the C’s are all attributes of R not among the A’s and B’s. name →→ street city ⇒ name →→ title year • More trivial MVD’s If all the attributes of relation R are {A1, A2, …, An, B1, B2, …, Bm} then A1 A2 … An →→ B1 B2 … Bm holds in R. Database Systems

  45. 3.6 Multivalued Dependencies 3.6.4 Fourth Normal Form • In this normal form, all nontrivial MVD are eliminated. • A relation R is in 4NF if whenever A1 A2 … An →→ B1 B2 … Bm is a nontrivial MVD, {A1, A2, …, An} is a superkey. • Example name →→ street city (name is not a superkey) • 4NF ⇒ BCNF, but no converse Database Systems

  46. 3.6 Multivalued Dependencies 3.6.5 Decomposition into Fourth Normal Form • Algorithm 3.33: decomposition into fourth normal form 1) Find a 4NF violation in R, say A1 A2 … An →→ B1 B2 … Bm, where {A1, A2, …, An} is not a superkey. 2) If there is such a 4NF violations, break the schema for the relation R that has the 4NF violation into two schemas: a. R1, whose schema is the A’s and the B’s. b. R2, whose schema is the A’s and all attributes of R that are not among the A’s or B’s. Database Systems

  47. 3.6 Multivalued Dependencies 3) Find the FD’s and MVD’s that hold in R1 and R2. Recursively decompose R1 and R2 with respect to their projected dependencies. • Example {name, street, city} {name, title, year} 3.6.6 Relationships Among Normal Forms • (See Fig. 3.12 and Fig. 3.13) Database Systems

  48. 3.6 Multivalued Dependencies Database Systems

  49. 3.6 Multivalued Dependencies Database Systems

More Related