CSC3170 Tutorial 2

1
CSC3170 Tutorial 2
-- Project Specification of a Flight Booking
System

• Yue Wang
• Sep 18, 2007

2
Outline

• Introduction
• System description
• Database
• Functions
• User Interface Stubs

3
Introduction

• System
• You need to implement a database application for
  clerks of a travel agency booking tickets for
  their passengers
• Programming
• Language Java SDK 6.0 (with JDBC)
• Testing Platform Windows XP
• Tutors will set up a database server and provide
  you a raw sample data set

4
Introduction Project Milestones
Phase 1
10
Sep 29

• ER Diagram (hard-copy)
• a suggested answer will be released after the
  deadline

Phase 2
10
Oct 9

• Your database application with stubs (soft-copy)
• Programming in Java

Phase 3
15
Oct 23

• Relational schema and proof of 3rd normal form
  (hard-copy)
• a suggested answer will be released after
  the deadline
• a sample data set will be released after the
  deadline

Phase 4
65
Nov 23

• Database application (soft-copy)
• Insert the sample data set into DB
• Implement system functions in Java and JDBC
• Final report Demo (hard-copy)

5
Introduction Student Grouping

• Groups of at most 3 students
• We suggest 2 students each group
• Extra work for groups of 3 students (2 more
  system functions)
• Form your groups online at course homepage
• Deadline Sep 20, 2007
• Students who do not form groups after the
  deadline will be assigned to groups of 1 person

6
System Flight Booking System

• A system for clerks of a travel agency booking
  tickets for passengers
• All information of
• Flight
• Booking
• Ticket
• needs to be stored and accessed online

7
System Flight Info

• Flight
• Flight ID
• Airline
• DepartCity
• ArrivalCity
• DepartTime
• ArrivalTime
• Price
• Number of Seats available

8
System Booking Info

• Booking
• Booking ID
• Passenger Name
• FromCity (Passengers source)
• ToCity (Passengers destination)
• DepartAfter
• ArrivalBefore
• Passenger Phone
• Passenger Credit Card No
• For simplicity, a booking contains only one
  passenger.

9
System Ticket Info

• Ticket
• Booking ID
• Passenger Name
• Flight ID
• Airline
• DepartCity
• ArrivalCity
• DepartTime
• ArrivalTime
• Fee (flight price 20 tax)
• Issue Date
• One ticket contains only one flight for a
  passengers journey.

10
System Transit Flight

• A booking may have multiple potential itineraries
• The passenger will choose one itinerary finally
• One itinerary may have multiple flights due to
  transit flight
• E.g., Itinerary 1 Flight 1 (Hong Kong - Tokyo)
  and then Flight 2 (Tokyo - Atlanta)
• For simplicity, you can only consider at most one
  transit on a single-trip journey

11
System Constraints

• Seat constraint
• There should be available seats for each flight
  of an itinerary
• Location constraint
• Bookings FromCity Flight 1s DepartCity
• ( Flight 1s ArrivalCity Flight 2s
  DepartCity ) - if there is a transit
• Flight 2s ArrivalCity Bookings ToCity
• Time constraint
• Bookings DepartAfter
• (
• transit

12
System Functions

• Administrator Functions
• Booking Functions
• Evaluation Functions

13
System Administrator Functions

• Create all table schemas in the database
• We will set up a online DB server and provide a
  raw data set by Phase 3
• Delete all table schemas from the database

14
Working Flow of Booking Flights
15
System Booking Function 1

• Search for single-trip flights
• Input
• FromCity, ToCity
• e.g. Hong Kong, Atlanta
• DepartAfter, ArrivalBefore
• e.g. Sep 18, 800 am, Sep 20, 000 am
• Output
• List flights of all available single-trip
  itineraries
• e.g.
• Itinerary 1 Hong Kong - Atlanta (direct)
• Itinerary 2 Hong Kong - Tokyo - Atlanta (with a
  transit)

16
System Booking Function 2

• Search round-trip flights
• Actually, 2 single-trip bookings
• Input
• FromCity, ToCity
• DepartAfter, ArrivalBefore for 1st trip
• DepartAfter, ArrivalBefore for 2nd trip
• Output
• List flights of available round-trip itineraries

17
System Booking Function 3

• Create a Booking
• Input (some already in Function 1 or 2)
• Booking ID (auto-generated)
• Passenger Name
• FromCity
• ToCity
• DepartAfter
• ArrivalBefore
• Passenger Phone
• Passenger Credit Card No

18
System Booking Function 4

• Issue backup one-trip tickets
• The Passenger will choose an itinerary, if any
• Then the clerk should issue and backup the
  tickets
• Input Booking ID and Flight ID
• Output Ticket
• An itinerary may contains two flights. In this
  case, the clerk should generate 2 tickets.

19
System Booking Function 5

• Change the schedule of a flight
• Input New DepartTime, New ArrivalTime
• Output List all affected Bookings
• Whose tickets will violate Time Constraint

20
System Evaluation Function 1

• Show all information of the tables of your
  database
• For tutors to check your homework

21
System Evaluation Function 2

• Show the total sales within a period
• Input a period (e.g. 2007-6-1 to 2007-9-1)
• Output the total fee () of the issued tickets
  within the period

22
System Evaluation Function 3

• Show the N most popular cities by their total
  sales
• Input N
• Output N most popular cities
• The popularity of a city is defined as the total
  sale of the Bookings whose ToCity is the city.

23
System Interface Requirements

• Command line user interface is OK
• e.g.
• QueryFlights FromCity, ToCity, DepartAfter,
  ArrivalBefore

24
System Stubs

• A stub
• has the structure of functions
• doesnt do the details of your functions
• can be executed
• We suggest you to
• Implement your system (e.g. user interface) as
  much as possible in Phase 2
• leave real querying functions in Phase 4

25
System a stub example

• Example
• public int add (int n1, int n2)
• // Temporarily, display hard-coded information
  only
• return 12

26

• Q A
• Thanks!