BISM7206代写、data留学生代做、代做Java、Python/c++程序代写

日期：2020-05-17 08:50

BISM7206 Information and Retrieval

UQ Business School

April 2020

BISM7206 Information Retrieval

and Management

Case Study - Queensland Organics:

Description and Specifications

SEMESTER 1 2020

BISM7206 Information and Retrieval

UQ Business School

April 2020

Queensland Organics Case Study: Description and Assignment Specifications 1

Purpose

This document provides the case study and assignment specificationsfor the assessment Item

2 - “Case Study: Database analysis, design and development”. After you have read the

following case study description and assignment specifications, you should refer to the

associated Database Design Case Study Formatting and Submission Requirements document.

If you have any questions after you have read both of these documents, please post your

questionsto the Discussion Board under the relevant forum ‘Assessment Item 2 - Case study’.

Background

Sarah James grew up on the Atherton Tablelands in far north Queensland on her family’s

Avocado farm. On completion of her Business degree, Sarah realised that her business

management knowledge could be applied within the Queensland horticultural industry.

Queensland horticulture is worth almost $3 billion per year and is Queensland’s second

largest primary industry. Sarah’s passion for the land and the subtropical climate led her back

to the Atherton Tablelands where she invested in an organic fruit and vegetable farm and

started trading as Atherton Organics Ltd.

Initially, Sarah’s business plan was directed at farm fresh organics for the home delivery

market but subsequently expanded to cater to the needs of the local restaurant market. There

was increasingly high demand for organic fruit and vegetables both locally and nationally.

Similarly, there was a growing number of Queensland suppliers interested in joining the coop.

Sarah decided to form a Queensland based cooperative that would provide a common

platform to promote the supply of organically grown fruit and vegetables and a central

marketplace for customers throughout Australia. The newly formed co-op was renamed to

Queensland Organics (QO).

Prior to establishing QO, Sarah’s business relied primarily on spreadsheets to keep track of

her produce, expenditure, orders and invoices. This system was proving to be inadequate for

the growing and somewhat complex needs of QO. To address these needs, Sarah enlisted the

help of a local web developer (Jack) to build a common web platform for QO. Jack did a great

job and Sarah was very pleased with the new QO website. It was intuitive to navigate and

provided helpful information for customers as well as the basic functionality that enabled

processing and recording customer purchases and co-op member supplies. The QO website

also provided a platform for its members to collaborate and share ideas online with their

community.

While Sarah was extremely pleased when organic fruit and vegetable growers from all over

Queensland asked to join QO, it was becoming apparent that the web system was not coping

BISM7206 Information and Retrieval

UQ Business School

April 2020

Queensland Organics Case Study: Description and Assignment Specifications 2

with the growth in members and customers. Various system-related issues were being

reported to Sarah including data anomalies, slow information retrieval time, missing orders

and difficulties entering new information.

Sarah decided to talk to Jack to discuss how these system issues could be resolved as they

were having a negative impact on the business operations of QO. Jack admitted that whilst

he was an experienced web developer, he had less skill and experience at designing and

implementing databases. As a result, Sarah and Jack 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 Jack had created a flat file database and

entity relationships were not logically defined.

Based on your analysis, your recommendation is to create a basic Customer Relationship

Management style database which could be adapted for future enhancements and additional

functionality. As an experienced database designer, you are fully aware that the functionality

that Sarah would like, will not be possible to achieve in the first phase of the project. You

explain the need to define a core database system which will provide a foundation for added

functionality in the future.

User Requirements

A joint agreement was formed to design and implement the following core system

requirements as the first phase of the QO database project:

? The core database entities are co-op members, customers, produce items (fruit and

vegetables) and customer orders. You generally agree, however, you also know that there

will be other regular and composite entities, and business rules that will determine the

entity relationships.

? Co-op members will have a unique member id, a member name, a contact name, a date

