Normalization

[attachment=16360]

Before Normalization
1. Begin with a list of all of the fields that must appear in the database. Think of this as one big table.
2. Do not include computed fields
3. One place to begin getting this information is from a printed document used by the system.
4. Additional attributes besides those for the entities described on the document can be added to the database.


Normalization: First Normal Form
• Separate Repeating Groups into New Tables.
• Repeating Groups Fields that may be repeated several times for one document/entity
• Create a new table containing the repeating data
• The primary key of the new table (repeating group) is always a composite key; Usually document number and a field uniquely describing the repeating line, like an item number.
First Normal Form Example
The new table is as follows:
SalesOrderNo, ItemNo, Description, Qty, UnitPrice
The repeating fields will be removed from the original data table, leaving the following.
SalesOrderNo, Date, CustomerNo, CustomerName, CustomerAdd, ClerkNo, ClerkName
These two tables are a database in first normal form


Second Normal Form Example
The new table will contain the following fields:
ItemNo, Description
All of these fields except the primary key will be removed from the original table. The primary key will be left in the original table to allow linking of data:
SalesOrderNo, ItemNo, Qty, UnitPrice


Completed Tables in Third Normal Form
Customers: CustomerNo, CustomerName, CustomerAdd
Clerks: ClerkNo, ClerkName
Inventory Items: ItemNo, Description
Sales Orders: SalesOrderNo, Date, CustomerNo, ClerkNo
SalesOrderDetail: SalesOrderNo, ItemNo, Qty, UnitPrice