In this project you will design a database for the following application, implement the design in ORACLE, and test your implementation with sample data entered by you.
The application required is for an international airport that wants to keep track of airlines, flights, passengers, baggage, etc. From the requirements for this database, suppose that the following information was collected.
- Each airline has a name, code, website, and a set of flights (routes) it operates.
- Each route has a number and is operated by some airline using a certain plane model.
- There are two kinds of routes, incoming and outgoing.
- Outgoing routes have a destination and a planned departure time (outT).
- Incoming routes have a source and a planned arrival time (inT).
- There are departures and arrivals corresponding to outgoing and incoming routes, respectively.
- A departure has a gate, departure date, and a planned departure time (depT)
- depT can be different from the corresponding outT.
- An arrival has a gate, arrival date, and a planned arrival time (arrT).
- arrT can be different from the corresponding inT.
- With each departure and arrival there is a set of passengers associated with it.
- For each passenger the airport records the name, date and place of birth, and driver’s license (DL) or passport number.
- Each passenger that arrives in the airport gets a fresh id.
- Each passenger that departs from the airport gets a fresh id, unless the passenger has a connection from an arrival; in which case, the id of the passenger from the arrival is used.
- The same person might be more than once a passenger (i.e. on different days). For the purposes of this database, he/she is considered a different passenger each time.
- Each passenger can have checked-in baggage. All baggage needs to be recorded for each passenger.
- Draw an E/R diagram for the given problem.
- Translate your E/R diagram into tables. Create the tables in ORACLE. Create the necessary constraints on your tables.
- Some more advanced constraints are:
- No passenger can be associated with more than two flights, which have to be connecting flights.
Remark. Connecting flights are a pair of an arrival and departure that corresponds to a pair of connecting routes (see 5.a), and the flights real time allow for a connection.
- No two flights can have the same gate (during a time interval of -1, +1 hour around their planned time)
- Create forms to populate (insert data into) your tables.
- However, no form to insert passengers and baggage is needed; they will be inserted in batches sent by the airlines. For this purpose you should create programs that populate the corresponding tables from text files.
- Create a form to perform a route deletion (demonstrate also automatic deletions due to foreign key constraints).
- Create forms/interfaces and queries for extracting the following information.
- Given an airline find all the routes it operates.
- Given a place (e.g. Toronto) find all the routes from and to that place.
- Given a time of the day find all the arrivals and departures around that time and print their status.
- Given a departure or arrival find all the passengers recorded for it. Print all the information about these passengers.
- For a given passenger in a flight find his/her baggage.
- Find a free gate for a flight (arrival or departure).
- Assign a flight as “done” and return the gate.
- Create SQL queries for extracting the following information.
- List all the possible connecting routes, i.e. pairs (r1, r2) of incoming-outgoing routes, such that the scheduled arrival time of r1 is not more than 12 hours earlier but not less than 1 hour earlier of the scheduled departure time of r2.
- Find all the passengers in transit.
- Find the top three persons with respect to the number of flights they have taken.
- For each (from, to) route, find the airline with the most delays.
- E/R diagram
- Table creation statements
- Table population statements
- SQL queries for extracting the required information
Each group should demo the functionality of implementation. The demos will take place during the last week of classes in the labs.
You can use HTML forms/tables backed by Java Servlets. The results of the above query requirements can be displayed as HTML tables.
The graphical interfaces do not need to be fancy. They only need to be functional.