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,
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.
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.