Second normal form
🏠The everyday picture
An enrolment register that also writes the course's full syllabus on every line. The syllabus depends only on the course, not on which student enrolled, so it belongs in the course register.
📘What it really is
-
Second normal form (2NF) matters for tables with a composite primary key (Module 52). A table is in 2NF when it's in 1NF and every non-key column depends on the whole key, not just part of it.
-
Example:
enrolments (student_id, course_id, enrolled_on, course_title, course_fee). enrolled_ondepends on both student and course: fine;-
course_titleand the course's standard fee depend only oncourse_id: they belong incourses. -
Fix: move columns that depend on part of the key into the table for that part.
-
Careful: the fee actually charged for this enrolment (perhaps with a discount) depends on the enrolment and stays; the course's standard fee belongs to the course. Same-looking values can be different facts (Module 53, Lesson 3).
-
Tables with a single-column primary key are automatically in 2NF once they're in 1NF.
🖱️Try it
In your 1NF orders design, check the items table: which columns depend only on the product (name, current price,
category)? Move them to a products table, keeping only what belongs to the order line (quantity, price charged).
✅Quick check
In order_items (order_id, product_id, quantity, product_name), which column breaks 2NF?
Show the answer
product_name: it depends only on product_id, part of the key.
Is the price charged on an order line a 2NF violation?
Show the answer
No. It's a fact about that sale (the whole key), different from the product's current price.