1 / 18

Normalization- 3NF

Normalization- 3NF. Instructor: Mohamed Eltabakh meltabakh@cs.wpi.edu. Part III. Announcements. Homework 2 is due NOW !!! Homework 3 will be out today (Nov. 15) and due on Nov. 22, 8:00AM Midterm on Nov. 22 Until Normalization (Normalization is included)

nimrod
Télécharger la présentation

Normalization- 3NF

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- 3NF Instructor: Mohamed Eltabakh meltabakh@cs.wpi.edu Part III

  2. Announcements • Homework 2 is due NOW !!! • Homework 3 will be out today (Nov. 15) and due on Nov. 22, 8:00AM • Midterm on Nov. 22 • Until Normalization (Normalization is included) • Next lecture is mostly revision + short quiz

  3. Third Normal Form: Motivation • There are some situations where • BCNF is not dependency preserving • Solution: Define a weaker normal form, called Third Normal Form (3NF) • Allows some redundancy (we will see examples later) • But all FDs can be checked on individual relations without computing a join • There is always a lossless-join, dependency-preserving decomposition into 3NF

  4. Normal Form : 3NF Relation R is in 3NF if, for every FD in F+ α  β, where α ⊆ R and β ⊆ R, at least one of the following holds: • α → β is trivial (i.e.,β⊆α) • α is a superkey for R • Each attribute in β-α is part of a candidate key (prime attribute)

  5. Testing for 3NF • Use attribute closure to check for each dependency α → β, if α is a superkey • If α is not a superkey, we have to verify if each attribute in (β- α) is contained in a candidate key of R

  6. 3NF: Example Lot (ID, county, lotNum, area, price, taxRate) Candidate key: <county, lotNum> FDs: county  taxRate area  price • Is relation Lot in 3NF ? NO Decomposition based on county  taxRate Lot (ID, county, lotNum, area, price) County (county, taxRate) • Are relations Lot and County in 3NF ? Lot is not

  7. 3NF: Example (Cont’d) Lot (propNo, county, lotNum, area, price) County (county, taxRate) Candidate key for Lot: <county, lotNum> FDs: county taxRate area  price Decomposition based on area  price Lot (propNo, county, lotNum, area) County (county, taxRate) Area (area, price) • Is every relation in 3NF ? YES

  8. Main Idea of the 3NF Decomposition • Use the decomposition algorithm as in BCNF • But to ensure dependency preservation • If α  β is not preserved, then create relation (α, β) where α is the key • To ensure the result of decomposition is dependency-preserving and lossless • Use the canonical cover in the decomposition

  9. Canonical Cover of FDs • Canonical Cover (Minimal Cover) = G • Is the smallest set of FDs that produce the same F+ • There are no extra attributes in the L.H.S or R.H.S of and dependency in G • Given set of FDs (F) with functional closure F+ • Canonical cover of F is the minimal subset of FDs (G), where G+ = F+ Every FD in the canonical cover is needed, otherwise some dependencies are lost

  10. Example : Canonical Cover • Example : • Given F: • A  B, ABCD  E, EF  GH, ACDF  EG • Then the canonical cover G: • A  B, ACD  E, EF  GH

  11. Computing the Canonical Cover • Given a set of functional dependencies F, how to compute the canonical cover G Use the next algorithm for this step

  12. Finding Extraneous Attributes

  13. Example : Canonical Cover(Lets Check L.H.S) • Given F= {A  B, ABCD  E, EF  G, EF H, ACDF  EG} • Union Step: {A  B, ABCD  E, EF GH, ACDF  EG} • Test ABCD  E • Check A: • {BCD}+ = {BCD}  A cannot be deleted • Check B: • {ACD}+ = {A B C D E}  Then B can be deleted • Now the set is: {A  B, ACD  E, EF  GH, ACDF  EG} • Test ACD  E • Check C: • {AD}+ = {ABD}  C cannot be deleted • Check D: • {AC}+ = {ABC}  D cannot be deleted

  14. Example: Canonical Cover(Lets Check L.H.S-Cont’d) • Now the set is: {A  B, ACD  E, EF  GH, ACDF  EG} • Test EF  GH • Check E: • {F}+ = {F}  E cannot be deleted • Check F: • {E}+ = {E}  F cannot be deleted • Test ACDF  EG • None of the H.L.S can be deleted

  15. Example: Canonical Cover(Lets Check R.H.S) • Now the set is: {A  B, ACD  E, EF  GH, ACDF  EG} • Test EF  GH • Check G: • {EF}+ = {E F H}  G cannot be deleted • Check H: • {EF}+ = {E F G}  H cannot be deleted • Test ACDF  EG • Check E: • {ACDF}+ = {A B C D F E G}  E can be deleted • Now the set is: {A  B, ACD  E, EF  GH, ACDF G}

  16. Example: Canonical Cover(Lets Check R.H.S-Cont’d) • Now the set is: {A  B, ACD  E, EF  GH, ACDF G} • Test ACDF  G • Check G: • {ACDF}+ = {A B C D F E G}  G can be deleted Now the set is: {A  B, ACD  E, EF GH} The canonical cover is: {A  B, ACD  E, EF  GH}

  17. Use of Canonical Cover • Used in the decomposition of relations to be in 3NF • The resulting decomposition is lossless and dependency preserving

  18. Summary of Normalization • Normalization forms • First Normal Form (1NF) • BCNF • Third Normal Form (2NF) • Fourth Normal Form (4NF) – Not covered • Used to ensure the database design is in a good form • Decomposing the relation according to functional dependencies

More Related