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
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, Phone, Email, InvoiceNumber, InvoiceDate, PreTaxAmount)
- CUSTOMER (Name, Phone, Email, InvoiceNumber, InvoiceDate, PreTaxAmount)
- 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
PURCHASE (InvoiceNumber, InvoiceDate, PreTaxAmount, Email)
- CUSTOMER (Name, Email)
and
PURCHASE (InvoiceNumber, Phone, InvoiceDate, PreTaxAmount, Email)
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.
A. Write SQL CREATE TABLE statements for each of these tables.
Answer)
CREATE TABLE CUSTOMER (
CustomerID Integer Primary Key IDENTITY (100,10),
LastName Char(50),
FirstName Char(50) Not Null,
Phone Char(12),
Email VarChar(100)
);
CREATE TABLE PURCHASE (
InvoiceNumber Integer Primary Key,
InvoiceDate Date Not Null,
PreTaxAmount Money Not Null,
CustomerID Integer Not Null
);
CREATE TABLE PURCHASE_ITEM (
InvoiceNumber Integer Not Null,
ItemNumber Integer Not Null,
RetailPrice Money Not Null
);
ALTER TABLE PURCHASE_ITEM ADD CONSTRAINT PurchaseItemPK PRIMARY KEY (InvoiceNumber, ItemNumber);
CREATE TABLE ITEM(
ItemNumber Integer Primary Key,
Description Char(100) Not Null,
Cost Money Not Null,
ArtistName Char(100)
);
B. Write foreign key constraints for the relationships in each of these tables. Make your own assumptions regarding cascading deletions and justify those assumptions.
Answer)
ALTER table PURCHASE ADD CONSTRAINT CustomerFK FOREIGN KEY (CustomerID) REFERENCES CUSTOMER;
ALTER table PURCHASE_ITEM ADD CONSTRAINT PurchaseFK FOREIGN KEY (InvoiceNumber) REFERENCES PURCHASE ON DELETE CASCADE;
ALTER table PURCHASE_ITEM ADD CONSTRAINT ItemFK FOREIGN KEY (ItemNumber) REFERENCES PURCHASE;
Trending now
This is a popular solution!
Step by step
Solved in 2 steps
