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
- Make the ERD diagram of dbFlight
- 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
- Create the preceding tables in the right order (one command per table) . Add drop table in the beginning of the script.
- 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:
- 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)
- Displays the pilots (identification and name) who perform more than two flights from Montreal (it is required to use city name).
- 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)
- Displays the pilots (pilot id and name) who perform at least one departure flight from MONTREAL.(use city name )
- Displays the pilots (pilot id, name and plane description) who pilot a BOEING.
- 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)
- Displays the pilots (pilot name and city name) who live in the same city as AIRBUS localization city.
- 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)
- Displays the total number of pilots in service.(pilots in service are pilots who perform at least one flight)
- For each AIRBUS in service during the afternoon (arr_time]=1200), displays its id, description, the departures and arrivals cities names.
- Create a view returning the pilots(last name,first name) who are not in service
- 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)
- 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