ISM7206 Information and Retrieval
UQ Business School
Semester 1 2023
Assessment Task 2 – Team Project: Description and Specifications 1
BISM7206 Information Retrieval and Management
Assessment Task 2 – Team Project
Description and Specifications
Purpose
This document provides the description and specifications for Assessment Task 2 – Team Project
(AT2). After you have read the following background description and assignment specifications, you
should refer to the associated BISM7206 Team Project - Formatting and Submission Requirements
document. If your team has any questions after you have read both documents and researched the
issue, please post your questions to the ‘Assessment Task 2 – Team Project’ forum on the Discussion
Board under the relevant thread.
Background
While Southeast Queensland agriculture supports a diverse range of plant and livestock industries,
Sienna and Jake Marshall focus their farming within the horticultural industry. In particular, the
Marshalls grow strawberries and tomatoes on their farm at Beerwah on the Sunshine Coast. Sienna
and Jake are young, very well educated and tech savvy. Their respective families were also farmers,
so Sienna and Jake have hands-on farming experience.
Sienna and Jake both attended university and completed degrees in Agricultural Engineering. Their
final year project required investigation of the accuracy and capacity of unmanned aerial systems
(UASs) and imaging technologies for biosecurity surveillance in horticultural industries. Sienna and
Jake believed these emerging technologies and the ‘smart farming’ methods and tools would
maximize crop yields and other agribusiness outcomes.
Essentially, smart farming uses agricultural technology encompassing a broad range of disciplines and
devices including autonomous machines, robotics, drones, mobile devices, artificial intelligence, the
Internet of Things, and satellites. In an era of ubiquitous automation and digital connectivity, Sienna
and Jake decided they had to implement smart farming on their farm.
The application of these smart farming tools and techniques has optimised the Marshall’s farming
activities, from monitoring fields and using drones for pesticide delivery to creating the perfect
greenhouse climate and harvesting. Although the Marshalls are happy with their smart farming
achievements, they want to undertake some off-farm activity to generate further income. In this
context, Jake would like to pursue his enthusiasm for smart farming and especially drones, by offering
farming technologies to neighbouring farms as a paid service. For example, farmers could hire Jake to
make harvest predictions using his drones, cameras, sensors, and artificial intelligence to count the
number of flowers and fruit on their plants. Other services might include monitoring the health of
plants, identifying diseases, spreading predatory insects and more. Jake hopes that other horticultural
farmers will embrace the new technology and will be keen to hire Jake and his smart farming services.
BISM7206 Information and Retrieval
UQ Business School
Semester 1 2023
Assessment Task 2 – Team Project: Description and Specifications 2
To better understand the business potential of neighbouring farms, Jake wants to develop a business
system called the Smart Farming System (SFS), to identify and categorise data about horticultural
farms in the Sunshine Coast region. For example, SFS should maintain records about each horticultural
farm within the Sunshine Coast region including the farmer’s name, address (including the local area),
email address and phone number. Jake needs to know the crop types (commodities) grown on each
farm and recognises that it is common to grow more than one type e.g., the Marshalls grow
strawberries and tomatoes. Ideally, the GPS coordinates for the location of each crop type as well as
the field size would also be stored in the SFS.
For each crop type, Jake also wants the SFS to store the crop’s botanical name, a brief description,
and the sub-industry it belongs to (e.g., vegetables). The names and descriptions of diseases and pests
(e.g., insects) that commonly attack these crop types also needs to be stored. In the future, Jake hopes
the SFS will include methods for preventing and eradicating pests and diseases, as well as images of
affected plants. However, this requires significant research and will not be included in the SFS initially.
The Marshalls believe that embracing technological advancement is the key to cost-effective
agribusiness and they are keen to incorporate ‘digital agriculture’ into their future business strategies.
Digital agriculture is the next generation of farming methods and tools using big data analytics and
artificial intelligence technology. Data collected and analysed is based on satellite data which is
processed and interpreted and enables farmers to gain a deep understanding of the overall state of
their farms. While the Marshalls are keen to pursue this innovative approach, they realise that future
growth strategies need to have the fundamental business information systems in place. To start with,
therefore, the SFS will focus on the basic needs of Jake’s new business venture.
Some of the required data for the SFS is contained in a large dataset Jake acquired from the
Queensland Department of Agriculture. The dataset contains records for various agricultural
industries in the north, south, and central state divisions of Queensland. He plans to use some of this
data to determine the gross production value (GPV) of crop types within the Sunshine Coast. For
example, Jake will compare the total GPV of strawberries against the total GPV of tomatoes to
determine the crops with the highest production value which can be used as the best entry point for
his business venture.
Jake will only be offering his SFS services to horticultural farms and initially, wants to limit his potential
customers to those that grow fruits, nuts and vegetables including the following crop types or
commodities: strawberries, tomatoes, pineapples, and bananas. Jake also wants to limit his travelling
distance within the Sunshine Coast region to a subset of local areas including the following:
• Caloundra West • Glass House Mountains
• Beerwah • Bli Bli
• Palmwoods • Landsborough
At this stage, the dataset contains more records and data than Jake requires, and it is challenging for
him to determine the best way to export and integrate the data into the SFS. The Marshalls recognise
that the success of the new business venture will largely depend on easy and timely access to accurate,
complete, consistent, and useful information. Jake has done some research and believes that a well-
BISM7206 Information and Retrieval
UQ Business School
Semester 1 2023
Assessment Task 2 – Team Project: Description and Specifications 3
structured database system would address his requirements. While being tech savvy, Jake does not
have database development skills; nor does he have sufficient time to acquire these skills.
Consequently, your database design consultancy team has been hired to make recommendations for
the development of Phase 1 of the SFS. Ultimately, a centralised database system that maintains data
about the complete business will be required. Initially, however, a phased approach needs to be
adopted to limit the extensive nature and complex possibilities of the SFS. Fundamentally, Phase 1 of
the SFS database should enable access to the basic details about farms, crop types grown on those
farms, and the diseases and pests that commonly attack those crop types within the Sunshine Coast
region. Phase 1 will also include the gross value of the crop types.
Your team should consider the future growth of the SFS when designing Phase 1 of the proposed
system. Therefore, Phase 1 should be designed in a manner that will enable the integration of future
enhancements and additional functionality. However, your design should reflect the “as-is” processes
or current requirements, not the “to-be” state and future requirements, of the SFS business venture.
Phase 1 Limitations and Assumptions:
You are only required to complete the first Phase of the project which has a limited scope.
Please take into consideration the following assumptions which may simplify your database design:
• A particular crop type only grows in one field or location on a farm.
• Only one variety of crop species is grown e.g., although many varieties of strawberry grow on
the Sunshine Coast including Red Rhapsody, Juliette, Scarlet Rose and others, this assignment
assumes only one variety is grown.
• Assume that each crop type can only be attacked by one disease and only one pest.
• For horticultural crops, the rural land/field sizes are best stored as hectares. For consistency, if
any land/field sizes are specified in acres, Jake wants this to be converted to an approximate size
in hectares (based on ten acres being equivalent to approximately four hectares).
• All GVP amounts should be in Australian dollars and should whole dollars.
Further assumptions made by your consultancy team should be documented and justified in your
business report.
To avoid potentially unnecessarily complex designs, please post questions that you might ask the end-
user, to the ‘Assessment Task 2 – Team Project’ forum on the Discussion Board under the relevant
thread.
BISM7206 Information and Retrieval
UQ Business School
Semester 1 2023
Assessment Task 2 – Team Project: Description and Specifications 4
Assignment Specifications and Deliverables
Your database design consultancy team has been asked to design and develop a well-structured
database for the SFS based on the previous background narrative and the stated limitations and
assumptions. You are required to write a professional business report that includes or addresses the
following:
1) Team charter:
Your team should have completed a team charter. Please provide documentation of elements in
your team charter that address the following:
a) Work breakdown and task allocation.
b) Team dynamics including communication methods, action to be taken for poor team
performance and conflict resolution.
2) Logical database design (SFS DB Phase 1):
a) Using Crows Foot notation, construct a relational data model that shows the logical database
design (use app.diagrams.net). Your model should be based on the background narrative,
limitations and assumptions, and the dataset referred to below in 4) Cleanse and import data.
a) Create a data dictionary with details about each relation including attribute names,
descriptions, SQL data types and sizes as well as integrity constraints on the data and any
default values. Your data dictionary should be presented as a set of tables in your report and
it is suggested that you consider changing the Page Orientation to Landscape in this section
to make it more readable.
3) Relational Schema
Draw a relational schema based on the logical database design using app.diagrams.net. The
relational schema should be drawn in the same way as the Week 4 Tutorial, remembering to
include all relations and appropriate key notation.
4) Cleanse and import data:
Jake has provided you with an excel workbook containing the dataset acquired from the
Queensland Department of Agriculture (AgTrendsDataset.xlsx). It is likely that there will be errors
in the dataset so you are required to cleanse and filter this data so that data imported into the
new database is required. accurate, complete, and consistent. You are required to use this data
to populate part of the new database (and incorporate this into your logical database design).
a) Cleanse and filter the worksheet and import the data into the new database:
• In addition to Jake’s requirements already specified in the background narrative, not all
data in the dataset is required in the SFS. For example, Jake does not need data in the
Division or Industry columns.
• Document the step-by-step process you apply to achieve the requirements of 4a. e.g.
- What did you change or do to cleanse and filter the data in each step?
- How did you import the data into the new database?
• Carefully consider and apply the details in the background narrative.
BISM7206 Information and Retrieval
UQ Business School
Semester 1 2023
Assessment Task 2 – Team Project: Description and Specifications 5
• Ensure that all fields are free of data errors and contain valid, accurate, complete and
consistent data.
b) Display results:
• Write a SELECT statement in MySQL Workbench to display the column headings and first
row of each table created in 4a) above.
• Display the data created in 4a) above, in alphabetical order by local area and crop type,
using one SELECT statement.
• After executing these scripts, copy the SQL scripts and paste into your report. Then, take
a screenshot of the Result Grid (displaying the full results) and paste this into your report
as an image. Check that all the details in this image are complete and legible.
MySQL scripts must be copied from MySQL Workbench and pasted into your Microsoft Word
document (not as an image). Do not embed links to .sql files as these will not be marked and
you will receive zero marks for your SQL scripts.
5) Database queries:
a) Jake and Sienna expect the SFS database system will enable more informed strategic planning
and better decision-making for Jake’s new business venture. In this context, what are two (2)
business management questions they would expect the database to answer? Briefly explain
how the answers to these questions might help improve the management planning and/or
decision-making processes by Jake and Sienna. These questions should be insightful rather
than daily transactions such as finding the email address of a particular farmer.
b) Write one SELECT statement for each of the two questions specified above (5a). You are not
required to use MySQL Workbench for these queries but format these queries as you would
in MySQL Workbench (there should be no syntax errors).
c) Having subscribed to the Queensland Department Agriculture’s mailing list, the Marshalls
expect notifications of any updates or changes to legislation regarding the usage of chemical
pesticides. How could the Department Agriculture’s database system automatically activate
a notification to farmers on their mailing list to inform them of changed legislation for usage
of chemical pesticides? (Limit your answer to concepts/tools discussed in BISM7206).