Your firm operates a commuter airline covering portions of western Canada and the northwest United States.  Presently, you store your schedule in a database with the following tables:

FLIGHT(FlightNumber, OriginAirport, DepartureTime, DestinationAirport,

 ArrivalTime, PlaneCode)

                        PlaneCode foreign key to KINDOFPLANE

           FLIESONDAY(FlightNumber, Day)

                        FlightNumber foreign key to FLIGHT

           KINDOFPLANE(PlaneCode, FullPlaneName, SeatingCapacity)

OriginAirport and DestinationAirport are standard three-letter airport codes (like “EWR” for Newark). The permitted values of Day in the FLIESONDAY table are “Weekdays”, “Saturday”, or “Sunday”.  For instance, the records shown below indicate that flight 123 operates between Calgary, Alberta (airport code YYC) and Helena, Montana (airport code HLN), departing 10:00 AM and arriving 12:30 PM on weekdays and Saturdays, but not operating on Sundays; the flight uses a De Havilland Dash 7 turboprop aircraft (PlaneCode DH7), which seats 50 people.  The records pertaining to other flights and kinds of aircraft are not shown.

FLIGHT:                     123      YYC    10:00   HLN    12:30   DH7

KINDOFPLANE:        DH7    “De Havilland Dash 7 Turboprop”     50

 

FLIESONDAY:	123	Weekdays
	123	Saturday

(a) Draw an entity-relationship diagram for this database.

The operations department decides that Saturday demand for flight 123 is too small to justify a DH7 aircraft, and they want to substitute a smaller Saab 340 aircraft on Saturdays.  They will continue to use the DH7 aircraft on weekdays.

(b) With this change of aircraft, briefly explain why the database forces you to assign a different flight number to the Saturday flight, even though its scheduled arrival and departure information are identical to the weekday flight.

(c)  Redesign the database so the same flight number can be used on every day a flight operates, even if different aircraft are used.  Write a new entity-relationship diagram and database design outline.
 

(d)  Suppose you want to be able to print reports in which you list not only the three-letter codes for airports, but their full names. For example, a line of the report might read

      Flight  123  Departs Calgary, Alberta  10:00am   Arrives Helena, Montana  12:30pm

You plan to add a table to the database storing the name, state/province, altitude, latitude and longitude of each airport (latitude and longitude are useful for calculating flight distance).  Draw a new entity-relationship diagram and design outline for database.