Skip to content
SQLDatabase Designintermediate45 min read

First, Second, and Third Normal Forms

Transform relational tables step by step using First Normal Form (1NF), Second Normal Form (2NF), and Third Normal Form (3NF) to reduce redundancy and prevent data anomalies.

On This Page (Table of Contents)

In this lesson, you will learn

  • ✓Transform tables into 1NF by eliminating repeating groups and representing individual values appropriately.
  • ✓Achieve 2NF by removing partial dependencies of non-prime attributes on candidate keys.
  • ✓Achieve 3NF by identifying and resolving transitive dependencies in common database designs.
  • ✓Identify candidate keys, composite keys, and functional dependencies.
  • ✓Explain how normalization reduces insertion, update, and deletion anomalies.
  • ✓Use primary keys, foreign keys, and JOIN operations to implement and query a normalized schema.
  • ✓Distinguish the introductory explanations of normal forms from their formal definitions.

1. Introduction: Why Do We Need Normal Forms?

Normalization is a way to organize relational tables so that facts are stored consistently and unnecessary duplication is reduced.

The first three normal forms address different aspects of table design:

- **1NF:** Avoid repeating groups and represent values atomically within the intended data model.
- **2NF:** Remove partial dependencies of non-prime attributes on candidate keys.
- **3NF:** Satisfy the formal third-normal-form condition, which addresses certain dependencies that can cause redundancy.

Consider a college database. Students enroll in courses, and each course is taught by an instructor.

A poorly designed table might store student details, course details, instructor details, and grades together. Student names and course descriptions may be repeated many times.

This can create three anomalies:

- **Update anomaly:** A course name is changed in some rows but not others.
- **Insertion anomaly:** A course cannot be stored until a student enrolls.
- **Deletion anomaly:** Deleting the last enrollment accidentally removes the only stored course information.

We will transform an illustrative enrollment table step by step. The exact dependencies used in each stage are assumptions about the example's business rules. Real schemas must be normalized according to their actual candidate keys and functional dependencies.

2. Before Normalization: Identify the Problem

Suppose the database initially stores student and course information in one table.

| student_id | student_name | course_ids | course_names |
|---|---|---|---|
| 1 | Asha | 101, 102 | SQL, Python |
| 2 | Ravi | 101 | SQL |

The columns course_ids and course_names contain multiple values in one field.

This representation is inconvenient because:
- Finding students enrolled in course 101 requires searching inside a list.
- Enforcing a foreign key for each listed course is not straightforward.
- Adding or removing one course involves editing a collection stored in a single value.

The first step is to represent each student-course association as its own row.

An unnormalized table

An unnormalized table
sql
-- Illustrative design: multiple values are stored in each course column.
CREATE TABLE unnormalized_enrollments (
    student_id INTEGER PRIMARY KEY,
    student_name VARCHAR(100) NOT NULL,
    course_ids TEXT NOT NULL,
    course_names TEXT NOT NULL
);
 
INSERT INTO unnormalized_enrollments
    (student_id, student_name, course_ids, course_names)
VALUES
    (1, 'Asha', '101,102', 'SQL,Python'),
    (2, 'Ravi', '101', 'SQL');
 
SELECT *
FROM unnormalized_enrollments
ORDER BY student_id;
Output
student_id | student_name | course_ids | course_names -----------+--------------+------------+------------- 1 | Asha | 101,102 | SQL,Python 2 | Ravi | 101 | SQL

This table is deliberately unnormalized for teaching purposes. The lists are stored as text, so individual course values are not represented as separate relational rows.

3. First Normal Form (1NF): One Value per Attribute

First Normal Form (1NF) requires each attribute to contain an atomic value relative to the chosen data model and eliminates repeating groups.

For our example, store one student-course association per row.

| student_id | student_name | course_id | course_name |
|---|---|---|---|
| 1 | Asha | 101 | SQL |
| 1 | Asha | 102 | Python |
| 2 | Ravi | 101 | SQL |

Now each row represents one enrollment, and course_id contains one course identifier.

For this example, assume the candidate key is (student_id, course_id), meaning a student can enroll in a particular course only once.

However, the table still repeats facts:
- Asha's name appears in multiple rows.
- The SQL course name appears in multiple rows.

