Create a new schema for this lab and import Lab07DB.sql.

• Create a second new schema for this lab and import dept_emp.sql.

1. Following the steps in Lab 4:
   • Reverse engineer the first newly created schema.
   • Reverse engineer the second newly created schema.

These new models will be needed to perform the following tasks.

Before proceeding with tasks 3 and 4, analyze the data in each of the tables and familiarize yourself with the two models.

1. For the Lab07DB schema, complete the following requirements:
2. Display the professor name, professor program, student first and last names from the studend and professor tables.  Referring to the model created in step 2, determine the PK/FK relationships to build the SQL joining the three tables.  Sort the data by professor name then by student last name.  
3. Display the professor name and student's first and last names who are being mentored.  The output should be sorted by professor's name, student's last name then first name.  The Mentor column on the student_professor table determines if a student is being mentored; value of 0 = no, 1 = yes. 
4. Display all columns from the professor table where the professor is identified as a mentor in the student_professor table.  A subquery is to be used to determine the mentor status of a professor.  

 

1. For the dept_emp schema, complete the following requirements:
2. Display all contents from the Departments and Employees tables, only employees who are assigned to a department should be displayed.
3. Using the same SQL as 4a, modify it to include employees who are not yet assigned to a department.
4. Using the same SQL as 4a, modify it to include departments who do not yet have any employees working in it