MENU


Second Normal Form

Second Normal Form (2NF) builds on First Normal Form. A table is in 2NF when:

It is already in 1NF (atomic columns, no repeating groups).
Every non-key column depends on the whole of every candidate key, not just part of it – no partial functional dependency.

2NF only has teeth when a table's primary key is a composite key (two or more columns). If a table's primary key is a single column, it is automatically in 2NF as soon as it is in 1NF, since there is no way for a non-key column to depend on only "part" of a one-column key.

Symptom: Partial Dependency

A partial dependency happens when a column depends on only a subset of the composite key. This produces redundancy: the same fact is repeated once per row that shares that subset, and an update must be applied consistently to every one of those rows or the data goes inconsistent.

The Fix

Move each partially-dependent column, together with the part of the key it actually depends on, into its own table. The original table keeps only the columns that depend on the full composite key.

Worked Example

Continuing the order_items_unnormalized table from Functional Dependencies, with composite key (order_id, product_id):

(order_id, product_id) → quantityFull dependency – stays as-is.
product_id → product_name, unit_pricePartial dependency – depends only on product_id, not order_id. Violates 2NF.
order_id → customer_id, customer_cityNot a dependency on any part of the composite key at all (order_id alone isn't the key here) – this one is a transitive dependency, left for the next page.

Before — product_name and unit_price are repeated on every order line that includes that product; renaming a product or changing its price means updating every historical order line that referenced it.

After — product_name and unit_price move into a products table keyed on product_id alone, leaving order_items with only the columns that genuinely depend on the full (order_id, product_id) pair. See the worked CREATE TABLE statements below. The orders table produced here still carries customer_id and customer_city together, which is a transitive dependency resolved by Third Normal Form, not by 2NF. 

-- BEFORE: violates 2NF (product_name, unit_price partially depend on
-- product_id only, not on the full composite key (order_id, product_id))
CREATE TABLE order_items_1nf (
    order_id      INT UNSIGNED NOT NULL,
    product_id    INT UNSIGNED NOT NULL,
    customer_id   INT UNSIGNED NOT NULL,
    customer_city VARCHAR(80)  NOT NULL,
    product_name  VARCHAR(120) NOT NULL,
    unit_price    DECIMAL(10,2) NOT NULL,
    quantity      INT UNSIGNED NOT NULL,
    PRIMARY KEY (order_id, product_id)
) ENGINE=InnoDB;

-- Changing a product's price means updating it on every order line that
-- ever referenced the product:
-- UPDATE order_items_1nf SET unit_price = 12.50 WHERE product_id = 501;

-- AFTER: 2NF-compliant. product_name/unit_price moved to their own table,
-- keyed on the column they actually depend on (product_id alone).
CREATE TABLE products (
    product_id   INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    product_name VARCHAR(120)  NOT NULL,
    unit_price   DECIMAL(10,2) NOT NULL
) ENGINE=InnoDB;

-- orders still carries customer_id/customer_city together for now; that
-- transitive dependency is resolved in Third-Normal-Form.
CREATE TABLE orders (
    order_id      INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    customer_id   INT UNSIGNED NOT NULL,
    customer_city VARCHAR(80)  NOT NULL
) ENGINE=InnoDB;

CREATE TABLE order_items (
    order_id   INT UNSIGNED NOT NULL,
    product_id INT UNSIGNED NOT NULL,
    quantity   INT UNSIGNED NOT NULL,
    PRIMARY KEY (order_id, product_id),
    CONSTRAINT fk_order_items_order
        FOREIGN KEY (order_id) REFERENCES orders (order_id)
        ON DELETE CASCADE,
    CONSTRAINT fk_order_items_product
        FOREIGN KEY (product_id) REFERENCES products (product_id)
) ENGINE=InnoDB;

-- A price change is now a single-row UPDATE, seen immediately by every
-- order that references the product:
UPDATE products SET unit_price = 12.50 WHERE product_id = 501;

-- Rebuilding the original flattened view is a JOIN, not a redundant column:
SELECT o.order_id, p.product_name, p.unit_price, oi.quantity
FROM order_items oi
JOIN orders o   ON o.order_id = oi.order_id
JOIN products p ON p.product_id = oi.product_id;