IS3331 Data Management
Group Project (Part 1 & 2)
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.
- 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
- 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
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 subtype entities: full time staff (FULLTIME) and contract staff (CONTRACT). For each staff in STAFF entity share same type of information like name (Staff_Name), position (Position) , date of entry (Date_Entry), Phone and Address. Another attribute call Staff_Type is used for distinguish two subtype of STAFF: FULLTIME or CONTRACT.
FULLTIME are those who work as a permanent employee in the company and enjoying fixed monthly salary (Monthly_Salary) under specified monthly working hours (Working_Hour). If full time staff works overtime, they can also receive extra per-hour wages (Overtime_Wage) according to their extra working hours on that month (Overtime_Hour).
CONTRACT are those who work for the company under terms specified in a contract. The terms usually included the agreed salary (Contract_Salary) and the expire date of the contract (ContractEndDate). Relationship between CUSTOMER, TICKET, FLIGHTDATE, FLIGHT and AIRPORT [pic]
Figure 3: Extracted ERD for CUSTOMER, TICKET, FLIGHTDATE, FLIGHT and AIRPORT
Each customer (CUSTOMER) have to provide their personal information (Name, Passport_No, Gender, Phone, Nationality) to create a new column in CUSTOMER table with an unique identity (Customer_ID) before they purchase a ticket.
Each customer (CUSTOMER) can purchase none or many air ticket (TICKET) from the airline, but one air ticket (TICKET) refers to exactly one customer (CUSTOMER) only. Once they purchase a new ticket, a new column will be created in TICKET table with a unique identity (Ticket_ID). Each TICKET record contains one customer’s information by using foreign key (Customer_ID) to identify which customer belongs to.
Each record in FLIGHT table refers to a flight under a unique flight number. It contains two airports code (Departure_Airport and Arrivial_Airport) and departure and arrival time (Departure_Time and Arrival_Time).
Since each FLIGHT can be repeated on different day (i.e....