Online flight booking system

Understand the design process of a relational database and its applications

Design an ER diagram and a relational database. Customers can search for flights (one way or round trip), purchase tickets, and view their future and past flights. Airline staff can add new airplanes, create new flights, and update flight status.

Develop a web application that communicates with the database

Based on the database design and the required use cases, build an appropriate web application: public information for guests; registration and login; for customers, viewing, searching, purchasing and cancelling flights, rating previous flights and tracking spending; for airline staff, managing flights, airplanes and airports, and viewing ratings, frequent customers, reports and revenue.

Designing the ER diagram

I designed the ER diagram before creating a schema. Based on the required features of the project, I generated entities and relationships with draw.io.

Creating the relational database design

I designed the schema based on the ER diagram. While designing it, I realized I needed an additional table, flight_passenger, because a customer can buy tickets for other passengers without providing everyone's information beyond their names. With the earlier design, I would have had to collect every passenger's information in the customer table. With flight_passenger, only one ticket is generated, whether it's for a single customer or several.

Developing the web application

I designed the web application in Figma, inspired by the TV show Kim's Convenience, with lime as the primary color to match the theme. The page structure follows Expedia and Google Flights.

I chose Azure Database for MySQL to build my cloud skills in the Azure environment. I had some exposure to cloud databases through MongoDB Atlas and wanted to broaden my skill set.

I first planned to use Next.js for the full stack, but chose Next.js with Flask. Flask was part of the class curriculum, and I wanted more exposure to Python on the server side, having used Node.js before.

Database schema redesign

It would be best to design the database perfectly in the first place, but that isn't always the case. This project taught me that sometimes I have to redesign the database to model the data correctly.

Learning beyond the requirements

In a two-month timeframe, I wanted to spend the time learning, not just meeting the requirements, and delivered working features with a careful UX design. I can't share the code and queries publicly due to the course's plagiarism policy.