Transcript Assignment2

Due Thursday November 14, 2013
1. Show the result of the following queries as they would apply to the
database of Figure 3.6 on page 72 of your textbook
(a) Retrieve the name and address of all employees who
work for the ‘research’ department:
RESEARCH_DEPT  DNAME = ‘Research’(DEPARTMENT)
RESEARCH_EMPS  (RESEARCH_EPT
DNUMBER = DNO EMPLYEE
RESULT  FNAME, LNAME, ADDRESS(RESEARCH_EMPS)
)
(b) For every project located in ‘Stafford’, list the project number, the controlling
department number, and the department manager’s last name, address, and the
birthdate.
STAFFORD_PROJS  PLOCATION = ‘Stafford’(PROJECT)
CONTR_DEPT  (STAFFORD_PROJS
PROJ_DEPT_MGR  CONTR_DEPT
DNUM=DNUMBER DEPARTMENT
MGRSSN=SSN EMPLOYEE
RESULT  PNUMBER,DNUM,LNAME,ADDRESS,BDATE(PROJ_DEPT_MGR)
)
)
(C) Retrieve the names of employees who have no dependents.
ALL_EMPS  SSN(EMPLOYEE)
EMPS_WITH_DEPS  ESSN(DEPENDENT)
EMPS_WITHOUT_DEPS  (ALL_EMPS – EMPS_WITH_DEPS)
RESULT  LNAME, FNAME(EMPS_WITHOUT_DEPS * EMPLOYEE)
2. (30) Specify the following queries on the database schema shown in
Figure 3.5, using the relational operators defined in the relational
algebra. Also show the result of each query as it would apply to the
database of Figure 3.6.
a. Retrieve the name of all employees in department 5 who work
more than 10 hours per week on the ‘Product X’ project.
b. List the names of all employees who have a dependent with the
same first name as themselves.
c. Find the names of all employees who are directly supervised by
‘Franklin Wong’.
d. For each project, list the project name and the total hours per week
(by all employees) spent on that project.
e. Retrieve the names of all employees who work on every project.
3. (20) Exercise 3.13 on Page 81
Consider the relation
CLASS(Course#, Univ_Section#, InstructorName,
Semester, BuildingCode, Room#, TimePeriod, Weekdays,
CreditHours).
This represents classes taught in a university, with unique
Univ_Section#. Identify what you think should be various
candidate keys, and write in your own words the constraints
under which each candidate key would be valid.
4. (20) Exercise 6.22 on Page 190
Consider the two tables T1 and T2 shown above (Figure
6.15 of your text). Show the results of the following
operations: