# 代写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:

ATTRIBUT TYPE, TAILLE
PILOT_ID N 3
LAST_NAME V 20
FIRST_NAME V 20
SALARY N 7,2
CITY_ID N 3
CITY_NAME V 20
PLA_ID N 2
PLA_DESC V 20
MAX_PASSENGER N 3
FLIGHT_ID N 3
DEP_DATE D
DEP_TIME N 4

## Part II

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

### Database dbTransport

PILOT TYPE, SIZE Explanation
PILOT_ID N 3 Pilot identification
LAST_NAME V 20 Last name
FIRST_NAME V 20 First name
CITY_ID N 3 City identification
SALARY N 7,2 salary

### PLANE

COLUMN TYPE, SIZE Explanation
PLA_ID N 2 Plane identification
PLA_DESC V 20 Plane description
MAX_PASSENGER N 3 Maximum of passengers
CITY_ID N 3 Localization city id of a plane

### CITY

COLUMN TYPE, SIZE Explanation
CITY_ID N 3 City identification
CITY_NAME V 20 City name

### FLIGHT

COLUMN TYPE, SIZE Explanation
FLIGHT_ID N 3 Flight identification
PILOT_ID N 3 Pilot identification
PLA_ID N 2 Plane identification
CITY_DEP N 3 City id departure
CITY_ARR N 3 City id arrivals
DEP_DATE D Departure date
DEP_TIME N 4 Departure time
ARR_TIME N 4 Arrival 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

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

• QQ：99515681
• 邮箱：99515681@qq.com
• 工作时间：8:00-23:00
• 微信：codinghelp