I need Q1 only Q1: Consider the following database (5 marks) EMPLOYEE No Emp Nam

Question

I need Q1 only

Q1: Consider the following database (5 marks) EMPLOYEE No Emp Name Emp Address Emp Salary Dept No DEPARTMENT Dept No Dept Name Dept Location Total Salary Note that Dept_No in EMPLOYEE table is foreign key. Total Salary in DEPARTMENT table is a derived atribute and it keeps the total salary of all the employees working in a specific department. On the basis of Active Dstabase Concepts, answer the following questions: A. Identify all possible events which may change the value of Total_Salary attribute. B. Write the active rules (trizgers) for any two events you have identified in part A of this question. Use syatax of creating triggers SQL. Q2. Suppose that the system crashes before the Iwrite item,T2,D,25,26l entry is written to the log in the following figure; Which transactions have to be rolled back? Why? (1.5 marks) read tenlA) roed der(4)

Explanation / Answer

A:

Employee salary (Emp_Salary) that affects the total department salary

First let me tell you the Total_Salary of department is directly depend on the employee salary. If salary of employee will be change than total salary of employee will also be change because department Total_Salary is the sum of all employers salary in that department.

Dept_No is also effect the Total_Salary of department

If the department number of an employee is change or employee is transferring in some other department. So that total number of employee of the department will changed. Because when employee leave’s old department than total number of employee of that department is decreased and sum of salary of all employee in that department also decreases. Same thing when an employee join a new department than total number of employee in that department is increases so the total sum of salary of all employees in that department also increases. That is why Dept_No is also affecting the Total_Salary of department

B:

CREATE OR REPLACE TRIGGER t

BEFORE

    INSERT OR

    UPDATE OF Total_Salary OR

    UPDATE OF Dept_No OR

    DELETE

ON employees

BEGIN

CASE

    WHEN INSERTING THEN

      DBMS_OUTPUT.PUT_LINE('Inserting');

    WHEN UPDATING(' Total_Salary') THEN

      DBMS_OUTPUT.PUT_LINE('Updating salary');

    WHEN UPDATING(Dept_No') THEN

      DBMS_OUTPUT.PUT_LINE('Updating Dept_No');

    WHEN DELETING THEN

      DBMS_OUTPUT.PUT_LINE('Deleting');

END CASE;

END;

Related Questions

Navigate

• Browse (All)
• Browse I
• Subjects

---

---

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