The table illustrates the 1NF transformation, but it is not yet in 2NF under the dependencies we will examine next.

Remember: 1NF does not mean every value in the table must be unique. It means the table's attributes and rows follow the intended relational representation.

Represent one enrollment per row

Represent one enrollment per row
sql
CREATE TABLE enrollments_1nf (
    student_id INTEGER NOT NULL,
    student_name VARCHAR(100) NOT NULL,
    course_id INTEGER NOT NULL,
    course_name VARCHAR(100) NOT NULL,
    grade VARCHAR(2),
    PRIMARY KEY (student_id, course_id)
);
 
INSERT INTO enrollments_1nf
    (student_id, student_name, course_id, course_name, grade)
VALUES
    (1, 'Asha', 101, 'SQL', 'A'),
    (1, 'Asha', 102, 'Python', 'B'),
    (2, 'Ravi', 101, 'SQL', 'A');
 
SELECT student_id, student_name, course_id, course_name, grade
FROM enrollments_1nf
ORDER BY student_id, course_id;
Output
student_id | student_name | course_id | course_name | grade -----------+--------------+-----------+-------------+------ 1 | Asha | 101 | SQL | A 1 | Asha | 102 | Python | B 2 | Ravi | 101 | SQL | A

The table uses one row per student-course association. The composite primary key prevents duplicate enrollments for the same student and course, assuming that is the intended business rule.

4. Second Normal Form (2NF): Remove Partial Dependencies

Second Normal Form (2NF) requires:
1. The table is in 1NF.
2. Every non-prime attribute is fully functionally dependent on every candidate key.

A non-prime attribute is one that does not belong to any candidate key.

In our example, assume the following functional dependencies:

- student_id -> student_name
- course_id -> course_name
- (student_id, course_id) -> grade

The composite key is (student_id, course_id).

Look carefully:
- student_name depends on student_id alone.
- course_name depends on course_id alone.
- grade depends on the student-course pair.

The student name and course name therefore have partial dependencies on the composite key. Under these assumptions, the table violates 2NF.

To resolve this, separate the student facts, course facts, and enrollment facts:

students(student_id, student_name)

courses(course_id, course_name)

enrollments(student_id, course_id, grade)

Each non-prime attribute now depends on the key of the table where it belongs.

Important: The formal definition uses all candidate keys, not only the chosen primary key. The example focuses on the stated composite key and dependencies.

Create the 2NF tables

Create the 2NF tables
sql
CREATE TABLE students (
    student_id INTEGER PRIMARY KEY,
    student_name VARCHAR(100) NOT NULL
);
 
CREATE TABLE courses (
    course_id INTEGER PRIMARY KEY,
    course_name VARCHAR(100) NOT NULL
);
 
CREATE TABLE enrollments (
    student_id INTEGER NOT NULL REFERENCES students(student_id),
    course_id INTEGER NOT NULL REFERENCES courses(course_id),
    grade VARCHAR(2),
    PRIMARY KEY (student_id, course_id)
);
 
INSERT INTO students (student_id, student_name)
VALUES (1, 'Asha'), (2, 'Ravi');
 
INSERT INTO courses (course_id, course_name)
VALUES (101, 'SQL'), (102, 'Python');
 
INSERT INTO enrollments (student_id, course_id, grade)
VALUES (1, 101, 'A'), (1, 102, 'B'), (2, 101, 'A');
Output
Three tables are created and populated: students, courses, and enrollments. Student and course descriptions are no longer stored in every enrollment row.

The primary keys identify the records in each table. Foreign keys ensure that an enrollment references an existing student and course. Run this example in a clean schema where these table names do not already exist.

5. Third Normal Form (3NF): Remove Transitive Dependencies

Third Normal Form (3NF) requires 2NF and satisfies the formal dependency condition:

For every non-trivial functional dependency X -> A, either:
- X is a superkey, or
- A is a prime attribute.

In common introductory examples, 3NF is often explained by removing transitive dependencies of non-key attributes on a key.

Consider an employee table:

| employee_id | employee_name | department_id | department_name |
|---|---|---|---|
| 1 | Asha | 10 | Engineering |
| 2 | Ravi | 10 | Engineering |
| 3 | Meera | 20 | Finance |

Assume these dependencies:

- employee_id -> employee_name, department_id
- department_id -> department_name

