SQL Server – Normalization Forms With Examples

Normalization Forms with Examples
According to the database rules, below are the lists of Normalization forms
1.    1NF (Eliminating Repeating Groups)
•    Make a separate table for each set of related attributes and give each table a primary key. In simple words I can say it as
•    There are no repeating or duplicate fields.
•    Each cell contains only a single value.
•      Each record is unique and identified by primary key
Let’s see the example below :
Prior to Normalization
Item    Colors         Price    Tax
Pen       red, blue        2.0        0.20
Scale     red, yellow     2.0       0.20
Pen        red, blue       2.0        0.20
Bag      blue, black    150.00    7.80
This table is not in first normal form because:
•      A. There are multiple fields in color lab.
•      B. Records are repeating (Duplicate records) or no primary key.
First Normal Form (1NF)
Item    Colors    Price    Tax
Pen         red         2.0          0.20
Pen        blue        2.0         0.20
Scale       red       2.0         0.20
Scale      yellow    2.0        0.20
Bag        blue      150.00    7.80
Bag       black      150.00    7.80
•    This table is now in first normal form.
2.    2 NF (Eliminating Redundant Data)
•    If an attribute is dependent on only part of the multivalued key, then remove it to a separate table. In simple words,
•    It should meet all the requirements of the first normal form.
.  It should remove subsets of data that apply to multiple rows of a table and place them in separate tables.
.  It create relationships between these new tables and their predecessors through the use of foreign keys.
The First Normal form deals with the atomicity whereas the Second Normal Form deals with the relationship between the composite key columns and non-key columns. To achieve the next progressive level your table should satisfy the requirement of First Normal Form then move towards the Second Normal Form.
Let’s introduce a Review table as an example :
Item    Colors    Price    Tax
Pen        red           2.0        0.20
Pen        blue          2.0        0.20
Scale       red         2.0         0.20
Scale       yellow    2.0        0.20
Bag         blue       150.00    7.80
Bag        black    150.00    7.80
Table is not in Second Normal Form because the price and tax depends on the item, but not color.
Item    Colors
Pen    red
Pen    blue
Scale    red
Scale    yellow
Bag    blue
Bag    black
Item    Price    Tax
Pen        2.0         0.20
Scale      2.0        0.20
Bag     150.00     7.80
Tables are now in Second Normal Form.
3.    3NF (eliminate Columns not dependent on the Key)
•    If attributes do not contribute description of the key, then remove it to the separate table. All the attributes must be directly dependent on the primary key. In simple words,
•    It should meet all the requirements of the second normal form.
•     It should remove columns that are not dependent upon the primary key.
In the Third Normal Form all columns depend upon the primary key. When one column depends upon the other column, tables break the rule and turn into the dependency on the primary key.


Item    Colors
Pen        red
Pen      blue
Scale    red
Scale    yellow
Bag      blue
Bag      black
Item    Price    Tax
Pen        2.0    0.20
Scale       2.0    0.20
Bag       150.00    7.80
Tables are not in Second Normal Form because tax depends on price, not item.
Item      Colors
Pen         red
Pen         blue
Scale      red
Scale      yellow
Bag       blue
Bag       black
Item       Price
Pen          2.0
Scale       2.0
Bag          150.00
Price       Tax
2.0           0.20
150.00     7.80
Tables are now in Third Normal Form.