Database Normalization Lecture Notes PDF

5 Lecture 7 Normalization of Database Tables 1  5 Database Tables and Normalization Normalization – Process for evaluating and correcting table structures to minimize data redundancies...

5 Lecture 7 Normalization of Database Tables 1  5 Database Tables and Normalization Normalization – Process for evaluating and correcting table structures to minimize data redundancies helps eliminate data anomalies – Works through a series of stages called normal forms: First normal form (1NF) Second normal form (2NF) Third normal form (3NF) 2 A Sample Report Layout 5 3  5 Data Anomalies Anomalies are problems that can occur in poorly planned, un-normalized databases where all the data is stored in one table (a flat-file database). – Insertion Anomaly - The nature of a database may be such that it is not possible to add a required piece of data unless another piece of unavailable data is also added. E.g. A database that cannot store the details of a new employee until that employee is assigned to a department that doesn’t exist. 4  5 Data Anomalies – Deletion Anomaly - A record of data can be deleted from a database, and the deletion can result in the deletion of the only instance of other, required data, E.g. Deleting a book loan from a library member can remove all details of the particular book from the database such as the author, book title etc. – Modification Anomaly - Incorrect data may have to be changed, which could involve many records having to be changed, leading to the possibility of some changes being made incorrectly. 5  5 Database Tables and Normalization (continued) – 2NF is better than 1NF; 3NF is better than 2NF – For most business database design purposes, 3NF is the highest we need to go in the normalization process – Highest level of normalization is not always most desirable 6  5 The Need for Normalization Example: company that manages building projects – Charges its clients by billing hours spent on each contract – Hourly billing rate is dependent on employee’s position – Periodically, a report is generated that contains information displayed in Table 5.1 7  A Sample Report Layout 5 Eliminate Derived Data 8 5 9  5 Figure 5.1 Observations PROJ_NUM intended to be primary key Table entries invite data inconsistencies Table displays data anomalies – Update Modifying JOB_CLASS – Insertion New employee must be assigned project – Deletion If employee deleted, other vital data lost 10  5 The Need for Normalization (continued) Structure of data set in Figure 5.1 does not handle data very well The table structure appears to work; report is generated with ease Unfortunately, the report may yield different results, depending on what data anomaly has occurred 11  5 Conversion to First Normal Form Repeating group – Derives its name from the fact that a group of multiple (related) entries can exist for any single key attribute occurrence Relational table must not contain repeating groups Normalizing the table structure will reduce these data redundancies Normalization is three-step procedure 12  5 Step 1: Eliminate the Repeating Groups Present data in a tabular format, where each cell has a single value and there are no repeating groups Eliminate repeating groups by eliminating nulls, making sure that each repeating group attribute contains an appropriate data value 13  5 14  5 Step 2: Identify the Primary Key Primary key must uniquely identify attribute value New key may be composed 15  5 Step 3: Identify all Dependencies Dependencies can be depicted with the help of a diagram Dependency diagram: – Depicts all dependencies found within a given table structure – Helpful in getting bird’s-eye view of all relationships among a table’s attributes 16  5 Steps of Conversion to 1NF Repeating groups must be eliminated Proper primary key developed – Uniquely identifies attribute values (rows) – Combination of PROJ_NUM and EMP_NUM Dependencies can be identified – Desirable dependencies based on primary key – Less desirable dependencies Partial Dependency – based on part of composite primary key Transitive Dependency – one nonprime attribute depends on another nonprime attribute 17  5 18  5 A Dependency Diagram: First Normal Form (1NF) 19  5 First Normal Form Properties Tabular format in which: – All key attributes are defined – There are no repeating groups in the table – All attributes are dependent on primary key All relational tables satisfy 1NF requirements Some tables contain dependencies 20  5 Steps of Conversion to 2NF 1. Start with 1NF format 2. Write each key component on separate line 3. Write original key on last line 4. Each component is new table 5. Each component will become the key in a new table 6. Write dependent attributes after each key 7. Determine which attributes are dependent on which other attributes 21  5 Second Normal Form (2NF) Conversion Results 22  5 Second Normal Form Properties Table is in second normal form (2NF) if: – It is in 1NF and – It includes no partial dependencies: No attribute is dependent on only a portion of the primary key May contain Transitive dependencies 23  5 Steps of Conversion to 3NF Create separate table(s) to eliminate transitive functional dependencies 24  5 Third Normal Form (3NF) Conversion Results 25  5 Third Normal Form Properties A table is in third normal form (3NF) if: – It is in 2NF and – It contains no transitive dependencies 26  5 Improving the Design Table structures are cleaned up to eliminate the troublesome initial partial and transitive dependencies Normalization cannot, by itself, be relied on to make good designs It is valuable because its use helps eliminate data redundancies 27  5 Improving the Design (continued) The following changes may be made: – PK assignment – Naming conventions – Attribute atomicity – Adding attributes – Adding relationships – Refining PKs – Maintaining historical accuracy – Using derived attributes 28 The Completed Database 5 29  5 The Completed Database (continued) 30  5 The Completed Database (continued) 31  5 The Boyce-Codd Normal Form (BCNF) Every determinant in the table is a candidate key – Has same characteristics as primary key, but for some reason, not chosen to be primary key If a table contains only one candidate key, the 3NF and the BCNF are equivalent BCNF can be violated only if the table contains more than one candidate key 32 The Boyce-Codd Normal Form (BCNF) 5 (cont.) Most designers consider the Boyce-Codd normal form (BCNF) as a special case of 3NF A table is in 3NF if it is in 2NF and there are no transitive dependencies. A transitive dependency exists when one nonprime attribute is dependent on another nonprime attribute. A table can be in 3NF and not be in BCNF – A nonkey attribute is the determinant of a key attribute 33  5 A Table That is in 3NF but not in BCNF 34  5 Sample Data for a BCNF Conversion 35  5 Decomposition to BCNF 36 Another BCNF Decomposition 5 37 Exercise: 5 Assume the following a table, 1.Convert the above table to a BCNF. 2. Draw and ER diagram showing the relationships between the generated relations. 38