The department name is determined by department_id, which is in turn determined by employee_id.

The department name is a fact about the department, not about each individual employee. Keeping it in the employee table duplicates that fact.

Separate the data into:

departments(department_id, department_name)

employees(employee_id, employee_name, department_id)

The employee table now stores the department's identifier and references the department record.

This is the common 3NF transformation. Formal assessment still depends on all candidate keys and functional dependencies, so the phrase "remove transitive dependencies" is a useful teaching shortcut rather than the complete formal definition.

Decompose employee and department information

Decompose employee and department information
sql
CREATE TABLE departments (
    department_id INTEGER PRIMARY KEY,
    department_name VARCHAR(100) NOT NULL
);
 
CREATE TABLE employees (
    employee_id INTEGER PRIMARY KEY,
    employee_name VARCHAR(100) NOT NULL,
    department_id INTEGER NOT NULL
        REFERENCES departments(department_id)
);
 
INSERT INTO departments (department_id, department_name)
VALUES (10, 'Engineering'), (20, 'Finance');
 
INSERT INTO employees (employee_id, employee_name, department_id)
VALUES
    (1, 'Asha', 10),
    (2, 'Ravi', 10),
    (3, 'Meera', 20);
 
SELECT
    e.employee_id,
    e.employee_name,
    d.department_name
FROM employees AS e
JOIN departments AS d
    ON d.department_id = e.department_id
ORDER BY e.employee_id;
Output
employee_id | employee_name | department_name ------------+---------------+---------------- 1 | Asha | Engineering 2 | Ravi | Engineering 3 | Meera | Finance

The department name is stored in one department record and retrieved with a JOIN. Multiple employees can reference the same department, which is a normal one-to-many relationship.

6. Compare 1NF, 2NF, and 3NF

| Normal form | Main requirement | Problem addressed |
|---|---|---|
| 1NF | Atomic attributes and no repeating groups in the intended relational model | Lists or repeating groups embedded in columns |
| 2NF | 1NF plus full functional dependency of non-prime attributes on every candidate key | Partial dependencies on part of a candidate key |
| 3NF | 2NF plus the formal 3NF functional-dependency condition | Certain dependencies that create redundant facts |

Use this order when examining a table:

1. Identify its attributes and candidate keys.
2. Check whether values and repeating groups follow 1NF.
3. Check whether any non-prime attribute depends on only part of a candidate key.
4. Examine the functional dependencies for 3NF violations.
5. Decompose the table only when the dependencies justify it.
6. Add primary keys, foreign keys, and other constraints that reflect the business rules.

Passing a visual inspection is not enough to prove a table is in a particular normal form. You must understand the candidate keys and functional dependencies.

7. Querying the Normalized Design

Normalization separates facts into related tables. When an application needs information from more than one table, it can combine those facts using JOIN.

For example, the enrollment table contains the student_id, course_id, and grade. It does not need to repeat the student name or course name.

A JOIN uses the foreign keys to retrieve the complete enrollment report.

This is not a failure of normalization. The tables are separated to maintain facts consistently, and JOIN reconstructs the information required by a query.

Build an enrollment report

Build an enrollment report
sql
SELECT
    s.student_name,
    c.course_name,
    e.grade
FROM enrollments AS e
JOIN students AS s
    ON s.student_id = e.student_id
JOIN courses AS c
    ON c.course_id = e.course_id
ORDER BY s.student_id, c.course_id;
Output
student_name | course_name | grade -------------+-------------+------ Asha | SQL | A Asha | Python | B Ravi | SQL | A

The query combines data stored in separate tables. The expected rows match the illustrative records inserted in the earlier example, assuming the tables are created and populated as shown.

8. Common Mistakes

Mistake 1: Thinking 1NF means every value in the entire table must be unique.
Correction: Values can repeat. The goal is atomic attributes and a proper relational representation.

Mistake 2: Thinking a composite primary key automatically makes a table 2NF.
Correction: A composite key identifies rows, but non-prime attributes may still depend on only part of that key.

Mistake 3: Thinking a single-column primary key automatically guarantees 3NF.
Correction: Other candidate keys and functional dependencies can still produce normal-form violations.

Mistake 4: Splitting a table without preserving its relationships.
Correction: Use appropriate keys and constraints so the intended facts can be related after decomposition.

