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 Course. Code SGPA Customer. Name Product 1 Product 2 1 Raju 17 CSE 112 3. 50 Rafi Basketball Playstation 1 Raju 17 CSE 213 3. 80 Raju Football Mobile 2 Meena 18 CSE 333 4 3 Mithu 19 CSE 112 3. 80 3 Mithu 19 ECO 314 3. 25 Row Level Column Level Product 3 watch

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 H 940 is now 500. Deletion anomalies : If we want to delete Course_no 351, then the detail of room number C 320 will also lost. Insert anomalies : there is a problem when we want to insert a room H 856.

Types of Normalization • 1 st Normal Form (1 NF) • 2 nd Normal Form (2 NF) • 3 rd Normal Form (3 NF) • Boyce Code Normal Form (BCNF) • 4 th Normal Form (4 NF)

1 NF 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: Customer. Name Contact Rafi 12 Shukrabad, Dhaka rafi@gmail. com Raju Road no 4, Dhanmondi, Dhaka raju@gmail. com Customer. Name Address Email Rafi 12 Shukrabad, Dhaka rafi@gmail. com Raju Road no 4, Dhanmondi, Dhaka raju@gmail. com Here, Values stored in Contact column contains both address and email. (violates rule 2)

1 NF • Example 2: Customer. Name Product. Order Rafi Basketball, Playstation Raju Football, Mobile, watch Customer. Name Product. Order Rafi Basketball Rafi Playstation Raju Football Raju Mobile Raju watch Product. Order does not have single(atomic) valued attributes/columns. • Example 3: Customer. N Product 1 ame Product 2 Rafi Basketball Playstation Raju Football Mobile Product 3 watch All the columns in a table should have unique names. We will discuss about it later.

2 NF For a table to be in the Second Normal Form, • It should be in the First Normal form. • And, it should not have Partial Dependency. Customer. Name Address Rafi 12 Shukrabad, Dhaka Raju 4 Dhanmondi, Dhaka Hena Adabor 2, dhaka Hakim Mirpur 1, Dhaka We have to find Composite key / Partial dependence Customer. Name Address Product Quantit y Date Rafi 12 Shukrabad, Dhaka Basketb all 2 1 -11 -2019 Raju 4 Dhanmondi, Dhaka Football 1 11 -3 -2018 12 Shukrabad, Dhaka Watch Adabor 2, dhaka Mobile Mirpur 1, Dhaka Football Rafi Hena Hakim 1 2 1 18 -7 -2018 Custom er. Name Product Date Quantity Rafi Basketball 12 -11 -2019 2 Raju Football 11 -3 -2018 1 Rafi Watch 18 -7 -2018 1 Hena Mobile 12 -11 -2017 2 Hakim Football 7 -4 -2017 1 2 -11 -2017 7 -4 -2017

3 NF • Table must be in 2 NF • Transitive functional dependency of non-prime attribute on any super key should be removed.

Example Consider This given Table Department. Student DNo DName Address Phn. No Dept. Head DHemail Student D 1 CSE Add 1 012 Head 1@gmail. com Anik, Anu D 2 EEE Add 2 013 Head 2@gmail. com Antora, Aditi, Raju D 3 ETE Add 3 014 Head 3@gmail. com Meena D 4 NFE Add 4 015 Head 4@gmail. com Deepa, Mithu, Ani D 5 MCT Add 5 016 Head 5@gmail. com Kakon, Apurbo D 6 SWE Add 6 017 Head 6@gmail. com Koli, Hasan D 7 Civil Add 7 018 Head 7@gmail. com Helal, Hamid, Sumi

Now we will try to normalize this table: Department. Student DNo DName Address Phn. No Dept. Head DHemail Student D 1 CSE Add 1 012 Head 1@gmail. com Anik, Anu D 2 EEE Add 2 013 Head 2@gmail. com Antora, Aditi, Raju D 3 ETE Add 3 014 Head 3@gmail. com Meena D 4 NFE Add 4 015 Head 4@gmail. com Deepa, Mithu, Ani D 5 MCT Add 5 016 Head 5@gmail. com Kakon, Apurbo D 6 SWE Add 6 017 Head 6@gmail. com Koli, Hasan D 7 Civil Add 7 018 Head 7@gmail. com Helal, Hamid, Sumi Single valued Attribute: Dno, Dname, Address, Phn. No, Dept. Head, DHemail Multivalued Attribute: Student Primary. Key: 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. Department. Student DNo DName Address Phn. No Dept. Head DHemail Student 1 Student 2 Student 3 D 1 CSE Add 1 012 Head 1@gmail. com Anik Anu D 2 EEE Add 2 013 Head 2@gmail. com Antora Aditi Raju D 3 ETE Add 3 014 Head 3@gmail. com Meena D 4 NFE Add 4 015 Head 4@gmail. com Deepa Mithu Ani D 5 MCT Add 5 016 Head 5@gmail. com Kakon Apurbo D 6 SWE Add 6 017 Head 6@gmail. com Koli Hasan D 7 Civil Add 7 018 Head 7@gmail. com Helal Hamid Sumi

