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.

From the sample sales data it would appear:

Example: LastName (FirstName, Phone)

From the sample purchase data it would appear:

Example: (InvoiceItem, PurchasePrice) (PurchaseDate, Vendor, Phone)

B.Given your assumptions you made in part A, comment on the appropriateness of the following designs:

CUSTOMER (LastName, FirstName, Phone, Email, InvoiceDate, InvoiceItem, Price, Tax, Total)

_____GOOD ____NOT GOOD Why: _______________________

CUSTOMER (LastName, FirstName, Phone, Email, InvoiceDate, InvoiceItem, Price, Tax, Total)

_____GOOD ____NOT GOOD Why: _______________________

CUSTOMER (LastName, FirstName, Phone, Email, InvoiceDate, InvoiceItem, Price, Tax, Total)

_____GOOD ____NOT GOOD Why: _______________________

CUSTOMER (LastName, FirstName, Phone, Email, InvoiceDate, InvoiceItem, Price, Tax, Total)

_____GOOD ____NOT GOOD Why: _______________________

CUSTOMER (LastName, FirstName, Phone, Email, InvoiceDate, InvoiceItem, Price, Tax, Total)

_____GOOD ____NOT GOOD Why: _______________________

CUSTOMER (LastName, FirstName, Phone, Email)

and:

SALE (InvoiceDate, InvoiceItem, Price, Tax, Total)

_____GOOD ____NOT GOOD Why: _______________________

CUSTOMER (LastName, FirstName, Phone, Email, InvoiceDate)

and:

SALE (InvoiceDate, InvoiceItem, Price, Tax, Total)

_____GOOD ____NOT GOOD Why: _______________________

CUSTOMER (LastName, FirstName, Phone, Email, InvoiceDate, InvoiceItem)

and:

SALE (InvoiceDate, Item, Price, Tax, Total)

_____GOOD ____NOT GOOD Why: _______________________

C.Modify what you consider to be the best design in part B to include surrogate ID columns called CustomerID 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:

PURCHASE (PurchaseItem, PurchasePrice, PurchaseDate, Vendor, Phone)

_____GOOD ____NOT GOOD Why: _______________________

PURCHASE (PurchaseItem, PurchasePrice, PurchaseDate, Vendor, Phone)

_____GOOD ____NOT GOOD Why: _______________________

PURCHASE (PurchaseItem, PurchasePrice, PurchaseDate, Vendor, Phone)

_____GOOD ____NOT GOOD Why: _______________________

PURCHASE (PurchaseItem, PurchasePrice, PurchaseDate, Vendor, Phone)

_____GOOD ____NOT GOOD Why: _______________________

PURCHASE (PurchaseItem, PurchasePrice, PurchaseDate)

and:

VENDOR (Vendor, Phone)

_____GOOD ____NOT GOOD Why: _______________________

PURCHASE (PurchaseItem, PurchasePrice, PurchaseDate, Vendor)

and:

VENDOR (Vendor, Phone)

_____GOOD ____NOT GOOD Why: _______________________

PURCHASE (PurchaseItem, PurchasePrice, PurchaseDate, Vendor)

and:

VENDOR (Vendor, Phone)

_____GOOD ____NOT GOOD Why: _______________________

F.Modify what you consider to be the best design in part E to include surrogate ID columns 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. (post an ERD using Lucid Diagrams and a text version of the relations and their attributes)

