Consider the following database. Note that Dept_No in EMPLOYEE table is foreign

Question

Consider the following database. Note that Dept_No in EMPLOYEE table is foreign key. Total_Salary in DEPARTMENT table is a derived attribute and it keeps the total salary of all the employees working in a specific department. On the basis of Active Database Concepts, answer the following questions: Identify all possible events which may change the value of Total_Salary attribute. Write the active rules (triggers) for any two events you have identified in part A of this question. Use syntax of creating triggers SQL.

Explanation / Answer

A)
1) Whenever a New Employee come in the company, we need to update the Total_salary;
2) Whenever Salary of an Employee incremented or decremented
3) Whener an Employee leaves the company.

B)

CREATE TRIGGER update_salary AFTER UPDATE DELETE INSERTION ON employee
FOR EACH ROW
BEGIN
UPDATE department,Employee SET Total_Salary = sum(Emp_Salary) where department.Dept_NO = Employee.Dept_no
END;

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

---

---

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