1

School of Computer Science


COMP9120 Database Management Systems
Assignment 1: Conceptual Modelling & Logical DB Design

Group assignment (12%)
Introduction
The purpose of this assignment is to provide you with experience in conceptual and relational database modelling.
You are given a domain description for the Western Sydney Airport (WSA). There are 2 high level tasks in this
assignment:
• Create an Entity Relationship Diagram (ERD) that captures the business concepts and requirements conveyed in
this description,
• Translate your ER diagram into a logical database design including relational database schema creation, key
constraints and integrity constraints.
This is a group assignment for teams of 3 people per group. You must be enrolled in an assignment group on Canvas.
You must inform the unit coordinator if you have not formed a group by the end of Week 5.
Please also keep an eye on your email and Ed for any announcements that may be made.
Submission Details
The submission of your solution is due at 11:59pm on Sunday 17/4/2022 (end of Week 8). You must submit the items
for submission (detailed below) via Canvas.
Items for submission
Please submit your solution to Assignment 1, in the ’Assignment’ section of the unit’s Canvas site by the deadline,
including the following four files:
1. Firstly, you should submit an assignment coversheet as a PDF document (.pdf file suffix) which is available for
download from this link on Canvas.
2. Secondly, you are required to submit your conceptual model in the form of an E-R diagram using the lecture
notation, formatted as a PDF document (.pdf file suffix). Please justify your choices for entity types,
relationship types, attributes, primary keys, constraints and design specialities.
3. Thirdly, you should submit an SQL file (.sql file suffix) containing all DDL statements necessary to fully instantiate
a working database based upon your ER diagram, and DML statements to populate each relation. Your file
should run without errors in PostgreSQL 9.5.1. You can annotate your statements using ‘--‘ at the start of lines for
comment. You should group your statements for ease of reading (e.g. by keeping all table constraints within the
relevant CREATE TABLE statement rather than declaring them externally, if possible).



COMP9120 Assignment 1
2
4. Lastly, you should submit another pdf document (.pdf file suffix) including the relational model (RM) diagram
that provides a visual model of your database schema. The figure below summarises the syntax to use for the RM
diagram.

Task 1: Domain Description for Entity Relationship Diagram (ERD) Modelling
Due to the recent border opening and consumers craving for overseas travel, the Western Sydney Airport (WSA) has
responded swiftly by hiring you to design a model that represents the organisation at the airport. The WSA is a new
world-class international airport currently under construction, built to service 10 million passengers a year with the first
stage expected to be open by December 2026. Your first task is to model a database management system in order to
deal with aircrafts that will be stationed and maintained at the airport, as well as their human resource departments.
The airport has a technical department that maintains aircrafts. The new information system shall support the
maintenance units of the technical department by storing information about every aircraft maintained at the airport.
The aircrafts are identified with a unique international registration number. Each registration number consists of a
unique two-letter code identifying the aircraft owner and a four-letter unique id within the airline fleet. For example,
’QF-APAC’ would be a valid aircraft registration number. ’QF’ is the code for the Qantas as the owner of the fleet of
aircrafts and ’APAC’ identifies one particular aircraft of the Qantas fleet. Furthermore, airline often associates a
specific name to an aircraft. For example, ’QF-APAC’ is named ‘Gadigal Land’ by Qantas.
Each airline must have a contact phone, email, and a registered website. Each airline owner is identified by a unique
name, and associated to a home country. Every aircraft has a specific type which the airport must license to
accommodate. Each type has a unique model name (e.g., ’A380-800’) and is produced by a specific maker (e.g.,
Airbus). The airport keeps track of a number of technical details for each aircraft type such as number of engines,
maximum number of passengers, maximum cruising speed (in km/h), as well as aircraft length, span and height (all
three in m). The airport also registers the date of the first flight where it originated, list price and noise class of each
aircraft type. The noise class is expressed as a numeric value ranging from 1 for the loudest aircrafts up to 10 for the
quietest. Class 0 is used for propeller driven aircrafts.
When it comes to the human resources, the airport’s initial focus is on three important departments as they recruit for
their respective staff: ground crew, technicians, and traffic controllers. Each employee must have their name and
salary captured. They are uniquely identified by a tax file number (TFN). Employees can join an airport-supported
society. The system will keep a unique membership number for those enrolled employees.
The ground crews ensure the safety and comfort of passengers. Employees may occasionally be called upon to work
overtime. Should that occur, the extra hours must be entered on each day so that the crews can be compensated
accordingly. We assume that each technician is an expert in at least one type of aircraft, but not more than a total of
six. The airport records the technician’s name, TFN, address, mobile number, email, and salary. The technician’s level
of proficiency should also be tracked. For traffic controllers, a medical examination must be completed every year and
each examination date needs to be logged.
The airport has a number of compliance test specifications that are periodically performed to ensure that aircrafts are
still airworthy. Each test specification has a Civil Aviation Safety Authority (CASA) test number, name, passing score,
and maximum total score. The CASA test number is a unique serial number which clearly identifies each test
specification. The CASA requires the airport to keep track of each time a given aircraft is tested by a technician with
regards to a certain test specification. Each testing event records the date and time, the number of hours the
technician spent on the test, and the score the aircraft received on the test.
To meet Australian regulatory requirements, the system must allow any aircraft that fails a compliance test to be
notified based on its test result, and reported to both CASA and the relevant airline. In this case, the affected aircraft
must also cease from flying until the required service and maintenance activities are completed to CASA satisfaction.
RelationA
keyA attrib1 fkey
RelationB
keyB attrib2
COMP9120 Assignment 1
3
Task 2: Relational Database Design & Modelling
Your second task is to design and create a relational database schema based on the Entity Relationship Diagram
(ERD) modelled from the first task. In particular, your solution should include:
• Tables and attributes with appropriate data types to capture all information in the model (please use the
same names as in your ER diagram for naming tables and attributes);
• Appropriate PRIMARY KEY, UNIQUE, FOREIGN KEY constraints for all tables;
• Correct foreign key specifications including ON DELETE clauses where suitable;
• Appropriate additional integrity constraints expressed by means of NOT NULL or CHECK clauses;
• INSERT statements to populate each relation with at least one record, to demonstrate a database instance
consistent with the ER model.
Additional details
In addition to the model captured through your ER diagram, the following details apply:
1. Attributes representing names should always have values.
2. Fields representing dates and/or times should always have values.
3. Airlines and technicians must have a specified email address.
4. The capacity and number of engines of an aircraft should always have values greater than zero, but not
exceed 575 passengers and 4 engines respectively.
5. All attributes in a tuple relating to details about an airline, ground crew, and traffic controller should always
have values. The employee’s salary should always be larger than nil.
Escaping PostgreSQL keywords in DDL
If you need to escape PostgreSQL keywords like “Table”, you will need to use double quotes.
e.g. CREATE TABLE “Table” (…);
Q&A
Q: How to draw the link from foreign key in a table to its referenced candidate key in another table if the foreign key
contains more than one attributes?
A: You should draw it in a similar way to the following RM diagram (specifically, see the Sell table). You can use any of
the available tools such as draw.io, Visio, Lucidchart, etc. to draw your diagram.

