Demo Data

The following data is for the demo of your online travel reservation system. You should also use this data to test the SQL DML statements you write for Project Assignment No. 2. Please input the data into your relational database system according to the schema design you produced for Project Assignment No. 1.
 

Important!

You may assume a booking fee of 10% for reservations.

Airlines

name
Air Berlin
Air Japan
Air Madagascar
American Airlines
British Airways
Delta Airlines
JetBlue Airways
Lufthansa
Southwest Airlines
United Airlines

Airports

airportName city country
Berlin Tegel Berlin Germany
Chicago O'Hare International Chicago Illinois
Hartsfield-Jackson Atlanta Int Atlanta United States of America
Ivato International Antananarivo Madagascar
John F. Kennedy International New York United States of America
LaGuardia New York United States of America
Logan International Boston United States of America
London Heathrow London United Kingdom
Los Angeles International Los Angeles United States of America
San Francisco International San Francisco United States of America
Tokyo International Tokyo Japan

Flights

FlightNo DaysOperating NoOfSeats AirlinesName
111 1010100 100 American Airlines
111 1111111 150 JetBlue Airways
1337 0000011 33 Air Madagascar

StopsAt

arrTime stopNo deptTime airportName FlightNo AirlinesName
2011-01-05 09:00:00 1 2011-01-05 11:00:00 LaGuardia 111 American Airlines
2011-01-05 17:00:00 2 2011-01-05 19:00:00 Los Angeles International 111 American Airlines
2011-01-06 07:30:00 3 2011-01-06 10:00:00 Tokyo International 111 American Airlines
2011-01-10 12:00:00 1 2011-01-10 14:00:00 San Francisco International 111 JetBlue Airways
2011-01-10 19:30:00 2 2011-01-10 22:30:00 Logan International 111 JetBlue Airways
2011-01-11 05:00:00 3 2011-01-11 08:00:00 London Heathrow 111 JetBlue Airways
2011-01-13 05:00:00 1 2011-01-13 07:00:00 John F. Kennedy International 1337 Air Madagascar
2011-01-13 23:00:00 2 2011-01-14 03:00:00 Ivato International 1337 Air Madagascar

Passengers

passengerName Address Phone Email
Jane Smith 100 Nicolls Rd, Stony Brook, New York 17790 555-555-5555 awesomejane@ftw.com
John Doe 123 N Fake Street, New York, New York 10001 123-123-1234 jdoe@woot.com
Rick Astley 1337 Internet Lane, Los Angeles, California 90001 314-159-2653 rickroller@rolld.com

Reservations

ResrNo TotalFare
111 $1200.00
222 $500.00
333 $3333.33

Includes

deptDate seatNo meal class legNo fromStopNo resrNo flightNo passName airlineName
2011-01-05 11:00:00 33F Chips Economy 1 1 111 111 John Doe American Airlines
2011-01-05 19:00:00 33F Chips Economy 2 2 111 111 John Doe American Airlines
2011-01-14 22:30:00 13A Fish and Chips First 1 2 222 111 Jane Smith JetBlue Airways
2011-01-13 07:00:00 1A Sushi First 1 1 333 1337 Rick Astley Air Madagascar

Bid History

ResrNo LegNo Bid Accepted?
111 1 $400 yes