James River Jewelry is a small jewelry shop. While James River Jewelry does sell typical jewelry purchased form jewelry vendors, including such items as rings, necklaces, earrings, and watches, it specializes in hard-to-find Asian jewelry. Although some Asian jewelry is manufactured jewelry purchased from vendors in the same manner as the standard jewelry is obtained, many of the Asian jewelry pieces are often unique single items purchased directly from the artisan who created the piece (the term “manufactured” would be an inappropriate description of these pieces). It has a small but loyal clientele, and it wants to further increase customer loyalty by creating a frequent buyer program. In this program, after every 10 purchases, a customer will receive a credit equal to 50 percent of the sum of his or her 10 most recent purchases. This credit must be applied to the next (or “11th”) purchase.

A. Create a sample list of customers and purchases and a second list of customers and credits. Your lists should include customer data you think would be important to James River along with typical purchase data. Credit data should include the date of the credit, the total amount of the 10 purchases used as the basis of the credit, and the credit amount.

B. Describe modification problems that are likely to occur if James River attempts to maintain the lists in a spreadsheet.

C. Split the lists into tables such that each has only a single theme. Create appropriate ID columns. Use one ID to represent the relationship between a purchase and a customer and use another ID to represent the relationship between a credit and a customer.

D. Attempt to combine the two lists you created in part A into a single list. What problems occur as you try to do this? Look closely at Figure 1-31. An essential difference exists between a list of the three themes customer, purchase, and credit and a list of the three themes PetName, Owner, and Service in Figure 1-31. What do you think this difference is?

E. Change the tables from part C so that the purchase list has not only the ID of Customer but also the ID of Credit. Compare this arrangement to the tables in your answer to question 1.12. What is the essential difference between these two designs?