when they joined the co-op and an end date (should they 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.

? A customer will register on the QO website and must provide their name, email address,

delivery address including State and Postcode. The database must also allocate a

customer id and a start date.

? Each produce item has an item id, an item name, weight and a description.

BISM7206 Information and Retrieval

UQ Business School

April 2020

Queensland Organics Case Study: Description and Assignment Specifications 3

? Each produce item may be stocked by many co-op members and each co-op member will

stock many different produce items. Each co-op member has their own price for each

produce item although weights are standard across the co-operative. This weak entity will

have the QO co-op member id and the item id, the price, price date, unit shipping cost,

and an ‘in stock’ attribute.

? Customers may order produce items from any QO member. The order will have an order

id, customer id, QO member id, order date, order status, shipping date, courier name and

shipping reference number. Each order must include one or more order items. Each order

item is for a produce item and has an item id, order id, item cost, quantity and unit

shipping cost. While each member has their own unit shipping costs for their produce

items, to simplify this phase of the database project, these unit shipping costs do not vary

across customer delivery locations.

? When a customer submits an order through the QO website, the order is saved into the

database and an alert message is sent to the QO member automatically through the

messaging system.

? Jack described the messaging system he had built into the QO website for customers to

ask questions and send alerts including notifying QO members of new customer orders,

etc. However, you have decided 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 QO member id. The message entity will also include a date

stamp field and a message field. This simple format will give Jack the flexibility to use it in

many different ways on the website.

BISM7206 Information and Retrieval

UQ Business School

April 2020

Queensland Organics Case Study: Description and Assignment Specifications 4

Required Data

IMPORTANT NOTE – the following data MUST be used in your Part B Database

Implementation. Zero marks will be awarded for Part B if the following data is not used as

part of your SQL implementation.

Table 1 – Members of Queensland Organics

Business Name Contact Name Address

Fabulous Fruit & Veg Shelly Adams Bluebell Road, Tinana, QLD 4650

Organic Green Grocer Gary Ward Brook Rd, Kumbia, QLD 4610

Bellthorpe’s Best Berries Jessica Stockdale Brandons Road, Bellthorpe, QLD 4514

Stanthorpe Strawberries Anthony Johnstone Mount Banca Road, Stanthorpe, QLD 4380

Atherton Organics Sarah James 142 Channel Road, Tinaroo, QLD 4882

Organic Fruit Emporium Nick Andrews 190 Green Rd, Wamuran, QLD 4512

Table 2 – Produce Items

Item name Description Weight Item price

Unit

shipping

cost

Vegetable Box Fresh seasonal vegetables 15kg $50 $18

Salad Box Fresh seasonal salad vegetables 10kg $40 $15

Fruit Box Fresh seasonal fruit 10kg $45 $15

Mixed Box Fresh seasonal fruit and vegetables 15kg $70 $18

Vegetable & Herb Box Household staples plus garden fresh herbs 15kg $60 $18

Berry Mix Seasonal mix of strawberries, blueberries

and raspberries 1 kg $19 $9

Capsicum Organic red 220g $6.95 $4

Mushrooms Flat brown mushrooms 500g $8.95 $5

Turmeric Fresh turmeric roots 200g $11.95 $4

Asian Greens Random – bok/choisum/kailan 500g $9.95 $5

Tomatoes Organic grape/cherry 250g $5.95 $4

Ginger Fresh ginger roots 200g $6.95 $4

BISM7206 Information and Retrieval

UQ Business School

April 2020

Queensland Organics Case Study: Description and Assignment Specifications 5

Please note: The item prices and unit shipping costs listed in Table 2 are a guide only. Each

co-op member has their own price and unit shipping cost for each of their produce items

These costs can vary among members although the weights for each produce item are the

same for all co-op members.

Assignment Specifications and Deliverables

Part A: Database Analysis and Design

The first part of this assignment requires you to analyse the database requirements and

design the first phase of the database project in accordance with the case study background

and requirements. The following documentation is required to be submitted as a MS Word

Document:

? Entity Relationship Diagram using Crows Foot Notation (preferably using draw.io);

? Relational Schema - including Primary and Foreign Keys (preferably using draw.io);

? Supplementary Design Requirements formatted as a data dictionary. The following

information can be used as a guide for the elements to be included:

o Table names and their data attributes and descriptions;

o Information on data type, length, format, acceptable values;

o The compulsory/optional nature of the attribute and if nulls are valid.

? Assumptions

Regardless of the drawing software you use, each of your drawings must be saved as an

image (e.g. png) and then pasted into your Word document. Do not embed the link to your

drawing as this will not be marked and you will receive zero marks for your models.

Part B: Database Implementation

You are required to implement a MySQL database using the database design developed in

Part A. The following requirements are to be implemented:

? CREATE TABLE statements for all tables including integrity constraints;

? CREATE TRIGGER statement to automatically insert a message to the Message table when

a customer order is placed;

? INSERT INTO statements for populating the database:

o Incorporate the exact 6 member names given in Table 1 (make up email addresses

and phone numbers);

o Incorporate the exact 12 items and their names given in Table 2;

o Create your own member item pricing for produce items and their unit shipping

costs (at least 3 produce items for 3 members);

BISM7206 Information and Retrieval

UQ Business School

April 2020

Queensland Organics Case Study: Description and Assignment Specifications 6

o Create at least 3 customer entries;

o Create at least 3 customer orders;

> Remember that data may need to be inserted in a particular order to comply with

integrity constraints.

Part C: Database Queries

When you have implemented the MySQL database (Part B above), you are required to write

SELECT statements to provide the following user requirements:

1) SELECT statement/s that will display the following data for a sample customer order:

? customer name and account number;

? order number/id and the total amount for the order;

? order date;

? At least three order items:

o item name;

o quantity;

o price;

o shipping cost;

o item total cost.

2) SELECT statement that will produce a report of the ordersfrom a particular co-op member

within a specified period of time:

? List all orders between a start-date and an end-date for a particular co-op

member such that:

o the report will be grouped by customer;

o each line in the report will include the customer name, customer state,

order number/id and total for that order; and

o will be ordered by the oldest order to the most recent order.

Please refer to the associated Case Study Assessment Guidelines for

submission instructions and the marking rubric.

Prepared by: Dr Sherrill Cooper

Course Coordinator

BISM7206 Information Retrieval and Management

April 2020