Normalizing to 3NF: Eliminating Transitive Dependencies

1. The Method: Decomposing to Remove Transitive Dependencies

The process of achieving 3NF from a 2NF table involves breaking down, or decomposing, the table. The goal is to isolate the transitive dependency in its own table, ensuring that all attributes in the remaining tables are fully dependent only on their primary key.

Here is the step-by-step procedure:

1. Identify the Transitive Dependency: Start with your 2NF table. Find the chain of dependencies where the primary key determines a non-key attribute, which in turn determines another non-key attribute. If your primary key is A, and you have non-key attributes B and C, the transitive dependency is the link $B\to C$.
2. Create a New Table: Create a new table specifically for the transitive dependency. Move both the determinant (B) and the dependent attribute (C) into this new table.
3. Assign the Primary Key: In the new table, the determinant attribute (B) becomes the primary key.
4. Clean Up the Original Table: Remove the transitively dependent attribute (C) from the original table. The attribute B remains in the original table, where it now serves as a foreign key linking to the new table you just created.

This process transforms an indirect relationship into two direct ones, as illustrated below.

2. A Step-by-Step Example

Let's apply this method to a concrete example. The document below describes how to normalize a BranchManager table that is not in 3NF.

Let's break down the process described in the document:

Initial Table (violates 3NF):
BranchManager(branchNo, branchAddress, telNo, mgrStaffNo, name)

• Primary Key: branchNo
• Dependencies:
  • branchNo → branchAddress, telNo, mgrStaffNo
  • mgrStaffNo → name (This is the transitive dependency!)

Decomposition Steps:

1. Identify: The transitive dependency is mgrStaffNo → name. The manager's name depends on their staff number, not on the branch number.
2. Create New Table: Create a new table to hold the facts about the manager. We'll call it ManagerStaff. It will contain mgrStaffNo and name.
   • ManagerStaff(mgrStaffNo, name)
3. Assign Primary Key: In the ManagerStaff table, mgrStaffNo becomes the primary key.
4. Clean Up Original Table: Remove the name attribute from the original BranchManager table. The mgrStaffNo attribute remains to serve as a foreign key. The original table is now better named Branch.

Final 3NF Tables:

• Branch(branchNo, branchAddress, telNo, mgrStaffNo)
  • branchNo is the PK.
  • mgrStaffNo is a FK referencing ManagerStaff.
• ManagerStaff(mgrStaffNo, name)
  • mgrStaffNo is the PK.

Now, all attributes in each table depend only on their respective primary keys. We have successfully removed the transitive dependency and achieved 3NF.

3. Visualizing the Transformation

Seeing a few more examples can help solidify the concept. The following short video clips demonstrate the decomposition process for different scenarios we discussed in the last lesson.

Each of these examples follows the same pattern: the transitively dependent attribute is removed and placed in a new table where its determinant becomes the primary key.

4. Practice Exercise: Your Turn

In the last lesson, you analyzed an INVOICE table and found the transitive dependency VEND_CODE → VEND_NAME. To focus purely on the 3NF step, let's assume we have already created a PRODUCT table from that scenario which is in 2NF but not 3NF.

Starting Table (2NF):
PRODUCT(PROD_NUM, PROD_LABEL, VEND_CODE, VEND_NAME)

• Primary Key: PROD_NUM
• Transitive Dependency: VEND_CODE → VEND_NAME

Your task is to convert this 2NF PRODUCT table into a set of 3NF tables. Follow the four-step decomposition process. What do your final tables look like? What are their primary and foreign keys?

5. A Complete Normalization Walkthrough

So far, we have focused on the specific step of moving from 2NF to 3NF. This final video puts everything together, showing the entire process of taking a single, unnormalized table all the way to 3NF.

This exercise demonstrates that normalization is a sequential process. You first ensure 1NF, then resolve partial dependencies to get to 2NF, and finally resolve transitive dependencies to get to 3NF.

Conclusion

In this lesson, you learned the practical skill of converting a 2NF table into a set of 3NF tables. By systematically identifying and removing transitive dependencies, you can create a database structure that is less redundant, more flexible, and free from the update, insertion, and deletion anomalies we've discussed.

Key Takeaways:

• Converting a 2NF table to 3NF is done by decomposing the table to remove transitive dependencies.
• The process involves creating a new table for the transitive dependency, where the determinant becomes the new primary key.
• The transitively dependent attribute is removed from the original table, and the determinant remains as a foreign key to link the tables.
• This procedure ensures that every non-key attribute in every table depends on "the key, the whole key, and nothing but the key."

You have now covered all the fundamental stages of normalization up to 3NF. In the next lesson, we will synthesize everything you've learned. We will take a complex, unnormalized dataset, perform all the normalization steps to achieve 3NF, and then construct a complete ERD to represent the final, well-designed database schema.