What does Second Normal Form (2NF) require of a table, and what exactly is a partial dependency?

## The vocabulary 2NF is built on A **functional dependency**, written X to Y, means any two rows that agree on the attributes in X must also agree on Y. "Order number determines order date" is a functional dependency: one order number can never carry two different dates. A **candidate key** is a minimal set of attributes that functionally determines every other attribute of the relation. Minimality matters: `(order_id, product_id)` may be a candidate key while `(order_id, product_id, quantity)` is only a superkey, because you can drop `quantity` and still determine everything. A relation can have several candidate keys; one is designated the primary key. A **prime attribute** is any attribute that appears in at least one candidate key. Every other attribute is **non-prime**. This distinction is the whole of 2NF. ## The rule itself A relation is in Second Normal Form when both hold: 1. It is in First Normal Form (atomic, single-valued attributes, no repeating groups). 2. No non-prime attribute is functionally dependent on a **proper subset** of any candidate key. A violation of clause 2 is a **partial dependency**. Note three things about the scope. First, 2NF is defined against *all* candidate keys, not only the one you picked as primary. Second, it says nothing about dependencies whose determinant is a non-key attribute; that is Third Normal Form's job. Third, it says nothing about dependencies among prime attributes. A direct corollary: if every candidate key consists of a single attribute, the only proper subset is the empty set, so no partial dependency can exist and any 1NF relation is already in 2NF. Partial dependencies are a phenomenon of composite keys only. ## Why partial dependencies hurt Take `ENROLLMENT(student_id, course_id, grade, student_name, course_title)` keyed by `(student_id, course_id)`. `student_name` depends on `student_id` alone and `course_title` on `course_id` alone. - **Redundancy.** A student's name is stored once per enrolment row. A course title is stored once per enrolled student. - **Update anomaly.** Correcting a misspelled name requires touching every row for that student. Miss one and the database now holds two contradictory names, which no key constraint prevents. - **Insertion anomaly.** You cannot record a newly created course that nobody has enrolled in, because `student_id` is part of the primary key and cannot be null. - **Deletion anomaly.** Deleting the last enrolment for a course destroys the only record of its title. All three anomalies come from storing a fact about one entity in a table whose key identifies a different, finer-grained thing. ## The decomposition For each partial dependency, project the determinant plus the attributes it determines into a new relation keyed by that determinant, and drop those attributes from the original: - `STUDENT(student_id, student_name)` - `COURSE(course_id, course_title)` - `ENROLLMENT(student_id, course_id, grade)` with foreign keys to both The decomposition is **lossless**: each new relation shares its key with the residual relation, so the natural join reconstructs the original exactly, with no spurious rows. The original facts are all still derivable; only the duplication is gone. ## A method you can run in an interview 1. Enumerate the candidate keys from the stated functional dependencies. Do not just accept the declared primary key. 2. If every candidate key is a single attribute, stop: the relation is in 2NF. 3. Otherwise, for each composite candidate key, take each proper subset and ask whether any non-prime attribute is determined by that subset alone. 4. Every yes is a partial dependency. Extract it. 5. Re-check the residual relation, then continue to 3NF. ## Judgement, not mechanics Whether a dependency is partial depends on what the attribute *means*, not on its name. In `ORDER_LINE(order_id, product_id, quantity, unit_price)`, if `unit_price` is the price actually charged on this line then it depends on the full key and stays. If it means the product's current catalogue price then it depends on `product_id` alone, is partial, and belongs in the product table. Interviewers use exactly this pair to see whether you reason about semantics or pattern-match on column names. Finally, 2NF is a stepping stone. Nobody targets 2NF as a design goal; you pass through it on the way to 3NF or Boyce-Codd Normal Form. Its value in an interview is that it names the specific defect of composite keys precisely.