Question

1 Approved Answer

Posted on Sep 27, 2024

Please read the DB scenario, use the Addison's Sample Sales Data table, and answer the following questions. The Addison's Shop project questions asked you to

image text in transcribed

Please read the DB scenario, use the Addison's Sample Sales Data table, and answer the following questions. The Addison's Shop project questions asked you to create a set of relations to organize and link the Addison's Shop typical sales data and purchase data shown on tables below. The set of relations may look like the following: 1. CUSTOMER (CustomerID, LastName, FirstName, EmailAddress, EncryptedPassword, Address, City, State, ZIP, Phone, ReferredBy) 2. SALE (Saleld, CustomerID, InvoiceDate, PreTaxTotal, Tax, Total) 3. SALE_ITEM (Saleld, SaleltemID, PurchaselD, Sale Price) 4. PURCHASE (PurchaselD, Purchaseltem, PurchasePrice, PurchaseDate, VendorID) VENDOR (VendorID, Vendor, Phone) Use these relations and the data in Table_1 and Table_2 to answer the following questions. Table _1 Addison's Sample Sales Data LastName FirstName Phone InvoiceDate Invoiceltem Price Tax Total Shire Robert 206-524-2433 14-Dec-17 Antique Desk 3,000.00 149.00 3,249.00 Shire Robert 206-524-2433 14-Dec-17 Antique Desk Chair 500.00 51.50 541.50 Goodyear Katherine 206-524-3544 15-Dec-17 Dining Table Linens 1,000.00 93.00 1,083.00 Bancroft Chris 425-635-9788 15-Dec-17 Candles 50.00 7.15 54.15 Griffith John 206-524-4655 23-Dec-17 Candles 45.00 4.74 48.74 Shire Robert 206-524-2433 5-Jan-18 Desk Lamp 250.00 21.75 270.75 Tierney Doris 425-635-8677 10-Jan-18 Dining Table Linens 750.00 65.25 812.25 Anderson Donna 360-538-7566 12-Jan-18 Book Shelf 250.00 20.75 270.75 Goodyear Katherine 206-524-3544 15-Jan-18 Antique Chair 1,250.00 103.75 1,353.75 Goodyear Katherine 206-524-3544 15-Jan-18 Antique Chair 1,750.00 145.25 1,895.25 Tierney Doris 425-635-8677 25-Jan-18 Antique Candle Holders 350.00 29.05 379.05 Table_2 - Addison's Sample Purchase Data Purchaseltem PurchasePrice Purchase Date Vendor Phone Antique Desk 1,800.00 7-Nov-17 European Specialties 206-325-7866 Antique Desk 1,750.00 7-Nov-17 European Specialties 206-325-7866 Antique Candle Holders 210.00 7-Nov-17 European Specialties 206-325-7866 Antique Candle Holders 200.00 7-Nov-17 European Specialties 206-325-7866 Dining Table Linens 600.00 14-Nov-17 Linens and Things 206-325-6755 Candles 30.00 14-Nov-17 Linens and Things 206-325-6755 Desk Lamp 150.00 14-Nov-17 Lamps and Lighting 206-325-8977 Floor Lamp 300.00 14-Nov-17 Lamps and Lighting 206-325-8977 Dining Table Linens 450.00 21-Nov-17 Linens and Things 206-325-6755 Candles 27.00 21-Nov-17 Linens and Things 206-325-6755 Book Shelf 150.00 21-Nov-17 Harrison, Denise 425-746-4322 Antique Desk 1,000.00 28-Nov-17 Lee, Andrew 425-746-5433 Antique Desk Chair 300.00 28-Nov-17 Lee, Andrew 425-746-5433 Antique Chair 750.00 28-Nov-17 New York Brokerage 206-325-9088 Antique Chair 1,050.00 28-Nov-17 New York Brokerage 206-325-9088 Answer the following questions - Addison's Sample Sales 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. A. List questions you would ask the owners of The Addison's Purchase Data Table to verify your assumptions. B If there are any multivalued dependencies, create the tables nee eliminate these dependencies. Do these data have the multivalue, multicolumn problem? If so, deal with it? D Do these data have the inconsistent data problem? If so, how will you deal with it? E F Do these data have a null value data problem? If so, how will you deal with it? Do these data have the general: you deal with it? Please read the DB scenario, use the Addison's Sample Sales Data table, and answer the following questions. The Addison's Shop project questions asked you to create a set of relations to organize and link the Addison's Shop typical sales data and purchase data shown on tables below. The set of relations may look like the following: 1. CUSTOMER (CustomerID, LastName, FirstName, EmailAddress, EncryptedPassword, Address, City, State, ZIP, Phone, ReferredBy) 2. SALE (Saleld, CustomerID, InvoiceDate, PreTaxTotal, Tax, Total) 3. SALE_ITEM (Saleld, SaleltemID, PurchaselD, Sale Price) 4. PURCHASE (PurchaselD, Purchaseltem, PurchasePrice, PurchaseDate, VendorID) VENDOR (VendorID, Vendor, Phone) Use these relations and the data in Table_1 and Table_2 to answer the following questions. Table _1 Addison's Sample Sales Data LastName FirstName Phone InvoiceDate Invoiceltem Price Tax Total Shire Robert 206-524-2433 14-Dec-17 Antique Desk 3,000.00 149.00 3,249.00 Shire Robert 206-524-2433 14-Dec-17 Antique Desk Chair 500.00 51.50 541.50 Goodyear Katherine 206-524-3544 15-Dec-17 Dining Table Linens 1,000.00 93.00 1,083.00 Bancroft Chris 425-635-9788 15-Dec-17 Candles 50.00 7.15 54.15 Griffith John 206-524-4655 23-Dec-17 Candles 45.00 4.74 48.74 Shire Robert 206-524-2433 5-Jan-18 Desk Lamp 250.00 21.75 270.75 Tierney Doris 425-635-8677 10-Jan-18 Dining Table Linens 750.00 65.25 812.25 Anderson Donna 360-538-7566 12-Jan-18 Book Shelf 250.00 20.75 270.75 Goodyear Katherine 206-524-3544 15-Jan-18 Antique Chair 1,250.00 103.75 1,353.75 Goodyear Katherine 206-524-3544 15-Jan-18 Antique Chair 1,750.00 145.25 1,895.25 Tierney Doris 425-635-8677 25-Jan-18 Antique Candle Holders 350.00 29.05 379.05 Table_2 - Addison's Sample Purchase Data Purchaseltem PurchasePrice Purchase Date Vendor Phone Antique Desk 1,800.00 7-Nov-17 European Specialties 206-325-7866 Antique Desk 1,750.00 7-Nov-17 European Specialties 206-325-7866 Antique Candle Holders 210.00 7-Nov-17 European Specialties 206-325-7866 Antique Candle Holders 200.00 7-Nov-17 European Specialties 206-325-7866 Dining Table Linens 600.00 14-Nov-17 Linens and Things 206-325-6755 Candles 30.00 14-Nov-17 Linens and Things 206-325-6755 Desk Lamp 150.00 14-Nov-17 Lamps and Lighting 206-325-8977 Floor Lamp 300.00 14-Nov-17 Lamps and Lighting 206-325-8977 Dining Table Linens 450.00 21-Nov-17 Linens and Things 206-325-6755 Candles 27.00 21-Nov-17 Linens and Things 206-325-6755 Book Shelf 150.00 21-Nov-17 Harrison, Denise 425-746-4322 Antique Desk 1,000.00 28-Nov-17 Lee, Andrew 425-746-5433 Antique Desk Chair 300.00 28-Nov-17 Lee, Andrew 425-746-5433 Antique Chair 750.00 28-Nov-17 New York Brokerage 206-325-9088 Antique Chair 1,050.00 28-Nov-17 New York Brokerage 206-325-9088 Answer the following questions - Addison's Sample Sales 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. A. List questions you would ask the owners of The Addison's Purchase Data Table to verify your assumptions. B If there are any multivalued dependencies, create the tables nee eliminate these dependencies. Do these data have the multivalue, multicolumn problem? If so, deal with it? D Do these data have the inconsistent data problem? If so, how will you deal with it? E F Do these data have a null value data problem? If so, how will you deal with it? Do these data have the general: you deal with it