This project involved reverse engineering the Jet2holidays.com website to design and implement a MySQL database representing its working structure. Jet2Holidays offers customers package holidays, including accommodation, travel arrangements, and additional services like insurance.
Our team focused on the main aspect of booking a direct flight and accommodation, with assumptions made regarding flight and accommodation availability. Features such as insurance or in-flight meals were deemed out of scope for this project.
We started by identifying the entity relationships from the Jet2Holidays website and created an entity relationship diagram to visualize the findings.
The database evolved through continuous refinement and examination, with various tables added and removed to model the real-world relationships as closely as possible.
- Entity relation notation implementation
- Redundancy elimination
- Data integrity with foreign key constraints
- Title Table: Created a separate table for titles to reduce redundancy.
- Airport Table: Introduced an airport_id attribute to maintain atomic data and reduce redundancy.
- Hotel Table & hotel_id: Implemented a hotel_id primary key linked to multiple related tables, allowing for atomic data and reduced redundancy.
- Ability to book multiple flights and rooms under one booking
- Total price calculation for bookings
- Outsourced payment processing for security
- Different routes representation for flights
- Address geo-location for accurate location data
- Various data types used depending on the goal of the data held
- Soft delete feature excluded to avoid potential issues with data integrity and redundancy