Does 2NF require a composite primary key?

No. It is a property tested against candidate keys; partial dependencies commonly become visible with composite keys.

Does a generated ID automatically fix a violation?

No. Other candidate keys and the original dependencies still need examination.

Is a 2NF table necessarily in 3NF?

No. Nonprime attributes may still depend transitively on a key through another determinant.

AI Glossary / Data engineering and architecture / Intermediate

Second normal form (2NF)

Definition

Second normal form, abbreviated 2NF, is a relational database design condition. A relation must be in first normal form, and every nonprime attribute must be fully functionally dependent on every candidate key. In practical terms, a nonprime fact must not depend on only a proper part of a candidate key. A nonprime attribute belongs to no candidate key. The general definition considers all candidate keys, not only the one selected as the primary key.

What it means

A table may combine facts about an association with facts about only one participant in that association. For example, an enrollment identified by LearnerID and CourseID might repeat a learner's name on every enrollment. If the learner identifier alone determines the name, the name has a partial dependency on the enrollment key. Second normal form identifies this mismatch so the learner fact can be represented at its own appropriate grain.

How it works

Start by establishing first normal form and the valid functional dependencies. Identify candidate keys from those rules rather than assuming that today's unique-looking columns are keys. For each nonprime attribute, check whether a proper subset of any candidate key determines it. If so, separate the dependent fact into a relation with its determinant and preserve the association in the remaining design. Verify that the decomposition retains the intended information and supports the necessary constraints and business operations.

Three practical examples

Enrollments: learner names can be stored with learners, while the learner-course association stores facts about that enrollment.
Order lines: an order-level delivery instruction should not be repeated as if it were determined by both OrderID and LineNumber.
AI labeling: an annotator's organizational details can be separate from annotations identified by item and annotator, while the label remains attached to the actual annotation event.

Think of it this way

Think of a folder labeled with two identifiers that contains some papers relevant to only one of them. Moving those papers to the appropriate single-identifier folder reduces unnecessary copies. The analogy is limited because 2NF depends on formal dependencies and candidate keys; visual repetition or a convenient filing preference is not enough to prove a violation.

Worked example

Consider Enrollment(LearnerID,CourseID,LearnerName,Grade), where the candidate key is (LearnerID,CourseID). Assume LearnerID → LearnerName and (LearnerID,CourseID) → Grade, with no other candidate keys. Three enrollments for L7 repeat the same learner name three times. LearnerName is nonprime and depends on only LearnerID, so the relation violates 2NF. Create Learner(LearnerID,LearnerName) and Enrollment(LearnerID,CourseID,Grade). The grade remains associated with the full enrollment key, while the learner name has one authoritative fact location. If learners can take the same course several times, this key is insufficient: an attempt or offering identifier must be considered before normalization. The example assumes the stated rules; it does not infer them from three sample rows.

Illustrative case study

Fictional example: Pine Academy notices that changing a learner's preferred name requires editing several enrollment records. The database designer confirms that the repeated name is intended to be the current learner name rather than a historical certificate name. The team separates that fact and tests learners with no enrollments. This invented case emphasizes meaning: a deliberate historical snapshot may require different rules and should not be removed merely because its text is repeated.

Common misconceptions and mistakes

Do not reduce 2NF to “use a single-column primary key.” Adding a surrogate key does not erase other candidate keys or their partial dependencies. Do not test prime attributes as if the rule concerned every attribute equally. If all candidate keys are single attributes, ordinary partial-key violations cannot arise, but first normal form still matters. Also, satisfying 2NF does not remove transitive dependencies or establish 3NF. Repeated values such as a city name are not, by themselves, proof of a normalization defect.

Business use

Second normal form helps reduce inconsistent updates and clarify which entity owns a fact. This is useful when operational records become shared inputs to reporting and machine learning. A corrected design can make it easier to update one fact without accidentally changing unrelated associations. It does not guarantee faster queries, eliminate joins, or validate every business rule. Evaluate the complete logical design and preserve historical meaning before planning the implementation or denormalized reporting copies.

Check your understanding

In a table keyed by OrderID and LineNumber, OrderID alone determines OrderDate. OrderDate is nonprime. Is that compatible with 2NF?

Answer: No. It is a partial dependency on a candidate key, so the order-level date should be considered for a separate order relation under those assumptions.

Related terms

Learn next

list the candidate keys and dependencies for a small association table. Mark every nonprime attribute and test each against proper subsets of the keys. After proposing a decomposition, check reconstruction, insertion of independent entities, and the meaning of historical values rather than stopping at a diagram.

Nonprime attribute

Sources

Sources: University of Otago COSC344, Lecture 9: Normalisation, https://www.cs.otago.ac.nz/cosc344/lectures/L9_2022.pdf ; University of Otago COSC344, Lecture 8: Functional Dependencies, https://www.cs.otago.ac.nz/cosc344/lectures/L8_2022.pdf ; Adrienne Watt and Nelson Eng, Database Design, Normalization, https://opentextbc.ca/dbdesign01/chapter/chapter-12-normalization/ . Primary instructor passages and textbook excerpts consulted. The general candidate-key definition is used rather than the simplified primary-key-only teaching definition. Examples are original.

Previous: Seasonally adjusted annual rate Next: Second Order Centrality