Some more examples of Normalization

• The Equipment Repair table in 3-10

  • ItemNo	EquipmentType	AcquisitionCost	RepairNumber	RepairDate	RepairCost
    100	Drill Press	3500	2000	2011-05-05	375
    200	Lathe	4750	2100	2011-05-07	255
    100	Drill Press	3500	2200	2011-06-19	178
    300	Mill	27300	2300	2011-06-19	1875
    100	Drill Press	3500	2400	2011-07-05	0
    100	Drill Press	3500	2500	2011-08-17	75

  • EQUIPMENT_REPAIR(ItemNumber, EquipmentType, AcquisiontCost, RepairNumber, RepairDate, ReapairCost)
  • Dependencies:
    • RepairNumber → (ItemNo, EquipmentType, AcquisitionCost, RepairDate, RepairCost)
    • ItemNo → (EquipmentType, AcquisitionCost)
    • Are there any for EquipmentType, AcquisitionCost, RepairDate or RepairCost?
  • Select RepairNumber as a primary key
  • Remove ItemNo, EquipmentType and AcquisitionCost to a new table with ItemNo as primary key.
  • New Schema:
    • REPAIR(RepairNumber, ItemNo, RepairDate, RepairCost)
    • EQUIPMENT_ITEM(ItemNo, EquipmentType, AcquisitionCost)
  • Are these tables in BCNF?
• From Cape Codd
  • ORDER_ITEM(OrderNumber, SKU, Quantity, Price, ExtendedPrice)
  • (OrderNumber, SKU) → (Quantity, Price, ExtendedPrice)
  • (Quantity, Price) → ExtendedPrice.
  • We could derive:
    • ORDER_ITEM(OrderNumber, SKU, Quantity, Price)
    • EXTENDED_PRICE(Quantity,Price, ExtendePrice)
  • But this would be silly.
  • Can this computation be done by the DBMS?
  • Why would we store a computable field?
  • What is more, could such a table introduce errors?
• Student Activities
  • STUDENT_ACTIVITY(StudentID, StudentName, Activity, ActivityFee, AmountPaid)

  • StudentID	StudentName	Activity	ActivityFee	AmountPaid
    100	Jones	Golf	65	65
    100	Jones	Skiing	200	0
    200	Davis	Skiing	200	0
    200	Davis	Swimming	50	50
    300	Garrett	Skiing	200	100
    300	Garrett	Swimming	50	50
    400	Jones	Golf	65	65
    400	Jones	Swimming	50	50

  • What depends on?
    • StudentID
    • StudentName
    • Activity
    • ActivityFee
  • What questions can we ask to determine if we are correct?
    • Activity fees are fixed and based upon activities
  • Final Schema
    • STUDENT(StudentNo,StudentName)
    • ACTIVITY(Activity, ActivityFee)
    • PAYMENT(StudentNo, Activity, AmountPaid)
• Cape Codd SKU_Data (Revised)
  • SKU_DATA_3(SKU, SKU_Description, Department, DeptBudgetCode, Buyer)
  • Pete HansenPete HansenNancy MeyersNancy MeyersCindy LoCindy LoJerry MartinJerry Martin

    SKU	SKU_Description	Department	DeptBudgetCode	Buyer
    100100	Std. Scuba Tank, Yellow	Water Sports	BC-100
    100200	Std. Scuba Tank, Magenta	Water Sports	BC-100
    101100	Dive Mask, Small Clear	Water Sports	BC-100
    101200	Dive Mask, Med Clear	Water Sports	BC-100
    201000	Half Dome Tent	Camping	BC-200
    202000	Half Dome Tent Vestibule	Camping	BC-200
    301000	Light Fly Climbing Harness	Climbing	BC-300
    302000	Locking carabiner, Oval	Climbing	BC-300

  • What are the dependencies?
    • SKU → (SKU_Description, Department, DeptBudgetCode, Buyer)
    • SKU_Description → (SKU, Department, DeptBudgetCode, Buyer)
    • Buyer → (Department, DeptBudgetCode)
    • Department → DeptBudgetCode
  • Select SKU as primary key for SKU_DATA_4 table
  • Removing the Buyer → (Department, DeptBudgetCode) dependency
    • SKU_DATA_4(SKU, SKU_Description, Buyer)
    • BUYER(Buyer, Department, DeptBudgetCode)
  • Are both tables in BCNF?
  • Department → DeptBudgetCode
  • So we need to iterate again on BUYER
    • BUYER(Buyer,Department)
    • DEPARTMENT(Department,DeptBudgetCode)
  • what about SKU_Description →(SKU, Buyer) in