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
DescriptionIn this final assignment, the students will demonstrate their ability to apply two ma
Path finding involves finding a path from A to B. Typically we want the path to have certain properties,such as being the shortest or to avoid going t
Develop a program to emulate a purchase transaction at a retail store. Thisprogram will have two classes, a LineItem class and a Transaction class. Th
1 Project 1 Introduction - the SeaPort Project series For this set of projects for the course, we wish to simulate some of the aspects of a number of
1 Project 2 Introduction - the SeaPort Project series For this set of projects for the course, we wish to simulate some of the aspects of a number of
| Sun | Mon | Tue | Wed | Thu | Fri | Sat |
|---|---|---|---|---|---|---|
23 | 24 | 25 | 26 | 27 | 28 | 1 |
2 | 3 | 4 | 5 | 6 | 7 | 8 |
9 | 10 | 11 | 12 | 13 | 14 | 15 |
16 | 17 | 18 | 19 | 20 | 21 | 22 |
23 | 24 | 25 | 26 | 27 | 28 | 29 |
30 | 31 | 1 | 2 | 3 | 4 | 5 |
