What is Normalization?
Normalization is the process of efficiently organizing data in a database.
There are two goals of the normalization process:
eliminating redundant data (for example, storing the same data in more than one table) and
ensuring data dependencies make sense (only storing related data in a table).
Both of these are worthy goals as they reduce the amount of space a database consumes and ensure that data is logically stored.
First Normal Form (1NF) sets the very basic rules for an organized database:
Eliminate duplicative columns from the same table.
Create separate tables for each group of related data and identify each row with a unique column (the primary key).
Example - A table within a human resources database that stores the manager-subordinate relationship
-
Manager
Subordinate1
Subordinate2
Subordinate3
Subordinate4
Normalized to 1NF:
-
ManagerID
SubordinateID
General requirements of Second Normal Form (2NF) :
Meet all the requirements of the first normal form.
Remove subsets of data that apply to multiple rows of a table and place them in separate tables.
Create relationships between these new tables and their predecessors through the use of foreign keys.
Example - A table which stores customer information in a database
| CustNum | FirstName | LastName | Address | City | State | ZIP |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Normalized to 2NF :
-
CustNum
FirstName
LastName
Address
ZIP
-
ZIP
City
State
Normalized to 3NF :
-
Order Number
Customer Number
Unit Price
Quantity
SELECT OrderNumber, UnitPrice * Quantity AS Total
FROM WidgetOrders
