The Queen Anne Curiosity Shop sells both antiques and current-production household items that complement or are useful with the antiques. For example, the store sells antique dining room tables and new tablecloths. The antiques are purchased from both individuals and wholesalers, and the new items are purchased from distributors. The stores customers include individuals, owners of bed-and-breakfast operations, and local interior designers who work with both individuals and small businesses. The antiques are unique, although some multiple items, such as dining room chairs, may be available as a set (sets are never broken). The new items are not unique, and an item may be reordered if it is out of stock. New items are also available in various sizes and colors (for example, a particular style of tablecloth may be available in several sizes and in a variety of colors).

A. Create a sample list of sample inventory items and vendors and a second list fo customers and sales. he first list should include inventory data such as description, manufacturer and model (if available), item cost and vendor identification and contact data you think should be recorded. he second lisdt should include customer data you think would be important to The Queen Anne Curiosity Shop, along with typical sales data.

B. Describe problems that are likely to occur when inserting, updating and deleting data in these spreadsheets.

C. Atempt to combine he two lists that you created in part A into a single list. What problem occur when you try to do this?

D. Split the spreadsheets you created in part A into tables such that each has only one theme. Create appropriate ID columns.

E. Explain how the tables in your answers to part D will eliminate the problems you identified in part B.

F. What is the relationship between thre tables you created from the first spreadsheet and the tables you created from the second spreadsheet? If your set of tables does not already contain this relationship. how will you add it into your set of tables.

Figure 2-33 shows typical sales data for the Queen Anne Curiosity Shop, and Figure 2-34 shows typical purchase data.


A. (8 points) 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. (8 points) Given your assumptions in part A, comment on the appropriateness of the following designs:
1. CUSTOMER (LastName, FirstName, Phone, Email, InvoiceDate, InvoiceItem, Price, Tax, Total)
2. CUSTOMER (LastName, FirstName, Phone, Email, InvoiceDate, InvoiceItem, Price, Tax, Total)
3. CUSTOMER (LastName, FirstName, Phone, Email, InvoiceDate, InvoiceItem, Price, Tax, Total)
4. CUSTOMER (LastName, FirstName, Phone, Email, InvoiceDate, InvoiceItem, Price, Tax, Total)
5. CUSTOMER (LastName, FirstName, Phone, Email, InvoiceDate, InvoiceItem, Price, Tax, Total)
6. CUSTOMER (LastName, FirstName, Phone, Email)
and:
SALE (InvoiceDate, InvoiceItem, Price, Tax, Total)
7. CUSTOMER (LastName, FirstName, Phone, Email, InvoiceDate)
and:
SALE (InvoiceDate, InvoiceItem, Price, Tax, Total)
8. CUSTOMER (LastName, FirstName, Phone, Email, InvoiceDate, InvoiceItem)
and:
SALE (InvoiceDate, Item, Price, Tax, Total)
C. (4 points) 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. (4 points) 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. (8 points) Given your assumptions, comment on the appropriateness of the following designs:
1. PURCHASE (PurchaseItem, PurchasePrice, PurchaseDate, Vendor, Phone)
2. PURCHASE (PurchaseItem, 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 (PurchaseItem, PurchasePrice, PurchaseDate, Vendor)
and:
VENDOR (Vendor, Phone)
7. PURCHASE (PurchaseItem, PurchasePrice, PurchaseDate, Vendor)
and:
VENDOR (Vendor, Phone)
F. (4 points) 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. (4 points) 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.