Database Design
Using Crow's foot notation, create Entity Relationship Diagram (ERD) to support the following business operations:
Assume that SmartTravels Airways Pty Ltd were an Australian low-cost airline based in Victoria. The airline operates an extensive domestic network as well as regional and international services. SmartTravels wishes to track its flights and has chosen to hire you as a database designer. Your task is to design a database that will store details about the airline's fleet, flights, and seat bookings.
The IT Manager of the company has provided the following business rules to you as the database developer:
User view requirement 1: An Airplane is uniquely identified by its RegistrationNumber. An airplane can be involved in any number of flights, while each flight uses exactly one airplane.
User view requirement 2: A Flight is uniquely identified by its FlightNumber. For each Flight, the departure and destination airports are captured in the From and To attributes, and the departure and arrival date and time are also recorded.
User view requirement 3: Because no two passengers will share an email address, the EmailAddress will be the unique identification for each Passenger. The database should include the age, gender, and frequent-flier number of each passenger.
User view requirement 4: A Passenger can book any number of flights, while a flight can be booked by any number of passengers.
User view requirement 5: A flight comprises multiple seats. Each seat is identified by a Seat ID and is assigned to only one flight. For each seat, the Seat ID, Class, RowNumber, and SeatNumber should be recorded.
User view requirement 6: For each booking, the booking status is recorded as 'Paid' or 'Not Paid'.
Passengers can reserve a seat during the booking process, and reserved seat details will be included