Second Normal Form (2NF) and Third Normal Form (3NF) in MySQL

Course: MySQL Training

Topic: Second Normal Form (2NF) and Third Normal Form (3NF)

Course Link: MySQL Training Course at JustAcademy

Demo Link: Register for a Course Demo

Website: JustAcademy Official Website

1. Introduction to Database Normalization

Database normalization is the process of organizing data into well-structured tables to reduce unnecessary duplication and prevent data anomalies. It helps ensure that each fact is stored in an appropriate place and that relationships between tables are represented correctly.

In MySQL, normalization is applied when designing relational database tables. Instead of storing every piece of information in one large table, a normalized design separates related information into suitable tables and connects those tables using primary keys and foreign keys.

Second Normal Form (2NF) and Third Normal Form (3NF) are two important stages of normalization. Both build on First Normal Form (1NF), but they address different types of dependency problems.

• 1NF: Each column contains values from its intended domain without repeating groups or lists stored in a single cell.
• 2NF: The table is in 1NF and has no partial dependencies of non-prime attributes on a proper subset of any candidate key.
• 3NF: The table is in 2NF and satisfies the additional conditions that prevent inappropriate transitive dependencies of non-prime attributes on candidate keys.

Understanding these normal forms helps developers design databases that are easier to maintain, update, and query.

2. Learning Objectives

After completing this topic, you should be able to:

• Explain the purpose of database normalization.
• Understand the roles of 1NF, 2NF, and 3NF.
• Identify primary keys, candidate keys, and composite keys.
• Explain functional dependencies and partial dependencies.
• Identify transitive dependencies in database tables.
• Convert a table that violates 2NF into a normalized design.
• Convert a table that violates 3NF into separate related tables.
• Implement normalized tables using MySQL.
• Use joins to retrieve data from normalized tables.
• Recognize common normalization mistakes and test a database design.

3. Important Terms and Definitions

4. Understanding Functional Dependencies

Functional dependencies are central to understanding 2NF and 3NF. A functional dependency describes a rule about the data: if two rows have the same value for a determining attribute or attribute set, they must also have the same value for the dependent attribute.

Functional dependency notation is written as X -> Y, meaning that X functionally determines Y.

Example 1: Student ID Determines Student Name

student_id -> student_name

If each student ID identifies exactly one student, the student ID determines that student's name. The same student ID cannot correctly identify two different student names within the same valid snapshot of the data.

Example 2: Product ID Determines Product Name

product_id -> product_name, current_price

If each product ID identifies one product, it determines that product's descriptive name and current price according to the business rules.

Example 3: Composite Key Determines Quantity

Consider an order-item table in which each product appears at most once per order. The combination of order ID and product ID identifies a specific order line.

(order_id, product_id) -> quantity

The quantity depends on the specific order and product combination. The product ID alone cannot determine the quantity because the same product may be ordered in different quantities in different orders.

Why Functional Dependencies Matter

• They help identify appropriate candidate keys.
• They reveal when information is stored in the wrong table.
• They help detect partial dependencies that violate 2NF.
• They help detect transitive dependencies relevant to 3NF.
• They guide the decomposition of tables into better structures.

5. Quick Review of First Normal Form (1NF)

Before applying 2NF or 3NF, the table must satisfy First Normal Form.

A table in 1NF has columns containing individual values from their intended domains rather than lists of multiple values in one cell. It should not use repeating groups of columns for the same type of information.

Example of a Table That Violates 1NF

The phone_numbers column contains a list rather than one phone number per value.

Improved Design

STUDENTS
student_id (PK)
student_name

STUDENT_PHONES
student_id (FK)
phone_number

Each phone number is stored in a separate row in STUDENT_PHONES. This structure makes individual phone numbers easier to search, add, and remove.

6. What Is Second Normal Form (2NF)?

Second Normal Form (2NF) is a level of database normalization designed to eliminate partial dependencies. A relation is in 2NF when it is already in 1NF and every non-prime attribute is fully functionally dependent on every candidate key, rather than on only a proper subset of a candidate key.

In practical terms, when a table uses a composite candidate key, descriptive attributes should not depend on only one part of that key. They should describe the complete key's subject.

Conditions for 2NF

• The table must first satisfy 1NF.
• All relevant candidate keys must be identified.
• There must be no partial dependency of a non-prime attribute on a proper subset of a candidate key.
• Attributes should be stored with the entity or relationship they actually describe.

Important: 2NF is not simply a rule that every table must have a composite primary key. If a relation has no partial dependencies because its candidate keys consist of single attributes, it satisfies the 2NF condition once it is in 1NF. Candidate keys must still be considered accurately.

