PLEASE MODIFY WHAT I HAVE ALREADY BELOW TO FIT THE ASSIGNMENT, I AM ALMOST DONE:

Question

PLEASE MODIFY WHAT I HAVE ALREADY BELOW TO FIT THE ASSIGNMENT, I AM ALMOST DONE:

1. Consider the following relation schemas: Department (dnumber, dname, employeecount), PK: dnumber. Employee (eid. fname, lname, salary, dno). PK: eid, FK: dno refs Department. Write triggers in PL/SQL that satisfy the following requirements: The employee count column in the Department table records the current number of employees in each department You should guarantee that, whenever an employee record is inserted, deleted or updated in the Employee table, the employee count attribute always has the correct value for the affected department(s) in the Department table. Also, no department in the organization should have an average salary lower than 10,000. When the insertion, deletion or update of an employee record causes the average salary of a department to be lower than 10,000, the corresponding operation should be cancelled. You may create additional tables or views if needed.l

Explanation / Answer

CREATE TRIGGER EMPLOYEE_COUNT
AFTER UPDATE | INSERT | DELETE
OF dno ON Employee FOR EACH ROW
BEGIN
UPDATE Department SET dnumber, employee_count
SELECT department, COUNT(1) AS "Number_of_employees"
FROM employees
GROUP BY department;
WHERE Department.dnumber = Number_of_employees.department
END;

Related Questions

Navigate

• Browse (All)
• Browse P
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.