Functional Dependencies

• Functional dependencies describe the relationship between attributes.
• Book Example CookieCost = NumberOfBoxes x5
  • If we know the NumberOfBoxes attribute then we know the CookieCost attribute
  • In some sense the NumberOfBoxes determines the CookieCost.
  • Or the CookieCost is functionally dependent on the Number of Boxes.
  • NumberOfBoxes → CookieCost
  • NumberOfBoxes is called the determinant
• Another book example
  • In our sports store example, ExtendPrice = Quantity X UnitPrice
  • (Quantity, UnitPrice) → ExtendedPrice
• Be careful, in the first two examples, this was just a Mathematical function.
• In our SKU_DATA table,
  • SKU → (SKU_Description, Buyer, Department)
  • Do we know this to be true?
  • Is there any case where this will not be true?
  • Determining Functional Dependencies may require knowledge about the business.
• He provides the following example

  • ObjectColor	Weight	Shape
    Red	5	Ball
    Blue	5	Cube
    Yellow	7	Cube

  • If we know the Weight, do we know anything else?
  • If we know the Shape, do we know anything else?
  • If we know the Color, do we know anything else?
  • Are we sure?
    • From the example, yes
    • But we would need to ask
    • And we can not ask "Does the color determine the object?"
  • ObjectColor → (Weight, Shape)
• Connolly defines "A functional dependency describes the relationship between attributes in a relation. For example, if A and B are attributes of relation R, B is functionally dependent on A (denoted A → B) , if each value of A is associated with exactly one value of B. (A and B may each consist of one or more attributes)
• Functional Dependencies in the SKU_DATA table
  • We are using the ORIGINAL SKU_DATA table, not the modified one we have been playing with.
  • We are making assumptions based on the data on hand, we need to double check these assumptions with the business people.
  • Does The Department determine anything?
    • If I know I have something dealing with Water Sports, do I know anything?
    • NO, multiple SKUs, SKU_Descriptions, and Buyers
  • Does the Buyer determine anything
    • It appears that a buyer may only be associated with one Department, therefore
    • Buyer → Department
  • How about SKU?
    • SKU → (SKU_Description, Buyer, Department)
  • Same for SKU_Description
    • SKU_Description → (SKU, Buyer, Department)
  • BY THE WAY box on 127
    • You cannot always determine functional dependencies from sample data. You may not have any sample data, or you may have just a few rows that are not representative of all of the data conditions. in such cases, you must ask the users who are experts in the application that creates the data. For the SKU_DATA table, you would ask questions such as,"Is a Buyer always associated with the same Department?" and "Can a Department have more than one Buyer?" In most case, the answers to such questions are more reliable than sample data. When in doubt, trust the users.
• Let's look at ORDER_ITEM
  • Does SKU → Price?
    • What if we give different prices at different times of the year
    • Or for preferred customers?
    • Or under other conditions?
  • (OrderNumber,SKU) → Price
  • (Price, Quantity) → ExtendedPrice
  • (OrderNumber, SKU) → (Quantity, Price, ExtendedPrice)
• We will use functional dependencies throughout database design.