Normalize the following relation:

R (flightnumber, flighttime, airline (airlinename), passenger (passengername, gender, date_of_birth), pilot (pilotname, gender, date_of_birth), departure_city, arrival_city, airplane (planeID, type, seats))

- a flight has a unique flight number, a passenger has a unique name, a

- pilot has a unique name;

- a flight is always handled by one airline;

- a flight can have multiple passengers, a passenger can be on multiple

flights;

- a flight has one pilot, a pilot can operate multiple flights;

- a flight is always handled by exactly one airplane;

First Normal Form:

Second Normal Form:

Boyce-Codd Normal Form:

Hi Riahroo! This is a good question on the concept of relational databases.

We can normalize the relations as follows:

Flight

(flightnumber (unique), flighttime, airline_id, departure_city, arrival_city, passenger_id, pilot_id, airplane_id)

has_one_and_belongs_to : airline

has_many : passengers

has_one : pilot

Itinerary (passenger_id, flight_id)

Belongs_to

Passenger_details

(passengername (unique), gender, date_of_birth)

has_many : flights

Pilot

(pilotname (unique), gender, date_of_birth)

has_many : flights

airline (airlinename)

airplane (planeID, type, seats))

Explanation:

To normalize a relation, we have to remove any redundancies from the relationships between database objects/tables and simplify the structure. This also means simplifying many-to-many relationships. In this question, we see there is a many-to-many relationship between flights and passengers. To resolve this we can introduce a join table which simplifies this relationship to a one-to-many between the objects.