Normalization in DBMS: Master 1NF to BCNF with SQL Examples

Say a college office in Pune keeps one spreadsheet for every enrolment. Student ID, course, instructor, department and fees all sit in one table. Then one instructor moves from CSE to IT. Someone must edit forty rows by hand, one at a time, late on a Friday afternoon. They miss one. Half the sheet says CSE, half says IT, and nobody knows which is true. That single sheet is the whole reason normal forms exist.

Normalization in DBMS means splitting one messy table into smaller linked tables so each fact is stored once. Each normal form removes one kind of repeated data. 1NF fixes multi-value cells, 2NF fixes partial dependency, 3NF fixes transitive dependency, and BCNF fixes the case where a determinant that is not a superkey still decides another column, even when 3NF passes.

Start with the unnormalized enrolment table

One flat table holding student, course, instructor, department and fees lets the same fact be typed many times, and that causes the anomalies freeCodeCamp's normalization guide describes as the reason to normalize at all. Here is our college's sheet, with the fees column left out for space:

Look at Riya's row first. One cell holds two courses, so you cannot search or count them cleanly. The sheet also suffers from three anomalies, and each one is easy to see in a single row:

Insert anomaly

The college launches a Cloud course. Nobody has enrolled yet, so there is no student row to put it in.

Update anomaly

Sen moves to IT. You change Arjun's row but forget Riya's, and the data now disagrees with itself.

Delete anomaly

Emily drops Python. She was its only student, so deleting her row also deletes the fact that Miller teaches Python.

In SQL, the sheet looks like this:

CREATE TABLE enrolment_raw (
  student_id   VARCHAR(10),
  student_name VARCHAR(50),
  courses      VARCHAR(100),  -- 'DBMS, Java'
  instructors  VARCHAR(100),  -- 'Sen, Rao'
  department   VARCHAR(50),
  fees         DECIMAL(10,2)
);

Make every cell atomic for 1NF

1NF means one value per cell, a primary key, and no repeating columns, so a student with two courses becomes two rows, as the GeeksforGeeks page on normal forms lays out. "Atomic" just means the cell cannot be broken into smaller useful parts.

Riya now gets one row for DBMS and one for Java. No single column identifies a row anymore, because Riya appears twice and DBMS appears twice. The pair (student_id, course_id) does, so that becomes the composite key, which is a key made of more than one column.

CREATE TABLE enrolment_1nf (
  student_id  VARCHAR(10),
  course_id   VARCHAR(10),
  instructor  VARCHAR(50),
  department  VARCHAR(50),
  fees        DECIMAL(10,2),
  PRIMARY KEY (student_id, course_id)
);
INSERT INTO enrolment_1nf VALUES
  ('S_RIYA',  'DBMS',   'Sen',    'CSE', 45000),
  ('S_RIYA',  'JAVA',   'Rao',    'CSE', 40000),
  ('S_ARJUN', 'DBMS',   'Sen',    'CSE', 45000),
  ('S_EMILY', 'PYTHON', 'Miller', 'IT',  38000);

The update anomaly is still here. Sen and CSE appear on every DBMS row, and the DBMS fee is typed again for each student. Each normal form also includes the one before it, so a table cannot be in 2NF unless it already passes 1NF.

Remove partial dependencies for 2NF

2NF moves every column that depends on only part of the composite key into its own table, and freeCodeCamp calls this a partial dependency. Here, course details leave the enrolment table.

Ask what the three course-level columns actually depend on. Knowing course_id alone tells you all three. The student_id half of the key adds nothing. That is the partial dependency, and the fix follows the same four moves every time:

1. Find the key

   Write down the primary key, here (student_id, course_id).

2. List dependencies

   For each non-key column, write which columns decide it.

3. Spot partial ones

   Mark any column decided by only part of the key.

4. Split

   Move those columns, with the part of the key they need, into a new table.

CREATE TABLE course (
  course_id   VARCHAR(10) PRIMARY KEY,
  instructor  VARCHAR(50),
  department  VARCHAR(50),
  fees        DECIMAL(10,2)
);
CREATE TABLE enrolment (
  student_id  VARCHAR(10),
  course_id   VARCHAR(10) REFERENCES course(course_id),
  PRIMARY KEY (student_id, course_id)
);

The Cloud course can now be inserted with no students. A fee change for DBMS is one row in course, not one row per student.

Remove transitive dependencies for 3NF

3NF stops a non-key column from depending on another non-key column, so department details move out of the course table, following the transitive dependency rule on GeeksforGeeks.