7. Understanding Partial Dependency

A partial dependency occurs when a non-prime attribute depends on only part of a composite candidate key instead of the entire key.

Suppose a table stores student course registrations and uses (student_id, course_id) as its composite primary key.

The composite key is (student_id, course_id). However:

• student_id -> student_name. The student's name depends only on student_id.
• course_id -> course_name. The course name depends only on course_id.
• (student_id, course_id) -> grade. The grade for a particular registration depends on the student-course combination.

The student_name and course_name attributes depend on only part of the composite key. Assuming these are non-prime attributes and the stated functional dependencies hold, the table violates 2NF.

8. Example: Converting a Table into 2NF

Step 1: Identify the Original Table

STUDENT_COURSE
-------------------------------------------------
student_id | course_id | student_name | course_name | grade

Assume that each student can register for several courses, and each course can have several students. The composite key is (student_id, course_id).

Step 2: Identify the Dependencies

student_id -> student_name

course_id -> course_name

(student_id, course_id) -> grade

Step 3: Identify the Problem

Student names are repeated for each course in which a student registers. Course names are repeated for each student taking a course. This creates unnecessary duplication and partial dependencies.

Step 4: Decompose the Table

Separate student information, course information, and registration information into three tables.

STUDENTS
-------------------------
student_id (PK)
student_name

COURSES
-------------------------
course_id (PK)
course_name

ENROLLMENTS
-------------------------
student_id (PK, FK)
course_id (PK, FK)
grade

Step 5: Why the New Design Satisfies 2NF

• Student names are stored with the student they describe.
• Course names are stored with the course they describe.
• The enrollment grade is stored with the student-course relationship.
• The enrollment table uses the composite key to identify each registration.
• There is no partial dependency of the non-prime attribute grade on only student_id or only course_id, under the stated business rules.

The new structure reduces duplicated descriptive information and makes updates more consistent.

9. What Is Third Normal Form (3NF)?

Third Normal Form (3NF) is a level of normalization that addresses certain transitive dependencies and other dependency patterns that can cause unnecessary duplication. A relation is in 3NF when it is in 2NF and, for every non-trivial functional dependency X -> A, either X is a superkey or A is a prime attribute.

For introductory database design, this is commonly explained as avoiding non-key attributes that depend indirectly on a candidate key through another non-key attribute. The formal definition is more precise and is important for cases involving multiple candidate keys.

Conditions for 3NF

• The table must satisfy 1NF.
• The table must satisfy 2NF.
• Functional dependencies must be examined to identify transitive dependencies and other violations of the formal 3NF rule.
• Non-key facts that belong to a different entity should be stored in an appropriate separate table.
• Relationships between the resulting tables should be represented with suitable keys.

10. Understanding Transitive Dependency

A transitive dependency occurs when a key determines an attribute indirectly through another attribute or attribute set. A common example is a student record that stores both a department ID and the department name, even though the department name is determined by the department ID.

student_id -> department_id

department_id -> department_name

Therefore:
student_id -> department_name (transitively)

If each student has one department and each department ID identifies one department name, the student's department name can be derived through department_id. Storing the department name repeatedly in every student row creates avoidable duplication.

Example of a Transitive Dependency

In this table, the department name is repeated for every student in the same department. If the department name changes, multiple rows may need to be updated. Forgetting to update one row could create inconsistent data.

11. Example: Converting a Table into 3NF

Step 1: Identify the Original Table

STUDENTS
--------------------------------------------------
student_id | student_name | department_id | department_name

Assume student_id is the primary key, each student belongs to one department, and department_id uniquely identifies a department.

Step 2: Identify the Dependencies

student_id -> student_name, department_id

department_id -> department_name

The department name depends on department_id rather than directly on the student identity. Because department_id is not a key of the original student table, this is a typical transitive dependency.

Step 3: Separate Department Information

STUDENTS
-------------------------
student_id (PK)
student_name
department_id (FK)

DEPARTMENTS
-------------------------
department_id (PK)
department_name

Step 4: Why the New Design Satisfies 3NF

• Each student's details are stored in STUDENTS.
• Each department's details are stored in DEPARTMENTS.
• The student table stores the department reference instead of repeating the department name.
• The department name is determined by the department's primary key.
• The foreign key connects each student to a valid department.

This design reduces duplication and makes department updates more reliable.

12. Implementing 2NF in MySQL

MySQL does not provide a command such as CONVERT TO 2NF. Normalization is achieved by designing suitable tables, selecting keys, moving attributes to the tables they describe, and creating relationships between those tables.

Step 1: Create the Database

CREATE DATABASE normalization_demo;
USE normalization_demo;

