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 MinimizationPerformance 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 valuesin 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 belowEMP_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_ZIPEMP 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 tableEMP 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 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.