Follow the chain. course_id decides the instructor. The instructor decides the department. Department reaches the key only through instructor, like a message passed through a middleman. Split the middleman into its own table:

CREATE TABLE instructor (
  instructor_id  VARCHAR(10) PRIMARY KEY,
  name           VARCHAR(50),
  department     VARCHAR(50)
);
CREATE TABLE course (
  course_id      VARCHAR(10) PRIMARY KEY,
  instructor_id  VARCHAR(10) REFERENCES instructor(instructor_id),
  fees           DECIMAL(10,2)
);

Sen's move from CSE to IT is now a single-row update in instructor. The office from the opening never edits forty rows again.

Interviewers like a one-line test for 3NF: every non-key column depends on the key, the whole key, and nothing but the key. "The whole key" is 2NF. "Nothing but the key" is 3NF.

Apply BCNF when a determinant is not a superkey

A superkey is any set of columns that identifies a row uniquely. A candidate key is a smallest superkey, one with no column to spare. In a rule such as A → B, the column on the left is called the determinant. BCNF says: if column A decides column B, then A must be able to identify a whole row. This catches cases that 3NF allows, which is why GeeksforGeeks calls BCNF a stricter 3NF.

The 3NF course table breaks here, so I am changing the college's rules: one course can have several instructors. DBMS is so popular that it runs in two sections, Sen's and Iyer's. Each instructor still teaches only one course, and each student picks one section. The 3NF course table above, with one instructor_id per course, no longer fits, so the enrolment table has to record the instructor itself:

This table has two candidate keys: (student_id, course_id) and (student_id, instructor_id). The rule instructor_id → course_id also holds, yet instructor_id alone is not a superkey. This still passes 3NF, because course_id is part of a candidate key, and 3NF allows this case. BCNF does not, since instructor_id is a determinant that is not a superkey. Split it, and remove instructor_id from the course table, which now holds only course_id and fees:

CREATE TABLE instructor_course (
  instructor_id  VARCHAR(10) PRIMARY KEY,
  course_id      VARCHAR(10) REFERENCES course(course_id)
);
CREATE TABLE student_instructor (
  student_id     VARCHAR(10),
  instructor_id  VARCHAR(10) REFERENCES instructor_course(instructor_id),
  PRIMARY KEY (student_id, instructor_id)
);

With our example rows, the two new tables look like this:

One join rebuilds the original enrolment view of student, course and instructor:

SELECT si.student_id, ic.course_id, si.instructor_id
FROM student_instructor si
JOIN instructor_course ic ON ic.instructor_id = si.instructor_id;

Iyer's section can be recorded before any student joins it. The cost is real, though. The rule "one student, one section per course" no longer lives in a single table. Checking it needs a join or a trigger, which is code that runs on every insert.

Normal forms compared side by side

The table shows the rule each form checks and the anomaly it removes; the definitions follow GeeksforGeeks.

Two more forms exist. 4NF removes multi-valued dependencies, and 5NF removes join dependencies. In my experience, freshers are rarely asked about either beyond a definition.

Know when to stop normalizing

More tables mean more joins, so read-heavy reporting systems sometimes keep denormalized copies on purpose, a trade-off GeeksforGeeks lists under the costs of over-normalization.

My rule: stop at 3NF for most apps. Go to BCNF only when a real dependency like the section rule exists. For reports, keep a denormalized copy built from the clean tables. If you are heading toward analytics work, this trade-off shows up daily, which is why it sits in my list of data analyst skills.

Common mistakes in interview answers

In my experience, answers go wrong by mixing up partial and transitive dependency or by skipping the key before judging the table, and freeCodeCamp's worked examples both start by naming the key for this reason.

• Not naming the candidate key first. Partial and transitive dependency only make sense relative to a key. Say the key out loud before anything else.
• Calling any repeated value a violation. "CSE" appearing in two instructor rows is fine. Repetition is a problem only when the same fact is stored twice.
• Treating 3NF and BCNF as the same. Use the section example: 3NF allows a determinant that is not a superkey when it decides part of a key, BCNF does not.
• Forgetting that forms stack. If asked "is this in 3NF?", check 1NF and 2NF on the way.

Your next step: copy the enrolment table into MySQL or PostgreSQL and run each step yourself, running DROP TABLE on the old version of a table before you create the next one, and using INSERT INTO ... SELECT to copy the rows across. At every level, try to break it with an insert, an update and a delete, and note which one fails. Then explain the 1NF to BCNF path out loud in under two minutes.