Question

1 Approved Answer

Posted on Sep 09, 2024

How do I do step A and B? thank you so much! A. Using these data, state assumptions about functional dependencies among the columns of

How do I do step A and B? thank you so much! image text in transcribed

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 InvoiceDate Invoiceltem Price Tax Total Shire Antique Desk Antique Desk Chair 3,000.00 249.00 3,249.00 Robert 206-524-2433 14-Dec-18 Shire 206-524-2433 14-Dec-18 Robert 500.00 41.50 541.50 83.00 1,083.00 Dining Table Linens 1,000.00 15-Dec-18 Goodyear 206-524-3544 Katherine 54,15 50.00 4.15 Candles 15-Dec-18 425-635-9788 Bancroft Chris 48.74 3.74 45.00 Candles 23-Dec-18 206-524-4655 Griffith John 270.75 250.00 20.75 Desk Lamp 5-Jan-19 206-524-2433 Shire Robert 812.25 750.00 62.25 Dining Table Linens 10-Jan-19 425-635-8677 Tierney Doris 270.75 20,75 250.00 Book Shelf 12-Jan-19 1,250.00 103.75 1,353.75 1,750.00 145.25 1,895.25 350.00 360-538-7566 Anderson Donna Antique Chair 15-Jan-19 Goodyear Katherine Goodyear Katherine Tierney 206-524-3544 Antique Chair Antique Candle Holders 15-Jan-19 379.05 206-524-3544 29.05 25-Jan-19 425-635-8677 Doris Sample Purchase Data for the Queen Anne Curiosity Shop Phone Vendor PurchasePrice PurchaseDate 1,800.00 206-325-7866 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 Purchaseltem 7-Nov-18 Antique Desk Antique Desk Antique Candle Holders Antique Candle Holders Dining Table Linens 206-325-7866 7-Nov-18 1,750.00 206-325-7866 7-Nov-18 210.00 206-325-7866 7-Nov-18 200.00 206-325-6755 14-Nov-18 600.00 206-325-6755 14-Nov-18 30.00 Candles 206-325-8977 14-Nov-18 Desk Lamp 150.00 206-325-8977 Floor Lamp 14-Nov-18 300.00 206-325-6755 Dining Table Linens Candles 21-Nov-18 450.00 206-325-6755 21-Nov-18 27.00 425-746-4322 Harrison, Denise Book Shelf 21-Nov-18 150.00 Antique Desk Antique Desk Chair Antique Chair Antique Chair 425-746-5433 Lee, Andrew 1,000.00 28-Nov-18 425-746-5433 300.00 28-Nov-18 Lee, Andrew New York Brokerage 206-325-9088 750.00 28-Nov-18 1,050.00 28-Nov-18 New York Brokerage 206-325-9088 B. Given your assumptions in part A, comment on the appropriateness of the following designs: 1. CUSTOMER (LastName, FirstName, Phone, InvoiceDate, Invoiceltem, Price, Tax, Total) 2. CUSTOMER (LastName, FirstName, Phone, InvoiceDate, Invoiceltem, Price, Tax, Total) 3. CUSTOMER (LastName, FirstName, Phone, InvoiceDate, Invoiceltem, Price, Tax, Total) 4. CUSTOMER (LastName, FirstName, Phone, InvoiceDate, Invoiceltem, Price, Tax, Total) 5. CUSTOMER (LastName, FirstName, Phone, InvoiceDate, Invoiceltem Price, Tax, Total) 6. CUSTOMER (LastName, EirstName, Phone) and: SALE (InvoiceDate, Invoiceltem, Price, Tax, Total) 7. CUSTOMER (LasName, FirstName, Phope, InpoiceDate) and: SALE (InvoiceDate, Invoiceltem, Price, Tax, Total) 8. CUSTOMER (LasName, FirsName, Phone) and: SALE (InvoiceDate, Invoiceltem, Price, Tax, Total, LastName, FirstName) C. Modify what you consider to be the best design in part B to include surrogate ID col- umns called CustomerID and SalelD. 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 (PurchaseItem, PurchasePrice, PurchaseDate, Vendor, Phone) 2. PURCHASE (Purchaseltem, PurchasePrice, PurchaseDate, Vendor, Phone) 3. PURCHASE (PurchaseItem, PurchasePrice, PurchaseDate, Vendor, Phone) 4. PURCHASE (PurchaseItem, PurchasePrice, PurchaseDate, Vendor, Phone) 5. PURCHASE (PurchaseItem, PurchasePrice, PurchaseDate) and: VENDOR (Vendor, Phone) 6. PURCHASE (Purchaseltem, PurchasePrice, PurchaseDate, Vendor) and: VENDOR (Vendor, Phone) 7. PURCHASE (PurchaseItem, PurchasePrice, PurchaseDate, Vendor) and: VENDOR (Vendor, Phone) F. Modify what you consider to be the best design in part E to include surrogate ID col- umns called PurchaselD 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. 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 InvoiceDate Invoiceltem Price Tax Total Shire Antique Desk Antique Desk Chair 3,000.00 249.00 3,249.00 Robert 206-524-2433 14-Dec-18 Shire 206-524-2433 14-Dec-18 Robert 500.00 41.50 541.50 83.00 1,083.00 Dining Table Linens 1,000.00 15-Dec-18 Goodyear 206-524-3544 Katherine 54,15 50.00 4.15 Candles 15-Dec-18 425-635-9788 Bancroft Chris 48.74 3.74 45.00 Candles 23-Dec-18 206-524-4655 Griffith John 270.75 250.00 20.75 Desk Lamp 5-Jan-19 206-524-2433 Shire Robert 812.25 750.00 62.25 Dining Table Linens 10-Jan-19 425-635-8677 Tierney Doris 270.75 20,75 250.00 Book Shelf 12-Jan-19 1,250.00 103.75 1,353.75 1,750.00 145.25 1,895.25 350.00 360-538-7566 Anderson Donna Antique Chair 15-Jan-19 Goodyear Katherine Goodyear Katherine Tierney 206-524-3544 Antique Chair Antique Candle Holders 15-Jan-19 379.05 206-524-3544 29.05 25-Jan-19 425-635-8677 Doris Sample Purchase Data for the Queen Anne Curiosity Shop Phone Vendor PurchasePrice PurchaseDate 1,800.00 206-325-7866 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 Purchaseltem 7-Nov-18 Antique Desk Antique Desk Antique Candle Holders Antique Candle Holders Dining Table Linens 206-325-7866 7-Nov-18 1,750.00 206-325-7866 7-Nov-18 210.00 206-325-7866 7-Nov-18 200.00 206-325-6755 14-Nov-18 600.00 206-325-6755 14-Nov-18 30.00 Candles 206-325-8977 14-Nov-18 Desk Lamp 150.00 206-325-8977 Floor Lamp 14-Nov-18 300.00 206-325-6755 Dining Table Linens Candles 21-Nov-18 450.00 206-325-6755 21-Nov-18 27.00 425-746-4322 Harrison, Denise Book Shelf 21-Nov-18 150.00 Antique Desk Antique Desk Chair Antique Chair Antique Chair 425-746-5433 Lee, Andrew 1,000.00 28-Nov-18 425-746-5433 300.00 28-Nov-18 Lee, Andrew New York Brokerage 206-325-9088 750.00 28-Nov-18 1,050.00 28-Nov-18 New York Brokerage 206-325-9088 B. Given your assumptions in part A, comment on the appropriateness of the following designs: 1. CUSTOMER (LastName, FirstName, Phone, InvoiceDate, Invoiceltem, Price, Tax, Total) 2. CUSTOMER (LastName, FirstName, Phone, InvoiceDate, Invoiceltem, Price, Tax, Total) 3. CUSTOMER (LastName, FirstName, Phone, InvoiceDate, Invoiceltem, Price, Tax, Total) 4. CUSTOMER (LastName, FirstName, Phone, InvoiceDate, Invoiceltem, Price, Tax, Total) 5. CUSTOMER (LastName, FirstName, Phone, InvoiceDate, Invoiceltem Price, Tax, Total) 6. CUSTOMER (LastName, EirstName, Phone) and: SALE (InvoiceDate, Invoiceltem, Price, Tax, Total) 7. CUSTOMER (LasName, FirstName, Phope, InpoiceDate) and: SALE (InvoiceDate, Invoiceltem, Price, Tax, Total) 8. CUSTOMER (LasName, FirsName, Phone) and: SALE (InvoiceDate, Invoiceltem, Price, Tax, Total, LastName, FirstName) C. Modify what you consider to be the best design in part B to include surrogate ID col- umns called CustomerID and SalelD. 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 (PurchaseItem, PurchasePrice, PurchaseDate, Vendor, Phone) 2. PURCHASE (Purchaseltem, PurchasePrice, PurchaseDate, Vendor, Phone) 3. PURCHASE (PurchaseItem, PurchasePrice, PurchaseDate, Vendor, Phone) 4. PURCHASE (PurchaseItem, PurchasePrice, PurchaseDate, Vendor, Phone) 5. PURCHASE (PurchaseItem, PurchasePrice, PurchaseDate) and: VENDOR (Vendor, Phone) 6. PURCHASE (Purchaseltem, PurchasePrice, PurchaseDate, Vendor) and: VENDOR (Vendor, Phone) 7. PURCHASE (PurchaseItem, PurchasePrice, PurchaseDate, Vendor) and: VENDOR (Vendor, Phone) F. Modify what you consider to be the best design in part E to include surrogate ID col- umns called PurchaselD 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