The minimum requirement for active attendance: 3 tasks done
1. Consider the following view.
create view DEPT_SUMMARY (Dept, EmpCount, TotalSalary, AverageSalary) as
select DNO, COUNT(*), SUM(Salary), AVG(Salary) from EMPLOYEE
group by DNO.
a) Convert the following query into an executable form, that is,
expand the view occurrences in order to obtain a query that involves
base tables only. What optimizations are possible?
select Dept, AverageSalary from DEPT_SUMMARY
where EmpCount > (select EmpCount from DEPT_SUMMARY where Dept = 4).
b) Is the following operation allowed?
2. What is the result of the following triggers R1, R2?
R1: create trigger stock_change
after insert on change_log
for each row
update stock set amount = ...;
R2: create trigger save_change
after update of amount on stock
for each row
insert into change_log values (...);
3. Give triggers for the maintenance of the referential integrity
between the relations EMPLOYEE and DEPARTMENT. Transactions that
violate referential integrity are aborted. What kind of execution
semantics should be chosen for the trigger, that is, should the trigger
fire immediately or at commit time, within the same transaction or as a
separate transaction?
4. Repeat the previous exercise in such a way that the solution is
equivalent to what is obtained by the specifications given on page 189
of the work of E&N2 (E&N3: page 249). What execution semantics should be
chosen for the triggers?
5. Let's add a new attribute, NumberOfEmployees, to the table
DEPARTMENT. This attribute will hold the number of employee tuples
currently in the database for the department in question. (Cf. the ER
diagram shown on page 43 in E&N2, or page 65 in E&N3.) Give a trigger
(or triggers) to keep the attribute up-to-date. What execution
semantics should be chosen for the triggers?
6. Give an Embedded SQL/C or an Embedded SQL/Pascal program
that prints the name of each employee of the Research department
on the screen one by one, prompts a possible salary raise for
each employee, and installs the raise (if any) to the database.