Consider the following relational schema. An employee can work in more than one

Question

Consider the following relational schema. An employee can work in more than one department; the

pct time field of the Works relation shows the percentage of time that a given employee works in a given department.

Emp(eid: integer, ename: string, age: integer, salary: real)

Works(eid: integer, did: integer, pct time: integer)

Dept(did: integer, budget: real, managerid: integer)

Write SQL integrity constraints (domain, key, foreign key, or CHECK constraints; or assertions) or SQL triggers to ensure each of the following requirements, considered independently.

1. A manager must always have a higher salary than any employee that he or she manages.

2. Whenever an employee is given a raise, the manager’s salary must be increased to be at least as much.

3. Whenever an employee is given a raise, the manager’s salary must be increased to be at least as much. Further, whenever an employee is given a raise, the department’s budget must be increased to be greater than the sum of salaries of all employees in the department.

Explanation / Answer

1. A manager must always have a higher salary than any employee that he or she manages.

Note : Because this involves more than one table, you have the hint that an assertion is needed.

CREATE ASSERTION MangerMakesMore
CHECK (NOT EXISTS
(SELECT *
FROM Emp AS e, Works AS w, Dept AS d
WHERE e.eid = w.eid AND
w.did = d.did AND
e.salary > (SELECT salary
FROM Emp AS manager
WHERE d.managerid = manager.eid)))

2. Whenever an employee is given a raise, the manager’s salary must be increased to be at least as much.

Note : Here we know we want a trigger because upon a certain event occurring, we want to check a condition and then if the condition is true we want to make a change to the database

CREATE TRIGGER ManSalEqEmp
AFTER UPDATE OF salary ON Emp
REFERENCING
OLD ROW AS OldTuple,
NEW ROW AS NewTuple
FOR EACH ROW
WHEN (NewTuple.salary > OldTuple.salary)
UPDATE Emp
SET salary = NewTuple.salary
WHERE eid IN
(SELECT man.eid
FROM Emp AS man, Works AS w, Dept AS d
WHERE NewTuple.eid = w.eid
AND w.did = d.did
AND d.managerid = man.eid
AND man.salary < NewTuple.salary)

3. Whenever an employee is given a raise, the manager’s salary must be increased to be at least as much. Further, whenever an employee is given a raise, the department’s budget must be increased to be greater than the sum of salaries of all employees in the department.

CREATE TRIGGER ManSalEqEmpAndRaiseBudget
AFTER UPDATE OF salary ON Emp
REFERENCING
OLD ROW AS OldTuple,
NEW ROW AS NewTuple
FOR EACH ROW
WHEN (NewTuple.salary > OldTuple.salary)
BEGIN
UPDATE Emp
SET salary = NewTuple.salary
WHERE eid IN
(SELECT man.eid
FROM Emp AS man, Works AS w, Dept AS d
WHERE NewTuple.eid = w.eid
AND w.did = d.did
AND d.managerid = man.eid
AND man.salary < NewTuple.salary);
UPDATE Dept
SET budget = 1 + (SELECT Sum(salary)
FROM (SELECT DISTINT e.eid, salary
FROM Emp AS e, Works AS w
WHERE NewTuple.eid = w.eid
AND w.eid = e.eid));
END

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

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