Review: Database Design and Normalization

1
Review Database Designand Normalization
2
Review of Database Design

• Design Considerations
• Purpose of the database
• Tables and fields
• Relationships between tables
• Avoid data redundancy
• Ensure data consistency

3
Normalization

• First Normal Form
• Eliminate Repeating Groups
• Second Normal Form
• Eliminate Redundancy
• Third Normal Form
• Eliminate Non-key Columns Not Dependent on the Key

4
Normalization Example

• Un-normalized table with internal redundancy and
  repeating fields
• How can you track more than two clients?

Client 1
Client 2
5
Apply First Normal Form

• Eliminate Repeating Groups
• Side-effect
• internal redundancy (record level)
• redundant employee information

Emp. 10
Emp. 17
6
Apply Second Normal Form

• Eliminate data redundancy
• Create a separate Employee table
• Primary Key EmployeeID
• Note EmployeeID 10 and 17 only saved once

7
Apply Second Normal Form (2)

• No partial dependencies Every non-key column
  must be fully dependent on the entire primary key
• Primary Key ClientID
• Foreign Key EmployeeID

redundant
ProvinceName is dependent on ProvinceCode (a
non-key field)
8
Apply Third Normal Form

• Eliminate Non-Key Columns Not Dependent on the
  Key
• Move ProvinceName to a new look-up table

9
Your Turn

• Normalize the CustomerOrders table
• Is the table in First, Second or Third Normal
  Form?