Database Normalization

1. Overview
2. Anomaly
3. Normalization

•   First Normal Form (1NF)
•  Second Normal Form (2NF)
•  Third Normal Form (3NF)
• Boyce Codd Normal Form (BCNF)

1. Overview

            What is problem with that table?

The problem with above table is: Redundancy Data
And Data Redundancy will produce Anomaly
 
   2. What is Anomaly?
   Tables that have redundant data have problems known as anomalies. So data redundancy is cause of an anomaly.
   Redundancy is the duplication of the data.
   Type of anomalies
   There are 3 types of anomalies

•     Insert Anomaly
•     Delete Anomaly
•     Update Anomaly

   Insert Anomaly
   An insert anomaly occurs when certain attribute cannot be inserted into the database without the presence of other attribute.

Delete Anomaly
   A delete anomaly exists when certain attributes are lost because of deletion of other attributes.

Update Anomaly
    An update anomaly exist when one or more instances of duplicated data is updated, but not all.

3.Normalization
  This is database design technique which organizes table in manner that reduces redundancy and dependency of data (That means avoid anomaly).
  Normalization is 'standardized' rule for make database.

First Normal Form (1NF)
 First Normal Form: No Repeating Elements or Groups of Elements

• A row of data cannot contain repeating groups of similar data; and
• Each row of data must have a unique identifier (or Primary Key).

Second Normal Form (2NF)
All requirements for 1NF must be met.
Redundant data across multiple rows of a table must be moved to a separated table.

Third Normal Form (3NF)

All requirements for 2NF must be met.

Eliminate fields that do not depend on the primary key.

Boyce Codd normal form (BCNF)
It is an advance version of 3NF that’s why it is also referred as 3.5NF. BCNF is stricter than 3NF. A table complies with BCNF if it is in 3NF and for every functional dependency X->Y, X should be the super key of the table.

Example: Suppose there is a company wherein employees work in more than one department.
They store the data like this: