2.2Functional dependency and normalization theory

Normalization is not "theory for theory's sake"—it is a practical procedure for removing update anomalies lurking in a schema (problems where the same fact ends up written in multiple places, leading to missed updates, contradictions, and unnecessary NULLs). A database designer must recognize what update anomalies a given schema is prone to and judge the degree of normalization needed—no more, no less—to eliminate them. This section covers the concept of functional dependency, the foundation of normalization, and which anomaly each of first, second, and third normal form removes.

2.2.1Functional dependency fundamentals

• A functional dependency (X → Y) is a relationship in which, once the value of an attribute (or set of attributes) X is fixed, the value of attribute Y is uniquely determined. For example, "employee ID → name" holds because fixing the employee ID uniquely determines the name.
• Full functional dependency is where Y depends functionally on the entirety of a composite key X and on no proper subset of X. Partial functional dependency is where Y depends on only part of a composite key X (for example, given the composite key (employee ID, project ID), an employee's name is actually determined by employee ID alone).
• A transitive functional dependency exists when X → Y and Y → Z both hold, so X → Z follows as a consequence, but Z depends on X only indirectly, via the non-key attribute Y (for example, the chain employee ID → department code → department name means department name is only indirectly determined by employee ID).

2.2.2First and second normal form

• First normal form (1NF) eliminates repeating groups—cramming multiple values into a single column (cell)—so that every attribute holds only a single (atomic) value. Leaving repeating groups in place results in an inefficient design where searching or updating on a specific value requires parsing the entire column's string.
• Second normal form (2NF) satisfies 1NF and additionally eliminates partial functional dependency (every non-key attribute is fully functionally dependent on the entire composite key). If a partial functional dependency remains, a fact tied to only part of the composite key ends up duplicated and managed alongside the other part, causing update anomalies (the same fact stored redundantly, with the risk of missed updates).

2.2.3Third normal form and removing transitive dependencies

• Third normal form (3NF) satisfies 2NF and additionally eliminates transitive functional dependency (no non-key attribute depends on the key only indirectly, via another non-key attribute). If a transitive dependency remains, information that is redundantly stored (such as a department name) can become inconsistent unless the intervening non-key attribute (such as a department code) is kept in perfect sync everywhere it appears.
• The more normalization is pursued, the fewer update anomalies remain, but the number of tables increases, so SQL requires more JOINs, and read performance degrades. Given this trade-off, practical design requires judging that business data with frequent updates, where consistency matters most, should be prioritized for normalization, while read-mostly data with almost no updates is a candidate for denormalization (covered later, in section 5).

A designer of a project-management system is asked to review a schema currently run as a single table: a "project assignment" table (employee ID, project ID, employee name, department code, department name, assignment role), with the composite key (employee ID, project ID) as its primary key. Looking first at "employee name": it is determined by employee ID alone, not by project ID—a partial functional dependency. In this state, if one employee is assigned to three projects, the same name is duplicated across three rows, and changing that name requires updating all three rows or a contradiction (an update anomaly) results. Looking next at "department name": it is determined via the chain employee ID → department code → department name, so a transitive functional dependency from employee ID to department name exists. If a department's name changes, every assignment row for every employee in that department must be updated without omission, or a contradiction results—another update anomaly. To resolve both, the design first applies 2NF, splitting off an "employee" table (employee ID, employee name, department code), extracting the partially dependent name so it depends on employee ID alone. It then applies 3NF, further splitting off a "department" table (department code, department name), extracting department name so it depends on department code alone. The "project assignment" table is left holding only (employee ID, project ID, assignment role); update anomalies are resolved, but obtaining an employee's name or department name now requires joining multiple tables. Here the designer concludes that, since project assignments change frequently and employee transfers happen routinely, prioritizing consistency by proceeding with normalization is the correct judgment, treating the resulting increase in JOIN cost as a separate problem to be addressed later through performance design (such as indexing).

2.2.4Section summary

• Functional dependency (X to Y) is the foundation of normalization. Partial functional dependency depends on only part of a composite key; transitive functional dependency depends indirectly via a non-key attribute
• 1NF eliminates repeating groups, 2NF eliminates partial functional dependency, and 3NF eliminates transitive functional dependency, progressively removing update anomalies
• Normalization trades off against increased JOIN cost; judging when to stop at the degree that is necessary and sufficient for the update frequency and consistency requirements matters in practice