Welcome back! In our previous lesson, we delved into the concept of partial dependency, learning how to spot situations where a non-key attribute depends on only a part of a composite primary key. We saw that this creates data redundancy and potential for errors.
This lesson builds directly on that knowledge. We will now formally define the rule that is designed to eliminate this exact problem. You will learn the requirements for a table to be in Second Normal Form (2NF). Achieving 2NF is the second major step in the normalization process, moving us closer to a clean, efficient database design.
1. The Two Conditions for Second Normal Form
A table is in Second Normal Form (2NF) if it meets two specific conditions. A short video from the Studytonight channel states these conditions very clearly.
Second Normal Form (2NF) | Database Normalization | DBMS
This video directly presents the two criteria a table must satisfy to be in 2NF.
Please watch the brief segment from the beginning that lists the two conditions for 2NF.
As the video states, for a table to be in 2NF, it must:
- Be in First Normal Form (1NF).
- Have no partial dependencies.
This second rule is the key to 2NF. It means that every non-prime attribute in the table must be fully functionally dependent on the entire primary key.
Let's get a more detailed definition from the "Database Design" textbook. This reading reinforces the two conditions and adds an important clarification about tables with single-attribute primary keys.
Chapter 12 Normalization – Database Design – 2nd Edition
This text provides a formal definition of 2NF and explains how it applies based on the type of primary key.
Please read the section titled "Second Normal Form (2NF)". Focus on the two main paragraphs that define the form and its relationship to the primary key.
Based on this, we can establish a very precise set of rules:
- Condition 1: The table must already be in 1NF (i.e., have no repeating groups and all values are atomic).
- Condition 2: If the primary key is a single attribute, the table is automatically in 2NF (as long as it's in 1NF). This is because partial dependencies are impossible without a composite key.
- Condition 3: If the primary key is composite, then every attribute that is not part of the key (a non-prime attribute) must depend on the entire key, not just a part of it.
2. Understanding the "No Partial Dependency" Rule in Practice
The main purpose of 2NF is to enforce the rule you learned about in the last lesson. Let's revisit what a partial dependency looks like to see why this rule is so important.
A table with this structure suffers from data redundancy. The name of an employee with a given SSN would be repeated for every project they are assigned to. 2NF prevents this.
The following video provides another excellent example and explains the logic of 2NF.
Database Normalization 1NF 2NF 3NF
This video from Jesper Lowgren explains the rule for 2NF and walks through an example of a table that violates it.
Watch the section on Second Normal Form. Notice how the presenter identifies an attribute (Skill Name) that does not depend on the whole primary key and explains that it must be split into a new table.
The key takeaway is that 2NF ensures that every table is about a single "thing" or concept. In the video's example, the initial table was about two things: which employees have which skills, AND the names of those skills. 2NF forces you to separate these into distinct tables: one for the employee-skill relationship and another for the skill information itself.
3. Practice: Is This Table in 2NF?
Let's test your understanding. Below is a dependency diagram for a table with attributes C1, C2, C3, C4, and C5. A dependency diagram uses arrows to show functional dependencies.
Given this information:
- Primary Key:
{C1, C3} - Functional Dependencies:
C1 -> C2,C3 -> C4, andC4 -> C5. (The full key{C1, C3}also determines all other attributes, which is implied).
Your Task: Is the table represented by this diagram in 2NF? Explain why or why not.
Click here for the solution
Answer: No, this table is not in 2NF.
Explanation:
- Check Condition 1 (1NF): We assume the table is in 1NF as we are focused on normalization rules beyond that.
- Check Condition 2 (No Partial Dependencies):
- The primary key is composite:
{C1, C3}. - The prime attributes are
C1andC3. - The non-prime attributes are
C2,C4, andC5. - We examine the dependencies for the non-prime attributes:
C1 -> C2: The non-prime attributeC2is dependent onC1, which is only a part of the primary key. This is a partial dependency.C3 -> C4: The non-prime attributeC4is dependent onC3, which is also only a part of the primary key. This is another partial dependency.
- Because the table contains at least one partial dependency, it violates the requirements for Second Normal Form.
- The primary key is composite:
(Note: The dependency C4 -> C5 represents a transitive dependency, which is a violation of Third Normal Form (3NF), something we will cover in a future lesson.)
Conclusion
In this lesson, you've learned the formal definition of Second Normal Form. This is a critical checkpoint in the database design process that ensures your tables are structured to avoid the redundancy caused by partial dependencies.
Key Takeaways:
- A table is in 2NF if it is in 1NF and has no partial dependencies.
- This means every non-prime attribute must be fully functionally dependent on the entire primary key.
- If a 1NF table has a single-attribute primary key, it is automatically in 2NF.
- The rule for 2NF primarily applies to tables with composite primary keys.
You can now state the conditions for 2NF and explain the reasoning behind them. In our next lesson, we will put this knowledge into practice by focusing on how to systematically identify violations of 2NF in various table structures.