The Queen Anne Curiosity Shop Project Questions 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 similar the following although some additional columns have been added to the CUSTOMER relation to more closely match the Queen Anne Curiosity Shop database schema shown in the Project Questions for Chapter 2: CUSTOMER (Customer ID, LastName, FirstName, EmailAddress, EncryptedPassword, Address, City, State, ZIP, Phone, ReferredBy) SALE (SaleID, CustomerID, InvoiceDate, PreTaxTotal, Tax, Total) SALE_ITEM (SaleID, SaleItemID, PurchaseID, Sale Price) PURCHASE (PurchaseID, Purchaseltem, Purchase Price, Purchase Date, VendorID) VENDOR (VendorID, Vendor, Phone) FIGURE 3-33 Sample Sales Data for The Queen Anne Curiosity Shop LastName FirstName Phone InvoiceDate Invoiceltem Price Tax Total Shire Robert 206-524-2433 14-Dec-17 Antique Desk 3,000.00 249.00 3,249.00 Shire Robert 206-524-2433 500.00 41.50 541.50 Goodyear 14-Dec-17 Antique Desk Chair 15-Dec-17 Dining Table Linens Katherine 206-524-3544 1,000.00 83.00 1,083.00 Bancroft Chris 425-635-9788 15-Dec-17 Candles 50.00 4.15 54.15 Griffith John 206-524-4655 23-Dec-17 Candles 45.00 3.74 48.74 Shire Robert 206-524-2433 5-Jan-18 Desk Lamp 250.00 20.75 270.75 Tierney Doris 425-635-8677 10-Jan-18 Dining Table Linens 750.00 62.25 812.25 Anderson Donna 360-538-7566 12-Jan-18 Book Shelf 250.00 20.75 270.75 Goodyear Goodyear Katherine 206-524-3544 15-Jan-18 Antique Chair 1,250.00 103.75 1,353.75 Katherine 206-524-3544 15-Jan-18 Antique Chair 1,750.00 145.25 Tierney 1,895.25 Doris 425-635-8677 25-Jan-18 Antique Candle Holders 350.00 29.05 379.05 188 PART 2 Database Design Phone Purchase Price 206-325-7866 1,800.00 206-325-7866 1.750.00 206-325-7866 210.00 206-325-7866 200.00 206-325-6755 600.00 PurchaseDate Vendor 7-Nov-17 European Specialties 7-Nov-17 European Specialties 7-Nov-17 European Specialties 7-Nov-17 European Specialties 14-Nov-17 Linens and Things 14-Nov-17 Linens and Things 14-Nov-17 Lamps and Lighting 14-Nov-17 Lamps and Lighting 21-Nov-17 Linens and Things 21-Nov-17 Linens and Things 21-Nov-17 Harrison, Deniso 206-325-6755 30.00 150.00 Purchaseltem Antique Desk Antique Desk Antique Candle Holders Antique Candle Holders Dining Table Linens Candles Desk Lamp Floor Lamp Dining Table Linens Candles Book Shelt Antique Desk Antique Desk Chair Antique Chair Antique Chair 206-325-8977 300.00 206-325-8977 450.00 206-325-6755 27.00 206-325-6755 150.00 425-746-4322 1,000.00 28-Nov-17 Lee, Androw 425-746-5433 300.00 28-Nov-17 Lee, Andrew 425-746-5433 750.00 28-Nov-17 New York Brokerage 206-325-9088 1,050.00 28-Nov-17 New York Brokerage 206-325-9088 FIGURE 3-34 Sample Purchase Data for The Queen Anne Curiosity Shop 5. CUSTOMER (LastName, FirstName, Phone, EmailAddress, InvoiceDate Invoiceltem, Price, Tax, Total) 210 PART 2 Database Design Use these relations and the data in Figures 3-33 and 3-34 (which do not contain all the data for all the columns shown in this schema) to answer the following questions. A. Follow the procedure shown in Figure 4-1 to assess these data. 1. Listall 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 (missing) 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 The Queen Anne Curiosity Shop Project Questions 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 similar the following although some additional columns have been added to the CUSTOMER relation to more closely match the Queen Anne Curiosity Shop database schema shown in the Project Questions for Chapter 2: CUSTOMER (Customer ID, LastName, FirstName, EmailAddress, EncryptedPassword, Address, City, State, ZIP, Phone, ReferredBy) SALE (SaleID, CustomerID, InvoiceDate, PreTaxTotal, Tax, Total) SALE_ITEM (SaleID, SaleItemID, PurchaseID, Sale Price) PURCHASE (PurchaseID, Purchaseltem, Purchase Price, Purchase Date, VendorID) VENDOR (VendorID, Vendor, Phone) FIGURE 3-33 Sample Sales Data for The Queen Anne Curiosity Shop LastName FirstName Phone InvoiceDate Invoiceltem Price Tax Total Shire Robert 206-524-2433 14-Dec-17 Antique Desk 3,000.00 249.00 3,249.00 Shire Robert 206-524-2433 500.00 41.50 541.50 Goodyear 14-Dec-17 Antique Desk Chair 15-Dec-17 Dining Table Linens Katherine 206-524-3544 1,000.00 83.00 1,083.00 Bancroft Chris 425-635-9788 15-Dec-17 Candles 50.00 4.15 54.15 Griffith John 206-524-4655 23-Dec-17 Candles 45.00 3.74 48.74 Shire Robert 206-524-2433 5-Jan-18 Desk Lamp 250.00 20.75 270.75 Tierney Doris 425-635-8677 10-Jan-18 Dining Table Linens 750.00 62.25 812.25 Anderson Donna 360-538-7566 12-Jan-18 Book Shelf 250.00 20.75 270.75 Goodyear Goodyear Katherine 206-524-3544 15-Jan-18 Antique Chair 1,250.00 103.75 1,353.75 Katherine 206-524-3544 15-Jan-18 Antique Chair 1,750.00 145.25 Tierney 1,895.25 Doris 425-635-8677 25-Jan-18 Antique Candle Holders 350.00 29.05 379.05 188 PART 2 Database Design Phone Purchase Price 206-325-7866 1,800.00 206-325-7866 1.750.00 206-325-7866 210.00 206-325-7866 200.00 206-325-6755 600.00 PurchaseDate Vendor 7-Nov-17 European Specialties 7-Nov-17 European Specialties 7-Nov-17 European Specialties 7-Nov-17 European Specialties 14-Nov-17 Linens and Things 14-Nov-17 Linens and Things 14-Nov-17 Lamps and Lighting 14-Nov-17 Lamps and Lighting 21-Nov-17 Linens and Things 21-Nov-17 Linens and Things 21-Nov-17 Harrison, Deniso 206-325-6755 30.00 150.00 Purchaseltem Antique Desk Antique Desk Antique Candle Holders Antique Candle Holders Dining Table Linens Candles Desk Lamp Floor Lamp Dining Table Linens Candles Book Shelt Antique Desk Antique Desk Chair Antique Chair Antique Chair 206-325-8977 300.00 206-325-8977 450.00 206-325-6755 27.00 206-325-6755 150.00 425-746-4322 1,000.00 28-Nov-17 Lee, Androw 425-746-5433 300.00 28-Nov-17 Lee, Andrew 425-746-5433 750.00 28-Nov-17 New York Brokerage 206-325-9088 1,050.00 28-Nov-17 New York Brokerage 206-325-9088 FIGURE 3-34 Sample Purchase Data for The Queen Anne Curiosity Shop 5. CUSTOMER (LastName, FirstName, Phone, EmailAddress, InvoiceDate Invoiceltem, Price, Tax, Total) 210 PART 2 Database Design Use these relations and the data in Figures 3-33 and 3-34 (which do not contain all the data for all the columns shown in this schema) to answer the following questions. A. Follow the procedure shown in Figure 4-1 to assess these data. 1. Listall 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 (missing) 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