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