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. 3. 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-92 integrity constraints (domain, key, foreign key, or CHECK constraints; or assertions) or SQL:1999 triggers to ensure each of the following requirements, considered independently a. Employees must make a minimum salary of $20000. Create the Emp table with CHECK constraint: b. Every manager must be also be an employee Create an assertion) C. The total percentage of all appointments for an employee must be under 100%. (Add CHECK constraint to the Works table)

Explanation / Answer

If you have any doubts, please give me comment...

-- a)

CREATE TABLE Emp (

eid INTEGER,

ename CHAR(20),

age INTEGER,

salary REAL,

PRIMARY KEY (eid),

CHECK (salary > 20000)

);

-- b)

CREATE ASSERTION ManagerIsEmployee

CHECK ((

SELECT COUNT (*)

FROM Dept D

WHERE D.managerid NOT IN(

SELECT * FROM Emp

))= 0);

-- c)

CREATE TABLE Works (

eid INTEGER,

did INTEGER,

pct time INTEGER,

PRIMARY KEY (eid, did),

CHECK ((SELECT COUNT (W.eid)

FROM Works W

GROUP BY W.eid

HAVING Sum(pct_time) > 100) = 0));

-- d)

CREATE ASSERTION ManagerHigherSalary

CHECK (SELECT E.eid

FROM Emp E, Emp M, Works W, Dept D

WHERE E.eid = W.eid

AND W.did = D.did

AND D.managerid = M.eid

AND E.salary > M.salary

);

-- e)

CREATE TRIGGER GiveRaise AFTER UPDATE ON Emp

WHEN old.salary < new.salary

FOR EACH ROW

BEGIN

UPDATE Emp M

SET M.Salary = new.salary

WHERE M.salary < new.salary AND M.eid IN (

SELECT D.mangerid

FROM Emp E, Works W, Dept D

WHERE E.eid = new.eid AND E.eid = W.eid AND W.did = D.did);

END;

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

---

---

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