Mistake 5: Assuming every repeated value is redundant.
Correction: Repeated foreign-key values are expected when multiple rows reference the same entity.

Mistake 6: Assuming normalization always improves query speed.
Correction: Normalization primarily supports integrity and maintainability. Query performance also depends on indexes, execution plans, data volume, and workload.

Mistake 7: Defining normal forms only by memorized phrases.
Correction: Practice identifying candidate keys and functional dependencies in concrete examples.

9. Practice Exercises

Exercise 1 — 1NF:
A student row contains course_names = 'SQL, Python, Java'. Explain the issue and describe a relational alternative.

Exercise 2 — 2NF:
A table has candidate key (order_id, product_id), along with order_date, product_name, and quantity. Assume order_id determines order_date, product_id determines product_name, and the composite key determines quantity. Identify the partial dependencies and suggest a decomposition.

Exercise 3 — 3NF:
An employee table stores employee_id, department_id, and department_name. Assume employee_id determines department_id and department_id determines department_name. Explain why department_name should usually be stored with the department.

Exercise 4 — Dependencies:
What does X -> Y mean in a functional dependency?

Exercise 5 — Formal definitions:
Why is "remove transitive dependencies" a useful but incomplete description of 3NF?

Exercise 6 — Querying:
After splitting students, courses, and enrollments into separate tables, how can an application retrieve the student name, course name, and grade together?

Exercise 7 — Business rules:
If a student is allowed to retake a course and each attempt has its own grade, why might (student_id, course_id) no longer be a sufficient key for enrollments? Suggest an additional attribute that could identify an attempt.

Practice solution: order, product, and order-line facts

Practice solution: order, product, and order-line facts
sql
CREATE TABLE orders (
    order_id INTEGER PRIMARY KEY,
    order_date DATE NOT NULL
);
 
CREATE TABLE products (
    product_id INTEGER PRIMARY KEY,
    product_name VARCHAR(150) NOT NULL
);
 
CREATE TABLE order_items (
    order_id INTEGER NOT NULL REFERENCES orders(order_id),
    product_id INTEGER NOT NULL REFERENCES products(product_id),
    quantity INTEGER NOT NULL CHECK (quantity > 0),
    PRIMARY KEY (order_id, product_id)
);
Output
The schema stores order dates with orders, product names with products, and quantities with individual order-product associations.

This decomposition assumes each product appears at most once per order. If the application allows multiple separate lines for the same product in an order, include an order_line_id or an appropriate line number in the key.

10. Knowledge Check

Answer the questions to check your understanding of 1NF, 2NF, and 3NF.

11. Interview Questions

Q1. What is 1NF?
Answer: First Normal Form requires attributes to contain atomic values relative to the intended data model and eliminates repeating groups.

Q2. What is 2NF?
Answer: Second Normal Form requires 1NF and full functional dependency of every non-prime attribute on every candidate key.

Q3. What is a partial dependency?
Answer: It occurs when an attribute depends on a proper subset of a composite candidate key rather than on the whole key.

Q4. What is 3NF?
Answer: Formally, for every non-trivial functional dependency X -> A, X must be a superkey or A must be a prime attribute, assuming the standard relational definition.

Q5. What is a transitive dependency?
Answer: A common pattern is that a key determines one attribute, and that attribute determines another. If this creates a dependency of a non-key attribute through another non-key attribute, it may cause redundancy.

Q6. Why is a composite key important when studying 2NF?
Answer: It allows partial dependencies to occur when a non-prime attribute depends on only part of the key.

Q7. Does normalization mean eliminating every repeated value?
Answer: No. Repeated foreign-key values are normal when multiple records refer to the same entity.

Q8. Why do normalized schemas often require JOINs?
Answer: Related facts are stored separately and combined through their keys when a query needs information from multiple tables.

Q9. Can a table with a single-column primary key violate 3NF?
Answer: Yes. Other candidate keys and functional dependencies can still violate 3NF.

Q10. Does normalization always improve performance?
Answer: No. Normalization primarily improves data organization and integrity. Performance depends on workload, indexes, execution plans, and other design choices.

12. Lesson Summary

The first three normal forms help organize relational data around keys and functional dependencies.

