Some examples

• SKU_DATA
  • We know
    • SKU_DATA(SKU, SKU_Description, Department Buyer)
    • SKU → (SKU_Description, Department, Buyer)
    • SKU_DESCRIPTION → (SKU, Department, Buyer)
    • Buyer → Department
  • Is it in 1NF?
  • Is it in 2NF? (primary key is not compound, so yes)
  • Is it in 3NF? no, Buyer → Department
  • Break it into
    • SKU_DATA(SKU, SKU_Description, Buyer)
    • BUYER(Buyer, Department)
• EQUIPMENT_REPAIR
  • EQUIPMENT_REPAIR(ItemNumber, EquipmentType, AcquisitionCost, RepairNumber, RepairDate, RepairCost)
  • ItemNumber → (EquipmentType, AcquisitionCost)
  • RepairNumber → (ItemNumber, EquipmentType, AcquisitionCost, RepairDate, RepairCost)
  • Again, not in 3NF
  • EQUIPMENT_ITEM(ItemNumber,EquipmentType, AcquisitionCost)
  • REPAIR(RepairNumber,ItemNumber,RepairDate, RepairCost)
• ORDER_ITEM
  • ORDER_ITEM(OrderNumber, SKU, Quantity, Price, ExtendedPrice)
  • (OrderNumber , SKU) → (Quantity, Price, ExtendedPrice)
  • (Quantity, Price) → ExtendedPrice
  • We could break it into two tables
    • ORDER_ITEM(OrderNumber, SKU, Quantity, Price)
    • EXTENDED_PRICE(Quantity, Price, ExtendedPrice)
    • But this seems silly
    • We are just storing a multiplication table.
  • Better
    • ORDER_ITEM(OrderNumber, SKU, Quantity, Price)
    • And compute ExtendedPrice
• Student Activities
  • STUDENT_ACTIVITY(StudentID, StudentName, Activity, ActivityFee, AmountPaid)

  • StudentID	StudentName	Activity	ActivityFee	AmountPaid
    100	Jones	Golf	65.00	65.00
    100	Jones	Skiing	200.00	0.00
    200	Davis	Skiing	200.00	0.00
    200	Davis	Swimming	50.00	50.00
    300	Garrett	Skiing	200.00	100.00
    300	Garrett	Swimming	50.00	50.00
    400	Jones	Golf	65.00	65.00
    400	Jones	Swimming	50.00	50.00

  • StudentID → StudentName
  • Activity → ActivityFee
  • (StudentID, Activity) → AmountPaid
  • So StudentID and StudentName need to go to a table.
  • Activity and ActivityFee need to go to a table.
  • STUDENT(StudentID,StudentName)
  • Activity(Activity, ActivityFee)
  • PAYMENT(StudentID,Activity,AmountPaid)
• SKU_DATA_3
  • SKU_DATA_3(SKU, SKU_Description, Department, DeptBudgetCode, Buyer)

  • SKU	SKU_Description	Department	DeptBudgetCode	Buyer
    100100	Std. Scuba Tank, Yellow	Water Sports	BC-100	Pete Hansen
    100200	Std. Scuba Tank, Magenta	Water Sports	BC-100	Pete Hansen
    101100	Dive Mask, Small Clear	Water Sports	BC-100	Nancy Meyers
    101200	Dive Mask, Med Clear	Water Sports	BC-100	Nancy Meyers
    201000	Half-dome Tent	Camping	BC-200	Cindy Lo
    202000	Half-dome Tent, Vestibule	Camping	BC-200	Cindy Lo
    301000	Light Fly Climbing Harness	Climbing	BC-300	Jery Martin
    302000	Locking carabiner, Oval	Climbing	BC-300	Jery Martin

  • Buyer → (Department, DeptBudgetCode)
  • Department → DeptBudgetCode
  • DeptBudgetCode → Department
  • SKU →(SKU_Description, Department, DeptBudgetCode, Buyer )
  • SKU_Description(SKU, Department, DeptBudgetCode, Buyer)