Second Normal Form (2NF)

Some normalization problems appear only after 1NF is already fixed. Your cells are atomic. Your repeating groups are gone. But a table can still store facts in the wrong place.

Second normal form (2NF) asks one focused question: when a table has a composite key, does every non-key column depend on the whole key?

2NF only matters with composite keys

A composite key is a primary key made from more than one column. For example, an order item can be identified by the pair (order_id, product_id): the same product can appear on many orders, and the same order can contain many products, but the pair identifies one line.

If a table has a single-column primary key, it cannot have a dependency on only part of the key. There is no part to split off. That is why 2NF is specifically about composite-key tables.

Partial dependency: the 2NF problem

A partial dependency happens when a non-key column depends on only one part of a composite key.

Consider this 1NF table:

The key is (order_id, product_id). The quantity depends on the whole key: order 100 bought 2 of product 10, while order 101 bought 1 of the same product.

But product_name depends only on product_id. Product 10 is Notebook no matter which order it appears on.

That partial dependency means the product name is copied into every order line for that product. If the name changes from Notebook to Field Notebook, many rows must be updated.

Quick check

QuestionSelect one

When does 2NF become relevant?

Whenever a table has any text column.

When a table has a composite key and a non-key column may depend on only part of it.

Only when a table has no primary key at all.

Only after denormalizing a reporting table.

QuestionSelect one

In an order item keyed by order and product, which column most clearly depends on the whole key?

Product name.

Product category.

Quantity ordered.

Customer email.

The fix: split facts by what they depend on

To reach 2NF, move each partial dependency to the table where its determinant is the key. Product facts belong in products. Order-line facts stay in order_items.

The split removes the repeated product name from the order-items table. The order item keeps only facts about the complete (order_id, product_id) pair.

A step-by-step 2NF test

When you see a composite-key table, walk through each non-key column:

For order_items, the test looks like this:

• quantity: depends on both order_id and product_id, so it stays.
• product_name: depends only on product_id, so it moves.
• order_date: depends only on order_id, so it belongs in orders.

2NF in the normal-forms path

1NF made every cell atomic. 2NF now checks whether each non-key column belongs with the whole composite key. 3NF will ask whether non-key columns depend on other non-key columns.

Check your understanding

QuestionSelect one

A table has primary key (order_id, product_id) and columns product_name and quantity. Which column violates 2NF?

Quantity, because it is numeric.

Product name, because it depends only on product_id.

Product_id, because it is part of the key.

Order_id, because it repeats across rows.

QuestionSelect one

Where should product_name go when fixing the order_items example?

In every order_items row for readability.

In a products table keyed by product_id.

In the orders table keyed by order_id.

In no table at all.

QuestionSelect one

Which statement best describes a column that is safe to keep in a 2NF order_items table?

It depends only on order_id.

It depends only on product_id.

It depends on the specific combination of order_id and product_id.

It is repeated in many rows.

QuestionSelect one

A table has a single-column primary key. What should you remember about 2NF?

It must be checked for partial dependencies in the same way as a composite-key table.

It automatically has no possible partial dependency on part of a composite key.

Every non-key column must be deleted.

It must be converted to a composite key before it can be normalized.