System Design

Only available on StudyMode
  • Download(s) : 146
  • Published : February 21, 2013
Open Document
Text Preview

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 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....
tracking img