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

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

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

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