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

Updated 20 Nov 20266 min read
Second Normal Form (2NF) in DBMS: Worked Decomposition and Exam Traps

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.

The ENROLLMENT_1NF table showing partial dependencies on parts of its composite key alongside the full dependency to grade.

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.

Decomposing ENROLLMENT_1NF into separate STUDENT, COURSE, and ENROLLMENT tables whose natural join reconstructs the original rows.

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) -> grade is 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 COURSE can 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:

  1. Verify that the relation is in 1NF.

  2. Find every candidate key.

  3. Mark all attributes that are prime.

  4. Compute the closures of proper subsets of each candidate key.

  5. Move every partial dependency together with its determinant.

  6. 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.