Differences Between 2NF and 3NF

• 2NF (Second Normal Form) – requires 1NF and that every non-key column depends on the *whole* composite primary key, not just part of it; eliminates partial dependencies.
• 3NF (Third Normal Form) – requires 2NF and that every non-key column depends *only* on the key, not on another non-key column; eliminates transitive dependencies (key → A → B is disallowed).
• Scope difference – 2NF is only meaningful for composite keys; 3NF applies to any key including single-column keys.
• Strict ladder – 3NF presupposes 2NF presupposes 1NF.
• BCNF – a stricter version of 3NF requiring every determinant to be a candidate key.

What is a partial dependency (2NF violation)?

A partial dependency happens when a non-key column depends on only part of a composite primary key rather than the entire key. 2NF only becomes relevant when a table has a composite primary key.

-- VIOLATION: branch_name depends only on account_id, not on product_code
CREATE TABLE account_products (
    account_id   VARCHAR(10),
    product_code VARCHAR(10),
    branch_name  VARCHAR(50),   -- depends on account_id alone!
    enrolled_at  TIMESTAMP,
    PRIMARY KEY (account_id, product_code)
);

account_id | product_code | branch_name      | enrolled_at
-----------+--------------+------------------+------------
SAV2001    | SAVINGS      | Mumbai Central   | 2024-01-01
SAV2001    | FIXED_DEP    | Mumbai Central   | 2024-02-01  ← duplicated!
SAV2001    | LOAN         | Mumbai Central   | 2024-03-01  ← duplicated!

branch_name is duplicated for every product because it depends only on account_id. Changing the branch means updating many rows.

How do you fix a 2NF violation?

Split the partially dependent column into a table keyed by the column it actually depends on.

-- Fix: separate table for account-level facts
CREATE TABLE accounts (
    account_id  VARCHAR(10) PRIMARY KEY,
    branch_name VARCHAR(50) NOT NULL
);

CREATE TABLE account_products (
    account_id   VARCHAR(10) REFERENCES accounts(account_id),
    product_code VARCHAR(10),
    enrolled_at  TIMESTAMP,
    PRIMARY KEY (account_id, product_code)
);
-- branch_name lives in one place. No duplication.

What is a transitive dependency (3NF violation)?

A transitive dependency is the chain key → A → B, where a non-key column depends on another non-key column instead of directly on the key.

-- VIOLATION: branch_city depends on branch_code, not on account_id
CREATE TABLE accounts (
    account_id  VARCHAR(10) PRIMARY KEY,
    branch_code VARCHAR(10) NOT NULL,
    branch_city VARCHAR(50) NOT NULL   -- depends on branch_code, not account_id!
);

account_id | branch_code | branch_city
-----------+-------------+------------
SAV2001    | BR001       | Mumbai      ← duplicated!
SAV2002    | BR001       | Mumbai      ← duplicated!
CUR3001    | BR002       | Delhi

branch_city is transitively dependent: account_id → branch_code → branch_city. It is repeated for every account at the same branch.

How do you fix a 3NF violation?

Move the transitively dependent column into a separate table keyed by the column it depends on.

-- Fix: separate table for branch-level facts
CREATE TABLE branches (
    branch_code VARCHAR(10) PRIMARY KEY,
    branch_city VARCHAR(50) NOT NULL
);

CREATE TABLE accounts (
    account_id  VARCHAR(10) PRIMARY KEY,
    branch_code VARCHAR(10) NOT NULL REFERENCES branches(branch_code)
    -- branch_city is no longer here; it lives in branches
);

How do 2NF and 3NF differ?

| Aspect | 2NF | 3NF |
|--------|-----|-----|
| Eliminates | Partial dependencies | Transitive dependencies |
| Relevant when | Composite primary key | Any key (single or composite) |
| Dependency shape | non-key depends on part of key | non-key depends on another non-key |
| Fix | Split partial-dependent column to table keyed by the part | Split transitive-dependent column to its own table |
| Prerequisite | 1NF | 2NF |

What anomalies do they prevent?

Both 2NF and 3NF prevent three anomalies caused by redundancy:

1. Update anomaly – a fact stored in many rows must be updated in many places, risking inconsistency.
2. Insert anomaly – you cannot record a fact because unrelated data is missing.
3. Delete anomaly – deleting one row accidentally erases an unrelated fact.

Before 3NF (branch_city repeated in accounts):
  Update anomaly: Rename Mumbai to Mumbai City → update 1000 rows.
  Insert anomaly: Cannot record a new branch that has no accounts yet.
  Delete anomaly: Deleting the last account at BR002 erases Delhi's city.

After 3NF (branch_city in branches table):
  Update: Change city in one place.
  Insert: Add a branch without any accounts.
  Delete: Removing accounts does not lose branch info.

What is BCNF?

Boyce-Codd Normal Form is a stricter version of 3NF. It requires that every determinant (anything that functionally determines another column) is a candidate key. It closes an edge case 3NF allows when there are overlapping candidate keys.

3NF allows: non-prime attribute determines another non-prime (if via candidate key)
BCNF forbids: any non-candidate-key determinant

In practice, most 3NF schemas are already in BCNF. The distinction matters mainly in academic settings or when candidate keys overlap.

How does this look in Java with JPA?

package com.bank.model;

import jakarta.persistence.*;

@Entity
@Table(name = "branches")
public class Branch {
    @Id
    @Column(name = "branch_code", length = 10)
    private String branchCode;

    @Column(name = "branch_city", nullable = false, length = 50)
    private String branchCity;
}

@Entity
@Table(name = "accounts")
public class Account {
    @Id
    @Column(name = "account_id", length = 10)
    private String accountId;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "branch_code", nullable = false)
    private Branch branch;  // 3NF: city lives in Branch, not here
}

What are common interview follow-up questions?

• "Is a table with a single-column PK automatically in 2NF?" Yes, because partial dependencies require a composite key. With a single-column key there is nothing to partially depend on.
• "What is the difference between 3NF and BCNF?" BCNF is stricter: every determinant must be a candidate key, while 3NF allows non-prime determinants in certain overlapping key scenarios.
• "When would you not normalize to 3NF?" When read performance matters more than write correctness, you deliberately denormalize for faster reads.