COMP9120代写、代写SQL语言编程

日期：2022-04-14 11:16

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

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