Consider the following relational schema. An employee can work in more than one
ID: 3740946 • Letter: C
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
Question 1
CREATE ASSERTION BUDINC
CHECK (NOT EXISTS(SELECT * FROM Emp AS es, Works AS wo, Dept AS de
WHERE es.eid = wo.eid AND wo.did = de.did AND es.salary > (SELECT salary
FROM Emp AS manager WHERE de.managerid = manager.eid)))
Question 2
CREATE TRIGGER BUDINC AFTER UPDATE OF salary ON Emp
REFERENCING OLD ROW AS odt,NEW ROW AS nwt
FOR EACH ROW WHEN (nwt.salary > odt.salary)
UPDATE Emp SET salary = nwt.salary WHERE eid IN
(SELECT em.eid FROM Emp AS em, Works AS wo, Dept AS de
WHERE nwt.eid = wo.eid AND wo.did = de.did
AND de.managerid = em.eid AND em.salary < nwt.salary)
Question 3
CREATE TRIGGER BUDINC AFTER UPDATE OF salary ON Emp
REFERENCING OLD ROW AS odt,NEW ROW AS nwt
FOR EACH ROW WHEN (nwt.salary > odt.salary)
BEGIN
UPDATE Emp
SET salary = nwt.salary
WHERE eid IN
(SELECT em.eid FROM Emp AS em, Works AS wo, Dept AS de
WHERE nwt.eid = wo.eid AND wo.did = de.did
AND de.managerid = em.eid AND em.salary < nwt.salary);
UPDATE Dept
SET bdg = 1 + (SELECT Sum(salary) FROM (SELECT DISTINT em.eid, salary
FROM Emp AS em, Works AS wo WHERE nwt.eid = wo.eid AND wo.eid = em.eid));
END