Second Normal Form (2NF)

Master partial dependencies and transform BookVault tables from 1NF to clean 2NF structure using functional dependency analysis.

What Makes Second Normal Form Special

First Normal Form gets your data atomic. Second Normal Form tackles a deeper problem: partial dependencies. When part of your composite key determines other columns, you're storing redundant data everywhere.

Think about Netflix's movie ratings table. If they stored (user_id, movie_id, rating, user_name, movie_title), what happens? User "Sarah Chen" appears in thousands of rows. Change her name once? Update thousands of records. That's a partial dependency nightmare.

Second Normal Form demands: "If a table has a composite primary key, every non-key column must depend on the entire key, not just part of it." Simple rule. Massive impact on data integrity.

Understanding Partial Dependencies

Before fixing BookVault's 2NF violations, you need to spot them. A partial dependency occurs when a non-key column depends on only part of a composite primary key.

Here's BookVault's problematic order_items table from our 1NF lesson. Notice the composite key (order_id, book_id) and what depends on what:

See the problem? book_title depends only on book_id, not the complete key (order_id, book_id). Same with order_date — it only depends on order_id.

Result? "The Great Gatsby" appears twice. Order date "2024-01-15" repeats. Every book in every order carries redundant information. Database bloat and update anomalies guaranteed.

Functional Dependency Analysis

Map out exactly what determines what in the problematic table. This reveals where to split:

The red dependencies violate 2NF. Time to decompose this table into proper 2NF structure.

BookVault 2NF Transformation

Fix partial dependencies by creating separate tables. Each table holds attributes that depend on the same key. No more redundancy, no more update anomalies.

Split the problematic table into three clean 2NF tables:

Beautiful. Each table now holds attributes that depend on its complete primary key. Book title "The Great Gatsby" appears once. Order date "2024-01-15" appears once. Zero redundancy, maximum integrity.

Creating the 2NF Schema

Transform BookVault's structure into clean 2NF tables. Each table serves a single purpose with no partial dependencies:

-- BookVault 2NF: Orders table (order-level attributes only)
-- No partial dependencies - all columns depend on complete key
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT NOT NULL,
    order_date DATE NOT NULL,
    status VARCHAR(20) DEFAULT 'pending',
    total_amount DECIMAL(10,2) NOT NULL
);

Table 'orders' created successfully.
- Primary key: order_id
- Foreign key opportunity: customer_id → customers(customer_id)
- All non-key columns depend on complete primary key

-- BookVault 2NF: Books table (book-level attributes only)
-- Book details separate from order relationships
CREATE TABLE books (
    book_id INT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    author_id INT NOT NULL,
    category_id INT NOT NULL,
    price DECIMAL(8,2) NOT NULL,
    stock_qty INT DEFAULT 0,
    published_year INT
);

Table 'books' created successfully.
- Primary key: book_id
- Book attributes stored once, referenced by foreign keys
- No redundancy across order_items relationships

-- BookVault 2NF: Order items (pure relationship table)
-- Composite key with attributes depending on complete key
CREATE TABLE order_items (
    order_item_id INT PRIMARY KEY,
    order_id INT NOT NULL,
    book_id INT NOT NULL,
    quantity INT NOT NULL CHECK (quantity > 0),
    unit_price DECIMAL(8,2) NOT NULL,
    FOREIGN KEY (order_id) REFERENCES orders(order_id),
    FOREIGN KEY (book_id) REFERENCES books(book_id),
    UNIQUE (order_id, book_id)
);

Table 'order_items' created successfully.
- Primary key: order_item_id (surrogate key)
- Composite unique constraint: (order_id, book_id)
- Both quantity and unit_price depend on specific order-book combination

Testing 2NF Compliance

Verify your 2NF design by testing queries and updates. If you can modify book information once and see changes everywhere, you've achieved 2NF success.

-- Test 2NF: Insert sample data into normalized tables
-- Book information stored once, referenced by foreign keys
INSERT INTO orders VALUES 
(1001, 501, '2024-01-15', 'completed', 44.98),
(1002, 502, '2024-01-16', 'pending', 24.99);

INSERT INTO books VALUES 
(201, 'The Great Gatsby', 301, 101, 24.99, 50, 1925),
(205, 'To Kill a Mockingbird', 302, 101, 19.99, 30, 1960);

INSERT INTO order_items VALUES 
(1, 1001, 201, 2, 24.99),
(2, 1001, 205, 1, 19.99),
(3, 1002, 201, 1, 24.99);

3 rows inserted into orders.
2 rows inserted into books.
3 rows inserted into order_items.

Data successfully normalized - each fact stored once.

-- Test 2NF: Update book price in one place
-- Should affect all order calculations through references
UPDATE books 
SET price = 22.99 
WHERE book_id = 201;

-- Verify the change appears in joined results
SELECT o.order_id, b.title, oi.quantity, b.price as current_price, oi.unit_price as order_price
FROM order_items oi
JOIN books b ON oi.book_id = b.book_id  
JOIN orders o ON oi.order_id = o.order_id
WHERE b.book_id = 201;

1 row updated in books table.

order_id | title           | quantity | current_price | order_price
---------|-----------------|----------|---------------|------------
1001     | The Great Gatsby| 2        | 22.99         | 24.99
1002     | The Great Gatsby| 1        | 22.99         | 24.99

Book price updated once, visible across all references.

Common 2NF Violations and Fixes

Spot these typical 2NF problems in real applications. Each creates maintenance nightmares and data inconsistencies:

The Surrogate Key Advantage

Notice how BookVault's order_items uses order_item_id as surrogate primary key? Smart move. Single-column primary keys eliminate partial dependency concerns entirely. You can't have partial dependencies when the key has only one part.

But keep logical constraints. The UNIQUE (order_id, book_id) constraint maintains business logic while the surrogate key simplifies relationships.

Your BookVault database now meets Second Normal Form requirements. No partial dependencies. No redundant data. Each update touches exactly one row. But there's still work ahead — transitive dependencies can lurk in 2NF tables, waiting to cause problems.

Quiz