Normalization in DBMS: 1NF, 2NF, 3NF and BCNF Explained

Normalization is the design discipline of storing each fact exactly once. A table that mixes facts about several things (students, departments, courses) repeats some facts on many rows, and repetition invites contradictions. Normalization splits such a table into smaller ones, using the functional dependencies among its columns, until every dependency is "a key determines a fact about that key". This note takes one table through 1NF, 2NF, 3NF and BCNF, checks that every split is lossless, and ends with 4NF and when to denormalize.

The running example and its anomalies

A college keeps its enrolment sheet like this, one row per student with a list of courses:

The rules of the college, as functional dependencies:

Flattened to one row per student and course, the sheet becomes one wide table, R:

Candidate keys. roll_no appears on no right-hand side, so it is in every key, but its closure is only {roll_no, name, dept, hod}. Adding course_id gives everything (FD4, FD3); adding instructor also gives everything (FD5 brings course_id). name, dept, hod, course_title and grade appear only on right-hand sides, so they are in no key. The candidate keys are {roll_no, course_id} and {roll_no, instructor}; the prime attributes are roll_no, course_id and instructor.

The repetition causes three anomalies, each visible on these four rows:

1. Update anomaly: the fact "Dr. Rao heads CSE" is stored on 3 rows, one per CSE enrolment, so a new head means changing all 3 of them.
2. Miss one of them and the table contradicts itself: CSE now has two heads, Dr. Das and Dr. Rao, and nothing says which is right. dept → hod is broken by the table's own rows.
3. Insertion anomaly: a new course, CS303, cannot be recorded until someone enrols. Its row would need roll_no, which is part of every candidate key and so cannot be NULL.
4. Deletion anomaly: when Meera leaves and her row is deleted, the only record that Dr. Iyer heads ECE goes with it. One delete removed two facts.

The normal forms at a glance

Each form includes the ones above it: every BCNF relation is in 3NF, every 3NF relation in 2NF.

First normal form (1NF)

A relation is in 1NF when every attribute holds a single, indivisible value: no lists, no repeating groups such as course1, course2, course3, and no nested tables. The sheet above breaks 1NF, because one cell holds several courses. Flattening it to one row per (student, course) gives R, which is in 1NF. Atomicity is judged by how the data is used: a full name stored in one column is fine if you never query its parts.

Second normal form (2NF)

A relation is in 2NF when it is in 1NF and no non-prime attribute is partially dependent on a candidate key, that is, dependent on a proper subset of it. Only composite keys can cause this, so a 1NF table whose candidate keys are all single attributes is automatically in 2NF.

R has three partial dependencies: roll_no → name, dept, hod (roll_no is part of both keys), course_id → course_title (part of {roll_no, course_id}), and instructor → course_title through FD5 (part of {roll_no, instructor}). Move each group into a table keyed by its determinant:

Student keeps three rows and Course two, so CS303 can now be added before anyone enrols; Enrollment keeps all four. In Enrollment the only non-prime attribute is grade, and it depends on whole keys, so all three tables are in 2NF.

Third normal form (3NF)

A relation is in 3NF when, for every non-trivial dependency X → A, either X is a super key or A is a prime attribute. Equivalently: it is in 2NF and no non-prime attribute is transitively dependent on a key through another non-key attribute.

Student breaks 3NF: roll_no → dept and dept → hod, where dept is not a super key and hod is not prime. That is why the CSE head was repeated. Split on dept → hod:

Enrollment is already in 3NF. Its one dependency without a super-key determinant is instructor → course_id, and course_id is prime (it belongs to the key {roll_no, course_id}), which 3NF allows. The 3NF design is Student, Department, Course and Enrollment.

Boyce–Codd normal form (BCNF)

A relation is in BCNF when, for every non-trivial dependency X → A, X is a super key. No exception for prime attributes.

Enrollment breaks BCNF: instructor → course_id, and instructor alone is not a super key. The redundancy is visible: "Sen teaches CS301" is stored on two rows. Split on the offending dependency, X → A becomes one table (X, A), and the original loses A:

The BCNF design has five tables: Student, Department, Course, Teaches and Enrollment. Every determinant in every table is now a key. The whole decomposition, replayed with the dependency that forced each split:

1. R is in 1NF: one row per student and course. Its candidate keys are {roll_no, course_id} and {roll_no, instructor}, so roll_no, course_id and instructor are prime and every other column is not. Bold underlined columns are a key.
2. 2NF: roll_no → name, dept, hod breaks it, a partial dependency (roll_no is only part of a key). Split Student off R; the common column roll_no is Student's key, so the join back is lossless.
3. 2NF: course_id → course_title breaks it, a partial dependency (course_id is only part of a key). Split Course off rest of R; the common column course_id is Course's key, so the join back is lossless.
4. 3NF: dept → hod breaks it, a transitive dependency (dept is not a key and hod is not prime). Split Department off Student; the common column dept is Department's key, so the join back is lossless. The head of CSE, stored 3 times in R, is now stored once.
5. BCNF: instructor → course_id breaks it, a determinant that is not a super key (instructor is not a super key of Enrollment). Split Teaches off Enrollment; the common column instructor is Teaches's key, so the join back is lossless. Sen teaching CS301 is now one row, not two.
6. Every determinant is now a key: five tables in BCNF. The price is FD4: (roll_no, course_id) → instructor, grade spans Teaches and Enrollment, so no single table can check it any more.

A relation can be in 3NF but not BCNF only if it has two or more candidate keys that overlap, as {roll_no, course_id} and {roll_no, instructor} do here. That is the quick answer when an interviewer asks why 3NF is "usually enough".

Lossless join and dependency preservation

