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
/* Delete the tables if they already exist */ | |
drop table if exists Movie; | |
drop table if exists Reviewer; | |
drop table if exists Rating; | |
/* Create the schema for our tables */ | |
create table Movie(mID int, title text, year int, director text); | |
create table Reviewer(rID int, name text); | |
create table Rating(rID int, mID int, stars int, ratingDate date); |
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 |