- **1NF:** Use an appropriate atomic representation and eliminate repeating groups.
- **2NF:** Ensure non-prime attributes fully depend on every candidate key, not merely part of a composite key.
- **3NF:** Satisfy the formal dependency condition that prevents certain forms of redundancy.
- **Functional dependencies:** Explain which attributes determine other attributes.
- **Primary and foreign keys:** Identify records and connect related tables.
- **JOINs:** Retrieve related information from normalized tables.
- **Business rules:** Determine the real candidate keys and dependencies.
- **Performance:** Evaluate separately from normalization, using actual workload evidence.

The best way to learn normalization is to take a table, identify its candidate keys and dependencies, explain the anomalies it permits, and then justify each decomposition.

Common pitfalls to avoid

Real debugging lessons and exact syntax contrasts to avoid runtime bugs

#1 Treating 1NF as a rule that every column must have globally unique values

Why it happens: Atomicity is confused with uniqueness.

Incorrect Approach
sql
sql
-- SQL, Python, and Java are stored in one text field.
INSERT INTO student_courses (student_id, courses)
VALUES (1, 'SQL, Python, Java');
Correct Solution
sql
sql
-- Represent each student-course association as a separate row.
CREATE TABLE student_courses (
    student_id INTEGER NOT NULL,
    course_name VARCHAR(100) NOT NULL,
    PRIMARY KEY (student_id, course_name)
);

Key takeaway: A repeating collection should be represented appropriately in the relational model. The example assumes a student cannot have duplicate associations with the same course name.

#2 Assuming a composite primary key guarantees 2NF

Why it happens: Uniqueness of the key is mistaken for full functional dependency.

Incorrect Approach
sql
sql
-- student_name depends only on student_id,
-- even though the key is (student_id, course_id).
Correct Solution
sql
sql
-- Store student facts separately from enrollment facts.
CREATE TABLE students (
    student_id INTEGER PRIMARY KEY,
    student_name VARCHAR(100) NOT NULL
);
 
CREATE TABLE enrollments (
    student_id INTEGER NOT NULL REFERENCES students(student_id),
    course_id INTEGER NOT NULL REFERENCES courses(course_id),
    grade VARCHAR(2),
    PRIMARY KEY (student_id, course_id)
);

Key takeaway: The student name belongs with the student entity, while the grade belongs to the enrollment, according to the dependencies assumed in this lesson.

#3 Defining 3NF only as removing transitive dependencies

Why it happens: A simplified classroom explanation is mistaken for the full formal definition.

Incorrect Approach
sql
sql
-- Declare a table normalized without examining
-- its candidate keys and functional dependencies.
Correct Solution
sql
sql
-- For each non-trivial dependency X -> A,
-- verify that X is a superkey or A is a prime attribute.
-- Also verify the preceding normal-form requirements.

Key takeaway: The transitive-dependency explanation is useful for common examples, but the formal 3NF definition is based on functional dependencies, superkeys, and prime attributes.

Ready for hands-on challenges?

Test your skills with shuffled coding challenges in the Practice Arena.

Practice Arena →

First, Second, and Third Normal Forms Knowledge Check

Test your understanding with immediate feedback

Question 1 of 6

What is the main concern addressed by 1NF in a basic relational design?

Question 2 of 6

A table has candidate key (student_id, course_id), and student_id determines student_name. What does this illustrate?

Question 3 of 6

What is required for 2NF?

Question 4 of 6

Assume employee_id determines department_id and department_id determines department_name. What is the common design issue if department_name is stored in every employee row?

Question 5 of 6

Does a single-column primary key guarantee that a table is in 3NF?

Question 6 of 6

Why are foreign-key values allowed to repeat?

Key takeaways

  • 1NF requires values to be atomic according to the intended data model and eliminates repeating groups.
  • 2NF requires 1NF and full functional dependency of every non-prime attribute on every candidate key.
  • 3NF requires 2NF and, for every non-trivial functional dependency X -> A, either X is a superkey or A is a prime attribute.
  • Partial dependencies are especially important when a candidate key contains multiple attributes.
  • Transitive dependencies can cause descriptive facts to be duplicated across records.
  • Normalization is based on keys, functional dependencies, and business rules—not merely on splitting a table into smaller tables.
  • Foreign keys connect normalized tables, and JOIN operations reconstruct related information.
Was this lesson helpful?
Found an error? Report an issue