1 Delete the tables if they already exist 2drop table if exists Movie; 3drop table if exists Reviewer; drop table if exists Rating; Create the schema for our tables*/ create 6 7 . table Movie(mID int, title VARCHAR(200), year int, director VARCHAR(200)); 8create table Reviewer(ID int, name VARCHAR (200)); 9create table Rating(rID int, mID int, stars int, ratingDate date); 10 Populate the tables with our data 12insert into Movie values (101, "Gone with the Wind', 1939, Victor Fleming' 13insert into Movie values (102, Star Wars', 1977, "George Lucas') 14insert into Movie values (103, The Sound of Music', 1965, Robert Wise); 15nsert into Movie values (184, E.T.', 1982, Steven Spielberg'); 16 . insert into Movie values(105, 'Titanic', 1997, "James Cameron'); 17insert into Movie values (106, Snow White, 1937, null); 18 . insert into Movie values(107, 'Avatar', 2009, 'James Cameron'); 19 insert into Movie values(108, 'Raiders of the Lost Ark., 1981, Steven Spielberg'); 20 21insert into Reviewer values (201, Sarah Martinez); 22insert into Reviewer values(202, Daniel Lewis') 23insert into Reviewer values (203, Brittany Harris'); 24insert into Reviewer values (204, 'Mike Anderson); 25insert into Reviewer values (205, Chris Jackson) 26insert into Reviewer values (206, 'Elizabeth Thomas') 27insert into Reviewer values (207, James Cameron; 28insert into Reviewer values (208, 'Ashley White') 29 insert into Rating values(201, 101, 2, 2011-01-22"); 31insert into Rating values (201, 101, 4, 2011-01-27) 32insert into Rating values(202, 186, 4, nul1); 33insert into Rating values (203, 103, 2, '2011-01-20*); 34 insert into Rating values(203, 108, 4, "2011-01-12'); 35insert into Rating values (203, 108, 2, '2011-01-30*; 36insert into Rating values(204, 101, 3, '2011-01-09') 37insert into Rating values(205, 103, 3, "2011-01-27'); 38insert into Rating values (205, 104, 2, 2011-01-22"); 39insert into Rating values (205, 108, 4, nul1); insert into Rating values(206, 107, 3, 2011-01-15'); 41insert into Rating values(206, 106, 5, '2011-01-19" Task 1: Constraint Declarations Modify the three CREATE TABLE statements in the rating_lab.sql to add the following constraint Your three resulting CREATE TABLE statements should meet both the Key constraints and Not- constraints. (I have attached some examples at the end of the file) Key Constraints 1. mID is a key for Movie and (title,year) is another key for Movie 2. rID is a key for Reviewer 3. (rID,mID ratingDate) is a key for Rating but with null values allowed Not-Null Constraints 4. Reviewer.name may not be NULL 5. Rating.stars may not be NULL Task 3: Constraint Enforcement Each of the following commands should generate an error. Note if one of the following didn't generate any error, you did something wrong with your CREATE TABLE statements cause these should have violated some of the required constraints. You may need to modify your CREATE TABLE statements to get proper error message from the following statements 11. update Movie set mID mID1; 12. insert into Movie values (109, Titanic', 1997, JC); 13. insert into Reviewer values (201, Ted Codd'); 14. update Rating set rID 205, mID 104; 15. insert into Reviewer values (209, null); Task 4: Referential Integrity Declarations Further modify one or more of your CREATE TABLE statements to include the following referential integrity constraints and policies. 26. Referential integrity from Rating.rlD to Reviewer.rlD Reviewers updated: cascade Reviewers deleted: set nul All others: error 26. Referential integrity from Rating.mID to Movie.mID Movies deleted: cascade All others: error