Second Normal Form 2NF Explained with Examples

Second Normal Form only becomes relevant when a table has a composite primary key — one made of two or more columns together. It requires that every non-key column depend on the entire composite key, not just part of it. This lesson works through a classic 2NF violation step by step, using the exact partial dependency concept introduced in Lesson 8.6.

Key Definitions

• Second Normal Form (2NF): A table satisfies 2NF if it is already in 1NF, and every non-key column depends on the entire composite primary key, not just part of it.
• Composite primary key: A primary key made up of two or more columns combined together to uniquely identify a row.

What You'll Learn

• Define Second Normal Form and its composite key requirement.
• Identify a partial dependency violating 2NF in a sample table.
• Decompose a 2NF-violating table into properly structured tables.
• Understand why tables with a single-column primary key automatically satisfy 2NF.

Detailed Explanation

Consider a table tracking which doctors work in which departments, with columns doctor_id, department_id, doctor_name, and department_location, where the composite primary key is (doctor_id, department_id). Now examine the functional dependencies: doctor_name depends only on doctor_id (not on department_id at all) — a partial dependency. Similarly, department_location depends only on department_id, not on the full composite key — another partial dependency. Both doctor_name and department_location are violating 2NF, since neither truly depends on the complete (doctor_id, department_id) pair.

The practical symptom of this violation is redundancy: if Dr. Verma works in two departments, his doctor_name 'Dr. Verma' gets repeated across both rows, and if that department appears for multiple doctors, its department_location gets repeated too. This redundancy is exactly the kind of update anomaly risk covered in Lesson 8.5.

The fix is decomposition: split the table into three focused tables. A doctors table holding doctor_id and doctor_name (since doctor_name only depends on doctor_id). A departments table holding department_id and department_location (since department_location only depends on department_id). And a doctor_departments junction table holding just the composite key (doctor_id, department_id) itself, representing the relationship without any partially-dependent extra columns attached. Tables with a single-column primary key automatically satisfy 2NF, since there's no way for a dependency to be 'partial' when the entire key is just one column — 2NF only becomes a meaningful concern once composite keys enter the picture.

Visual Summary

A before-and-after diagram. Before: one wide table [doctor_id, department_id, doctor_name, department_location] with two red arrows showing 'doctor_name depends on doctor_id only' and 'department_location depends on department_id only', both bypassing the full composite key. After: three tables — [doctors: doctor_id, doctor_name], [departments: department_id, department_location], [doctor_departments: doctor_id, department_id] — each with a green checkmark.

Quick Reference

Column	Depends On (Full Key or Partial?)	2NF Action
doctor_name	doctor_id only — partial dependency	Move to doctors table
department_location	department_id only — partial dependency	Move to departments table
(no extra columns)	The full composite key (doctor_id, department_id)	Remains in the doctor_departments junction table

SQL Example

-- VIOLATES 2NF: composite key (doctor_id, department_id),
-- but doctor_name and department_location each depend on only PART of it
-- doctor_id | department_id | doctor_name | department_location
-- 101       | 1             | Dr. Verma   | Building A
-- 101       | 3             | Dr. Verma   | Building C   <-- doctor_name repeated!

-- 2NF-COMPLIANT fix: decompose into three focused tables
CREATE TABLE doctors (
  doctor_id   INT PRIMARY KEY AUTO_INCREMENT,
  doctor_name VARCHAR(100) NOT NULL
);

CREATE TABLE departments (
  department_id       INT PRIMARY KEY AUTO_INCREMENT,
  department_location VARCHAR(100)
);

CREATE TABLE doctor_departments (
  doctor_id     INT,
  department_id INT,
  PRIMARY KEY (doctor_id, department_id),
  FOREIGN KEY (doctor_id) REFERENCES doctors(doctor_id),
  FOREIGN KEY (department_id) REFERENCES departments(department_id)
);

In the violating table, 'Dr. Verma' appears twice because he works in two departments, even though his name has nothing to do with which department is being referenced on that particular row — a clear partial dependency symptom. After decomposition, doctor_name lives exactly once in the doctors table, department_location lives exactly once per department in the departments table, and the doctor_departments junction table cleanly represents just the relationship itself, with no redundant partially-dependent data attached.

Real-World Examples

• E-commerce order-line tables (order_id, product_id composite key) commonly violate 2NF if product_name is stored directly on the line item instead of being looked up from a separate products table.
• University enrollment tables (student_id, course_id composite key) violate 2NF if student_name or course_title are stored directly on the enrollment row instead of in their own tables.
• Inventory systems with a (warehouse_id, product_id) composite key violate 2NF if product_description is stored on every warehouse-product row instead of once in a products table.

Common Mistakes to Avoid

• Applying 2NF analysis to tables with a single-column primary key, where it isn't relevant.
• Failing to recognize a partial dependency because the redundancy symptom (like a repeated doctor_name) isn't immediately obvious in a small sample of data.
• Moving the entire table into new tables instead of only relocating the specifically partially-dependent columns.

Interview Questions

Q1. What does Second Normal Form require?

2NF requires that a table already be in 1NF, and that every non-key column depend on the entire composite primary key, not just part of it — eliminating partial dependencies.

Q2. When does 2NF become a relevant concern?

2NF is only relevant for tables with a composite primary key made of two or more columns. A table with a single-column primary key automatically satisfies 2NF, since a dependency can't be 'partial' when the key has only one part.

Q3. How do you fix a table that violates 2NF?

Decompose the table by moving each partially-dependent column into its own table, keyed by whichever part of the composite key it actually depends on, leaving the original table (or a junction table) to represent just the relationship itself.

Practice MCQs

1. 2NF specifically addresses which type of table?

1. Tables with a single-column primary key
2. Tables with a composite primary key
3. Tables with no primary key
4. Tables with foreign keys only

Answer: B. Tables with a composite primary key

Explanation: 2NF's partial dependency concept only applies to tables where the primary key consists of two or more columns combined together.

2. What is the fix for a column that partially depends on only part of a composite key?

1. Delete the column entirely
2. Move it to a separate table keyed by the part of the key it depends on
3. Add it to every other table as well
4. Rename the column

Answer: B. Move it to a separate table keyed by the part of the key it depends on

Explanation: The standard 2NF fix decomposes the table, relocating partially-dependent columns to a table keyed by just the portion of the composite key they actually depend on.

Quick Revision Points

• 2NF only applies to tables with composite primary keys.
• 2NF eliminates partial dependencies: non-key columns depending on only part of the composite key.
• A table with a single-column primary key automatically satisfies 2NF.

Conclusion

• 2NF requires every non-key column to depend on the full composite primary key, not just part of it.
• 2NF is only a relevant concern for tables with composite primary keys.
• Fixing a 2NF violation means relocating partially-dependent columns to their own properly-keyed table.

Second Normal Form applies specifically to tables with composite primary keys, requiring that every non-key column depend on the entire key, not just part of it. A classic violation occurs when a column like doctor_name depends only on doctor_id while the primary key is (doctor_id, department_id) — this partial dependency causes redundancy and is fixed by decomposing the table, relocating each partially-dependent column into its own table keyed appropriately, and leaving a clean junction table to represent just the underlying relationship.