The following tables form an instance of an Airport Database held in a relational DBMS: Airport...

Transcribed Image Text:

The following tables form an instance of an Airport Database held in a relational DBMS: Airport Carrier Flight Airport airportID CDG DXB JFK LGA MIA SFO - Carrier carrierID AF DL EK US (airportID, name, city, state, country) (carrierID, name, fleetSize, employees, revenue) (carrierID, flightNo, date, time, airportID, destination, distance) name Charles de Gaulle Paris Dubai International Dubai John F Kennedy International New York LaGuardia New York Miami International Miami San Francisco International San Francisco Each airport name is unique in its country. DL AF US AF name Air France Delta Air Lines Emirates Airline US Airways fleetSize employees revenue 225 84,602 15,414 855 80,000 39,639 239 84,153 18,300 338 31,467 13,050 Each carrier name is unique in the whole world. Flight carrierID flightNo date DL DL DL EK US city state NULL NULL 426 10/16/2017 426 10/17/2017 NY NY FL CA country FRA UAE USA USA USA ISA time airportID destination 7:00 AM JFK SFO 7:00 AM JFK SFO 479 10/16/2017 7:25 PM JFK SFO 721 10/17/2017 11:25 AM CDG DXB 783 10/17/2017 3:20 AM DXB JFK 1472 10/17/2017 7:30 AM LGA MIA 3577 10/17/2017 5:45 PM JFK CDG 3577 10/17/2017 9:40 AM LGA SFO 3634 10/16/2017 1:20 PM CDG MIA Any (carrier ID, flightNo) combination is unique for the whole day. distance 2,586 2,586 2,586 3,259 6,843 1,099 3,629 2,576 4,580 11. Display the number of airports in each country. 12. Display, for each carrierID, the number of flights and the average of their distances. The following table lists (for each day) the number of flights, the earliest and the latest flight times in that day. date col2 col3 10/16/2017 3 7:00 AM 10/17/2017 6 3:20 AM col4 7:25 PM 5:45 PM I 13. What is the relational algebra expression that outputs the above relation? Let V and W be defined as follows: V = IIcarrierID, date, airportID (date-10/17/2017 (Flight)) W = IairportID (Ostate='NY' (Airport)) 14. Output the result of the expressions V, W, and V÷W. Make sure your output includes the attribute names.. V W V÷W The following tables form an instance of an Airport Database held in a relational DBMS: Airport Carrier Flight Airport airportID CDG DXB JFK LGA MIA SFO - Carrier carrierID AF DL EK US (airportID, name, city, state, country) (carrierID, name, fleetSize, employees, revenue) (carrierID, flightNo, date, time, airportID, destination, distance) name Charles de Gaulle Paris Dubai International Dubai John F Kennedy International New York LaGuardia New York Miami International Miami San Francisco International San Francisco Each airport name is unique in its country. DL AF US AF name Air France Delta Air Lines Emirates Airline US Airways fleetSize employees revenue 225 84,602 15,414 855 80,000 39,639 239 84,153 18,300 338 31,467 13,050 Each carrier name is unique in the whole world. Flight carrierID flightNo date DL DL DL EK US city state NULL NULL 426 10/16/2017 426 10/17/2017 NY NY FL CA country FRA UAE USA USA USA ISA time airportID destination 7:00 AM JFK SFO 7:00 AM JFK SFO 479 10/16/2017 7:25 PM JFK SFO 721 10/17/2017 11:25 AM CDG DXB 783 10/17/2017 3:20 AM DXB JFK 1472 10/17/2017 7:30 AM LGA MIA 3577 10/17/2017 5:45 PM JFK CDG 3577 10/17/2017 9:40 AM LGA SFO 3634 10/16/2017 1:20 PM CDG MIA Any (carrier ID, flightNo) combination is unique for the whole day. distance 2,586 2,586 2,586 3,259 6,843 1,099 3,629 2,576 4,580 11. Display the number of airports in each country. 12. Display, for each carrierID, the number of flights and the average of their distances. The following table lists (for each day) the number of flights, the earliest and the latest flight times in that day. date col2 col3 10/16/2017 3 7:00 AM 10/17/2017 6 3:20 AM col4 7:25 PM 5:45 PM I 13. What is the relational algebra expression that outputs the above relation? Let V and W be defined as follows: V = IIcarrierID, date, airportID (date-10/17/2017 (Flight)) W = IairportID (Ostate='NY' (Airport)) 14. Output the result of the expressions V, W, and V÷W. Make sure your output includes the attribute names.. V W V÷W

Answer rating: 100% (QA)

Airport Carrier and Flight and a set of relational algebra problems The problems ask for exp... View the full answer