Normalization in DBMS: 1NF, 2NF and 3NF

 

Normalization:

 Normalization is a process of decomposing the relational schemas and breaking them into smaller relational schemas that possesses desirable properties.

1.    First Normal Form (1NF):

 

The definition of 1NF says that all the attributes in a relation must be atomic i.e., simple and indivisible values from their respective domains. Multivalued attributes, composite attributes, nested relations are split such that they can be made atomic. Nested relations are the relations within relations.

The above relation contains a relation ‘Project’ which must be split into two relations as:

 

Emp (EmpNo, EmpName, EmpCity)

EmpProject (EmpNo, ProjectNo, ProjectName, TimeAllotted)

 

where, primary key EmpNo  of Emp  is combined in the primary key of EmpProject as {EmpNo, ProjectNo}.

 

Multivalued attributes:

 

Here, Qty_Per_Pack is a multivalued attribute. It should be split as;

 

Drug (DrugNo, Name, Rate, Tablet_Syrup, Unit, Packing)

Drug_Pack (DrugNo, Qty_Per_Pack)

 

2.   Second Normal Form (2NF):

 

Relation is said to be in 2NF if it is in 1NF and every non key attribute of the relation is fully functionally dependent on the key attribute. By fully functional dependency, we mean that if any attribute from the key attributes is removed, the dependency is not preserved.

 

Consider 1NF of ex:

 

Emp (EmpNo, EmpName, EmpCity)

EmpProject (EmpNo, ProjectNo, ProjectName, TimeAllotted)

 

Observe that EmpNo + ProjectNo is a key in EmpProject.

 

It means that (EmpNo, ProjectNo) → ProjectName

 

                         (EmpNo, ProjectNo) → Hours

 

should hold as fully functional dependency.

 

In case of (EmpNo, ProjectNo)→ TimeAllotted

 

if we remove any attributes from the key,

 

EmpNo → TimeAllotted or

ProjectNo → TimeAllotted

 

the functional dependency holds. But, in case of

(EmpNo, ProjectNo) → ProjectName,

observe that ProjectNo→ ProjectName  holds

but EmpNo → ProjectName  does not hold.

 

So, ProjectName is partially dependent on the key. In transformation of 2NF, such dependencies are split to form a new relation. The 2NF of above will be;

 

Project (ProjectNo, ProjectName)

Emp_Project (EmpNo, ProjectNo, TimeAllotted)

 

In this way, the partial dependencies, if any, are removed in the transformation from 1NF to 2NF.

 

 

3.   Third Normal Form (3NF):

 

Third normal form is applied when the relations are in 2NF and there is any non-key attribute that depends transitively on the primary key.

 

The attributes are said to be transitively dependent if PK → A and A → B.

 

An attribute A is dependent on the attribute PK (primary key) and B is dependent on A, implies that B is transitively dependent on PK => PK → B.

Here, EmpNo → (EmpName, AreaMarket, Vehicle, Vehicle_Allowance)

But, EmpNo → Vehicle

And Vehicle → Vehicle_Allowance

So, EmpNo → Vehicle_Allowance

 

That means Vehicle_Allowance  is transitively dependent on Emp_No. To transform the relations to 3NF, the transitive dependencies if any, are removed by decomposing the relations and referring them by a primary key attribute of new relation. The 3NF will be:

 

Emp (EmpNo, EmpName, AreaMarket, Vehicle_No)

Vehicle (Vehicle_No, Vehicle_Name, Allowance)