Last active
August 29, 2015 14:14
-
-
Save chinkouu/2e9bd7188c1266996949 to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- SQL Movie-Rating View Modification Exercises[ | |
-- Question 1 | |
-- Write an instead-of trigger that enables updates to the title attribute of view LateRating. | |
-- Policy: Updates to attribute title in LateRating should update Movie.title for the corresponding movie. (You may assume attribute mID is a key for table Movie.) Make sure the mID attribute of view LateRating has not also been updated -- if it has been updated, don't make any changes. Don't worry about updates to stars or ratingDate. | |
-- | |
-- Question 2 | |
-- Write an instead-of trigger that enables updates to the stars attribute of view LateRating. | |
-- Policy: Updates to attribute stars in LateRating should update Rating.stars for the corresponding movie rating. (You may assume attributes [mID,ratingDate] together are a key for table Rating.) Make sure the mID and ratingDate attributes of view LateRating have not also been updated -- if either one has been updated, don't make any changes. Don't worry about updates to title. | |
-- | |
-- Question 3 | |
-- Write an instead-of trigger that enables updates to the mID attribute of view LateRating. | |
-- Policy: Updates to attribute mID in LateRating should update Movie.mID and Rating.mID for the corresponding movie. Update all Rating tuples with the old mID, not just the ones contributing to the view. Don't worry about updates to title, stars, or ratingDate. | |
-- | |
-- Question 4 | |
-- Finally, write a single instead-of trigger that combines all three of the previous triggers to enable simultaneous updates to attributes mID, title, and/or stars in view LateRating. Combine the view-update policies of the three previous problems, with the exception that mID may now be updated. Make sure the ratingDate attribute of view LateRating has not also been updated -- if it has been updated, don't make any changes. | |
-- | |
-- Question 5 | |
-- Write an instead-of trigger that enables deletions from view HighlyRated. | |
-- Policy: Deletions from view HighlyRated should delete all ratings for the corresponding movie that have stars > 3. | |
-- | |
-- Question 6 | |
-- Write an instead-of trigger that enables deletions from view HighlyRated. | |
-- Policy: Deletions from view HighlyRated should update all ratings for the corresponding movie that have stars > 3 so they have stars = 3. | |
-- | |
-- Question 7 | |
-- Write an instead-of trigger that enables insertions into view HighlyRated. | |
-- Policy: An insertion should be accepted only when the (mID,title) pair already exists in the Movie table. (Otherwise, do nothing.) Insertions into view HighlyRated should add a new rating for the inserted movie with rID = 201, stars = 5, and NULL ratingDate. | |
-- | |
-- Question 8 | |
-- Write an instead-of trigger that enables insertions into view NoRating. | |
-- Policy: An insertion should be accepted only when the (mID,title) pair already exists in the Movie table. (Otherwise, do nothing.) Insertions into view NoRating should delete all ratings for the corresponding movie. | |
-- | |
-- Question 9 | |
-- Write an instead-of trigger that enables deletions from view NoRating. | |
-- Policy: Deletions from view NoRating should delete the corresponding movie from the Movie table. | |
-- | |
-- Question 10 | |
-- Write an instead-of trigger that enables deletions from view NoRating. | |
-- Policy: Deletions from view NoRating should add a new rating for the deleted movie with rID = 201, stars = 1, and NULL ratingDate. | |
-- | |
-- | |
-- 1 | |
create trigger myTrigger | |
instead of update of title on LateRating | |
for each row | |
when new.mID = old.mID | |
begin | |
update Movie | |
set title = new.title | |
where mID = old.mID; | |
end; | |
-- 2 | |
create trigger myTrigger | |
instead of update of stars on LateRating | |
for each row | |
when new.mID = old.mID and new.ratingDate = old.ratingDate | |
begin | |
update Rating | |
set stars = new.stars | |
where mID = old.mID | |
and ratingDate = old.ratingDate; | |
end; | |
-- 3 | |
create trigger myTrigger | |
instead of update of mID on LateRating | |
for each row | |
when new.mID <> old.mID | |
begin | |
update Movie set mID = new.mID where title = old.title; | |
update Rating set mID = new.mID where mid = old.mid; | |
end; | |
-- 4 | |
create trigger myTrigger | |
instead of update on LateRating | |
for each row | |
when old.mID in (select mID from Rating where ratingDate = new.ratingDate) | |
begin | |
update Movie set title = new.title, mID = new.mID | |
where old.mID = mID; | |
update Rating set stars = new.stars | |
where old.mID = mID and ratingDate = old.ratingDate; | |
update Rating set mID = new.mID | |
where old.mID = mID; | |
end; | |
-- 5 | |
create trigger myTrigger | |
instead of delete on HighlyRated | |
for each row | |
begin | |
delete from Rating | |
where mID = old.mID | |
and stars > 3; | |
end; | |
-- 6 | |
create trigger myTrigger | |
instead of delete on HighlyRated | |
for each row | |
begin | |
update Rating set stars = 3 | |
where mID = old.mID and stars > 3; | |
end; | |
--7 | |
create trigger myTrigger | |
instead of insert on HighlyRated | |
for each row | |
when new.mID in (Select mID from Movie where mID = new.mID and title = new.title) | |
begin | |
insert into Rating values (201, new.mID, 5, null); | |
end; | |
-- 8 | |
create trigger myTrigger | |
instead of insert on NoRating | |
for each row | |
when new.mID in (Select mID from Movie where mID = new.mID and title = new.title) | |
begin | |
delete from Rating where mID=new.mID; | |
end; | |
--9 | |
create trigger myTrigger | |
instead of delete on NoRating | |
for each row | |
begin | |
delete from Movie where mID=old.mID; | |
end; | |
--10 | |
create trigger myTrigger | |
instead of delete on NoRating | |
for each row | |
begin | |
Insert into Rating Values (201,old.mID,1,null); | |
end; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment