support@essaywritingexpert.orgOverview
Task description
Foundational to addressing business concerns is an understanding of core concepts, principles and skills required for understanding, designing, and managing databases. This implies
understanding the kinds of techniques that are used to model data and an ability to develop a concise conceptual model that represents a given universe of discourse.
This assessment will involve creating a conceptual schema design for a given universe of discourse and the generation of a relational mapping.
USE OF AI
The use of artificial intelligence tools (for example, Chat GPT, Co-Pilot, Bard or any other AI tool) is not permitted in any assessment in this unit. The use of such tools, when not
authorised, may be treated as a breach of MOPP C/5.3 Academic integrity, and appropriate penalties may be imposed.
PREPARING FOR ASSESSMENT
You should watch pre-recorded lecture videos and attend all the tutorials from Week 1 to do well in this unit. DO NOT START THIS ASSESSMENT UNTIL THE END OF WEEK 2.
If you have any questions, please contact Ms. Toni De Palo via Teams (preferred) or email (t.depalo@qut.edu.au (mailto:t.depalo@qut.edu.au) ).
See further instructions at the bottom of this page under What to Do and What to Submit
Assessment 1 is broken into 2 tasks.
Task 1a and 1b [25 marks] – Group
Your task is to design a conceptual information model using ORM in an effective and efficient way to represent the information described in the scenario below. See the Deliverables section
following the scenario description for more details of what is required.
The following scenario is based on a fictitious (made up) small airline, Sunshine Bay Carriers (SBC), which carries passengers between mainland airfields located at Redcliffe and
Bundaberg, and two island tourist facilities – Lady Elliot Island, and Lady Musgrave Island. Whilst the details are made up, the case study represents a real-life scenario.
This assessment is based ONLY on the following scenario.
Case study (scenario)
Sunshine Bay Carriers (SBC) is a small airline that operates in Queensland. The airline has a fleet of aircrafts including fixed-wing crafts or aeroplanes and rotary-wing crafts or helicopters.
SBC provides passenger services for people wishing to travel to Lady Elliot Island and Lady Musgrave Island. SBC operates from two airports on the mainland. One airport is located at
Redcliffe and the other is in Bundaberg. The two-destination points Lady Elliot Island and Lady Musgrave Island have a shared airstrip for landing and take-off.
Airports and airstrips are identified by an International Civil Aviation Organisation (ICAO) code.
Airports used by SBC have the following ICAO codes;
Redcliffe SRED, Bundaberg SBUN, Lady Elliot Island SLEI, and Lady Musgrave Island SLMI.
Passenger aircraft are described according to Category and Class Rating (see Table 1 below). Pilots are endorsed to fly aircraft of based on the Categories and Class Ratings. Pilots are not
allowed to fly aircraft for which they are not endorsed. For example, a pilot may be endorsed to fly only a Single-engine Aeroplane and not the Single-engine Helicopter. This pilot may only
be assigned to flights that are aligned to their allowable class and category.
Aircrafts have identifying characteristics (see Table 2 below). Every aircraft will have an identifying unique registration number. The aircraft’s make and model are recorded, along with its
category and class rating. SBC also notes the number of seats on the aircraft that can be booked by a passenger (i.e. maximum number of passengers the
aircraft can carry). Other details include the aircraft’s weight (in kilograms) when empty (known as tare weight), the maximum allowed take-off weight (MTOW), and the operating range (in
kilometres) of the aircraft (i.e., the distance an aircraft can safely fly without refueling)
Sunshine Bay Carriers keeps detailed information about the pilots it employs (see Table 3). This information is both current and past. Each year, SBC completes an audit of all its active pilots
to ensure compliance with regulations.
To note: Tables are only samples of information and are not the complete list.
Table 1 – Aircraft Category and Class Ratings Relevant to Sunshine Bay Carriers (SBC).
Class Rating
Aircraft
Category
Single-engine
Aeroplane
Multi-engine
Aeroplane
Single-engine
Helicopter
Aeroplane X X
Helicopter X
Table 2 – SBC – extract of Aircraft Fleet
Aircraft
Reg. ID
Make Model Category Class Seats TARE MTOW Range Speed
KV-DML Cessna
208B Grand
Caravan
Aeroplane Single 14 2350 5750 1980 320
KV-CES Cessna 208 Caravan Aeroplane Single 12 2145 5550 1980 310
KV-RUT Pilatus BN2A Islander Aeroplane Multi 9 1886 3000 1398 216
KV-LLB Bell 206L Helicopter Single 5 1011 2018 450 105
KV-BOR Robinson R44II Raven Helicopter Single 4 683 1334 300 216
Table 3 – Sunshine Bay Carriers – extract of pilot details
Pilot ID License Number FamilyName FirstName Status
Emp.
Start Date
Emp.
End Date
SBC001 14785 Smart Max Active 25/07/2009
SBC002 16985 Singh Lydia Inactive 02/02/2010 05/08/2023
SBC003 35894 Walker Samantha Active 09/04/2011
SBC004 25847 Solo Hans Active 16/01/2020
SBC005 96854 Bowler Ferris Active 31/07/2021
SBC006 28795 Jones Simon Inactive 06/03/2022 06/03/2024
Table 4 – SBC – extract of pilot endorsement details
Pilot ID
License
Number
Category Class Rating
SBC001 14785 Aeroplane Single
SBC001 14785 Aeroplane Multi
SBC002 16985 Helicopter Single
SBC003 35894 Aeroplane Single
SBC003 35894 Aeroplane Multi
SBC003 35894 Helicopter Single
SBC004 25847 Aeroplane Single
SBC004 25847 Helicopter Single
SBC005 96854 Aeroplane Single
SBC006 28795 Aeroplane Single
SBC006 28795 Aeroplane Multi
SBC006 28795 Helicopter Single
SBC organises its operations based on the airports / airstrips. Routes are determined based on the following information (see Table 5)
Table 5 – Sunshine Bay Carriers – extract of routes
Route Origin Destination Distance
RLEI SRED SLEI 350
RLMI SRED SLMI 400
BLEI SBUN SLEI 110
BLMI SBUN SLMI 130
LEIR SLEI SRED 350
LEIB SLEI SBUN 110
LMIR SLMI SRED 400
LMIRB SLMI SBUN 130
Specific Flights operated by SBC involve, a Route, an Aircraft and a set of date/times. This is a simplified version of the real scenario where we would consider gates and Runway options.
Details in table 6 include pilot on-board, take-off, and landing times.
A Pilot will be assigned to a Flight. It is important to note that the allocation of an aircraft to a flight determines the number of passengers that can book for the flight as well as the weight of
luggage that may be carried.
Table 6 – extract of the list of WBA flights
Flight ID Route Aircraft Pilot Co Pilot Pilot on Board Take Off Landing
SBC1011 RLEI KV-CES SBC004 SBC001
05/6/2024
08:45
05/6/2024
09:30
05/6/2024
10:35
SBC1012 RLMI KV-LLB SBC006
05/06/2024
9:30
05/6/2024
10:15
05/6/2024
11:25
SBC1011 RLEI KV-DML SBC003
05/06/2024
11:00
05/6/2024
11:45
05/6/2024
12.02
SBC2013 LEIR KV-CES SBC001 SBC004
05/06/2024
13:00
05/6/2024
13:50
05/6/2024
14:55
SBC2013 LEIR KV-DML SBC003
05/06/2024
16:00
05/6/2024
16:45
05/6/2024
17:50
SBC2012 LMIR KV-LLB SBC006
05/06/2024
17:30
05/6/2024
18:15
05/6/2024
19:20
Table 7 – extract of Passenger Details
Passenger
ID
FamilyName FirstName EmailAddress PassengerPhone
1098811 Garland Merrick MerrickG@gmail.com 0457895365
1886123 Smith Cameron CSmith@optus.net 0471888365
7886444 Smith Jenny Jennyfrom@bigpond.com 0412359841
2312123 Coburn James JCoburn@tpg.com.au
1285962 French Dawn Dawnfrench@gmail.com 0415987426
1358954 French Toast TFrench@bigpond.com
Things to be considered
There are several constraints that govern Sunshine Bay Carriers operations. These constraints include:
Number of passengers booked on any flight cannot exceed the number of bookable seats in the aircraft assigned to the flight.
SBC allocates 90kgs /person as a weight for their calculations. Individual passengers are not weighed but the 90kgs is used as a metric.
Luggage weight is restricted to maximum 20kgs per person and NO in cabin luggage other than small handbag / laptop is permitted
An aircraft can only be assigned to a flight if it can fly (has capacity) the route for the flight
A pilot can only be assigned to a flight if the pilot’s license carries an endorsement for the category and class rating of the aircraft assigned to the flight
A pilot cannot be assigned to concurrent flights – flights that follow each other with no down time or have an overlap in times.
A pilot cannot be assigned to a flight if the pilot has not had at least 10 hours non-flying time in the previous 24 hours
Examples of Manifests
Manifest example 1
Sunshine Bay Carriers
Flight Manifest
Flight SBC1012
Departure 05/06/2023 9:30 am Arrival 05/06/2023 10:30 am
Aircraft KV-CES
Pilot SBC004 Han Solo Co Pilot SBC001 Max Smart
Route Redcliffe Airport to Lady Elliot Island
Passenger details
Passenger Number Family Name First Name Seat Luggage Weight
1098811 Garland Merrick 1a 10
7886444 Smith Jack 1b 15
7886445 Smith Jenny 2b 17
2312123 Coburn James 1d 15
1285962 French Dawn 3a 18
1358954 French Toast 3b 17
1589564 Illuzzi Grazia 3e 10
1487596 Illuzzi Sam 3d 12
Aircraft details
TARE (kgs) 2154
Fuel (kgs) 500
Take Off Weight (kgs) 3668
Manifest example 2
Sunshine Bay Carriers
Flight Manifest
Flight SBC1012
Departure 05/6/2024 10:15 Arrival 05/6/2024 11:25
Aircraft KV-LLB
Pilot SBC006 Simon Jones
Route Bundaberg Airport to Lady Musgrave Island
Passenger details
Passenger Number Family Name First Name Seat Luggage Weight
1098811 Garland Merrick 1a 12
3124566 Johns Graham 2a 14
1886 123 Smith Cameron 2b 17
Aircraft details
TARE (kgs) 1011
Fuel (kgs) 200
Take Off Weight (kgs) 1614
Criteria Sheet – Task 1 [25 marks]
Criteria
High Distinction
[25 – 21 marks]
Distinction / Credit
[20 – 16 marks]
Credit / Pass
[15 – 11 marks]
Pass / Borderline
[5 – 10 marks]
Fail
[4 – 0 marks]
Derive
elementary facts
and apply quality
checks.
[3 marks]
All elementary fact types from
the scenario are identified.
All
significant fact types are included.
Most of the elementary facts
typed from the scenario are
included.
Most
significant fact types are included.
Some of the elementary facts
are stated from the scenario.
Some
significant fact types are included.
Few of the elementary facts are
stated from the scenario are
identified.
Few
significant fact types are included.
Elementary facts do not reflec
facts from the scenario
are identified.
No
significant fact types are include
Syntactic
Correctness
[4 marks]
The model is complete and fully
syntactically correct.
The model is complete and
mostly syntactically correct.
The model is mostly complete
and/or reasonably syntactically
correct.
The model is partially complete
and/or mainly syntactically
incorrect.
The model is mostly incomplet
and/or syntactically incorrect.
Semantic
Correctness
[15 marks]
The model fully and correctly
reflects every aspect of the facts
described in the scenario.
The model fully and correctly
reflects most aspects of the facts
described in the scenario.
The model correctly reflects
many aspects of the facts
described in the scenario or
reflects most aspects but is
somewhat incorrect or inefficient
in structure.
The model reflects a few aspects
of the facts described in the
scenario and/or has an incorrect
or inefficient structure.
The model incorrectly reflects
the facts described in the
scenario.
Pragmatic
Correctness
[3 marks]
The model has a clear structure
designed for maximal
understandability by stakeholders
(layout, labels, annotations, etc.)
The model has a mostly clear
structure and/or is designed for
high understandability by
stakeholders (layout, labels,
annotations, etc.)
The model has a mainly clear
structure and/or is designed for
reasonable understandability by
stakeholders (layout, labels,
annotations, etc.)
The model has a somewhat
messy structure and/or does not
reflect that it has been designed
with consideration for stakeholder
understandability.
The model has an unclear
structure and/or most would fin
it difficult to understand.
Task 2 [25 marks] – Group
Following is an ORM model for customers. Map the schema to a relational database schema. Any possible constraints need to be included (e.g., primary key, foreign key).
Write the relations clearly, showing the Primary key in bolded and underlined (e.g., CustomerId).
The foreign key is shown in italics (e.g., OrderNumber ) and separately defined beneath the relation (e.g. ShoppingCart OrderNumber FK to Order OrderNumber).
Marks will be awarded for the following:
Full marks will be awarded if all relations are correctly mapped with the schema, including correct primary, foreign keys and other attributes.
Any incorrect/missing relations will be deducted:
penalty for missing relation (entity)
5 marks per missing relation
penalties for missing primary key, foreign key, and other attributes:
4 marks if missing a primary key.
1–2 marks if part of a primary key is missing.
3 marks for missing foreign key.
2 marks for missing non key attributes.
1 mark each for minor errors.
1 mark for each missing relational sentence or arrow
Criteria Sheet – Task 2 [25 marks]
Criteria
25 to >23.0 Pts
High Distinction
23 to >20.0 Pts
Distinction / Credit
20 to >15.0 Pts
Credit / Pass
15 to >12.5 Pts
Pass / Borderline
12.5 to >0 Pts
Fail
Application of RMapping
Procedures [25 marks]
In-depth and expert
application of rules 1 – 4 of
the Rmapping
Comprehensively applies
rules 1 – 4 of the Rmapping
In general, successfully
applies most of rules 1 – 4 of
the Rmapping
Makes an attempt to apply
rules 1 – 4 of the Rmapping
Unable to apply rules 1 – 4 of
the Rmapping
Unit Learning Outcomes assessed:
Knowledge of conceptual data modelling principles to understand, capture and analyse an abstraction of the complex data aspect of business domains.
Estimated time for completion Weighting Group or Individual How I will be assessed
20 hours 50% of final grade Group Using a 7-Point grading scale
What you need to do
This assessment is to be completed in groups of 2 (exceptions may be made with the approval of the teaching team). Group members should belong to the same tutorial group.
You will have to solve two tasks:
Task 1a. Present a list of elementary facts that fully and explicitly verbalise the fact types contained in the scenario description.
Task 1b. Apply the Conceptual Schema Design Procedure (steps 1 – 7) to a given business concern (group task)
Task 2. Map a conceptual schema design to a relational database schema (RMapping)
For task 1, you can use any modelling tool (including MS PowerPoint, Draw.io or clearly handwritten) to draw your models. Make sure the diagrams are readable, clearly labelled and based
on the above scenario.
What to submit
Assignment submissions MUST be a single PDF file properly identified (student name and number). Only 1 submission is needed per group. Please ensure you are enrolled in a Group on
Canvas to ensure your marks are allocated correctly.
Use the provided template file IFN554-Assessment 1 24s2 5c Template.docx (https://canvas.qut.edu.au/courses/17416/files/4664231?wrap=1)
(https://canvas.qut.edu.au/courses/17416/files/4664231/download?download_frd=1) to record your answers.
Please use the following link to submit your solution to this Assessment.
IFN554 Assessment 1: Submission Link (https://canvas.qut.edu.au/courses/17416/assignments/176281)
Feedback:
TEQSA PRV12079 | CRICOS 00213J | ABN 83 791 724 622
Under normal circumstances, you will receive marks for each criterion via a Canvas rubric within 10-15 working days of submission. Click on Grades to see your results. Usually, the reason
for each choice of mark is self-evident; the marker will look to include some written feedback about your performance. You should use this feedback to strengthen your performance in the
next assessment item.
Moderation:
All staff who are assessing your work meet to discuss and compare their judgements before marks or grades are finalised.