Given the following assumptions:

A flight has a unique flight number $($two alphabetic characters

$($airline company$),4$ digit code$)$

$$ A passenger has a unique name $($Make a surrogate ID$,$ plus legal

name$)$

$$ A pilot has a unique name $($Make a surrogate ID$,$ plus legal 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

$$ A planeID is a unique name $($one or two alphabetic characters

$($country code$),$ one to five alpha$-$numeric characters$)$

Build the ERD diagram and Normalize $($with keys, field names, and

data types$)$ for the following relation:

$($Flightinfo $($FlightNumber$,$Flightdate, flighttime$),$ airline $($twocode$,$

airlinename$),$ passenger $($passid$,$ passengername, gender, date of

birth$),$ pilot $($pilotid$,$ pilotname, gender, date of birth$),$ departure$\_$city,

arrival$\_$city, airplane $($planeID$,$ type, seats$),$ Country $($ccode$))$