Second Normal Form (2NF) in DBMS: Worked Decomposition and Exam Traps
Find the composite candidate key, trace partial dependencies, and decompose a populated enrolment relation without losing data.
KnowledgeGate Team
Exam prep & CS education

Second Normal Form does not mean splitting a table into two, and a composite key does not prove a violation. The deciding question is whether any non-prime attribute depends on only part of a candidate key. The ENROLLMENT_1NF relation requires key discovery, dependency tests, anomaly checks, and a populated 2NF decomposition. The decomposition stores nine tuples across three relations, and their natural join reconstructs the four original enrolments.
Second Normal Form in one precise test
A relation must first be in First Normal Form. It is in 2NF when every non-prime attribute is fully functionally dependent on every candidate key. A prime attribute belongs to at least one candidate key, while a partial dependency is X -> A, where X is a proper subset of a candidate key and A is non-prime.
For a key (K1,K2), apply one precise test: (K1,K2) -> value is full only when neither K1 -> value nor K2 -> value holds. If either proper subset determines the value, the relation fails 2NF.
This also gives a useful shortcut. A 1NF relation whose candidate keys are all single-attribute cannot contain a partial dependency, so it is automatically in 2NF, although it may still fail 3NF. Use Introduction to Normalization and 1NF in DBMS for the preceding job of flattening repeating groups and multivalued cells; the subset-dependency test here starts after atomicity. The CS Fundamentals category places both steps within the wider syllabus.
Set up the worked relation, rules and candidate key
Start with this exact scalar relation:
ENROLLMENT_1NF(student_id, course_id, student_name, course_title, instructor_id, instructor_name, grade)
student_id | course_id | student_name | course_title | instructor_id | instructor_name | grade |
|---|---|---|---|---|---|---|
S101 | C201 | Asha | DBMS | I7 | Meera | A |
S101 | C202 | Asha | Operating Systems | I9 | Ravi | B+ |
S102 | C201 | Vikram | DBMS | I7 | Meera | B |
S103 | C201 | Neha | DBMS | I7 | Meera | A- |
Apply the business rules before judging the normal form. Each student ID fixes one student name. Each course ID fixes one course title and, in this exercise, one instructor ID. Each instructor ID fixes one instructor name. Only a particular student-course pair fixes a grade.
The functional dependencies are student_id -> student_name; course_id -> course_title, instructor_id; instructor_id -> instructor_name; and (student_id,course_id) -> grade.
Compute the key. (student_id,course_id)+ reaches all seven attributes, so it is a superkey. Yet student_id+ = {student_id,student_name} and course_id+ = {course_id,course_title,instructor_id,instructor_name}; neither reaches grade or the other key component. Neither component can be removed, so (student_id,course_id) is the candidate key. Its components are prime; the other five attributes are non-prime.
Find each partial dependency and prove the 2NF failure
Test every non-prime attribute, including dependencies implied through other arrows.
Non-prime attribute | Smallest stated or implied determinant | Whole key needed? | 2NF result |
|---|---|---|---|
student_name | student_id | no | partial |
course_title | course_id | no | partial |
instructor_id | course_id | no | partial |
instructor_name | course_id through course_id -> instructor_id -> instructor_name | no | partial |
grade | (student_id,course_id) | yes | full |
Scalar cells put the relation in 1NF. Several non-prime attributes depend on proper subsets of its composite candidate key, so it is not in 2NF. The partial dependencies, not the composite key itself, are the violations.
The anomalies are concrete. Asha's name appears twice. The C201 facts DBMS, I7, and Meera appear three times, so renaming Meera to Meera Nair needs three consistent updates. A new course (C203, Computer Networks, I11, Karan) needs an invented student and grade. Deleting the sole C202 enrolment (S101,C202,...,B+) loses the course and instructor facts for C202 and I9.

Decompose to 2NF and populate every resulting table
Move each fact to the relation keyed by its determinant.
STUDENT(student_id PK, student_name)
student_id | student_name |
|---|---|
S101 | Asha |
S102 | Vikram |
S103 | Neha |
COURSE(course_id PK, course_title, instructor_id, instructor_name)
course_id | course_title | instructor_id | instructor_name |
|---|---|---|---|
C201 | DBMS | I7 | Meera |
C202 | Operating Systems | I9 | Ravi |
ENROLLMENT(student_id FK, course_id FK, grade, PK(student_id,course_id))
student_id | course_id | grade |
|---|---|---|
S101 | C201 | A |
S101 | C202 | B+ |
S102 | C201 | B |
S103 | C201 | A- |
In STUDENT, the single-attribute key determines student_name. In COURSE, its single-attribute key determines every non-prime attribute, leaving no proper key subset. In ENROLLMENT, grade needs the complete composite key. All three are in 2NF.
The original 4 rows become 3 + 2 + 4 = 9 tuples. Normalization gives each fact one home; it does not minimise row count. Join ENROLLMENT to STUDENT on student_id and COURSE on course_id: the same four seven-column rows return, with no invented tuple. The dependencies remain enforceable inside the new relations. ENROLLMENT keeps its natural composite key, with no surrogate ID.

