LastName FirstName Phone InvoiceDate Invoiceltem Shire Robert 206-524-2433 14-Dec-15 Antique Desk Price 3,000.00 500.00 Tax 249.00 41.50 Total 3,249.00 541.50 Shire Robert 206-524-2433 14-Dec-15 15-Dec-15 Antique Desk Chair Dining Table Linens Goodyear Katherine 83.00 1,083.00 206-524-3544 425-635-9788 Bancroft Chris 15-Dec-15 Candles 50.00 4.15 54.15 Griffith John 206-524-4655 23-Dec-15 Candles 45.00 3.74 48.74 Shire Robert 206-524-2433 20.75 270.75 5-Jan-16 10-Jan-16 Desk Lamp Dining Table Linens Tierney Doris 750.00 62.25 812.25 425-635-8677 360-538-7566 Anderson Donna 12-Jan-16 Book Shelf 250.00 20.75 270.75 Goodyear Katherine 15-Jan-16 Antique Chair Goodyear Katherine 206-524-3544 206-524-3544 425-635-8677 15-Jan-16 Antique Chair 1,250.00 1,750.00 350.00 103.75 145.25 29.05 1,353.75 1,895.25 379.05 Tierney Doris 25-Jan-16 Antique Candle Holders Purchaseltem PurchasePrice Purchase Date Vendor Phone Antique Desk 1,800.00 206-325-7866 7-Nov-15 7-Nov-15 European Specialties European Specialties Antique Desk 1,750.00 206-325-7866 Antique Candle Holders 210.00 7-Nov-15 European Specialties 206-325-7866 Antique Candle Holders 200.00 206-325-7866 Dining Table Linens 600.00 7-Nov-15 European Specialties 14-Nov-15 Linens and Things 14-Nov-15 Linens and Things 206-325-6755 Candles 30.00 206-325-6755 Desk Lamp 150.00 14-Nov-15 Lamps and Lighting 206-325-8977 Floor Lamp 300.00 206-325-8977 Dining Table Linens 450.00 14-Nov-15 Lamps and Lighting 21-Nov-15 Linens and Things 21-Nov-15 Linens and Things Things 206-325-6755 Candles 27.00 206-325-6755 Book Shelf 150.00 21-Nov-15 Harrison, Denise 425-746-4322 Antique Desk 1,000.00 425-746-5433 Antique Desk Chair 300.00 425-746-5433 28-Nov-15 Lee, Andrew 28-Nov-15 Lee, Andrew 28-Nov-15 New York Brokerage 28-Nov-15 New York Brokerage 750.00 206-325-9088 Antique Chair Antique Chair 1,050.00 206-325-9088 The Queen Anne Curiosity Shop The Queen Anne Curiosity Shop project questions in Chapter 3 asked you to create a set of relations to organize and link the Queen Anne Curiosity Shop typical sales data shown in Figure 3-33 and the typical purchase data shown in Figure 3-34. The set of relations may look like the following: CUSTOMER (CustomerID, LastName, FirstName, Phone, Email) SALE (SaleID, Customer ID, InvoiceDate, Pre Tax Total, Tax, Total) SALE_ITEM (SaleID, SaleItemID, PurchaseID, Sale Price) PURCHASE (PurchaseID, PurchaseItem, Purchase Price, Purchase Date, VendorID) VENDOR (VendorID, Vendor, Phone) Use these relations and the data in Figures 3-33 and 3-34 to answer the following questions. A. Follow the procedure shown in Figure 4.1 to assess these data. 1. List all functional dependencies. 2. List any multivalued dependencies. 3. List all candidate keys. 4. List all primary keys 5. List all foreign keys. 6. State any assumptions you make as you list these components. B. List questions you would ask the owners of the Queen Anne Curiosity Shop to verify your assumptions. c. If there are any multivalued dependencies, create the tables needed to eliminate these dependencies. D. Do these data have the multivalue, multicolumn problem? If so, how will you deal with it? E. Do these data have the inconsistent data problem? If so, how will you deal with it? F. Do these data have a null value data problem? If so, how will you deal with it? G. Do these data have the general-purpose remarks problem? If so, how will you deal with it? + LastName FirstName Phone InvoiceDate Invoiceltem Shire Robert 206-524-2433 14-Dec-15 Antique Desk Price 3,000.00 500.00 Tax 249.00 41.50 Total 3,249.00 541.50 Shire Robert 206-524-2433 14-Dec-15 15-Dec-15 Antique Desk Chair Dining Table Linens Goodyear Katherine 83.00 1,083.00 206-524-3544 425-635-9788 Bancroft Chris 15-Dec-15 Candles 50.00 4.15 54.15 Griffith John 206-524-4655 23-Dec-15 Candles 45.00 3.74 48.74 Shire Robert 206-524-2433 20.75 270.75 5-Jan-16 10-Jan-16 Desk Lamp Dining Table Linens Tierney Doris 750.00 62.25 812.25 425-635-8677 360-538-7566 Anderson Donna 12-Jan-16 Book Shelf 250.00 20.75 270.75 Goodyear Katherine 15-Jan-16 Antique Chair Goodyear Katherine 206-524-3544 206-524-3544 425-635-8677 15-Jan-16 Antique Chair 1,250.00 1,750.00 350.00 103.75 145.25 29.05 1,353.75 1,895.25 379.05 Tierney Doris 25-Jan-16 Antique Candle Holders Purchaseltem PurchasePrice Purchase Date Vendor Phone Antique Desk 1,800.00 206-325-7866 7-Nov-15 7-Nov-15 European Specialties European Specialties Antique Desk 1,750.00 206-325-7866 Antique Candle Holders 210.00 7-Nov-15 European Specialties 206-325-7866 Antique Candle Holders 200.00 206-325-7866 Dining Table Linens 600.00 7-Nov-15 European Specialties 14-Nov-15 Linens and Things 14-Nov-15 Linens and Things 206-325-6755 Candles 30.00 206-325-6755 Desk Lamp 150.00 14-Nov-15 Lamps and Lighting 206-325-8977 Floor Lamp 300.00 206-325-8977 Dining Table Linens 450.00 14-Nov-15 Lamps and Lighting 21-Nov-15 Linens and Things 21-Nov-15 Linens and Things Things 206-325-6755 Candles 27.00 206-325-6755 Book Shelf 150.00 21-Nov-15 Harrison, Denise 425-746-4322 Antique Desk 1,000.00 425-746-5433 Antique Desk Chair 300.00 425-746-5433 28-Nov-15 Lee, Andrew 28-Nov-15 Lee, Andrew 28-Nov-15 New York Brokerage 28-Nov-15 New York Brokerage 750.00 206-325-9088 Antique Chair Antique Chair 1,050.00 206-325-9088 The Queen Anne Curiosity Shop The Queen Anne Curiosity Shop project questions in Chapter 3 asked you to create a set of relations to organize and link the Queen Anne Curiosity Shop typical sales data shown in Figure 3-33 and the typical purchase data shown in Figure 3-34. The set of relations may look like the following: CUSTOMER (CustomerID, LastName, FirstName, Phone, Email) SALE (SaleID, Customer ID, InvoiceDate, Pre Tax Total, Tax, Total) SALE_ITEM (SaleID, SaleItemID, PurchaseID, Sale Price) PURCHASE (PurchaseID, PurchaseItem, Purchase Price, Purchase Date, VendorID) VENDOR (VendorID, Vendor, Phone) Use these relations and the data in Figures 3-33 and 3-34 to answer the following questions. A. Follow the procedure shown in Figure 4.1 to assess these data. 1. List all functional dependencies. 2. List any multivalued dependencies. 3. List all candidate keys. 4. List all primary keys 5. List all foreign keys. 6. State any assumptions you make as you list these components. B. List questions you would ask the owners of the Queen Anne Curiosity Shop to verify your assumptions. c. If there are any multivalued dependencies, create the tables needed to eliminate these dependencies. D. Do these data have the multivalue, multicolumn problem? If so, how will you deal with it? E. Do these data have the inconsistent data problem? If so, how will you deal with it? F. Do these data have a null value data problem? If so, how will you deal with it? G. Do these data have the general-purpose remarks problem? If so, how will you deal with it? +