Part G
How to find the relations in the designs from Part D and part F??
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 Tax Total 3,000.00 249.00 3,249.00 500.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 Donna Goodyear Katherine Goodyear Katherine Tierney Doris 360-538-7566 206-524-3544 1006-524-3544 206-524-3544 425-635-8677 35-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 Phone 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 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, First Name, 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, InvoiceItem, 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, Last Name, 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, PurchasePrice, 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) 7. PURCEL 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. E. Given your assumptions, comment on the appropriateness of the following designs: 1. PURCHASE (PurchaseItem, Purchase Price, 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 (PurchaseItem, PurchasePrice, Purchase Date) and: VENDOR (Vendor, Phone) 6. PURCHASE (Purchaseltem. 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 E to include surrogate ID col. umos called PurchaseID and VendorID. How does this improve the design? G. The relations in your design from part D and part I are not connected. Modify the database design so that sales data and purchase data are related
