6 - Normalization

Abstract

This chapter focuses on the fundamentals of normal forms for relational databases and the database design step that normalizes the candidate tables. It defines the constraints imposed on tables, most commonly the functional dependencies (FDs). Based on these constraints, practical normal forms for database tables are defined: 1NF, 2NF, 3NF, and Boyce–Codd normal form (BCNF). All are based on the types of FDs present. A practical algorithm for finding the minimum set of 3NF tables is given. It also investigates the equivalence between the conceptual data model (e.g., the entity–relationship (ER) model) and normal forms for tables. For most database practitioners, the first three sections of this chapter cover the critical normalization needed for everyday use, through BCNF. The final section describes an algorithm for finding the minimum set of third normal form (3NF) tables when the initial design of tables becomes large and unwieldy. Within an entity. The level of normalization is totally dependent on the interrelationships among the key and nonkey attributes. It could be any form from un-normalized to BCNF. Binary (or binary recursive) one-to-one or one-to-many relationship. Within the “child” entity, the foreign key (a replication of the primary key of the “parent”) is functionally dependent on the child's primary key. Binary (or binary recursive) many-to-many relationship. The intersection table has a composite key and possibly some nonkey attributes functionally dependent on it. It was observed that a good, methodical conceptual design procedure often results in database tables that are either normalized (BCNF) already, or can be normalized with very minor changes.