3NF Definition

Having explored transitive dependencies and their destructive potential, we now arrive at Third Normal Form (3NF)—the normal form specifically designed to eliminate these indirect dependency chains while maintaining practical usability.

Third Normal Form represents a crucial milestone in database normalization. Unlike BCNF (which we'll study later), 3NF strikes a careful balance: it removes transitive dependencies on non-prime attributes while preserving functional dependencies through decomposition. This balance makes 3NF the most commonly used normal form in production database design.

The original definition of Third Normal Form, proposed by E.F. Codd in 1971, is elegant in its simplicity:

Codd's Definition of 3NF:

A relation R is in Third Normal Form if and only if:

1. R is in Second Normal Form (2NF), AND
2. Every non-prime attribute of R is non-transitively dependent on every candidate key of R

This definition builds directly on what we learned about 2NF and transitive dependencies. Let's unpack each component:

In Simpler Terms:

A relation is in 3NF if:

• It has no repeating groups or non-atomic values (1NF satisfied)
• No attribute depends on only part of a candidate key (2NF satisfied)
• No attribute depends on another non-key attribute that depends on and doesn't determine a candidate key (3NF addition)

Or even simpler: Non-prime attributes must depend on the key, the whole key, and nothing but the key.

This famous mnemonic, often attributed to Bill Kent, captures 1NF, 2NF, and 3NF together:

• "The key" → 1NF: There is a key (no repeating groups)
• "The whole key" → 2NF: Depend on the complete key (no partial dependencies)
• "Nothing but the key" → 3NF: Depend only on the key (no transitive dependencies)

While Codd's original definition is historically important, database theory has evolved to express 3NF more directly. The modern definition doesn't require checking 2NF as a prerequisite—it captures everything in a single statement:

Modern Definition of 3NF:

A relation R with functional dependencies F is in Third Normal Form if and only if:

For every non-trivial functional dependency X → A in F⁺:

• X is a superkey, OR
• A is a prime attribute (A is part of some candidate key)

This definition is more general and powerful because it doesn't rely on the normal form hierarchy—it directly characterizes 3NF through properties of functional dependencies.

Why This Definition is Equivalent:

Let's verify that the modern definition captures both 2NF and the 3NF transitive dependency condition:

Capturing 2NF (No Partial Dependencies):

• A partial dependency occurs when X → A where X is a proper subset of a candidate key
• If X is a proper subset of a key, X is NOT a superkey
• If A is not part of any candidate key (non-prime), we violate the modern 3NF condition
• Therefore, the modern definition automatically prevents partial dependencies

Capturing 3NF (No Transitive Dependencies on Non-Prime):

• A transitive dependency K → Y → A means Y → A exists where Y is not a superkey
• If A is non-prime, we have Y → A where Y is not a superkey and A is not prime
• This directly violates the modern 3NF condition
• Therefore, transitive dependencies to non-prime attributes are forbidden

The distinction between prime and non-prime attributes is central to understanding 3NF. Let's examine this distinction rigorously.

Definitions:

Prime Attribute: An attribute that is a member of at least one candidate key

Non-Prime Attribute: An attribute that is NOT a member of ANY candidate key

Note that an attribute can be prime by virtue of belonging to just one candidate key, even if it's not in others.

Why the Distinction Matters for 3NF:

The 3NF definition treats prime and non-prime attributes differently:

1. Non-prime attributes must depend directly on a superkey. Any transitive dependency through a non-superkey intermediate violates 3NF.

2. Prime attributes can depend on non-superkey determinants. The reasoning is that dependencies involving prime attributes are "acceptable" because they participate in the key structure of the relation.

This asymmetry is what distinguishes 3NF from BCNF:

• 3NF: X → A violates 3NF only if X is not a superkey AND A is non-prime
• BCNF: X → A violates BCNF if X is not a superkey (regardless of A being prime or not)

While 3NF builds on 2NF, they address different classes of problematic dependencies. Understanding their relationship clarifies what each normal form accomplishes.

The Progression:

1NF: Ensures atomic values and proper relational structure 2NF: Removes partial dependencies (non-prime depends on part of key) 3NF: Removes transitive dependencies (non-prime depends on non-key)

Key Insight: Why 2NF Doesn't Imply 3NF

A relation can be in 2NF (no partial dependencies) but still have transitive dependencies. Consider:

Employee(EmpID, DeptID, DeptName, Salary)

• Primary Key: EmpID (single attribute—partial dependencies impossible)
• FDs: EmpID → DeptID, EmpID → Salary, DeptID → DeptName

2NF Check: ✓ Passes. With a single-attribute key, partial dependencies cannot exist.

3NF Check: ✗ Fails! EmpID → DeptID → DeptName is a transitive dependency. DeptID is not a superkey, and DeptName is non-prime.

This demonstrates that 2NF handles a different class of problem than 3NF. Both must be achieved for a well-designed relation.

To rigorously determine whether a relation is in Third Normal Form, follow this systematic procedure:

Algorithm: Test for 3NF

Input: Relation R(A₁, A₂, ..., Aₙ), Set of FDs F
Output: TRUE if R is in 3NF, FALSE otherwise (with violating FD)

1. Find all candidate keys of R
   - Use attribute closure to identify minimal superkeys

2. Classify attributes as Prime or Non-Prime
   - Prime: appears in at least one candidate key
   - Non-Prime: all other attributes

3. For each non-trivial FD (X → A) in F (or F⁺):
   a. Is X a superkey of R?
      - If YES: This FD satisfies 3NF (continue to next FD)
   b. Is A a prime attribute?
      - If YES: This FD satisfies 3NF (continue to next FD)
   c. If NEITHER: This FD violates 3NF
      - Return FALSE with (X → A) as the violating FD

4. If all FDs pass: Return TRUE

Let's work through several examples to solidify your understanding of the 3NF test procedure.

Example 1: Relation that Passes 3NF

Example 2: Relation that Fails 3NF

Example 3: Relation with Multiple Candidate Keys

Third Normal Form has become the most widely used normal form in production database design. This isn't by accident—3NF achieves a crucial balance that makes it uniquely practical.

The Key Guarantee:

For any relation schema R and set of FDs F, there always exists a decomposition of R that is:

1. In 3NF
2. Lossless-join (no information lost)
3. Dependency-preserving (all FDs can be checked within single relations)

This triple guarantee is remarkable. BCNF, which is "stricter" than 3NF, cannot always provide dependency preservation. Many real-world schemas must sacrifice dependency preservation to achieve BCNF, which has practical consequences for constraint enforcement.

Why Dependency Preservation Matters:

When FDs are preserved in the decomposition, each FD can be enforced by checking a single relation. Consider:

• Preserved FD: CustomerID → CustomerName in Customer table. To verify, check only Customer.
• Lost FD: If decomposition splits attributes across tables, verifying the FD requires joining tables first, which is expensive and sometimes impractical in real-time enforcement.

For most applications, the ability to efficiently enforce constraints outweighs the marginal extra redundancy that 3NF allows compared to BCNF.

Industry Practice:

Most production databases target 3NF as their normalization goal:

• 3NF eliminates the most common and harmful redundancies
• Dependency preservation enables efficient constraint enforcement
• The allowed redundancy (transitive dependencies to prime attributes) is rare and low-impact
• Moving beyond 3NF typically offers diminishing returns for increased complexity