Normalization in Dbms and Different normal forms in dbms

Normalization in dbms with example

What is database normalization ?
 

What are benefits of normalization in dbms?

What are different types of normal forms in dbms?

So Let’s start with normalization introduction.

What is Database Normalization?

Normalization makes database design simpler. It is also known as database normalization or data normalization. Database Normalizationis an important part of database design. It helps in the speed, accuracy of the database. Database Normalization is a technique used to design a relational database.
 
Normalization occurs in two processes, which removes the repeating groups from the data in the form of a table, and then removes duplicate entries of the same from the relational table.
 
The work of Database Normalization is to reduce redundancy and reducing redundancy means to store information at once. Storing the same information more than once can increase the storage. Relative Normalized means that whenever the relation is altered in the database, the information should not be lost.
 
By normalizing the database, you can arrange the data into tables and columns. You make sure that each table has only related data. If the data is not directly related, then you create a new table for that data.
                                       
different normal forms



Example of Database Normalization   

If you have a “Customer” table, then you can usually create a separate table for those products that they can order (you can call this table “Product”). 
 
You will prepare another table for customer orders (which is called “order”). And if there are many items in each order, you usually create another table (perhaps called “order list”) to store each order item. All these tables will be linked to their primary key, which allows you to find related data in all these tables (such as all orders by a given customer).

Benefits of Database Normalization

These are the following benefits of database normalization. 
 
       Redundancy Minimization

      Performance Improvement

           Query Optimization

           Minimizing Insertion, Deletion, Modification Anomalies

Types of Normal Forms

Database normalization process is divided into the normal forms
In this section we have explained step by step normalization with example.
1. First normal form (1NF)
 
First Normal Form also represents 1NF, in a relational table 1NF, when all the values  in the column are atomic. That means it does not contain repeating values. We can understand it in such a way that a table is in 1 NF if- There should be no duplicate rows in the table. Every single cell should have single values. Entries should be of the same type in the column.

Here first normal form in dbms with example is explained.

 Consider the following table.

Professor
Subject Code
Dr. L.S.Maurya
(NCS701,NCS 702)
Dr.Amit Srivatsrava
NCS302

 

Here in this table issue is that here we are storing two courses against Dr. L.S.Maurya. This is not the optimal way.This table is not in 1 NF. Better way is that we have to store each subject separately as shown in following table.
 
Professor
Subject Code
Dr. L.S.Maurya
NCS701
Dr. L.S. Maurya
NCS702
Dr.Amit Srivatsrava
NCS302
This table also store the unique information there is no repetition so this table is in 1 NF.
2. Second normal form (2NF)
 
A table or relation is then in 2nd normal form when it meets all the needs of 1st normal form and all non key attributes will depend entirely on the primary key.

Here 2nd normal form in dbms with example is explained.

Consider the following PROFESSOR table
 
Professor ID
Subject Code
Age
P1
NCS701
50
P1
NCS702
50
P3
NCS302
45
Since a teacher can teach more than one subject so In this table candidate key is the combination of teacher id and subject code. But teacher age is the non prime attribute which is dependent on the professor Id. Which is a part of candidate key so her is partial Dependency which is not allow in 2 NF. So we need to decompose this table in order to convert it in to 2 NF. This decomposition is as follows:

PROFESSOR_SUBJECT
Professor ID
Subject Code
P1
NCS701
P1
NCS702
P3
NCS302

PROFESSOR_AGE

Professor ID
Age
P1
50
P1
50
P3
45

Now this decomposition of Professor table into two above table is in 2 NF.

3. Third normal form (3NF)
 
A table or relation is then in 3rd normal form when it meets all the requirements of 2nd normal form and there should not be any transitive function dependency.

Consider the following EMP table to understand the concept of 3nf in dbms with example.

EMP ID
EMP NAME
EMP CITY
EMP STATE
ZIP CODE
E1
Saurabh Giri
Varansi
U.P
123456
E2
Pawan Rana
Agra
U.P
789101
E3
Kunal
Dedhradoon
U.P
121314
E4
Ravi
Ghaziabd
U.P
141516
E5
Hemant
Noida
U.P
161718
Candidate key of this table is Emp Id. All other attribute are non prime attributes.
Since in this table there is a transitivity dependency. Because Non prime attribute EMP CITY , EMP STATE can be determined by other non prime attribute zip code. This is not allowed in 3nf .

So in order to convert this table in 3 NF we have to decompose it

AS given below

EMP_ZIPCODE
 
EMP ID
EMP NAME
ZIP CODE
E1
Saurabh Giri
123456
E2
Pawan Rana
789101
E3
Kunal
121314
E4
Ravi
141516
E5
Hemant
161718
EMP_CITY_ZIP
EMP CITY
EMP STATE
ZIP CODE
Varansi
U.P
123456
Agra
U.P
789101
Dedhradoon
U.P
121314
Ghaziabd
U.P
141516
Noida
U.P
161718
This decomposition is now in 3 NF.

4. Boyce-Codd Normal Form (BCNF)

 
A table or relation is then in 3rd normal form and all tables in the database should have only one primary key.           

Consider the following table

EMP ID
EMP COUNTRY
EMP DEPT
DEPT TYPE
DEPT NUMBER
E1
INDIA
CSE
Teaching
D1
E2
INDIA
ME
Teaching
D2
E3
USA
HR
Non Teaching
D3
E4
USA
Placement
Non Teaching
D4
Here in this table candidate key is { EMP ID ,EMP DEPT}. This table is not in BCNF because neither EMP DEPT nor EMP ID is alone the key.


So to convert this table we need to decompose it in to three tables.

EMP COUNTRY TABLE.

EMP ID
EMP COUNTRY
E1
INDIA
E2
INDIA
E3
USA
E4
USA
EMP DEPT Table.

EMP DEPT
DEPT TYPE
DEPT NUMBER
CSE
Teaching
D1
ME
Teaching
D2
HR
Non Teaching
D3
Placement
Non Teaching
D4
EMP Dept Mapping table.

EMP ID
DEPT NUMBER
E1
D1
E2
D2
E3
D3
E4
D4
Now this decomposition is in BCNF.
 

5. Fourth Normal Form (4NF)

A relation or table is in 4NF when it satisfies the following condition “A relation or table is in 4NF if it is in 3 normal form (3NF) and does not have any multivalued dependencies.”

What is Multivalued dependency?

“Multivalued dependency occurs when one table contains multiple independent (multiple) multivalued attributes.”

6. Fifth Normal Form (5NF)

A relation or table is in 5NF when it satisfies the following condition “A table or relation is then in 5NF when it is in 4NF and there is no non-loss decomposition in the table.”

Leave a Comment

x