Student 1 NF Department. Student DNo DName Address Phn. No Dept. Head DHemail Student 1 Student 2 D 1 CSE Add 1 012 Head 1@gmail. com Anik Anu D 2 EEE Add 2 013 Head 2@gmail. com Antora Aditi D 3 ETE Add 3 014 Head 3@gmail. com Meena D 4 NFE Add 4 015 Head 4@gmail. com Deepa Mithu D 5 MCT Add 5 016 Head 5@gmail. com Kakon Anu D 6 SWE Add 6 017 Head 6@gmail. com Koli D 7 Civil Add 7 018 Head 7@gmail. com Helal Department Hamid Student 3 Raju Ani Sumi DNo SID SName City Zip D 1 S 1 Anik Dhaka 1200 D 1 S 2 Anu Rangpur 1400 D 2 S 3 Antora Dhaka 1200 D 2 S 4 Aditi Sylhet 1500 D 2 S 5 Raju Sylhet 1500 D 3 S 6 Meena Rangpur 1400 D 4 S 7 Deepa Chittagong 1300 D 4 S 8 Mithu Rangpur 1400 DNo DName Address Phn. No Dept. Head DHemail D 4 S 9 Ani Barishal 1100 D 1 CSE Add 1 012 Head 1@gmail. com D 5 S 10 Kakon Chittagong 1300 D 2 EEE Add 2 013 Head 2@gmail. com D 5 S 11 Anu Dhaka 1200 D 3 ETE Add 3 014 Head 3@gmail. com D 4 NFE Add 4 015 Head 4@gmail. com D 6 S 12 Koli Barishal 1100 D 5 MCT Add 5 016 Head 5@gmail. com D 7 S 13 Helal Chittagong 1300 D 6 SWE Add 6 017 Head 6@gmail. com D 7 S 14 Hamid Dhaka 1200 D 7 Civil Add 7 018 Head 7@gmail. com D 7 S 15 Sumi Rangpur 1400

Student After 1 NF Department DNo SID SName City Zip D 1 S 1 Anik Dhaka 1200 D 1 S 2 Anu Rangpur 1400 D 2 S 3 Antora Dhaka 1200 D 2 S 4 Aditi Sylhet 1500 D 2 S 5 Raju Sylhet 1500 D 3 S 6 Meena Rangpur 1400 D 4 S 7 Deepa Chittagong 1300 DNo DName Address Phn. No Dept. Head DHemail D 1 CSE Add 1 012 Head 1@gmail. com D 2 EEE Add 2 013 Head 2@gmail. com D 3 ETE Add 3 014 Head 3@gmail. com D 4 S 8 Mithu Rangpur 1400 D 4 NFE Add 4 015 Head 4@gmail. com D 4 S 9 Ani Barishal 1100 D 5 MCT Add 5 016 Head 5@gmail. com D 5 S 10 Kakon Chittagong 1300 D 6 SWE Add 6 017 Head 6@gmail. com D 5 S 11 Anu Dhaka 1200 D 7 Civil Add 7 018 Head 7@gmail. com D 6 S 12 Koli Barishal 1100 D 7 S 13 Helal Chittagong 1300 D 7 S 14 Hamid Dhaka 1200 D 7 S 15 Sumi Rangpur 1400

2 NF Student DNo SID SName City Zip D 1 S 1 Anik Dhaka 1200 D 1 S 2 Anu Rangpur 1400 • The table must be in 1 NF D 2 S 3 Antora Dhaka 1200 D 2 S 4 Aditi Sylhet 1500 • Here, All non-key attributes must depend on key attributes D 2 S 5 Raju Sylhet 1500 D 3 S 6 Meena Rangpur 1400 D 4 S 7 Deepa Chittagong 1300 D 4 S 8 Mithu Rangpur 1400 D 4 S 9 Ani Barishal 1100 D 5 S 10 Kakon Chittagong 1300 Department DNo DName Address Phn. No Dept. Head DHemail D 1 CSE Add 1 012 Head 1@gmail. com D 2 EEE Add 2 013 Head 2@gmail. com D 3 ETE Add 3 014 Head 3@gmail. com D 4 NFE Add 4 015 Head 4@gmail. com D 5 MCT Add 5 016 Head 5@gmail. com D 6 SWE Add 6 017 Head 6@gmail. com D 7 Civil Add 7 018 Head 7@gmail. com Department. New Dept. Head DNo DName Address Phn. No Dept. Head. ID Dept. Head DHemail D 1 CSE Add 1 012 H 1 Head 1@gmail. com D 2 EEE Add 2 013 H 2 Head 2@gmail. com D 5 S 11 Anu Dhaka 1200 D 3 ETE Add 3 014 H 3 Head 3@gmail. com D 6 S 12 Koli Barishal 1100 D 4 NFE Add 4 015 H 4 Head 4@gmail. com D 7 S 13 Helal Chittagong 1300 D 5 MCT Add 5 016 H 5 Head 5@gmail. com D 7 S 14 Hamid Dhaka 1200 D 6 SWE Add 6 017 H 6 Head 6@gmail. com D 7 Civil Add 7 018 H 7 Head 7@gmail. com D 7 S 15 Sumi Rangpur 1400

