3. Comment on following designs (whether the primary keys are appropriate). Hint: The answer may not be simply yes or no. (10 points) 1 (a) PURCHASE(ItemName, PurchasePrice, PurchaseDate, VendorName, VendorPhone, VendorAddress) (b) PURCHASE(ItemName, PurchasePrice, PurchaseDate, VendorName, VendorPhone, VendorAddress) (c) PURCHASE(ItemName, PurchasePrice, PurchaseDate, VendorName, VendorPhone, VendorAddress) (d) PURCHASE(ItemName, PurchasePrice, PurchaseDate, VendorName, VendorPhone, VendorAddress)

4. Add ID columns called VendorID and PurchaseID into the PURCHASE table. Answer following questions. (40 points) (a) List one candidate key in this relation. (b) List at least two functional dependencies. What is the determinant in each functional dependency? (c) Is the PURCHASE relation well-formed? Why? (d) If the relation is not well-formed, normalize it to a well-formed relation. In the well-formed relation, please indicate the primary key and foreign key.

5. You may realize that items in SALE and PURCHASE tables are not connected. For example, for the lamp sold to customer Anderson, we did not keep the information of where this lamp was purchased. Add an additional ITEM table to remedy this problem. How would you design ITEM table and how would you modify the ables you designed in Question 2d and 4d accordingly? Discuss how you keep track of the items in both SALE and PURCHASE in this design. (Bonus: 5 points)

Problem 1 Assuming we have the following SALE Table and PURCHASE Table. Table 1: SALE CustomerName | CustomerPhone | ItemName SalePrice | SaleDate Anderson 425-125-8461 533 Main street, Chicago, IL, 62803 Antique Desk3000 14-Dec-12 Anderson 425-125-8461 533 Main street, Chicago, IL, 62803Lamp 200 17-Dec-12 Judy231-234-1232 112 University Ave., State College, PA, 16802Chair1000 2-Dec-12 Judy 231-234-1232 112 University Ave., State College, PA, 16802| Coffee Table1502-Dec-12 David131-122-9987 17 Green Street, Seattle, WA, 10592Book Shelf500 20-Dec-12 CustomerAddress Table 2: PURCHASE ItemName PurchasePrice | PurchaseDateVendorNameVendorPhone | Coffee Table10020-Nov-12 |European Specialties |206-329-1920| 18 Union St., San Francisco, CA, 28192 Antique Desk|2500 14-Nov-12 European Specialties |206-329-1920 |18 Union St., San Francisco, CA, 28192 Crystal lights |9002-Nov-12|Lamps and Lights 231-129-1232 |210 Broadway, Dallas, TX, 23212 VendorAddress Lamp 170 |1-Nov-12 |Lamps and Lights 231-129-1232|210 Broadway, Dallas, TX, 23212 Book shelf 320 24-Oct-12A Antique Things 321-329-1203 | 190 Alley St., Miami, 96802 Chair700 13-Nov-12 |European Specialties | 206-329-1920| 18 Union St., San Francisco, CA, 28192 1. Comment on following designs (whether the primary keys are appropriate). Hint: The answers may not be simply yes or no (10 points) (a) SALE (CustomerName, CustomerPhone, CustomerAddress, ItemName, SalePrice, SaleDate) (b) SALE (CustomerName, CustomerPhone, CustomerAddress, ItemName, SalePrice, SaleDate) (c) SALE (CustomerName, CustomerPhone, CustomerAddress, ItemName, SalePrice, SaleDate) (d) SALE (CustomerName, CustomerPhone, CustomerAddress, ItemName, SalePrice, SaleDate) 2. Add ID columns called CustomerID and SaleID into the SALE table. Answer the following questions (40 points) (a) List one candidate key in this relation (b) List at least two functional dependencies. What is the determinant in each functional dependency? (c) Is the SALE relation well-formed? Why? (d) If the relation is not well-formed, normalize it to a well-formed relation. In the well-formed relation, please indicate the primary key and foreign key