Question

THE QUEEN ANNE CURIOSITY SHOP PROJECT QUESTIONS Figure 2-36 shows typical sales data for the Queen Anne Curiosity Shop, and Figure 2.37 shows typical purchase data. 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 Shire Robert 206-524-2433 14-Dec-18 Shire Robert 206-524-2433 14-Dec-18 Price Tax Total 3,000.00 249.00 3,249.00 500.00 41.50 541.50 206-524-3544 15-Dec-18 1,000.00 83.00 1,083.00 Bancroft Griffith Shire Tierney Chris John Robert Doris 425-635-9788 206-524-4655 206-524-2433 425-635-8677 15-Dec-18 23-Dec-18 5-Jan-19 10-Jan-19 Invoiceltem Antique Desk Antique Desk Chair Dining Table Linens Candles Candles Desk Lamp Dining Table Linens Book Shelf Antique Chair Antique Chair Antique Candle Holders 50.00 45.00 250.00 750.00 4.15 3.74 20.75 62.25 54.15 48.74 270.75 812.25 Anderson Goodyear Goodyear Tierney Donna Katherine Katherine Doris 360-538-7566 206-524-3544 206-524-3544 425-635-8677 12-Jan-19 15-Jan-19 15-Jan-19 25-Jan-19 250.00 20.75 270.75 1,250.00 103.75 1,353.75 1.750.00 145.25 1,895.25 350.00 29.05 379.05

Answer 1

A.

Functional dependencies:

Phone -> FirstName,LastName

InvoiceItem , InvoiceDate -> Price,Tax

B.

1. Incorrect .LastName can be repeated so cannot be primary key .Item is not functionally dependent on Customer

2. Incorrect .InvoiceItem is not functionally dependent on Customer

3. Incorrect .Item is not functionally dependent on Customer

4. Incorrect . Replication of data exists

5.Incorrect .Item is not functionally dependent on Customer

6.Incorrect . No foreign key

7.Incorrect .A Customer can have many invoices

8.Correct.

C.

Customer(CustomerID , Phone ,FirstName,LastName)

Sale(SaleID , InvoiceItem , InvoiceDate , Price,Tax, CustomerID)

D.

Customer(CustomerID , Phone ,FirstName,LastName)

Sale(SaleID , CustomerID, , InvoiceDate )

SaleItem(SaleID,InvoiceItem,Price,Tax)

underlined are primary keys and italicized are foreign keys.

Do ask if any doubt. Please up-vote.