BookAndFly!
My Role: Lead Systems Analyst
In this project, I served as the Lead Systems Analyst, focusing on creating a streamlined and efficient flight booking platform that unifies multiple airline systems to address common challenges like booking errors, delays, and data inconsistencies. My role involved identifying information needs and designing a comprehensive database structure to improve service customization, flight availability checks, and data security. The solution provides a cohesive user experience by consolidating passenger profiles, real-time availability, and booking records into a single interface, simplifying the booking process. I developed an ER model and relational schema to support these objectives, ensuring data integrity and a smooth, error-free user experience.
An in-depth project report is available for download below.
Project Overview
Project Proposal
Despite its vast applications, the airline industry faces significant challenges in providing customers with a straightforward and efficient booking experience. The multitude of airline-specific platforms and procedures creates a fragmented system that often leads to errors, delays, and a frustrating user experience. Our goal is to develop a unified booking platform that consolidates flight options from multiple carriers, transforming the booking process into a seamless, accurate, and user-friendly experience. By integrating client preferences and travel history, this single platform will enhance personalization and reduce booking errors, ultimately improving overall satisfaction and efficiency in the airline industry.
Problem Identifcation
The current airline booking landscape is hindered by inefficiencies and inconsistencies due to the variety of airline-specific platforms, which complicates the booking process. These disparate systems often lead to booking errors and delays, such as overlapping or misbooked reservations, frustrating users and causing unnecessary setbacks. Additionally, the lack of a unified structure increases the risk of data discrepancies and potential data loss, compromising both security and integrity in customer information management.
Information Needs
To create a seamless booking experience, the system needs unified traveler profiles that offer a comprehensive view of each passenger’s flight records, preferences, and feedback, allowing for tailored service customization. Additionally, consolidated flight records will keep track of all reservations, cancellations, and user modifications within a single platform, reducing errors and ensuring consistency. Real-time flight availability is crucial for accurate, up-to-date cross-checking across multiple airlines, while robust backup and security protocols are necessary to uphold data integrity and protect against potential breaches or data loss.
Entites List
The key entities in this system include the Passenger, which captures personal details, frequent flyer IDs, flight history, and preferences; Bookings, encompassing data on all flight services reserved by travelers; Flights, which details available flight options, schedules, seating arrangements, pricing, and any special provisions; and Airlines, a table that compiles data about each airline within the platform, accounting for varying rules, offers, loyalty programs, and partnerships. Additionally, foreign tables are included for Aircraft and Multiple Flights to support the structure of the unified booking system.
ER Diagram & Buisness Rules
The ER diagram visualizes the structure and relationships within the booking system, illustrating how each entity—Passenger, Booking, Flight, and Aircraft—interconnects. The business rules establish key associations: each passenger may have multiple bookings, and each booking can include multiple passengers, accommodating group or family travel. A flight can be linked to multiple bookings, but each booking is tied to a single flight to prevent duplicate reservations. Furthermore, each flight is associated with only one aircraft, though each aircraft may serve multiple flights, ensuring a flexible yet organized system that meets diverse booking and scheduling needs.
Relational Schema
The relational schema for the booking system is structured to optimize data organization and relationships among key entities.
- Passenger table stores each traveler's information, including a unique ID, name, frequent flyer ID, contact details, and preferences, supporting personalized service.
Passenger(ID INT PRIMARY KEY, Name VARCHAR, FrequentFlyerID VARCHAR, ContactInfo VARCHAR, Preferences VARCHAR)
- Booking table tracks all flight bookings, with a unique ID for each booking, a reference to the passenger’s ID, booking date, status, and total cost. The
PassengerIDfield is a foreign key that links to the Passenger table, enabling retrieval of traveler-specific bookings.Booking(ID INT PRIMARY KEY, PassengerID INT, Date DATE, Status VARCHAR, TotalCost DECIMAL, FOREIGN KEY (PassengerID) REFERENCES Passenger(ID))
- Flight table contains essential flight details, including a unique ID, departure and arrival times, origin, destination, and pricing information.
Flight(ID INT PRIMARY KEY, DepartureTime TIME, ArrivalTime TIME, Origin VARCHAR, Destination VARCHAR, Price DECIMAL)
- Airlines table holds each airline’s details, including a unique ID, name, loyalty programs, and partnership details, which can influence booking choices.
Airlines(ID INT PRIMARY KEY, Name VARCHAR, LoyaltyPrograms VARCHAR, PartnershipDetails VARCHAR)
- Aircraft table lists aircraft information, such as type and seating capacity, along with a reference to the associated airline through
AirlineID. This field serves as a foreign key linked to the Airlines table, aligning aircraft with specific carriers.Aircraft(ID INT PRIMARY KEY, Type VARCHAR, Capacity INT, AirlineID INT, FOREIGN KEY (AirlineID) REFERENCES Airlines(ID))
This schema ensures data consistency, maintains relationships between entities, and allows for efficient retrieval of booking, passenger, flight, and airline information.
Passenger Bookings Query
This query retrieves a list of all passengers in the database, displaying the total number of bookings each passenger has made along with the cumulative cost of those bookings. Interestingly, the data shows that passengers with bookings tend to have a high booking frequency, with each having made at least eight bookings, and a minimum total cost of 4650. This suggests that passengers who engage with the system tend to make multiple bookings, reflecting potentially frequent travel patterns. While this frequency might not reflect typical real-world behavior, it highlights the booking trends within this dataset.
Average Flight Price by Origin-Destination Pair Query
This query calculates the average flight price for each origin and destination pair, providing insights into price variations based on location rather than date. The results reveal that flights from JFK to LAX are the most expensive, while flights from SFO to ORD are the least expensive. This information enables users to identify costly and economical routes, supporting more informed booking decisions based on typical pricing trends for specific travel routes.
Additional Queries
In addition to analyzing flight prices by origin-destination pairs, several other queries were implemented to explore key insights within the booking system. For instance, one query retrieves the total number of passengers per flight, highlighting peak travel routes and high-demand flights. Another query calculates the average booking cost per passenger, offering a perspective on customer spending patterns and identifying potential opportunities for targeted promotions. Additionally, queries examining flight availability across different times and days help pinpoint trends in travel demand and inform scheduling optimization. Other queries identify flights with the highest cancellation rates, analyze frequent flyer program utilization, and track popular booking modifications, all of which provide a holistic view of user behavior and system performance. Collectively, these queries facilitate a deeper understanding of travel patterns, financial metrics, and customer preferences, enabling data-driven enhancements to the platform's functionality and user experience.
Conclusion
Through this project, I gained valuable insights into database design and the importance of data organization in creating a seamless user experience. Developing the ER diagram and relational schema taught me how to structure information effectively, balancing both system functionality and user needs. By identifying inefficiencies in existing booking processes and addressing them with a consolidated platform, I learned the impact of thoughtful, user-centered design in simplifying complex systems. This experience reinforced the significance of clear data relationships and intuitive interfaces, which are essential in reducing errors and improving usability. These skills have strengthened my approach as a designer, enhancing my ability to translate user needs into functional, efficient, and visually cohesive solutions.