1 / 19

Normalization

Normalization. Reading Assignments. Database Systems The Complete Book: Chapters 3.6, 3.7, 3.8 Following lecture slides are modified from Jeff Ullman’s slides for Fall 2002 -- Stanford. Closures. Closure of attributes (A + ): find keys

triage
Télécharger la présentation

Normalization

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

  2. Reading Assignments • Database Systems The Complete Book: Chapters 3.6, 3.7, 3.8 • Following lecture slides are modified from Jeff Ullman’s slides for Fall 2002 -- Stanford CSCE 520

  3. Closures • Closure of attributes (A+): find keys • Closure of FDs (S+): projection of FDs to decompositions of schema • Canonical cover: minimize the number of functional dependencies • Important for updates CSCE 520

  4. Canonical Cover • Combine FDs if possible and eliminate extraneous attributes: Given a set of FDs S, and an FD XY • Attribute A is extraneous in X if S logically implies (S-{XY})  {(X-A)Y}. • Attribute A is extraneous in Y if the set of FDs (S-{XY})  {X(Y-A)} logically implies S. CSCE 520

  5. Canonical Cover • No functional dependency in Sc contains an extraneous attribute • Each left side of a functional dependency in Sc is unique. CSCE 520

  6. Example Canonical Cover • Given: • A  BC • B  C • A  B • AB  C • Combine 1 and 3 into: A BC • From 2 and 4: A is extraneous in 4: BC • C is extraneous in 1: A  B • Result: AB, BC CSCE 520

  7. Problems of Relational Database Design • Loss of information (lossless-join) • Inability to represent certain information (dependency preservation) • Repetition of information (normal forms) CSCE 520

  8. Desirable Properties of Decomposition – Lossless-join • Lossless-Join: Let R be a relation schema, S a set of FDs on R, R1 and R2 a decomposition of R. R1 and R2 form a lossless-join decomposition if at least one of the following functional dependencies are in S+ • R1  R2  R1 • R1  R2  R2 CSCE 520

  9. Create Lossless-Join Decomposition • Let R be the DB Schema and f: X  Y an FD in S+ • Decompose R into two relations • R1(X,Y) • R2(R-Y) • Continue for each decomposed relation until cannot be decomposed any more CSCE 520

  10. Desirable Properties of Decomposition – Dependency Preserving • Dependency Preservation: all dependencies that hold on the original schema should be able to test on individual schemas after decomposition. • Testing dependency preservations on projections is straight forward. CSCE 520

  11. Testing dependency preservations on decomposition • Let S be the original set of FDs on R, and S’ the union of FDs on projections R1, R2,…,Rn. • A decomposition is dependency preserving if S’+=S+ CSCE 520

  12. Desirable Properties of Decomposition – Avoid Redundancy FD: O.Name  O.Address, O.Phone D.Name, D.Breed  D.Color, D.Age CSCE 520

  13. Decomposition and FDs • Functional dependencies: can be used in designing a relational database to remove the undesired properties • Normalization using FDs: • Boyce-Codd Normal Form (BCNF) • 3rd Normal Form (3NF) CSCE 520

  14. Boyce-Codd Normal Form • A relation is in Boyce-Codd Normal Form if for all FDs X  A in S+ over R at least one of the followings hold: • X  A is a trivial FD • if X  A is a nontrivial FD then X is a superkey for schema R • Example: • R(Name,Breed,Date, Kennel) • FD: Name,Breed,Date  Kennel • R is in BCNF CSCE 520

  15. Decomposition into BCNF • Compute S+ (for FDs in S); • if there is a R that is not BCNF then - let XA a non-trivial FD on R s.t. XR is not in S+ and X  A = ; - Decompose R into: • R1(R-A) • R2(X,A) CSCE 520

  16. BCNF • Not every BCNF decomposition is dependency preserving • Example: • FDs: AB  C and C B • Keys: {A,B} and {A,C} • C  B is BCNF violation, therefore need to decompose to R1=(AC) and R2=(BC) • Decomposition cannot enforce AB C! CSCE 520

  17. Third Normal Form • Modifies BCNF conditions so no need to decompose in this problem situation • An attribute is prime if it is member of any key • X  A violates 3NF if and only if X is not a superkey and also A is not a prime. CSCE 520

  18. 3NF Conditions • A relation is in 3NF if for all FDs X  A in S+ over R at least one of the followings hold: • X  A is a trivial FD • if X  A is a nontrivial FD then X is a superkey for schema R • Each attribute B in A-X is contained in a candidate key for R CSCE 520

  19. 3NF and BCNF • BCNF gives • Lossless-join • No-redundancy • Dependency preservation not always possible • 3NF gives • Lossless-join • Dependency preservation • May have null values (transitive dependencies) CSCE 520

More Related