Course contentsShow
← Computer Science
Lesson 1636 of 2,87237. Database Design and NormalizationFree lesson

Achieving Second Normal Form

Decomposing 1NF tables to eliminate partial dependencies by creating separate tables for each key component.

Achieving Second Normal Form

You'll learn how to eliminate partial dependencies by splitting one table into smaller, focused tables.

What Does "Achieving 2NF" Mean?

You've identified partial dependencies in your 1NF table—now it's time to fix them. Achieving Second Normal Form means decomposing (splitting) your table so that every non-key attribute depends on the entire primary key, not just part of it.

This only matters when you have a composite key (a primary key made of multiple columns). If a column depends on only one part of that composite key, it creates a partial dependency—and that's what we're eliminating.

The Decomposition Process

Step 1: Identify all partial dependencies in your 1NF table.

Step 2: For each part of the composite key that has attributes depending only on it, create a new table:

  • That key component becomes the primary key of the new table
  • Move all partially dependent attributes into this new table

Step 3: Keep a table with the original composite key, retaining only attributes that depend on the full key.

Example

Suppose you have:

OrderDetails (OrderID, ProductID, ProductName, Quantity)

  • Primary key: (OrderID, ProductID)
  • ProductName depends only on ProductID (partial dependency!)

Decompose into:

  1. Products (ProductID, ProductName)
  2. OrderDetails (OrderID, ProductID, Quantity)

Now ProductName lives where it belongs—with Product. OrderDetails references it via ProductID (which will become a foreign key).

Why This Matters

By moving partially-dependent data to its own table, you avoid redundancy and update anomalies. ProductName is stored once per product, not once per order line.

Key Takeaway: Achieving 2NF means creating separate tables for each component of a composite key, moving attributes that depend only on that component into their own table.