Second Normal Form

• A table is in second normal form (2NF) if it is in 1NF and all non-key attributes are determined by the entire primary key.
  • Note, if the primary key is not composite, then the table is in 2NF.
• Consider the following table:
  • STUDENT_ACTIVITY(StudentID, Activity, ActifityFee)

  • StudentID	Activity	ActivityFee
    100	Golf	65.00
    100	Skiing	200.00
    200	Skiing	200.00
    200	Swimming	50.00
    300	Skiing	200.00
    300	Swimming	50.00
    400	Golf	65.00
    400	Swimming	50.00
    500	Tennis	35.00

  • We are told
    • (StudentID, Activity) → ActivityFee
    • Activity → ActivityFee
  • How could we introduce an error in this table?
    • Inserting items
    • Deleting Items
    • Modifying Items
• Consider the new schema
  • STUDENT_ACTIVITY(StudentID, Activity)
  • ACTIVITY_FEE(Activity, ActivityFee)

  • StudentID	Activity
    100	Golf
    100	Skiing
    200	Skiing
    200	Swimming
    300	Skiing
    300	Swimming
    400	Golf
    400	Swimming
    500	Tennis

  • Activity	ActivityFee
    Golf	65.00
    Swimming	50.00
    Skiing	200.00
    Tennis	35.00

  • Notice, we can not break these tables in the same way.
  • We can still enter bad data, just not multiple copies of that data.
• Generically speaking for R(a,b,c,d)
• None of the following can be true if the table is in 2NF.
  • a → c
  • a → d
  • b → c
  • b → d