Implement the native plant cost calculation

Assignment Help Database Management System

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

Reference no: EM132287333

Questions Cloud

What is the cathode ph in co : What is the cathode pH in Co/Co2+(1.00 M)//H1+(x M), H2(1.0 atm)/Pt if E = 0.01 V? Give the pH to 0.01.

Contents take up no volume compared to the gas : Container volume is 1.09 L. How many grams of NaHCO3(s) needed to to fill this container given a 54.4% CO2 recovery

What quality does the painting venus of urbino have : What quality does the painting Venus of urbino have? Venus of Urbino by Titian is it full-round or relief? What kind of mass does it have? How does the form.

Calculating the moles of calcium carbonate : Assume the pressure has not changed. You may apply the findings regarding percent-yield in Part A to Part B when calculating the moles of calcium carbonate.

Implement the native plant cost calculation : ICT211 Database Design - Where you are asked to incorporate the exact data provided there will be a Zero (0) mark awarded if different data is incorporated

What is the theoretical yield in grams : What is the theoretical yield in grams if 120. g of Ca reacts with 21.0 g of N 2

Write the net ionic equation for reaction : When calcium carbonate reacts with hydrochloric acid, calcium ions are transferred to the solution. Write the net ionic equation for this reaction.

Detail how an electronic crime scene be processed : Detail how an electronic crime scene be processed. How can law enforcement use the locations of cell tower information in a criminal investigation?

Calculate the hardness of water in ppm fo equivalent : The 25.00mL sample of tap water required 12.82 mL of 0.0100M solution of EDTA. Calculate the hardness of water in ppm fo equivalent CaCO3

Reviews

len2287333

4/18/2019 3:53:31 AM

(30%) 5. Create SQL code to insert, search and manipulate the relational database data. Skillful and seamless The SQL script will be skillfully constructed and seamlessly insert, search and manipulate MySQL database data without error. (15%) 6. Create SQL code to demonstrate the use and understanding of procedural language in relational databases. Skillful and seamless The SQL script will be skillfully constructed and seamlessly demonstrate MySQL appropriate and correct procedures, functions and / or triggers without error.

len2287333

4/18/2019 3:53:04 AM

(20%) 3. Create relational database design schema and documentation. Systematic and skillful Accurate, clear and skillful creation of ER diagrams and relational schema. Systematic, clear and accurate supplementary design requirements and assumptions.(20%) 4. Create SQL code to create and delete relational database tables. Skillful and seamless The SQL script will be skilfully constructed and seamlessly drop and create MySQL tables along with comprehensive constraints without error.

len2287333

4/18/2019 3:52:45 AM

Criteria 10%) 1. Demonstrate an understanding of client requirements. High Distinction (85-100%) Comprehensive and insightful Client specifications are clearly and comprehensively reflected in the ER diagrams, relational schema, supplementary design requirements. Assumptions show a good depth of insight into the sometimes unclear case study business rules. (5%) 2. Create a cohesive database design that is reflected in the prototype code. Comprehensive ER diagrams, relational schema, supplementary design requirements are comprehensively reflected in the prototype code. Assumptions and business rules are meticulously reflected in the database constraints.

Write a Review

Required(*) Message

Assured A++ Grade

Get guaranteed satisfaction & time on delivery in every assignment order you paid with us! We ensure premium quality solution document along with free turntin report!

Submit Assignment

User Account

All Pages