The James River Jewelry Project Questions are available online for Appendix D, which can be downloaded from the textbook’s Web site: . The solutions for these questions will be included in the Instructor’s Manual for each chapter]
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.
We will write a
specifically for you for only
805 certified writers online
Figure D-1 shows data that James River Jewelry collects for its frequent buyer program.
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:
C. Modify what you consider to be the best design in part B to include a column called AwardPurchaseAmount. The purpose of this column is to keep a balance of the customers’ purchases for award purposes. Assume that returns will be recorded with invoices having a negative PreTaxAmount.
D. Add a new AWARD table to your answer to part C. Assume that the new table will hold data concerning the date and amount of an award that is given after a customer has purchased 10 items. Ensure that your new table has appropriate primary and foreign keys.