Normalization in Database

Normalization in Database

• 1.
  Normalization Normalization is theprocess of removing redundant data from your tables in order to improve storage efficiency, data integrity and scalability. This improvement is balanced against an increase in complexity and potential performance losses from the joining of the normalized tables at query-time. Why we need Normalization?  Minimize data redundancy i.e. no unnecessarily duplication of data.  To make database structure flexible i.e. it should be possible to add new data values and rows without reorganizing the database structure.  Data should be consistent throughout the database i.e. it should not suffer from following anomalies. o Insert Anomaly - Due to lack of data i.e., all the data available for insertion such that null values in keys should be avoided. This kind of anomaly can seriously damage a database. o Update Anomaly - It is due to data redundancy i.e. multiple occurrences of same values in a column. This can lead to inefficiency. o Deletion Anomaly - It leads to loss of data for rows that are not stored else where. It could result in loss of vital data.  Complex queries required by the user should be easy to handle. Advantages of Normalization The following are the advantages of the normalization.  More efficient data structure.  Avoid redundant fields or columns.  More flexible data structure i.e. we should be able to add new rows and data values easily  Better understanding of data.  Ensures that distinct tables exist when necessary.  Easier to maintain data structure i.e. it is easy to perform operations and complex queries can be easily handled.  Minimizes data duplication.  Close modeling of real world entities, processes and their relationships. Types of Normalization Here are the most commonly used normal forms:  First normal form(1NF)  Second normal form(2NF)  Third normal form(3NF)  Boyce & Codd normal form (BCNF) First Normal Form – If a relation contain composite or multi-valued attribute, it violates first normal form or a relation is in first normal form if it does not contain any composite or multi-valued attribute. A relation is in first normal form if every attribute in that relation is singled valued attribute.
• 2.
  emp_id emp_name emp_addressemp_mobile 101 Herschel New Delhi 8912312390 102 Jon Kanpur 8812121212 9900012222 103 Ron Chennai 7778881212 104 Lester Bangalore 9990000123 8123450987 To make the table complies with 1NF we should have the data like this: emp_id emp_name emp_address emp_mobile 101 Herschel New Delhi 8912312390 102 Jon Kanpur 8812121212 102 Jon Kanpur 9900012222 103 Ron Chennai 7778881212 104 Lester Bangalore 9990000123 104 Lester Bangalore 8123450987 Second normal form (2NF) A table is said to be in 2NF if both the following conditions hold:  Table is in 1NF (First normal form)  No non-prime attribute is dependent on the proper subset of any candidate key of table. An attribute that is not part of any candidate key is known as non-prime attribute. teacher_id subject teacher_age 111 Maths 38 111 Physics 38 222 Biology 38 333 Physics 40 333 Chemistry 40 Candidate Keys: {teacher_id, subject} Non prime attribute: teacher_age
• 3.
  To make thetable complies with 2NF we can break it in two tables like this: teacher_details table: teacher_id teacher_age 111 38 222 38 333 40 teacher_subject table: teacher_id subject 111 Maths 111 Physics 222 Biology 333 Physics 333 Chemistry Third normal form (3NF) A table is said to be in 3NF if both the following conditions hold:  Table is in 2NF (Second normal form)  No transitive functional dependencies. A functional dependency is said to be transitive if it is indirectly formed by two functional dependencies. For e.g. X -> Z is a transitive dependency if the following three functional dependencies hold true: X->Y Y does not ->X Y->Z 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 1201 Steve 222999 MP Gwalior Ratan
• 4.
  To make thistable complies with 3NF we have to break the table into two tables to remove the transitive dependency: employee table: emp_id emp_name emp_zip 1001 John 282005 1002 Ajeet 222008 1006 Lora 282007 1101 Lilly 292008 1201 Steve 222999 employee_zip table: emp_zip emp_state emp_city emp_district 282005 UP Agra Dayal Bagh 222008 TN Chennai M-City 282007 TN Chennai Urrapakkam 292008 UK Pauri Bhagwan 222999 MP Gwalior Ratan A. S. M. Shafi Lecturer Department of Computer Science and Engineering Khwaja Yunus Ali University Enaytpur, Sirajgonj-6751, Bangladesh