Normalization- 3NF

# Normalization- 3NF

Télécharger la présentation

## Normalization- 3NF

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