Step 2: Create the Students Table

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    student_name VARCHAR(100) NOT NULL
) ENGINE = InnoDB;

Step 3: Create the Courses Table

CREATE TABLE courses (
    course_id INT PRIMARY KEY,
    course_name VARCHAR(100) NOT NULL
) ENGINE = InnoDB;

Step 4: Create the Enrollments Table

CREATE TABLE enrollments (
    student_id INT NOT NULL,
    course_id INT NOT NULL,
    grade VARCHAR(2),
    PRIMARY KEY (student_id, course_id),
    CONSTRAINT fk_enrollments_students
        FOREIGN KEY (student_id)
        REFERENCES students(student_id),
    CONSTRAINT fk_enrollments_courses
        FOREIGN KEY (course_id)
        REFERENCES courses(course_id)
) ENGINE = InnoDB;

Step 5: Insert Sample Data

INSERT INTO students (student_id, student_name)
VALUES
(101, 'Rahul'),
(102, 'Priya'),
(103, 'Amit');

INSERT INTO courses (course_id, course_name)
VALUES
(201, 'MySQL'),
(202, 'Python');

INSERT INTO enrollments (student_id, course_id, grade)
VALUES
(101, 201, 'A'),
(101, 202, 'B'),
(102, 201, 'A'),
(103, 202, 'A');

Step 6: Retrieve the Combined Information

SELECT
    s.student_id,
    s.student_name,
    c.course_id,
    c.course_name,
    e.grade
FROM enrollments AS e
INNER JOIN students AS s
    ON e.student_id = s.student_id
INNER JOIN courses AS c
    ON e.course_id = c.course_id
ORDER BY s.student_id, c.course_id;

The query joins the normalized tables to present the combined information. Separating the data into multiple tables does not prevent you from retrieving it in one result set.

13. Implementing 3NF in MySQL

Now consider a student database in which department names are stored repeatedly in the students table. We can remove this transitive dependency by creating a separate departments table.

Step 1: Create the Departments Table

CREATE TABLE departments (
    department_id INT PRIMARY KEY,
    department_name VARCHAR(100) NOT NULL UNIQUE
) ENGINE = InnoDB;

Step 2: Create the Students Table with a Foreign Key

CREATE TABLE department_students (
    student_id INT PRIMARY KEY,
    student_name VARCHAR(100) NOT NULL,
    department_id INT NOT NULL,
    CONSTRAINT fk_students_department
        FOREIGN KEY (department_id)
        REFERENCES departments(department_id)
) ENGINE = InnoDB;

Step 3: Insert Department Records

INSERT INTO departments (department_id, department_name)
VALUES
(10, 'Computer Science'),
(20, 'Information Technology'),
(30, 'Finance');

Step 4: Insert Student Records

INSERT INTO department_students
    (student_id, student_name, department_id)
VALUES
(101, 'Rahul', 10),
(102, 'Priya', 10),
(103, 'Amit', 20),
(104, 'Neha', 30);

Step 5: Display Students with Department Names

SELECT
    s.student_id,
    s.student_name,
    d.department_id,
    d.department_name
FROM department_students AS s
INNER JOIN departments AS d
    ON s.department_id = d.department_id
ORDER BY s.student_id;

This query retrieves the department name through the foreign key relationship. The department name is stored once in the departments table rather than repeated in every student row.

Step 6: Update a Department Name

UPDATE departments
SET department_name = 'Computer Science and Engineering'
WHERE department_id = 10;

After this update, students associated with department 10 will see the updated name when the query is run again. There is no need to update every student record separately.

14. Understanding Data Anomalies

Data anomalies are problems that arise when information is stored redundantly or in an unsuitable table structure. Normalization helps prevent three common types of anomalies.

14.1 Insertion Anomaly

An insertion anomaly occurs when a fact cannot be recorded without also inserting unrelated information.

Example: In a combined student-department table, the database may make it difficult to register a new department before any students have joined it.

Solution: Store departments independently so a department can be inserted without creating a student record.

14.2 Update Anomaly

An update anomaly occurs when the same fact is stored in multiple rows and every copy must be updated consistently.

Example: If a department name appears in 200 student rows, changing the department name requires updating all 200 rows in the original design.

Solution: Store the department name once in DEPARTMENTS and reference it through department_id.

14.3 Deletion Anomaly

A deletion anomaly occurs when deleting one record unintentionally removes the only stored copy of another fact.

Example: If a table stores department information only alongside students, deleting the last student in a department could also remove the only record of that department.

Solution: Store departments in a separate table so deleting a student does not automatically remove the department's details.

