CSG1207D Systems and Database Design
Assignment Task 1 - (Database Analysis & Design)
Trimester 1, 2021
Assignment: Task 1 (Database Analysis and Design), Task 2(Implementation and Testing)
Assignment Marks: Marked out of 60, (30% of unit)
Task 1 is marked out of 20, (10% of unit)
Task 2 is marked out of 40, (20% of unit)
Due Dates: Task 1 due 23:59, Thursday 15/4/2021 (Week 7)
Task 2 due 23:59, Thursday 13/5/2021 (Week 11)
Learning Outcomes (Why are you doing this assignment?)
This assignment addresses the following learning outcomes from the Unit Outline:
1. Use Data Analysis to design a Database (Task 1);
3. Implement a Database design using a Database Management system (DBMS), and
to construct complex queries upon it (Task 2);
General Assignment Information
Your first task is to design a database by analysing the requirements for the scenario detailed on the
following pages. State any assumptions/notes you have made regarding your database design at the
beginning of the database design document. Do not make any assumptions/notes that change the
structure of the scenario, as this may make Task 2 of the assignment difficult. Only make
assumptions/notes that influence your database design. If you are unsure about an
assumption/note you wish to make, ask your lecturer/tutor.
Once you have identified the entities, attributes and relationships of the scenario in sufficient depth,
you are required to create a logical ER diagram and a physical ER diagram to depict your database.
Adhere to the distinctions between logical and physical ER diagrams covered in lectures. Show the
Table Creation Order in your physical ERD.
Lastly, create a Table Instance Chart (TIC) for each entity in your data model. List your TICs in the
appropriate table creation order that will need to be used to create the database. Include any
additional information, if any, that may be needed to implement the database. Remember, the TICs
should contain all the information needed to implement your database. Download the ‘Sample
Table Instance Charts for Assignment Task 1’ from Week 0 in Moodle.
Your complete database design should consist of a list of assumptions/notes, logical and physical ER
diagrams and TICs. This should be in the form of a single word-processed document. Include an ECC
Assignment Coversheet on the first page of this document. Be sure to include details of both team
members if appropriate.
You can use [Link] or [Link] (or any other modelling tool you are familiar with) to
draw your ERDs. Show Primary and Foreign Keys as such: CustomerID (PK) and CustID (FK).
Page 1 of 4
Scenario Details
You are required to design and create a database for an airline. The database must
contain details of the airline’s planes, flights, flight instances and staff, as well as
supporting data as detailed below.
Details of the planes owned by the airline must be stored. This must include the registration
number of the plane, the year it was built, its first class and economy passenger capacities,
and a foreign key identifying the model of the plane.
Details of plane models must be stored. This must include the model number, manufacturer
name, range (how many kilometres it can travel without refuelling) and cruise speed.
Details of flight paths must be stored. This must include a flight number, a foreign key
identifying the airport the flight departs from, a foreign key identifying the airport the flight
arrives at, and the distance between the airports in kilometres.
Note: A flight is a route that the airline offers – e.g. Flight QF574 is a Perth to Sydney
route offered by Qantas.
Details of flights must be stored. This must include a flight instance ID, a foreign key
identifying the plane making the flight, a foreign key identifying the flight path that is being
flown, the date/time that the flight leaves, and date/time that the flight arrives.
o Note: A flight is a specific occurrence of a flight – e.g. The 2016-07-04 QF574 flight
at 05:30.
o The arrival date/time of a flight instance must be later than its departure date/time
Details of airports must be stored. This must include the airport code (e.g. “PER” for Perth),
the name of the airport, a contact phone number, its latitude and longitude, and a foreign
key identifying the country that the airport is in.
A list of countries must be stored. This must simply contain the two letter country code (e.g.
“AU”) and the name of the country.
Details of pilots must be stored. This must include a pilot ID, their first name, last name,
date of birth and number of hours flown.
o The database must record which models of plane each pilot is qualified to fly. Each
pilot must be qualified to fly at least one model of plane.
o The database must record which pilots are aboard each flight instance. Each flight
instance requires exactly two pilots – a pilot and co-pilot.
o The pilot and co-pilot of a flight instance cannot be the same.
Details of flight attendants must be stored. This must include an attendant ID, their first
name, last name, date of birth and hire date.
o For training purposes, some flight attendants mentor other flight attendants. The
database must record each flight attendant’s mentor, if they have one.
o The database must record which attendants are aboard each flight instance. A flight
instance can have many attendants on board.
o The database must record which attendant has been designated the flight service
manager (FSM) of each flight instance. Each flight instance must have one FSM.
Page 2 of 4
General Information and Guidelines
The information above describes the entities, attributes and relationships required in the database.
Some minor details, such as the cardinality of some relationships, have been omitted. It is up to you
to make (and state) any assumptions you need in order to complete the database design. If you are
uncertain about any part of the scenario described above, seek clarification from your lecturer.
Be sure to specify the most appropriate data type (and length, where applicable) for each attribute
in your data dictionary. Note that when you are storing a date/time, it should be stored as a single
column – do not split the date and time into two columns unless there is a very good and necessary
reason to do so. Some tables will benefit from having a compound primary key.
Read the scenario details several times to ensure that your database design incorporates all the
elements described. If you desire feedback on your work in progress, send it to your lecturer.
Page 3 of 4
Submission Requirements
Submit your database design as a single word-processed document by the Task 1 due date
above. Include an ECC Assignment and Report Cover Sheet as the first page.
You are free to use whichever font you prefer for your assignment but you must ensure the
following:
1. Pages must be numbered using the Page x of y format
2. Heading Styles must be used
3. All diagrams must be titled
4. The page orientation for the Table Instance Charts must be changed to landscape to
accommodate the width of the tables
5. The document must be spell checked before submission
Late submissions will be penalised according to the Unit Outline.
Assignment – Task 1
Marks Allocation
Criteria Marks
Assumptions
2
All/Any assumptions that influence the database design clearly stated.
Logical ER Diagram
4
Diagram accurately depicts the scenario and includes all elements specified in the brief.
Physical ER Diagram
4
Accurately depicts the scenario and is a correct translation of the logical ER diagram.
Data Dictionary
8
Includes all entities and details of attributes as specified in brief and correct creation order used.
Presentation and Notation
2
Assignment is well presented, uses consistent and appropriate notation.
20
Total:
(10% of unit)
~ End of Assignment ~
Page 4 of 4
