Consider the following schema:
Student(Name:STRING, Id:STRING, Status:STRING)
Transcript(StudId:STRING,Course:STRING,Grade:FLOAT)
A student has status ?good? if and only if the average grade for all courses taken by that student is above 3.0. Write the triggers that maintain the student status eld in accordance with these requirements. Namely, if the student has status ?good? and the average grade falls to 3.0 or less then the status is changed to ?bad.? If the current status is ?bad? and the average grade
raises to above 3.0 then the status is changed to ?good.? The status should not be changed in
any other case.
Solution:
CREATE TRIGGER GoodToBad
AFTER INSERT, UPDATE ON Transcript
REFERENCING NEW AS N
FOR EACH ROW
WHEN ( EXISTS ( SELECT S.Id
FROM Student S, Transcript T
WHERE S.Status = 'good' AND T.StudId = N.Id
AND S.Id = N.Id
GROUP BY S.Id
HAVING avg(T.Grade) =< 3 ) )
UPDATE Student
SET Status = 'bad'
WHERE Id = N.StudId
CREATE TRIGGER BadToGood
AFTER INSERT, UPDATE ON Transcript
REFERENCING NEW AS N
FOR EACH ROW
WHEN ( EXISTS ( SELECT S.Id
FROM Student S, Transcript T
WHERE S.Status = 'bad' AND T.StudId = N.Id
AND S.Id = N.Id
GROUP BY S.Id
HAVING avg(T.Grade) > 3 ) )
UPDATE Student
SET Status = 'good'
WHERE Id = N.StudId
For deletion, the triggers are similar, except that we need to use REFERENCING OLD AS.
For instance,
CREATE TRIGGER GoodToBadWhenDeleted
AFTER DELETE ON Transcript
REFERENCING OLD AS O
FOR EACH ROW
WHEN ( EXISTS ( SELECT S.Id
FROM Student S, Transcript T
WHERE S.Status = 'good' AND T.StudId = O.Id
AND S.Id = O.Id
GROUP BY S.Id
HAVING avg(T.Grade) =< 3 ) )
UPDATE Student
SET Status = 'bad'
WHERE Id = O.StudId
You might also like to view...
Critical thinking typically includes passively thinking about the issue, asking questions, analyzing and evaluating evidence and symptoms, and seeking different perspectives. These activities help you identify and evaluate different options when faced with a problem
Indicate whether the statement is true or false
?Disadvantages of a(n) ____ computer include that they are less powerful and offer less functionality than other types of computers.
A. ?tablet B. ?ultrabook C. ?notebook D. ?desktop
An organization has agreed on the software that will be used to virtualize the company's web servers. Which of the following should the administrator reference prior to purchasing physical servers to be deployed as hosts in the new virtual architecture?
A. The vendor's software compatibility list B. The vendor's service level agreement C. The vendor's hardware compatibility list D. The vendor's end user licensing agreement
In PowerPoint, a document file is called a(n) ____________________.
Fill in the blank(s) with the appropriate word(s).