NORMALIZATION v Database Normalization is a technique of

NORMALIZATION v. Database Normalization is a technique of organizing the data in the database. v. Normalization is a systematic approach of decomposing tables to eliminate data redundancy and undesirable characteristics like Insertion, Update and Deletion Anomalies. v It is a multi-step process that puts data into tabular form, removing duplicated data from the relation tables. v. Normalization is used for mainly two purposes, 1. Eliminating redundant data. 2. Ensuring logical storage of data. C. N. . Umadevi, Asst. Professor in C. S, Enathi Rajappa Arts and Science College, Pattukkottai, Thanjavur District 1

NEED FOR NORMALIZATION v. An un-normalized table may have data redundancy so it occupies extra memory space and also make it difficult to handle. v. Insert, Update and Delete Anomalies are very frequent, if database is not normalized. C. N. . Umadevi, Asst. Professor in C. S, Enathi Rajappa Arts and Science College, Pattukkottai, Thanjavur District 2

NORMALIZATION RULE Normalization rules are divided into the following normal forms: §First Normal Form (1 NF) §Second Normal Form (2 NF) §Third Normal Form (3 NF) §Boyce Code Normal Form (BCNF) §Fourth Normal Form (4 NF) C. N. . Umadevi, Asst. Professor in C. S, Enathi Rajappa Arts and Science College, Pattukkottai, Thanjavur District 3

First Normal Form (1 NF): Definition: For a table to be in 1 NF it must satisfy the following 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: Let consider the following STUDENT table roll_no name subject STUDENT 101 Akon OS 103 Ckon Java 102 Bkon C, C++ C. N. . Umadevi, Asst. Professor in C. S, Enathi Rajappa Arts and Science College, Pattukkottai, Thanjavur District 4

First Normal Form (1 NF) (continued……) In the above table, attribute values belongs to same domain and attribute names are unique but doesn’t contains single valued attributes. That is attribute subject has two values in third row (C , C++), thus student was not in 1 NF. The student table is modified as follows to convert it into 1 NF roll_no name subject 101 Akon OS 101 Akon CN 103 Ckon Java 102 Bkon C++ The above table is in 1 NF (atomic vales) but redundancy is increased C. N. . Umadevi, Asst. Professor in C. S, Enathi Rajappa Arts and Science College, Pattukkottai, Thanjavur District 5

Second Normal Form (2 NF): Definition: For a table to be in the Second Normal Form, 1. It should be in the First Normal form. 2. All non-key attributes are fully functional dependent on the primary key. Functional Dependency: In a table, if attribute B is functionally dependent on A, but is not functionally dependent on a proper subset of A, then B is considered fully functional dependent on A. Hence, in a 2 NF table, all non-key attributes cannot be dependent on a subset of the primary key. C. N. . Umadevi, Asst. Professor in C. S, Enathi Rajappa Arts and Science College, Pattukkottai, Thanjavur District 6

Second Normal Form (2 NF) (continued……) Example: Consider the following PURCHASE table: This table has a composite primary key [Customer ID, Store ID]. The non-key attribute is [Purchase Location]. In this case, [Purchase Location] only depends on [Store ID], which is only part of the primary key. Therefore, this table does not satisfy second normal form. Customer-id Shop-id Purchase-location 1 1 Chennai 1 3 Trichy 2 1 Chennai 3 2 Coimbatore 4 3 Trichy This table has a composite primary key [customer-id, shop-id] and the non-key attribute is [Purchase-location]. In this case, [Purchase Location] only depends on [Store ID], which is only part of the primary key. Therefore, this table does not satisfy second normal form. C. N. . Umadevi, Asst. Professor in C. S, Enathi Rajappa Arts and Science College, Pattukkottai, Thanjavur District 7

Second Normal Form (2 NF) (continued……) To bring this table to second normal form, we break the table into two tables, purchase and store. Purchase store Customer-id Shop-id Purchase-location 1 1 1 Chennai 1 3 3 Trichy 2 1 1 Chennai 3 2 2 Coimbatore 4 3 3 Trichy Thus the partial functional dependency is removed and both the tables are in 2 NF. C. N. . Umadevi, Asst. Professor in C. S, Enathi Rajappa Arts and Science College, Pattukkottai, Thanjavur District 8

Third Normal Form (3 NF): Defintion: A table is in third normal form if: 1. It must be in 2 NF. 2. It contains only columns that are non-transitively dependent on the primary key. Transitive Dependency: Let us consider the attributes a, b and c. They holds the following with following dependencies: a→b b→c then a→c C. N. . Umadevi, Asst. Professor in C. S, Enathi Rajappa Arts and Science College, Pattukkottai, Thanjavur District 9

Third Normal Form (3 NF) (continued……) Example: Let us consider the following employee table. 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 The keys of the employee table are listed below: Super keys: {emp_id}, {emp_id, emp_name, emp_zip}…so on Candidate Keys: {emp_id} Non-prime attributes: all attributes except emp_id are non-prime as they are not part of any candidate keys. C. N. . Umadevi, Asst. Professor in C. S, Enathi Rajappa Arts and Science College, Pattukkottai, Thanjavur District 10

