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

Transcribed Image Text:

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