Normalization

1. Normalization • First Normal Form (1NF) • Boyce-Codd Normal Form (BCNF) • Third Normal Form (3NF) • Canonical Cover of FDs

2. Big Picture • DB design may be bad and has problems • Insert/Update/Delete anomaly • Inconsistent data • When a FD violates a normalization rule • Then, one or more of the problems above exist • The decomposition will solve the problem

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 are preserved • There is always a lossless, dependency-preserving decomposition in 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) L.H.S is superkey OR R.H.S consists of prime attributes

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) Primary key: ID 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 (ID, county, lotNum, area, price) County (county, taxRate) Candidate key for Lot: <county, lotNum> FDs: county taxRate area  price Decompose Lot based on area  price Lot (ID, county, lotNum, area) County (county, taxRate) Area (area, price) • Is every relation in 3NF ? YES

8. Comparison between 3NF & BCNF ? • If R is in BCNF, obviously R is in 3NF • If R is in 3NF, R may not be in BCNF • 3NF allows some redundancy and is weaker than BCNF • 3NF is a compromise to use when BCNF with good constraint enforcement is not achievable • Important: Lossless, dependency-preserving decomposition of R into a collection of 3NF relations always possible !

9. Normalization • First Normal Form (1NF) • Boyce-Codd Normal Form (BCNF) • Third Normal Form (3NF) • Canonical Cover of FDs

10. Canonical Cover of FDs

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

12. Example : Canonical Cover • Given F: • A  B, ABCD  E, EF  GH, ACDF  EG • Then the canonical cover G: • A  B, ACD  E, EF  GH The smallest set (minimal) of FDs that can generate F+

13. Computing the Canonical Cover • Given a set of functional dependencies F, how to compute the canonical cover G

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

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

16. 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}

17. 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}

18. Canonical Cover • Used to find the smallest (minimal) set of FDs that have the same closure as the original set. • Used in the decomposition of relations to be in 3NF • The resulting decomposition is lossless and dependency preserving

19. Done with Normalization • First Normal Form (1NF) • Boyce-Codd Normal Form (BCNF) • Third Normal Form (3NF) • Canonical Cover of FDs

20. Summary (I) • Functional Dependencies • How to derive more FDs • FDs   Keys • Functional closure and Attribute closure • Decomposition • Lossy vs. Lossless • How to make the decomposition and how to check • Dependency Preservation • Whether all dependencies are preserved or some FDs are lost

21. Summary (II) • Normalization • Check whether a relation satisfies BCNF or 3rd NF • If there are violations, then how to decompose • Canonical Cover • Given a set of FDs, how to find its canonical cover

22. Questions ?