Reference no: EM132920750
AIRLINE RESERVATION SYSTEM
1. Problem Statement
Objective
Designing the database, developing the queries and basic reports required for Airline Reservation System.
Abstract of the Project
We are Domestic Airline Company in India there is a (single) booking office. You are going to design a central air-reservation database to be used by all booking offices.
The flight has a unique flight number, air line code, business class indicator, smoking allowed indicator. Flight availability has flight number, date + time of departure, number of total seats available in business class, number of booked seats in business class, number of total seats available in economy class, and number of booked seats in economy class.
Customer has first & last name, mailing address, zero or more phone numbers, zero or more fax numbers, and zero or more email addresses.
Mailing address has street, city, province or state, postal code, Phone/fax. Email address has only one string, and no structure is assumed. A customer can book one or more flights. Two or more customers may have same mailing address and/or same phone number(s) and/or same fax number(s). But the email address is unique for each customer. First and last names do not have to be unique.
Booking has an unique booking number, booking city, booking date, flight number, date + time of departure (time is always in hours and minutes), date + time of arrival (time is always in hours and minutes ), class indicator, total price (airport tax in origin + airport tax in destination + flight price. The flight price for business class is 1.5 times of the listed flight price), status indicator (three types: booked. Cancelled - the customer cancelled the booking, scratched - the customer had not paid in full 30
days prior to the departure), customer who is responsible for payment, amount-paid-so far, outstanding balance, the first & last names to be printed on the ticket.
Functional components of the project
Design the normalized relational database using the following details.
You can make appropriate assumptions wherever required. Some of the attributes are given below with the restrictions on data it can contain. Find the required attributes for all the tables and create appropriate constraints on it. (For Ex. Primary key, Foreign key, etc.)
Some of the entities and attributes are as follows:
• customer_email - customer id, customer email
• customer_master - customer id, customer first name , customer last
name , customer street, customer city, customer state
• customer_fax_mst - customer id,customer fax number
• customer_phone_mst - customer id, phone1 , phone2
• booking_details - customer id, flight number, class id, origin, destination, departure date time, arrival date time, booking city, paid by, flight price, total price, paid by, balance, booking date
• city_master - city id, city name
• airport_master - airport code, city id, airport name, air port tax
• flight_availability - flight no, origin, dest, dept_time, arrival_time
• flight_master - flight no
Customer Flight availability checking and Booking a flight online presentation mode.
This project shall be done in 2 parts :
• Customer login and registration.
• Customer Flight availability checking and Booking a flight
• Some of the guidelines/protocols are given below:- Normalize the tables.
• Create additional tables, if necessary.
Technology Used
Databases: Oracle sql/plsql
Codes required plsql
• Create a procedure which gets number_of_bookings, number_of_emails, number_of_phones and number_of_faxs for each Customer. Customer id will be the input to the procedure.
• Create a procedure which Display all customer's first_name, last_name, phone_no and email. Please sort them by customer_id. Customer id will be the input to the procedure.
• Create a procedure which will give list of all customers which did not place any booking.
• Create a procedure which will list all cancelled bookings, please display booking_no, customer_id, flight_no, origin, destination, Booking city. Also sort by booking_no, customer_id and flight no.
• Create a procedure which do transaction processing the i.e a customer must be able to book the flight the effects must go to respective tables
• Create a procedure which do transaction processing the i.e a customer must be able to cancel the flight the effects must go to respective tables
SQL
From customers driven capabilities: -
1. The system will allow customers to search for flights and its availabilities.
2. The system will allow customers to make a reservation, add billing address, credit card details, pick departure and arrival time, and make due payment.
3. The system will allow customers to update the flight reservation by changing their departing date/time or/ arrival date/time within 24 hours of booking time.
4. The system will allow existing or new customers to cancel a reservation.
5. The system will allow customers to add more than one booking under the same customer id and billing address for different destinations.
6. The system sends customers email confirmation after the booking is made successfully.
7. The system allows the customer to manage booking after confirmation (reserve seats, change meal plans (Hindu, Asian, veg or non-veg) and ask wheel chair assistance.
8. The system allows customers to enter into a loyalty program to earn miles per flight, rewards, frequent traveler status, and lounge access.
From booking offices internal system driven capabilities: -
9. Add/insert customer information in the database system as required by the administrator.
10. Update customer information in the database system by the administrator.
11. Add/insert flight information if any new flight needs to be added.
12. Update flight information if any flight is canceled or rescheduled from the existing listing.
13. Get all the customers who have seats reserved on a given flight.
14. Get all flights for a given airport.
15. Calculate total sales for a given flight.
16. Add a new airport to the database.
17. Update fare for any given flights in any given country.
18. Get all the customers who live in any of the countries and sort by customer_id.
19. Display all currency exchange rate is greater than 1. Please sort them by from_currency and to_currency.
20. List all the flight availabilities between any given country airport (airport code is 'YYZ') and (airport code is 'JFK'). Please display flight_no, origin, destination, departure_time, and arrival_time. Please sort them by flight_no.
21. List top 10 customers with rewards earned and redeemed and send them congratulations email and mail special loyalty star card
22. List of all the customer's meal preferences for each flight last three months for food inventory in future analysis.
23. List the customers who canceled the flight with date/time and when the same customers booked the other flight with date/time. List the amount of booking and cancellations each country wise to see where do we have more revenue generations and cancellations.
24. List total_price, total_payment, and total_balance for each city. Please exclude canceled bookings and sort records by city_name.
25. Calculate new total_price for each booking if the origin airport tax increase by 0.03 and destination airport tax decrease by 0.006. Please display booking_no, origin, destination, flight_price, previous_total_price and new_total_price.
26. List number_of_bookings, number_of_emails, number_of_phones and number_of_faxs for each customer.
27. The airport taxes must be stored in local currencies for each country flight bookings.
28. List the amount of booking sales for each country and customer wise to calculate the most popular destinations.
29. Display the top 5 customers based on their total booking payment, each country wise and sort them based on the booking amount and sort them within each country total booking amount wise.
30. Calculate the aggregate of the amount of bookings by customers each country wise and list the top 3 popular country bookings.
Attachment:- RESERVATION.zip