Prior to Normalization
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)
This table is now in first normal form.
Second Normal Form (2NF)
The concept of remove the delicacy of data comes in the Second Normal Form (2NF).
Let's introduce a Review table as an example :
Table is not in Second Normal Form because the price and tax depends on
the item, but not color.
Tables are now in Second Normal Form.
Third Normal Form (1NF)
The Third Normal Form has one more additional requirement :
A. It should meet all the requirements of the second normal form.
B. 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, table break the rule and turns into the dependency on the primary key.
Tables are not in Second Normal Form because tax depends on price, not item.
Tables are now in Third Normal Form with tables
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 |
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 |
Second Normal Form (2NF)
The concept of remove the delicacy of data comes in the Second Normal Form (2NF).
A. It should meet all the requirements of the first normal form.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.
B. It should remove subsets of data that apply to multiple rows of a table and place them in separate tables.
C. It create relationships between these new tables and their predecessors through the use of foreign keys.
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 |
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 |
Third Normal Form (1NF)
The Third Normal Form has one more additional requirement :
A. It should meet all the requirements of the second normal form.
B. 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, table break the rule and turns 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.
Tables are now in Third Normal Form with tables
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 |
No comments:
Post a Comment