Normalization
Rubaiya Hafiz
Senior Lecturer
Daffodil International University
Normalization
• Database Normalization is a technique of organizing the data in the
database. Normalization is a systematic approach of decomposing
tables to eliminate data redundancy and undesirable characteristics
like Insertion, Update and Deletion anomalies. It is a multi-step
process that puts data into tabular form by removing duplicated data
from the relation tables.
• Normalization is used for mainly two purpose,
– Eliminating redundant(useless) data.
– Ensuring data dependencies make sense i.e data is logically stored.
Redundant data
SID Name Age CourseCode SGPA CustomerName Product1 Product2 Product3
1 Raju 17 CSE112 3.50 Rafi Basketball Playstation
1 Raju 17 CSE213 3.80 Raju Football Mobile watch
2 Meena 18 CSE333 4
3 Mithu 19 CSE112 3.80
3 Mithu 19 ECO314 3.25
Row Level Column Level
Normalization Anomalies
If a database design is not perfect, it may contain anomalies, which are like a bad dream
for any database administrator. Managing a database with anomalies is next to
impossible.
• Update anomalies − If data items are scattered and are not linked to each other
properly, then it could lead to strange situations. For example, when we try to update
one data item having its copies scattered over several places, a few instances get
updated properly while a few others are left with old values. Such instances leave the
database in an inconsistent state.
• Deletion anomalies − We tried to delete a record, but parts of it was left undeleted
because of unawareness, the data is also saved somewhere else.
• Insert anomalies − We tried to insert data in a record that does not exist at all.
Normalization is a method to remove all these anomalies and bring the database to a
consistent state.
Anomalies
Update anomalies − For example the room_size of H940 is
now 500.
Deletion anomalies : If we want to delete Course_no 351, then
the detail of room number C320 will also lost.
Insert anomalies : there is a problem when we want to insert a
room H856.
Types of Normalization
• 1st Normal Form (1NF)
• 2nd Normal Form (2NF)
• 3rd Normal Form (3NF)
• Boyce Code Normal Form (BCNF)
• 4th Normal Form (4NF)
1NF
For a table to be in the First Normal Form, it should follow the following 4 rules:
1. It should only have single(atomic) valued attributes/columns.
2. Values stored in a column should be of the same domain
3. All the columns in a table should have unique names.
4. And the order in which data is stored, does not matter.
Example 1:
CustomerName Contact Here, Values stored in
Rafi 12 Shukrabad, Dhaka rafi@[Link] Contact column contains
both address and email.
Raju Road no 4, Dhanmondi, Dhaka raju@[Link] (violates rule 2)
CustomerName Address Email
Rafi 12 Shukrabad, Dhaka rafi@[Link]
Raju Road no 4, Dhanmondi, raju@[Link]
Dhaka
1NF
• Example 2: • Example 3:
CustomerName ProductOrder CustomerN Product1 Product2 Product3
ame
Rafi Basketball, Playstation
Rafi Basketball Playstation
Raju Football, Mobile, watch
Raju Football Mobile watch
CustomerName ProductOrder
Rafi Basketball All the columns in a table should
have unique names.
Rafi Playstation
Raju Football
Raju Mobile
We will discuss about it later.
Raju watch
ProductOrder does not have
single(atomic) valued
attributes/columns.
For a table to be in the Second Normal Form,
2NF • It should be in the First Normal form.
• And, it should not have Partial Dependency. CustomerName Address
We have to find Composite key / Partial dependence
Rafi 12 Shukrabad, Dhaka
Raju 4 Dhanmondi, Dhaka
CustomerName Address Product Quantit Date
y Hena Adabor 2, dhaka
Rafi 12 Shukrabad, Basketb 2 1-11-2019 Hakim Mirpur 1, Dhaka
Dhaka all
Raju 4 Dhanmondi, Football 1 11-3-2018
Dhaka
Custome Product Date Quantity
Rafi 12 Shukrabad, Watch 1 18-7-2018 rName
Dhaka Rafi Basketball 12-11-2019 2
Hena Adabor 2, Mobile 2 2-11-2017
dhaka Raju Football 11-3-2018 1
Hakim Mirpur 1, Football 1 7-4-2017
Dhaka
Rafi Watch 18-7-2018 1
Hena Mobile 12-11-2017 2
Hakim Football 7-4-2017 1
3 NF
• Table must be in 2NF
• Transitive functional dependency of non-prime attribute on any super key should be removed.
Example
Consider This given Table
DepartmentStudent
DNo DName Address PhnNo DeptHead DHemail Student
D1 CSE Add1 012 Head1 Head1@[Link] Anik, Anu
D2 EEE Add2 013 Head2 Head2@[Link] Antora, Aditi, Raju
D3 ETE Add3 014 Head3 Head3@[Link] Meena
D4 NFE Add4 015 Head4 Head4@[Link] Deepa, Mithu, Ani
D5 MCT Add5 016 Head5 Head5@[Link] Kakon, Apurbo
D6 SWE Add6 017 Head6 Head6@[Link] Koli, Hasan
D7 Civil Add7 018 Head7 Head7@[Link] Helal, Hamid, Sumi
Now we will try to normalize this table:
DepartmentStudent
DNo DName Address PhnNo DeptHead DHemail Student
D1 CSE Add1 012 Head1 Head1@[Link] Anik, Anu
D2 EEE Add2 013 Head2 Head2@[Link] Antora, Aditi, Raju
D3 ETE Add3 014 Head3 Head3@[Link] Meena
D4 NFE Add4 015 Head4 Head4@[Link] Deepa, Mithu, Ani
D5 MCT Add5 016 Head5 Head5@[Link] Kakon, Apurbo
D6 SWE Add6 017 Head6 Head6@[Link] Koli, Hasan
D7 Civil Add7 018 Head7 Head7@[Link] Helal, Hamid, Sumi
Single valued Attribute: Dno, Dname, Address, PhnNo, DeptHead, DHemail
Multivalued Attribute: Student
PrimaryKey: DNo
1 NF
1. Every cell must contain atomic value.
2. There must be a key (primary, composite) that uniquely identifies each row.
3. There can not have multiple columns that contains similar information.
DepartmentStudent
DNo DName Address PhnNo DeptHead DHemail Student1 Student2 Student3
D1 CSE Add1 012 Head1 Head1@[Link] Anik Anu
D2 EEE Add2 013 Head2 Head2@[Link] Antora Aditi Raju
D3 ETE Add3 014 Head3 Head3@[Link] Meena
D4 NFE Add4 015 Head4 Head4@[Link] Deepa Mithu Ani
D5 MCT Add5 016 Head5 Head5@[Link] Kakon Apurbo
D6 SWE Add6 017 Head6 Head6@[Link] Koli Hasan
D7 Civil Add7 018 Head7 Head7@[Link] Helal Hamid Sumi
Student
1 NF
DepartmentStudent
DNo
D1
SID
S1
SNam
e
Anik
City
Dhaka
Zip
1200
DNo DName Address PhnNo DeptHead DHemail Student1 Student Student3
2 D1 S2 Anu Rangpur 1400
D1 CSE Add1 012 Head1 Head1@[Link] Anik Anu D2 S3 Antora Dhaka 1200
D2 EEE Add2 013 Head2 Head2@[Link] Antora Aditi Raju
D2 S4 Aditi Sylhet 1500
D3 ETE Add3 014 Head3 Head3@[Link] Meena
D4 NFE Add4 015 Head4 Head4@[Link] Deepa Mithu Ani D2 S5 Raju Sylhet 1500
D5 MCT Add5 016 Head5 Head5@[Link] Kakon Anu D3 S6 Meena Rangpur 1400
D6 SWE Add6 017 Head6 Head6@[Link] Koli
D4 S7 Deepa Chittagong 1300
D7 Civil Add7 018 Head7 Head7@[Link] Helal Hamid Sumi
D4 S8 Mithu Rangpur 1400
Department
DNo DName Address PhnNo DeptHead DHemail D4 S9 Ani Barishal 1100
D1 CSE Add1 012 Head1 Head1@[Link]
D5 S10 Kakon Chittagong 1300
D2 EEE Add2 013 Head2 Head2@[Link]
D5 S11 Anu Dhaka 1200
D3 ETE Add3 014 Head3 Head3@[Link]
D6 S12 Koli Barishal 1100
D4 NFE Add4 015 Head4 Head4@[Link]
D5 MCT Add5 016 Head5 Head5@[Link] D7 S13 Helal Chittagong 1300
D6 SWE Add6 017 Head6 Head6@[Link] D7 S14 Hamid Dhaka 1200
D7 Civil Add7 018 Head7 Head7@[Link] D7 S15 Sumi Rangpur 1400
Student
After 1 NF DNo
D1
SID
S1
SNam
e
Anik
City
Dhaka
Zip
1200
D1 S2 Anu Rangpur 1400
D2 S3 Antora Dhaka 1200
D2 S4 Aditi Sylhet 1500
Department
D2 S5 Raju Sylhet 1500
DNo DName Address PhnNo DeptHead DHemail
D3 S6 Meena Rangpur 1400
D1 CSE Add1 012 Head1 Head1@[Link]
D2 EEE Add2 013 Head2 Head2@[Link] D4 S7 Deepa Chittagong 1300
D3 ETE Add3 014 Head3 Head3@[Link] D4 S8 Mithu Rangpur 1400
D4 NFE Add4 015 Head4 Head4@[Link] D4 S9 Ani Barishal 1100
D5 MCT Add5 016 Head5 Head5@[Link] D5 S10 Kakon Chittagong 1300
D6 SWE Add6 017 Head6 Head6@[Link]
D5 S11 Anu Dhaka 1200
D7 Civil Add7 018 Head7 Head7@[Link]
D6 S12 Koli Barishal 1100
D7 S13 Helal Chittagong 1300
D7 S14 Hamid Dhaka 1200
D7 S15 Sumi Rangpur 1400
2 NF Student
DNo SID SNam
e
City Zip
Department D1 S1 Anik Dhaka 1200
DNo DName Address PhnNo DeptHead DHemail
D1 S2 Anu Rangpur 1400
D1 CSE Add1 012 Head1 Head1@[Link]
D2 EEE Add2 013 Head2 Head2@[Link] • The table must be D2 S3 Antora Dhaka 1200
in 1NF D2 S4 Aditi Sylhet 1500
D3 ETE Add3 014 Head3 Head3@[Link]
• Here, All non-key
D4 NFE Add4 015 Head4 Head4@[Link] D2 S5 Raju Sylhet 1500
attributes must
D5 MCT Add5 016 Head5 Head5@[Link] depend on key D3 S6 Meena Rangpur 1400
D6 SWE Add6 017 Head6 Head6@[Link]
attributes
D4 S7 Deepa Chittagong 1300
D7 Civil Add7 018 Head7 Head7@[Link]
D4 S8 Mithu Rangpur 1400
DepartmentNew DeptHead D4 S9 Ani Barishal 1100
DNo DName Address PhnNo DeptHead HeadID DeptHead DHemail
D1 CSE Add1 012 H1 H1 Head1 Head1@[Link] D5 S10 Kakon Chittagong 1300
D2 EEE Add2 013 H2 H2 Head2 Head2@[Link] D5 S11 Anu Dhaka 1200
D3 ETE Add3 014 H3 H3 Head3 Head3@[Link] D6 S12 Koli Barishal 1100
D4 NFE Add4 015 H4 H4 Head4 Head4@[Link]
D7 S13 Helal Chittagong 1300
D5 MCT Add5 016 H5 H5 Head5 Head5@[Link]
D7 S14 Hamid Dhaka 1200
D6 SWE Add6 017 H6 H6 Head6 Head6@[Link]
D7 S15 Sumi Rangpur 1400
D7 Civil Add7 018 H7 H7 Head7 Head7@[Link]
After 2 NF Student
DNo SID SNam
e
City Zip
D1 S1 Anik Dhaka 1200
D1 S2 Anu Rangpur 1400
D2 S3 Antora Dhaka 1200
D2 S4 Aditi Sylhet 1500
DepartmentNew DeptHead D2 S5 Raju Sylhet 1500
DNo DName Address PhnNo DeptHead HeadID DeptHead DHemail D3 S6 Meena Rangpur 1400
D1 CSE Add1 012 H1 H1 Head1 Head1@[Link] D4 S7 Deepa Chittagong 1300
D2 EEE Add2 013 H2 H2 Head2 Head2@[Link]
D4 S8 Mithu Rangpur 1400
D3 ETE Add3 014 H3 H3 Head3 Head3@[Link]
D4 S9 Ani Barishal 1100
D4 NFE Add4 015 H4 H4 Head4 Head4@[Link]
D5 MCT Add5 016 H5 H5 Head5 Head5@[Link] D5 S10 Kakon Chittagong 1300
D6 SWE Add6 017 H6 H6 Head6 Head6@[Link] D5 S11 Anu Dhaka 1200
D7 Civil Add7 018 H7 H7 Head7 Head7@[Link] D6 S12 Koli Barishal 1100
D7 S13 Helal Chittagong 1300
D7 S14 Hamid Dhaka 1200
D7 S15 Sumi Rangpur 1400
StudentNew
DNo SID SNam
e
Zip 3 NF Student
DNo SID SNam
e
City Zip
ZipCity D1 S1 Anik Dhaka 1200
D1 S1 Anik 1200 Zip City
D1 S2 Anu Rangpur 1400
D1 S2 Anu 1400 1200 Dhaka
D2 S3 Antora Dhaka 1200
D2 S3 Antora 1200 1400 Rangpur
D2 S4 Aditi Sylhet 1500
D2 S4 Aditi 1500 1500 Sylhet
D2 S5 Raju Sylhet 1500
D2 S5 Raju 1500 1300 Chittagong
D3 S6 Meena Rangpur 1400
D3 S6 Meena 1400 1100 Barishal
D4 S7 Deepa Chittagong 1300
D4 S7 Deepa 1300
D4 S8 Mithu Rangpur 1400
D4 S8 Mithu 1400
D4 S9 Ani Barishal 1100
D4 S9 Ani 1100
D5 S10 Kakon Chittagong 1300
D5 S10 Kakon 1300
D5 S11 Anu Dhaka 1200
D5 S11 Anu 1200
D6 S12 Koli Barishal 1100
D6 S12 Koli 1100
D7 S13 Helal Chittagong 1300
D7 S13 Helal 1300
D7 S14 Hamid Dhaka 1200
D7 S14 Hamid 1200
D7 S15 Sumi Rangpur 1400
D7 S15 Sumi 1400
StudentNew
DNo SID SNam Zip ZipCity
e
Zip City
D1 S1 Anik 1200
Tables after 3NF 1200 Dhaka
D1 S2 Anu 1400
1400 Rangpur
D2 S3 Antora 1200
1500 Sylhet
D2 S4 Aditi 1500
1300 Chittagong
D2 S5 Raju 1500
1100 Barishal
D3 S6 Meena 1400
D4 S7 Deepa 1300
DepartmentNew DeptHead
D4 S8 Mithu 1400
DNo DName Address PhnNo DeptHead HeadID DeptHead DHemail
D4 S9 Ani 1100 D1 CSE Add1 012 H1 H1 Head1 Head1@[Link]
D5 S10 Kakon 1300 D2 EEE Add2 013 H2 H2 Head2 Head2@[Link]
D5 S11 Anu 1200 D3 ETE Add3 014 H3 H3 Head3 Head3@[Link]
D4 NFE Add4 015 H4 H4 Head4 Head4@[Link]
D6 S12 Koli 1100
D5 MCT Add5 016 H5 H5 Head5 Head5@[Link]
D7 S13 Helal 1300
D6 SWE Add6 017 H6 H6 Head6 Head6@[Link]
D7 S14 Hamid 1200
D7 Civil Add7 018 H7 H7 Head7 Head7@[Link]
D7 S15 Sumi 1400
Thank
You