LET’S START WITH DBMS :)
Normalisation and its types
Normalisation
Normalization is a process in which we organize data to reduce
redundancy(duplicacy) and improve data consistency. It involves dividing a database
into two or more tables
What is data redundancy and consistency and why its important
When there is same set of data repeated each and every time it results in
duplicacy of data (either in row or column)
Now row level duplicacy can be remove by using primary key for unique
values.
LET’S START WITH DBMS :)
Normalisation and its types
Now when we have same data for some set of columns , it leads to different
anomalies (inconsistencies or errors that occur when manipulating or
querying data in a database)
1. Insertion Anomaly
2. Updation Anomaly
3. Deletion Anomaly
Also it also increases the size of database with the same data.
LET’S START WITH DBMS :)
Normalisation and its types
Insertion Anomaly: Employee
id name age department Manager salary
It occurs when it is difficult to insert 1 Rahul 25 'IT' Raj 1500
data into the database due to the 2 Afsara 26 'HR' Avinash 1000
absence of other required data. 3 Abhimanyu 27 'IT' Raj 1500
4 Aditya 25 'HR' Avinash 1000
Consider you want to add a new
5 Raj 24 'HR' Avinash 1000
department but there is no employee in
that dept yet.
let's say all the employees in IT
department left the company then if we
LET’S START WITH DBMS :) delete all the records of all the employees
of IT department then we will also loose
Normalisation and its types the data that who is the manager and
what is the salary of the IT Department.
Deletion Anomaly: Employee
id name age department Manager salary
It occurs when deleting data removes 1 Rahul 25 'IT' Raj 1500
other valuable data. 2 Afsara 26 'HR' Avinash 1000
3 Abhimanyu 27 'IT' Raj 1500
Consider if you delete all the record in 4 Aditya 25 'HR' Avinash 1000
the table, you will loose the track of
5 Raj 24 'HR' Avinash 1000
dept, their manager and salaries.
This is because we don't have the department and
salary data stored some where else therefore this
causes deletion anomaly.
LET’S START WITH DBMS :)
Normalisation and its types
Updation Anomaly: Employee
id name age department Manager salary
It occurs when changes to data require 1 Rahul 25 'IT' Raj 1500
multiple updates 2 Afsara 26 'HR' Avinash 1000
3 Abhimanyu 27 'IT' Raj 1500
Consider you want to change the salary 4 Aditya 25 'HR' Avinash 1000
for people working in HR department,
5 Raj 24 'HR' Avinash 1000
you need to update it at 3 place .
LET’S START WITH DBMS :)
Normalisation and its types
How normalisation helps here?
Using normalisation we can divide the employee table in two tables
1. Employee
2. Department
Employee Department
id name age department department Manager salary
1 Rahul 25 IT IT Raj 1500
2 Afsara 26 HR HR Avinash 1000
Abhimany
3 27 IT
u
4 Aditya 25 HR
5 Raj 24 HR
LET’S START WITH DBMS :)
Normalisation and its types
Types of Normalisation
First Normal Form (1NF)
Second Normal Form (2NF)
Third Normal Form (3NF)
Boyce-Codd Normal Form(BCNF)