Second Normal Form (2NF)

2NF works by splitting a table so that no non-key attribute is determined by part of a composite key: when an attribute's true determinant is a proper subset of the key, you move that attribute into its own table keyed by that subset, which is what stops the same fact from being stored once per row that happens to share the subset.

This only bites when the primary key is composite. If the key is a single column, no proper subset of it can be a determinant, so a 1NF table with a single-attribute key is automatically in 2NF. The whole question of 2NF is therefore: given a composite key, does any non-key attribute depend on only some of the key columns?

The running example: one messy sheet, carried through 1NF → 2NF → 3NF

The 1NF, 2NF, and 3NF pages walk one messy enrollment sheet end to end — the same three students the 1NF page split apart, now with their department columns on board. Stage zero, the raw export nobody has normalized yet:

Stage 1 — 1NF (previous page). The Courses cell is not atomic; the FD machinery can't even start until every cell holds one value. Splitting the comma list gives one row per enrollment and widens the key to the composite {Student_ID, Course_ID}:

Stage 2 — this page. With the key now composite, look at what Student_ID alone determines: Student_ID → Student_Name, Department_ID, Department_Name, Department_Location — a determinant that is only part of the key. That partial dependency is exactly the violation the walkthrough below dissects and fixes by splitting out a Student table, leaving Enrollment(Student_ID, Course_ID) behind. (To keep the tables narrow, the walkthrough writes the three department columns as a single Department column — its name; Department_ID and Department_Location travel with it unchanged and take center stage on the 3NF page, which picks up the Student table this split produces.)

The starting table and its functional dependencies

Consider Student_Course_Enrollment, recording which students take which courses, plus each student's name and department. The chosen key is the composite {Student_ID, Course_ID} — you need both to identify one enrollment row.

Write out the functional dependencies the data obeys (this is exactly the FD reasoning from the previous unit):

• {Student_ID, Course_ID} → Student_Name, Department — trivially true, the key determines everything.
• Student_ID → Student_Name, Department — a student's name and department do not change with the course. This is the dangerous one.

The second FD has a determinant (Student_ID) that is a proper subset of the key. So Student_Name and Department are partially dependent on the key. That is the 2NF violation, stated precisely in FD terms.

Connecting to closure and candidate keys

Recall closure from the FD unit. Compute the closure of the partial determinant: {Student_ID}+ = {Student_ID, Student_Name, Department}. It does not contain Course_ID, so Student_ID alone is not a candidate key — you genuinely need the composite. Yet Student_ID still functionally determines two attributes. A non-prime attribute (one not part of any candidate key) that is determined by a proper subset of a candidate key is the formal definition of a 2NF violation. Closure is the tool that tells you whether a determinant is the whole key or just part of it.

The anomaly this prevents (the real payoff)

Leave the table un-normalized and watch what the partial dependency costs you. Alice's department is the same fact stored in two rows, because she appears once per course:

Now Alice transfers to Arts. The correct update touches every row for student 101:

UPDATE Student_Course_Enrollment
SET Department = 'Arts'
WHERE Student_ID = 101;   -- must hit BOTH rows

If a buggy query, a partial transaction, or a careless edit updates only the C101 row, the table now says Alice is in Science and Arts simultaneously — an update anomaly. There is no single place that holds "Alice's department," so the database cannot enforce that it has one value. Two related anomalies ride along:

• Insertion anomaly: you cannot record a new student's department until they enroll in at least one course, because there is no row without a Course_ID.
• Deletion anomaly: if Carol drops her only course (C103), deleting that row also erases the fact that Carol is in Arts.

Decomposing into 2NF

The fix follows mechanically from the FDs: each determinant becomes the key of its own table, carrying exactly the attributes it determines.

Student — keyed by Student_ID

Holds attributes determined by Student_ID alone. Each student's department now lives in exactly one row.

Enrollment — keyed by {Student_ID, Course_ID}

Holds only the relationship. Student_ID is a foreign key back to Student.

Now Alice's transfer is a one-row update with no way to go inconsistent:

UPDATE Student SET Department = 'Arts' WHERE Student_ID = 101;

The original table is recoverable by joining the two on Student_ID, so the decomposition is lossless — you traded duplication for a join, not for lost information.

Pitfalls

• Assuming a single-column key needs 2NF work. 2NF can only be violated by a composite key. If your key is one column, you are already in 2NF the moment you reach 1NF — do not invent decompositions that aren't required.
• Confusing partial with transitive dependency. 2NF removes dependence on part of the key. If a non-key attribute depends on another non-key attribute (e.g. Department → Department_Head), that is a transitive dependency and is left for 3NF. A table can be in 2NF and still be ugly.
• Eyeballing dependencies instead of checking the data's real FDs. Whether Student_ID → Department holds is a fact about the business, not the sample rows. If a student could belong to two departments, that FD is false and the "violation" disappears. Confirm the FD before decomposing.
• Forgetting the foreign key. After splitting, Enrollment.Student_ID must reference Student.Student_ID. Skip the FK constraint and you can insert enrollments for students who don't exist — you removed one anomaly and opened another.
• Over-normalizing hot read paths. 2NF is the default for transactional schemas, but analytics and read-heavy stores often denormalize on purpose to avoid joins. Normalize first; relax deliberately, with eyes open to the update anomalies you are re-accepting.

Takeaways

• 2NF = 1NF plus no non-key attribute depends on only part of a composite key. Single-column keys get it for free.
• Find violations by listing FDs and computing closures: a determinant that is a proper subset of a candidate key, determining a non-prime attribute, is the violation.
• The concrete payoff is killing the update/insert/delete anomalies caused by storing one fact in many rows — Alice's department now has exactly one home.
• Decompose by giving each determinant its own table; reconnect with a foreign key; verify the join restores the original (lossless).

Sources: Codd, E. F., "Further Normalization of the Data Base Relational Model" (1971); Garcia-Molina, Ullman & Widom, Database Systems: The Complete Book, 2nd ed., ch. 3 (functional dependencies, closure, decomposition); Silberschatz, Korth & Sudarshan, Database System Concepts, 7th ed., ch. 7. Re-authored and deepened for this guide: added the mechanism-first framing, the concrete update/insert/delete anomalies, the closure/candidate-key link to the FD unit, the dependency diagram, and engineering pitfalls.