06 Normalizing Database Designs â Part 1.pdf

21/3/2020 TMF2034 Database Concept and Design 1  Objectives By the end of the class, the student is able to – Name and explain the different anomalies state of database – Apply first, second and third normal form to improve the database logical...

21/3/2020 TMF2034 Database Concept and Design 1  Objectives By the end of the class, the student is able to – Name and explain the different anomalies state of database – Apply first, second and third normal form to improve the database logical design 21/3/2020 TMF2034 Database Concept and Design 2  Database Tables and Normalization Normalization – A process for evaluating and correcting table structures to minimize data redundancies Reduces data anomalies – Series of stages called normal forms: First normal form (1NF) Second normal form (2NF) Third normal form (3NF) 3 21/3/2020 TMF2034 Database Concept and Design  Database Tables and Normalization (cont’d.) Normalization (continued) – 2NF is better than 1NF; 3NF is better than 2NF – Highest level of normalization is not always most desirable De-normalization produces a lower normal form – Increased performance but greater data redundancy 4 21/3/2020 TMF2034 Database Concept and Design  The Need for Normalization Eliminate anomalies – To eliminate data redundancy – To ensure data consistency Used while designing and re-designing a database structure – Analyzes the relationship among the attributes within each entity – Determines if the structure can be improved 21/3/2020 TMF2034 Database Concept and Design 5  The Need for Normalization (cont’d.) 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, report is generated that contains information such as displayed in Table 9.1 6 21/3/2020 TMF2034 Database Concept and Design 7 21/3/2020 TMF2034 Database Concept and Design 8  Anomalies Update anomaly Delete anomaly Insert anomaly 21/3/2020 TMF2034 Database Concept and Design 9  StudentNum CourseNum Student Address Course Name S21 9201 Jones Edinburgh Accounts S21 9267 Jones Edinburgh Physics S24 9267 Smith Glasgow Physics S30 9201 Richards Manchester Accounts S30 9322 Richards Manchester Maths 21/3/2020 TMF2034 Database Concept and Design 10  The Need for Normalization (cont’d.) Structure of data set in Table 9.1 does not handle data very well Table structure appears to work; report is generated with ease Report may yield different results depending on what data anomaly has occurred Relational database environment is suited to help designer avoid data integrity problems 11 21/3/2020 TMF2034 Database Concept and Design  The Normalization Process Objective is to ensure that each table conforms to the concept of well- formed relations – Each table represents a single subject – No data item will be unnecessarily stored in more than one table – All nonprime attributes in a table are dependent on the primary key – Each table is void of insertion, update, and deletion anomalies 12 21/3/2020 TMF2034 Database Concept and Design  The Normalization Process (cont’d.) Objective of normalization is to ensure that all tables are in the highest form Higher forms are not likely to be encountered in business environment Normalization works on one relation at a time Progressively breaks table into new set of relations based on identified dependencies 13 21/3/2020 TMF2034 Database Concept and Design  Normal Forms 21/3/2020 TMF2034 Database Concept and Design 14  Functional Dependence Concepts Concept Definition Functional dependence The attribute B is fully functionally Given R(A,B,C,D) dependent on the attribute A if each value AàBCD, BCàD of A determines one and only one value of B. Functional dependence Attribute A determines attribute B if all of (Generalized definition) the rows in the table that agree in value for attribute A also agree in value for attribute B. Fully functional dependence If attribute C is functionally dependent on (composite key) a composite key AB but not on any subset of that composite key (just A or just B), the attribute C is fully functionally dependent on AB. 21/3/2020 TMF2034 Database Concept and Design 15 21/3/2020 TMF2034 Database Concept and Design 16 17  Types of Functional Dependencies Partial dependency – Exists when there is a functional dependence in which the determinant is only part of the primary key – Assumption - One candidate key – Straight forward – Easy to identify Transitive dependency – Exists when there are functional dependencies such that X → Y, Y → Z, and X is the primary key – An attribute functionally depends on another nonkey attribute 18 21/3/2020 TMF2034 Database Concept and Design 21/3/2020 TMF2034 Database Concept and Design 19  Characteristic 21/3/2020 TMF2034 Database Concept and Design 20  Conversion to First Normal Form Steps 1. Eliminate the repeating groups 2. Identify the primary key 3. Identify all dependencies Repeating group – Group of multiple entries of same type can exist for any single key attribute occurrence Existence proves the presence of data redundancies – Relational table must not contain repeating groups – Enable reducing data redundancies 21 21/3/2020 TMF2034 Database Concept and Design  Repeating Group StudentName StudentID StudentTel1 StudentTel2 StudentTel3 StudentName StudentID Course1 Course2 Course3 21/3/2020 TMF2034 Database Concept and Design 22  Conversion to First Normal Form (cont’d.) Identify primary key – Remember: primary key can be an attribute, or combination of attributes Use a dependency diagram to depicts all dependencies – Helps to get an overview of all relationships among table’s attributes – Makes it less likely that an important dependency will be overlooked 21/3/2020 TMF2034 Database Concept and Design 23 24 25 26 27 21/3/2020 TMF2034 Database Concept and Design 28  Characteristic 21/3/2020 TMF2034 Database Concept and Design 29  Conversion to Second Normal Form Step 1: Make New Tables to Eliminate Partial Dependencies – Write each key component on separate line, then write original (composite) key on last line – Each component will become key in new table Step 2: Reassign Corresponding Dependent Attributes – Determine attributes that are dependent on other attributes – At this point, most anomalies have been eliminated 30 21/3/2020 TMF2034 Database Concept and Design 31 32 21/3/2020 TMF2034 Database Concept and Design 33  Characteristic 21/3/2020 TMF2034 Database Concept and Design 34  Conversion to Third Normal Form Step 1: Make New Tables to Eliminate Transitive Dependencies – For every transitive dependency, write its determinant as PK for new table – Determinant: any attribute whose value determines other values within a row 35 21/3/2020 TMF2034 Database Concept and Design  Conversion to Third Normal Form (cont’d.) Step 2: Reassign Corresponding Dependent Attributes – Identify attributes dependent on each determinant identified in Step 1 Identify dependency – Name table to reflect its contents and function 36 21/3/2020 TMF2034 Database Concept and Design 37  Improving the Design Table structures should be cleaned up to eliminate initial partial and transitive dependencies Normalization cannot, by itself, be relied on to make good designs Valuable because it helps eliminate data redundancies 38 21/3/2020 TMF2034 Database Concept and Design  Improving the Design (cont’d.) Issues to address, in order, to produce a good normalized set of tables: – Evaluate PK Assignments (JOB_CLASSàCHG_HOUR) – Evaluate Naming Conventions – Refine Attribute Atomicity – Identify New Attributes 39 21/3/2020 TMF2034 Database Concept and Design  Improving the Design (cont’d.) – Identify New Relationships (identify project manager) – Refine Primary Keys as Required for Data Granularity – Maintain Historical Accuracy – Evaluate Using Derived Attributes 40 21/3/2020 TMF2034 Database Concept and Design 21/3/2020 TMF2034 Database Concept and Design 41  42 21/3/2020 TMF2034 Database Concept and Design 21/3/2020 TMF2034 Database Concept and Design 43 21/3/2020 TMF2034 Database Concept and Design 44  Surrogate Key Considerations When primary key is considered to be unsuitable, designers use surrogate keys Data entries in Table 9.4 are inappropriate because they duplicate existing records – No violation of entity or referential integrity 45 21/3/2020 TMF2034 Database Concept and Design  46 TMF2034 Database Concept and Design 21/3/2020  Unnormalized Table TMF2034 Database Concept and Design 47 21/3/2020  1NF TMF2034 Database Concept and Design 48 21/3/2020  2NF TMF2034 Database Concept and Design 49 21/3/2020  3NF TMF2034 Database Concept and Design 50 21/3/2020 21/3/2020 TMF2034 Database Concept and Design 51