Chapter 4In this chapter, you will learn:Database Tables and NormalizationNeed for NormalizationFigure 4.1 ObservationsConversion to 1NFDependency Diagram (1NF)Data Organization: 1NF1NF SummarizedConversion to 2NF2NF Conversion Results2NF SummarizedConversion to 3NF3NF SummarizedAdditional DB EnhancementsBoyce-Codd Normal Form (BCNF)3NF Table Not in BCNFDecomposition of Table Structure to Meet BCNFDecomposition into BCNFNormalization and Database DesignInitial ERD for Contracting CompanyModified ERD for Contracting CompanyFinal ERD for Contracting CompanyHigher-Level Normal FormsConversion to 4NFDenormalizationUnnormalized Table Defects4Chapter 4Normalization of Database TablesDatabase Systems: Design, Implementation, and Management, Fifth Edition, Rob and CoronelDatabase Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel24In this chapter, you will learn:•What normalization is and what role it plays in database design•About the normal forms 1NF, 2NF, 3NF, BCNF, and 4NF •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•That some situations require denormalization to generate information efficientlyDatabase Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel34Database Tables and Normalization•Table is basic building block in database design•Normalization is process for assigning attributes to entities–Reduces data redundancies–Helps eliminate data anomalies–Produces controlled redundancies to link tables•Normalization stages–1NF - First normal form–2NF - Second normal form–3NF - Third normal form–4NF - Fourth normal formDatabase Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel44Need for NormalizationFigure 4.1Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel54Figure 4.1 Observations•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 lostDatabase Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel64Conversion 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 attributeDatabase Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel74Dependency Diagram (1NF)Figure 4.4Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel84Data Organization: 1NFFigure 4.3Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel941NF Summarized•All key attributes defined•No repeating groups in table•All attributes dependent on primary keyDatabase Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel104Conversion to 2NF•Start with 1NF format:•Write each key component on separate line•Write original key on last line•Each component is new table•Write dependent attributes after each keyPROJECT (PROJ_NUM, PROJ_NAME)EMPLOYEE (EMP_NUM, EMP_NAME, JOB_CLASS, CHG_HOUR)ASSIGN (PROJ_NUM, EMP_NUM, HOURS)Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel1142NF Conversion ResultsFigure 4.5Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel1242NF 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 attributesDatabase Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel134Conversion 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)Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel1443NF Summarized•In 2NF•Contains no transitive dependenciesDatabase Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel154Additional DB EnhancementsFigure 4.6Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel164Boyce-Codd Normal Form (BCNF)•Every determinant in the table is a candidate key–Determinant is attribute whose value determines other values in row–3NF table with one candidate key is already in BCNFDatabase Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel1743NF Table Not in BCNFFigure 4.7Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel184Decomposition of Table Structure to Meet BCNFFigure 4.8Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel194Decomposition into BCNFFigure 4.9Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel204Normalization 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 determinedDatabase Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel214Initial ERD for Contracting CompanyFigure 4.10Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel224Modified ERD for Contracting CompanyFigure 4.11Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel234Final ERD for Contracting CompanyFigure 4.12Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel244Higher-Level Normal Forms•Fourth Normal Form (4NF)–Table is in 3NF–Has no multiple sets of multivalued dependenciesDatabase Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel254Conversion to 4NFFigure 4.14Multivalued DependenciesFigure 4.15Set of Tables in 4NFDatabase Systems: Design, Implementation, & Management, 5th Edition, Rob &