Reference no: EM132315624

Case Study: Australian Native Plants Ltd - Online CRM Database

Background

Important requirements

Josh would like to use the sample code that you create. For this reason you MUST:

• Incorporate MySQL database and its corresponding SQL and procedural language,
• Sequence your script so Josh can run (and re-run) it as one sequential script without error

Required native plant cost calculation stored procedure / function

Clients can purchase native plants from any ANP member. However shipping costs for shipping plants within that ANP member's state is much cheaper than sending them interstate. Anna has a shipping cost agreement among ANP coop members as follows:

• If a client purchases plants from ANP members located within the client's state or territory, the total price for the plant delivered to the client will be the listed price plus the unit shipping cost.
• for all interstate purchases the client will multiply the corresponding shipping multiplier (table below) by the listed price.

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:
o information on length of identifiers, postcodes, names,
o 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:
o Automatically insert a message to the Message table when a client order is placed,
• CREATE FUCNTION / PROCEDURE
o implement the native plant cost calculation as a function or stored procedure.
• INSERT INTO statements for populating the database:
o Incorporate the exact 6 nursery ANP member names given in the dataset (make up email addresses and phone numbers)
o Incorporate the exact 11 plants and their names given in the dataset
o 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.)
o Create your own member plant pricing for plants and their shipping costs (at least 3 plants for 3 members)
o Create at least 3 client entries
o Create at least 3 client orders
o 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):
o 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):
o List of all orders with a particular order status
o the report will be grouped by member nursery,
o each line will list the participating nursery name, nursery state, total number of outstanding orders, and the total value of those orders

Attachment:- Database Design.rar