DBMS Normalization from 1NF to BCNF: One Complete Worked Example

You may remember that normalization removes redundancy, yet still get stuck when a question gives you a relation and functional dependencies. A single university-registration relation moves from a repeating group to BCNF through candidate-key closures and each decomposition. Name the highest normal form, justify each violation, and check the result during your GATE CS preparation.

What normalization fixes, and what it does not

Normalization gives each fact one home according to declared dependencies. The 1NF relation is R(StudentID, StudentName, DeptID, DeptName, CourseID, InstructorID, Grade):

Changing D10 from CSE to Computer Science needs three updates. You cannot insert I10 -> C103 before a student registers, and deleting Ravi's only row removes the I9 occurrence. Normalize from declared business-rule dependencies, not sample patterns; extra tables are not automatically better.

First Normal Form: turn the repeating group into atomic rows

Start with Asha's unnormalised record:

S1 | Asha | D10 | CSE | {(C101,I7,A), (C102,I8,B+)}

The Courses cell contains a repeating group. Replace it with (S1,Asha,D10,CSE,C101,I7,A) and (S1,Asha,D10,CSE,C102,I8,B+). Adding (S2,Ravi,D10,CSE,C101,I9,A-) and (S3,Noor,D20,ECE,C102,I8,B) gives the exact table above. Every cell now contains one student ID, name, department value, course ID, instructor ID, or grade. B+ is one grade-domain value; its plus sign does not create a repeating group. The row boundary represents each registration. That grain matters: if one row mixed a student with a set of courses again, the relation would fall outside 1NF and its cells would no longer describe one registration each.

That is 1NF. Atomicity is judged against the chosen domain, not by asking whether a string such as Asha can be split into characters. The conversion removes the repeating group, but values including Asha, D10, CSE, C101, and I8 still repeat. It neither solves every anomaly nor prescribes a physical storage layout.

Functional dependencies and candidate keys come before 2NF

Use this complete business-rule dependency set:

• StudentID -> StudentName, DeptID

• DeptID -> DeptName

• (StudentID, CourseID) -> InstructorID, Grade

• InstructorID -> CourseID

A determinant is the left side of a dependency. A superkey determines every attribute; a candidate key is a minimal superkey. Attributes in any candidate key are prime, while all others are non-prime.

For (StudentID,CourseID)+, start with {StudentID,CourseID}. Add {StudentName,DeptID}, then DeptName, then {InstructorID,Grade}. The closure reaches all seven attributes. By contrast, StudentID+ = {StudentID,StudentName,DeptID,DeptName}, and CourseID+ = {CourseID}, so neither component is a key.

For (StudentID,InstructorID)+, first use InstructorID -> CourseID. The student path adds StudentName, DeptID, and DeptName; the student-course dependency adds Grade. It also reaches all seven attributes. Since neither StudentID+ nor InstructorID+ reaches all attributes, this key is also minimal. The two candidate keys are therefore (StudentID,CourseID) and (StudentID,InstructorID). StudentID, CourseID, and InstructorID are prime; the other four attributes are non-prime.

Second Normal Form: remove the partial dependency

A 1NF relation is in 2NF when no non-prime attribute depends on a proper subset of any candidate key. Here, StudentID -> StudentName,DeptID is partial because StudentID is a proper subset of both composite candidate keys. StudentID -> DeptName follows through DeptID. Grade, however, remains fully dependent on the student-course combination. (StudentID,InstructorID) reaches it only after InstructorID supplies CourseID; neither individual component reaches Grade alone.

Decompose the relation into:

• STUDENT_DETAILS(StudentID PK, StudentName, DeptID, DeptName): (S1,Asha,D10,CSE), (S2,Ravi,D10,CSE), (S3,Noor,D20,ECE)

• REGISTRATION(StudentID, CourseID, InstructorID, Grade): (S1,C101,I7,A), (S1,C102,I8,B+), (S2,C101,I9,A-), (S3,C102,I8,B)

