Airline Company An airline company is interested in implementing a database system to manipulate the flights that it organizes. The airline company has a certain number of airplanes. Each airplane has a unique code, a number of seats and a production year. An airplane belongs to a certain airplane model. Each model has a unique model number and is provided by a certain company. We have different airplanes of each model. Each flight is identified by a flight number, a scheduled departure time and a scheduled arrival time. We want also to keep track of the airport from which the flight is scheduled as well as the destination airport. Each airport is identified by a unique code, its name and the country in which it is located. Journeys of each flight are organized on a daily basis. Each journey is identified by a date, its actual departure time, its actual arrival time, and the number of passengers, in addition to the airplane that performs the journey. We want to keep track of the seats occupied, where each seat is identified by a seat number, a passenger name, nationality, age and phone number. Steps to be done Step 1: Data Modeling - Create an ER diagram, Step 2: Map ER diagram into a relational model, Step 3: Create your tables and fill them with few records of logical data, Step 4: SQL Queries SQL Queries List the total number of journeys done during October 2015. List the company name of all available airplanes. List the total number of airports in each country. List flight number, departing airport name, arrival airport name, scheduled departure time, scheduled arrival time of all the flights that has ‘France’ as their destination country. List the total number of passengers that traveled on ‘25-09-2015’. List the total number of passengers from each nationality that travelled on the journey date between ’11-10-2015’ and ’16-10-2015’ of the flight number ‘227’. List journey date, flight number, departing airport name, arrival airport name, actual departure time, actual arrival time of all the journeys that departed or landed at ‘Beirut International Airport’. List airplane code, number of seats, production year and airplane company of all airplanes produced after the year ‘2010’. List the average age of passengers who traveled to ‘Lebanon’ in ‘2015’. List flight number, journey date, departing airport, actual departure time, arrival airport, actual arrival time of all flights that are done on airbus airplanes (company name = ‘airbus’) on ’19-11-2015’ Write 5 additional queries that you find useful for this application. The queries should not be easy.

Airline Company An airline company is interested in implementing a database system to manipulate the flights that it organizes. The airline company has a certain number of airplanes. Each airplane has a unique code, a number of seats and a production year. An airplane belongs to a certain airplane model. Each model has a unique model number and is provided by a certain company. We have different airplanes of each model. Each flight is identified by a flight number, a scheduled departure time and a scheduled arrival time. We want also to keep track of the airport from which the flight is scheduled as well as the destination airport. Each airport is identified by a unique code, its name and the country in which it is located. Journeys of each flight are organized on a daily basis. Each journey is identified by a date, its actual departure time, its actual arrival time, and the number of passengers, in addition to the airplane that performs the journey. We want to keep track of the seats occupied, where each seat is identified by a seat number, a passenger name, nationality, age and phone number. Steps to be done Step 1: Data Modeling - Create an ER diagram, Step 2: Map ER diagram into a relational model, Step 3: Create your tables and fill them with few records of logical data, Step 4: SQL Queries SQL Queries List the total number of journeys done during October 2015. List the company name of all available airplanes. List the total number of airports in each country. List flight number, departing airport name, arrival airport name, scheduled departure time, scheduled arrival time of all the flights that has ‘France’ as their destination country. List the total number of passengers that traveled on ‘25-09-2015’. List the total number of passengers from each nationality that travelled on the journey date between ’11-10-2015’ and ’16-10-2015’ of the flight number ‘227’. List journey date, flight number, departing airport name, arrival airport name, actual departure time, actual arrival time of all the journeys that departed or landed at ‘Beirut International Airport’. List airplane code, number of seats, production year and airplane company of all airplanes produced after the year ‘2010’. List the average age of passengers who traveled to ‘Lebanon’ in ‘2015’. List flight number, journey date, departing airport, actual departure time, arrival airport, actual arrival time of all flights that are done on airbus airplanes (company name = ‘airbus’) on ’19-11-2015’ Write 5 additional queries that you find useful for this application. The queries should not be easy.

Systems Architecture

7th Edition

ISBN:9781305080195

Author:Stephen D. Burd

Publisher:Stephen D. Burd

Chapter3: Data Representation

Section: Chapter Questions

Problem 3RP

See similar textbooks