Normalization in DBMS
In DBMS, database normalization is a process of making the database consistent by
 Reducing the redundancies
 Ensuring the integrity of data through lossless decomposition
Normalization is done through normal forms.
Normal Forms
The standard normal forms used are
 First Normal Form (1NF)
 Second Normal Form (2NF)
 Third Normal Form (3NF)
 BoyceCodd Normal Form (BCNF)
There exists several other normal forms even after BCNF but generally we normalize till BCNF only.
First Normal Form
A given relation is called in First Normal Form (1NF) if each cell of the table contains only an atomic value.
OR
A given relation is called in First Normal Form (1NF) if the attribute of every tuple is either single valued or a null value.
Example
The following relation is not in 1NF
Student_id  Name  Subjects 
100  Akshay  Computer Networks, Designing 
101  Aman  Database Management System 
102  Anjali  Automata, Compiler Design 
Relation is not in 1NF
However,
 This relation can be brought into 1NF.
 This can be done by rewriting the relation such that each cell of the table contains only one value.
Student_id  Name  Subjects 
100  Akshay  Computer Networks 
100  Akshay  Designing 
101  Aman  Database Management System 
102  Anjali  Automata 
102  Anjali  Compiler Design 
Relation is in 1NF
This relation is in First Normal Form (1NF).
NOTE
 By default, every relation is in 1NF.
 This is because formal definition of a relation states that value of all the attributes must be atomic.
Second Normal Form
A given relation is called in Second Normal Form (2NF) if and only if
 Relation already exists in 1NF.
 No partial dependency exists in the relation.
Also read Functional Dependency in DBMS
Partial Dependency
A partial dependency is a dependency where few attributes of the candidate key determines nonprime attribute(s). OR A partial dependency is a dependency where a portion of the candidate key or incomplete candidate key determines nonprime attribute(s).
In other words, A → B is called a partial dependency if and only if
If any one condition fails, then it will not be a partial dependency.
NOTE

Example
Consider a relation R ( V , W , X , Y , Z ) with functional dependencies
VW → XY
Y → V
WX → YZ
The possible candidate keys for this relation are
VW , WX , WY
From here,
 Prime attributes = { V , W , X , Y }
 Nonprime attributes = { Z }
Now, if we observe the given dependencies
 There is no partial dependency.
 This is because there exists no dependency where incomplete candidate key determines any nonprime attribute.
Thus, we conclude that the given relation is in 2NF.
Third Normal Form
A given relation is called in Third Normal Form (3NF) if and only if
 Relation already exists in 2NF.
 No transitive dependency exists for nonprime attributes.
Transitive Dependency
A → B is called a transitive dependency if and only if
If any one condition fails, then it is not a transitive dependency.
NOTE

OR
A relation is called in Third Normal Form (3NF) if and only if
Any one condition holds for each nontrivial functional dependency A → B
 A is a super key
 B is a prime attribute
Example
Consider a relation R ( A , B , C , D , E ) with functional dependencies
A → BC
CD → E
B → D
E → A
The possible candidate keys for this relation are
A , E , CD , BC
From here,
 Prime attributes = { A , B , C , D , E }
 There are no nonprime attributes
Now,
 It is clear that there are no nonprime attributes in the relation.
 In other words, all the attributes of relation are prime attributes.
 Thus, all the attributes on RHS of each functional dependency are prime attributes.
Thus, we conclude that the given relation is in 3NF.
BoyceCodd Normal Form
A given relation is called in BCNF if and only if
 Relation already exists in 3NF.
 For each nontrivial functional dependency A → B, A is a super key of the relation.
Example
Consider a relation R ( A , B , C ) with the functional dependencies
A → B
B → C
C → A
The possible candidate keys for this relation are
A , B , C
Now, we can observe that RHS of each given functional dependency is a candidate key.
Thus, we conclude that the given relation is in BCNF.
Next Article Important Points About Normal Forms
Get more notes and other study material of Database Management System (DBMS).
Watch video lectures by visiting our YouTube channel LearnVidFun.