note: it\'s written in mysql. Movie ( mID, title, year, director ) English: Ther

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 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

Explanation / Answer

here is your task solutions : ------------>>>>>>

TASK     1 : -------------->>>>>>>>>
a)
i) ALTER TABLE Movie ADD PRIMARY KEY(mID);
ii) ALTER TABLE Movie ADD CONSTRAINT myUnique UNIQUE(title,year);

b) ALTER TABLE Reviewer ADD PRIMARY KEY(rID);
c) ALTER TABLE Rating ADD CONSTRAINT myUnique1 UNIQUE(rID,mID,ratingDate);
ALTER TABLE Rating MODIFY rID NULL;
ALTER TABLE Rating MODIFY mID NULL;
ALTER TABLE Rating MODIFY ratingDate NULL;

d) ALTER TABLE Reviewer MODIFY name NOT NULL;

e) ALTER TABLE Rating MODIFY stars NOT NULL;

TASK      2 : --------------------------->>>>>>>>>>>
a) ALTER TABLE Rating ADD FOREIGN KEY(rID) REFERENCES Reviewer(rID) ON DELETE SET NULL ON UPDATE CASCADE;
b) ALTER TABLE Rating ADD FOREIGN KEY(mID) REFERENCES Movie(mID) ON DELETE CASCADE;

Related Questions

Navigate

• Browse (All)
• Browse N
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.