Achieving 2NF: Eliminating Partial Dependencies

1. The Process of Decomposition into 2NF

Converting a 1NF table to 2NF is a methodical process of breaking it down, or decomposing it, into smaller, well-structured tables. The goal is to ensure that every non-key attribute in every table is fully functionally dependent on the primary key.

The process involves three key steps:

1. Identify the Primary Key and all Partial Dependencies. This is the diagnostic step you practiced in the previous lesson. You must first find any non-key attributes that are dependent on only a part of the composite primary key.
2. Create a New Table for Each Partial Dependency. For each partial dependency you find, you create a new table.
   • The determinant (the part of the original primary key that the attribute depends on) becomes the primary key of this new table.
   • The dependent non-key attribute(s) are moved from the original table into this new table.
3. Update the Original Table. The attributes you moved in step 2 are now removed from the original table. The original table is left with its composite primary key and any non-key attributes that were fully dependent on it.

Let's see this process in action.

2. Example 1: Decomposing a School Table

The following video provides a concise example of identifying a partial dependency and resolving it through decomposition.

As the video explained, the school table had a partial dependency. Let's formalize the decomposition process based on the video's example.

Before: The 1NF Table

• SCHOOL(studentID, subjectID, marks, teacherName)
• Primary Key: {studentID, subjectID}
• Partial Dependency: subjectID -> teacherName (The teacher's name depends only on the subject, not the student.)

The Decomposition:

1. We identify the partial dependency subjectID -> teacherName.
2. We create a new table for this dependency. Let's call it SUBJECT_TEACHER. The determinant, subjectID, becomes its primary key.
   • SUBJECT_TEACHER(subjectID, teacherName)
3. We remove teacherName from the original table. The marks attribute remains, as it is fully dependent on both studentID and subjectID.

After: The 2NF Tables

• SCORE(studentID, subjectID, marks)
• SUBJECT_TEACHER(subjectID, teacherName)

By splitting the table, we've eliminated the redundancy. The name of the Java teacher is now stored only once, not for every student taking Java. We have successfully converted the database schema to 2NF.

3. Example 2: Decomposing with Functional Dependencies

This process works just as well when you are given a set of abstract functional dependencies instead of a table with sample data. Let's revisit the practice exercise from our last lesson and complete the normalization process.

Scenario:

• Relation: R(A, B, C, D, E)
• Primary Key: {A, B}
• Functional Dependencies:
  • {A, B} -> C (Full dependency)
  • A -> D (Partial dependency)
  • B -> E (Partial dependency)

As you identified in the last lesson, the FDs A -> D and B -> E are partial dependencies because the non-prime attributes D and E depend on only parts of the primary key {A, B}.

The Decomposition:

1. Handle the first partial dependency (A -> D):

   • Create a new table, R1, with the determinant A as its primary key.
   • Move the dependent attribute D into this table.
   • Result: R1(A, D)

2. Handle the second partial dependency (B -> E):

   • Create another new table, R2, with the determinant B as its primary key.
   • Move the dependent attribute E into this table.
   • Result: R2(B, E)

3. Update the original table:

   • Remove D and E from the original relation R.
   • The remaining attributes are A, B, and C. The original primary key {A, B} and the fully dependent attribute C stay together.
   • Result: R3(A, B, C)

After: The 2NF Tables

The final set of tables in 2NF is:

• R1(A, D)
• R2(B, E)
• R3(A, B, C)

Each table is now in 2NF. R1 and R2 have single-attribute primary keys, so they can't have partial dependencies. R3 has a composite primary key, but its only non-key attribute C is fully dependent on {A, B}.

The following reading provides another detailed walkthrough of this process with a StudentCourse example.

4. Practice Exercise

Now it's your turn to apply the decomposition process.

Scenario:
An agency supplies temporary staff to hotels. The following table is used to track the hours each staff member works on different contracts.

Table Schema:
CONTRACT_STAFF(ContractNo, HotelNo, HotelLocation, StaffNo, StaffName, HoursWorked)

Analysis:

• To uniquely identify a row, you need to know which contract and which staff member are involved. So, the Primary Key is {ContractNo, StaffNo}.
• The business rules dictate the following dependencies:
  • A contract number determines the hotel: ContractNo -> HotelNo, HotelLocation
  • A staff number determines the staff member's name: StaffNo -> StaffName
  • The hours worked depends on both the staff member and the specific contract they are working on: {ContractNo, StaffNo} -> HoursWorked

Your Task:
Based on the schema and dependencies above, decompose the CONTRACT_STAFF table into a set of tables that are in Second Normal Form (2NF). List the schema for each new table and underline its primary key.

Conclusion

In this lesson, you have moved from diagnosing to solving 2NF violations. You learned the systematic process of decomposition, which is the cornerstone of normalization. By separating partial dependencies into their own tables, you can create a database structure that is more robust, efficient, and free from the update anomalies we discussed previously.

Key Takeaways:

• Converting a 1NF table to 2NF is done through decomposition.
• The process involves creating a new table for each identified partial dependency.
• The determinant of the partial dependency becomes the primary key of the new table.
• The partially dependent attributes are removed from the original table, which retains the full primary key and any fully dependent attributes.

Our tables are now in 2NF, a significant improvement. However, there is another, more subtle type of dependency that can still cause problems. In our next lesson, we will explore transitive dependencies and learn how to eliminate them to achieve the Third Normal Form (3NF).