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.