1 / 18
Télécharger la présentation

Chapter 4

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Chapter 4 Normalization Hachim Haddouti

  2. In this chapter, you will learn: • What normalization is and what role it plays in database design • About the normal forms 1NF, 2NF, 3NF • How normal forms can be transformed from lower normal forms to higher normal forms • That normalization and E-R modeling are used concurrently to produce a good database design Hachim Haddouti, CH4, see also Rob & Coronel

  3. Database Tables and Normalization • Normalization is process for assigning attributes to entities • Reduces data redundancies (remember our discussion about file systems vs. DBMS) • Helps eliminate data anomalies • Produces controlled redundancies to link tables • Normalization stages • 1NF - First normal form • 2NF - Second normal form • 3NF - Third normal form Hachim Haddouti, CH4, see also Rob & Coronel

  4. Need for Normalization • PRO_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 Hachim Haddouti, CH4, see also Rob & Coronel

  5. 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: based on part of composite primary key • Transitive: one nonprime attribute depends on another nonprime attribute Hachim Haddouti, CH4, see also Rob & Coronel

  6. Data Organization: 1NF Hachim Haddouti, CH4, see also Rob & Coronel

  7. 1NF Other Example Unnormalized Table Hachim Haddouti, CH4, see also Rob & Coronel

  8. 1NF Example (con’t.) Conversion to 1NF Hachim Haddouti, CH4, see also Rob & Coronel

  9. 1NF Summarized • All key attributes defined • No repeating groups in table (atomic attributes) • All attributes dependent on primary key • BUT • Redundancy • Update Anomalies • Update, inconsistent data, additions, deletions • Occur because a column is dependent on a portion of a multi-column primary key Hachim Haddouti, CH4, see also Rob & Coronel

  10. 2NF Summarized • In 1NF • Includes no partial dependencies • No attribute dependent on a portion of primary key • Still possible to exhibit transitive dependency • Attributes may be functionally dependent on nonkey attributes Hachim Haddouti, CH4, see also Rob & Coronel

  11. Conversion to 2NF • Write each key component on separate line adn then write the composite key on the last line • PROJ_NUM • EMP_NUM • PROJ_NUM and EMP_NUM • Each component become the key in a new table (Project, Employee, Assign • Write the dependent attributes after each key • POJECT (PROJ_NUM, PROJ_NAME) • EMPLOYEE(EMP_NUM, EMP_Name, JOB_CLASS, CHG_HOUR) • ASSIGN (PROJ_NUM, EMP_NUM, ASSING_HOURS) Hachim Haddouti, CH4, see also Rob & Coronel

  12. 2NF Conversion Results Hachim Haddouti, CH4, see also Rob & Coronel

  13. 2 NF summarized • Most anomalies are eliminated, eg. Change, delete or add in a PROJECT record, need to go only to the PROJECT table • But still transitive dependencies (Attributes may be functionally dependent on nonkey attributes) and could cause anomalies, such as if CHR_HOUR changes for JOB_CLASS  change has to be done for each employee of that JOB_CLASS Hachim Haddouti, CH4, see also Rob & Coronel

  14. 3NF Summarized • In 2NF • Contains no transitive dependencies Hachim Haddouti, CH4, see also Rob & Coronel

  15. Conversion to 3NF • Create separate table(s) to eliminate transitive functional dependencies PROJECT (PROJ_NUM, PROJ_NAME) ASSIGN (PROJ_NUM, EMP_NUM, HOURS) EMPLOYEE (EMP_NUM, EMP_NAME, JOB_CLASS) JOB (JOB_CLASS, CHG_HOUR) Hachim Haddouti, CH4, see also Rob & Coronel

  16. Normal Forms Summary Hachim Haddouti, CH4, see also Rob & Coronel

  17. Normalization and Database Design • Normalization should be part of the design process • E-R Diagram provides macro view • Normalization provides micro view of entities • Focuses on characteristics of specific entities • May yield additional entities • Difficult to separate normalization from E-R diagramming • Business rules must be determined Hachim Haddouti, CH4, see also Rob & Coronel

  18. Denormalization • Normalization is one of many database design goals • Normalized table requirements • Additional processing • Loss of system speed • Normalization purity is difficult to sustain due to conflict in: • Design efficiency • Information requirements • Processing Hachim Haddouti, CH4, see also Rob & Coronel

More Related