BCNF AND 4NF - Normalization Why&What 1stNormalForm...

Normalization

Why & What

1st Normal Form

2nd Normal Form

3rd Normal Form

Boyce-Codd Normal Form

4th Normal Form

Normalization - Why

Eliminate anomalies

Avoid duplication

Increase flexibility and stability

Reduce maintenance

Normalization - What?!?

Analysis of functional dependencies between
attributes

Building several smaller tables from larger ones

Decomposing relations with anomalies to
produce smaller, well-structured relations

Reducing complexity & increasing stability

Normalization - What (2)

Series of Steps

Recipe for constructing a “good” physical
model of a database from a logical model

Applied to all existing tables, including
ones produced by earlier normalization
steps

Example
Sales
(Order# , Date, CustID, Name, Address,
City, State, Zip, {Product#, ProductDesc,
Price, QuantityOrdered}, Subtotal, Tax,
S&H, Total)

What are the problems with using a single
table for all order information?

Think of it as a single spreadsheet

Problems

Implementing Repeating Groups

Duplication of Data (customer name &
address)

Unnecessary Data (subtotal, total, tax)

Others
Normalization is a process to eliminate
these problems.

1st Normal Form

Eliminate Repeating Groups

1st Normal Form has no repeating
groups

Create definition with all other attributes,
remove the repeat {}, and change the
primary key to include the “key” for the
repeating group.

Example
Sales
(Order# , Date, CustID, Name, Address,
City, State, Zip, Product# , ProductDesc,
Price, QuantityOrdered, Subtotal, Tax,
S&H, Total)

2nd Normal Form

Remove all partial functional
dependencies
