Step # 1
Step (A) How to state assumptions about functional dependencies and justify these assumptions. Thank you!
THE QUEEN ANNE CURIOSITY SHOP PROJECT QUESTIONS Figure 2-36 shows typical sales data for the Queen Anne Curiosity Shop, and Figure 2.37 shows typical purchase data. A. Using these data, state assumptions about functional dependencies among the columns of data. Justify your assumptions on the basis of these sample data and also on the basis of what you know about retail sales. FIGURE 2-36 LastName FirstName Shire Shire Price 3,000.00 500.00 Tax Total 249.00 3,249.00 41.50 541.50 1,000.00 83.00 1,083.00 Sample Sales Data for the Queen Anne Curiosity Shop Phone Invoice Date Invoiceltem Robert 206-524-2433 14-Dec-18 Antique Desk Robert 206-524-2433 14-Dec-18 Antique Desk Chair Goodyear Katherine 206-524-3544 206-52 Dining Table 15-Dec-18 Linens Chris 425-635-9788 15-Dec-18 Candles Candles John 23-Dec-18 206-524-4655 Robert 206-524-2433 Desk Lamp 5-Jan-19 10-Jan-19 Dining Table Doris 425-635-8677 Linens Book Shelf 12-Jan-19 Anderson Donna 15-Jan-19 Antique Chair Soodyear Katherine 15-Jan-19 Antique Chair Goodyear Katherine 25-Jan-19 Antique Candle Doris Holders Bancroft Griffith Shire Tierney 50.00 45.00 250.00 750.00 4.15 3.74 20.75 62.25 54.15 48.74 270.75 812.25 360-538-7566 206-524-3544 206-524-3544 425-635-8677 250.00 20.75 270.75 1,250.00 103.75 1,353.75 1,750.00 145.25 1,895.25 350.00 29.05 379.05 Tiemey Phone 206-325-7886 206-325-7866 Ities 206-325 206-325-76 206-325-7888 206-325.70 206-325-675 206-325-6756 FIGURE 2-37 Sample Purchase Data for the Queen Anne Curiosity Shop Purchaseltem PurchasePrice Purchase Date 7-Nov-18 1,800.00 Antique Desk 7-Noy-18 1,750.00 Antique Desk Antique Candle Holders 210.00 7-Nov-18 7-Nov-18 200.00 Antique Candle Holders 14-Nov-18 600.00 Dining Table Linens 14-Nov-18 30.00 Candles Desk Lamp 150.00 14-Nov-18 Floor Lamp 300.00 14-Nov-18 Dining Table Linens 450.00 21-Nov-18 Candles 27.00 21-Nov-18 Book Shelf 150.00 21-Nov-18 Antique Desk 1,000.00 28-Nov-18 Antique Desk Chair 300.00 28-Nov-18 Antique Chair 750.00 28-Nov-18 Antique Chair 1.050.00 28-Nov-18 Vendor European Specialties European Specialties European Specialties European Specialties Linens and Things Linens and Things Lamps and Lighting Lamps and Lighting Linens and Things Linens and Things Harrison, Denise Lee, Andrew Lee, Andrew New York Brokerage New York Brokerage 206-325-8977 206-325-8977 206-325-6755 206-325-6755 425-746-4302 425-746-5433 425-746-5433 206-325-9088 206-325-9088 B. Given your assumptions in part A. comment on the appropriateness of the followine designs: 1. CUSTOMER (Last Name. FirstName, Phone, InvoiceDate, Invoiceltem, Price, Tax, Total) 2. CUSTOMER (Last Name First Name, Phone, InvoiceDate, Invoiceltem, Price, Tax, Total) 3. CUSTOMER (Last Name, FirstName, Phone, Invoice Date, Invoiceltem, Price, Tax, Total) 4. CUSTOMER (Last Name, First Name, Phone, InvoiceDate, Invoiceltem, Price, Tax, Total) 5. CUSTOMER (Last Name, FirstName, Phone, InvoiceDate, Invoiceltem, Price, Tax, Total) 6. CUSTOMER Last Name First Name, Phone) and: SALE (InvoiceDate, Invoiceltem, Price, Tax, Total) 7. CUSTOMER (Last Name. Erst Name, Phone, Invoice Date) and: SALE (InvoiceDate. Invoiceltem, Price, Tax, Total) 8. CUSTOMER (Last Name, FirstName. Phone) and: SALE (Invoice Date InvoiceItem. Price, Tax, Total, Last Name, FirstName Chapter 2 The Relational Model 133 to be the best design in part B to include surrogate ID col- C. Modify what you consider to be the best design in part B to includes umns called Customer ID and SaleID. How does this improve the design? D. Modify the design in part C by breaking SALE into two relations named SALE SALE ITEM. Modify columns and add additional columns as you think How does this improve the design? E. Given your assumptions, comment on the appropriateness of the following designs 1. PURCHASE (Purchaseltem, Purchase Price, Purchase Date, Vendor, Phone) 2. PURCHASE (Purchaseltem, Purchase Price. Purchase Date, Vendor, Phone) 3. PURCHASE (PurchaseItem, PurchasePrice, Purchase Date. Vendor, Phone) 4. PURCHASE (Purchaseltem, Purchase Price, Purchase Date, Vendor, Phone) 5. PURCHASE (Purchaseltem. PurchasePrice, Purchase Date) and: VENDOR (Vendor, Phone) 6. PURCHASE (PurchaseItem. Purchase Price. Purchase Date, Vendor) and: VENDOR (Vendor, Phone) 7. PURCHASE (Purchaseltem, Purchase Price. Purchase Date. Vendor) and: VENDOR (Vendor. Phone) F. Modify what you consider to be the best design in part E to include surrogate ID col- umns called PurchaseID and VendorID. How does this improve the design G. The relations in your design from part D and part F are not connected. Modify the database design so that sales data and purchase data are related A. Using these data, state assumptions about functional dependencies among the columns of data. Justify your assumptions on the basis of these sample data and also on the basis of what you know about retail sales
