7003ICT Data Management Assignment

2814ICT/7003ICT Data Management Assignment
Trimester 1, 2020
The Airline Case Study
Date / time due: Friday Week 11, 17:00 15 May 2020
This assignment contributes 30% to your final assessment.
Instructions:
The assignment should be done in groups of (maximum) 2 persons. Members in each
group should contribute equally. Submit one report per group with your solutions to all the
tasks. On the cover sheet include the following Contribution Agreement table (the
contribution needs to add up to 100%):

Student NameStudent NumberContributionSignature
%
%

2
Section I. Task Description
An airline company needs a database to track its staff, airplanes, flights and passengers
and so on. The detailed system specification is given in Section II.
Task 1: Conceptual design (50 marks)
Draw an (Enhanced) ER diagram for the conceptual design of the database. Make sure
your diagram is visible in the submission. Using automatic ER generation tools is NOT
acceptable.
You may use the Chen’s notation, the UML notation, or the crow’s feet notation in drawing
your EERD, but please do not use a mixture of them. If you use any non-standard
notation, you must provide a legend.
You can make your own reasonable assumptions if necessary. Clearly list your
assumptions.
Task 2: Logical design (25 marks)
Map your (Enhanced) ER diagram to tables, using the rules in text or lecture slides.
Clearly show the primary key and foreign keys.
Task 3: Schema refinement and documentation (10 marks)
1. Explain whether your tables are (at least) in 3NF. If a table is not in 3NF, modify your
ERD or do normalization. If you choose to use non-3NF tables, provide the reasons why.
2. Assign appropriate data types and lengths to all attributes using MySQL data types.
Include necessary integrity constraints such as domain constraints, null value constraints,
and other important integrity constraints.
Document your final database schema using a form similar to the following:
3
Task 4: Implementation (15 marks)
Write the MySQL SQL statements for the queries (1) to (8) in SECTION II – 8.
While implementation of the tables and the queries is optional, you should make sure the queries
are logically correct based on your final design in Task 3.
4
Section II. Terra Firma Airlines Case Study
Terra Firma Airlines requires a system to schedule flights and to control passenger
reservations. The system must be designed to satisfy the following requirements:
1. The airline has several types of planes of varying passenger capacities and flight ranges.
There are one or more airplanes of each type in the airline’s fleet. Each airplane has a
unique serial number. For each airplane, its serial number, type, manufacture date, and full
service history (service date, place, and description) should be maintained.
2. The airline has pilots, flight attendants as well as other staff. For each member of staff it
keeps data about his / her staff number (which is a unique identifier), the staff name, date
of birth, home address, and date joined the airline.
3. For each staff member, an emergency contact person, including name, postal address,
phone, email, and relationship with the staff, must be recorded.
4. Pilots are certified only on certain types of planes. Information on the types of planes
certified for each pilot, as well as the date of certification, must be maintained.
5. The airline provides regular training to flight attendants. Records of all trainings received
by each flight attendant must be recorded. Such records include the training program name,
the start and finish dates, and a brief description about the content.
6. A flight, which is identified by a unique flight number, has an original city, a destination
city but no stop-overs. The scheduled departure time and arrival time must be maintained.
In addition to all of the above information, an actual scheduled flight has a date, an airplane,
a captain, a co-pilot and up to 8 flight attendants. The captain and co-pilot must both be
pilots certified for the assigned type of airplane, and we assume that a pilot can work as
captain on one scheduled flight, and as co-pilot on another. In addition, the actual departure
time, actual arrival time, and a description (which describes incidents such as cancellation
due to weather conditions, or delays due to mechanical problems) need to be recorded. A
scheduled flight is uniquely identified by the combination of a flight number and date.
7. The airline needs an online booking system that people can use to search for available
flights and purchase tickets. For each ticket sold, the system must record the ticket number
(which is an identifier of the ticket), date purchased, payment type (e.g., credit card, paypal,
bank transfer), the flight number, flight date, ticket type (e.g., promotion-fare, flexi-fare,
premium-fare), ticket price as well as details of the passenger: ID type and ID number (a
valid ID can be a passport or a driver’s license), first name, last name, sex, address, contact
phone, and email address. The booking system must ensure that the same passenger is
not booked on the same scheduled flight more than once.
8. The system must be able to make the following queries (These requirements need to
be reflected in Tasks 1 and 2 in Section I):
(1) For each airplane (given the serial number), list the type code, type description,
5
capacity and flight range, manufacture date, purchase date, and the next service date.
(2) For each pilot (given the staff number), list the pilot name, the type code and
description of each of the types of planes on which the pilot is certified.
(3) List the flight number, date and airplane serial number for all flights on which the
pilot is currently scheduled using the pilot staff number.
(4) For each city served, list the city name, the state, and a description of the airport
(we assume each city has only one airport).
(5) For each city (given the city name), produce a list of the flights scheduled to
departure within the next 24 hours. The list should include the flight number, the time
of departure in ascending order of departure time.
(6) Given the flight number, list the origination city, destination city as well as the
departure and/or arrival times at these cities.
(7) For a scheduled flight, list the captain, the co-pilot, the flight attendants, and the
airplane serial number.
(8) For each scheduled flight (given the flight number and date), prepare a customer
call list, which includes the ID type, ID number, full name, date of birth, address and
contact phone number of the passengers booked on the flight.
(9) Given a date, an origination city and a destination city, list all scheduled flights and
the number of available seats on each flight.
(10) For a given passenger (identified by ID type and ID number), list the flights he or
she has been booked on, including the departure/arrival city and time.
(11) For a staff member, list the details of the emergency contact person.
(12) List all airplanes by the serial number and type code, and if an airplane is
scheduled to fly today, also list the flight number.
(13) For each flight attendant, find the details of training he/she received.
(14) For each airplane (given the serial number), list its scheduled flights (departure
and arrival city as well as time) today.
6
Section III. Checklist
Task 1:
• The EERD contain all necessary entities, relationships, attributes so that all the
queries can be answered.
• Correct entity/relationship types (strong or weak) are used.
• Each strong entity has a PK
• Each relationship has the two types of constraints
• Correct class-subclass relationships are identified
• There are no redundant entities, relationships or attributes (In particular, no foreign
key attributes are present in the entities
Please do not confuse ER diagram with schema diagram, and confuse entities with
tables.
Task 2:
• You have mapped your EERD to tables correctly using the appropriate rules.
• You have marked/listed all primary key and foreign key constraints.
Please do not just copy your entities as the tables…usually you need more tables
than entities because you may need to map some relationships into tables, and
multi-valued attributes into tables. Please do not combined this task with task 3.
Task 3:
• All your tables are in 3NF (and otherwise convincing reasons are provided to justify
the use of non-3NF tables).
• An appropriate data type and length for each attribute.
• Other constraints such as null value constraints and domain constraints are
present, and important business rules are also identified.
Task 4:
• The SQL commands are logically correct against your final tables in Task 3.
Submission:
Submit an electronic copy of your assignment online, where the filename should be of the
format
StudentNumber1StudentNumber2.pdf (for 2-person group), or
StudentNumber.pdf (for 1 person group)
Each group member should sign the declaration form on the cover sheet.

We are the Best!

course-preview

275 words per page

You essay will be 275 words per page. Tell your writer how many words you need, or the pages.


12 pt Times New Roman

Unless otherwise stated, we use 12pt Arial/Times New Roman as the font for your paper.


Double line spacing

Your essay will have double spaced text. View our sample essays.


Any citation style

APA, MLA, Chicago/Turabian, Harvard, our writers are experts at formatting.


We Accept

Secure Payment
Image 3