System Design
IS3331 Data Management
Group Project (Part 1 & 2)
Final Report
Tutorial Group
L03
Group members
Lee Sze Yin, Timothy (51435230)
Hui Nga Wai, Kitty (51447436)
Cheng Ka Shing, Gary (51471880)
Leung Sin Hang (51442649)
Chui Kai Wing, Tom (51216738)
Section 1: External Schema
We selected “Airline Operation” as our project topic. The routine operation of the database system for airline operation involved three major individuals: Customer, Staff and Human Resources Manager. Here are some individual views and functions from three individuals related to the system.
Customer - Search for flight available between two airports - Search for a seat available on a particular flight - Purchase air ticket by providing his/her personal information - Select seat and class for his/her flight
Staff - Arrange the usage of the aircrafts - Schedule and arrange the flights - Enter customer information into the system when they buy a ticket - Calculate the price, surcharge and airport tax for customer - Issue air ticket to customer
Human Resources Manager - Arrange a roster for staff members - Calculate the working hours for each staff member - Monitor overtime working for each staff member - Consider the extension of contract for contract staff - Issue salary and wages
The major entities and relationships in the database system will be described in the following section.
Section 2: Conceptual Database Schema
[pic]
Figure 1: Entity-Relationship Diagram of an airline operation database
The airline operation database contains 13 entities: CUSTOMER, TICKET, FLIGHTDATE, FLIGHT, AIRPORT, STAFFROSTER, STAFF, FULLTIME, CONTRACT, FLEET, FLEETTYPE, SEAT and CLASS.
Section 3: Detailed description of all entities and relationships
Supertype and subtype entities – STAFF, FULLTIME and CONTRACT
[pic]
Figure 2: Supertype and subtype entities of STAFF, FULLTIME and CONTRACT
According to the Entity-Relationship Diagram (Figure 2), STAFF is a supertype entity with two
Group Project (Part 1 & 2)
Final Report
Tutorial Group
L03
Group members
Lee Sze Yin, Timothy (51435230)
Hui Nga Wai, Kitty (51447436)
Cheng Ka Shing, Gary (51471880)
Leung Sin Hang (51442649)
Chui Kai Wing, Tom (51216738)
Section 1: External Schema
We selected “Airline Operation” as our project topic. The routine operation of the database system for airline operation involved three major individuals: Customer, Staff and Human Resources Manager. Here are some individual views and functions from three individuals related to the system.
Customer - Search for flight available between two airports - Search for a seat available on a particular flight - Purchase air ticket by providing his/her personal information - Select seat and class for his/her flight
Staff - Arrange the usage of the aircrafts - Schedule and arrange the flights - Enter customer information into the system when they buy a ticket - Calculate the price, surcharge and airport tax for customer - Issue air ticket to customer
Human Resources Manager - Arrange a roster for staff members - Calculate the working hours for each staff member - Monitor overtime working for each staff member - Consider the extension of contract for contract staff - Issue salary and wages
The major entities and relationships in the database system will be described in the following section.
Section 2: Conceptual Database Schema
[pic]
Figure 1: Entity-Relationship Diagram of an airline operation database
The airline operation database contains 13 entities: CUSTOMER, TICKET, FLIGHTDATE, FLIGHT, AIRPORT, STAFFROSTER, STAFF, FULLTIME, CONTRACT, FLEET, FLEETTYPE, SEAT and CLASS.
Section 3: Detailed description of all entities and relationships
Supertype and subtype entities – STAFF, FULLTIME and CONTRACT
[pic]
Figure 2: Supertype and subtype entities of STAFF, FULLTIME and CONTRACT
According to the Entity-Relationship Diagram (Figure 2), STAFF is a supertype entity with two