Third Normal Form (3 NF) (continued……) §emp_state, emp_city and emp_district dependent on emp_zip. §emp_zip is dependent on emp_id that makes non-prime attributes (emp_state, emp_city & emp_district) transitively dependent on super key (emp_id). Thus the rule for 3 NF is violated. The employee table must be divided into two so as to remove the transtitive dependency. Employee emp_zip 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 C. N. . Umadevi, Asst. Professor in C. S, Enathi Rajappa Arts and Science College, Pattukkottai, Thanjavur District 11

Boyce Codd Normal Form (BCNF) Definition: Boyce-Codd Normal Form (BCNF) is an extension of Third Normal Form on strict terms. BCNF states that • For any non-trivial functional dependency, X → A, X must be a super-key. Example: Consider the following employee table in which employees work in more than one department. emp_id emp_nationality emp_dept_type dept_no_of_emp 1001 Austrian Production and planning D 001 200 1001 Austrian stores D 001 250 1002 American design and technical support D 134 1002 American Purchasing department D 134 600 C. N. . Umadevi, Asst. Professor in C. S, Enathi Rajappa Arts and Science College, Pattukkottai, Thanjavur District 12

BCNF (continued……) Functional dependencies in the table above: emp_id -> emp_nationality emp_dept -> {dept_type, dept_no_of_emp} Candidate key: {emp_id, emp_dept} The table is not in BCNF as neither emp_id nor emp_dept alone are keys. Employee table can be divided in to 3 to follow BCNF. Emp_nationality emp_dept_map emp_id emp_nationality emp_id emp_dept 1001 Austrian 1001 Production and planning 1002 American 1001 stores 1002 design and technical support 1002 Purchasing department C. N. . Umadevi, Asst. Professor in C. S, Enathi Rajappa Arts and Science College, Pattukkottai, Thanjavur District 13

BCNF (continued……) Emp-dept emp_dept_type dept_no_of_emp Production and planning D 001 200 stores D 001 250 design and technical support D 134 100 Purchasing department D 134 600 Functional dependencies: emp_id -> emp_nationality emp_dept -> {dept_type, dept_no_of_emp} Candidate keys: For first table: emp_id For second table: {emp_id, emp_dept} For third table: emp_dept This is now in BCNF as in both the functional dependencies left side part is a key. C. N. . Umadevi, Asst. Professor in C. S, Enathi Rajappa Arts and Science College, Pattukkottai, Thanjavur District 14

Fourth Normal Form (4 NF): Multivalued Dependency: Definition: Multivalued dependency occurs when there are more than one independent multivalued attributes in a table. Example: Consider the following table bike_model manuf_year color M 1001 2007 Black M 1001 2007 Red M 2012 2008 Black M 2012 2008 Red M 2222 2009 Black M 2222 C. N. . Umadevi, Asst. Professor in C. S, Enathi Rajappa Arts and Science College, 2009 Red Pattukkottai, Thanjavur District 15

Multivalued Dependency (continued……) Here columns manuf_year and color are independent of each other and dependent on bike_model. In this case these two columns are said to be multivalued dependent on bike_model. These dependencies can be represented like this: bike_model ->> manuf_year Fourth Normal Form (4 NF): Definintion: For a table to satisfy the Fourth Normal Form, it should satisfy the following two conditions: 1. It should be in the Boyce-Codd Normal Form. 2. And, the table should not have any Multi-valued Dependency. C. N. . Umadevi, Asst. Professor in C. S, Enathi Rajappa Arts and Science College, Pattukkottai, Thanjavur District 16

Fourth Normal Form (4 NF) (continued……) Consider the following table with multivalued dependency. Student s_id course hobby 1 Science Cricket 1 Maths Hockey 2 C# Cricket 2 Php Hockey As you can see in the table above, student with s_id 1 has opted for two courses, Science and Maths, and has two hobbies, Cricket and Hockey. The two records for student with s_id 1, will give rise to two more records, as shown below, because for one student, two hobbies exists, hence along with both the courses, these hobbies should be specified. C. N. . Umadevi, Asst. Professor in C. S, Enathi Rajappa Arts and Science College, Pattukkottai, Thanjavur District 17

Fourth Normal Form (4 NF) (continued……) There is multi-value dependency, which leads to un-necessary repetition of data and other anomalies as well. To eliminate multi-value dependency the student table must be divided into two: Stu_course stu_hobby s_id course 1 Science 1 Maths 2 C# 2 Php s_id hobby 1 Cricket 1 Hockey 2 Cricket 2 Hockey Now this relation satisfies the fourth normal form. C. N. . Umadevi, Asst. Professor in C. S, Enathi Rajappa Arts and Science College, Pattukkottai, Thanjavur District 18

THANK YOU C. N. . Umadevi, Asst. Professor in C. S, Enathi Rajappa Arts and Science College, Pattukkottai, Thanjavur District 19