Consider the given Relational database schema for an Airlines application. The p
ID: 3842336 • Letter: C
Question
Consider the given Relational database schema for an Airlines application. The primary key of each relation is underlined. Flight (FlightNo) FlyON (Flight, NQ WeekDay) FlightLeg (FlightNo, LegNo, DepartureTime, DepartureAirportCode, ArrivalTime, ArrivalAirport Code) FlightLegInstance (FightNo.LegNo.Date, ActualDepartureTime, ActualDepartureAirportCode, ActualArrivalTime, ActualArrivalAirportCode, AirplaneType, AirplaneNo) Airport (Airport Code AirportCity) AirplaneType (AirplaneType, ManufacturingCompany, SeatCapacity) Airplane (AirplaneType, AirplaneNo, ManufacturingYear, Status) CanLand (AirplaneType, AirportCode) FlightLegFares (FlightNo, LegNo, Class, Farevalue) Using DDL/DML of SQL2, write the appropriate statements to perform the following operations: a. Write a complete schema (you have to specify all necessary entity and referential constraints) for the table FlightLegInstance, Assume appropriate data types for the underlying attributes. b. Retrieve the total number of all flights that fly only on Sundays. C. List the flight number, day and time of all flights that depart from Cairo airport with destination JFK New York airport. The flights should be displayed in an ascending order according to day and time. d. Get all Airplane types that can land on "Hurgada" and "Sharm El-Sheikh" airports but not on "Luxor" airport. e. Define a view to get the total number of flying trips (flight leg instances) for each airplane during the last year (2006). Write a query to display such airplanes with more than 20 trips.Explanation / Answer
a) CREATE TABLE FlightLegInstance (
FlightNo int NOT NULL,
LegNo int NOT NULL,
date DATE,
ActualDepartureTime TIME,
ActualDepartureAirportCode int NOT NULL,
ActualArrivalTime TIME,
ActualArrivalAirportCode int NOT NULL,
Airplane type VARCHAR(255),
AirplaneNo int NOT NULL,
PRIMARY KEY (FlightNo),
);
b)Select count(Flight) as Total from FlyON where weekDay='Sunday';
c)select F.FlightNo, F.Date, F.ActualDepartureTime from FlightLegInstance F,Airport A,CanLand C where F.AirplaneType=C.AirplaneType and C.Airportcode=A.Airportcode and F.departureCity='cairo' and F.ArrivalCity='JFK' order by F.date and F.ActualDepartureTime ASC;
d) select C.AirplaneType from CanLand C,Airport A where A.AirportCode=C.AirportCode and A.AirportCity='Hurgada'
and 'Sharm EI-Sheikh';