DBMS Normalization Explained: 1NF, 2NF, 3NF and BCNF with Examples
Functional dependencies, the anomalies normalization removes, and how to normalise a table step by step.
Coding problems with instant test-case feedback
Write Python, C++, C or JavaScript in your browser and see which hidden tests pass.
Normalization is the process of splitting a table into smaller, well-structured tables so that every fact is stored exactly once. It is one of the most asked DBMS topics in campus interviews and written tests because it checks whether you understand keys, dependencies and data integrity rather than just SQL syntax.
1. Why normalise? The three anomalies#
Consider one table that stores student enrolments: (StudentID, StudentName, CourseID, CourseName, Instructor, InstructorPhone). Each row says "this student takes this course". Because course and instructor details repeat on every enrolment row, the table suffers from three problems.
- Update anomaly: if an instructor changes phone number, every row for every student in that course must be updated. Miss one and the data contradicts itself.
- Insert anomaly: you cannot record a new course until at least one student enrols, because StudentID is part of the key.
- Delete anomaly: if the last student drops a course, deleting that row also deletes the only record of the course and its instructor.
Say it in one line first: "Organising tables so each fact is stored once, which removes update, insert and delete anomalies." Then give the anomaly example. Interviewers care more about the why than reciting definitions.
2. Functional dependencies and keys#
A functional dependency X → Y means: if two rows agree on X, they must agree on Y. In the enrolment table, CourseID → CourseName and CourseID → Instructor, because a course ID always identifies the same course and instructor. Instructor → InstructorPhone holds as well.
A candidate key is a minimal set of columns that determines every other column. Here the candidate key is (StudentID, CourseID). Columns that belong to some candidate key are prime attributes; the rest are non-prime.
- Partial dependency: a non-prime column depends on only part of a composite key (CourseName depends on CourseID alone).
- Transitive dependency: a non-prime column depends on another non-prime column (InstructorPhone depends on Instructor, which depends on CourseID).
Practise placement aptitude on the real test pattern
TCS NQT, Infosys, Wipro, Accenture and Cognizant style mocks with section-wise analysis.
3. The normal forms, one step at a time#
Each normal form adds one rule on top of the previous one. A table in 3NF is automatically in 2NF and 1NF.
| Normal form | Rule | What it removes |
|---|---|---|
| 1NF | Every column holds a single atomic value; no repeating groups or lists in a cell | Multi-valued cells like "DBMS, OS, CN" |
| 2NF | 1NF, and no non-prime column depends on part of a composite key | Partial dependencies |
| 3NF | 2NF, and no non-prime column depends on another non-prime column | Transitive dependencies |
| BCNF | For every dependency X → Y, X is a super key | Remaining anomalies where a non-key column determines part of a key |
4. Worked example: from one table to BCNF#
2NF: CourseName and Instructor depend only on CourseID, so move them out. We get Enrolment(StudentID, CourseID), Student(StudentID, StudentName) and Course(CourseID, CourseName, Instructor, InstructorPhone).
3NF: In Course, InstructorPhone depends on Instructor, which is not a key. Move it: Course(CourseID, CourseName, Instructor) and Instructor(Instructor, InstructorPhone).
BCNF: every remaining dependency now has a key on its left side, so the design is in BCNF. Each fact (a student name, a course name, a phone number) is stored once.
| 1 | -- Final BCNF schema |
| 2 | CREATE TABLE student ( |
| 3 | student_id INT PRIMARY KEY, |
| 4 | student_name VARCHAR(100) NOT NULL |
| 5 | ); |
| 6 | |
| 7 | CREATE TABLE instructor ( |
| 8 | instructor_name VARCHAR(100) PRIMARY KEY, |
| 9 | instructor_phone VARCHAR(15) |
| 10 | ); |
| 11 | |
| 12 | CREATE TABLE course ( |
| 13 | course_id VARCHAR(10) PRIMARY KEY, |
| 14 | course_name VARCHAR(100) NOT NULL, |
| 15 | instructor_name VARCHAR(100) REFERENCES instructor(instructor_name) |
| 16 | ); |
| 17 | |
| 18 | CREATE TABLE enrolment ( |
| 19 | student_id INT REFERENCES student(student_id), |
| 20 | course_id VARCHAR(10) REFERENCES course(course_id), |
| 21 | PRIMARY KEY (student_id, course_id) |
| 22 | ); |
5. When not to normalise#
Normalised schemas need joins to answer questions, and joins cost time on very large tables. Reporting databases and read-heavy systems often denormalise on purpose, copying some columns to avoid joins. That is a deliberate trade of storage and update effort for read speed, not a design mistake.
"Is 3NF always enough?" Most production schemas aim for 3NF. BCNF is stricter; in rare cases reaching BCNF loses a dependency you wanted the database to enforce, so teams stop at 3NF.
Test Your Concept Mastery
Verify your retention on: DBMS Normalization Explained: 1NF, 2NF, 3NF and BCNF with Examples
A table has key (OrderID, ProductID) and a column ProductPrice that depends only on ProductID. Which normal form does it violate?
Which anomaly stops you from adding a new course before any student enrols?
Ready for a full proctored assessment?
Take a free aptitude mock on a real company pattern, with a worked solution for every question.
