What Are 1NF, 2NF, and 3NF in Schema Design?
Learn the differences between 1NF, 2NF, and 3NF with clear rules and a worked SQL example for database schema design interviews.
Expected Interview Answer
1NF, 2NF, and 3NF are progressive normalization rules that remove repeating groups, partial key dependencies, and transitive dependencies from a table, each level building on the one before it to reduce data redundancy and update anomalies.
First Normal Form (1NF) requires every column to hold a single atomic value with no repeating groups or embedded lists. Second Normal Form (2NF) requires 1NF plus that every non-key column depends on the entire primary key, not just part of a composite key. Third Normal Form (3NF) requires 2NF plus that every non-key column depends only on the primary key and not on another non-key column (no transitive dependency). Applying these in order systematically splits a messy table into smaller, well-behaved tables joined by foreign keys.
- Eliminates duplicate data across rows
- Prevents insert, update, and delete anomalies
- Keeps each fact stored in exactly one place
- Produces a schema that is easier to evolve safely
AI Mentor Explanation
Imagine a single scoresheet column listing "Batsmen: Kohli, Rohit, Gill" for one innings entry — that violates 1NF because the cell holds multiple values instead of one atomic name per row. Splitting it into one row per batsman fixes 1NF. If a composite key of (MatchId, PlayerId) is used but the player's jersey number only depends on PlayerId, not the full match key, that partial dependency violates 2NF, so jersey number moves to a separate Players table. If a team's stadium name is stored redundantly next to team name in every match row, that transitive dependency violates 3NF, so stadium moves into a Teams table keyed by team alone.
Step-by-Step Explanation
Step 1
Apply 1NF
Remove repeating groups and multi-valued columns so every cell holds one atomic value.
Step 2
Apply 2NF
Ensure every non-key column depends on the whole composite primary key, not just part of it.
Step 3
Apply 3NF
Ensure every non-key column depends only on the primary key, not on another non-key column.
Step 4
Split into related tables
Move violating columns into their own tables connected by foreign keys.
What Interviewer Expects
- Correct, precise definitions of 1NF, 2NF, and 3NF
- Ability to identify a violation from an example table
- Understanding that each form builds on the previous one
- Awareness that normalization trades some read simplicity for write safety
Common Mistakes
- Confusing 2NF partial dependency with 3NF transitive dependency
- Applying 3NF checks to a table that only has a single-column key, where 2NF is automatic
- Believing normalization always improves query performance
- Not being able to point to which column causes which violation
Best Answer (HR Friendly)
“1NF, 2NF, and 3NF are three levels of table design cleanliness. 1NF says every cell should hold one value. 2NF says every extra column should depend on the whole key, not just part of it. 3NF says every extra column should depend directly on the key, not on some other column. Following all three keeps data from being duplicated and prevents inconsistent updates.”
Code Example
-- Violates 1NF, 2NF, and 3NF: repeating values, partial and transitive dependencies
CREATE TABLE OrderInfo (
order_id INT,
product_id INT,
product_name VARCHAR(100), -- depends only on product_id (2NF violation)
customer_id INT,
customer_city VARCHAR(100), -- depends on customer_id, not order (3NF violation)
PRIMARY KEY (order_id, product_id)
);
-- After normalizing to 3NF:
CREATE TABLE Orders (
order_id INT PRIMARY KEY,
customer_id INT REFERENCES Customers(customer_id)
);
CREATE TABLE Products (
product_id INT PRIMARY KEY,
product_name VARCHAR(100)
);
CREATE TABLE OrderItems (
order_id INT REFERENCES Orders(order_id),
product_id INT REFERENCES Products(product_id),
PRIMARY KEY (order_id, product_id)
);
CREATE TABLE Customers (
customer_id INT PRIMARY KEY,
customer_city VARCHAR(100)
);Follow-up Questions
- What is Boyce-Codd Normal Form and how does it differ from 3NF?
- When might you deliberately denormalize a schema?
- How do foreign keys enforce relationships created by normalization?
- What is a transitive dependency, in your own words?
MCQ Practice
1. A table storing a comma-separated list of phone numbers in one column violates which normal form?
1NF requires atomic values; a packed list in one cell is not atomic, so it fails 1NF before any higher form applies.
2. A non-key column that depends on only part of a composite primary key violates which form?
2NF specifically requires every non-key column to depend on the entire composite key, not just a portion of it.
3. A non-key column that depends on another non-key column instead of the primary key violates which form?
3NF forbids transitive dependencies, where a non-key column depends on another non-key column rather than the key directly.
Flash Cards
What does 1NF require? — Every column holds a single atomic value; no repeating groups or lists.
What does 2NF require? — 1NF plus every non-key column depends on the whole composite primary key.
What does 3NF require? — 2NF plus no non-key column depends on another non-key column (no transitive dependency).
Why normalize to 3NF? — To eliminate redundant data and prevent insert, update, and delete anomalies.