Decomposing tables to achieve 3NF involves breaking a larger table into smaller, related tables to eliminate transitive dependencies. By moving non-key attributes that depend on non-key parts into separate entities, you remove data redundancy and prevent insertion, update, or deletion anomalies while preserving the original data relationships.

Understanding the Need for Decomposition

Before performing decomposition, it is crucial to understand why existing tables fail the Third Normal Form (3NF) requirements. A table is in 3NF only if it is in Second Normal Form (2NF) and contains no transitive dependencies.

A transitive dependency occurs when a non-prime attribute depends on another non-prime attribute, rather than directly on the primary key. This often happens when we try to store too much information in a single, wide table.

If you leave these dependencies, your database will suffer from update anomalies. For instance, if a vendor’s address changes, you must update that address in every single row where that vendor appears.

Proper decomposition to 3NF breaks this cycle. It ensures that every non-key attribute depends strictly on the primary key of its specific table.

Step-by-Step Decomposition Process

Step 1: Identify Transitive Dependencies

Action: Examine the current table schema and trace the relationships between non-key attributes.

Result: You locate a chain of dependencies where Attribute A determines Attribute B, and Attribute B determines Attribute C.

In our standard example, consider an Orders table with a composite primary key of (OrderID, ProductID). Suppose this table also contains VendorID and VendorName.

If we have the functional dependency VendorID → VendorName, we have a transitive dependency because VendorName depends on VendorID, which is not the primary key of the Orders table.

The dependency chain here is effectively OrderID, ProductID → VendorID → VendorName. The name of the vendor is not directly determined by the order, but by the vendor ID associated with the product.

Step 2: Create a New Sub-Table for the Dependent Attribute

Action: Extract the transitive dependency into a new table. The determinant of the transitive dependency becomes the primary key of this new table.

Result: The new table holds the non-key attributes that are transitively dependent. The original table retains a reference to this new table.

We create a new table called Vendors. The VendorID becomes the primary key of this new table. We move VendorName into this table as a non-key attribute.

This isolates vendor information. The Vendors table now contains only attributes directly determined by the VendorID.

Step 3: Remove the Transitive Attributes from the Original Table

Action: Delete the extracted non-key attributes from the original parent table to break the functional dependency.

Result: The parent table no longer holds redundant data. It keeps a foreign key to maintain the link to the new table.

Back in the Orders table, we remove VendorName. The VendorID remains as a foreign key to maintain the relationship.

Now, if we look at a specific row in Orders, the VendorName is not stored there. To see the name, we must join the two tables.

While this increases query complexity slightly, it dramatically reduces storage space and eliminates the risk of inconsistent data.

Step 4: Verify Preservation of Dependencies

Action: Check that all functional dependencies from the original table are preserved in the decomposition.

Result: Ensure that no information is lost by the separation.

In our decomposition to 3NF, the original table could tell us which vendor supplied a product via the join. The new Vendors table tells us the details of that vendor.

Because we preserved the foreign key link (OrderID, ProductID) → VendorID, we can reconstruct the original data relationships perfectly.

Before and After Comparison

To visualize the impact of decomposition, let us compare a poorly designed table with a properly decomposed 3NF structure.

Table A: The Unnormalized State

This table represents a scenario before proper normalization. It mixes order data with vendor data.

OrderID | ProductID | VendorID | VendorName | ProductName
--------|-----------|----------|------------|------------
O1      | P100      | V001     | TechCorp   | Laptop
O2      | P101      | V002     | SoftInc    | Mouse
O3      | P100      | V001     | TechCorp   | Laptop

Here, VendorName is repeated. If TechCorp changes its name to “TechGroup,” you must update every row containing V001.

If you delete an order that happens to be the only one for a specific vendor, you might accidentally delete the vendor’s name permanently.

This table is in 2NF but fails 3NF because VendorName depends on VendorID, not the full primary key.

Table B: The Decomposed 3NF State

After performing the decomposition to 3NF, the data is split into two distinct entities.

Table: Orders

OrderID | ProductID | VendorID
--------|-----------|---------
O1      | P100      | V001
O2      | P101      | V002
O3      | P100      | V001

Table: Vendors

VendorID | VendorName | Address
---------|------------|-----------------
V001     | TechCorp   | 123 Tech Blvd
V002     | SoftInc    | 456 Software Ln

The redundancy of VendorName is gone. Now, to change the name of TechCorp, you update exactly one row in the Vendors table.

Deleting an order leaves the vendor’s information intact, preventing data loss.

This structure ensures that every non-key attribute depends only on the primary key.

Handling Complex Scenarios

Sometimes, decomposition becomes more complex when multiple transitive dependencies exist in a single table.

You must identify all such chains. If a table has dependencies DeptID → DeptName and DeptID → Location, both DeptName and Location must be moved to a new department table.

It is vital to ensure that you do not accidentally remove a dependency that is actually a direct dependency on the primary key.

Only non-key attributes that depend on other non-key attributes are moved.

Common Mistakes During Decomposition

Developers often confuse partial dependencies with transitive dependencies during the 3NF phase.

Remember that partial dependencies should have been removed in 2NF. If you are in 3NF, all non-key dependencies must be fully functional on the primary key.

Another mistake is breaking the table into too many small tables. While technically correct, this can lead to performance issues.

Always weigh the benefits of redundancy reduction against the cost of join operations in your application layer.

Do not normalize simply for the sake of normalization. Focus on eliminating specific anomalies.

Validating Your Decomposition to 3NF

After you have finished moving your attributes, run a validation check on your schema.

Ask yourself: “If I delete a row from the parent table, do I lose information about any non-key entity?”

Ask: “Is there any single table where an attribute depends on another non-key attribute?”

If the answer is no to the second question, you have successfully decomposed the tables.

Finally, check the foreign keys. Every foreign key in a child table must point to a primary key or a unique key in the parent table.

Key Takeaways

• Decomposition to 3NF targets transitive dependencies where non-key attributes depend on other non-key attributes.
• The primary goal is to eliminate data redundancy and prevent update anomalies.
• Move transitive attributes into a new table where the determinant becomes the new primary key.
• Preserve the relationship between tables using Foreign Keys to maintain referential integrity.
• Always validate that no information is lost and that the original relationships can be reconstructed.