Figure 3-33 shows this table:

(LastName, FirstName, Phone, EmailAddress, InvoiceDate, InvoiceItem, Price, Tax, Total)

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.

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

1. CUSTOMER(LastName, FirstName, Phone, EmailAddress, InvoiceDate, InvoiceItem, Price, Tax, Total)

2. CUSTOMER(LastName, FirstName, Phone, EmailAddress, InvoiceDate, InvoiceItem, Price, Tax, Total)

3. CUSTOMER(LastName, FirstName, Phone, EmailAddress, InvoiceDate, InvoiceItem, Price, Tax, Total)

4. CUSTOMER(LastName, FirstName, Phone, EmailAddress, InvoiceDate, InvoiceItem, Price, Tax, Total)

5. CUSTOMER(LastName, FirstName, Phone, EmailAddress, InvoiceDate, InvoiceItem, Price, Tax, Total)

6. CUSTOMER(LastName, FirstName, Phone, EmailAddress) and

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

7. CUSTOMER(LastName, FirstName, Phone, EmailAddress, InvoiceDate) and

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

8. CUSTOMER(LastName, FirstName, Phone, EmailAddress, InvoiceDate, InvoiceItem) and

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

C. Modify what you consider to be the best design in Part B to include surrogate ID column 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 columns as you think necessary. How does this improve the design?

Figure 3-34 contains this table:

(PurchaseItem, PurchasePrice, PurchaseDate, Vendor, VendorPhone)

E. Given your assumptions about the table in Figure 3-34, comment on the appropriateness of the following designs:

1. PURCHASE(PurchaseItem, PurchasePrice, PurchaseDate, Vendor, VendorPhone)

2. PURCHASE(PurchaseItem, PurchasePrice, PurchaseDate, Vendor, VendorPhone)

3. PURCHASE(PurchaseItem, PurchasePrice, PurchaseDate, Vendor, VendorPhone)

4. PURCHASE(PurchaseItem, PurchasePrice, PurchaseDate, Vendor, VendorPhone)

5. PURCHASE(PurchaseItem, PurchasePrice, PurchaseDate)

and VENDOR(Vendor, VendorPhone)

6. PURCHASE(PurchaseItem, PurchasePrice, PurchaseDate, Vendor)

and VENDOR(Vendor, VendorPhone)

7. PURCHASE(PurchaseItem, PurchasePrice, PurchaseDate, Vendor)

and VENDOR(Vendor, VendorPhone)

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.