Airlines deal with complexity that most systems never touch. Thousands of flights. Millions of passengers. Reservations that connect hotels, cars, and insurance. All happening in real-time across different time zones.
For my Database Systems II course, I built a flight reservation system from scratch using Oracle. Not just tables and queries. A complete simulation with stored procedures, triggers, custom types, and role-based security. The goal was to understand how real-world systems handle complex business logic at the database level.
What It Does
The system simulates a full airline operation. Users can search for flights between airports. They can book seats, choose between one-way and round-trip options, and pay with credit cards, debit cards, or accumulated miles.
But flights are just the beginning. The system supports “triple reservations” where a flight booking automatically triggers car rental and hotel reservations at the destination. Everything stays connected. Cancel the flight, and the car and hotel reservations get canceled too.
The simulation runs hundreds of booking scenarios. Random users pick random destinations. The system finds available flights, handles schedule conflicts, applies dynamic pricing, and processes payments. It even handles edge cases like opening new flights when no routes exist.
The Data Model
Airlines need to track many interconnected things. I designed around 30 tables covering the full domain.
Aircraft and Fleet. Airlines own planes. Each plane belongs to a model made by a manufacturer. Each model has different seat configurations. A Boeing 737 has different capacity than an Airbus A320.
Geography. Airports exist in cities within countries. Each airport has coordinates for distance calculations. Flights connect airports with scheduled departure times and durations.
Reservations. Users book seats on flights. Each seat has a class and price. Bookings can include insurance from different providers. They can also include car rentals and hotel stays.
Payments. Users pay with credit cards, debit cards, or loyalty miles. The system tracks every transaction. Miles accumulate based on distance flown.
The challenge was keeping everything consistent. A canceled flight should release its seats. A triple reservation should fail completely if any part fails.
Custom Types in Oracle
Oracle lets you create your own data types. I used this to model complex values that appear throughout the system.
Units. Prices are in dollars. Distances are in kilometers. Speeds are in km/h. Instead of storing raw numbers, I created a type that carries the value, unit name, and conversion methods. Converting dollars to euros? The type knows how. Converting kilometers to nautical miles? Same approach.
Locations. Addresses appear everywhere. Airports have locations. Hotels have locations. Car rental pickup points have locations. A custom type holds country, city, street, and timezone. A method converts timestamps between zones.
Reservations. Every booking shares common fields. Start date. End date. Amount. Status. A reservation type encapsulates this pattern.
These types made the schema cleaner. More importantly, they centralized business logic. Currency conversion lives in one place, not scattered across dozens of queries.
Stored Procedures and Packages
The real work happens in PL/SQL packages. I organized them by domain.
Flight Reservation Package. This handles the booking flow. It finds available flights between two airports. It checks for schedule conflicts with the user’s existing reservations. It assigns seats. It calculates prices based on demand. If few seats remain, prices go up. If the plane is mostly empty, discounts apply.
Car Reservation Package. Users can rent cars at their destination. The package checks availability, handles date conflicts, and manages the rental lifecycle.
Hotel Package. Similar to cars. It manages room inventory, tracks guest information, and handles ratings.
Payment Package. This processes transactions. It validates payment methods. It applies miles when available. It handles refunds when reservations get canceled.
Each package encapsulates its domain. The flight package doesn’t know about hotel internals. It just calls the hotel package when needed.
Triggers for Automatic Actions
Some things should happen automatically. Triggers handle these cases.
When a new plane gets added, a trigger creates its seats. The seat configuration depends on the model. Boeing 737-800? That means 16 first class, 24 economy extra, and 126 main cabin seats. Each with appropriate pricing.
When a new user registers, a trigger creates their payment methods. Every user starts with a debit card, credit card, and miles account. This simulates the real-world scenario where airlines issue co-branded cards.
When a hotel gets created, a trigger adds its amenities. Five-star hotels get heliports, pools, and taxi services. Budget hotels get basic features.
Triggers keep the data consistent without requiring external code to remember these rules.
Distance Calculations
The system calculates actual flight distances. This matters for two reasons: pricing and miles.
I implemented the Haversine formula in PL/SQL. It takes two airports with latitude and longitude coordinates. It calculates the great-circle distance between them. The math involves converting degrees to radians, computing sines and cosines, and handling the earth’s curvature.
When a user completes a flight, their miles account gets credited with the actual distance traveled. A flight from Caracas to Dubai earns more miles than Caracas to Miami.
Role-Based Security
Not everyone should access everything. I implemented seven distinct roles.
Airline Administrator. Full control over flights, planes, and airports. Can manage pricing and schedules.
Airline Employee. Can update flight statuses and handle reservations. Cannot modify pricing structures.
Car Administrator. Manages the vehicle fleet. Has no access to flight data.
Hotel Administrator. Manages properties and rooms. Cannot see user payment information.
System User. Regular customers. Can make reservations and manage their own accounts. Cannot see other users’ data.
Each role has specific grants. The airline admin can modify the fare structure. The employee can only read it. The hotel admin has no visibility into airline operations at all.
This mirrors real organizations where departments have separate concerns and limited access.
Simulation and Testing
I built a simulation that creates realistic scenarios. The system processes 500 reservation attempts with random users and destinations.
Each simulation run exercises the full flow. Find available flights. Check for conflicts. Apply pricing. Process payment. Update miles. Handle cancellations and refunds.
The simulation exposed edge cases. What happens when no direct flight exists? The system checks for connections through intermediate airports. What if all routes are full? The user can request a new flight be opened for a future date.
Random probability drives decisions. Will the user want round-trip? 70% chance. Will they add insurance? 50% chance. Will they cancel? 5% chance. This creates diverse test scenarios.
What I Learned
Databases can hold business logic. Before this project, I thought of databases as dumb storage. This taught me that stored procedures, triggers, and custom types can encapsulate serious complexity. The application layer becomes thinner.
Transactions matter. A triple reservation must succeed or fail atomically. Either the flight, car, and hotel all get booked, or none of them do. Oracle’s transaction support made this possible.
Types prevent errors. Storing prices as plain numbers invites mistakes. Is that amount in dollars or bolivars? Custom types make the meaning explicit. The compiler catches mismatches before runtime.
Security is a design concern. Role-based access isn’t an afterthought. It shapes how you structure your schema and procedures. Who can call what determines where logic lives.
This project showed me the power of relational databases when used to their full potential. Modern development often treats databases as simple key-value stores. But they can do so much more.
Interested in discussing database architecture or complex system design? Let’s connect.
