Chapter 7 - Normalization of Database Tables

Chapter Overview

  • Title: Normalization of Database Tables

  • Authors: Carlos Coronel, Steven Morris

  • Edition: 13th

Learning Objectives

  • Understand normalization and its importance in database design.

  • Identify and describe normal forms: 1NF, 2NF, 3NF, BCNF, and 4NF.

  • Transform normal forms and apply normalization rules to evaluate and correct table structures.

  • Recognize the need for denormalization for efficiency.

  • Use a data-modeling checklist to evaluate ERD's compliance with standards.

Normalization and Its Importance

  • Normalization: A process focused on evaluating and correcting table structures to minimize data redundancy and anomalies.

  • Data anomalies are inconsistencies that may arise due to poor design.

  • Different levels of normal forms exist:

    • 1NF (First Normal Form)

    • 2NF (Second Normal Form)

    • 3NF (Third Normal Form)

  • Higher normal forms are considered better than lower ones, with 4NF aiming to eliminate more complex dependencies.

The Normalization Process

  1. Table Composition:

    • Each table should represent a single subject.

    • Each row/column intersection must contain a single value.

    • No data should be redundantly stored across multiple tables.

    • Nonprime attributes in a table should depend entirely on the primary key.

  2. Elimination of Anomalies:

    • Prevent insertion, update, and deletion anomalies through proper design.

Normal Forms

  • 1NF Characteristics:

    • Tabular format without repeating groups.

    • Primary key defined, all attributes depend on it.

  • 2NF Characteristics:

    • Must be in 1NF.

    • No partial dependencies present.

  • 3NF Characteristics:

    • Must be in 2NF.

    • No transitive dependencies exist.

  • BCNF (Boyce-Codd Normal Form):

    • Every determinant in the table should be a candidate key.

    • Specialized case of 3NF.

  • 4NF Characteristics:

    • Must be in 3NF.

    • No independent multivalued dependencies.

Functional Dependencies

  • Definition: Dependence of one attribute on another.

  • Full functional dependence means attribute A determines attribute B uniquely.

  • Differentiates between functional dependence, partial dependency, and transitive dependencies:

    • Partial Dependency: Determinant is part of a composite key.

    • Transitive Dependency: A dependency on another non-key attribute.

Conversion Processes

  1. To 1NF:

    • Eliminate repeating groups, define primary key, identify dependencies.

  2. To 2NF:

    • Requires that 1NF has a composite key; create new tables for partial dependencies.

  3. To 3NF:

    • Make new tables for transitive dependencies.

Design Improvement Strategies

  • Ensure normalization is part of the database design process to eliminate redundancies.

  • Refine data models, primary keys, and relationships for accuracy and efficiency.

  • Maintain a focus on historical accuracy while making adjustments for derived attributes.

Surrogate Keys

  • Used when primary keys are unsuitable.

  • System-defined, managed by the DBMS, and automatically incremented.

Normalization in Practice

  • Crucial component of designing effective databases.

  • Percentage of tables must meet higher normal forms for efficiency.

  • Normalization highlights the need for careful attribute and relationship management.

Denormalization

  • Aspects where normalization may be relaxed for performance:

    • May involve redundant data for efficiency in join operations.

    • Derived data may be stored for quick access without recalculation.

Data-Modeling Checklist

  • Business rules documentation and proper naming conventions are critical.

  • Attributes and relationships must be distinctly identified and structured to eliminate redundancies.

  • Ensure all entities conform to the 3NF or higher.

  • Minimize redundancies through tightly controlled relationships and defined attributes.

Key Takeaways

  • Normalization reduces the chance of anomalies and data redundancy while ensuring structural integrity.

  • The effective use of normalization and denormalization techniques can balance data integrity with performance needs.