Part C
How do include surrgate ID culmns in part C? And how this would improve the design?
Thank you so much
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 LastName FirstName Phone Invoice Date Shire Robert 206-524-2433 14-Dec-18 Shire Robert 206-524-2433 14-Dec-18 Price 3,000.00 500.00 Tax Total 249.00 3,249.00 41.50 541.50 Goodyear Katherine 206-524-3544 15-Dec-18 1,000.00 83.00 1,083.00 Bancroft Griffith Shire Tierney Chris John Robert Doris 425-635-9788 206-524-4655 206-524-2433 425-635-8677 15-Dec-18 23-Dec-18 5-Jan-19 10-Jan-19 Invoiceltem Antique Desk Antique Desk Chair Dining Table Linens Candles Candles Desk Lamp Dining Table Linens Book Shelf Antique Chair Antique Chair Antique Candle Holders 50.00 45.00 250.00 750.00 4.15 3.74 20.75 62.25 54.15 48.74 270.75 812.25 Anderson Goodyear Goodyear Tierney Donna Katherine Katherine Doris 360-538-7566 206-524-3544 206-524-3544 425-635-8677 12-Jan-19 15-Jan-19 15-Jan-19 25-Jan-19 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 Sample Purchase Data for the Queen Anne Curiosity Shop Purchaseltem Purchase Price PurchaseDate Antique Desk 1,800.00 7-Nov-18 Antique Desk 1,750.00 7-Nov-18 Antique Candle Holders 210.00 7-Nov-18 Antique Candle Holders 200.00 7-Nov-18 Dining Table Linens 600.00 14-Nov-18 Candles 30.00 14-Nov-18 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 Phone 206-325-7866 206-325-7866 206-325-7866 206-325-7866 206-325-6755 206-325-6755 206-325-8977 206-325-8977 206-325-6755 206-325-6755 425-746-4322 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 following designs: 1. CUSTOMER (Last Name, FirstName, Phone, InvoiceDate, Invoiceltem, 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 (Last Name, FirstName, Phone, Invoice Date, InvoiceItem, Price, Tax, Total) 5. CUSTOMER (Last Name, FirstName, Phone, InvoiceDate, InvoiceItem. Price, Tax, Total) 6. CUSTOMER (Last Name, First Name, Phone) and: SALE (Invoice Date, Invoiceltem, Price, Tax, Total) 7. CUSTOMER (Last Name, FirstName, Phone, InvoiceDate) and: SALE (InvoiceDate, Invoiceltem, Price, Tax, Total) 8. CUSTOMER Last Name, FirstName, Phone) and: SALE (InvoiceDate, Invoiceltem, Price, Tax, Total, LastName, FirstName) Chapter 2 The Relational Model 133 C. Modify what you consider to be the best design in part B to include surrogate ID col- 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 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 (Purchaseltem, PurchasePrice, Purchase Date, Vendor, Phone) 2. PURCHASE (PurchaseItem, PurchasePrice, Purchase Date, Vendor, Phone) 3. PURCHASE (PurchaseItem, Purchase Price, Purchase Date, Vendor, Phone) 4. PURCHASE (PurchaseItem, Purchase Price, Purchase Date, Vendor, Phone) 5. PURCHASE (Purchaseltem, Purchase Price, Purchase Date) and: VENDOR (Vendor, Phone) 6. PURCHASE (PurchaseItem, Purchase Price, Purchase Date, Vendor) and: VENDOR (Vendor, Phone) 7. PURCHASE (PurchaseItem, Purchase Price, Purchase Date. Vendor) and: VENDOR (Vendor, Phone) F. Modify what you consider to be the best design in part Eto 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
