keys defined for all its relations. Movies(t i t l e , ye a r , len g th , genre, studioName, producerC#) S ta rs ln (m ovieT itle. movieYear. starName) M ovieStar(name, address, gender, b irth d a te ) MovieExec(name, address, c e rt# , netWorth) S tu d io (name, address, presC#) Declare the following referential integrity constraints for the movie database as in Exercise 7.1.1. a) The producer of a movie must be someone mentioned in MovieExec. Modifications to MovieExec that violate this constraint are rejected. b) Repeat (a), but violations result in the producerC# 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 S ta rs ln must also appear in Movie. Handle violations by rejecting the modification. e) A star appearing in S ta rs ln must also app
--Explanations are provided as comments -- and solution is
provided beside the questions numbers.
--Here we are altering the table Movies by adding a foreign
key
-- add foreign key producerC# makes producerC# as foreign key and
it is being referenced from attribute name of
MovieExec
-- table. On update Restrict specifies that modifications to
name in MovieExec are rejected when the above Constraints
are
--violated.
(a) ALTER TABLE Movies ADD FOREIGN KEY producerC# REFERENCES
MovieExec(name) ON UPDATE RESTRICT;
--Here we are altering the table Movies by adding a foreign
key
-- add foreign key producerC# makes producerC# as foreign key and
it is being referenced from attribute name of
MovieExec
-- table. On update Restrict specifies that modifications to
name in MovieExec makes the value producerC# as NULL
--when the above Constraints are violated.
(b)ALTER TABLE Movies ADD FOREIGN KEY producerC# REFERENCES MovieExec(name) ON UPDATE SET NULL;
--Here we are altering the table Movies by adding a foreign
key
-- add foreign key producerC# makes producerC# as foreign key and
it is being referenced from attribute name of
MovieExec
-- table. On update Restrict specifies that modifications to
name in MovieExec makes the value producerC# as the
same
--value of name in Movies table,when the above Constraints
are violated.
(c)ALTER TABLE Movies ADD FOREIGN KEY producerC# REFERENCES MovieExec(name) ON UPDATE CASCADE;
--Here we are altering the table Movies by adding a foreign
key
-- add foreign key title makes title as foreign key and it is being
referenced from attribute movieTitle of StarsIn
-- table. On update NO ACTION specifies that modifications to
movieTitle in StarsIn are rejected when the above
--Constraints are violated.
(d) ALTER TABLE Movies ADD FOREIGN KEY title REFERENCES StarsIn(movieTitle) ON UPDATE NO ACTION;
--Please Note that you have given the sub-part (e) incomplete
Get Answers For Free
Most questions answered within 1 hours.