Functional Dependencies and Second Normal Form (2NF)

What is a Functional Dependency?

Before we can understand 2NF, we must first grasp a core concept in database theory: functional dependency. This is a fundamental relationship between attributes (columns) within a table. Your background in programming and logical thinking will be helpful here, as this concept is about defining deterministic relationships in your data.

In simple terms, a functional dependency exists when the value of one attribute (or a set of attributes) uniquely determines the value of another attribute. We write this as $A\to B$, which is read as "A determines B".

For a deep dive into this concept, the following video provides an excellent, clear explanation.

As the video showed, the key rule for a functional dependency $A\to B$ is: for any given value of $A$ in the table, there can only be one corresponding value of $B$.

For instance, in a Users table:

• UserID -> Email is a valid functional dependency because a single user ID will correspond to exactly one email address.
• City -> UserID is likely not a valid functional dependency, because many users can live in the same city. Knowing the city does not uniquely determine the user.

The Problem: Partial Dependencies and Their Anomalies

Now we can talk about Second Normal Form (2NF). The problem that 2NF solves is called a partial dependency. This issue can only arise when a table has a composite primary key (a primary key made up of two or more columns).

A partial dependency occurs when a non-key attribute is functionally dependent on only part of the composite primary key, not the whole thing.

Let's see what problems this causes in practice.

The video demonstrates that when Player_Rating is added to the Player_Inventory table, we create redundancy. The primary key is {Player, Item_Type}, but the Player_Rating depends only on {Player}. This is a partial dependency, and it leads to:

• Insertion Anomaly: You can't add a new player's rating until they have an item in their inventory.
• Update Anomaly: To change a player's rating, you must update multiple rows, risking inconsistent data.
• Deletion Anomaly: If a player loses their last inventory item, their rating information is also deleted from the database.

This image provides a sharp, visual definition of a partial dependency.

The Solution: Applying Second Normal Form (2NF)

The rule for 2NF is straightforward:

1. The table must be in 1NF.
2. The table must have no partial dependencies. Every non-key attribute must be fully functionally dependent on the entire primary key.

To fix a 2NF violation, we decompose the table. We move the partially dependent attributes, along with the part of the key they depend on, into a new table.

Let's see this in action using a couple of examples.

The article shows that the original table had a composite key {course, textbook}. However, the lecturer and department only depended on course. By splitting the table, we create:

1. A Courses2 table containing course, department, and lecturer.
2. A CourseTextbooks table containing just course and textbook.

This resolves the anomalies. You can now add a course without a textbook, and changing a lecturer only requires a single-row update.

This next image shows the same decomposition process for the EMP_PROJ table we saw earlier.

The 2NF Algorithm

Since you appreciate abstract and logical frameworks, here is the generalized algorithm for normalizing a 1NF table to 2NF, as described in the Red-Gate article.

Given a table R that is not in 2NF, find a functional dependency $A\to B$ that violates the rule (where A is a part of the primary key).

1. Let A be the attributes on the left side of the dependency (the determinant).
2. Let B be the attributes on the right side of the dependency (the dependent attributes).
3. Create a new table, R2, with the attributes from A and B. The primary key of this new table is A.
4. Remove the attributes in B from the original table, R. The original table is now R1.
5. Repeat this process for all partial dependencies.

This is the formal procedure that we followed in the examples above.

Conclusion

In this lesson, we took a crucial step toward creating a robust database schema. By understanding and resolving partial dependencies, we eliminate a significant source of data redundancy and protect our database from modification anomalies.

Key Takeaways:

• A Functional Dependency ($A\to B$) means the value of attribute(s) A uniquely determines the value of attribute(s) B.
• A Partial Dependency occurs when a non-key attribute depends on only a part of a composite primary key.
• A table is in Second Normal Form (2NF) if it is in 1NF and contains no partial dependencies.
• To achieve 2NF, you decompose the table, moving partially dependent attributes into a new table where the determinant part of the key becomes the primary key.

Preview of the Next Lesson:
We've solved partial dependencies, but our work isn't done. Look again at the Courses2 table from the Red-Gate example:

Here, course -> lecturer and lecturer -> department. This means department is indirectly dependent on the primary key, course. This is called a transitive dependency, and it introduces its own set of anomalies. In our next lesson, we will learn how to resolve this by applying Third Normal Form (3NF).