Consider the COMPANY database described in Figure 5.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 manages a department or supervises employees, set the Mgr_ssn for that department to NULL and set the Super_ssn for those employees to NULL.

Figure 5.6

Figure 5.6 One possible database state for the COMPANY relational database schema. EMPLOYEE Sex Salary 30000 333445555 Fname Minit Lname Ssn Bdate Address Super_ssn Dno 123456789 1965-01-09 731 Fondren, Houston, TX M 333445555 1955-12-08 638 Voss, Houston, TX 999887777 1968-01-19 3321 Castle, Spring, TX Wallace 987654321 1941-06-20 291 Berry, Bellaire, TX John B Smith Franklin Wong 40000 888665555 M 5 Alicia Zelaya F 25000 987654321 4 Jennifer F 43000 888665555 4 Narayan 666884444 1962-09-15 975 Fire Oak, Humble, TX M English 453453453 1972-07-31 5631 Rice, Houston, TX 987987987 1969-03-29 980 Dallas, Houston, TX Ramesh K 38000 333445555 Joyce A 25000 333445555 Ahmad V Jabbar M 25000 987654321 4 James E Borg 888665555 1937-11-10 450 Stone, Houston, TX M 55000 NULL 1 DEPARTMENT DEPT LOCATIONS Dname Dnumber Mgr_ssn Mgr_start date Dnumber Dlocation Research 333445555 1988-05-22 1 Houston Administration 4 987654321 1995-01-01 4 Stafford Headquarters 1 888665555 1981-06-19 5 Bellaire Sugarland 5 Houston WORKS ON PROJECT Essn Pno Hours Pname Pnumber Plocation Dnum 123456789 32.5 ProductX 1 Bellaire 5 123456789 2 7.5 ProductY Sugarland 5 666884444 3 40.0 ProductZ 3 Houston 453453453 20.0 Computerization 10 Stafford 4 453453453 20.0 Reorganization 20 Houston 1 333445555 10.0 Newbenefits 30 Stafford 4 333445555 3 10.0 333445555 10 10.0 DEPENDENT 333445555 20 10.0 Essn Dependent name Relationship Sex Bdate 999887777 30 30.0 333445555 Alice F 1986-04-05 Daughter 999887777 10 10.0 333445555 Theodore M. 1983-10-25 Son 987987987 10 35.0 333445555 Joy F 1958-05-03 Spouse 987987987 30 5.0 987654321 Abner M 1942-02-28 Spouse 987654321 30 20.0 123456789 Michael M. 1988-01-04 Son 987654321 20 15.0 123456789 Alice F 1988-12-30 Daughter 888665555 20 NULL 123456789 Elizabeth F 1967-05-05 Spouse