Why this result is 2NF but not yet necessarily 3NF
Consider COURSE(course_id, course_title, instructor_id, instructor_name). Its only candidate key is the single attribute course_id. Therefore course_id -> instructor_name is not partial, and the relation satisfies 2NF.
However, course_id -> instructor_id -> instructor_name is a transitive chain. Under the stated rules, instructor_id is not a superkey and instructor_name is non-prime, so COURSE fails 3NF. The next decomposition would be INSTRUCTOR(instructor_id PK, instructor_name), containing (I7,Meera) and (I9,Ravi), plus COURSE(course_id PK, course_title, instructor_id FK), containing (C201,DBMS,I7) and (C202,Operating Systems,I9).
The boundary is precise: 2NF removes partial dependencies on part of a candidate key; 3NF separately tests whether non-key facts depend transitively through another non-key determinant. Higher forms impose stronger determinant or join-dependency conditions.
Common 2NF traps and the correction for each
Trap: assuming every composite key violates 2NF. This is incorrect because
(student_id,course_id) -> gradeis a full dependency. Test every non-prime attribute against every proper subset of each candidate key.Trap: assuming a single-column key guarantees a well-designed table. A single-column key rules out partial dependencies only, so the transitive chain in
COURSEcan remain. After confirming 2NF, test the relation separately for 3NF.Trap: checking only the declared primary key. The definition of 2NF applies to all candidate keys, and an alternate composite candidate key may reveal a partial dependency. Find every candidate key first, then mark every prime attribute before testing dependencies.
Trap: inferring dependencies from sample rows alone. Repeated values in the available rows may be coincidental and do not establish a functional dependency. Use the dependencies stated in the question or defined by the schema.
Trap: splitting columns arbitrarily. An arbitrary split may lose a determinant, duplicate facts, or prevent a lossless join. Move each determinant together with the attributes it determines, retain the candidate-key relation, then join the populated tables back to check the result.
How exams test 2NF without relying on memorised slogans
Exams test 2NF in several connected ways. They may ask you to find candidate keys and prime attributes, identify the highest normal form, or produce a valid decomposition. They may also test whether you can distinguish partial dependencies from transitive ones and verify reconstruction through a lossless-join check.
For R(A,B,C,D) with F = {AB -> C, A -> D}, start with the closures. AB+ = {A,B,C,D}, while A+ = {A,D} and B+ = {B}, so AB is the candidate key. The non-prime attribute D depends on A, a proper subset of AB, which means R is not in 2NF. Decompose it into R1(A,D) and R2(A,B,C), where C still requires the full key AB.
For S(P,Q,R,S) with F = {PQ -> R, R -> S}, no proper subset of PQ determines a non-prime attribute. The relation therefore satisfies 2NF. However, R -> S creates a transitive dependency, so it fails 3NF.
Use this solving order in every question:
Verify that the relation is in 1NF.
Find every candidate key.
Mark all attributes that are prime.
Compute the closures of proper subsets of each candidate key.
Move every partial dependency together with its determinant.
Recheck the resulting relations against the normal-form conditions.
For more practice, work through 12 solved DBMS normalization MCQs as the next guided problem set.
The short version and the next step
The key is (student_id,course_id). student_name depends only on student_id; course and instructor facts depend only on course_id; grade depends on both. Thus, the original scalar table is in 1NF but not 2NF. STUDENT, COURSE, and ENROLLMENT remove its partial dependencies.
Now add (S104,C202,Kabir,Operating Systems,I9,Ravi,A). The decomposition needs one new STUDENT row, no new COURSE row, and one new ENROLLMENT row. Write candidate keys and subset closures before naming the normal form.
For the next step, semester learners can continue with the Zero to Hero Complete CS Course, while placement learners can use CS Fundamentals for Placements by Sanchit Sir.
Keep learning
Second Normal Form (2NF) MCQs: 12 Solved Questions with Explanations
Test your 2NF understanding with 12 solved MCQs on full dependency, partial dependency, candidate keys, closures, and normal-form guarantees.
Boyce-Codd Normal Form (BCNF): Worked Decomposition and GATE Checks
See exactly how a relation can pass 3NF yet fail BCNF. The worked ENROLMENT example covers closures, keys, a lossless split, and the dependency that the split does not preserve.
CNF and GNF for GATE: CFG Simplification and Normal-Form Conversion, Step by Step
Run epsilon, unit and useless-production removal in the right order, convert one full grammar to CNF, and verify the two derivation-count formulas.
Third Normal Form (3NF) MCQs: 12 Solved Questions with Explanations
Solve 12 real 3NF exam MCQs with clear explanations, candidate-key closures, a raw-row decomposition, and the prime-attribute exception that separates 3NF from BCNF.





