DBMS Normalisation Made Simple: 1NF, 2NF, 3NF and BCNF with One Worked Example

Normalisation is the process of organising tables to reduce redundancy and avoid anomalies (problems when inserting, updating or deleting data). Exams love it because it tests whether you really understand keys and dependencies. We will use one table throughout.

The problem table#

Problems:

• Update anomaly: if Mr. Sinha's phone changes, we must change many rows.
• Insertion anomaly: we can't add a new course until a student enrols in it.
• Deletion anomaly: if Aman leaves and was the only student in a course, we lose the course details.

Key concepts#

• Functional dependency (FD): X → Y means the value of X decides the value of Y. RollNo → Name.
• Candidate key: a minimal set of attributes that identifies a row. Here: {RollNo, CourseID}.
• Prime attribute: part of any candidate key (RollNo, CourseID).
• Partial dependency: a non-prime attribute depends on part of a composite key.
• Transitive dependency: A → B and B → C, so A → C through B.

FDs in our table:

• RollNo → Name
• CourseID → CourseName, Teacher
• Teacher → TeacherPhone
• {RollNo, CourseID} → Marks

1NF: atomic values#

A table is in First Normal Form if every cell holds a single value (no lists like "C1, C2" in one cell) and there are no repeating groups. Our table already stores one course per row, so it is in 1NF.

2NF: no partial dependencies#

2NF = 1NF + every non-prime attribute depends on the whole candidate key.

Name depends only on RollNo, and CourseName/Teacher only on CourseID: partial dependencies. Split:

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

3NF: no transitive dependencies#

3NF = 2NF + no non-prime attribute depends on another non-prime attribute.

In Course, CourseID → Teacher → TeacherPhone is transitive. Split again:

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

Final design: Student, Course, Teacher, Enrolment. Each fact is stored once.

BCNF: the stricter version#

BCNF: for every FD X → A, X must be a superkey. The difference from 3NF: 3NF allows X → A when A is prime; BCNF doesn't.

Classic example: Table(Student, Subject, Teacher) with FDs {Student, Subject} → Teacher and Teacher → Subject (each teacher teaches one subject). It is in 3NF (Subject is prime) but not BCNF, since Teacher is not a superkey. Decompose into (Teacher, Subject) and (Student, Teacher).

Summary table#

Lossless and dependency-preserving#

A good decomposition should be:

• Lossless join: joining the parts gives back exactly the original data. Test for two parts R1, R2: their common attributes must be a key of R1 or R2.
• Dependency preserving: every original FD can be checked inside one of the parts.

BCNF decomposition is always lossless but may not preserve dependencies; 3NF decomposition can always be both.

Practice questions#

1. R(A, B, C, D) with key {A, B} and FDs AB → C, B → D. Highest normal form?
2. R(A, B, C) with key A and FDs A → B, B → C. Highest normal form?
3. Is every BCNF relation also in 3NF?

Answers: 1) 1NF (B → D is a partial dependency). 2) 2NF (A → B → C is transitive). 3) Yes, BCNF is stricter than 3NF.