FIGURE 2-33 Sample Sales Data for The Queen Anne Curiosity Shop Tax Total LastName FirstName Phone Shire Shire InvoiceDate Invoiceltem ice Robert Robert 206-524-2433 14-Dec-12 Antique Desk 3,000.00 249.003,249.00 206-524-2433 14-Dec-12 Antique Desk Chair 500.00 41.50 541.50 Goodyear Katherine 206-524-3544 15-Dec-12 Dining Table Linens 1,000.00 83.00 1,083.00 50.00 4.15 54.15 45.00 3.74 48.74 250.00 20.75270.75 425-635-8677 10 Dining Table Linens 750.00 62.25 812.25 250.00 20.75 270.75 1,250.00 103.75 1,353.75 1,750.00 145.25 1,895.25 425-635-8677 25-Jan-13 Antique Candle Holders 350.00 29.05 379.05 425-635-9788 15-Dec-12 Candles 206-524-4655 23-Dec-12 Candles Bancroft Chris GriffithJohn Shire Tie Anderson D 360-538-7566 12-Jan-13 Book Shelf Robert 206-524-24335-Jan-13 Desk Lamp rneyDoris Goodyear Katherine 206-524-354415-Jan-13 Antique Chair Goodyear Katherine 206-524-3544 15-Jan-13 Antique Chair Tie riS FIGURE 2-34 Sample Purchase Data for The Queen Anne Curiosity Shop PurchasePrice 1,800.00 1,750.00 210.00 200.00 600.00 Phone Purchaseltem Antique Desk Antique Desk Antique Candle Holders Antique Candle Holders Dining Table Linens Candles Desk Lamp PurchaseDate 7-Nov-12 Vendor European Specialties 206-325-7866 European Specialties 206-325-7866 European Specialties European Specialties Linens and Things Linens and Things Lamps and Lighting206-325-8977 Lamps and Lighting206-325-8977 Linens and Things Linens and Things Harrison, I Lee, Andrew Lee, Andrew New York Brokerage 206-325-9088 New York Brokerage 206-325-9088 206-325-7866 206-325-7866 206-325-6755 206-325-6755 150.00 300.00 450.00 27.00 oor 206-325-6755 206-325-6755 425-746-4322 425-746-5433 425-746-5433 Dining Table Linens es Book Shelf Antique Desk Antique Desk Chair Antique Chair Antique Chair 1,000.00 300.00 750.00 1,050.00 28-Nov-12 28-Nov-12 28-Nov-12 28-Nov-12 FIGURE 2-33 Sample Sales Data for The Queen Anne Curiosity Shop Tax Total LastName FirstName Phone Shire Shire InvoiceDate Invoiceltem ice Robert Robert 206-524-2433 14-Dec-12 Antique Desk 3,000.00 249.003,249.00 206-524-2433 14-Dec-12 Antique Desk Chair 500.00 41.50 541.50 Goodyear Katherine 206-524-3544 15-Dec-12 Dining Table Linens 1,000.00 83.00 1,083.00 50.00 4.15 54.15 45.00 3.74 48.74 250.00 20.75270.75 425-635-8677 10 Dining Table Linens 750.00 62.25 812.25 250.00 20.75 270.75 1,250.00 103.75 1,353.75 1,750.00 145.25 1,895.25 425-635-8677 25-Jan-13 Antique Candle Holders 350.00 29.05 379.05 425-635-9788 15-Dec-12 Candles 206-524-4655 23-Dec-12 Candles Bancroft Chris GriffithJohn Shire Tie Anderson D 360-538-7566 12-Jan-13 Book Shelf Robert 206-524-24335-Jan-13 Desk Lamp rneyDoris Goodyear Katherine 206-524-354415-Jan-13 Antique Chair Goodyear Katherine 206-524-3544 15-Jan-13 Antique Chair Tie riS FIGURE 2-34 Sample Purchase Data for The Queen Anne Curiosity Shop PurchasePrice 1,800.00 1,750.00 210.00 200.00 600.00 Phone Purchaseltem Antique Desk Antique Desk Antique Candle Holders Antique Candle Holders Dining Table Linens Candles Desk Lamp PurchaseDate 7-Nov-12 Vendor European Specialties 206-325-7866 European Specialties 206-325-7866 European Specialties European Specialties Linens and Things Linens and Things Lamps and Lighting206-325-8977 Lamps and Lighting206-325-8977 Linens and Things Linens and Things Harrison, I Lee, Andrew Lee, Andrew New York Brokerage 206-325-9088 New York Brokerage 206-325-9088 206-325-7866 206-325-7866 206-325-6755 206-325-6755 150.00 300.00 450.00 27.00 oor 206-325-6755 206-325-6755 425-746-4322 425-746-5433 425-746-5433 Dining Table Linens es Book Shelf Antique Desk Antique Desk Chair Antique Chair Antique Chair 1,000.00 300.00 750.00 1,050.00 28-Nov-12 28-Nov-12 28-Nov-12 28-Nov-12