Normalization 3nf
This presentation is the property of its rightful owner.
Sponsored Links
1 / 18

Normalization- 3NF PowerPoint PPT Presentation


  • 111 Views
  • Uploaded on
  • Presentation posted in: General

Normalization- 3NF. Instructor: Mohamed Eltabakh [email protected] 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)

Download Presentation

Normalization- 3NF

An Image/Link below is provided (as is) to download presentation

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

Presentation Transcript


Normalization 3nf

Normalization- 3NF

Instructor: Mohamed Eltabakh

[email protected]

Part III


Announcements

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

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

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

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

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

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

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

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


Finding extraneous attributes

Finding Extraneous Attributes


Example canonical cover lets check l h s

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

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

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

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

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

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


  • Login