15. Difference Between 1NF, 2NF, and 3NF

16. Difference Between 2NF and 3NF

17. Can a Table Be in 2NF but Not in 3NF?

Yes. A table can satisfy 2NF and still violate 3NF.

Consider the following table:

Assume employee_id is the only candidate key, and department_id determines department_name.

employee_id -> employee_name, department_id

department_id -> department_name

Because the candidate key consists of a single attribute, there cannot be a partial dependency on a proper subset of that key. The table therefore satisfies the 2NF condition, assuming it is in 1NF. However, department_name depends on department_id, which is not a superkey of the employee table, and department_name is assumed to be non-prime. This violates 3NF.

The solution is to separate EMPLOYEES and DEPARTMENTS, as demonstrated in the preceding MySQL example.

18. Can a Table Be in 3NF but Not in BCNF?

Yes. Third Normal Form is not always as restrictive as Boyce-Codd Normal Form (BCNF). A table can satisfy 3NF while violating BCNF when a determinant is not a superkey but the dependent attribute is prime.

For most introductory MySQL database design tasks, 2NF and 3NF are essential concepts. In more complex designs with overlapping candidate keys and several functional dependencies, BCNF may be considered to address additional redundancy.

19. Common Normalization Mistakes

20. Best Practices for 2NF and 3NF

• Understand the data and business rules before designing tables.
• Identify candidate keys rather than relying only on the chosen primary key.
• Write down functional dependencies before decomposing a table.
• Check whether each non-prime attribute depends on the whole candidate key.
• Separate descriptive facts about students, products, departments, and other entities from relationship-specific facts.
• Use primary keys to identify rows and foreign keys to maintain relationships.
• Choose suitable data types and use NOT NULL or UNIQUE constraints when the business rules require them.
• Test that decomposing a table does not lose information or create unintended duplicate rows when the original data is reconstructed.
• Use joins to retrieve combined information from normalized tables.
• Review indexes and query execution plans when optimizing performance.
• Document important dependencies so future changes preserve the intended data model.

21. Practical Exercise: Normalize an Order Database

Scenario: An online store stores order information in a single table. The table contains order ID, customer ID, customer name, product ID, product name, quantity, and unit price.

Original Table

ORDER_DETAILS
--------------------------------------------------------
order_id
customer_id
customer_name
product_id
product_name
quantity
unit_price

Assume an order can contain multiple products, and a product can appear in multiple orders. Each product appears at most once per order in this simplified model. The candidate key is (order_id, product_id).

Step 1: Identify Dependencies

customer_id -> customer_name

product_id -> product_name

(order_id, product_id) -> quantity, unit_price

If unit_price represents the actual price charged for a product in a particular order, it belongs with the order-product relationship rather than being determined solely by product_id.

Step 2: Design Normalized Tables

CUSTOMERS
-------------------------
customer_id (PK)
customer_name

ORDERS
-------------------------
order_id (PK)
customer_id (FK)

PRODUCTS
-------------------------
product_id (PK)
product_name

ORDER_ITEMS
-------------------------
order_id (PK, FK)
product_id (PK, FK)
quantity
unit_price

Step 3: Create the Tables in MySQL

CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    customer_name VARCHAR(100) NOT NULL
) ENGINE = InnoDB;

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT NOT NULL,
    CONSTRAINT fk_orders_customer
        FOREIGN KEY (customer_id)
        REFERENCES customers(customer_id)
) ENGINE = InnoDB;

CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(150) NOT NULL
) ENGINE = InnoDB;

CREATE TABLE order_items (
    order_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT NOT NULL,
    unit_price DECIMAL(10, 2) NOT NULL,
    PRIMARY KEY (order_id, product_id),
    CONSTRAINT fk_items_order
        FOREIGN KEY (order_id)
        REFERENCES orders(order_id),
    CONSTRAINT fk_items_product
        FOREIGN KEY (product_id)
        REFERENCES products(product_id)
) ENGINE = InnoDB;

Step 4: Retrieve the Combined Data

SELECT
    o.order_id,
    c.customer_name,
    p.product_name,
    oi.quantity,
    oi.unit_price,
    oi.quantity * oi.unit_price AS line_total
FROM orders AS o
INNER JOIN customers AS c
    ON o.customer_id = c.customer_id
INNER JOIN order_items AS oi
    ON o.order_id = oi.order_id
INNER JOIN products AS p
    ON oi.product_id = p.product_id
ORDER BY o.order_id, p.product_id;

Exercise Questions

