Question
note: it's written in mysql. Movie ( mID, title, year, director ) English: There is a movie with ID number mID, a title, a release
note: it's written in mysql.
Movie ( mID, title, year, director )
English: There is a movie with ID number mID, a title, a release year, and a director.
Reviewer ( rID, name )
English: The reviewer with ID number rID has a certain name.
Rating ( rID, mID, stars, ratingDate )
English: The reviewer rID gave the movie mID a number of stars rating (1-5) on a certain ratingDate
Task 1:
Modify the three CREATE TABLE statements in the database to add the following constraints. Your three resulting CREATE TABLE statements should meet both the Key constraints and Not-Null constraints
key Constrains:-
a. mID is a key for Movie and (title,year) is another key for Movie
b. rID is a key for Reviewer
c.. (rID,mID,ratingDate) is a key for Rating but with null values allowed
Not-Null Constraints
d.Reviewer.name may not be NULL
e.Rating.stars may not be NULL
Task 2:
Further modify one or more of your CREATE TABLE statements to include the following referential integrity constraints and policies.
a. Referential integrity from Rating.rID to Reviewer.rID
Reviewers updated: cascade
Reviewers deleted: set null
b.Referential integrity from Rating.mID to Movie.mID
Movies deleted: cascade
create table Movie (mID int, title VARCHAR (200), year int, director VARCHAR (200)) create table Reviewer (rID int , name VARCHAR (200) create table Rating (rID int, mID int, stars int , ratingDate date) /Populate the tables with our data / insert into Movie values (10, "Gone with the Wind',1939, 'Victor Fleming'); insert into Movie values (102, Star Wars',1977, George Lucas' insert into Movie values (103, 'The Sound f Music', 1965, 'R bert Wise') ; insert into Movie values (104, "E.T.', 1982, 'Steven Spielberg insert into Movie values (105, 'Titanic',1997, James Cameron') insert into Movie values (106, Snow White, 1937, nul1) insert into Movie values (107, 'Avatar', 2009, James Cameron') insert into Movie values (108, 'Raiders of the Lost Ark', 1981, 'Steven Spielberg') insert into Reviewer values (201, "Sarah Martinez') insert into Reviewer values (202, Daniel Lewis') insert into Reviewer values (203, Brittany Harris' insert into Reviewer values (204, 'Mike Anderson insert into Reviewer values (205, "Chris Jackson insert into Reviewer values (206,Elizabeth Thomas' insert into Reviewer values (20 James Cameron insert into Reviewer values (208, Ashley White") insert into Rating values (201, 101, 2, 2011-01-22 insert into Rating values (201, 101, 4, '2011-01-27 insert into Rating values (202, 106, 4, null) insert into Rating values (203, 103, 2, 2011-01-20 insert into Rating values (203, 108, 4, '2011-01-12 insert into Rating values (203, 108, 2, 2011-01-30 insert into Rating values (204, 101, 3, '2011-01-09 insert into Rating values (205, 103, 3, '2011-01-27 insert into Rating values (205, 104, 2, 2011-01-22 insert into Rating values (205, 108, 4, null) insert into Rating values (206, 107, 3, '2011-01-15 insert into Rating values (206, 106, 5, '2011-01-19 insert into Rating values (207,107, 5, '2011-01-20 insert into Rating values (208, 104, 3, '2011-01-02
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started