Reference no: EM132309515

Case Study: Australian Native Plants Ltd - Online CRM Database

User Requirements

The following is what you, Josh and Anna agree would be a good core system for this first database design project:

Anna believes the core database entities are coop members, clients, plants and plant orders. You generally agree, but you also know that there will be other regular and composite entities, and business rules which will determine the entity relationships.

Anna explained that coop members will have a unique member id, member name, contact name, date of start, date of end (for when they leave the coop - it will be blank by default), name of nursery, address of nursery, phone, email, and a member description where the ANP member can describe themselves and their specialty.

Josh explained that a client will register on the ANP website and must provide their name, email address, and location. The database must also allocate a client id and start date. The client may order plants so there must be a delivery address attribute as well.

When asked about plants as a database entity Anna explained that each plant entity has a plant id, botanical name, it may have a common name, and a description.

Each plant variety may be stocked by many coop members and each coop member will stock many plants. Each coop member has their own price for each plant variety. This weak entity will have the ANP coop member id and the plant id, price, price date, unit shipping cost (described below for use in a stored function), and an ‘in stock' attribute.

Anna went on to explain that clients may order plants from any ANP member. The order will have an order id, client id, ANP member id, order date, order status, shipping date, courier name, shipping cost multiplier, and shipping reference number. Each order must include one or more order items. each order item is a plant with has a plant id, order id, plant cost, quantity, and unit shipping cost.

Because plants can be of various sizes and clients can order plants from any ANP member, shipping costs can vary (for example a client in Queensland may order a rare native plant from Western Australia). To allow the client to get a rough estimate of total costs, including shipping, a stored database function must be created as described below. When a client submits an order through the ANP website, the order is saved into the database and an alert message is sent to the ANP member automatically through the messaging system.

Josh described the messaging system he had built into the ANP website for clients to ask questions and send alerts including notifying ANP members of new client orders, etc. You decide to adjust the messaging system in the database to simplify and make it more efficient as follows: a message entity will have a unique message id, client id, and ANP member id. The message entity will also include a date stamp field and a message field. This simple format will give Josh the flexibility to use it in many different ways on the web site.

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