Example Of 1nf 2nf And 3nf

5 min read

Introduction

Database normalization is a systematic approach to organizing data in relational databases. It reduces redundancy and improves data integrity by breaking down tables into smaller, related structures. The most commonly taught levels are First Normal Form (1NF), Second Normal Form (2NF), and Third Normal Form (3NF). Understanding concrete examples of each normal form helps students and practitioners visualize the transformation process and appreciate why each step matters. This article walks through step‑by‑step examples, explains the underlying principles, and answers common questions to give you a thorough grasp of 1NF, 2NF, and 3NF Easy to understand, harder to ignore..

Understanding First Normal Form (1NF)

First Normal Form requires that every column in a table holds atomic (indivisible) values and that each record is unique. In practice, this means eliminating repeating groups, ensuring a single value per cell, and removing duplicate rows The details matter here..

Key Characteristics of 1NF

  • Atomic values: Each field contains a single piece of data.
  • Unique rows: No two rows are identical.
  • Ordered structure: Columns have a defined sequence.

Example: A Table Before 1NF

Consider a simple student enrollment table that stores multiple courses per student as a comma‑separated list:

StudentID Name Courses
101 Alice Math,Physics,Chemistry
102 Bob Physics,Chemistry
103 Carol Math,Physics

This table violates 1NF because the Courses column contains multiple values, which are not atomic. It also risks data anomalies if a student drops a course.

Converting to 1NF

To achieve 1NF, we split the table into two:

Student Table (1NF)

StudentID Name
101 Alice
102 Bob
103 Carol

Enrollment Table (1NF)

StudentID Course
101 Math
101 Physics
101 Chemistry
102 Physics
102 Chemistry
103 Math
103 Physics

Now each column holds a single value, and rows are unique. This structure is ready for higher normal forms.

Understanding Second Normal Form (2NF)

Second Normal Form builds on 1NF by eliminating partial dependencies. A table is in 2NF when it is already in 1NF and every non‑key attribute depends on the entire primary key, not just part of it.

Key Characteristics of 2NF

  • Fully functional dependencies: All non‑key attributes are dependent on the whole primary key.
  • No partial dependencies: No attribute depends on only a portion of a composite key.

Example: A Table Before 2NF

Suppose we extend the enrollment scenario to include course details such as credit hours and department. A naive design might combine student and course information:

StudentID Name Course Dept Credits
101 Alice Math CS 3
101 Alice Physics PHYS 4
102 Bob Physics PHYS 4
102 Bob Chemistry CHEM 3

If we choose (StudentID, Course) as the composite primary key, the attributes Name, Dept, and Credits depend only on Course (or only on StudentID), creating partial dependencies. To give you an idea, Name is tied to StudentID but not to the whole key Easy to understand, harder to ignore..

Converting to 2NF

We separate the data into two tables:

Student Table (2NF)

StudentID Name
101 Alice
102 Bob
103 Carol

Course Table (2NF)

CourseID Course Dept Credits
1 Math CS 3
2 Physics PHYS 4
3 Chemistry CHEM 3

Enrollment Table (2NF)

StudentID CourseID EnrollmentDate
101 1 2023‑01‑10
101 2 2023‑01‑15
102 2 2023‑02‑01
102 3 2023‑02‑05

Now each non‑key attribute fully depends on its primary key, eliminating partial dependencies.

Understanding Third Normal Form (3NF)

Third Normal Form removes transitive dependencies. A table is in 3NF when it is already in 2NF and no non‑key attribute depends on another non‑key attribute.

Key Characteristics of 3NF

  • No transitive dependencies: Non‑key attributes must not be determined by other non‑key attributes.
  • Direct dependency: All attributes depend only on the primary key.

Example: A Table Before 3NF

Consider adding a Department Manager attribute to the Course table because each department has a manager. If we store this directly in the Course table:

CourseID Course Dept Credits Manager
1 Math CS 3 Alice
2 Physics PHYS 4 Bob
3 Chemistry CHEM 3 Carol

Here, Manager depends on Dept, which is a non‑key attribute. This creates a transitive dependency: CourseID → Dept → Manager. The table is not in 3NF Still holds up..

Converting to 3NF

We split the table to isolate the manager information:

Department Table (3NF)

DeptID Dept Manager
CS Computer Science Alice
PHYS Physics Bob
CHEM Chemistry Carol

Course Table (3NF)

CourseID Course Dept Credits
1 Math CS 3

| 2 | Physics | PHYS | 4 | | 3 | Chemistry| CHEM | 3 |

Now Dept remains in the Course table, but Manager no longer appears there. The transitive dependency has been removed because Manager depends only on Dept, and Dept is now the primary key of its own table.

To retrieve the original combined view, a simple join reunites the data without duplication:

SELECT c.CourseID, c.Course, c.Dept, d.Manager, c.Credits
FROM Course c
JOIN Department d ON c.Dept = d.Dept;

Why 3

What's New

Just Shared

For You

Good Reads Nearby

Thank you for reading about Example Of 1nf 2nf And 3nf. We hope the information has been useful. Feel free to contact us if you have any questions. See you next time — don't forget to bookmark!
⌂ Back to Home