I am currently working on my database module assignment and have completed the initial design of my ERD (Entity-Relationship Diagram). I would greatly appreciate your insights and feedback on the design to ensure it meets the requirements and best practices.To provide context, I've included the case study below. Please review the attached ERD and share your thoughts, specifically focusing on any areas for improvement or potential enhancements.
Thank you in advance for your time and expertise.
Case Study
TRAVEL SAFE INTERNATIONAL (TSI) is one of the leading companies in the business of providing global distribution systems on selling tickets for multiple airlines. Its head office is located at Singapore. Tickets can be booked in one of three classes – First, Business, and Economy. One-way, Round-trip and Multi-city options are available for flight booking. Multiple types of meal options are available for different flights. Duration of the flight decides about serving refreshment, single meal or multi meal. Special services, such as child-care are provided for children aged between 2 to 11 years by offering special meal. Fare would be based on different age categories for passengers as mentioned below. • Infant (Ages: under 2) • Child (Ages: 2-11) • Youth (Ages: 12-17) • Adult (Ages: 18-64) • Senior (Ages: 65 or over) Fare for an infant depends on whether the infant is “in lap” or “in seat”. In case of travelling with an infant in lap, the airlines will charge between 10-15% of the adult fare whereas travelling with an infant in seat, the airlines will charge the child fare. *Some airlines have a maximum of 10 passengers per booking. Most of the airlines restrict that a child aged less than 17 cannot travel alone but some of the airlines offer unaccompanied minor service for children from 5 to 17 years of age to ensure that child is boarded onto the aircraft. The service is mandatory for all unaccompanied children and subject to extra cost. Unaccompanied minor service is not required for children ages 17 and younger when they are travelling with a parent or with an adult 18 years of age or older. Every two children who are travelling together will be assessed a single fee in each direction. Wheelchair service is also provided by some airlines. General constraints and rules applicable for flight reservation are being followed including but not limited to baggage limit. Passenger could purchase additional baggage during ticket reservation. Ticket cancellation can be done, however re-fund is not permitted. Upon cancellation, the amount paid will be transferred to credit account where it can be used for future booking. The amount in credit account is only valid for one year from the date of cancellation. For ticket changes, such as changing travel date/time without changing original/destination place, may be done with some additional charges subject to availability. The new travel date/time must be within one year from the original date. If a traveler cancels or change its itinerary less than 24 hours before its scheduled flight, it will lose the entire ticket value. You are required to design a database covering following areas. • Flight Reservations • Flight Cancellations & Rescheduling
