Marcia’s Dry Cleaning – P.97

1. Marcia’s Dry Cleaning – P.97

2. Assume that Marcia keeps a table of data about her customers. Consider just the following part of that table:CUSTOMER (Phone, FirstName, LastName) Explain the conditions under which each of the following are true: 1.Phone  (FirstName, LastName) 2. (Phone, FirstName)  LastName 3. (Phone, LastName)  FirstName 4. (LastName, FirstName)  Phone 5. Phone  LastName 6. Phone  FirstName 7. Phone  (FirstName, LastName) • Is condition 7 the same as conditions 5 and 6? Why or why not?

3. Answer to A. 1. Phone  (FirstName, LastName) TRUE when phone numbers uniquely determines (FirstName, LastName) combo. 2. (Phone, FirstName)  LastName TRUE when each combination of phone number and FirstName is unique. 3. (Phone, LastName)  FirstName TRUE when each combination of phone number and LastName is unique. 4. (LastName, FirstName)  Phone TRUE when each combination of FirstName and LastName is unique. 5. Phone  LastName TRUE when a phone number is associated with more than one LastName. 6. Phone  FirstName TRUE when a phone number is associated with more than one FirstName. 7. Phone  (FirstName, LastName) TRUE when a phone number is associated with more than one combination of FirstName and LastName.

4. Answer to A – conti. • Is condition 7 the same as conditions 5 and 6? Why or why not? • No, it is not. • If we were dealing with regular functional dependencies, the answer would be yes, condition 7 is the same as the conditions 5 and 6. This is because of the fact that if A (B, C), then A  B and A  C. • Look at the following hypothetical situation:

5. Answer to A – conti. 123-4567 (“John”, “Linda”) [FirstName] 123-4567  (“Doe”, “Jones”) [LastName] 123-4567  (“John Doe”, “Linda Jones”) [FirstName, LastName] • The fact is that condition 7 gives us “John Doe” and “Linda Jones,” both of which are recognizable individuals with a valid combination of FirstName and LastName. But if we allow a combination of conditions 5 and 6 we also get “John Jones” and “Linda Doe,” which are nonexistent people. • Thus, condition 7 shows the equivalent of a composite identifier or composite key, and this is NOT equivalent to the random sum of its parts.

6. Question B • Consider the tables: CUSTOMER (Phone, FirstName, LastName) ORDER (OrderNumber, DateIn, DateOut, Phone) • State an appropriate referential integrity constraint. • ORDER.Phone must exist in CUSTOMER.Phone

7. Question C • Consider the tables: CUSTOMER (Phone, FirstName, LastName) ORDER (OrderNumber, DateIn, DateOut, FirstName, LastName) • Is this constraint the same as: ORDER (FirstName) must be in CUSTOMER (FirstName) and ORDER. (LastName) must be in CUSTOMER (LastName) • Explain why or why not. • No, these constraints are not equivalent.

8. Question C – conti. • Consider the tables: CUSTOMER (Phone, FirstName, LastName) ORDER (OrderNumber, DateIn, DateOut, FirstName, LastName) • What does the following referential integrity constraint mean? • ORDER (FirstName, LastName) must be in CUSTOMER (FirstName, LastName)

9. Question C – conti. ORDER (FirstName, LastName) must be in CUSTOMER (FirstName, LastName) • requires that the composite foreign key combination of the FirstName and LastName (“Joe Smith”) must exist as the composite primary key in one record in CUSTOMER, • while the constraint set ORDER (FirstName) must be in CUSTOMER (FirstName) and ORDER. (LastName) must be in CUSTOMER (LastName) • does not acknowledge the existence of the composite keys, and would be met if the FirstName (“Joe”) existed in one record in CUSTOMER and the LastName (“Smith”) existed in another, different record in CUSTOMER. Since each ORDER should be associated with one specific CUSTOMER, we should use the constraint with the composite keys: • ORDER (FirstName, LastName) must be in CUSTOMER (FirstName, LastName)

10. Question D • Do you prefer the design in B or the design in C? Explain your reasoning. • The design in B is preferable given that names are not unique. We may have more than one CUSTOMER named “Joe Smith”, but each of them should have a different phone number. If they don’t, we need to add a surrogate key (CustomerNumber or CustomerID). • At the same time, this design is limited by the fact that Phone must be unique if it is to function as the primary key. This means that we can only associate one person in a household with that phone and therefore that account. Again, adding a surrogate key (CustomerNumber or CustomerID) would solve the problem (and would be a much better design).

11. Question E • Transform the following table into two or more tables in BCNF and 4NF. Indicate the primary keys, candidate keys, foreign keys, and referential integrity constraints. Make and state assumptions as necessary. ORDER (CustomerNumber, FirstName, LastName, Phone, OrderNumber, DateIn, DateOut, ItemType, Quantity, ItemPrice, ExtendedPrice, SpecialInstructions)

