Normalization in SQL Server

Normalization 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:

1. There are multiple fields in color lab.
2. 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 creates 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