Normalization in RDBMS

Normalization is the process of minimizing redundancy from a relation or set of relations. Redundancy in relation may cause insertion, deletion and updation anomalies. So, it helps to minimize the redundancy in relations. Normal forms are used to eliminate or reduce redundancy in database tables.Normalization divides the larger table into the smaller table and links them using relationship.
The normal form is used to reduce redundancy from the database table.UNF: Unnormalized form
1NF: First normal form
2NF: Second normal form
3NF: Third normal form

1. DEPARTMENT OF COMMERCE WITH FINANCE NORMALIZATION in DBMS Dr.SNS RAJALAKSHMI COLLEGE OF ARTS AND SCIENCE COIMBATORE-49 (AUTONOMOUS) Accredited by NAAC (Cycle-III) with ‘A+’ Grade

2. CONTENTS Introduction Normal Forms Anomalies in DBMS Types of Normal Forms Conclusion

3. INTRODUCTION Normalization is the process of organizing the data in the database . Normalization is used to minimize the redundancy from a relation or set of relations. It is also used to eliminate the undesirable characteristics like Insertion, Update and Deletion Anomalies . Normalization divides the larger table into the smaller table and links them using relationship . The normal form is used to reduce redundancy from the database table.

4. NORMAL FORMS  Edgar Codd proposed the theory of normalization of data with First Normal Form and extending the theory with Second and Third Normal Form. Later he joined Raymond F. Boyce to develop the theory of Boyce- Codd Normal Form . By using different normal forms we can remove all these anomalies and bring the database to a consistent state.

5. ANOMALIES IN DBMS Stu_id Stu_name Stu_add Course_code 1001 shiva cbe 219 1002 arul chennai 218 1001 shiva cbe 218 1003 vijay ooty 219 krishna kerela 220 1005 reva Kodai 221 Update anomalies   Deletion anomalies   Insert anomalies   Fees not Paid Deleting a course code will delete the data

6. TYPES OF NORMAL FORMS It has no multi-valued dependency.

7. First Normal Forms(1NF) A relation will be 1NF if it contains an atomic value . It states that an attribute of a table cannot hold multiple values. It must hold only single-valued attribute . First normal form disallows the multi-valued attribute, composite attribute, and their combinations . id name phone add 101 hari 247859,248795 cbe 102 lali 248964 ooty id name phone add 101 hari 247859, cbe 101 hari 248795 cbe Before 1NF After 1NF

8. Second Normal Forms(2NF ) In the second normal form, all non-key attributes are fully functional dependent on the primary key. id sub 101 maths 101 che 102 social 103 tamil id sub age 101 maths 40 101 che 40 102 social 28 103 tamil 30 id name 101 40 101 40 102 28 103 30

9. A relation will be in 3NF if it is in 2NF and not contain any transitive partial dependency . 3NF is used to reduce the data duplication. It is also used to achieve the data integrity . If there is no transitive dependency for non-prime attributes, then the relation must be in third normal form. Third Normal Forms(3NF )

10. emp_id emp_name emp_zip emp_state emp_city emp_district 1001 John 282005 UP Agra Dayal Bagh 1002 Ajeet 222008 TN Chennai M-City 1006 Lora 282007 TN Chennai Urrapakkam 1101 Lilly 292008 UK Pauri Bhagwan emp_id emp_name emp_zip 1001 John 282005 1002 Ajeet 222008 1006 Lora 282007 1101 Lilly 292008 emp_id emp_state emp_city emp_district 1001 UP Agra Dayal Bagh 1002 TN Chennai M-City 1006 TN Chennai Urrapakkam 1101 UK Pauri Bhagwan

11. BOYCE CODD Normal Forms(BCNF) BCNF is the advance version of 3NF. It is stricter than 3NF . A table is in BCNF if every functional dependency X → Y, X is the super key of the table . emp_id emp_country emp_Dept Dept_Type emp_dept no 1001 India 245 design 801 1001 India 521 test 541 1002 Africa 654 store 654 1002 Africa 245 maintain 254

12. E mp_country table: emp_id emp_country 1001 India 1001 India EMP_DEPT table: emp_Dept Dept_Type emp_dept no 245 design 801 521 test 541 654 store 654 245 maintain 254 EMP_DEPT_MAPPING table: emp_id emp_Dept 1001 245 1001 521 1002 654 1002 245