Fly igh Airline Ai r l i e Res Reser erva vatt i on Syst yst em
Submitted By: Artria Grace Alimurung 0533-0511i3291
IT18 IT18 Data Databas base e Mana Managem gement ent Syste Syste
I.
2
Fly High Airlines
STRATEGY AND ANALYSIS
Description of Current Business Activities Fly High Airlines is a orth orthco comi ming ng airl airlin ine e comp compan any y that that off offer erss lo al flights at an afford affordab able le rate. rate. The compa company ny i initia initially lly using using Micros Microsoft oft Excel Excel to monito monitorr flig flig t schedules, add passengers and process payme ts. This current current system system is considered considered incomp tent because of the lack of interface and user-co ntrol. To compete with other ival companies, they must develop first an ea y-to-use GUI for thei theirr empl employ oyee eess to to eas easily ily sear sear h fli fligh ghts ts,, pas passe seng nger erss and and book bookin ings gs - resu result ltii g to to a fast and reliable reliable customer customer service. service.
Description of Operations In Fly High High Airli irline ness Data Datab base System, there are 9 tables which are: ContactPerson
This table contains the p rson rsonal al info inform rmat atio ion n of the the pers person on book bookin ing g a fli ht. This includes the uniq unique ue cont contact actID, ID, th the chosen flight/s, the contact person’s name, passport number, birthday, sex, address, c ntact number and e-mail address.
Guest Guest (Passen (Passenger ger))
This This table table includ includes es the passen passenger’ ger’ss inform informati ation on such such as the the uniq uniqu u
guestnum, the
corresponding contactID that made the reserva reservation, tion, the guest’ guest’ss passpo passpo t number, name and birthday. Flight
This contains the flight number umber,, the the fligh flightt class, class, the flight flight schedu schedules les,, t e departure and arriva arrivall place, place, and and the the bas base fare.
2
IT18 Database Management Syste
2
Fly High Airlines
Class
This contains the flight class code and its corresponding description (Fli htDesp).
Mode of Payment
This contains the Mod ID and its equivalent description of the m de of payment (ModeType).
Add-On
This contains the Add nID and its equivalent description (AddOn ype) and price (AddOnPrice). The AddO is optional.
Booking
This table contains the booked flight number, the date of booking, and the contact person’s unique confir ation number. After acquiring the confirmation number, the contact person may now proceed to payment.
Payment
This table contains the unique transaction number, the status of pa ment, the total amount, the modeID and add-on ID.
Ticket
This table contains the guest’s ticket number, the guestnum, and the onfirmation and transaction numbers.
This system can also view the reports of what users ask like Search Flights b Departure Date and by Place of Destination, Vi ew Booking and Payment, View Passengers b Flight, and the Masterlist of Contact and Guest.
3
IT18 Database Management Syste
2
Fly High Airlines
Business Rules and Constraint Since the author of this paper only has minimal information on the general rules and government regulations of airli e management, she has referenced some c ncepts from the business rules of Cebu Pacific Ai . GENERAL RULES: 1. Any person, under 18 ye rs of age, cannot make a ticket reservation. 2. The airline will carry a p ssenger only (a) if he is named in the Itinerary Receipt, (b) if his booking is confirmed in the
eservations system and (c) upon presentation of proof of
identification and such valid tra el documents as may be required by applicabl law. 3. The airline serves the right to cancel a reservation or refuse the carria e of a passenger if the fare and other amounts d e have not been paid in full and the passenge is unable to pay the balance upon request.
RESERVATIONS: 4. The airline may allow t e transfer of a flight reservation and change of name of any passenger in the itinerary receipt under the following conditions:
the change is requested
the fare paid by or for the substituted passenger is not considered a promotional fare.
ore than 24 hours before flight departure time and;
5. A passenger may cancel his entire reservation not flown at any time up to more than 24 hours prior his scheduled depa ture; otherwise, the flight is considered flow . The passenger may not rebook the flight or a ply for the creation of a travel fund if the fli ht is considered flown and the fare paid will be f rfeited by the airline.
4
IT18 Database Management Syste
2
Fly High Airlines
cancellation of the reservation, the airlin
will allow the
REBOOKING, REFUND 6. Following the voluntar
passenger to rebook a flight onl if:
the request to rebook is
the passenger pays the r booking fee;
the passenger pays the f re difference, where the new flight is in a fare class higher than
ade more than 24 hours before flight departure time;
that of the cancelled flig t. SCHEDULES, CANCELLATION S, DELAYS 7. The airline may change t any time and from time to time the regular schedule of any flight or postpone a flight to any day other than the scheduled day. 8. The airline may cancel, t rminate or delay any flight, or suspend the op ration of a flight route at any time after a reservation has been made.
5
IT18 Database Management Syste
2
Fly High Airlines
Normalization 1NF CONTACTPERSON (PassportNu , Name, Birthday, Sex, ContactNum, EmailAdd, Address, Guest) FLIGHT (FlightNum, FlightClass , DepDate, DepTime, DepPlace, ArrDate, ArrTime, ArrPlace, BaseFare) BOOKING (ConfirmationNum, D teBooked, Status) PAYMENT (TotalAmt, Status, M deofPayment, AddOn)
2NF CONTACTPERSON (ContactID, PassportNum, Name, Birthday, Sex, Contact Num, EmailAdd, Address) GUEST (GuestNum, ContactID, P assportNum, Name, Birthday) FLIGHT (FlightNum, FlightClass , DepDate, DepTime, DepPlace, ArrDate, ArrTime, ArrPlace, BaseFare) TICKET (TicketNum, FlightNum, lightClass) BOOKING (ConfirmationNum, TicketNum, DateBooked, Status) PAYMENT (TransactionNum, TicketNum, TotalAmt, Status, ModeofPayment, A dOn)
3NF CONTACTPERSON (ContactID, PassportNum, Name, Birthday, Sex, Contact Num, EmailAdd, Address) GUEST (GuestNum, ContactID, P assportNum, Name, Birthday) FLIGHT (FlightNum, FlightClass , DepDate, DepTime, DepPlace, ArrDate, ArrTime, ArrPlace, BaseFare) TICKET (TicketNum, ConfirmationNum, TransactionNum, GuestNum)
6
IT18 Database Management Syste
2
Fly High Airlines
BOOKING (ConfirmationNum, TicketNum, DateBooked) PAYMENT (TransactionNum, To alAmt, Status, ModeID, AddOnID) ADDON (AddOnId, AddOnType, AddOnPrice) MODEOFPAYMENT (ModeID, ModeType)
Entity Relationship Diagra
1
1
is reserved by
is found in
∞
∞
1 olds
Reserves ∞
for
1
1
∞
Can have
is found in
1 is found in
∞ ∞
can
1
1
∞
hold
∞
Can have ∞
7
IT18 Database Management Syste
2
Fly High Airlines
Relational Schema (Based n ERD)
8
IT18 Database Management Syste
II.
2
Fly High Airlines
DESIGN
TABLE SPECIFICATION Table Name:
ContactPerson
Primary Key:
ContactID
Foreign Key:
FlightNum
Field Name
Data Type
Format/Field
Descrip ion
ContactID
Text
10
The unique ID assigned t each contact person
FlightNum
Text
10
Flight nu ber
PassportNum
Text
15
Passport number
Name
Text
50
Full na e
Birthday
Date/Time
General Date
Date of irth
Sex
Text
7
Gend r
ContactNum
Text
20
Contact Number (e.g mo ile number, landline)
EmailAdd
Hyperlink
Address
Text
Table Name:
Guest
Primary Key:
GuestNum
Foreign Key:
ContactID
E-mail address 100
Present a dress
Field Name
Data Type
Format/Field
Descrip ion
GuestNum
Number
Long Integer
The unique ID assigned to each guest
ContactID
Text
10
The unique ID assigned t each contact person
PassportNum
Text
15
Passport number
Name
Text
50
Full na e
Birthday
Date/Time
General Date
Date of irth
9
IT18 Database Management Syste
Table Name:
Flight
Primary Key:
FlightNum
Foreign Key:
FlightClass
2
Fly High Airlines
Field Name
Data Type
Format/Field
Descrip ion
FlightNum
Text
10
Flight nu ber
FlightClass
Number
Long Integer
Code for fli ht class
DepDate
Date/Time
General Date
Date of de arture
DepTime
Date/Time
Short Time
Time of de arture
DepPlace
Text
15
Place of de arture
ArrDate
Date/Time
General Date
Date of arrival
ArrTime
Date/Time
Short Time
Time of arrival
ArrPlace
Text
15
Place of arrival
BaseFare
Text
5
Flight base fare
Table Name:
Class
Primary Key:
FlightClass
Foreign Key:
none
Field Name
Data Type
Format/Field
Descrip ion
FlightClass
Number
Long Integer
Code for fli ht class
FlightDesp
Text
15
Description of flight class
10
IT18 Database Management Syste
Table Name:
Booking
Primary Key:
ConfirmationNu
Foreign Key:
FlightNum
2
Fly High Airlines
Field Name
Data Type
Format/Field
Description
ConfirmationNum
Text
10
DateBooked
Date/Time
General Date
Confirmation number iven to the contact person that has booked a flight Date of b oking
FlightNum
Text
10
Flight number
Table Name:
Payment
Primary Key:
GuestNum
Foreign Key:
ContactID
Field Name
Data Type
Format/Field
Descrip ion
TransactionNum
Text
10
Transaction number of the payment
Status
Text
10
Status of p yment
ModeID
Number
Long Integer
Code for the mode of payment
TotalAmt
Text
6
Total Amount
AddOnId
Number
Long Integer
Code for th add-on
Table Name:
Ticket
Primary Key:
TicketNum
Foreign Key:
ConfirmationNu , TransactionNum
Field Name
Data Type
Format/Field
Description
TicketNum
Text
10
TicketNum assigned to eac guest after payment
ConfirmationNum
Text
10
TransactionNum
Text
10
Confirmation number given to the contact person that has b oked a flight Transaction number of the payment
GuestNum
Number
Long Integer
The unique ID assign d to each guest
11
IT18 Database Management Syste
Table Name:
ModeOfPayment
Primary Key:
ModeID
Foreign Key:
none
2
Fly High Airlines
Field Name
Data Type
Format/Field
Descrip ion
ModeID
Number
Long Integer
Code for the mode of payment
ModeType
Text
20
Description of mo e of payment
Table Name:
AddOn
Primary Key:
AddOnID
Foreign Key:
none
Field Name
Data Type
Format/Field
Descrip ion
AddOnId
Number
Long Integer
Code for th add-on
AddOnType
Text
50
Description f add-on
AddOnPrice
Text
5
Price of a d-on
12
IT18 Database Management Syste
2
Fly High Airlines
Screen Designs
A. Forms
13
IT18 Database Management Syste
2
Fly High Airlines
14
IT18 Database Management Syste
2
Fly High Airlines
15
IT18 Database Management Syste
2
Fly High Airlines
Reports PARAMETER VALUE: Departure Date EXAMPLE INPUT: 11-01-2011
PARAMETER VALUE: Arrival Pla e EXAMPLE INPUT: Cebu
16
IT18 Database Management Syste
2
Fly High Airlines
PARAMETER VALUE: Contact N me EXAMPLE INPUT: Barney Stinso
PARAMETER VALUE: FlightNum EXAMPLE INPUT: flight2
17
IT18 Database Management Syste
2
Fly High Airlines
18
IT18 Database Management Syste
III.
2
Fly High Airlines
SCRIPT LISTING
DISPLAY: ContactID, Name, GuestNum, Name PARAMETER VALUE: None SELECT C.ContactID, C.Name, G.GuestNum, G.Name FROM ContactPerson AS C, Gues AS G WHERE C.ContactID=G.ContactID;
DISPLAY: FlightNum, DepDate, DepTime, DepPlace, ArrDate, ArrTime, ArrPlace, GuestNum, PassportNum, Name
PARAMETER VALUE: FlightNum SELECT Flight.FlightNum, Flight.DepDate, Flight.DepTime, Flight.DepPlace, Guest.Gu stNum, Guest.PassportNum, Guest.Nam , Flight.ArrDate, Flight.ArrTime, Flight.ArrPlace FROM Flight INNER JOIN (Contac Person INNER JOIN Guest ON ContactPerson.ContactID = Guest.ContactId) ON Flight.FlightNum = ContactPerson.FlightNum
DISPLAY: FlightNum, FlightClass, epDate, DepTime, DepPlace, ArrDate, ArrTime, ArrPlace, BaseFare PARAMETER VALUE: DepDate SELECT Flight.FlightNum, Flight.FlightClass, Flight.DepDate, Flight.DepTime, Flight.DepPlace, Flight.ArrDate, Flight.ArrTime, Flight.ArrPlace, Flight.BaseFare FROM Flight WHERE (((Flight.DepDate)=[Enter Date of Departure (mm-dd-yyyy):]));
19
IT18 Database Management Syste
2
Fly High Airlines
DISPLAY: FlightNum, FlightClass, epDate, DepTime, DepPlace, ArrDate, ArrTime, ArrPlace, BaseFare PARAMETER VALUE: ArrPlace SELECT Flight.FlightNum, Flight.FlightClass, Flight.DepDate, Flight.DepTime, Flight.DepPlace, Flight.ArrDate, Flight.ArrTime, Flight.ArrPlace, Flight.BaseFare FROM Flight WHERE (((Flight.ArrPlace)=[Enter Place of Destination:]));
DISPLAY: ContactID, PassportNu , Contact-Name, GuestNum, Guest-PassportNum, uest-Name, Birthday, ConfirmationNum, DateBooked, TicketNum, TransactionNum, Status, FlightNum, DepDate, DepTime, DepPlace, ArrDate, ArrTi e, ArrPlace
PARAMETER VALUE: ContactPer on.Name SELECT ContactPerson.ContactID, ContactPerson.PassportNum, ContactPerson.Nam , Guest.GuestNum, Guest.Passpor Num, Guest.Name, Guest.Birthday, Booking.Confir ationNum, Booking.DateBooked, Ticket.Tick tNum, Payment.TransactionNum, Payment.Status, Flight.FlightNum AS FlightNum_Flight, Flight.FlightClass, Flight.DepDate, Flight.DepTime, Flight.DepPl ce, Flight.ArrDate, Flight.ArrTime, Flight.ArrPlace FROM (ModeOfPayment INNER JOIN (AddOn INNER JOIN Payment ON AddOn.AddO ID = Payment.AddOnId) ON ModeOfPayment.ModeID = Payment.ModeID) INNER JOIN (( ontactPerson INNER JOIN Guest ON ContactPerson.ContactID = Guest.ContactId) INNER JOIN (Flig t INNER JOIN (Booking INNER JOIN Ticket ON Booking.ConfirmationNum = Ticket.ConfirmationNu ) ON Flight.FlightNum = Booking.FlightNum) ON Guest.GuestNum = Ticket.GuestNum) ON Payment.TransactionNum = Tick t.TransactionNum WHERE (((ContactPerson.Name) [Enter Name of Contact Person to view payment details: ]));
20
IT18 Database Management Syste
2
Fly High Airlines
CONCLUSION: The database system uses basic controls such as adding, deleting, viewing, updating, and searching. This system is not intended to be used for business purposes si ce it is deficient in controls. It does not perfor
automatic calculation of payments due to the creator of this
system’s limited knowledge of using Microsoft Office Access 2007. Further ore, security of data is compromised because o the lack of login-password control. Learning MS Access co mands for computation is highly recommended for future developers of reservation syste s.
Source for Business Rules and Constraints: http://www.cebupacificair.com/aboutus/terms-and-conditions.html
21