normalization 3nf
Download
Skip this Video
Download Presentation
Normalization- 3NF

Loading in 2 Seconds...

play fullscreen
1 / 18

Normalization- 3NF - PowerPoint PPT Presentation


  • 402 Views
  • Uploaded on

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)

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

PowerPoint Slideshow about 'Normalization- 3NF' - nimrod


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:

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:

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

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
ad