COMP9120 Assignment 1
4
Marking
This assignment is worth 12% of your final grade for the unit of study. Your group’s submission will be marked
according to the attached rubric (see last section of this assignment description).
Group member participation
If members of your group do not contribute sufficiently, you should alert the unit coordinator as soon as
possible. The course instructor has the discretion to scale the group’s mark for each member as follows:
Level of contribution Proportion of final grade received
No participation. 0%
Full understanding of the submitted work. 50%
Minor contributor to the group’s submission. 75%
Major contributor to the group’s submission. 100%
Marking Rubric
Your submissions will be marked according to the following rubric, with a maximum possible score of 12 points.
Novice (0 – 0.5 pt) Competent (1 – 1.5 pts) Proficient (2 pts)
ERD Notation &
Core Model
Major mistakes in the usage of
ER notation. Less than
competent model of the given
scenario.
Many entities, relationships, or
attributes were not correctly
captured by the model.
Good usage of E-R notation with
a few mistakes.
Entities, relationships, or
attributes were correctly captured
by the model, but with minor
mistakes.
Proficient usage of the E-R
notation.
The core model was very well
designed, and all the main
entities, relationships and
attributes were correctly captured
by the model.
ERD Constraints Many constraints were
incorrectly captured in the
model. No constraints captured
at all.
Constraints (key / total
participation constraints on
relationship types, etc.) were
correctly included in the model,
but with minor mistakes.
All appropriate constraints were
modelled correctly.
ERD Design
Specialities
Majority of design specialities
used were inappropriate or
incomplete. No design
specialities were used.
At least one useful ISA, weak
entity or aggregation used
appropriately. Minor or no
mistakes on design specialities
used.
All design specialities were used
appropriately.
Relational
Mappings
Less than competent schema
of the given scenario.
All main entities and relationships
were mapped correctly to
relations, with reasonable choice
of data types for most attributes
The core model was very well
mapped to a relational schema
and good choice of data types for
all attributes.
Key Constraints &
Semantic
Constraints
Major issues with key
constraints, or no key
constraints captured at all.
Major issues with integrity
constraints, or no integrity
constraints given.
Primary keys and foreign keys
were defined appropriately, but
with minor mistakes.
Integrity constraints such as
CHECK or NOT NULL were
defined correctly, but with minor
mistakes.
All the necessary primary keys
and foreign keys were defined
correctly, including appropriate
ON DELETE clauses.
All the necessary integrity
constraints for the model were
defined correctly.
COMP9120 Assignment 1
5
Example Data &
RM Diagram
No example data given or
yielded multiple errors.
No RM diagram submitted, or
major issues with the RM
diagram.
Some table example data
missing or generated an error.
RM diagram does not exactly
match the relational schema
created by the submitted SQL
file.
Database fully populated with a
consistent and correct set of data.
RM diagram exactly matches the
relational schema created by the
submitted SQL file (Note:
semantic constraints and example
data are not required in the RM
diagram).


欢迎咨询51作业君