Table Normalization

1
Table Normalization
Making Sense of Your Data In Five Easy Well,
Five Steps(Just Kidding)
2
Introduction

• Relational Database
• Relations vs. Tables
• 5 Normal Forms of Concern

3
First Normal Form (1NF)

• First normal form The table must be flat.
• All occurrences of a record type must contain the
  same number of columns.
• No column can contain repeating elements.

4
1NF Number of Columns
5
1NF Repeating Elements
6
1NF Compliant
7
Second Normal Form (2NF)

• All non-key columns must be related to all key
  columns
• Implied requirements
• Key column established
• All non-key columns describe the key column entry

8
2NF Key and Non-key Columns
9
2NF Compliant Choices
10
Third Normal Form (3NF)

• Non-key columns cannot be dependent on other
  non-key columns
• Maintains relationship between non-key columns
  and the key column

11
3NF Violation Examples

• Area code column based on City column of author
  address
• Total price column of order entry based on
  quantity column and unit price column (calculated
  value)

12
3NF Calculated Column
13
Fourth (Boyce-Codd) Normal Form (4NF)

• Only related data entities may be included in a
  single table
• Title-Author-AuthorAddr1

14
4NF Unrelated Data Entries
15
4NF Compliant (One Solution)
16
Fifth Normal Form (5NF)

• Any table that has been divided into multiple
  tables must be capable of being reconstructed to
  its exact original structure by one or more joins.

17
5NF Initial Table
18
5NF Initial Table (contd)
19
5NF Resultant Tables (Normalized)
20
5NF Query Recreating the Table
21
Review

• 1NF Flat, Column Count Equal, No Repeating
  Elements in a Column
• 2NF Non-key columns related to key columns
• 3NF No interdependent non-key columns
• 4NF (Boyce-Codd) Only related entities
• 5NF Reconstruction must be possible

22
Questions?

• References
• Microsoft, Inc. (2000). Database Normalization
  Basics (Q209534) Retrieved 11/15/2001 from
  http//support.microsoft.com/default.aspx?scidkb
  en-usQ209534
• Syngress Media, Inc. (1999). MCDBA SQL Server 7.0
  Database Design Study Guide (Exam 70-29).
  Osborne/McGraw-Hill, New York. (ISBN
  0-07-212078-9)
• Litwin, P., et. al. (1997). Access 97
  Developers Handbook, Third Edition. Sybex, San
  Francisco. (ISBN 0-7821-1941-7).