Consider the following schema:


Student(Student,Status)
Took(Student,Course)
Course(Course,Credits,Type)


In a Student relation, Status can be 'B' (beginner), 'CPR' (completed program requirements),
and 'EG' (eligible to graduate). In a Course relation, Type can be 'C' (core course)
or 'E' (elective course).

Write the following row-level triggers which monitors insertions into Took:
(a) When a 'CPR' student completes 130 credits, change the student's status to 'EG'.
(b) When a beginner student completes all core ('C') courses plus 3 electives ('E'), change
the status from 'B' to 'CPR'.

Hint: The main thing in constructing such triggers is to rst gure out the conditions in the
WHEN clause. This conditions are similar to complex WHERE clauses. For instance, (b)
involves relational division (recall how to do such things with NOT EXISTS).


(a)


CREATE TRIGGER EligibleToGraduate
AFTER INSERT ON Took
REFERENCING NEW AS N
FOR EACH ROW
WHEN (
130 <= ( SELECT SUM(C.Credits)
FROM Took T, Course C, Student S
WHERE T.Student = N.Student
AND T.Course = C.Course
AND T.Student = S.Student
AND S.Status = 'CPR' )
)
UPDATE Student
SET Status = 'EG'
WHERE N.Student = Student


(b)

CREATE TRIGGER DoneWithProgram
AFTER INSERT ON Took
FOR EACH ROW
REFERENCING NEW AS N
WHEN (
EXISTS ( -- Student has status 'B'
SELECT *
FROM Student S
WHERE N.Student = S.Student
AND S.Status = 'B'
)
AND
NOT EXISTS (
( SELECT C.Course
FROM Course C
WHERE C.Type = 'C' )
EXCEPT
( SELECT T.Course
FROM Took T
WHERE T.Student = N.Student)
)
AND
3 <= ( SELECT COUNT(T.Course)
FROM Took T, Course C
WHERE T.Student = N.Student
AND T.Course = C.Course
AND C.Type = 'E' )
)
UPDATE Student
SET Status = 'CPR'
WHERE N.Student = Student

Computer Science & Information Technology

You might also like to view...

Ensuring proper alignment between overall information security goals and the activities of application and system security requires a ____.

A. process B. team C. method D. plan

Computer Science & Information Technology

Name three areas (features/tools) in Photoshop that incorporate the use of vector technology.

What will be an ideal response?

Computer Science & Information Technology

_____ defines how solid a color appears.?

A. ?Clarity B. ?Saturation C. ?Lightness D. ?Opacity

Computer Science & Information Technology

Breaking the trust a client has placed in an ethical hacker can lead to the ________.

Fill in the blank(s) with the appropriate word(s).

Computer Science & Information Technology