Database Normalization is a technique of designing the database in structured way to reduce redundancy and to improve data integrity.

Purpose of Database Normalization:

• Eliminate the redundant data
• Reduce the complexity of the data
• Ensure the relationship between tables as well as data in the tables
• Ensure data dependencies.

In this tutorial, you will learn-

How to identify 1NF,2NF,3NF and BCNF.

1NF (First Normal Form) Rules

• Each table cell should contain a single value.
• Value of each column must belongs to same domain.
• Primary key must be able to determine all attribute.
• Every column should have unique name.

How to identify a relation is in 2NF (Second Normal Form)?

• Be in 1NF
• Full part of the LHS(left hand side) of a functional dependency should be candidate key, there should not be any partial dependency.
• If LHS is not candidate key then RHS(Right hand side) of that functional dependency should be prime.
• If LHS and RHS both are non prime then no problem in 2NF.

What are transitive functional dependencies?

A functional dependency is said to be transitive if it is indirectly formed by two functional dependencies. For e.g.

X -> Z is a transitive dependency if the following three functional dependencies hold true:

• X->Y
• Y does not ->X
• Y->Z

Note: A transitive dependency can only occur in a relation of three of more attributes. This dependency helps us normalizing the database in 3NF (3^rd Normal Form).

2NF (Second Normal Form): How to identify a relation is in 2NF ?

• Be in 2NF
• For each functional dependency
  LHS must be candidate key or super key
  OR
  RHS is a prime attribute.
• ** LHS and RHS both non prime is not allowed in 3NF.(must be noted)

Boyce-Codd Normal Form (BCNF)

Even when a database is in 3^rd Normal Form, still there would be anomalies resulted if it has more than one Candidate Key.

Sometimes is BCNF is also referred as 3.5 Normal Form.

How to identify a relation is in Boyce-Codd Normal Form(BCNF)?

• Be in 3NF
• LHS of every functional dependency should be super key or candidate key.
• RHS prime condition is not allowed here.