Part D

How do I modify the design in part C through breaking Sale into tow relations?

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 Sample Sales Data for the Queen Anne Curiosity Shop InvoiceDate Invoiceltem Price Tax LastName FirstName Phone Total Antique Desk Antique Desk Chair 3,000.00 249.00 3,249.00 14-Dec-18 206-524-2433 Shire Robert 14-Dec-18 500.00 41.50 541.50 206-524-2433 Shire Robert Dining Table Linens 83.00 1,083.00 15-Dec-18 1,000.00 Goodyear Katherine 206-524-3544 50.00 15-Dec-18 Candles 4.15 54.15 425-635-9788 Bancroft Chris Candles 45.00 23-Dec-18 3.74 48.74 206-524-4655 Griffith John Desk Lamp 250.00 5-Jan-19 20.75 270.75 206-524-2433 Shire Robert Dining Table Linens 750.00 10-Jan-19 62.25 812.25 425-635-8677 Tierney Doris Book Shelf 250.00 12-Jan-19 20.75 270.75 1,250.00 103.75 1,353.75 1,750.00 145.25 1,895.25 360-538-7566 Anderson Donna Antique Chair Antique Chair Antique Candle Holders 15-Jan-19 Goodyear Katherine Goodyear Tierney 206-524-3544 15-Jan-19 206-524-3544 Katherine 25-Jan-19 350.00 29.05 379.05 425-635-8677 Doris Sample Purchase Data for the Queen Anne Curiosity Shop PurchasePrice PurchaseDate Vendor Phone Purchaseltem Antique Desk Antique Desk Antique Candle Holders Antique Candle Holders Dining Table Linens European Specialties European Specialties European Specialties 206-325-7866 1,800.00 7-Nov-18 206-325-7866 1,750.00 7-Nov-18 210.00 206-325-7866 7-Nov-18 200.00 European Specialties Linens and Things 206-325-7866 7-Nov-18 600.00 206-325-6755 14-Nov-18 Candles 30.00 14-Nov-18 Linens and Things 206-325-6755 Desk Lamp Floor Lamp Dining Table Linens 150.00 Lamps and Lighting Lamps and Lighting Linens and Things 14-Nov-18 206-325-8977 300.00 14-Nov-18 206-325-8977 450.00 21-Nov-18 206-325-6755 Candles 27.00 21-Nov-18 Linens and Things 206-325-6755 Book Shelf 150.00 21-Nov-18 Harrison, Denise 425-746-4322 Antique Desk Antique Desk Chair Antique Chair Antique Chair 1,000.00 28-Nov-18 Lee, Andrew 425-746-5433 300.00 28-Nov-18 Lee, Andrew 425-746-5433 750.00 28-Nov-18 New York Brokerage New York Brokerage 206-325-9088 1,050.00 28-Nov-18 206-325-9088 B. Given your assumptions in part A, comment on the appropriateness of the following designs: 1. CUSTOMER (LastName, FirstName, Phone, InvoiceDate, InvoiceItem, Price, Tax, Total) 2. CUSTOMER (LastName, FirstName, Phone, InvoiceDate, InvoiceItem, Price, Tax, Total) 3. CUSTOMER (LastName, FirstName, Phone, InvoiceDate, InvoiceItem, Price, Tax, Total) 4. CUSTOMER (LastName, FirstName, Phone, InvoiceDate, InvoiceItem, Price, Tax, Total) 5. CUSTOMER (LastName, FirstName, Phone, InvoiceDate, Invoiceltem, Price, Tax, Total) 6. CUSTOMER (LastName, EirstName, Phone) and: SALE (InvoiceDate, InvoiceItem, Price, Tax, Total) 7. CUSTOMER (LastName, FirstName, Phone, InvoiceDate) and: SALE (InvoiceDate, InvoiceItem, Price, Tax, Total) 8. CUSTOMER (LastName, FirstName, Phone) and: SALE (InvoiceDate, Invoiceltem, Price, Tax, Total, LastName, FirstName) The Relational Model Chapter 2 133 C. Modify what you consider to be the best design in part B to include surrogate ID col- umns called CustomerID and SalelID. How does this improve the design? D. Modify the design in part C by breaking SALE into two relations named SALE and SALE_ITEM. Modify columns and add additional columns as you think necessary. How does this improve the design? E. Given your assumptions, comment on the appropriateness of the following designs: 1. PURCHASE (PurchaseItem, PurchasePrice, PurchaseDate, Vendor, Phone) 2. PURCHASE (Purchaseltem, PurchasePrice, PurchaseDate, Vendor, Phone) 3. PURCHASE (PurchaseItem, PurchasePrice, PurchaseDate, Vendor, Phone) 4. PURCHASE (Purchaseltem, PurchasePrice, PurchaseDate, Vendor, Phone) 5. PURCHASE (PurchaseItem, PurchasePrice, PurchaseDate) and: VENDOR (Vendor, Phone) 6. PURCHASE (PurchaseItem, PurchasePrice, PurchaseDate, Vendor) and: VENDOR (Vendor, Phone) 7. PURCHASE (Purchaseltem, PurchasePrice, PurchaseDate, 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 PurchaselD 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