Quiz 1: Take-home, Sep 27, Monday 7:55 AM - Sep 29, Tuesday 7:55 AM ET, 2021 CSE 241: Database Systems, Fall 2021 ALL ANSWERS should be on a scanned or generated PDF submitted to Coursesite before Tuesday 7:55 AM ET, 2021. This quiz is closed collaboration and Internet but you can use the textbook, slides, and past homework solutions. Please take the time to print your answers neatly. If we can’t read it, we have to mark it wrong. YOU ARE TO DO ALL QUESTIONS AND WE WILL RANDOMLY GRADE SOME OF THEM. A SELECTED QUESTION UNANSWERED WILL RECEIVE A ZERO. If you cross an answer out, make the crossout obvious. We won’t accept retroactive “crossout” requests after you turn in your quiz. The questions 1 through 8 on this quiz use the following database schema: person (id, name, city) – holding data for all people in the enterprise department (dept, budget) – listing all departments in the enterprise emp (id, dept, sal) – listing the department in which the employee works and the employee’s salary mgr (dept, id) – listing the ID of the manager of the department. has office (id, room, phone) – listing the room number and phone number for the employee with ID id. Some employees may not have an office. Important constraints: • Primary keys are underlined. • There are several foreign-key constraints and assertions that follow from the English description. We have left those for you to infer as you answer the questions below. Also one would expect that a manager must be an employee listed as working in the department she or he manages. For simplicity on this quiz we are not enforcing that here, though we certainly might want to do that in a real enterprise. 1. Write a SQL query to find the ID and name of each employee whose salary is larger than the salary of that employee’s manager, ignoring any employee who has no manager. 2. Write a SQL query to find for each department the average salary of employees working in that department, ignoring departments that have no employees. 3. Once again, write a SQL query to find for each department the average salary of employees working in that department, but this time list a null average salary for those departments that have no employees. 4. Write a SQL query to find those departments for which every employee has an office. Note that if a department has no employees, then it is vacuously true that every employee in that department has an office. 1 5. Write a SQL query to list all departments that have no manager. Be sure to include those arising both from nulls in the database and from the lack of certain tuples in the database. 6. Write a SQL query to create a view that list the employees’ id, names, city, names of the departments that the employees work for, office room number and phone, without the employees’ salary information. Then suppose this view is materialized, explain when insertion a new tuple into this materialized view is allowed and prohibited, respectively. 7. Draw an E-R diagram for this enterprise that represents the description given above as accurately as possible. If you have to make some additional assumptions about the enterprise, state them clearly in English. We shall expect that if we apply the textbook algorithm to convert an E-R diagram to tables, we will get the set of schemas listed above. 8. Write a PL/SQL procedure that takes a department as an argument and lists each employee in that department, giving the employee’s ID and name. If there no employees, it is okay to print nothing at all. This being a quiz, fancy output format is not required. 9. Consider a trigger implemented to handle the firing of an employee. When an employee is deleted from the emp relation, the trigger removes the employee from person and from office. Additionally, if the employee fired happened to be the manager of a department, then that department’s manager is set to be null. Now suppose that the SQL DDL had a constraint on the mgr table saying that id cannot be null. What happens if a manager is fired and this trigger is run? Be sure to explain not only how the trigger does or does not run, but also the state of the database and the end of the execution of the delete. (We are not asking you to implement the trigger here). 10. Complete the following JDBC program using PreparedStatement to find the id and salary of those employees whose salary is greater than a threshold (given by the first main function argument) and is working for the specific department (given by the second main function argument). Then print out those id and salary returned by the query. import java.sql.*; public class FindEmpWithSalaryAbove { public static void main(String[] args) { double lowest_salary_level = Double.parseDouble(args[0]); String department_name = args[1]; try { Class.forName ("oracle.jdbc.driver.OracleDriver"); } catch(Exception e){e.printStackTrace();} try ( Connection con= DriverManager.getConnection( "jdbc:oracle:thin:@edgar1.cse.lehigh.edu:1521:cse241", "xyz123", "xyz123"); // 1. YourAnswerHere ) { // 2. YourAnswerHere } catch(Exception e){e.printStackTrace();} } } 2