Eliminating Anomalies from Functional Dependencies with BCNF

• Kroenke suggests two methods
  • Step By Step.
    • Change a table to 1NF, change this to 2NF, change these to 3NF change these to BCNF
    • This may require iteration on each table.
  • Straight to BCNF
    • Just design all of your tables so that every determinate is a candidate key.
• He also gives a suggested algorithm:
  1. Identify every functional dependency
  2. Identify every candidate key
  3. If there is a functional dependency that has a determinate that is not a candidate key
     1. Move the columns of that functional dependency into a new relation.
     2. Make the determinate of that functional dependency the primary key of the new relation
     3. Leave a copy of the determinant as a foreign key in the original relation.
     4. Create a referential integrity constraint between the original relation and the new relation.
  4. Repeat steps 1-3 until every determinant of every relation is a candidate key.
• Example 1, from the book:
  • The Schema and Dependencies:
    • SKU_DATA(SKU, SKU_Description, Department, Buyer)
    • SKU → (SKU_Description, Department, Buyer)
    • SKU_Description → (SKU, Department, Buyer)
    • Buyer → Department
  • Step By Step Method
    • The table is already in 1NF.
    • Select SKU as the primary key.
    • SKU_DATA(SKU, SKU_Description, Department, Buyer)
    • Using SKU as a primary key, the table is already in 2NF, Why?
    • The table is not in 3NF since SKU → Buyer → Department
    • So we need to remove (Buyer → Department) to a new table.
    • SKU_DATA(SKU, SKU_Description, Buyer)
    • BUYER(Buyer, Department)
    • Add the constraint SKU_DATA.Buyer must exist in BUYER.Buyer
    • Once again, examine SKU_DATA
    • SKU_DATA(SKU, SKU_Description, Buyer)
    • SKU → (SKU_Description, Department)
    • SKU_Description → (SKU, Department)
    • Both of the determinate are candidate keys so this is in BCNF
    • Look at BUYER, this is trivially in BCNF