Salaried_worker (name, office, phone, salary) hourly_worker (name, hourly_wage)

Question

Salaried_worker (name, office, phone, salary)

hourly_worker (name, hourly_wage)

address (name, street, city)

__________________________________________________________________

Solve question 1 using standard SQL. Solve question 2 using either standard SQL or Oracle. Please write clearly and explain your solution where needed.

1. Consider the database given in above. 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.

2. 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 an SQL or Oracle trigger on insert on emp to keep the relation deptsalary up to date. Do not worry about deletes or updates. You can assume that there is already a tuple for each department in deptsalary so you do not need to worry about new deparments. (Note: if you solve this question using Oracle, do not create a view for deptsalary, just create a table for it.)

Explanation / Answer

Answer:-

1):-

create assertion worker check (not exists ( select name from Salaried_worker = select name from Hourly_worker));

2):-

create or replace trigger Total_SalaryUpdate

after insert on emp

for each row

declare

dept_exists pls_integer :=0;

begin

select count(1)

into dept_exists

from deptsalary

where dept = :new.dept;

if dept_exists > 0 then

UPDATE deptsalary

SET totalsalary = totalsalary +:new.SALARY

where dept = :new.dept;

else

INSERT INTO deptsalary VALUES (:new.DEPT,:new.SALARY);

end if;

end;

Related Questions

Navigate

• Browse (All)
• Browse S
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.