1. Why is customer_name stored in CUSTOMERS instead of ORDER_ITEMS?
2. Why is product_name stored in PRODUCTS?
3. Why does ORDER_ITEMS use a composite key?
4. Which columns depend on the complete composite key?
5. How would you modify the design if a product could appear on multiple separate lines in the same order?
6. How would you calculate the total amount of each order?

22. Interview Questions and Answers

Q1. What is Second Normal Form?

Second Normal Form requires a relation to be in 1NF and to have no partial dependency of a non-prime attribute on a proper subset of any candidate key.

Q2. What is Third Normal Form?

Third Normal Form requires a relation to be in 2NF and to satisfy the formal condition that, for every non-trivial functional dependency, the determinant is a superkey or the dependent attribute is prime.

Q3. What is a partial dependency?

A partial dependency occurs when a non-prime attribute depends on only part of a composite candidate key rather than the complete key.

Q4. What is a transitive dependency?

A transitive dependency is an indirect dependency in which a key determines an attribute through another attribute or attribute set. For example, employee_id determines department_name through department_id.

Q5. What is the difference between 2NF and 3NF?

2NF focuses on eliminating partial dependencies of non-prime attributes on candidate keys. 3NF addresses transitive dependencies and other dependency patterns that violate its formal definition.

Q6. Does a table with a single-column primary key automatically satisfy 2NF?

Not necessarily in every possible schema. If the table is in 1NF and has no partial dependencies of non-prime attributes on any candidate key, it satisfies 2NF. Other candidate keys and their dependencies must also be considered.

Q7. Why is normalization important?

Normalization reduces unnecessary duplication, helps prevent insertion, update, and deletion anomalies, and makes data maintenance more reliable.

Q8. Can a table satisfy 2NF but violate 3NF?

Yes. A table with a single-attribute candidate key may have no partial dependencies but can still contain a transitive dependency that violates 3NF.

Q9. How are 2NF and 3NF implemented in MySQL?

They are implemented through good schema design: decomposing tables according to dependencies, defining primary and foreign keys, and adding suitable constraints. MySQL does not automatically normalize tables with a dedicated command.

Q10. Does normalization always improve performance?

Not necessarily. Normalization generally improves consistency and maintainability, but retrieving combined information may require additional joins. Performance should be measured and optimized based on actual queries.

Q11. What is an update anomaly?

An update anomaly occurs when the same fact is stored in multiple places and changes are not applied consistently to every copy.

Q12. What is a composite key?

A composite key consists of two or more attributes that together identify a row. For example, student_id and course_id can form the primary key of an enrollment table.

Q13. What is the purpose of a foreign key after normalization?

A foreign key links related tables and can enforce referential integrity, ensuring that referenced parent records exist according to the defined constraint.

Q14. What is the difference between 3NF and BCNF?

BCNF is stricter than 3NF. In BCNF, every determinant of a non-trivial functional dependency must be a superkey. Some relations satisfy 3NF but violate BCNF.

23. Quick Revision Checklist

• Normalization organizes relational data and reduces avoidable duplication.
• 1NF addresses repeating groups and multiple values stored in a single cell.
• 2NF requires 1NF and eliminates partial dependencies of non-prime attributes on proper subsets of candidate keys.
• 3NF requires 2NF and satisfies its formal functional-dependency condition.
• A functional dependency is written as X -> Y.
• A composite key contains two or more attributes.
• A partial dependency involves only part of a composite candidate key.
• A transitive dependency is an indirect dependency through another attribute or attribute set.
• Separate student and course information from enrollment information when their dependencies require it.
• Separate department details from employee or student records when department_id determines department_name.
• Use primary keys, foreign keys, and suitable constraints to implement the normalized design.
• Use JOIN operations to retrieve related information.
• Validate the design against the actual business rules and candidate keys.

24. Conclusion

Second Normal Form (2NF) and Third Normal Form (3NF) are important principles in relational database design. 2NF focuses on removing partial dependencies of non-prime attributes on candidate keys, while 3NF addresses transitive dependencies and other functional-dependency patterns that do not satisfy its formal condition.

By identifying functional dependencies, selecting appropriate keys, and separating information into related tables, developers can reduce data duplication and avoid common insertion, update, and deletion anomalies. MySQL implements these designs through table definitions, primary keys, foreign keys, constraints, and joins.

Practice the student-course and order-product examples to understand how normalization works in real database projects. Always confirm the business rules and candidate keys before deciding that a table satisfies 2NF or 3NF.

25. Additional Learning Resources

• Explore the MySQL Training Course at JustAcademy
• Register for a MySQL Training Demo
• Visit the JustAcademy Website
• MySQL 8.4 Reference Manual
• MySQL Foreign Key Constraints Documentation
• MySQL Documentation: Functional Dependencies