Question

keys defined for all its relations. Movies(t i t l e , ye a r ,...

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

Homework Answers

Answer #1

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

Know the answer?
Your Answer:

Post as a guest

Your Name:

What's your source?

Earn Coins

Coins can be redeemed for fabulous gifts.

Not the answer you're looking for?
Ask your own homework help question
Similar Questions
ADVERTISEMENT
Need Online Homework Help?

Get Answers For Free
Most questions answered within 1 hours.

Ask a Question
ADVERTISEMENT