Normalisation in DBMS: 1NF, 2NF and 3NF Explained

Introduction

A database becomes difficult to maintain when the same information is stored repeatedly. Updating a student’s name might require changing several rows. Deleting an enrolment might accidentally remove the only stored details about a course.

Normalisation helps prevent these problems by organising data into related tables according to what each piece of information depends on.

The first three normal forms 1NF, 2NF, and 3NF provide a practical foundation for designing consistent, manageable relational databases.

What Is Normalisation?

Normalisation is the process of organising database tables to reduce unnecessary duplication and prevent problems when inserting, updating, or deleting data.

The central idea is to store each fact in the table where it belongs and connect related records using keys.

For example, a student’s name belongs in a students table. An enrolments table can refer to that student through student_id instead of storing the name again.

This does not mean that every repeated value is wrong. A student_id can legitimately appear in many enrolment records because it connects those records to the same student.

Why Normalisation Helps

Consider a table that stores student details, course details, instructor details, and grades together. Because students can take several courses, some information will appear in multiple rows.

This can create three types of anomalies:

Separating students, courses, and enrolments allows each kind of information to be maintained independently.

Foreign keys help preserve relationships between these tables. However, choosing whether related rows should be deleted automatically is a separate decision from normalisation.

First Normal Form (1NF)

A table is in first normal form when each cell contains a single value from its intended domain and the design avoids repeating groups.

Suppose a student’s courses are stored like this:

The course_ids cell contains multiple course identifiers. Queries must interpret the list instead of working directly with individual enrolment records.

Adding columns such as course1, course2, and course3 creates another problem: the table structure limits how many courses can be recorded.

A better design uses one row for each student-course enrolment.

For the example throughout this article, assume:

• A student can enrol in a particular course only once.

• Each course has one instructor, and an instructor can teach several courses.

• Names are not necessarily unique; identifiers distinguish students, courses, and instructors.

The following enrolment_details table satisfies 1NF:

Each cell contains one value, and each row represents one enrolment. The combination of student_id and course_id uniquely identifies a row However, student names and course details are still repeated. A table can satisfy 1NF and still contain unnecessary redundancy.

Atomicity depends on what a column represents. A full name can be one value in a student_name column; spaces do not automatically make it a list.

Second Normal Form (2NF)

Second normal form addresses partial dependencies.

Before applying it, two terms are useful:

• A candidate key is a minimal set of columns that uniquely identifies a row. One candidate key is chosen as the primary key.

• A non-key attribute is a column that does not belong to any candidate key.

A table is in 2NF when it is already in 1NF and every non-key attribute depends on the whole of every candidate key, rather than only part of one.

In our example, the candidate key is the pair (student_id, course_id). The dependencies follow these business rules:

The student’s name depends only on student_id. The course details depend only on course_id. These columns depend on part of the composite key, creating partial dependencies.

The grade belongs to the complete enrolment: we need both the student and the course to identify which grade is being recorded.

To reach 2NF, separate the information:

Now a student’s name is stored once in students, and course details are stored independently in courses. The enrolments table records which student takes which course and the associated grade.

2NF considers all candidate keys. Choosing a single-column primary key does not automatically remove partial dependencies involving another candidate key. If all candidate keys are single-column, a table already in 1NF satisfies 2NF automatically.

Third Normal Form (3NF)

Third normal form builds on 2NF by addressing transitive dependencies.

For the simple dependencies in this example, that means removing a non-key attribute that depends on the key through another non-key attribute.

Look at the courses table:

The dependencies are:

course_id → instructor_id → instructor_name

Here, an arrow means “determines.” A course identifies its instructor, and that instructor’s identifier determines the instructor’s name.

The instructor’s name is a fact about the instructor, so storing it in every course row creates repetition. If Nisha’s name changes, several course records may need updating.

Move instructor details into a separate table.

Courses

Instructors

The students and enrolments tables remain as they were after the 2NF step.

The final design contains four tables:

The student_id and course_id columns in enrolments reference students and courses. The instructor_id column in courses references instructors.

A student, instructor, or course can now be recorded independently. Deleting an enrolment does not require deleting the associated course details.

Comparing the First Three Normal Forms

Each stage builds on the previous one. Splitting a table arbitrarily does not establish a normal form, the dependencies determine which separation is appropriate.

Does Normalisation Make Queries Faster?

Normalisation primarily improves data consistency and maintainability. It does not guarantee faster queries.

Separating information can reduce repeated data, but retrieving related information may require joins. Query performance also depends on indexes, data volume, and the workload.

The goal is to organise facts correctly. Performance decisions should follow the application’s actual needs.

Summary

Normalisation organises related data to reduce unnecessary duplication and prevent insertion, update, and deletion anomalies. 1NF removes lists and repeating groups. 2NF removes partial dependencies of non-key attributes on candidate keys. 3NF separates instructor details to remove a transitive dependency.