Skip to main content
Back to path

Welcome to a Grasp lesson

Create a free account to keep studying and create your own study path.

Lesson 55

Normalizing to 2NF and 3NF

Recommended resources

Hello! Welcome back to our series on database design.

In the last lesson, we established the "why" behind normalization by looking at data anomalies, and you learned how to bring a table into First Normal Form (1NF) by ensuring all attributes are atomic. This is a great first step, but as we saw, a 1NF table can still contain significant data redundancy, which can lead to the very anomalies we're trying to prevent.

This lesson takes you through the next crucial steps in the normalization process. You will learn how to normalize a database schema to Second Normal Form (2NF) and Third Normal Form (3NF). By the end of this lesson, you will be able to identify and eliminate more subtle types of dependencies—partial and transitive—to create a database schema that is efficient, reliable, and free from the most common data integrity problems. This is a core topic for your GATE preparation.

Second Normal Form (2NF): Eliminating Partial Dependencies

A table is in Second Normal Form (2NF) if it meets two conditions:

  1. It is already in 1NF.
  2. It contains no partial dependencies.

So, what is a partial dependency?

A partial dependency can only occur in a table with a composite primary key (a primary key made up of two or more columns). It exists when a non-key attribute depends on only a part of the composite primary key, instead of the entire key.

Let's watch a video that explains this with a fantastic, practical example.

Learn Database Normalization - 1NF, 2NF, 3NF, 4NF, 5NF

The video from Decomplexify explains the concept of 2NF by building upon a 1NF table. It clearly demonstrates how a partial dependency leads to insertion, update, and deletion anomalies, and how to fix them.

Please watch the segment from 10:08 to 15:59. As you watch, focus on: The example table Player_Inventory with a composite key (Player, Item_Type). How the Player_Rating attribute creates a partial dependency because it depends only on Player, not the full key. The specific examples of deletion, update, and insertion anomalies that this dependency causes. The solution: decomposing the table into two separate tables.

To summarize the video's key points:

The table Player_Inventory(Player, Item_Type, Item_Quantity, Player_Rating) has a composite primary key of (Player, Item_Type).

The dependencies are:

  • (Player, Item_Type) → Item_Quantity: This is a full functional dependency. The quantity of an item depends on both the player and the specific item. This is correct.
  • Player → Player_Rating: This is a partial dependency. A player's rating depends only on the player, not on the items they hold. This violates 2NF.

This violation causes problems:

  • Insertion Anomaly: You can't add a new player (and their rating) until they have at least one inventory item.
  • Update Anomaly: If a player's rating changes, you must update it in multiple rows, risking data inconsistency if one update fails.
  • Deletion Anomaly: If a player loses all their items, all rows for that player are deleted, and you lose their rating information entirely.

The solution, as shown, is to decompose the table:

  1. Player_Inventory(Player, Item_Type, Item_Quantity)
  2. Player(Player, Player_Rating)

Now, the Player table holds player-specific information, and the Player_Inventory table holds information about the relationship between players and items. The tables are linked by the Player attribute, which would be a foreign key in the Player_Inventory table.

Second Normal Form (2NF) Explanation with Example
This diagram provides a concise visual summary of the 2NF rule. It shows that for a table to be in 2NF, all non-key attributes (C, D, E, F) must depend on the entire primary key (A, B), not just a part of it.

For your GATE preparation, it's also important to understand the formal definitions and how to apply them to problems where you are given a set of functional dependencies.

Lec-24: Second Normal Form | 2NF | Database Management System

This video from Gate Smashers provides a more formal, exam-oriented explanation of 2NF. It defines prime and non-prime attributes and walks through a sample GATE question, which is excellent practice for you.