Every decomposition must be lossless: joining the pieces back must give exactly the original rows. For a split of R into R1 and R2 the test is that the common attributes R1 ∩ R2 are a super key of R1 or of R2.

All four splits above pass it: each time the common column (roll_no, course_id, dept, then instructor) is the key of the table split off, as the decomposition's last line shows at every step.

A lossy split, for contrast: break the enrolment facts (roll_no, course_id, grade) into (roll_no, grade) and (course_id, grade). The common attribute grade determines nothing, and the join back on grade returns six rows instead of four:

1. Split the 4 enrolment facts into (roll_no, grade) and (course_id, grade). The only common column is grade, and grade determines nothing: A and B each appear on several rows of both pieces.
2. Joining the pieces back on grade pairs every roll number with every course of the same grade: 6 rows instead of 4. (101, CS301, B) and (103, CS302, B) never happened, and nothing marks them as false.

A lossy join adds rows rather than dropping them; the information lost is which rows are true.

A decomposition is dependency preserving if every original dependency can be checked inside a single table, without joins. The 2NF and 3NF steps preserve all five dependencies. The BCNF step does not: FD4, (roll_no, course_id) → instructor, grade, now spans Teaches and Enrollment, so an insert can break it unnoticed:

1. In BCNF, Teaches says which course each instructor teaches and Enrollment which instructor's section each student is in. FD4, a student takes a course once, is in neither table.
2. INSERT (101, Gupta, C) into Enrollment. Its key (roll_no, instructor) is new, so the table accepts it, and Teaches is not involved at all.
3. Only a join shows the damage: Gupta teaches CS301, so student 101 is now in CS301 twice, with grades A and C. FD4 is broken, and enforcing it needs that join or a trigger on every insert.

This trade-off is a theorem, not a flaw of this example: a lossless BCNF decomposition always exists but may not preserve dependencies, while 3NF synthesis always gives one that is both lossless and dependency preserving. Many designers stop at 3NF when they meet it. A practical middle way is to keep the 3NF Enrollment(roll_no, course_id, instructor, grade) and add a composite foreign key (instructor, course_id) referencing Teaches, so both rules are enforced by constraints.

Fourth normal form, briefly

A multivalued dependency X ↠ Y says that the set of Y values for an X value is independent of the other attributes. Suppose a separate table lists each course's recommended textbooks and its lab days, which have nothing to do with each other:

The only key is all three columns and there are no non-trivial functional dependencies, so the table is in BCNF. Yet adding a third textbook needs two new rows, one per lab day. course_id ↠ textbook and course_id ↠ lab_day hold with course_id not a super key, so it breaks 4NF. Split it into (course_id, textbook) and (course_id, lab_day), which is lossless. Fifth normal form deals with join dependencies that are not implied by keys, and rarely comes up in practice.

When to denormalize

Normalization optimizes for correct writes; every split adds a join to some read. Denormalization deliberately reintroduces redundancy where reads dominate:

• Reporting and analytics, where data warehouses use star schemas with wide, repeated dimension columns.
• Derived values read constantly: an order's total, a post's like count, a user's solved count.
• Hot pages where a join on every request costs more than keeping a copy in sync.

The price is the anomalies you removed: the copy must be updated in the same transaction, by a trigger, or by a job that tolerates brief staleness. Normalize first, measure, then denormalize the specific paths that need it.

Common mistakes

• Defining 2NF with the primary key only: partial dependencies on any candidate key count.
• Calling Enrollment "not in 3NF" because of instructor → course_id: course_id is prime, so 3NF allows it; only BCNF forbids it.
• Splitting on a dependency without checking the join is lossless: the common columns must be a key of one side.
• Assuming BCNF is always the goal: it can cost dependency preservation.
• Thinking a lossy join loses rows: it produces extra, spurious rows.
• Normalizing "for performance": normalization improves integrity; it often makes reads slower.

Interview questions

Why do we normalize a database? To remove redundancy and the insertion, update and deletion anomalies it causes. Each fact is stored once, so it can be changed in one place and cannot contradict itself.

Explain 1NF, 2NF and 3NF with an example. 1NF: atomic values, one course per row. 2NF: no non-key attribute depends on part of a composite key, so course_title moves to Course. 3NF: no non-key attribute depends on another non-key attribute, so hod moves from Student to Department.

What is the difference between 3NF and BCNF? Give a relation in 3NF but not BCNF. BCNF requires every determinant to be a super key; 3NF also allows a dependency whose right side is prime. Enrollment(roll_no, course_id, instructor, grade) with instructor → course_id is in 3NF but not BCNF.

How do you test whether a decomposition is lossless? For two pieces, check that their common attributes functionally determine all of one piece, that is, form a super key of R1 or of R2. If neither holds, the natural join can create spurious rows.

What is dependency preservation and why does it matter? Every original dependency can be checked within one decomposed table. If one is lost, enforcing it needs a join on every insert or update, which is expensive, so the rule usually goes unenforced.

Is a relation with only two attributes always in BCNF? Yes. Any non-trivial dependency between two attributes A → B makes A a super key, because A then determines both attributes.

What is a multivalued dependency? Which normal form handles it? X ↠ Y means the set of Y values for each X is independent of the remaining attributes, as with a course's textbooks and its lab days. 4NF requires the determinant of every non-trivial multivalued dependency to be a super key.

When would you not normalize fully? When read performance on a measured hot path needs fewer joins, in data warehouses, or when BCNF would lose a dependency you must enforce. The redundancy is then maintained deliberately.

Next, write the tables you designed in SQL: SQL Basics: DDL, DML, DCL and TCL. Normal forms are examined in the SQL (Intermediate) skill test.