Project Description: This project is based on a fictitious scenario. The problem that a company is currently facing is related to the database management system in which the ASK is to design a central airline reservation database system to store data in the central database system to be used by all booking offices. The newly designed database management system will help the airline system to issue reservation tickets for various air flights and maintains the records of various passengers and provide quick services to the passengers. The newly designed database the system will help provide different new features and data population with performance scalability to provide enhanced customer services for this company. This will also enhance their operational efficiency and reduce the number of passengers handling and improvise the forecasting accuracy. This case study will truly help me to demonstrate the skills and learning from the database management courses by implementing those skills and learning in designing an efficient and effective database management system into this case study.
Company XYZ runs 7 different airlines in 7 different countries with single booking offices in each of the cities. Countries include - India – Air India, USA- American Airlines, UK – British Airlines, Canada – Air Canada, Switzerland -Swiss Airlines, Singapore- Lufthansa Airlines, China – Air China. These airlines operated from 14 different cities across these 7 countries and each of the 14 cities there is a single booking office for the reservation.
The current problem is there is no central air-reservation database that can be used by all booking offices and customers to provide better and efficient services to their existing and new customers.
The manual process involved in the management of airlines was critically examined and the flaws noted. Each booking office Airline reservation system will be used to maintain records. of all the flight availability display, flight booking, fare prices, economy and business class, flight schedules, seat assignments, airport taxes, and currency rates based on each country, customer data, and sales reporting from each region. Another problem company facing is slow performance due to populating data from different database systems rather than a single centralized database having information for all the bookings and transactions at one common database. This makes them difficult to analyze the data across all the countries.
To overcome this problem, we will design the central air-reservation database to be used by all the booking offices. All the data across these airlines will be saved in this central repository database for better customer services and reporting structures to the management.
The flight has a unique flight number, airline code, business class indicator, smoking allowed indicator. Flight availability has a flight number, date+time of departure, number of total seats available in each class business class, and economy. The customers may come from any country, not just the 6 above, and from any province/state, and from any city. The customer has first & last name, mailing address, zero or more phone numbers, zero or more fax numbers, and zero or more email addresses. The mailing address has street, city, province or state, postal code, and country. The phone/fax number has country code, area code, and the local number. The 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 the same mailing address and/or same phone number(s) and/or the 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 a unique booking number, booking city, booking date, flight number, date + time of departure (in local time, and time is always in hours and minutes), date + time of arrival (in local time), class indicator, total price (airport tax in origin + airport tax in destination + flight price – in local currency. The flight price for business class is 1.5 times of the listed flight price), the status indicator (three types: booked. Canceled – the customer canceled the booking, scratched – the customer had not paid in full 30 days prior to the departure), the customer who is responsible for payment, amount-paid-so far (in local currency), the outstanding balance (in local currency), the first & last names to be printed on the ticket. The airport taxes must be stored in local currencies (i.e. Canadian dollars, US dollars, British Pounds, French francs, German marks, and Italian Liras). Since the exchange rates change daily, they also must be stored for calculations of all prices involved.
We need to create queries for the below requirements and take a screenshot of successful running output and screenshots of output not running with check constraints.
High Level Requirements: Below is the high-level list of the functionality of the system. As we evolve more into the project, the requirements will be enhanced and modified as appropriate.
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.
Data: High-level data entities and attributes that will be stored in the system as below.
If you are a student or database developer, administrator or someone with a basic understanding of the features of MongoDB, MySQL, Oracle Hire us and Get your projects done by Django expert developer or learn