Second Normal Form and Partial Dependency

Definition

A relation is in second normal form when:

1. It is in 1NF, and
2. Every non prime attribute is fully functionally dependent on every candidate key.

Equivalently: no non prime attribute depends on a proper subset of any candidate key. Such a dependency is called a partial dependency.

Two consequences follow immediately. If every candidate key is a single attribute, no dependency can be partial, so the relation is automatically in 2NF. And prime attributes are exempt — 2NF constrains only non prime attributes.

The violating relation

  ENROLMENT ( roll, course, student_name, course_title,
              credits, marks )

  candidate key = { roll, course }

  prime attributes     : roll, course
  non prime attributes : student_name, course_title,
                         credits, marks

  Dependencies:
    roll             -> student_name      PARTIAL
    course           -> course_title      PARTIAL
    course           -> credits           PARTIAL
    { roll, course } -> marks             FULL

  Three partial dependencies -> NOT in 2NF.

  roll | course | student_name | course_title | credits | marks
  -----+--------+--------------+--------------+---------+------
   21  | CS201  | Meera        | Databases    |    4    |  87
   21  | CS202  | Meera        | Networks     |    3    |  74
   22  | CS201  | Ravi         | Databases    |    4    |  91
   23  | EC101  | Anitha       | Circuits     |    4    |  65

  Meera repeats per enrolment.
  Databases and its credit value repeat per student.

  ANOMALIES
    insert  a new course with no enrolments cannot be stored
    update  renaming a course means changing many rows
    delete  removing the last enrolment of a course erases
            the course itself

Converting to 2NF

1. Find every candidate key.
2. Identify the non prime attributes.
3. For each, ask whether it depends on the whole key or only part of it.
4. Move each partially dependent attribute into a new relation, keyed by the part it actually depends on.
5. Keep the attributes that depend on the whole key in the original relation.

  AFTER DECOMPOSITION

  STUDENT ( roll, student_name )
     21 Meera
     22 Ravi
     23 Anitha

  COURSE ( course, course_title, credits )
     CS201 Databases 4
     CS202 Networks  3
     EC101 Circuits  4

  ENROLMENT ( roll, course, marks )
     21 CS201 87
     21 CS202 74
     22 CS201 91
     23 EC101 65

  Check each:
    STUDENT     key roll, single attribute      -> 2NF
    COURSE      key course, single attribute    -> 2NF
    ENROLMENT   marks needs BOTH, fully dependent -> 2NF

  Anomalies resolved:
    a course with no enrolments now has its own row
    a title is changed in exactly one place
    deleting an enrolment loses only the enrolment

Prime attributes are exempt

  R ( A, B, C, D )
  F = { A B -> C D,  A -> B }
  candidate keys: { A B } and { A }

  Wait - if A -> B, then A+ includes B, C and D, so
  { A } alone is a candidate key.

  With { A } as a candidate key, EVERY attribute depends
  on a single attribute key, so no partial dependency can
  exist against it, and the relation IS in 2NF.

  LESSON: find ALL candidate keys before judging 2NF.
  Testing against only one of them gives the wrong answer,
  and this is the most common error in the topic.

Second worked example

  SUPPLY ( supplier_id, part_id, supplier_city,
           qty_supplied )

  candidate key = { supplier_id, part_id }

  Dependencies:
    supplier_id -> supplier_city                   PARTIAL
    { supplier_id, part_id } -> qty_supplied       FULL

  supplier_city depends on only part of the key, so
  NOT in 2NF.

  DECOMPOSE

    SUPPLIER ( supplier_id, supplier_city )
    SUPPLY   ( supplier_id, part_id, qty_supplied )

  Both are now in 2NF.

  Note what was gained: a supplier city is stored once
  instead of once per part supplied, and a supplier who
  currently supplies nothing can still be recorded.

Common mistakes

• Testing against one candidate key only. 2NF must hold for every candidate key.
• Applying 2NF to a single attribute key. It is automatically satisfied; say so and move on.
• Treating a prime attribute as a violation. Only non prime attributes are constrained.
• Removing an attribute without creating the new relation. Decomposition means splitting, not deleting.
• Forgetting the determinant becomes the key of the new relation.

Exam and interview questions

1. Define 2NF and partial dependency.
2. Why is a relation with a single attribute primary key always in 2NF?
3. Why are prime attributes exempt from the 2NF rule?
4. Convert ENROLMENT above to 2NF, showing every step.
5. What can go wrong if you test 2NF against only one candidate key?

Practice

1. R ( A, B, C, D ) with { A, B } as key and F = { A B -> C, A -> D }. Is it in 2NF? Decompose if not.
2. Normalise ORDER_ITEM ( order_no, item_code, item_name, qty, order_date ) to 2NF.
3. Give a relation in 1NF but not 2NF where the offending attribute is prime, and explain why it is still in 2NF.

Conclusion

2NF removes partial dependencies: no non prime attribute may depend on part of a key. Find every candidate key first, move each partially dependent attribute to a relation keyed by its real determinant, and remember the rule is vacuous when keys are single attributes.