Section 3.3: Normalization

Relational Database Normalization

Normalization is a systematic, mathematical process used in relational database design to organize tables in a manner that minimizes data redundancy and eliminates structural update anomalies. Developed by Edgar F. Codd, the pioneer of the relational model, normalization breaks down large, complex tables into smaller, well-structured tables that represent distinct business concepts. Relationships between these tables are then established using foreign keys.

---

Data Anomalies: The Consequence of Poor Design

Without normalization, data tables often suffer from redundancy, which leads to three primary types of data anomalies:

1. Insertion Anomaly: Occurs when a user cannot insert a record because some required data is not yet known or does not exist.
   • Example: If a single table combines Course and Instructor details, you cannot add a new course if no instructor has been assigned yet (assuming Instructor ID is part of a composite primary key or mandatory).
2. Update (or Modification) Anomaly: Occurs when data is duplicated across multiple rows. If that data changes, every duplicate row must be updated. If any row is missed, the database is left in an inconsistent state.
   • Example: If an instructor's phone number is stored in every row representing a class they teach, updating their phone number requires updating dozens of rows.
3. Deletion Anomaly: Occurs when deleting a record unintentionally destroys other unrelated business data that exists only in that record.
   • Example: If deleting a student's enrollment record also deletes the only copy of the course's description because student and course details are combined in one table.

---

The Progressive Normal Forms

Normalization is applied progressively, with each higher normal form building on the rules of the previous ones. For the CDMP exam, you must master the definitions and mechanics of First, Second, Third, and Boyce-Codd Normal Forms.

First Normal Form (1NF)

A table is in First Normal Form (1NF) if:

1. All attributes contain only atomic (indivisible) values. There are no multi-valued attributes or comma-separated lists of values within a single cell.
2. There are no repeating groups (e.g., having columns like Phone1, Phone2, Phone3).
3. Each row is uniquely identifiable by a primary key.

Worked Example: A Customer table contains a column Phone Numbers with the value "555-0199, 555-0143". To achieve 1NF, you must split these into separate rows or, ideally, move phone numbers to a separate Customer Phone table where each row contains a single phone number.

Second Normal Form (2NF)

A table is in Second Normal Form (2NF) if:

1. It is already in 1NF.
2. It has no partial dependencies. This means that if the primary key is a composite key (made up of multiple columns), every non-key column must depend on the entire key, not just a part of it.

Note: If a table has a single-column primary key, it is automatically in 2NF if it is in 1NF, because partial dependency is mathematically impossible.

Worked Example: In an Order Line table with a composite key of {Order ID, Product ID}, we have attributes Quantity and Product Description. Quantity depends on both the order and the product (valid 2NF). However, Product Description depends only on Product ID (part of the key). To resolve this partial dependency, Product Description must be moved to a separate Product table.

Third Normal Form (3NF)

A table is in Third Normal Form (3NF) if:

1. It is already in 2NF.
2. It contains no transitive dependencies. A transitive dependency occurs when a non-key attribute determines another non-key attribute (e.g., Customer ID determines Zip Code, which determines State). In other words, non-key columns must depend only on the primary key.

A common mnemonic used by data modelers is that every non-key attribute must depend on "the key, the whole key, and nothing but the key, so help me Codd."

Worked Example: In an Employee table with Employee ID as the primary key, we have the columns Department ID and Department Name. The Department Name depends on the Department ID (a non-key attribute), which in turn depends on the Employee ID. This is a transitive dependency. To achieve 3NF, the Department Name must be moved to a separate Department table.

Boyce-Codd Normal Form (BCNF)

A table is in Boyce-Codd Normal Form (BCNF) if:

1. It is in 3NF.
2. For every functional dependency X -> Y, the determinant X must be a superkey (a candidate key or a primary key).

BCNF is a stronger variation of 3NF. It resolves edge cases where a table has multiple, overlapping composite candidate keys. For example, if a table has candidate keys {A, B} and {A, C}, and there is a dependency where C -> B, the table is in 3NF but violates BCNF because C is not a superkey.

---

Pragmatic Denormalization and Read Optimization

While normalization is the standard for transactional systems (Online Transaction Processing - OLTP) where data integrity and write performance are paramount, it can create performance bottlenecks in analytics and reporting databases (Online Analytical Processing - OLAP). Because normalized databases distribute data across dozens of tables, retrieving complete data requires executing resource-intensive table joins.

To optimize query speeds, database architects selectively perform denormalization.

Reasons for Denormalization:

• Query Performance: Minimizes the number of joins needed for complex reports.
• Pre-computed Aggregations: Storing calculated columns (e.g., Order Total or Year-to-Date Sales) instead of calculating them on the fly.
• Data Archiving: Freezing historical data so that it remains static, even if master records change.

The Cost of Denormalization:

Denormalization is not a shortcut; it is a calculated risk. It introduces data redundancy, meaning the application layer or database triggers must now manage data synchronization to prevent update anomalies. If a denormalized attribute is updated in one table, the database must write update logic to ensure all duplicate locations are updated, increasing write latency.