After 2 NF Department. New Student Dept. Head DNo SID SName City Zip D 1 S 1 Anik Dhaka 1200 D 1 S 2 Anu Rangpur 1400 D 2 S 3 Antora Dhaka 1200 D 2 S 4 Aditi Sylhet 1500 D 2 S 5 Raju Sylhet 1500 DNo DName Address Phn. No Dept. Head. ID Dept. Head DHemail D 3 S 6 Meena Rangpur 1400 D 1 CSE Add 1 012 H 1 Head 1@gmail. com D 4 S 7 Deepa Chittagong 1300 D 2 EEE Add 2 013 H 2 Head 2@gmail. com D 4 S 8 Mithu Rangpur 1400 D 3 ETE Add 3 014 H 3 Head 3@gmail. com D 4 NFE Add 4 015 H 4 Head 4@gmail. com D 4 S 9 Ani Barishal 1100 D 5 MCT Add 5 016 H 5 Head 5@gmail. com D 5 S 10 Kakon Chittagong 1300 D 6 SWE Add 6 017 H 6 Head 6@gmail. com D 5 S 11 Anu Dhaka 1200 D 7 Civil Add 7 018 H 7 Head 7@gmail. com D 6 S 12 Koli Barishal 1100 D 7 S 13 Helal Chittagong 1300 D 7 S 14 Hamid Dhaka 1200 D 7 S 15 Sumi Rangpur 1400

Student. New 3 NF Student DNo SID SName City Zip D 1 S 1 Anik Dhaka 1200 D 1 S 2 Anu Rangpur 1400 D 2 S 3 Antora Dhaka 1200 D 2 S 4 Aditi Sylhet 1500 D 2 S 5 Raju Sylhet 1500 D 3 S 6 Meena Rangpur 1400 1300 D 4 S 7 Deepa Chittagong 1300 Mithu 1400 D 4 S 8 Mithu Rangpur 1400 S 9 Ani 1100 D 4 S 9 Ani Barishal 1100 D 5 S 10 Kakon 1300 D 5 S 10 Kakon Chittagong 1300 D 5 S 11 Anu 1200 D 5 S 11 Anu Dhaka 1200 D 6 S 12 Koli 1100 D 6 S 12 Koli Barishal 1100 D 7 S 13 Helal 1300 D 7 S 13 Helal Chittagong 1300 D 7 S 14 Hamid 1200 D 7 S 14 Hamid Dhaka 1200 D 7 S 15 Sumi 1400 D 7 S 15 Sumi Rangpur 1400 DNo SID SName Zip D 1 S 1 Anik 1200 D 1 S 2 Anu 1400 D 2 S 3 Antora 1200 D 2 S 4 Aditi 1500 D 2 S 5 Raju 1500 D 3 S 6 Meena 1400 D 4 S 7 Deepa D 4 S 8 D 4 Zip. City Zip City 1200 Dhaka 1400 Rangpur 1500 Sylhet 1300 Chittagong 1100 Barishal

Student. New DNo SID SName Zip D 1 S 1 Anik 1200 D 1 S 2 Anu 1400 D 2 S 3 Antora 1200 D 2 S 4 Aditi 1500 D 2 S 5 Raju 1500 D 3 S 6 Meena 1400 D 4 S 7 Deepa 1300 D 4 S 8 Mithu 1400 D 4 S 9 Ani 1100 D 5 S 10 Kakon 1300 D 5 S 11 Anu D 6 S 12 D 7 Zip. City Tables after 3 NF Department. New Zip City 1200 Dhaka 1400 Rangpur 1500 Sylhet 1300 Chittagong 1100 Barishal Dept. Head DNo DName Address Phn. No Dept. Head. ID Dept. Head DHemail D 1 CSE Add 1 012 H 1 Head 1@gmail. com D 2 EEE Add 2 013 H 2 Head 2@gmail. com 1200 D 3 ETE Add 3 014 H 3 Head 3@gmail. com Koli 1100 D 4 NFE Add 4 015 H 4 Head 4@gmail. com S 13 Helal 1300 D 5 MCT Add 5 016 H 5 Head 5@gmail. com D 7 S 14 Hamid 1200 D 6 SWE Add 6 017 H 6 Head 6@gmail. com D 7 S 15 Sumi 1400 D 7 Civil Add 7 018 H 7 Head 7@gmail. com

Thank You