3. Comment on following designs (whether the primary keys are appropriate). Hint

Question

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

Explanation / Answer

1.a) CustomerName is not appropriate as candidate key/primary key should be unique for a row and this is not the case with CustomerName.
b) ItemName looks appropriate as candidate key/primary key for the current table shown as it is unique for a row. But actually it is not a good design.

c)ItemName,saledate looks appropriate as candidate key/primary for the current table as it is unique for a row.But actually it is not a good design.

d)CustomerName,Itemname looks appropriate for primary key for current table data shown as it is unique for each row.

2. a)CustomerID, SaleID can be a one candidate key.

    b) two functional dependencies are
       CustomerID determines name, phone and address
       SaleID determines saledate, item, price
    c)Sale realtion is not well formed as it is not properly normalized.It can be broken down to following tables:
       (CustomerID, Name, address)
       (SaleID, CustomerID,item, price, saledate)

3).Primary keys are not mentioned in this part

4) a)Vendor ID and Purchase Id can be one candidate key
   b) Functional dependencies:
        vendor id determines name, phone and address
        Purchase ID determines item , price and purchase date
   c) Not well formed
      Should be broken down to following:
      (Vendor id, name, phone,address)
       (Purchaseid,Vendorid,item,price,date) (primary key- Purchase id, Foreihn key - Vendorid)

5)   The item table can be:
      (Itemname,saleid, putrchaseid)

Related Questions

Navigate

• Browse (All)
• Browse 3
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.