CMSC 461: Database Management Systems Spring 07Course ProjectInstructor: Parag Namjoshi Due: May 13, 20071 ObjectivesTo analyze the requirements, design, implement, d ocument and test a database applicationto automate an airline database system. The database system should perform the followingbasic functions:1. Let staff make entries when a passenger makes reservations on a flight.2. Let staff enter flight information before and after the flight including flight schedule,passenger check–in etc.3. Let staff enter information on aircraft maintenance and d etails.The users of the application are staff members of the airline.2 Information needsIn an airline database system, a number of different types of information need to be main-tained. They are classified accord ing to the activities that take place:• When a flight is scheduled, the following information is needed:– Flight number– Date– Aircraft type(from those provided1)– Source city and state(from list of cities provided2)– Destination city and state(from list of cities provided)– Scheduled departu re time(s) and date(s)– Scheduled arrival time(s) and date(s)0The project was originally designed by Prof. Kalpakis. A few changes were made by the instructor.1The possible types of aircraft are: Boeing 727, 737, 747 and Airbus A300, 320, 3402The cities in which the airline operates: New York, Washington D.C., Baltimore, L.A., San Francisco,Seattle, Chicago, Newark, Detroit, Boston, Raleigh, Tucson, Columbus, Tampa, Houston, Las Vegas, Min-neapolis, Atlanta, Pittsburgh and TorontoCourse P roject-I• Flight crew and other flight information has to be entered:– Flight number– Date– Flight captain– Flight co–pilot– Flight engineer– Head steward/hostess– Number of seats booked– Fare information– Number of passengers on board– Fuel statusIt is assumed that none of the flight crew change for the entire duration of the flight.• The following information on the flight passengers is required:– Flight Number– Date– Social Security Number– First and last name– Age– Reservation status– Flight Class– Seat number– Amount charged– Contact address• For each aircraft owned/leased by the airline, the following information needs to bemaintained:– Aircraft identification number– Bought/leased– Capacity of aircraft– Year of manufacture– Year of acquisition– Model number– Checked by– Date of last service– Category of aircraft serviceCourse Project-II– Problems registered– Last FAA inspection date– Fu el requirements• For the pilots, flight engineer and stewards/air–hostesses, the following information isneeded:– SSN– First and last name– Contact address– Qualifications– Year of joining– salary– Aircraft to fly/service– Date of last health checkup• Address should be maintained in the following format wherever appropriate:– Street– Apartment number– City– State– Zip– Telephone numbers(home and office)– Email address3 FunctionsThe database application should be able to hand le the occur rence of the following events:• When a flight is scheduled, the following information is entered:– Flight number– Source and destination cities and state(s)– Departure and arrival time(s) and date(s) at each city– Crew member information in the form∗ SSN∗ Duty• When a passenger books a ticket, the following information is entered:– SSNCourse Project-III– Name– Contact address– Ticket type– Amount paid– Status of reservation(Confirmed/Waiting with waiting number)Please note that multiple people may be attempting to reserve the tickets f or the sameflight at the same time. Since our airline does not allow overbooking, the system isthe responsible to ensure that we do not sell more tickets than the seating capacity.• Allow a passenger to cancel a ticket.• Before a flight’s departure, the following information is entered/modified:– Departure time(s) and date(s) may be changed if the flight is re–scheduled– Arrival time(s) and d ate(s) may be changed if th e flight is rescheduled– Fu el position– Ground engineer’s pre–flight check report(free text format)– Captain’s pre–flight report(free text format)– When a passenger checks in, the information entered is:∗ SSN∗ Seat number assigned• The following in formation needs to be entered/modified after the journey’s comple-tion:– Number of miles flown– Duration of flight– Captain’s post–flight report– Ground engineer’s post–flight check report– if flight was on time or not• Find flights which are full.• Find three h ighest paid employees.• Find routes which are always full.• Find flights which were late.• List the captains who were late last month, sorted by number of times they were late.• Give a raise of 2% to p ilots which were late fewer than 5 times last year. Give a raiseof 5% who were never late.• Check if a particular city can be reached from a given city w ithin specified number(or fewer) of stopovers.Course Project-IV4 Deliverables4.1 CodeAll code and scripts necessary to run the project must be submitted as a single zip file.4.2 Project ReportThe project is to be done in five phases. The Project report should have a separate sectionfor each p hase. The project ph ases are:1. Analysis of the requirements of the project and a high–level description of the tasksinvolved.2. This ph ase involves the following• Develop an Entity–Relationship model detailing the relations involved• Identify the attributes of the entities and the r elations along with the primarykey for each entity• List the constraints for each relation and entity• Give details of the procedures that you will implement in the database applica-tion.You should be able to explain the reasons for the p articular design approach you havechosen.3. This ph ase involves the following:• Design an Oracle3database based on the design developed in section B.• Implement SQL tables for the relations and the constraints. Maintain s cripts forthe creation of tables.• Justify your design choices.4. This ph ase consists of the following:• Write Java4co de to access, update and administer SQL tables made.• Develop a user interface, using which each of these functions should be performed.• Create indexes for the database ap plication. Justify the reasons.• Integrate the user interface and rest of the database application.5. Make a user’s