Section 1 Ex ercise 8.1.1 a) CREATE VIEW RichExec AS SELECT * FROM MovieExec WHERE netWorth >= 10000000; b) CREATE VIEW StudioPres (name, address, cert#) AS SELECT MovieExec.name, MovieExec.address, MovieExec.cert# FROM MovieExec, Studio WHERE MovieExec.cert# = Studio.presC#; c) CREATE VIEW ExecutiveStar (name, address, gender, birthdate, cert#, netWorth) AS SELECT star.name, star.address, star.gender, star.birthdate, exec.cert#, exec.netWorth FROM MovieStar star, MovieExec exec WHERE star.name = exec.name AND star.address = exec.address; Exercise 8.1.2 a) SELECT name from ExecutiveStar WHERE gender = ‘f’; b) SELECT RichExec.name from RichExec, StudioPres where RichExec.name = StudioPres.name; c) SELECT ExecutiveStar.name from ExecutiveStar, StudioPres WHERE ExecutiveStar.netWorth >= 50000000 AND StudioPres.cert# = RichExec.cert#; Section 2 Ex ercise 8.2.1 The views RichExec and StudioPres are updatable; however, the StudioPres view needs to be created with a subquery. CREATE VIEW StudioPres (name, address, cert#) AS SELECT MovieExec.name, MovieExec.address, MovieExec.cert# FROM MovieExec WHERE MovieExec.cert# IN (SELECT presCt# from Studio); Ex ercise 8.2.2 a) Yes, the view is updatable. b) CREATE TRIGGER DisneyComedyInsert INSTEAD OF INSERT ON DisneyComedies REFERENCING NEW ROW AS NewRow FOR EACH ROW INSERT INTO Movies(title, year, length, studioName, genre) VALUES(NewRow.title, NewRow.year, NewYear.length, ‘Disney’, ‘comedy’); c) CREATE TRIGGER DisneyComedyUpdate INSTEAD OF UPDATE ON DisneyComedies REFERENCING NEW ROW AS NewRow FOR EACH ROW UPDATE Movies SET length NewRow.length WHERE title = NewRow.title AND year = NEWROW.year AND studionName = ‘Disney’ AND genre = ‘comedy’; Ex ercise 8.2.3 a) No, the view is not updatable...
