Database Design Concepts: Normalization

1
Database Design Concepts Normalization
2
Objectives

• Present the idea of functional dependence.
• Define first normal form (1NF), second normal
  form (2NF), and third normal form (3NF).
• Describe the problems associated with tables
  (relations) that are not in 1NF, 2NF, or 3NF,
  along with the mechanism for converting to all
  three.
• Discuss the problems associated with incorrect
  conversions to 3NF.

3
The Normalization Process

• A set of steps that enables you to identify the
  existence of potential problems, called update
  anomalies, in the design of a relational
  database.
• The goal of normal forms is to allow you to take
  a table or collection of tables and produce a new
  collection of tables that represents the same
  information but is free of problems.

4
Normal Forms

• First Normal Form
• Multi-value attributes have been removed
• Second Normal Form
• Partial dependencies have been removed
• Third Normal Form
• Transitive dependencies have been removed

5
Functional Dependencies
A constraint between two attributes or two sets
of attributes.

• SSN Name, Address, Birthdate
• VIN Make, Model, Color
• ISBN Title, Author_Name

SSN, VIN and ISBN are all Determinants of their
respective tables
6
Example of Functional Dependencies
Employee1
EMP_ID
Name
Dept
Salary
Employee2
EMP_ID
Course_Title
Name
Dept
Salary
Date_Completed
7
Table with Multi-valued attributes
8
First Normal Form
A relation which contains no multi-valued
attributes.
9
Second Normal Form

• Must be in First Normal Form to start with
• If the primary key consist of only one attribute
  then you are already in 2NF.
• No non-key attributes exist in the relation.
• Every non-key attribute is functionally dependent
  on the full set of primary key attributes.

10
Example of Partial Functional Dependencies
Employee2
EMP_ID
Course_Title
Name
Dept
Salary
Date_Completed
Causes Modification, Insert and Delete Anomalies.
11
Second Normal Form 2
Employee1
EMP_ID
Name
Dept
Salary
Emp_Course
EMP_ID
Course_Title
Date_Completed
12
Third Normal Form

• Relation must be in Second Normal Form to begin
  with
• The Relation must not have any transitive
  dependencies.
• Transitive Dependency
• A functional dependency between two or more
  non-key attributes

13
Example of Transitive Dependencies
Insertion Anomalies
Sales
Deletion Anomalies
Modification Anomalies
14
Third Normal Form 2
Sales Person is not a good Primary key
15
Third Normal Form 3