Créer une présentation
Télécharger la présentation

Télécharger la présentation
## Normalization- 3NF

- - - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - - -

**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) • Next lecture is mostly revision + short quiz**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**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)**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**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**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**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**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**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**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**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**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**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}**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}**Use of Canonical Cover**• Used in the decomposition of relations to be in 3NF • The resulting decomposition is lossless and dependency preserving**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