This task uses a relational database schema and instance adapted from Fundamentals of Database Systems, Elmasri and Navathe. (Question 5.11), given below and available as an SQLite database (Company.db) on the course Canvas shell under module Resources: Sample Databases and Tools. EMPLOYEE Frame Mint name S+ BO Sex Salary Superson Supersan Dao John B Smith 123 789 1965-01-09 731 Fondres Houston, TX M 30000 333445552 30000 3334455565 Franklin T Wong 3345555 1955-12-08 638 Voss, Houston, TX M 40000 886665555 Alicia 1 Zelaya 998877 1968-01-19 3321 Castle Spring, TX F 2000 987654321 4 Jennifer s Wallace $876543211941-06-20 291 Berry Bellaine. TX F 43000 885665555 4 K Narayan 666884441962-09-15 975 Fire Oak Hex M 38000 333445555 5 Joyce Englia 45345342 453453551972-07-31 5631 ReHouston, TX F 25000 333445555 5 Ahmad V Jabby 98798740/1969-03-2950 Dolls Houston, TX M 25000 987654321 4 James E Boya 88866565519311-10 450 Stone, Houston, TX , M 55000 NULL Ramesh 4 DEPARTMENT Drame Research Administration Headquarters Onumberto 5 33 4 987654321 888665555 DEPT LOCATIONS Dnumber Dlocation 1988-05-22 1 Houston 1995-01-01 4 Stafford 1981-06-1 5 Beli Sugarland Houston PROJECT Pumber Plocation Dnum Product + Belle Product 2 Sugarland 5 Productz Houston 5 Computerization 10 Stafford 4 Reorganization 20 Houston 1 Newbenefits 30 Stafford Pohon 1 325 2 75 1 40.0 20.0 20.0 100 2 2 WORKS ON Esen 123456789 123456789 666884444 453453453 453453453 333445555 333445555 333445555 333445555 999887777 999887777 987987987 987987987 987654321 100 100 10 DEPENDENT 20 Ess Sex 30 100 300 100 10 10 F M 350 5.0 333445555 333445555 333445555 987654321 123456789 123456789 123456789 Dependent name Alice Theodore Joy Abner Michael Alice Esbeth Bdate Relationship 1986-04-05 Daughter 1983-10-25 Son 1958-05-03 Spouse 1942-02-28 1988-01-04 Son 1988-1230 Daughter 1967-05-05 Spouse 30 30 M Spouse M 20.0 15.0 987654321 888666556 20 20 NULL F 2007 Ramez Elmasri and Shamkant B. Navathe Most of the attribute names are self-explanatory. Super_SSN refers to corresponding employee's supervisor's SSN (Social Security Number). This example is based on US system, assume SSN is similar to Australian Tax File Number. Arrows indicate foreign keys and the corresponding attributes in parent relation. In the case of Super_SSN, the parent relation is the Employee relation itself (self-referencing). Assume the following table is added to the Company database: CREATE TABLE works_on_total ( essn NUMERIC (9) NOT NULL, totalProjects INTEGER, totalHours NUMERIC (9, 2), PRIMARY KEY (essn), FOREIGN KEY (essn) REFERENCES employee (ssn) ); Assume table works_on_total already has a row for each employee, detailing the current total number of projects they are working on, and the total number of hours that they are working. Q4.1 Write an SQL AFTER trigger for updating an employee's works_on_total entry each time they have a project added to table works on to update the current total number of projects they are working on, and the total number of hours that they are working. This task uses a relational database schema and instance adapted from Fundamentals of Database Systems, Elmasri and Navathe. (Question 5.11), given below and available as an SQLite database (Company.db) on the course Canvas shell under module Resources: Sample Databases and Tools. EMPLOYEE Frame Mint name S+ BO Sex Salary Superson Supersan Dao John B Smith 123 789 1965-01-09 731 Fondres Houston, TX M 30000 333445552 30000 3334455565 Franklin T Wong 3345555 1955-12-08 638 Voss, Houston, TX M 40000 886665555 Alicia 1 Zelaya 998877 1968-01-19 3321 Castle Spring, TX F 2000 987654321 4 Jennifer s Wallace $876543211941-06-20 291 Berry Bellaine. TX F 43000 885665555 4 K Narayan 666884441962-09-15 975 Fire Oak Hex M 38000 333445555 5 Joyce Englia 45345342 453453551972-07-31 5631 ReHouston, TX F 25000 333445555 5 Ahmad V Jabby 98798740/1969-03-2950 Dolls Houston, TX M 25000 987654321 4 James E Boya 88866565519311-10 450 Stone, Houston, TX , M 55000 NULL Ramesh 4 DEPARTMENT Drame Research Administration Headquarters Onumberto 5 33 4 987654321 888665555 DEPT LOCATIONS Dnumber Dlocation 1988-05-22 1 Houston 1995-01-01 4 Stafford 1981-06-1 5 Beli Sugarland Houston PROJECT Pumber Plocation Dnum Product + Belle Product 2 Sugarland 5 Productz Houston 5 Computerization 10 Stafford 4 Reorganization 20 Houston 1 Newbenefits 30 Stafford Pohon 1 325 2 75 1 40.0 20.0 20.0 100 2 2 WORKS ON Esen 123456789 123456789 666884444 453453453 453453453 333445555 333445555 333445555 333445555 999887777 999887777 987987987 987987987 987654321 100 100 10 DEPENDENT 20 Ess Sex 30 100 300 100 10 10 F M 350 5.0 333445555 333445555 333445555 987654321 123456789 123456789 123456789 Dependent name Alice Theodore Joy Abner Michael Alice Esbeth Bdate Relationship 1986-04-05 Daughter 1983-10-25 Son 1958-05-03 Spouse 1942-02-28 1988-01-04 Son 1988-1230 Daughter 1967-05-05 Spouse 30 30 M Spouse M 20.0 15.0 987654321 888666556 20 20 NULL F 2007 Ramez Elmasri and Shamkant B. Navathe Most of the attribute names are self-explanatory. Super_SSN refers to corresponding employee's supervisor's SSN (Social Security Number). This example is based on US system, assume SSN is similar to Australian Tax File Number. Arrows indicate foreign keys and the corresponding attributes in parent relation. In the case of Super_SSN, the parent relation is the Employee relation itself (self-referencing). Assume the following table is added to the Company database: CREATE TABLE works_on_total ( essn NUMERIC (9) NOT NULL, totalProjects INTEGER, totalHours NUMERIC (9, 2), PRIMARY KEY (essn), FOREIGN KEY (essn) REFERENCES employee (ssn) ); Assume table works_on_total already has a row for each employee, detailing the current total number of projects they are working on, and the total number of hours that they are working. Q4.1 Write an SQL AFTER trigger for updating an employee's works_on_total entry each time they have a project added to table works on to update the current total number of projects they are working on, and the total number of hours that they are working