Question

Movies( title, year, length, genre, studioName, producerC#) StarsIn(movieTitle, movie Year, starName) MovieStar(name, address, gender, birthdate) MovieExec(name, address, cert#, net Worth) Studio name, address, presC#) Declare the following referential integrity constraints for the Movie database a) The producer of a movie must be someone mentioned in MovieExec. Modification to MovieExec that violate this constraint are rejected? b) Repeat (a), but violations result in the producers in Movie being set to NULL c) Repeat (a), but violations result in the deletion or update of the offending Movie tuple. d) A movie that appears in Starln must also appear in Movie. Handle violations by rejecting the modifications? ANSWER) b) CREATE TABLE Movies title year length genre studioName CHAR (30) producerC# INT REFERENCES MovieExec (cert#) ON DELETE SET NULL ON UPDATE SET NULL PRIMARY KEY (Eitle, year) CHAR (100) INT. INT CHAR (10), Page 1 Need A and C d) CREATE TABLE Starsin movieTitle CHAR (100) REFERENCES Mowie(title) movieYe INT starName CHAR (30) PRIMARY KEY (movieTItle, movieYear, starName

Answer 1

Answer:

a) ALTER TABLE Movies  

Include CONSTRAINT FK_Producer  

Outside KEY (producer#C) REFERENCES MovieExec(cert#);  

b) Here we will make the segment as NOTNULL.  

Modify TABLE Movies  

Modify COLUMN producer#C varchar2 NOT NULL;  

c) The outside key falling choices figure out what activities the database motor should take on the off chance that you attempt to erase or refresh information in the referenced sections in the parent table.  

Modify TABLE Movies  

Include CONSTRAINT FK_Producer  

Remote KEY (producer#C) REFERENCES MovieExec(cert#)  

ON DELETE CASCADE  

ON UPDATE CASCADE;  

d) ALTER TABLE StarsIn  

Include CONSTRAINT FK_mname  

Remote KEY (movieTitle) REFERENCES Movies(title);  

e) ALTER TABLE StarsIn  

Include CONSTRAINT FK_sname  

Remote KEY (starname) REFERENCES MovieStar(name)  

ON DELETE CASCADE;