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.

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 employees 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.