Normalization in DBMS | Normal Forms

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-

 

 

  1. First Normal Form (1NF)
  2. Second Normal Form (2NF)
  3. Third Normal Form (3NF)
  4. Boyce-Codd 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_idNameSubjects
100AkshayComputer Networks, Designing
101AmanDatabase Management System
102AnjaliAutomata, 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_idNameSubjects
100AkshayComputer Networks
100AkshayDesigning
101AmanDatabase Management System
102AnjaliAutomata
102AnjaliCompiler 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-

  1. Relation already exists in 1NF.
  2. 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 non-prime attribute(s).

OR

A partial dependency is a dependency where a portion of the candidate key or incomplete candidate key determines non-prime attribute(s).

 

In other words,

A → B is called a partial dependency if and only if-

  1. A is a subset of some candidate key
  2. B is a non-prime attribute.

If any one condition fails, then it will not be a partial dependency.

 

NOTE-

 

  • To avoid partial dependency, incomplete candidate key must not determine any non-prime attribute.
  • However, incomplete candidate key can determine prime attributes.

 

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 }
  • Non-prime 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 non-prime 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-

  1. Relation already exists in 2NF.
  2. No transitive dependency exists for non-prime attributes.

 

Transitive Dependency

 

A → B is called a transitive dependency if and only if-

  1. A is not a super key.
  2. B is a non-prime attribute.

If any one condition fails, then it is not a transitive dependency.

 

NOTE-

 

  • Transitive dependency must not exist for non-prime attributes.
  • However, transitive dependency can exist for prime attributes.

 

OR

 

A relation is called in Third Normal Form (3NF) if and only if-

Any one condition holds for each non-trivial functional dependency A → B

  1. A is a super key
  2. 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 non-prime attributes

 

Now,

  • It is clear that there are no non-prime 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.

 

Boyce-Codd Normal Form-

 

A given relation is called in BCNF if and only if-

  1. Relation already exists in 3NF.
  2. For each non-trivial 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.

Summary
Normalization in DBMS | Normal Forms
Article Name
Normalization in DBMS | Normal Forms
Description
Normalization in DBMS is a process of making database consistent. Normal Forms in DBMS- First Normal Form (1NF), Second Normal Form (2NF), Third Normal Form (3NF), Boyce Codd Normal Form (BCNF).
Author
Publisher Name
Gate Vidyalay
Publisher Logo
Liked this article? Share it with your friends and classmates now-