Consider a database schema with three relations: emp (eid(underline):integer, ename:string, age:integer, salary:decimal(10,2)) works (eid(underline):integer, did(underline):integer, pct_time:integer) dept(did(underline):integer, dname:string, budget:decimal(10,2), managerid:integer)

The keys are underlined in each relation. Relation emp stores employee information such as unique identifier eid, employee name ename, age and salary. Relation dept stores the department unique identifier did, unique department name dname, the department budget and managerid which is the eid of the employee who is managing the department. The managerid value must always be found in the eid field of a record of the emp relation. The works relation tracks which employee works in which department, and what percentage of the time s/he allocates to that department. Each eid and each did in works appears in emp or dept respectively. Note that, an employee can work in several departments. Note that this setup is almost exactly what we have specified in createdb.sql, where "string" is translated to char or varchar.

a. Explain how to change the create tables in createdb.sql to match these specifications. Put this answer in the paper submission.

b. Create a view ManagerSummary that lists for every department the department name, manager ID and manager name, manager salary and the number of employees in that department. The view will have five columns with headings: DeptName, MgrID, MgrName, MgrSalary and EmpCount. Put this SQL in createview.sql and drop-view in dropview.sql.

c. Query the view above to retrieve the set of distinct salaries of managers who manage a department called Sales. Put this SQL in queryview1.sql.

d. Query the view above to find the name of the manager who manages most employees. If the same employee works in several departments, that employee is counted once in each of the departments. The manager is included in the count the same as all other employees, i.e., based on his or her records in the Works table. Put this SQL in queryview2.sql.