Database Courses

I took a sequence of three courses on databases: Database Concepts (LIS2780), Information Organization and Communication (LIS3784), and Advanced Database Management (LIS3781). These courses covered range of topics pertaining to databases. I learned how to use MySQL workbench to design and create databases. I also learned how SQL can be used to retrieve, update, insert, and delete data from databases.

Here is an ERD I created for a project in Information Organization and Communication:

For Advanced Database management, we were given a specific database to work with for a final project. I had to draw an ERD for this database:

I also had to write SQL statements for the project.

4. Write and execute select statements for the following. Show the script and the execution plan and result.

a. List the name and earned rating date of each employee who is a pilot and has earned a rating of ‘MEL’


b. Which charters operated with a distance of more than 500 miles and had a copilot in the crew? List the customer name, aircraft model name, and distance flown.


c. List the aircraft number, the model name and the number of charters for each aircraft


d. Make list of all charters with fields customer last name, model name and manufacturer, charter date, distance and hours flown, pilot's name, and copilot's name, if there is a copilot on the charter.


5. Write and execute update or insert statements and show a snip of the resulting table rows.

a. Add a new charter just like charter 10001 except the date, which should be ‘1-Dec-2015’ and the charter number (primary key) which should be 10020. Be sure to add the crew and other related objects, as necessary. This will take more than one insert statement.



b. Record the hiring of a new pilot, Ms. Grace Jones, with employee number 111, DOB '11-Jun-1998', the same pilot qualifications and earned ratings as employee 109.