Need help performing task 4 in MySQL. I have the statements of what I have below

Question

Need help performing task 4 in MySQL. I have the statements of what I have below just need help making the adjustments for 26 and 27.

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.rID to Reviewer.rID

Reviewers updated: cascade

Reviewers deleted: set null

All others: error

27. Referential integrity from Rating.mID to Movie.mID

Movies deleted: cascade

All others: error

/* 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 PRIMARY KEY, title VARCHAR(200), year int, director VARCHAR(200), UNIQUE(title, year));
create table Reviewer(rID int PRIMARY KEY, name VARCHAR(200) NOT NULL);
create table Rating(rID int, mID int, stars int NOT NULL, ratingDate date, UNIQUE(rID, mID, ratingDate));

/* Populate the tables with our data */
insert into Movie values(101, 'Gone with the Wind', 1939, 'Victor Fleming');
insert into Movie values(102, 'Star Wars', 1977, 'George Lucas');
insert into Movie values(103, 'The Sound of Music', 1965, 'Robert 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, null);
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(207, '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

/*Only change required to implement these refrential integrity constraint is to change the table definition of Rating table*/

/* We have to add two constraints for each requirement, explaining them below

'Constraint' keyword followed by the name of constraint, it should be a logical representing the constraint

followed by 'Foreign key' key word followed by the name of foreign key column present in a current table

followed by 'References' keyword followed by primary table's name and the name of primary key column

followed by the constraints which we want to put, ON DELETE followed by an action means when there is deletion in

primary table the action mentioned after ON DELETE should be performed on concerned record in current table

similary for ON UPDATE followed by an action means when there is updation in

primary table the action mentioned after ON UPDATE should be performed on concerned record in current table

Same processes is followed to include other constraint as well

*/

create table Rating(rID int, mID int, stars int NOT NULL, ratingDate date, UNIQUE(rID, mID, ratingDate),

CONSTRAINT rID FOREIGN KEY (rID)

REFERENCES Reviewer(rID)

ON DELETE SET NULL

ON UPDATE CASCADE,

CONSTRAINT mID FOREIGN KEY (mID)

REFERENCES Movie(mID)

ON DELETE CASCADE);

/*P.S. I hope the explanation helps, please leave feedback for improvment, Thanks.*/

Related Questions

Navigate

• Browse (All)
• Browse N
• Subjects

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