Question 3 (20 points) Consider a database schema with three relations Deployee eidsinteger, namestrine integet. salarynreal) works toid:integer, did:integer, pet time:integer) Department is integer, string budget real, agridente The keys are underlined in each relation. Relationployee stores employee information such as unique identifiereld, employee name name age and salary. Relation Department stores the department unique identifier di department namens the department budget and managerid which is the eid of the employee who is managing the department. The managerid valumustaways be found in the eid field of a record of the Employee relation. The Works relation tracks which employee works in which department, and what percentage of the time whe locates to that department. Note that an employee can work in several departments Provide SQL statements for the following (a) Create a view Manager Summary that lists for every department the department name, manager 10 and manager name, manager salary and the number of employees in that department. The view will have five columns with headings Dept Name, Mgr D, Morane, Mgr Salary and EmpCount (b) Homework Query the view above to retrieve the set of distinct salaries of managers who manage a department with name starting with “Mar Homework Query the view above to find the name of the manager who manages most employees of the same employee works in several departments, that employee is counted once in each of the departments) Show transcribed image text Question 3 (20 points) Consider a database schema with three relations Deployee eidsinteger, namestrine integet. salarynreal) works toid:integer, did:integer, pet time:integer) Department is integer, string budget real, agridente The keys are underlined in each relation. Relationployee stores employee information such as unique identifiereld, employee name name age and salary. Relation Department stores the department unique identifier di department namens the department budget and managerid which is the eid of the employee who is managing the department. The managerid valumustaways be found in the eid field of a record of the Employee relation. The Works relation tracks which employee works in which department, and what percentage of the time whe locates to that department. Note that an employee can work in several departments Provide SQL statements for the following (a) Create a view Manager Summary that lists for every department the department name, manager 10 and manager name, manager salary and the number of employees in that department. The view will have five columns with headings Dept Name, Mgr D, Morane, Mgr Salary and EmpCount (b) Homework Query the view above to retrieve the set of distinct salaries of managers who manage a department with name starting with “Mar Homework Query the view above to find the name of the manager who manages most employees of the same employee works in several departments, that employee is counted once in each of the departments)
Expert Answer
Answer to Question 3 (20 points) Consider a database schema with three relations Deployee eidsinteger, namestrine integet. salaryn…