Define Normalization. Explain 1NF, 2NF and 3NF with a suitable example.

Normalization is the process of organising the tables of a relational database to reduce redundancy (repeated data) and remove update, insert and delete anomalies. It works by splitting a table into smaller tables based on functional dependencies, without losing information.

Example table: StudentCourse(RollNo, Name, CourseID, CourseName, Teacher, TeacherPhone)

First Normal Form (1NF)

A table is in 1NF if every attribute holds atomic (single) values and there are no repeating groups.

The row above has two courses in one cell, so it is not in 1NF. Make one row per student–course pair:

The key is now (RollNo, CourseID).

Second Normal Form (2NF)

A table is in 2NF if it is in 1NF and has no partial dependency: every non-key attribute depends on the whole primary key, not just part of it.

Here Name depends only on RollNo, and CourseName, Teacher, TeacherPhone depend only on CourseID. Split the table:

• Student(RollNo, Name)
• Course(CourseID, CourseName, Teacher, TeacherPhone)
• Enroll(RollNo, CourseID)

Third Normal Form (3NF)

A table is in 3NF if it is in 2NF and has no transitive dependency: a non-key attribute must not depend on another non-key attribute.

In Course, CourseID → Teacher and Teacher → TeacherPhone, so TeacherPhone depends on the key only through Teacher. Split again:

• Course(CourseID, CourseName, Teacher)
• Teacher(Teacher, TeacherPhone)

Now every table is in 3NF. A teacher's phone is stored once, so changing it updates one row, and deleting a course does not lose the teacher's details.