Contents
show
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.
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.
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
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_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.
AS given below
EMP_ZIPCODE
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.”