Name of the scenario

Logical diagram

ERD

• Country - This entity is used to store the information of the countries in which the airlines are running.

• City - This entity is used to store the information of the cities through which flights go.

• Booking - This entity is used to store the information about the bookings performed by the customers.

• Customer - This entity is used to store the information about the customers.

• EmailId - It stores the information about the email addresses of each customer.

• Payment - It stores the information about the payments performed by the customers.

• • cityId - It is the primary key attribute. The data type of this field is of type text. It stores the unique number for each city.

  • cityName - It stores the name of the city. The data type of this field is of type text. It is not null.

• Airline

  • airlineId - It is the primary key. The data type is of type text. It stores the unique number for each airline.
• • arrivalDateTime - It stores the date and time for the arrival of the flight. The data type will be datetime. It is not null.

  • departureDateTime - It stores the data and time for departure of the flight. The data type is datetime. It is not null.

• Customer

  • customerId - It is the primary key. It stores a unique customer number for each customer. It is of type text.
• • Street - It stores the value of the street in the mailing address. It is not null. It is of type text.

  • City - It stores the name of the city. It is not null. It is of type text.

• • citizenshipId - It stores the unique number for each citizenship. It is of text type. It is the primary key.

  • Country - It stores the name of the country. It is of text type. It is not null.
• • countryCode - It stores the country code. It is not null.

  • areaCode - It stores the area code. It is not null.

• The relationship between Flights and Cities is a many-to-many relationship. This is because a flight can have multiple cities it travels to and from, and a city can have multiple flights going to it.

• The relationship between Bookings and Payments is a one-to-one relationship. This is because a booking can only have one associated payment, and a payment can only be associated with one booking.

• Flight and Class are in a many-to-many relationship as each flight can have one or multiple classes while each class can be in multiple flights.

• Customer and Booking are in a one-to-many relationship as each customer can have multiple bookings.

• Customer and MailingAddress are in a one-to-one relationship as each customer can have only one mailing address.

• Customer and Citizenship are in a one-to-one relationship as each customer can have only one citizenship.

Bonus:

some entities, attributes, relationships, etc. should be added to the model. For example, an entity called Time Zone should be added in order to store time zones for each city. This will make it easier to track the time differences between cities. Additionally, an entity called Currency should be added in order to store the currency used in each country. This will make it easier to track exchange rates for different currencies. Finally, a relationship between the Flight and Payment entities should be added in order to track payments for flights.