ISM7206 Information and Retrieval
UQ Business School
Semester 2 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
After completing their master’s degrees in aerospace engineering and aviation, Zack Johnson and
Katie Miller wanted to generate an income based on their expertise and knowledge of unmanned
aerial vehicles (UAVs) or drones. Initially, Katie and Zack offered specialised drone consultancy
services to organisations planning to implement activities such as drone-based data acquisition, drone
mapping and photography, drone crop-dusting, and heavy lift drone technology.
While their consultancy services were successful, Katie and Jack decided to pursue a more hands-on
approach, thereby enabling them to directly share their enthusiasm for drones. Their goal was to build
a business that delivers an engaging product experience with drones.
The Flying Drones Company (FD) has emerged as a dynamic and innovative start-up specialising in
drone-related services. FD caters to both small companies and the general public, offering an
array of services, including basic training, First-Person View (FPV) simulator experiences, certified
pilot training, and team bonding activities. Katie and Zack refer to the various services as programs
and plan to expand their program offering in the future. Additionally, FD owns and maintains its own
fleet of drones, which are utilised in the various programs.
As the business continues to grow, Katie and Zack understand that efficient management of data is
crucial. In this context, Katie and Zack recognise that a well-structured database system is
fundamental to effectively manage and streamline operations at FD. A well-structured database
system also has the potential to provide insight into customers, products, and services; enhance the
customer experience, enable FD to get more value from its data. Deriving value from their data, FD
may improve their competitive advantage gained from insights on customers, products, processes,
and opportunities.
Accordingly, the new FD database should store and maintain data about each customer that book one
of the FD programs. Each customer will be allocated a unique customer number when they book into
BISM7206 Information and Retrieval
UQ Business School
Semester 2 2023
Assessment Task 2 – Team Project: Description and Specifications 2
one of the FD programs. Other customer details that Katie and Zack want to access include the
customer’s first and last name, their age, address, and phone number.
Each program has a unique program identifier and additional data to be stored about each program
includes the program name, and the duration and cost of the program. Currently there are four
programs (as specified previously), and the most expensive is the certified pilot training program
which costs $7,500.
Customers book their program for a specific date, but they cannot book into more than one program
on the same day. If the customer is booking into a program that runs over more than one day, the
starting date of their program is recorded but not the end date of the program. There can be up to
five customers booked into one program, but a program only needs one customer booking for that
program to go ahead.
It is important that there are enough drones available for each program booking. Essentially, a drone
reservation/allocation list is created based on the program identifier and the date the program is to
be booked. To complete a drone reservation, the status of the drone needs to be checked. The
availability status reflects whether a drone is ‘Available’, already ‘Booked’, or undergoing
‘Maintenance’. If the drone is available, the availability status will be changed to ‘Booked’ before the
program booking is finalised.
Currently, each drone undergoes a maintenance check every six months and this is when the
availability status is set to ‘Maintenance’ showing that the drone is currently unavailable.
Maintenance records are created for each drone that undergoes a maintenance check; these records
reflect only basic details such as the drone identifier, the date of the maintenance check and some
brief notes about maintenance. On completion, the maintenance team changes the availability status
back to ‘Available’.
There are only twelve drones in the FD fleet with each drone having a unique drone identifier. The
limited data about the drone fleet needs substantial work to transform it in to accurate, complete,
and consistent data that will be useful and can be stored in the new FD database system. In this
context, the drone identifier is much like a surrogate key with no meaning. To enhance the value of
the data in the FD database system, a unique drone identifier could be created whereby the year and
month of the drone purchase could be incorporated into the structure of the unique drone identifier.
For example, if the unique drone identifier was ‘2022021’, this would immediately indicate the age of
the drone.
The information that should be stored about each drone in the FD database includes a meaningful
drone identifier, brand, model-name, description, flight-time (minutes), flight-distance (kilometres),
and the purchase date. Ideally, Katie and Zack would like the database to also store the website
address for each brand of drone they have in the fleet. Currently, the FD fleet consists of four brands
including DJI, Parrot, Skydio, and XAG. Six DJI drones were purchased on 1 February 2022; four Parrot
drones were purchased on 12 July 2022; two Skydio drones were purchased on 10 November 2022;
and two XAG drones were purchased on 05 March 2023.
Katie and Zack are keen to purchase many more drones and to grow their business. They recognise
the new career opportunities in the world of unmanned aviation as well as recreational activities
embracing the joy of flying drones. This suggests increasing demand for the FD programs. However,
BISM7206 Information and Retrieval
UQ Business School
Semester 2 2023
Assessment Task 2 – Team Project: Description and Specifications 3
to further embrace prospective business opportunities and growth, Katie and Zack also recognise that
they need access to more informative business data to better understand the business potential. They
believe that the success of FD will largely depend on easy and timely access to accurate, complete,
consistent, and useful information. While Katie and Zack are both tech savvy, they do not have expert
skills in database design and development; nor do they have sufficient time to acquire these skills
while managing and operating their business.
Consequently, your database design consultancy team has been hired to make recommendations for
the development of Phase 1 of the FD database system. 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 potentially complex aspects of the FD system. Essentially,
Phase 1 of the FD database system should enable access to the basic details about customers,
programs, bookings, maintenance, and the drone fleet.
Your team should consider the future growth of the FD 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 FD business venture.
Phase 1 Limitations and Assumptions:
You are only required to complete the first phase of the project which has a limited scope. In
particular, the first phase of the project should not include the following in your database design:
• Instructors, maintenance crew, or any staff details (e.g., name, salary, job title).
• Inventory stock control (e.g., drones, parts, accessories).
• Customer invoices and customer payments (accounts receivable).
• FD’s accounts payable.
• Allocation of instructors to programs.
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 2 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 FD 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 (FD 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.
b) Create a data dictionary with details about each relation including attribute names,
descriptions, SQL data types and sizes as well as other integrity constraints on the data and
any default values. Your data dictionary should be presented as a set of tables in your report
with one separate table for each relation. Also, 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:
Katie and Zack have provided you with a dataset acquired from a drone industry colleague
(DronesDataset). It is likely that there will be errors in the dataset and not all the data is required
in the new FD database. Therefore, you are required to format, cleanse and filter the data so that
the data imported into the new database is accurate, complete, consistent, and needed. You are
required to use this data to populate part of the new database (and incorporate this into your
logical database design). No other data is to be created and inserted into the new database for
this project.
a) Cleanse and filter the dataset and import the data into the new database:
• 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 2 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. Remember, do not create any other tables in
MySQL Workbench except those required to store the data extracted from the dataset in
4a) above.
• Display all of the data created in 4a) above, in alphabetical order by the brand and model
of each drone, 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) Katie and Zack expect the FD database system will enable more informed strategic planning
and better decision-making for their future business growth. 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 Katie and Zack. These questions should be insightful rather than
daily transactions such as finding the email address of a particular customer.
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) Accurate and timely updates to the drone availability status is critical to the allocation of
drones to programs. This is also critical to the streamlined business operating processes at FD.
Based on database transactions, how could the FD database system automatically activate
the changes required for the drone availability status? (Limit your answer to concepts/tools
discussed in BISM7206).