Chapter 26: Enhanced Data Models for Advanced Applications
1
CHAPTER 26: ENHANCED DATA MODELS FOR ADVANCED APPLICATIONS
Answers to Selected Exercises
26.34 – Consider the COMPANY database described in Figure 3.6. Using the syntax of
Oracle triggers, write active rules to do the following:
(a) Whenever an employee’s project assignments are changed, check if the total hours per
week spent on the employee’s projects are less than 30 or greater than 40; if so, notify the
employee’s direct supervisor.
(b) Whenever an EMPLOYEE is deleted, delete the PROJECT tuples and DEPENDENT
tuples related to that employee, and if the employee is managing a department or
supervising any employees, set the MGRSSN for that department to null and set the
SUPERSSN for those employees to null.
Answer:
(a) We assume that a procedure TELL_SUPERVISOR(ARGSSN) has been created. This
procedure looks for an employee whose SSN matches the procedure’s AGRSSN argument
and it notifies the supervisor of that employee.
CREATE TRIGGER INFORM_SUPERVISOR_ABOUT_HOURS
(b) CREATE TRIGGER DELETE_IN_CASCADE
AFTER DELETE ON EMPLOYEE
FOR EACH ROW
BEGIN
DELETE FROM WORKS_ON
26.35 – Repeat 26.34 but use the syntax of STARBURST active rules.
Answer:
(a) We assume that a procedure TELL_SUPERVISOR(ARGSSNS) has been created.
Chapter 26: Enhanced Data Models for Advanced Applications
2
This procedure looks for employees whose SSN matches the social security numbers
passed by the procedure’s AGRSSNS argument and it notifies supervisors of those
employees.
CREATE RULE INFORM_SUPERVISOR_ABOUT_HOURS ON WORKS_ON
WHEN UPDATED (HOURS)
(b)
CREATE RULE DELETE_IN_CASCADE ON EMPLOYEE
WHEN DELETED
THEN DELETE FROM WORKS_ON AS W
WHERE W.ESSN IN (SELECT ESSN FROM DELETED AS D
WHERE D.ESSN = W.ESSN);
DELETE FROM DEPENDENT AS P
26.36 – Consider the relational schema shown in Figure 26.18. Write active rules for keeping
the SUM_COMMISSIONS attribute of SALES_PERSON equal to the sum of the
COMMISSION attribute in SALES for each sales person. Your rules should also check if the
SUM_COMMISSIONS exceeds 100000; if it does, call a procedure
NOTIFY_MANAGER(S_ID). Write both statement-level rules in STARBURST notation and
row-level rules in Oracle.
Answer:
Oracle notation rules:
CREATE TRIGGER KEEP_EM_SAME
AFTER INSERT OR UPDATE OF COMMISION ON SALES
Chapter 26: Enhanced Data Models for Advanced Applications
3
100000
NOTIFY_MANAGER(NEW.SALES_PERSON_ID);
Starburst notation rules:
CREATE RULE KEEP_EM_SAME ON SALES
OR (SELECT S_ID FROM INSERTED));
CREATE RULE NOTIFY_MANAGEMENT ON SALES_PERSON
WHEN INSERTED OR UPDATED(SUM_COMISSIONS)
THEN NOTIFY_MANAGER(SELECT SALESPERSON_ID
FROM SALES_PERSON AS S
WHERE S.SUM_COMISSIONS > 100000)
AND
S.SALESPERSON_ID IN
26.37 – Consider the UNIVERSITY EER schema of Figure 8.10. Write some rules (in
English) that could be implemented via active rules to enforce some common integrity
constraints that you think are relevant to this application.
26.38 – Discuss which of the updates that created each of the tuples shown in Figure 26.9
were applied retroactively and which were applied proactively.
