Reference no: EM132287333

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:
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 an order report based on a date range and member nursery (you will need to have the data in the database for this query):
o List of all orders between a start date and an end date for a particular nursery,
o the report will be grouped by client,
o each line in the list will include Client name, client state, order number / id and total for that order,
o will be ordered by oldest order at the top

Attachment:- Database Design.rar