Applying Normal Form

---

Normalization

• Occasionally, implementing entities, relationships, and attributes generates tables that contain redundancy
  • this redundancy is eliminated with normalization

Normalization eliminates redundancy by decomposing a table into two or more tables in higher normal form.

• usually normalize to Boyce-Codd normal form
  • if column A depends on column B, then B must be unique
• normalizing to Boyce-Codd normal form:
  1. List all unique columns
     • unique columns can be simple or composite
     • Composite columns must be minimal
       • remove any columns that are not necessary for uniqueness
  2. Identify dependencies on non-unique columns
     • non-unique columns are either
       • external to all unique columns
       • or contained within a composite unique column
  3. Eliminate dependencies on non-unique columns
     • if column A depends on a non-unique column B, A is removed from the original table
     • a new table is created containing A and B
     • B is a primary key in the new table and a foreign key in the original table

Denormalization

• Boyce-Codd normal form is ideal for tables with frequent inserts, updates, and deletes
• reporting databases may contain tables that, by design, are not in third normal form
  • changes are infrequent and redundancy is acceptable for reporting purposes

Denormalization means intentionally introducing redundancy by merging tables.

• eliminates join queries and therefore improves query performance
• results in first and second normal form tables
• should be applied selectively and cautiously

Denormalization Example

• Booking, Passenger, and Fare tables are denormalized into a single Booking table
• red highlight indicates redundancy

Database Design

• As tables and keys are specified, the database designer reviews each table for Boyce-Codd normal form
• Dependencies and unique columns are identified
• If any dependencies are not on unique columns,
  • the table is decomposed into smaller tables in Boyce-Codd normal form
• Tables that experience infrequent inserts, updates, and deletes may be denormalized to simplify and accelerate join queries

Applying Normal Form

Step	Activity
8A	Identify dependencies on non-unique columns.
8B	Eliminate redundancy by decomposing tables.
8C	Consider denormalizing tables in reporting databases.