Normalisation Rules with Implementations

Normalisation is a fundamental concept of Database Design.I have saw peoples working with database from 5 years but still have some doubt about its implementations.

Here we will see what is normalisation and how it will helpful in database desinging.

Database normalization:

It is the process of organizing the fields and tables of a relational database to minimize redundancy and dependency.
Normalization usually involves dividing large tables into smaller (and less redundant) tables and defining relationships between them.
The objective is to isolate data so that additions, deletions, and modifications of a field can be made in just one table and then propagated through the rest of the database using the defined relationships.

Edgar F. Codd, the inventor of the relational model, introduced the concept of normalization

So It is mainly used for two purpose -

• Eliminate Reduntant(Useless) Data.
• Ensuring that data dependance make sense.

Normalization is divided into four forms -

• First Normal Form
• Second Normal Form
• Third Normal Form
• BCNF (Boyce and Codd Normal Form)

First Normal Form -

• A data row can not contain repeated group of data. 
• 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.


Second Normal Form-

• It should follow first normal form.
• There must not be any partial dependency of any column on prmary key.

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.

Third Normal Form -

• Table must follow Second Normal Form.
• Every Non Prime Attribute must be dependent on Primary Key.
• Transitive functional dependancy should be removed from table.

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.

Boyce and Codd Normal Form-

When a relation has more than one candidate key, anomalies may result even though the relation is in 3NF.  

A relation is in BCNF is, and only if, every determinant is a candidate key.

 A table is in BCNF if every determinant is a candidate key. A table can be in 3NF but  not in BCNF. This occurs when a non key attribute is a determinant of a key attribute.