CSC 335 - Database System Concepts
Homework # 3
Due Date: March 1, 2011

1. [30 points] Exercise 4.12 page 155.
   

2. [20 points] Exercise 4.14 page 155 (write the query).
   

3. [25 points] Consider the database given in Figure 4.12, page 155 of the text. Create an SQL assertion that ensures that every name that appears in the relation address appears in either salaried_worker or hourly_worker, but not necessarily in both.
   

4. [25 points] Suppose you are given a relation emp(empid, dept, salary) and wish to maintain a materialized view deptsalary(dept, totalsalary) which stores the total salary for all employees of each department. Suppose the system does not support materialized views but supports triggers. Write SQL trigger(s) on insert and delete on emp to keep the relation deptsalary up to date. Do not worry about updates. (Note: if you solve this question using Oracle, do not create a view for deptsalary, just create a table for it.)