Third Normal Form (3NF) Requirements

1. The Core Requirements for 3NF

A table's journey through normalization is a cumulative process. To be in 2NF, it must first be in 1NF. Similarly, to achieve 3NF, a table must already satisfy the rules of 2NF. 3NF then adds one final, critical rule.

Let's get a clear definition of these requirements from a helpful guide.

As the article states, the two conditions for a table to be in 3NF are:

1. The table must be in Second Normal Form (2NF). This means all its data values are atomic (1NF), and it has no partial dependencies. In other words, every non-key attribute must be fully dependent on the entire primary key.
2. The table must have no transitive dependencies. This is the new rule for 3NF. It ensures that no non-key attribute is dependent on another non-key attribute.

These two rules work together to enforce the principle that every non-key attribute in a table must depend directly and exclusively on the primary key.

2. A More Formal Definition of 3NF

While the two rules above are the most common way to define 3NF, database theory also provides a more formal definition based on functional dependencies. Understanding this will give you a more precise, academic perspective, which is valuable in a university setting.

This formal definition states that for a table to be in 3NF, for every non-trivial functional dependency of the form $X\to Y$, one of the following two conditions must hold:

1. $X$ is a superkey.
2. $Y$ is a prime attribute (meaning, each attribute that makes up $Y$ is part of at least one candidate key).

Let's read about this formal definition and then break it down.

Let's connect this back to what we already know. A transitive dependency, as we learned in the last lesson, often has the form PK -> NonKey1 -> NonKey2.

The problematic dependency is NonKey1 -> NonKey2. Let's check this against the formal rules:

• Is NonKey1 (our $X$) a superkey? No.
• Is NonKey2 (our $Y$) a prime attribute? No.

Since this dependency fails to meet either condition, it violates the formal definition of 3NF. This shows that the rule "no transitive dependencies" is just a more practical way of stating this formal condition.

3. Comparing the Normal Forms

At this point, you've learned the rules for 1NF, 2NF, and 3NF. It's helpful to see them side-by-side to understand how they build upon one another. The goal of this progression is to progressively eliminate dependencies that cause data anomalies.

The following resource provides a simple table that summarizes the requirements for each normal form.

This table provides a concise summary:

This progression leads us back to the memorable phrase for database designers: "Every non-key attribute must provide a fact about the key, the whole key, and nothing but the key."

• "The key" (atomic values) gets you to 1NF.
• "The whole key" (eliminating partial dependencies) gets you to 2NF.
• "Nothing but the key" (eliminating transitive dependencies) gets you to 3NF.

This phrase is an excellent way to remember the purpose and requirements of each of the first three normal forms.

Conclusion

In this lesson, we have formally defined the requirements for a table to be in Third Normal Form. You now have both a practical and a formal understanding of the rules that govern this important stage of database normalization.

Key Takeaways:

• A table is in 3NF if it meets two conditions:
  1. It is already in 2NF.
  2. It contains no transitive dependencies.
• Formally, this means for any functional dependency $X\to Y$, either $X$ must be a superkey or $Y$ must be a prime attribute.
• The progression from 1NF to 3NF systematically removes dependencies, ensuring that all non-key attributes depend on "the key, the whole key, and nothing but the key."

Now that you can confidently state the rules for 3NF, our next lesson will focus on applying them. We will look at several tables and practice identifying whether they violate 3NF.