Subject: Database System

Jewels Jewelry Store sells jewelry of all kinds has a small but loyal clientle. As a result, they want to increase customer loyalty by creating a frequent buyer program. In the program,after every 10 purchases, a customer will receive a credit equal to 50 percent of the sum of their 10 most recent purchases. This credit must be applied to the next (or 11th) purchase. Jewels Jewelry Store designs a database with the following tables.

CUSTOMER (CustomerID, LastName, FirstName, Phone, EmailAddress)

PURCHASE (InvoiceNumber, InvoiceDate, PreTaxAmount, CustomerID)

PURCHASE_ITEM (InvoiceNumber, InvoiceLineNumber, ItemNumber, RetailPrice)

ITEM (ItemNumber, ItemDescription, Cost, ArtistLastName, ArtistFirstName)

AWARD (AwardID, AwardCountNumber, AwardTotalPreTaxAmount, IsAwardComplete, IsAwardUsed)

Hint: Purchase_Item and Award are both weak entities.

Draw an E-R data model for Jewels Jewelry database schema above. Use the Crows Foot E-R model for your E-R diagrams. Justify the decisions you make regarding minimum and maximum cardinalities.

Note that foreign keys are not shown in data models; they are added in the database design phase (Chapter 5). The only exception is where the foreign key must be part of the identifier of an ID-dependent weak entity.