nagyist / airline_project_final Goto Github PK

README:

Using the interface:

Open File AirlineInterface.jar

You will be prompted to select an option.  You can create an account if you wish.  If you want to login to an count, you can use the following:

Login using the following username and password combinations.

MANAGER:
USERNAME: jedenastexi PASSWORD: nvKYc547
PILOT:
USERNAME: hromihbe PASSWORD: V8wZ2xa3
CUSTOMER:
USERNAME: kdr213 PASSWORD: cse241db
(Or make your own customer account.)

There are still some bugs in the code, but most of the functionality is there.  I know there are still formatting issues, as well as some exceptions (sorry.)

Follow the interface by entering the appropriate information.  Customers can search and book flights, see their bookings, and manage their credit cards o file.  Managers can search for flights.  Pilots can see which flights they are scheduled to fly.

I wish I could have gotten more functionality for the managers and pilots, but at this point it's time to accept that it will never be perfect.

Airline Facts:

We have 40 Airplanes in our fleet.
The breakdown of our fleet is as follows:



MAKE                 MODEL                NUMBER OF AIRPLANES    
-------------------- -------------------- ---------------------- 
AIRBUS               A320-200             4                      
AIRBUS               A350-800             3                      
AIRBUS               A380-800             4                      
AIRBUS               A300-600R            9                      
AIRBUS               A300B4-200           2                      
AIRBUS                                    22                     
BOEING               747-400              8                      
BOEING               777-300              3                      
BOEING               747-100B             7                      
BOEING                                    18                     
                                          40 

NUMBER OF LEGS: 1304

 

DATA GENERATION:

Most of the significant data generation code was written by me.  

The data items not generated by me were as follows:

Usernames: http://generator.my-addr.com/generate_usernames-free_username_generator_online_tool.php

Passwords: http://www.random.org/passwords/

Names: http://www.ohloh.net/p/janag (Java application)

All of my airport and route data was obtained from:
	http://openflights.org/data.html


Other than that, see my java class Generator to see how the rest was generated.

Most data is fairly accurate (Distance between airport - held in the ROUTE table, is calculated precisely, all of the customer's frequent flier miles are correct and are updated accordingly via the interface)


QUERIES

FLEET DATA:
select make, model, count(*) as "NUMBER OF AIRPLANES" 
from airplane natural join model 
group by rollup(make, model)


FLIGHT SEARCH EXAMPLE:
Search for all flights with 4 or less connections leaving from BWI airport:
     
WITH RECURSIVE_LEG (DEPT_AP_ID, ARRI_AP_ID, LEG_ID, DEP_TIME, ORIG_ARR_TIME, CUR_ARR_TIME, LEG_COUNT, AP_LIST, LEG_LIST) AS  
(SELECT B.DEPT_AP_ID, B.ARRI_AP_ID, A.LEG_ID, A.DEP_TIME, A.ARR_TIME, A.ARR_TIME, 
  1, CAST(B.ARRI_AP_ID||' '||B.DEPT_AP_ID||' '  AS VARCHAR(2000)),
  CAST(LEG_ID||' ' AS VARCHAR(2000))
FROM LEG A NATURAL JOIN ROUTE B
WHERE B.ARRI_AP_ID = 3849
UNION ALL
SELECT D.DEPT_AP_ID, R.ARRI_AP_ID, C.LEG_ID, C.DEP_TIME, R.ORIG_ARR_TIME, C.ARR_TIME, 
  R.LEG_COUNT + 1, CAST(R.AP_LIST||' '||D.DEPT_AP_ID||' '  AS VARCHAR(2000)), 
  CAST(R.LEG_LIST||' '||C.LEG_ID||' ' AS VARCHAR(2000))
FROM RECURSIVE_LEG R, (LEG C NATURAL JOIN ROUTE D)
WHERE R.DEPT_AP_ID = D.ARRI_AP_ID AND C.ARR_TIME < R.DEP_TIME - INTERVAL '40' MINUTE AND R.LEG_COUNT+1 < 6)

CYCLE DEPT_AP_ID SET cyclic_data TO '1' DEFAULT '0' 
SELECT R.DEPT_AP_ID, R.ORIG_ARR_TIME, R.ARRI_AP_ID, R.LEG_COUNT, R.AP_LIST, R.LEG_LIST  
      FROM RECURSIVE_LEG R
      WHERE R.LEG_COUNT-1 < 4

The above query uses recursion to work.  It's really cool!

SEARCH FOR FREQUENT FLIER MILES BY CUSTOMER ID:

SELECT SUM(FF_MILES), CUST_ID FROM BOOKING GROUP BY CUST_ID