I can send the SQL query for JRJ schema.

Hello, I need help on the following. I can send the SQL query for JRJ schema.

Objectives

·     Create an E-R diagram of the JRJ application database using the MySQL workbench reverse engineering tool

·     Reverse engineering refers to taking an existing database and creating an E-R diagram

·     Modify the E-R diagram for new application requirements

·     Forward engineering is creating SQL DDL (create table statements) from an E-R diagram.

·     Create and execute SQL create table statements for a new design

JRJ stands for James River Jewelry. It is a fictitious jewelry store. Items for sale are listed in the Item table. Profile information for customers is in the Customer table. When customers make a purchase, a row in the Purchase describes the overall purchase (the date of transaction, total amount). The items purchased are recorded in the Purchase_Item table and include the price paid for this item.

Part 2: Modify JRJ diagram for new requirements

The Jewelry store wants to expand its database application to include

·     tracking of artists and styles

·     which customers are interested in artists and styles. 

·     The store wants to establish an award program. 

·     When a customer purchases 10 pieces of jewelry, the 11th purchase will receive a discount equal to 50% of the average of the 10 prior purchases.

·     The store also wants to track how often and which pieces of jewelry are purchased using this award program.

There will new entities for Style and Artist. Customers can register their interest in particular styles and artists and so there are relationships between Customer entity and Style entity and between Customer and Artist entities.

Modify the E-R diagram for these new entities and relationships.

To implement the awards the customer entity will need attributes to track the number of purchases and the total of these purchases. When the number of purchases reaches 10, the next purchase will receive a discount equal to 50% of the average purchase with is 0.50*(total/10). And the number of purchases and total will be reset to 0.

Modify the E-R diagram for these new attributes for the Customer entity. 

Modify the Purchase_Item entity to include a new field for AwardDiscount which is the discount (if any) given for this purchase.