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. 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: CUSTOMER (Name, Email) and PURCHASE (InvoiceNumber, Phone, InvoiceDate, PreTaxAmount, Email)
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.
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:
- CUSTOMER (Name, Email)
and
PURCHASE (InvoiceNumber, Phone, InvoiceDate, PreTaxAmount, Email)
CUSTOMER (LastName, FirstName, Phone, EmailAddress, InvoiceDate, InvoiceItem,Price, Tax, Total)
Since LastName here is the primary key, it is violating the very own definition of the primary key. Because there are two people with the last name Robert and this column is not uniquely defining the tuples in the table.
CUSTOMER (LastName, FirstName, Phone, EmailAddress, InoviceDate, InvoiceItem, Price, Tax, Total)
Same applies here as well since there are two tuples in the table where first name and the last name are the same Shire Robert.
(LastName, FirstName) is not uniquely identifying the table, hence it cannot be our primary key.
Trending now
This is a popular solution!
Step by step
Solved in 2 steps
