# Normalization- 3NF - PowerPoint PPT Presentation

1 / 18

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)

I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.

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

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