Rolando Andrade
Back to projects

Built in 2020

UCAB Airlines

Airline Flight System

UCAB Airlines preview

Database Systems II course project. Airline's flight system was developed using Oracle as DBMS.

A traveler books a flight, a rental car and a hotel in one go. Then the flight gets canceled. The car and the hotel must be canceled too. Real airlines handle this every day, across thousands of flights, millions of passengers and many time zones.

For my Database Systems II course, I built a flight reservation system from scratch with Oracle. It was more than tables and queries. It was a full simulation with stored procedures, triggers, custom types and role based security. I wanted to understand how real systems keep complex business logic inside the database.

A whole airline, simulated

The system simulates a full airline. Users search for flights between airports. They book seats, choose one way or round trip, and pay with a credit card, a debit card or saved miles.

Flights are only part of it. The system supports “triple reservations”, where booking a flight also books a rental car and a hotel at the destination. All three stay linked. Cancel the flight, and the car and hotel are canceled too.

The simulation runs hundreds of booking scenarios. Random users pick random destinations. The system finds available flights, handles schedule conflicts, adjusts prices to demand and processes payments. It even opens new flights when no route exists.

Around 30 tables

An airline tracks many connected things. I designed around 30 tables to cover them.

Aircraft and fleet. Airlines own planes. Each plane is a model made by a manufacturer. Each model has its own seat layout. A Boeing 737 holds a different number of people than an Airbus A320.

Geography. Airports sit in cities inside 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 a price. A booking can include insurance from different providers. It can also include car rentals and hotel stays.

Payments. Users pay with credit cards, debit cards or loyalty miles. The system records every transaction. Miles grow with the distance flown.

The hard part was keeping everything consistent. A canceled flight must free its seats. A triple reservation must fail completely if any part fails.

Types that know units

Oracle lets you create your own data types. I used them for complex values that appear all over the system.

Units. Prices are in dollars. Distances are in kilometers. Speeds are in km/h. Instead of storing plain numbers, I made a type that holds the value, the unit name and conversion methods. It knows how to turn dollars into euros, or kilometers into nautical miles.

Locations. Addresses appear everywhere. Airports, hotels and car rental pickup points all have one. A custom type holds the country, city, street and time zone. One of its methods converts times between zones.

Reservations. Every booking shares the same fields: start date, end date, amount and status. A reservation type holds that pattern.

These types made the schema cleaner. They also kept business logic in one place. Currency conversion lives in one type, not spread across dozens of queries.

Packages for each domain

The real work happens in PL/SQL packages. PL/SQL is Oracle’s programming language inside the database, and a package groups related procedures. I organized them by domain.

Flight reservation package. This runs the booking flow. It finds available flights between two airports. It checks for clashes with the user’s other reservations. It assigns seats. It sets prices by demand. If few seats remain, prices go up. If the plane is mostly empty, discounts apply.

Car reservation package. Users can rent a car at their destination. The package checks availability, handles date clashes and manages each rental from start to end.

Hotel package. It works like the car package. It manages rooms, keeps guest information and handles ratings.

Payment package. This processes transactions. It checks payment methods, applies miles when the user has them and handles refunds when reservations are canceled.

Each package owns its domain. The flight package knows nothing about hotel internals. It just calls the hotel package when it needs to.

Triggers do the chores

Some things should happen on their own. A trigger is code the database runs automatically when data changes.

When a new plane is added, a trigger creates its seats. The layout depends on the model. A Boeing 737-800 gets 16 first class seats, 24 economy extra seats and 126 main cabin seats, each with its own price.

When a new user signs up, a trigger creates their payment methods. Every user starts with a debit card, a credit card and a miles account. This copies the real cards airlines issue with banks.

When a hotel is created, a trigger adds its amenities. Five star hotels get heliports, pools and taxi services. Budget hotels get the basics.

Triggers keep the data consistent, so no outside code has to remember these rules.

Real distances, real miles

The system calculates real flight distances. Distance matters for pricing and for miles.

I wrote the Haversine formula in PL/SQL. It takes the latitude and longitude of two airports and finds the great circle distance, the shortest path over the curved Earth. The math converts degrees to radians and uses sines and cosines.

When a user finishes a flight, their miles account gets the real distance traveled. Caracas to Dubai earns more miles than Caracas to Miami.

Seven roles, separate access

Not everyone should see everything. I set up seven distinct roles.

Airline administrator. Full control over flights, planes and airports. Can manage prices and schedules.

Airline employee. Can update flight statuses and handle reservations. Cannot change the price structure.

Car administrator. Manages the car fleet. Has no access to flight data.

Hotel administrator. Manages properties and rooms. Cannot see users’ payment information.

System user. A regular customer. Can make reservations and manage their own account. Cannot see other users’ data.

Each role has its own grants, the permissions the database gives it. The airline administrator can change the fare structure. The employee can only read it. The hotel administrator cannot see airline operations at all.

This mirrors real companies, where departments have separate jobs and limited access.

500 random reservations

I built a simulation that creates realistic scenarios. It runs 500 reservation attempts with random users and destinations.

Every run goes through the whole flow:

  1. Find available flights.
  2. Check for clashes.
  3. Set the price.
  4. Process the payment.
  5. Update the miles.
  6. Handle cancellations and refunds.

The simulation found edge cases. What if there is no direct flight? The system looks for connections through other airports. What if every route is full? The user can ask for a new flight on a future date.

Random chances drive the choices. A user wants a round trip 70% of the time. They add insurance 50% of the time. They cancel 5% of the time. That gives many different test cases.

The database can think

Databases can hold business logic. Before this project, I saw databases as simple storage. Stored procedures, triggers and custom types showed me they can hold serious complexity. The application layer gets thinner.

Transactions matter. A triple reservation must succeed or fail as one unit. Either the flight, car and hotel all get booked, or none do. Oracle’s transactions made that possible.

Types prevent errors. A price stored as a plain number invites mistakes. Is it in dollars or bolivars? Custom types make the meaning clear. The compiler catches mismatches before the code runs.

Security is a design question. Role based access is not something you add at the end. It shapes how you build the schema and procedures. Who can call what decides where the logic lives.

Many projects treat the database as a simple key value store. This one showed me how much more a relational database can do.


Interested in discussing database architecture or complex system design? Let’s connect.

Interested in working together? Check out my services and pricing to see how we can collaborate on your next project. Let's Build