SQL代写:CS420 DBFlight

设计一个数据库,用于航空公司的航班管理。

Project description

Your City wants to build a database called DBFlight in order to inform its citizens about local flights

In this database, we plan to manage pilots, cities, planes and flights from one city to another.

A Pilot is characterized by his identifier, last name, first name and salary. Each pilot lives in one city. This latter is characterized by its identifier and name.

As for the plane, it is characterized by an identifier, a description, the maximum number of passengers. Note that an plane belongs to only one city whereas a city can own several planes.

Regarding to the flight, it is described by the following: A given flight is characterized by an identifier. In a given date, a flight is performed by only one pilot while a pilot can ensure several flights. Similarly, a plane can be assigned to different flights while for the same flight, only one plane is assigned

Other relevant information to consider such as:

  • A flight cannot perform more than once a day (example the flight 100 cannot perform twice a day)
  • A flight also is characterized by the city of departure and arrival as well as the departure date, and departure and arrival time.
  • In a given flight, the departure and arrival dates are the same

Part I

  1. Make the ERD diagram of dbFlight
  2. Convert the ERD to the logical model (relational model)

List of attributes to use:

ATTRIBUTTYPE, TAILLE
PILOT_IDN 3
LAST_NAMEV 20
FIRST_NAMEV 20
SALARYN 7,2
CITY_IDN 3
CITY_NAMEV 20
PLA_IDN 2
PLA_DESCV 20
MAX_PASSENGERN 3
FLIGHT_IDN 3
DEP_DATED
DEP_TIMEN 4

Part II

Suppose the following constraint of database DBFlight (below the logical design)

Database dbTransport

PILOTTYPE, SIZEExplanation
PILOT_IDN 3Pilot identification
LAST_NAMEV 20Last name
FIRST_NAMEV 20First name
CITY_IDN 3City identification
SALARYN 7,2salary

PLANE

COLUMNTYPE, SIZEExplanation
PLA_IDN 2Plane identification
PLA_DESCV 20Plane description
MAX_PASSENGERN 3Maximum of passengers
CITY_IDN 3Localization city id of a plane

CITY

COLUMNTYPE, SIZEExplanation
CITY_IDN 3City identification
CITY_NAMEV 20City name

FLIGHT

COLUMNTYPE, SIZEExplanation
FLIGHT_IDN 3Flight identification
PILOT_IDN 3Pilot identification
PLA_IDN 2Plane identification
CITY_DEPN 3City id departure
CITY_ARRN 3City id arrivals
DEP_DATEDDeparture date
DEP_TIMEN 4Departure time
ARR_TIMEN 4Arrival time
  • Pilot’ salary is between 5000 and 7500.(the values are included)
  • The maximum number of passenger cannot exceed 500
  • Departure time is less than arrival time
  • Pilot’s last name and first name, city name, plane description are mandatory
  1. Create the preceding tables in the right order (one command per table) . Add drop table in the beginning of the script.
  2. Populate the preceding tables (Strings must be entered in uppercase and date must be entered in the given format)

Part II

Answer to the following questions:

  1. Display the description and the capacity of the AIRBUS planes( Plane’s description that starts with A is AIRBUS, B is BOEING,C is CONCORDE)
  2. Displays the pilots (identification and name) who perform more than two flights from Montreal (it is required to use city name).
  3. Display the planes (plane id, its description, localization(city name) and the maximum number of passenger) that are located in OTTAWA and their max passenger is greater than 200 (display the result in the descending order of max passenger)
  4. Displays the pilots (pilot id and name) who perform at least one departure flight from MONTREAL.(use city name )
  5. Displays the pilots (pilot id, name and plane description) who pilot a BOEING.
  6. Displays the pilots (id and name) who earn the same salary as PETERS’s or LAHRIRE’s salary. (don’t’ include PETER and LAHRIRE pilots)
  7. Displays the pilots (pilot name and city name) who live in the same city as AIRBUS localization city.
  8. Displays the planes (description and maximum passenger) so that the corresponding max passenger is greater (]) than the max passenger of each plane located in Montreal.(use city name)
  9. Displays the total number of pilots in service.(pilots in service are pilots who perform at least one flight)
  10. For each AIRBUS in service during the afternoon (arr_time]=1200), displays its id, description, the departures and arrivals cities names.
  11. Create a view returning the pilots(last name,first name) who are not in service
  12. Create a view returning the pilot’s id, name, salary as well as the plane’s description that he pilots.(don’t return identical rows)
  13. Type the SQL command that displays the pilot id, the name, and the total number of times he pilot

What you should deliver

  • One sql file that contains the answer of Part II and Part III (question number and answer)
  • A pdf file that contains the question, the answer and the result (screenshot) of PII and PIII