=========================preview======================
(COMP231)2009_S_midterm.pdf
Back to COMP231 Login to download
======================================================
Spring 2009 Midterm Solutions
Problems 1-2 are multiple choice C circle only one letter corresponding to the correct answer. There is no mark deduction for wrong answers. The duration of the exam is 2 hours.
Problem 1 (50%)
Consider the following schema, where primary keys are underlined and foreign keys are in italic; mid is a reference to an employee id.
Employee(eid, ename, salary) Department(did, dname, mid) Works(eid, did, hours)
Since ename and dname are not keys, there may exist distinct employees and departments with the same name. We write for rename, for Cartesian product, . for set difference, and / for division.
1.1] Which expression describes the result of the following Algebra query (5%)?
eid (did = 1Works) .W1.eid(W1.hours > W2.hours ( (W1, did = 1Works) (W2, did=1Works)))
a) Find the ids of employees who work the maximum number of hours in the department with did=1.
b) Find the ids of employees who work the minimum number of hours in the department with did=1.
c) Find the ids of employees who do not work the maximum number of hours in the department with did=1.
d) Both b) and c)
e) None of the above.

1.2] Which expression describes the result of the following Algebra query (5%)?
E1.ename(E1.salary> E2.salary ( (E1,Employee) JOINeid Works JOINdid Department JOINmid=E2.eid (E2,Employee)))

a) Find the names of employees who have a higher salary than the manager of their department.
b) Find the names of employees who have the maximum salary in their department.
c) Find the names of employees who have a higher salary than another employee in the same department.
d) Find the names of employees who have a higher salary than their departments average salary.
e) None of the above.

1.3] Which Algebra query denotes the same result as the following SQL query (5%)? SELECT DISTINCT dname FROM Department D, Works W1, Works W2 WHERE D.did=W1.did AND D.did=W2.did AND W1.hours<20 AND W2.hours> 50
a) dname(Department JOINdid (W1, hours<20Works))
b) dname(Department JOINdid (W1, hours<20Works))
dname(Department JOINdid (W2, hours>50Works))
c) dname (did, dname(Department JOINdid (W1, hours<20Works)))

(did, dname(Department JOINdid (W2, hours>50Works)))
d) Both b) and c)
e) None of the above.

1.4] Which Algebra query denotes the same result as the following SQL query (5%)?
SELECT E.eid FROM Employee E WHERE NOT EXISTS ( SELECT D.did FROM Department D WHERE D.mid=1 EXCEPT SELECT W.did FROM Works W WHERE E.eid=W.eid)
a) eid,didWorks .mid=1Department
b) eid,didWorks / did(mid=1Department)
c) eid,did(Employee JOINeidWorks) / did(mid=1Department)
d) Both b) and c)
e) None of the above.

1.5] Which expression describes the result of the following SQL query (5%)? SELECT ename, SUM(hours) FROM Employee E, Works W, Department D WHERE E.eid=W.eid AND W.did=D.did AND D.mid=1 GROUP BY E.eid, ename HAVING COUNT