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)
CREATE ASSERTION MangerSalaryMore
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)
CREATE TRIGGER ManagerSalaryAtLeast
AFTER UPDATE OF salary ON Emp
REFERENCING
OLD ROW AS OldTuple,
NEW ROW AS NewRow
FOR EACH ROW
WHEN (NewRow.salary > OldTuple.salary)
UPDATE Emp
SET salary = NewRow.salary
WHERE eid IN
(SELECT man.eid
FROM Emp AS man, Works AS w, Dept AS d
WHERE NewRow.eid = w.eid
AND w.did = d.did
AND d.managerid = man.eid
AND man.salary < NewRow.salary)

-- 3)
CREATE TRIGGER MgrSalEqualEmpAndRaiseBudget
AFTER UPDATE OF salary ON Emp
REFERENCING
OLD ROW AS OldTuple,
NEW ROW AS NewRow
FOR EACH ROW
WHEN (NewRow.salary > OldTuple.salary)
BEGIN
UPDATE Emp
SET salary = NewRow.salary
WHERE eid IN
(SELECT m.eid
FROM Emp AS m, Works AS w, Dept AS d
WHERE NewRow.eid = w.eid
AND w.did = d.did
AND d.managerid = m.eid
AND m.salary < NewRow.salary);
UPDATE Dept
SET budget = 1 + (SELECT Sum(salary)
FROM (SELECT DISTINT e.eid, salary
FROM Emp AS e, Works AS w
WHERE NewRow.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.