Chapter 08 · Normalization: First, Second, and Third Normal Forms
Second Normal Form and Composite Keys
Apply Second Normal Form by removing dependencies on only part of a composite candidate key and separating facts by their true determinant.
Learning outcomes
Second Normal Form (2NF) builds on 1NF. Informally, a relation is in 2NF when every non-prime attribute is fully functionally dependent on every candidate key—not merely on a proper subset of a composite candidate key.
Recognize partial dependencies on composite candidate keys.
Separate attributes according to their true determinant.
Understand why single-attribute candidate keys are automatically free of partial dependency.
Apply 2NF without being fooled by surrogate primary keys.
Starting relation
Enrollment( student_id, course_id, student_name, course_title, final_grade)Candidate key: (student_id, course_id)Dependencies:
\[ student\_id \rightarrow student\_name \]
\[ course\_id \rightarrow course\_title \]
\[ (student\_id,course\_id) \rightarrow final\_grade \]
Why the relation violates 2NF
student_name depends on only student_id, which is part of the composite key. course_title depends on only course_id. These are partial dependencies.
2NF decomposition
Student( student_id, student_name)Course( course_id, course_title)Enrollment( student_id, course_id, final_grade)Now Enrollment contains only facts that depend on the whole enrollment key.
Full functional dependency
Y is fully functionally dependent on X when:
\[ X \rightarrow Y \]
and no proper subset of X determines Y.
For grade:
student_id alone -/-> final_gradecourse_id alone -/-> final_grade(student_id, course_id) -> final_grade2NF matters mainly with composite keys
If the only candidate key is a single attribute, there is no smaller non-empty subset to create a partial dependency. But do not stop analysis there: alternative composite candidate keys may still exist.
Surrogate-key trap
Suppose the bad Enrollment table receives:
enrollment_id INTEGER PRIMARY KEYand also:
UNIQUE (student_id, course_id)The business candidate key remains (student_id, course_id). Partial dependencies relative to that key still matter.
Evaluate all candidate keys and business dependencies, not only the declared primary key.
WorkshopHub example
PartUsageBad( work_order_id, part_id, part_description, quantity, charged_unit_price)Candidate key: (work_order_id, part_id)-- assuming one aggregate usage row per part/orderDependencies:
\[ part\_id \rightarrow part\_description \]
\[ (work\_order\_id,part\_id) \rightarrow quantity,charged\_unit\_price \]
part_description violates 2NF because it depends on only part of the key.
Decompose WorkshopHub PartUsage
Part( part_id, part_description)PartUsage( work_order_id, part_id, quantity, charged_unit_price)What belongs on the association?
Attributes such as quantity and charged unit price belong to the WorkOrder–Part association because they depend on the whole relationship instance.
2NF and weak entities
WorkOrderLine may use key:
(work_order_id, line_number)A line description specific to that line depends on the whole key. But customer_name depending only on work_order_id would be a partial dependency and belongs elsewhere.
Prime and non-prime attributes
A prime attribute appears in at least one candidate key. A non-prime attribute appears in no candidate key. Formal 2NF definitions focus on non-prime attributes being fully dependent on candidate keys.
Multiple candidate keys complicate analysis
Suppose a relation has two candidate keys. An attribute might depend fully on one but partially on another. 2NF must be evaluated against all candidate keys.
2NF removes one class of anomalies
After splitting Student and Course facts out of Enrollment:
- student name changes in one place;
- courses can exist before enrollment;
- deleting the last enrollment does not delete the course title.
But 2NF is not enough
This relation may be in 2NF:
WorkOrder( work_order_id, status_code, status_name)because the key is single-column. Yet:
\[ status\_code \rightarrow status\_name \]
creates a transitive dependency. 3NF addresses this.
Practice: identify partial dependencies
OrderLine relation
OrderLine( order_id, line_number, order_date, product_id, product_name, quantity)Key: (order_id, line_number)Assume order_id -> order_date and product_id -> product_name. Which attributes violate 2NF?
Review answer
order_date depends on order_id, which is only part of the key, so it is a partial dependency. Product name does not directly depend on a key subset unless product_id itself is functionally determined by the full line key; nevertheless product_name belongs to Product because product_id determines it. That latter issue is also explained through transitive dependency and broader determinant analysis.
Summary and next lesson
2NF ensures non-key facts do not depend on only part of a composite candidate key. It separates Student facts from Enrollment, Part facts from PartUsage, and parent facts from weak-child rows. The next lesson removes transitive dependencies through Third Normal Form.
References
- E. F. Codd, “Further Normalization of the Data Base Relational Model,” 1971.
- C. J. Date, Database Design and Relational Theory.
- Ramez Elmasri and Shamkant B. Navathe, Fundamentals of Database Systems.