This project is designed to help you gain experience with MySQL. You may refer to the documents in the Lecture Notes on MySQL for the various commands that will be needed to answer the questions. The database design and content for this exercise are for class purposes only and they do not represent the actual design of any airline database.
Please study the "make-airline-DB.sql" script file (attached in this post) which contains the commands to create the Airline Database that includes information about pilots, employee, flights, planes, and other tables includding populating them as shown in the make-airline-DB.sql.
Directions:
1. Upload the make-airline-DB.sql to the uisacad6.uis.edu.server.
2. Run the make-airline-DB.sql file in the SQL prompt using:
mysql> SOURCE make-airline-DB.sql;
Make sure that the make-airline-DB.sql is in the directory where you are running MySQL; otherwise you have to include the full path name where the file is located. Example:
mysql> SOURCE /home/netid/folder1/make-airline-DB.sql
Running the SQL script will remove any existing tables with same name, create then populate the relations.
3. Create a log of your session using tee:
sql> tee /home/netid/airline_netid.txt
The log file should be named this way: airline_[your UIS netid].txt. Example, if I were to do this exercise, I would name my log file as "airline_kzepp2.txt".
4. Verify that the tables have been created, using a select statement on each relation.
5. Verify that the tables have the necessary columns and types using describe statement.
6. Verify that the tuples are inserted using select statement on each relation.
7. When everything looks good, run all the operations as requested in the Questions below.
8. After completing the questions us SFTP (or WinSCP) to get the log file. Example,
sftp uisacad5.uis.edu
enter netid and password
psftp> get airline_netid.txt
This command will transfer a text file named airline_netid.txt to your PC's directory or folder where you invoke the sftp assuming that the log file is in the default folder at uisacad5 server; otherwise you have to change the directory or include the path names in the get command.
9. Open the log file you have created using notepad, wordpad, or textpad (please do not use Microsoft Word for this because it embeds a rich format in Blackboard and becomes unreadable), then go to the Exam Center and answer the questions (or simply copy and paste from the log file the respective answer to the questions on Project 1 in the Exam Center). You have one(1) hour to complete it. You should have completed the log file before the beginning the exam. The one hour duration is given just to allow you to copy the answers into Blackboard.
Questions:
Please include the SQL statement and result in each of the answer to get full points. 10 points for each questions.
1. List all the columns (not the contents) of aircraft and flight tables.
2. List the maker and model_no of all planes. (Hint: Use one table.)
3. List all flights originating from ORD. Include the num, origin, dets, dep-time, arr_time. (Hint use ont table.)
4. List the name, emp_no and salary of all pilots. (Hint: Use two tables.)
5. List the serial_no, model_no, maker of all aircrafts. (Hint: Use two tables.)
6. List all flights departing date (dep_date) on Oct 31. Include the departing date, origin, dest, departing time (dep_time) and arriving time (arr_time). (Hint: Use two tables.)
7. List all names of those who book their flight and sort the output by name. Include name, origin, dest, dep_date, dep_time, arr_time. (Hint: Use two tables.)
8. Provide the details of the flight of the passenger named Gates such as name, origin, dest, departing date, time of departure and arrival. (Hint: Use two tables and two conditions.)
9. Who can fly the B757 model? Provide the name, model_no and emp_no in your query. (Hint: Use two tables)
10. Give the total number of employees, the maximum salary, the minimum salary and the total salary of all employees. (Hint: Use aggregate functions and one table only.).