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. Figure D-1 shows data that James River Jewelry collects for its frequent buyer program. Name Ekzabeth Stanley Phone 556-236-7789 Fred Price 555-236-0001 Linda Becky 555-236-0392 Pamela Birch 555-236-4493 Email Elizabeth Stanley@somewhere.com Fred Price@somewhere.com Linda Becky somewhere.com Pamela Birch InvoiceNumber Date Pre TaxAmount 1000 5/5/2012 $ 155.00 1010 5/7/2012 S 203.00 1020 5/11/2012 $ 75.00 here com 1030 5/16/2012 $ 67.00 Richardo Romez 555-236-3334 Richard Romez mewhere com 1040 5/16/2012 $ 330 00 Ekzabeth Stanley 555-236-7789 Elizabeth Stan here com 1050 5/16/2012 $ 25.00 Linda Becky 555-236-0392 Linda Becky where.com 1060 5/16/2012 $ 45.00 Ekzabeth Stanley 555-238-7789 Elizabeth Stan somewhere com 1070 5/18/2012 S 445.00 Samantha Jackson 555-236-1095 Samantha Jackson somewhere.com 1080 5/19/2012 S 72.00 Figure D-1: Sample Data for James River Jewelry Figure D-1: Sample Data for James River Jewelry A. B. Using these data, state assumptions about functional dependencies among the columns of data. Justify your assumptions on the basis of these sample data an also on the basis of what you know about retail sales. Given your assumptions in part A, comment on the appropriateness of the following designs: 1. 2. CUSTOMER (Name, Phone, Email, InvoiceNumber, InvoiceDate, PreTaxAmount) CUSTOMER (Name, Phone, Email, InvoiceNumber. InvoiceDate. PreTaxAmount) 3 3 45 6 CUSTOMER (Name, Phone, Email, InvoiceNumber, InvoiceDate. PreTaxAmount CUSTOMER (CustomerID, Name, Phone, Email, InvoiceNumber InvoiceDate. PreTaxAmount) CUSTOMER (Name, Phone, Email) and PURCHASE (InvoiceNumber. InvoiceDate. PreTaxAmount) CUSTOMER (Name, Phone, Email) and C. D. 6. CUSTOMER (Name, Phone, Email) and PURCHASE (InvoiceNumber, InvoiceDate. PreTaxAmount, Email) 7. CUSTOMER (Name, Email) and PURCHASE (InvoiceNumber, Phone, InvoiceDate PreTaxAmount, Email) 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 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