logo Use SA10RAM to get 10%* Discount.
Order Now logo

Ask This Question To Be Solved By Our ExpertsGet A+ Grade Solution Guaranteed

expert
Arthur AskeyData mining
(5/5)

754 Answers

Hire Me
expert
Penn RitchieSociology
(5/5)

832 Answers

Hire Me
expert
Robert DLaw
(4.8/5)

715 Answers

Hire Me
expert
Neill BissonnetteData mining
(5/5)

700 Answers

Hire Me
Others

Emily enlisted the help of a local web developer Liam to build a platform for Australian Event Planning Co-operative (AEPC). Liam did a great job and Emily was very pleased with the new AEPC website

INSTRUCTIONS TO CANDIDATES
ANSWER ALL QUESTIONS

Case Study: The Australian Event Planning Co-operative

Background

Before having children Emily Plunkett had had two very different careers, one that she was extremely good at and one that she absolutely loved. Emily was very good at advertising and selling goods and services but her favourite occupation was organising weddings. In 2010, facing the future as a single parent she decided to see if she could combine her two careers and set up a business that could provide all the items needed to cater for small or large celebrations. Emily’s idea proved so popular that she received many requests to form a co- operative that would provide a common national web platform to promote and provide advice on organising events and offer all types of equipment and furnishings.

Emily enlisted the help of a local web developer Liam to build a platform for Australian Event Planning Co-operative (AEPC). Liam did a great job and Emily was very pleased with the new AEPC website. It was intuitive to navigate and provided plenty of information to which the co-operative members could contribute. Emily was extremely pleased when organisations from all over Australia asked to join the co-operative. However as the number of members of the co-operative grew, customers and supply organisation owners noticed data anomalies occurring and an increase in information retrieval time. Additionally order information went missing and there were some difficulties in entering new information.

Emily decided she needed to talk to Liam to see how the issues could be resolved as she

didn’t want the reputation of AEPC to be marred. Liam admitted that whilst he was good at designing websites he had less skill and experience at developing databases. As a result Emily and Liam have asked you, as a database design consultant to make recommendations for a way forward. Following several consultations and investigation of the current set-up you realise that Liam had created a flat file database and entity relationships were not logically defined. Your recommendation is to create a Customer Relationship Management (CRM) style database to which specific adaptions could be made at a later date. As an experienced database designer you are fully aware that the functionality Emily would like will not be possible to achieve in the first project. You explain the need to define a core database system which will provide a foundation for added functionality in the future.

User Requirements

The following provides what you, Emily and Liam agree would be a good core system for the first database design project.

Emily believes that the core database entities are co-op members, customers, equipment items and equipment orders. You generally agree however you also know that there will be other regular and composite entities, and business rules which will determine the entity relationships.

Emily explained that co-op members will have a unique member id, a member name, a contact name, a date when they joined the co-op and end date (in case they should leave the co-op – it will be left blank by default), the name of the business, its address, phone number, and email address and a member description where they can describe themselves and their specialty.

Liam explained that a customer will register on the AEPC website and must provide their name, email address and location. The database must also allocate a customer id and a start date. The customer may order equipment so there must be a delivery address as well.

When asked about equipment as a database entity Emily explained that each piece of equipment has an item id, an item name, a style name, a pack size and a description.

Each type of equipment may be stocked by many co-op members and each co-op member will stock many different types of equipment. The price of each item of equipment and the pack sizes are standard across the co-operative. This entity will have the AEPC co-op member id and the item id, the price, price date, unit shipping cost (described below for use in a stored function), and an ‘in stock’ attribute.

Emily explained that customers may order items from any AEPC member. The order will have an order id, customer id, AEPC 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 relates to a piece of equipment which has an item id, order id, item cost, quantity and unit shipping cost.

As pieces of equipment can be of various sizes and customers can order equipment from any AEPC member shipping costs can vary (for example, a customer in Queensland may place an order for something that can only be sourced in Victoria). To allow the customer to get a rough estimate of the total costs, including shipping, a stored database function must be created as described below. When a client submits an order through the AEPC website, the order is saved into the database and an alert message is sent to the AEPC member automatically through the messaging system.

Liam described the messaging system he had built into the AEPC website for customers to ask questions and send alerts including notifying AEPC members of new customer 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, customer id, and AEPC member id. The message entity will also include a date stamp field and a message field. This simple format will give Liam the flexibility to use it in many different ways on the website.

Important requirements

Liam 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 that it can be run (and re-run) as one sequential script without error

Required equipment/item cost calculation stored procedure / function 

Customers can order items of equipment from any AEPC member. However shipping costs for shipping the equipment within that AEPC member’s state is much cheaper than sending them interstate. Emily has a shipping cost agreement among AEPC co-op members as follows:

  • If a customer orders items of equipment from AEPC members located within the customer’s state or territory, the total price for the equipment to be delivered to the customer will be the listed price plus the unit shipping

  • For all interstate purchases, the customer will multiply the corresponding shipping multiplier (table below) by the unit shipping

Related Questions

. The fundamental operations of create, read, update, and delete (CRUD) in either Python or Java

CS 340 Milestone One Guidelines and Rubric  Overview: For this assignment, you will implement the fundamental operations of create, read, update,

. Develop a program to emulate a purchase transaction at a retail store. This  program will have two classes, a LineItem class and a Transaction class

Retail Transaction Programming Project  Project Requirements:  Develop a program to emulate a purchase transaction at a retail store. This

. The following program contains five errors. Identify the errors and fix them

7COM1028   Secure Systems Programming   Referral Coursework: Secure

. Accepts the following from a user: Item Name Item Quantity Item Price Allows the user to create a file to store the sales receipt contents

Create a GUI program that:Accepts the following from a user:Item NameItem QuantityItem PriceAllows the user to create a file to store the sales receip

. The final project will encompass developing a web service using a software stack and implementing an industry-standard interface. Regardless of whether you choose to pursue application development goals as a pure developer or as a software engineer

CS 340 Final Project Guidelines and Rubric  Overview The final project will encompass developing a web service using a software stack and impleme

Get Free Quote!

377 Experts Online