This project implements a robust relational database for an Airline Reservation System. It is designed to handle complex aviation operations, including flight scheduling, aircraft seat configurations, passenger bookings, and financial transaction tracking. The schema ensures data integrity using constraints and triggers, preventing issues like double-booking while supporting deep analytical reporting.
| File | Description |
|---|---|
| airline_reservation_system_setup.sql | Defines the core database structure, including tables, indexes, triggers, and stored procedures. |
| full_setup.sql | A unified script combining schema creation, data insertion, and sample analytical queries. |
| setup_and_test_fixed.bat | An automated Windows batch script to find MySQL, execute the setup, and display results. |
The database consists of structured tables tailored for airline management.
You can view the visual schema design here: DB Diagram - Google Drive
- Aircraft & Seats
- Aircraft: Tracks the fleet (model, manufacturer, year).
- Seats: Manages seat layout (seat_number, class: Economy/Business/First) for each aircraft.
- Routes & Flights
- Airports: Stores global airport details (IATA codes, city, country).
- Routes: Defines connections between airports with base pricing.
- Flights: Specific schedules for aircraft on given routes.
- Passengers & Users
- Users: System users/agents who manage bookings.
- Passengers: Detailed traveler information including passport data.
- Bookings & Items
- Bookings: Master record for a reservation with a unique 6-character PNR.
- Booking_Items: Specific seat assignments for passengers on specific flights.
- Payments
- Payments: Records transaction details, methods (Credit Card, PayPal), and success/refund status.
Prerequisites
- MySQL Server 8.0 or higher.
- MySQL added to System PATH (optional, the script attempts to find it).
Option 1: Automatic Setup (Windows)
- Navigate to the project folder.
- Double-click setup_and_test_fixed.bat.
- Enter your MySQL credentials when prompted.
- The script will initialize the database and run all analysis tests.
Option 2: Manual Execution
- Login to MySQL:
mysql -u root -p - Run the unified setup:
SOURCE full_setup.sql;
The system includes built-in views for business intelligence:
- Detailed Booking Report (
vw_booking_details)
- Provides a full manifest of bookings, flight info, and passenger names.
- Usage:
SELECT * FROM vw_booking_details;
- Flight Revenue Analytics (
vw_flight_revenue)
- Calculates total seats sold and revenue generated per flight.
- Usage:
SELECT * FROM vw_flight_revenue;
Design and Implementation by VIJAYAPANDIAN.T.