12. ORDER (CustomerNumber, FirstName, LastName, Phone, OrderNumber, DateIn, DateOut, ItemType, Quantity, ItemPrice, ExtendedPrice, SpecialInstructions) • First, let’s make some assumptions (1) CustomerNumber  (FirstName, LastName), but not CustomerNumber  (FirstName, LastName) [NOTE: This anticipates question F below] (2) CustomerNumber  Phone. This means that there may be more than one phone number for each customer. (3) One CUSTOMER has many ORDERS, but each ORDER is associated with only one CUSTOMER. (4) One ORDER has many ItemTypes, but each ItemType occurs only once in any ORDER. (5) Item price does not vary from order to order, so ItemType determines ItemPrice. (6) SpecialInstructions is associated with each item type, since different instructions may be given for different items. (7) Order number is a number assigned to the orders themselves, without association with any particular customer. Thus we have such numbers as “123454”, 123455”, etc, rather than “Customer 101, Order 1”, Customer 101, Order 2”, Customer 102, Order 1”, etc. (8) ItemPrice can vary with order so that special pricing offers are reflected in the price.

13. Multivalued dependencies • Identify multiple dependencies: CustomerNumber  Phone • Break this out into its own table: ORDER_2 (CustomerNumber, FirstName, LastName, OrderNumber, DateIn, DateOut, ItemType, Quantity, ItemPrice, ExtendedPrice, SpecialInstructions) CUSTOMER_PHONE (CustomerNumber, Phone) • The foreign key will be CustomerNumber, but we still have normalization to BCNF to do, so we’ll add the referential integrity constraint for this relation at the end of the process when we have all the normalized relations.

14. Functional dependencies ORDER_2 (CustomerNumber, FirstName, LastName, OrderNumber, DateIn, DateOut, ItemType, Quantity, ItemPrice, ExtendedPrice, SpecialInstructions) • Functional dependencies in the ORDER-2 table: CustomerNumber  (FirstName, LastName) OrderNumber  (CustomerNumber, FirstName, LastName, DateIn, DateOut) (OrderNumber, ItemType)  (CustomerNumber, FirstName, LastName, DateIn, DateOut, Quantity, ItemPrice, ExtendedPrice, SpecialInstructions) (CustomerNumber, OrderNumber)  (FirstName, LastName, DateIn, DateOut)

15. Are all determinants candidate keys? • CustomerNumber, OrderNumber and (OrderNumber, ItemType) are NOT candidate keys. Therefore the relation is NOT in BCNF.

16. Introduce a table ORDER_ITEM and move Order there. ORDER_ITEM (OrderNumber, ItemType, Quantity, ItemPrice, ExtendedPrice, SpecialInstructions) ORDER_3 (CustomerNumber, FirstName, LastName, OrderNumber, DateIn, DateOut)

17. ExamineORDER_ITEM ORDER_ITEM (OrderNumber, ItemType, Quantity, ItemPrice, ExtendedPrice, SpecialInstructions) • Functional Dependencies: (OrderNumber, ItemType)  (Quantity, ItemPrice, ExtendedPrice, SpecialInstructions) (Quantity, ItemPrice)  ExtendedPrice • Candidate Keys (OrderNumber, ItemType) • Is every determinant a candidate key? • No, and the relation is not in BCNF. (OrderNumber, ItemType)  (Quantity, ItemPrice, SpecialInstructions) • Is in BCNF

18. ExamineORDER_3 ORDER_3 (CustomerNumber, FirstName, LastName, OrderNumber, DateIn, DateOut) • Functional Dependencies OrderNumber  (CustomerNumber, FirstName, LastName, DateIn, DateOut) CustomerNumber  (FirstName, LastName) • Candidate Keys • OrderNumber • Is every determinant a candidate key? • NO, CustomerNumber is NOT a candidate key. The relation is NOT in BCNF. =>Modify ORDER_3

19. Modified Relations • Newly split relations ORDER_4 (CustomerNumber, OrderNumber, DateIn, DateOut) CUSTOMER (CustomerNumber, FirstName, LastName) • And the oldie we have already proven to be in BCNF: ORDER_ITEM (OrderNumber, ItemType, Quantity, ItemPrice, ExtendedPrice, SpecialInstructions)

20. Examine ORDER_4 ORDER_4 (CustomerNumber, OrderNumber, DateIn, DateOut) • Functional Dependencies OrderNumber  (CustomerNumber, DateIn, DateOut) • Candidate Keys • OrderNumber • Is every determinant a candidate key? YES, the relation is in BCNF.

21. Examine CUSTOMER CUSTOMER (CustomerNumber, FirstName, LastName) • Functional Dependencies CustomerNumber  (FirstName, LastName) • Candidate Keys • CustomerNumber • Is every determinant a candidate key? YES, the relation is in BCNF.

22. ExamineThe Tables Holding Multivalued Dependencies CUSTOMER_PHONE (CustomerNumber, Phone) • Are the fields of the multivalued dependency the only fields in this table? • YES, and CUSTOMER_PHONE is in 4NF.

23. Check Referential Integrity Constraints • ORDER_4 (CustomerNumber, OrderNumber, DateIn, DateOut) WHERE ORDER_4.CustomerNumbermust exist in CUSTOMER.CustomerNumber • ORDER_ITEM (OrderNumber,ItemType, Quantity) WHERE ORDER_ITEM.OrderNumber must exist in ORDER_4.OrderNumber Where ORDER_ITEM.Item_Type = ITEM.ItemType • CUSTOMER (CustomerNumber, FirstName, LastName) • CUSTOMER_PHONE (CustomerNumber, Phone) WHERE CUSTOMER_PHONE.CustomerNumber must exist in CUSTOMER.CustomerNumber • ITEM (Item_Type, Item_Price, SpecialInstructions)