Database Normalization

1. Database Normalization Lecture – 5 Course Code – MIS4102

2. Introduction • Edgar F. Codd, the inventor of the relational model, introduced the concept of normalization and what we now know as the First Normal Form (1NF) in 1970. • Coddwent on to define the Second Normal Form (2NF) and Third Normal Form (3NF) in 1971,and Codd and Raymond F. Boyce defined the Boyce-Codd Normal Form (BCNF) in 1974. • Higher normal forms were defined by other theorists in subsequent years, Fagin introduced Forth and Fifth normal form (Fagin 1977, 1979). • The most recent being the Sixth Normal Form (6NF) introduced by Chris Date, Hugh Darwen, and Nikos Lorentzos in 2002.

3. Why Normalization? • The step-by-step process of identifying and eliminating data redundancies and inconsistencies is called normalization. • Tables are the basic building blocks of the database, so good database design must be matched with good table structures. • Normalization enables us to recognize bad table structures and allow us to create good table structures.

4. Redundancy & Anomalies • Data redundancy = data stored in several places • Too much data redundancy causes problems--which value is correct? • Data integrity and consistency suffer • Data anomaly = abnormal data relationships • Insertion anomaly - Can’t add data because don’t know entire primary key value, e.g., primary key based on first, middle, and last name • Deletion anomaly - Deletions result in too many fields being removed unintentionally, e.g., delete an employee but lose transaction data • Update anomaly - Change requires many updates, e.g., if you store customer names in transaction tables

5. Normalization (Cont…) • Normalization stages are called normal forms (1NF, 2NF and 3NF) each better than previous (less anomalies/redundancy). • Highest level not always the most desirable. • Most professionally designed databases reach third normal form. • Fourth and Fifth normal forms are seldom used.

6. Non-loss Decomposition • The process of transforming an un- normalized data set into a fully normalized database is frequently referred to as a process of non-loss decomposition. • This is because we continually fragment our data structure into more tables without losing the fundamental relationships between data items.

7. Normalization Example • To recognize good design, first look at bad one • Example, construction company manages several projects and whose charges are dependent on employee’s position.

8. Desired Report

9. Table view of the previous report

10. Problems • P_No intended to be primary key but contains null values • Data redundancies • Invites data inconsistencies (Elect Eng & EE) • Wastes data entry time, wastes storage space • Anomalies • Update anomaly – modify Job_Class for E_No 101 requires many alterations • Insert anomaly – to add a project row we need an employee • Deletion anomaly – delete E_No 101, we delete other vital data too

11. Problems (cont…) • Table above has repeating groups • Each P_No has a group of entries

12. Conversion to 1NF • Eliminate repeating groups • By adding entries in primary key column

13. Problems • Primary key P_No does not uniquely identify all attributes in row • Must create composite key made up of P_No & E_No

14. Dependency Diagram • Helps us to discover relationships between entity attributes • Upper arrows implies dependency on P_No & E_No • Lower arrows implies dependency on only one attribute

15. Dependencies • Upper arrows • If you know P_No & E_No you can determine the other row values • Lower arrows • Partial dependencies – based on only part of key • P_Name only dependent on P_No • E_Name, Job_Class, Chg_Hr only dependent on E_No • Dependency diagram may be written: • P_No, E_No  P_Name, E_Name, Job_Class, Chg_Hr, Hrs • P_No  P_Name • E_No  E_Name, Job_Class, Chg_Hr

16. New Table (1NF) • Composite primary key P_No & E_No Charges Table

17. 1NF Definition • All the key attributes are defined • Any attribute that is part of the primary key • There are no repeating groups in the table • Each cell can contain one and only one value, rather than set • All attributes are dependent on the primary key

18. Problem - Partial Dependencies • Contains partial dependencies • Dependencies base on only part of the primary key • This makes table subject to data redundancies and hence to data anomalies • Redundancy caused by fact that every row entry requires duplicate data • E.g., suppose E_No 104 is entered 20 times, must also enter E_Name, Job_Class, Chg_Hr • Anomalies caused by redundancy • E.g., employee name may be spelled Didar Ahmed, Dedar Ahmed, Diader Ahmad or D. Ahmed

19. Conversion to 2NF 1. Starting with 1NF write each of the key components on separate lines, then write the original key on the last line P_No E_No P_No E_No • Each will become key in a new table • Here, original table split into three tables

20. Conversion to 2NF (cont…) 2. Write the dependent attributes after each of the new keys using the dependency diagram P_No  P_Name E_No  E_Name, Job_Class, Chg_Hr P_No E_No  Hrs

21. Three New Tables (2NF) Assign Table Project Table Employee Table

22. 2NF Definition • Table is in 1NF and • It includes no partial dependencies (no attribute is dependent on only a portion of the primary key) **Note: Since partial dependencies can exist only if there is a composite key, a table with a single attribute as primary key is automatically in 2NF if it is in 1NF

23. Problem - Transitive Dependency • Note that Chg_Hr is dependent on Job_Class, but neither Chg_Hr nor Job_Class is part of the primary key • This is called transitive dependency • A condition in which an attribute is functionally dependent on non-key attributes (another attribute that is not part of the primary key) • Transitive dependency yields data anomalies

24. Conversion to 3NF • Break off the pieces that are identified by the transitive dependency arrows (lower arrows) in the dependency diagram • Store them in a separate table P_No  P_Name E_No  E_Name, Job_Class P_No E_No  Hrs Job_Class  Chg_Hr **Note: Job_Class must be retained in Employee table to establish a link to the newly created Job table

25. New Tables (3NF) Project Table Assign Table Employee Table Job Table

26. 3NF Definition • Table is in 2NF and • It contains no transitive dependencies

27. Problem • Although the four tables are in 3NF, we have a potential problem • The Job_Class is entered for each new employee in the Employee table • For example, too easy to enter Electrical Engr, or EE, or El Eng Employee Table

28. New Attribute • Create a Job_Code attribute to serve as primary key in the Job table and as a foreign key in the Employee table

29. New Tables Project Table Assign Table Employee Table Job Table

30. Another Example…

31. The Problem:Keeping Track of a Stack of Invoices

32. Required Table

33. First Normal Form:No Repeating Elements or Groups of Elements • NF1 addresses two issues: 1. A row of data cannot contain repeating groups of similar data (atomicity) 2. Each row of data must have a unique identifier (or Primary Key)

34. Second Normal Form:No Partial Dependencies on a Concatenated Key • Here we test each table for partial dependencies on a concatenated key. • This means that for a table that has a concatenated primary key, each column in the table that is not part of the primary key must depend upon the entire concatenated key for its existence. • If any column only depends upon one part of the concatenated key, then we say that the entire table has failed Second Normal Form and we must create another table to rectify the failure.

35. Checking Partial Dependencies

36. Second Normal Form:

37. Third Normal Form:No Dependencies on Non-Key Attributes • Here, we return to the problem of the repeating customer information. As our database now stands, if a customer places more than one order then we have to input all of that customer's contact information again. This is because there are columns in the orders table that rely on "non-key attributes".

38. Third Normal Form:

39. Summary • 1NF – Eliminate repeating groups • 2NF – Eliminate partial dependencies • 3NF – Eliminate transitive dependencies

40. End Thanks to All