Normalization

1
Normalization

• enables the analysis of the design of a
  relational database
• gives a method for identifying the existence of
  potential problems in the design
• involves various types of normal forms

2
First, Second, and Third Normal Forms

• 1NF - Boyce-Codd (1972)
• 2NF - Boyce-Codd (1972)
• 3NF - Boyce-Codd (1972)
• BCNF - Boyce-Codd (1974)

3
Repeating Groups
SOFTWARE (PACKID, TAGNUM, INSTDATE, SOFTCOST)
4
First Normal Form

• definition - no repeating groups
• general solution - expand the primary key to
  include multiple attributes
• original primary key
• the attribute that differentiates the repeating
  group occurrences

5
Example of 1NF
FD PACKID --gt PACKNAMETAGNUM --gt COMPID PACKID,
TAGNUM --gt PACKNAME, COMPID, INSTDATE, SOFTCOST
6
Problems with 1NF

• update anomalies
• update
• inconsistent data
• insertions
• deletions
• occur because we have attributes that are
  dependent on only a portion of the primary key
• non-key attribute
• an attribute that is not part of the primary key

7
Second Normal Form

• already 1NF
• no non-key attribute is dependent on only a
  portion of the primary key.
• no partial dependencies (dependencies on less
  than the primary key)
• solution - partial dependencies are pulled out of
  the table and placed in a table that reflects
  their dependency

8
Example Solution for 2NF

• Step 1 - List the primary key and all possible
  subsets as new relations
• (PACKID,
• (TAGNUM,
• (PACKID, TAGNUM,

9
Example Solution for 2NF

• Step 2 - Place each attribute with the minimal
  primary key on which it is dependent
• SOFTWARE (PACKID, PACKNAME)
• PC (TAGNUM, COMPID)
• PACKAGE (PACKID, TAGNUM, INSTDATE, SOFTCOST)

10
Another Sample of 2NF

• PC (TAGNUM, COMPID, EMPNUM, EMPNAME, LOCATION)
• FD
• TAGNUM --gt COMPID, EMPNUM, EMPNAME, LOCATION
• EMPNUM --gt EMPNAME

11
Table for Sample of 2NF
12
Problems with 2NF

• update anomalies
• update
• inconsistent data
• insertions
• deletions
• problems occur because EMPNUM, a non-primary key,
  determines EMPNAME
• since EMPNUM is not the primary key it can appear
  in multiple rows as can the EMPNAME

13
Problems with 2NF

• determinant - any attribute or collection of
  attributes that determines another attribute

14
Third Normal Form

• Definition - any attribute (or collection of
  attributes) that determines anouther attribute is
  called a determinant.
• Definition - a relation is in third normal form
  (3NF) if it is in second normal form and if the
  only determinants it contains are candidate keys.

15
Sample Table in 2NF
PC (TAGNUM, COMPID, EMPNUM, EMPNAME, LOCATION)
16
Functional Dependencies for the Table

• TAGNUM --gt COMPID, EMPNUM, EMPNAME, LOCATION
• EMPNUM --gt EMPNAME

17
Dependency Diagram

• On page 229 Figure 6.11

18
General Solution to Decompose from 2NF to 3NF

• for each determinant that is not a candidate key,
  remove the attributes that depend on the
  determinant.
• create a new relation containing all the
  attributes that depend on the determinant.
• make the determinant the primary key for the new
  relation.
• (Very similar to decomposition from 1NF to 2NF)

19
Solution for our 2NF Table

• Two tables
• PC (TAGNUM, COMPID, EMPNUM, LOCATION)
• EMPLOYEE (EMPNUM, EMPNAME)

20
2 Tables

• PC

EMPLOYEE
21
Incorrect Decompositions

• If the original 2NF relation is decomposed
  incorrectly, the problems will still exist with
  redundant data and update anomalies
• never split a functional dependence across 2
  relations
• always preserve the original information

22
Application to Database Design

• 3NF is all that is needed
• instances exist where it is unwise to put a
  relation into 3NF
• splitting of relations requires the use of
  interrelational constraints