Watch the entire video (it's about 17 minutes but very thorough). Focus on: The formal definition of 2NF: No partial dependency, where a non-prime attribute depends on a proper subset of a candidate key. How to identify prime and non-prime attributes after finding the candidate key(s). The step-by-step process of checking each functional dependency in the GATE problem to see if it represents a partial dependency.

Test your understanding!

You have a table Enrollment with the following schema and functional dependencies:

  • Schema: Enrollment(student_id, course_id, student_name, grade, course_credits)
  • Primary Key: (student_id, course_id)
  • Functional Dependencies:
    1. (student_id, course_id) → grade
    2. student_id → student_name
    3. course_id → course_credits

Is this table in 2NF? If not, why? How would you decompose it to achieve 2NF?

Show answer

The table is not in 2NF.

Reason:
The primary key is (student_id, course_id).

  • student_name depends only on student_id, which is a part of the primary key. This is a partial dependency.
  • course_credits depends only on course_id, which is also a part of the primary key. This is another partial dependency.

Decomposition to 2NF:
To fix this, we decompose the table into three separate tables:

  1. Students(student_id, student_name) - PK: student_id
  2. Courses(course_id, course_credits) - PK: course_id
  3. Enrollment_Grades(student_id, course_id, grade) - PK: (student_id, course_id)

This decomposition removes both partial dependencies.

Third Normal Form (3NF): Eliminating Transitive Dependencies

Once a table is in 2NF, we can take it to the next level: Third Normal Form (3NF). A table is in 3NF if it meets two conditions:

  1. It is already in 2NF.
  2. It contains no transitive dependencies.

A transitive dependency exists when a non-key attribute determines another non-key attribute. In other words, an indirect dependency exists where A → B and B → C, where A is the primary key, and B and C are non-key attributes. This means the primary key A determines C via B.

Let's return to the Decomplexify video, which explains this concept very clearly.

Learn Database Normalization - 1NF, 2NF, 3NF, 4NF, 5NF

Continuing with the gaming example, this segment of the video introduces a transitive dependency and the data inconsistency it can cause.

Watch from 15:59 to 19:28. Pay attention to: The new table Player(Player_ID, Player_Skill_Level, Player_Rating). The dependencies: Player_ID → Player_Skill_Level and Player_Skill_Level → Player_Rating. How this creates a transitive dependency where Player_ID indirectly determines Player_Rating. The update anomaly this causes and the decomposition solution.

As the video explained, in the table Player(Player_ID, Player_Skill_Level, Player_Rating), we have a transitive dependency because Player_Rating (a non-key attribute) depends on Player_Skill_Level (another non-key attribute).

This can lead to update anomalies. If a player's skill level increases from 3 to 4, their rating should change from "Beginner" to "Intermediate." If we only update the skill level and forget the rating, the data becomes inconsistent.

The solution is, once again, to decompose the table:

  1. Player(Player_ID, Player_Skill_Level)
  2. Skill_Levels(Player_Skill_Level, Player_Rating)

This removes the transitive dependency, ensuring that facts about skill levels and ratings are stored in only one place.

2NF to 3NF Normalization Example
This image illustrates a simple decomposition from 2NF to 3NF. The `Dept` (Department) depends on the `Lecturer`, who in turn is assigned to a `Module`. This is a transitive dependency. Decomposing it into two tables, `3NFa` and `3NFb`, removes this indirect dependency.

For a more formal definition and practice, the following resource provides excellent notes and exercises.

NORMALIZATION NOTE & EXERCISES (Up to 3NF)

This document provides formal definitions and review questions that are very helpful for solidifying your understanding for exams.

Please read the following sections: Third normal form (3NF): This provides the formal definition of 3NF and transitive dependency. Reviews Questions & Answers: Focus on questions 6 and 7, which specifically ask you to describe the characteristics of 3NF and transitive dependency. The provided answers are very clear.

The Key, The Whole Key, and Nothing But The Key

There's a popular and very effective mnemonic to remember the first three normal forms. It summarizes the requirements for a non-key attribute:

Every non-key attribute must depend on the key [1NF], the whole key [2NF], and nothing but the key [3NF].

  • "The key": Refers to the fact that the attribute must depend on a primary key (this is a general rule, closely related to 1NF ensuring a proper key exists).
  • "The whole key": This addresses 2NF. The attribute must depend on the entire primary key, not just part of it (no partial dependencies).
  • "Nothing but the key": This addresses 3NF. The attribute must depend directly on the primary key, not on another non-key attribute (no transitive dependencies).

If you keep this phrase in mind during database design, you will naturally create schemas that are in 3NF.

Conclusion

In this lesson, you've moved beyond the basics of 1NF and learned the rules for creating truly robust and efficient database schemas. By eliminating partial and transitive dependencies, you can prevent a host of data integrity issues.

Key Takeaways:

  • Second Normal Form (2NF): A table must be in 1NF and have no partial dependencies. This means every non-key attribute must depend on the entire composite primary key.
  • Third Normal Form (3NF): A table must be in 2NF and have no transitive dependencies. This means no non-key attribute can depend on another non-key attribute.
  • Decomposition is the technique used to resolve violations of 2NF and 3NF by splitting a table into smaller, well-structured tables.
  • Remember the mnemonic: non-key attributes must depend on "the key, the whole key, and nothing but the key."

Preview of the Next Lesson:
We've spent the last few lessons on the theory of database design. Now that you understand how to structure tables correctly, we will shift our focus to database application. In the next lesson, you will learn how to connect to an SQL database from Python to execute queries and fetch data, bridging the gap between database theory and practical programming.