- 124 Views
- Uploaded on
- Presentation posted in: General

Normalization- 3NF

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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.

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

Normalization- 3NF

Instructor: Mohamed Eltabakh

meltabakh@cs.wpi.edu

Part III

- 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

- 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

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)

- 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

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

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

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

- Canonical cover of F is the minimal subset of FDs (G), where

Every FD in the canonical cover is needed, otherwise some dependencies are lost

- Example :
- Given F:
- A B, ABCD E, EF GH, ACDF EG

- Then the canonical cover G:
- A B, ACD E, EF GH

- Given a set of functional dependencies F, how to compute the canonical cover G

Use the next algorithm for this step

- 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

- Check A:
- 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

- Check C:

- 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

- Check E:
- Test ACDF EG
- None of the H.L.S can be deleted

- 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

- Check G:
- Test ACDF EG
- Check E:
- {ACDF}+ = {A B C D F E G} E can be deleted

- Check E:
- Now the set is: {A B, ACD E, EF GH, ACDF G}

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

- {ACDF}+ = {A B C D F E G} G can be deleted

- Check G:

The canonical cover is:

{A B, ACD E, EF GH}

- Used in the decomposition of relations to be in 3NF
- The resulting decomposition is lossless and dependency preserving

- 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