Reference no: EM132310929

Database Design Assignment -

Case Study: Australian Native Plants Ltd - Online CRM Database

Assignment Requirements and Deliverables -

Part A - Submitted as a MS Word Document:

Entity Relationship Diagram in Crows Foot Notation.

Relational Schema - including Primary and Foreign Keys.

Supplementary Design requirements - for example but not limited to:

• information on length of identifiers, postcodes, names,
• data attribute information (compulsory, variable length / type, etc.)

Assumptions

Part B - Submitted as a single plain text file with name <studentNumber>_crm.sql, containing all your SQL implementation:

IMPORTANT NOTES:

• Josh uses a MySQL database. Your Part B MUST work on a MySQL database and be able to be demonstrated to Josh so he is able to apply your SQL implementation into the ANP website.
• Where you are asked to incorporate the exact data provided there will be a Zero (0) mark awarded if different data is incorporated.

Instructions:

CREATE TABLE statements for all tables including integrity constraints,

CREATE TRIGGER statements:

• Automatically insert a message to the Message table when a client order is placed,

CREATE FUCNTION / PROCEDURE

• Implement the native plant cost calculation as a function or stored procedure.

INSERT INTO statements for populating the database:

• Incorporate the exact 6 nursery ANP member names given in the dataset (make up email addresses and phone numbers)
• Incorporate the exact 11 plants and their names given in the dataset
• Incorporate the Shipping Multiplier table into the database as an entity / table - it is recommended to use the abbreviated state name in the database (Vic, Qld, NSW, etc.)
• Create your own member plant pricing for plants and their shipping costs (at least 3 plants for 3 members)
• Create at least 3 client entries
• Create at least 3 client orders
• Data may need to be inserted in a particular order to comply with integrity constraints,

SELECT statement/s that will produce the following data for a sample order (you will need to have the data in the database for this query):

The Client ORDER will include:

• client name and account number,
• order number / id and the total amount for the order,
• order date,

At least three order items:

• item name,
• quantity,
• price,
• shipping cost,
• item total cost incorporating the native plant cost calculation Function / Procedure (HINT a function is much easier to call in a SELECT statement)

SELECT statement that will produce order report based on an order status for all member nurseries (you will need to have the data in the database for this query):

• List of all orders with a particular order status.
• The report will be grouped by member nursery,
• Each line will list the participating nursery name, nursery state, total number of outstanding orders, and the total value of those orders.

Attachment:- Assignment File - Case Study.rar