STUDENT_DETAILS has a single-attribute key. REGISTRATION retains candidate keys (StudentID,CourseID) and (StudentID,InstructorID). Within it, the closures of StudentID, CourseID, and InstructorID alone do not include the only non-prime attribute, Grade, while both complete keys do. Therefore no non-prime attribute has a partial dependency. The split is lossless because the common StudentID determines all of STUDENT_DETAILS; joining on it reconstructs every original registration row.

Third Normal Form: remove the transitive department fact

STUDENT_DETAILS still contains StudentID -> DeptID -> DeptName. The formal 3NF test says that for every non-trivial X -> A, either X is a superkey or A is prime. For DeptID -> DeptName, DeptID is not a superkey and DeptName is not prime.

Split it into STUDENT(StudentID PK, StudentName, DeptID FK) with (S1,Asha,D10), (S2,Ravi,D10), (S3,Noor,D20), and DEPARTMENT(DeptID PK, DeptName) with (D10,CSE), (D20,ECE). Keep REGISTRATION unchanged. StudentID is the determinant and key in STUDENT; DeptID plays both roles in DEPARTMENT. Each relation therefore satisfies BCNF too, and the department name now has one home.

REGISTRATION is in 3NF even though InstructorID -> CourseID has a determinant that is not a superkey. CourseID is prime because it belongs to candidate key (StudentID,CourseID). This prime-right-side allowance is where 3NF and BCNF separate in this example.

BCNF: remove the non-key determinant and check the trade-off

BCNF is stricter: every determinant of a non-trivial dependency must be a superkey. In REGISTRATION, InstructorID+ = {InstructorID,CourseID}. It does not reach StudentID or Grade, so InstructorID is not a superkey and InstructorID -> CourseID violates BCNF. This determinant therefore fails the stricter rule.

Decompose REGISTRATION into:

• INSTRUCTOR_COURSE(InstructorID PK, CourseID): (I7,C101), (I8,C102), (I9,C101)

• STUDENT_INSTRUCTOR_GRADE(StudentID, InstructorID, Grade): (S1,I7,A), (S1,I8,B+), (S2,I9,A-), (S3,I8,B)

The first relation has key InstructorID. In the second, (StudentID,InstructorID) determines Grade, so that pair is the key. Both relations satisfy BCNF for their projected dependencies.

The split is lossless. Its intersection is InstructorID, and InstructorID -> CourseID determines the entire INSTRUCTOR_COURSE relation. Joining on InstructorID restores C101 through I7, C102 through I8 for two rows, and C101 through I9. That reconstructs the four registrations exactly. However, (StudentID,CourseID) -> InstructorID cannot be enforced inside either new table alone. Checking it may require a join or an extra constraint. Lossless join and dependency preservation are separate properties, which is why a dependency-preserving 3NF design may sometimes be preferred to BCNF.

How GATE-style questions and interviews test normalization

Use a fixed workflow: write the FD set, compute every candidate key, mark prime attributes, test 2NF, 3NF, and BCNF in that order, decompose on the violating FD, then check lossless join and dependency preservation.

Watch for these distractors:

• Checking only the chosen primary key instead of both candidate keys.

• Inferring CourseID -> InstructorID from the sample rows.

• Saying a single-attribute key guarantees BCNF. It only makes 2NF automatic.

• Forgetting the prime-right-side allowance in 3NF.

• Accepting a decomposition without checking losslessness.

Use DBMS Normalization MCQs to apply the sequence to twelve solved questions. In an interview, do more than recite definitions: explain one anomaly, compute one closure, and show why InstructorID -> CourseID satisfies 3NF but violates BCNF.

The short version and the next study step

Use atomic values for 1NF, remove partial dependencies of non-prime attributes for 2NF, apply the superkey-or-prime test for 3NF, and require every determinant to be a superkey for BCNF. In this case, StudentID -> DeptID -> DeptName drives the 3NF split, while InstructorID -> CourseID drives the BCNF split. Then check losslessness and dependency preservation. Continue with GATE Guidance by Sanchit Sir and Zero to Hero. The university-registration relation keeps one FD set from its repeating group through the BCNF trade-off; Normalization in DBMS: 1NF to BCNF Rules, Worked Example is the separate rule-by-rule reference. Find keys first, name the violated rule second, and decompose only after both are justified.