Requirements specification

This system will manage a small regional commuter airline network. There are daily flights between different cities that fly at the same time each day (for example, there is a daily flight from Islip, NY to Baltimore, MD at 1:00pm).  And they arrive at their destinations at the same time each day.  Each flight is assigned a particular type of aircraft, such as a Boeing 727, and each aircraft type has a particular set of seat numbers and seating capacity.  Also each flight has a certain fare which will depend on the seat class (coach, business, or first class), and also on the day of the week it flies.

The system must maintain information on passenger reservations.  A reservation includes the passenger name, address, contact phone number, credit card type and number and email address, the flight number and flight date, the seat number, the meal type the passenger will be served (kosher, vegetarian, etc.). Notice it is highly likely that the same passenger may make multiple reservations on multiple flights over a reasonable period of time.

The airline plans to offer an online system to its prospective passengers, allowing them to enter preferences concerning reservations they want to make.  For example, a passenger will be able to enter a request for a reservation on a flight that goes from Islip to Boston next Tuesday or Wednesday morning.  The passenger wants to fly business class, eat a kosher meal and sit in a window seat.  So the options are: a desired source and destination, a preferred time to fly, a meal preference (kosher, vegetarian, regular), a preferred seat (aisle, window), and a preferred flight class (coach, business, first class.)

Operations that will have to be performed include the following.  A prospective passenger can enter or modify a reservation preference.  A prospective passenger can query to see if a particular flight has not been canceled and has available seats in various classes.  An airline representative can add new flights with all their necessary information of source and destination location and times, aircraft type assigned to a flight, fares, etc.  An airline representative can alter properties of a flight, and in particular can cancel a particular flight, such as next Thursday's flight from Islip to Boston.  An airline representative can also query to find what passengers have reservations on what flights.

There will also be the ability for a passenger to see what flights meet his or her stated preferences, or to see whether a particular flight is full.  There will also be a facility to make a reservation.

Assignment 1 (Due 9/26/00)

• To Do

Your task for the first assignment is to create an Entity Relationship (E-R) diagram for the data required to support this application. This diagram (model) should support all the aspects mentioned in the requirements specification.
 
 
• Delivery form

This will be a clearly drawn document (on paper or on computer) using the modeling figures of the E-R framework.  You must work individually and the work you turn in must be your own.

Assignment 2 (Due 10/17/00)

• To Do

For the second assignment you have to:
 
• Translate the ER-entities and the ER-relationships  to RDB-relations (RDB = relational data base).
• Write the SQL code that creates for each relation an SQL table.
• Write the SQL code that loads the tables with data corresponding to the following ascii file.


The starting point of this assignment is either our updated design, (ps, jpg) or your design from Assignment 1, provided it is close enough to ours or it contains the same amount of information. The relations (e.g. reserve) that have no explicit loading information in the ascii file should be loaded by you in a way that is consistent with the preferences data (you have some freedom).

Send an email to your instructor to get an account in the transaction lab. Get accustomed with the sybase SQL (see the links in the main page) and write the code. Have fun :)
 
 

• Delivery form

This will be a demo of an SQL program.  You have to run it to create and load the database. You have to work in groups of two students.  You are allowed to work with a student in the other section. The delivery form should be a dump (printout) of the relations (tables) schema and of their content. For instructions see here.

Assignment 3 (Due 11/14/00)

• To Do

In this assignment you have to update and query the data base schema you have created in assignment two according to the scenario below.  Each query should be an SQL statement that will answer the indicated question for ANY database instance, e.g., for any set of flights, aircraft, reservations, passengers, preferences....
 
1. Delete all aircrafts, all trips and all reservations.
2. Insert the following Aircrafts (model,capacity1, capacity2) where capacity1 and 2 are business class and coach class, the first seat is a window seat

in each class and the second seat is an aisle seat if the capacity of that class is two:
(Boeing 727,1, 2)
(Boeing 737,1, 2)
(Boeing 747,2, 3)
(Boeing 757,3, 3)
3. Modify the existing flights to use the new aircrafts. Modify the flight (1807,BOS,PIT,6:30am,8:17am) to (1807,BOS,PIT,7:30am,8:30am).
4. Insert the following Flights (no,srcAirport,destAirport,deptTime,arrTime):
(6801,BOS,PHL,7:30am,8:30am)
(1141,PIT,PHL,8:50am,9:30am)
(1808,PHL,LGA,10:00am,11:30am)

Flights 6801 and 1141 always use Boeing 727. Flight 1808 always uses Boeing 747.
Coach class costs $100 on weekends.
Coach class costs $150 on working days.
Business class costs $500 on weekends.
Business class costs $700 on working days.

5. Create trips for the following dates: 10/1/00, 10/22/00, 11/08/00, 12/24/00.
6. Add the following Passenger (name, address, phoneNo, email, ccType, ccNo)
(John John
Rocky Point, NY 11778
631-821-2324
john@sunysb.edu
VISA
4040320145890021)
with the following Preferences (id, srcAirport,destAirport,date,timeRange,meal,class,position)
(1,LGA,PIT,10/01/2000,morning,kosher,business,window)
(5,LGA,PIT,10/22/2000,morning,kosher,business,window)
(6,LGA,PIT,11/08/2000,morning,kosher,business,window)
(7,LGA,PIT,12/24/2000,morning,kosher,business,window)
(8,PIT,PHL,10/01/2000,morning,kosher,business,window)
(9,PHL,LGA,10/01/2000,morning,kosher,business,window)
7. Write a query that finds all matching seats for a passenger preference.
8. Create reservations by picking one result in the above query.  Delete a preference after making the reservation.
9. Write a query that for each trip displays passengers assigned to a same seat..
10. Write a query that for each trip returns all free seats.
11. Write a query that for each meal  returns total number of  reserved seats by trip.
12. Write a query that for each meal type returns a list (Class, SeatNo, Name). Sort by class and seat number.
13. Write a query that returns the trips and the fares that match a preference.
14. Write a query that returns all the passengers that flew on all trips from LGA to PIT.
15. Write a query that returns all flights scheduled after the current day and having a fare less than or equal to the lowest fare a passenger has already had for that flight.
16. Write a query returning the average money the company takes in per flight (considering all trips).
17. Write a query returning the number of legs and fare for all round trips starting in LGA.
18. Is it possible to write a query computing the number of legs and fare between any two airports, no matter what the current content of the database is? Justify your answer.
 
• Delivery form

This will be an SQL program.  You have to run it to update and query the database you created in assignment two. The delivery form should be a dump (printout) of the content of tables after the updates and the result of the queries. You should also give the SQL code. Continue working with the partner you already had for the second project.

Assignment 4 (Due 12/12/00)

• To Do

 

 

In this assignment you have to create a graphical user interface (GUI) for your database in Java and link it to the SQL server by using the JDBC API. Proceed as below:

The GUI
 

Use IBM Visuual Age tool to create an applet that contains a  nested tabbed pane as shown in this file. IBM Visual age allows you to create the applet and the panes in a drag and drop fashion without writing any line of Java code!

You have to create the applet, the tabbed panes and associated Java file in one session!  Then you have to export the java file in a directory you own. Between sessions, the tool will erase all your data. You cannot export the state of your visual editting (IBM bug) and therefore you cannot continue the visual editing next time.


The JDBC Actions
 

Open the Java file produced in the first step and edit the actions associated to the buttons in your panes by entering JAVA and JDBC code. This code has to provide a view of the state that you obtain from the SQL server by issuing the appropriate SQL query. Instructions on how to use Visual Age and JDBC can be found under these names on the translaction lab  home page.

 
• Delivery form

This is a demo.