Normalization Examples
Example 1
Q: Normalize this table up to 3rd Normal Form.
M-Id Mother Name DoB Contact C-Id Children Name Gender DoB
100 Ghunchay 13-11-1970 0415621 C-456 Sasha Pfiffer Female 27-02-2005
C-457 Jack Male 01-06-2007
C-458 Lucretia Female 18-07-2010
101 Haanday 03-07-1983 064886 C-459 Daalia Female 07-06-2017
C-460 Oliver Male 08-05-2018
First Normal Form
1) Remove Multi-Valued Attributes/Repeating Groups
2) Assign Primary Key
M-Id Mother Name DoB Contact C-Id Children Name Gender DoB
100 Ghunchay 13-11-1970 0415621 C-456 Sasha Pfiffer Female 27-02-2005
100 Ghunchay 13-11-1970 0415621 C-457 Jack Male 01-06-2007
100 Ghunchay 13-11-1970 0415621 C-458 Lucretia Female 18-07-2010
101 Haanday 03-07-1983 064886 C-459 Daalia Female 07-06-2017
101 Haanday 03-07-1983 064886 C-460 Oliver Male 08-05-2020
Second Normal Form: (Removing Partial Functional Dependency)
M-Id Mother Name DoB Contact
100 Ghunchay 13-11-1970 0415621
101 Haanday 03-07-1983 064886
M-Id C-Id C-Id Children Name Gender DoB
100 C-456 C-456 Sasha Pfiffer Female 27-02-2005
100 C-457 C-457 Jack Male 01-06-2007
100 C-458 C-458 Lucretia Female 18-07-2010
101 C-459 C-459 Daalia Female 07-06-2017
101 C-460 C-460 Oliver Male 08-05-2020
Second Normal Form: (Removing Partial Functional Dependency)
M-Id Mother Name DoB Contact
100 Ghunchay 13-11-1970 0415621
101 Haanday 03-07-1983 064886
C-Id Children Name Gender DoB M-Id
C-456 Sasha Pfiffer Female 27-02-2005 100
C-457 Jack Male 01-06-2007 100
C-458 Lucretia Female 18-07-2010 100
C-459 Daalia Female 07-06-2017 101
C-460 Oliver Male 08-05-2020 101
Third Normal Form (Removing Transitive Functional Dependency)
Both these tables are
already in third normal
M-Id Mother Name DoB Contact form because no non-key
100 Ghunchay 13-11-1970 0415621 attribute depends on any
other non-key attribute.
101 Haanday 03-07-1983 064886
C-Id Children Name Gender DoB M-Id
C-456 Sasha Pfiffer Female 27-02-2005 100
C-457 Jack Male 01-06-2007 100
C-458 Lucretia Female 18-07-2010 100
C-459 Daalia Female 07-06-2017 101
C-460 Oliver Male 08-05-2020 101
Example 2
Q: Normalize this table up to 3rd Normal Form.
Order Order Date C-Id C-Name C- Address Product Product Produc Ordered
Id Id Description t Price Quantity
100 20-01-2024 1100 Aslam Attock 07 Ball Pen 40 50
01 Board Markers 80 10
03 Staplers 400 05
101 27-01-2024 1300 Kareem Islamabad 01 Board Markers 80 20
10 Dusters 50 14
First Normal Form:
Order Order Custome Customer Customer Product Product Product Ordered
Id Date r Id Name Address Id Description Price Quantity
20-01-
100 1100 Aslam Attock 07 Ball Pen 40 50
2024
20-01-
100 1100 Aslam Attock 01 Board Markers 80 10
2024
20-01-
100 1100 Aslam Attock 03 Staplers 400 05
2024
27-01-
101 1300 Kareem Islamabad 01 Board Markers 80 20
2024
27-01-
101 1300 Kareem Islamabad 10 Dusters 50 14
2024
Second Normal Form:
Order Custom Customer Customer Product Product Product
Order Date
Id er Id Name Address Id Description Price
100 20-01-2024 1100 Aslam Attock 01 Board Markers 80
101 27-01-2024 1300 Kareem Islamabad 03 Staplers 400
07 Ball Pen 40
10 Dusters 50
Ordered
Order Id Product Id
Quantity
100 07 50
100 01 10
100 03 05
101 01 20
101 10 14
Third Normal Form
Customer Customer Customer Customer
Order Id Order Date
Id Id Name Address
100 20-01-2024 1100 1100 Aslam Attock
101 27-01-2024 1300
1300 Kareem Islamabad
Ordered Product Product Product
Order Id Product Id
Quantity Id Description Price
100 07 50
01 Board Markers 80
100 01 10
03 Staplers 400
100 03 05
07 Ball Pen 40
101 01 20
101 10 14 10 Dusters 50
Example 3
Q: Normalize this table up to 3rd Normal Form.
D-Id Doctor Sp Id Specialty Contact P-Id Patient Name Gender Address
Name
P-789, Sasha, Female, Rwp,
100 Ibrahim Sp-43 Heart Surgeon 12 P-156, Aslam, Male, Rwp,
P-700 Dansih Male Attock
P-456, Amjad, Male, Lahore,
101 Ahsan Sp-47 Eye Surgeon 27
P-700 Dansih Male Attock
P-456, Amjad, Male, Lahore,
102 Faaria Sp-43 Heart Surgeon 35 P-789, Sasha, Female Rwp,
P-600 Adnan Male Rwp
First Normal Form:
D-Id Doctor Sp Id Specialty Contact P-Id Patient Gender Address
Name Name
100 Ibrahim Sp-43 Heart Surgeon 12 P-789 Sasha Female Rwp
100 Ibrahim Sp-43 Heart Surgeon 12 P-156 Aslam Male Rwp
100 Ibrahim Sp-43 Heart Surgeon 12 P-700 Dansih Male Attock
101 Ahsan Sp-47 Eye Surgeon 27 P-456 Amjad Male Lahore
101 Ahsan Sp-47 Eye Surgeon 27 P-700 Dansih Male Attock
102 Faaria Sp-43 Heart Surgeon 35 P-456 Amjad Male Lahore
102 Faaria Sp-43 Heart Surgeon 35 P-789 Sasha Female Rwp
102 Faaria Sp-43 Heart Surgeon 35 P-600 Adnan Male Rwp
Second Normal Form: (Removing Partial Functional Dependency)
D-Id Doctor Sp Id Specialty Contact
Name
100 Ibrahim Sp-43 Heart Surgeon 12
D-Id P-Id
101 Ahsan Sp-47 Eye Surgeon 27
100 P-789
102 Faaria Sp-43 Heart Surgeon 35
100 P-156
100 P-700
P-Id Patient Gender Address
101 P-456
Name
101 P-700
P-789 Sasha Female Rwp
102 P-456
P-156 Aslam Male Rwp
102 P-789
P-700 Dansih Male Attock
102 P-600
P-456 Amjad Male Lahore
P-600 Adnan Male Rwp
Third Normal Form (Removing Transitive Functional Dependency)
D-Id Doctor Contact Sp Id
Sp Id Specialty
Name
Sp-43 Heart Surgeon
100 Ibrahim 12 Sp-43
D-Id P-Id Sp-47 Eye Surgeon
101 Ahsan 27 Sp-47
100 P-789
102 Faaria 35 Sp-50
100 P-156
100 P-700
P-Id Patient Gender Address
101 P-456
Name
101 P-700
P-789 Sasha Female Rwp
102 P-456
P-156 Aslam Male Rwp
102 P-789
P-700 Dansih Male Attock
102 P-600
P-456 Amjad Male Lahore
P-600 Adnan Male Rwp
Example 4
Un Normalized Table
Teacher
Father Subject Subject Subject Contact
Roll No Name Teacher Id Teacher Gender Grade
Name Id Name Marks No
Mr. Jahangir, Male, 032469,
20,22, Urdu, 100, 100, A100,
143 Kareem Akhtar Khan Miss Jasmine, Female, 0354659, C
35 English, Stats 85 A150, A200
Mr. Tariq Male 516489
Mr. Asghar, Mr. Male, 0333569
980 Masood Nisar 22, 35 English, Stats 100, 85 A190, A200 B
Tariq Male 516489
Or
Teacher
Subject Subject Total
Roll No Name Father Name Teacher Id Teacher Gender Contact Grade
Id Name Marks
No
143 Kareem Akhtar Khan 20 Urdu 100 A100 Mr. Jahangir Male 032469 C
22 English 100 A150 Miss. Jasmine Female 0354659 C
35 Stats 85 A200 Mr. Tariq Male 516489 C
980 Masood Nisar 22 English 100 A190 Mr. Asghar Male 0333569 B
35 Stats 85 A200 Mr. Tariq Male 516489 B
First Normal Form:
Teacher
Subject Total
Roll No Name Father Name Subject Id Teacher Id Teacher Gender Contact Grade
Name Marks
No
143 Kareem Akhtar Khan 20 Urdu 100 A100 Mr. Jahangir Male 032469 C
143 Kareem Akhtar Khan 22 English 100 A150 Miss. Jasmine Female 0354659 C
143 Kareem Akhtar Khan 35 Stats 85 A200 Mr. Tariq Male 516489 C
980 Masood Nisar 22 English 100 A190 Mr. Asghar Male 0333569 B
980 Masood Nisar 35 Stats 85 A200 Mr. Tariq Male 516489 B
Second Normal Form:
Roll No Name Father Name Grade
143 Kareem Akhtar Khan C
980 Masood Nisar B
Teacher
Subject
Roll No Teacher Id Teacher Gender Contact
Id
No
143 20 A100 Mr. Jahangir Male 032469
143 22 A150 Miss. Jasmine Female 0354659
143 35 A200 Mr. Tariq Male 516489
980 22 A190 Mr. Asghar Male 0333569
980 35 A200 Mr. Tariq Male 516489
Subject Subject Total
Id Name Marks
20 Urdu 100
22 English 100
35 Stats 85
Third Normal Form:
Roll No Name Father Name Grade Subject
Subject Id Total Marks
Name
143 Kareem Akhtar Khan C 20 Urdu 100
980 Masood Nisar B 22 English 100
35 Stats 85
Roll No Subject Id Teacher Id
143 20 A100
143 22 A150 Teacher
Teacher Id Teacher Gender
143 35 A200 Contact No
A100 Mr. Jahangir Male 032469
980 22 A190
A150 Miss. Jasmine Female 0354659
980 35 A200 A190 Mr. Asghar Male 0333569
A200 Mr. Tariq Male 516489
Example 5
Q: Normalize this table up to 3rd Normal Form.
E-Id E-Name E- E- City P-Id P-Name P-Budget Skill 1 Skill 2
Dept
100 Ahmad HR Lahore P01 Recruitment 5,00,000/- Comm Mgt
101 Sana IT Karachi P02 Inventory 10,000,000/- Java SQL
102 Amjad IT Lahore P01 Recruitment 5,00,000/- Web Dev Python
Repeating Groups
First Normal Form: Removing Repeating Groups and Assigning Primary Key
E-Id E-Name E- E- City P-Id P-Name P-Budget Skill
Dept
100 Ahmad HR Lahore P01 Recruitment 5,00,000/- Comm
100 Ahmad HR Lahore P01 Recruitment 5,00,000/- Mgt
101 Sana IT Karachi P02 Inventory 10,000,000/- Java
101 Sana IT Karachi P02 Inventory 10,000,000/- SQL
102 Amjad IT Lahore P01 Recruitment 5,00,000/- Web Dev
102 Amjad IT Lahore P01 Recruitment 5,00,000/- Python
Second Normal Form:
E-Id E-Name E- E- City P-Id P-Name P-Budget
Dept
100 Ahmad HR Lahore P01 Recruitment 5,00,000/-
101 Sana IT Karachi P02 Inventory 10,000,000/-
102 Amjad IT Lahore P01 Recruitment 5,00,000/-
E-Id Skill
100 Comm
100 Mgt
101 Java
101 SQL
102 Web Dev
102 Python
Third Normal Form:
E-Id E-Name E- E- City P-Id
Dept
100 Ahmad HR Lahore P01
101 Sana IT Karachi P02
102 Amjad IT Lahore P01
P-Id P-Name P-Budget
E-Id Skill P01 Recruitment 5,00,000/-
100 Comm P02 Inventory 10,000,000/-
100 Mgt
101 Java
101 SQL
102 Web Dev
102 Python