NEW YORK UNIVERSITY
COMPUTER SCIENCES DEPARTMENT

MIDTERM EXAM - PRACTICE QUESTIONS
G22.2433-001 (Database Systems) Spring 2000
Instructor: Viswanath Poosala

These are a few questions to practice your SQL and functional dependency algorithms. Of course, the midterm will have more than just these, but I don't think you need practice questions for them -- just study the text book.

1. Assume that we have an employee-project schema with the following relations: (Keys are in all capitals.)

2.
• Employee(fname, minit, lname, SSN, bdate, address, sex, salary, superssn, dno); fname,minit,lname is the employee's name; bdate is birth date; superssn is supervisor's social security #; dno is dept #
• Department(dname, DNUMBER, mgrssn, mgrstartdate); mgrssn is manager ssn
• Dept_locations(DNUMBER, DLOCATION); dlocation is department location
• Project(Pname, PNUMBER, plocation, dnum)
• Works_on(ESSN, PNO, hours); ESSN is employee ssn, pno is project number
• Dependent(ESSN, DEPENDENT_NAME, sex, bdate, relationship)
Other constraints:
• An employee belongs to a single department
• An employee has a unique supervisor
• An employee can have many dependents
• A department can be located in many locations and can control many projects.
• A project can have only one controlling dept.
• An employee can work on many projects (they need not be controlled by his/her dept
• Design an E-R diagram for this.
• Identify all the foreign key constraints.
• Write the following queries in SQL:
• Retrieve the birthdate and address of the employee whose name is "John B. Smith".

•

Select bdate, address
from Employee
where fname = 'John' and minit = 'B' and lname = 'Smith'

• Retrieve the name and address of all employees who work for the ``Research'' department.

•

Select fname,lname,address
from Employee, Department
where dname = 'Research' and dnumber = dno

• For every project located in "Stafford", list the project number, the controlling department number, and the department manager's last name, address birthdate.

•

Select pnumber, dnum, lname, address, bdate
from Project, Department, Employee
where dnum = dnumber and mgrssn = ssn and plocation = 'Stafford'

• Find the names of employees who work on *all* the projects controlled by department number 5.

•

Select fname,lname
from Employee
where not exists
((select pnumber from Project where dnum = 5)
except
(select pno from Works_On where ssn = essn))

• Retrieve the name of all employees who have no dependents.

•

Select fname,lname
from Employee
where not exists
(Select * from Dependent where ssn = essn)

• Find the sum of the salaries of all the employees, the maximum, the minimum and avergae salaries.

•

Select sum(salary), max(salary), min(salary), avg(salary)
from Employee

• Retrieve the total number of employees in the "Research" department.

•

Select count(*)
from Employee, Department
where dno = dnumber and dname='Research'

• For each dept, retrieve the dept number, the number of employees in the dept and their average salary.

•

Select dno, count(*), avg(salary)
from Employee
group by dno

• For each project on which more than two employees work, retrieve the project number, the project name and the number of employees who work on that project.

•

Select pnumber, pname, count(*) from Project, Works_On
where pnumber = pno
group by pnumber, pname
having count(*) > 2

• For each dept having more than 5 employees, retrieve the department number, and the number of employees making more than \$40,000.

•

Select dname, count(*)
from Department, Employee
where dnumber = dno and salary > 40000 and
dno in
(Select dno from Employee
group by dno
having count(*) > 5)
group by dname
The following answer is incorrect (why?):

Select dname, count(*)
from Department, Employee
where dnumber = dno and salary > 40000
group by dname having count(*) > 5

3. Compute the closure of the following set F of functional dependencies for relation schema R = (A,B,C,D,E).
• A -> BC;   CD -> E;   B -> D;   E -> A
• What are the candidate keys of R
• What is the closure of a candidate key of R

Starting with A->BC, we can conclude A->B, A->C.
Applying various rules:
A->B, B->D  -----> A->D
A->CD, CD->E ----> A->E
so: A->ABCDE
E->A ----> E -> ABCDE
CD-> -----> CD -> ABCDE
B->D, BC->CD ----> BC->ABCDE
...

Candidate keys: A, BC, CD, E.
Closure of a candidate key is R itself.