《数据库系统基础教程第八章答案.doc》由会员分享，可在线阅读，更多相关《数据库系统基础教程第八章答案.doc（7页珍藏版）》请在金锄头文库上搜索。
1、Section 1Exercise 8.1.1a)CREATE VIEW RichExec AS SELECT * FROM MovieExec WHERE netWorth = 10000000;b)CREATE VIEW StudioPres (name, address, cert#) ASSELECT MovieExec.name, MovieExec.address, MovieExec.cert# FROM MovieExec, Studio WHERE MovieExec.cert# = Studio.presC#;c)CREATE VIEW ExecutiveStar (nam
2、e, address, gender, birthdate, cert#, netWorth) ASSELECT star.name, star.address, star.gender, star.birthdate, exec.cert#, Worth FROM MovieStar star, MovieExec exec WHERE star.name = exec.name AND star.address = exec.address;Exercise 8.1.2a)SELECT name from ExecutiveStar WHERE gender = f;b)SELECT Ri
3、chExec.name from RichExec, StudioPres where RichExec.name = StudioPres.name;c)SELECT ExecutiveStar.name from ExecutiveStar, StudioPres WHERE ExecutiveSWorth = 50000000 AND StudioPres.cert# = RichExec.cert#;Section 2Exercise 8.2.1The views RichExec and StudioPres are updatable; however, the StudioPre
4、s view needs to be created with a subquery.CREATE VIEW StudioPres (name, address, cert#) ASSELECT MovieExec.name, MovieExec.address, MovieExec.cert# FROM MovieExec WHERE MovieExec.cert# IN (SELECT presCt# from Studio);Exercise 8.2.2a) Yes, the view is updatable.b)CREATE TRIGGER DisneyComedyInsert IN
5、STEAD OF INSERT ON DisneyComedies REFERENCING NEW ROW AS NewRowFOR EACH ROWINSERT 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
6、FOR EACH ROWUPDATE Movies SET length NewRow.lengthWHERE title = NewRow.title AND year = NEWROW.year ANDstudionName = Disney AND genre = comedy;Exercise 8.2.3a) No, the view is not updatable since it is constructed from two different relations.b)CREATE TRIGGER NewPCInsertINSTEAD OF INSERT ON NewPCREF
7、ERENCING NEW ROW AS NewRowFOR EACH ROW(INSERT INTO Product VALUES(NewRow.maker, NewRow.model, pc)(INSERT INTO PC VALUES(NewRow.model, NewRow.speed, NewRow.ram, NewRow.hd, NewRow.price);c)CREATE TRIGGER NewPCUpdateINSTEAD OF UPDATE ON NewPCREFERENCING NEW ROW AS NewRowFOR EACH ROWUPDATE PC SET price
8、= NewPC.price where model = NewPC.model;d)CREATE TRIGGER NewPCDeleteINSTEAD OF DELETE ON NeePCREFERENCING OLD ROW AS OldRowFOR EACH ROW(DELETE FROM Product WHERE model = OldRow.model)(DELETE FROM PC where model = OldRow.model);Section 3Exercise 8.3.1a)CREATE INDEX NameIndex on Studio(name);b)CREATE
9、INDEX AddressIndex on MovieExec(address);c)CREATE INDEX GenreIndex on Movies(genre, length);Section 4Exercise 8.4.1ActionNo IndexStar IndexMovie IndexBoth IndexesQ110041004Q210010044I2446Average2 + 98p1 + 98p24 + 96 p24 + 96 p16 2 p1 2 p2Exercise 8.4.2Q1 = SELECT * FROM Ships WHERE name = n;Q2 = SEL
10、ECT * FROM Ships WHERE class = c;Q3 = SELECT * FROM Ships WHERE launched = y;I = InsertsIndexesActionsNoneNameClass Launched Name & Class Name & Launched Class & Launched Three IndexesQ1502505022502Q21121212 2Q35050502650262626I24446668Average2 + 48p1 -p2 + 48p34 + 46 p3 - 2 p1 - 3 p24 + 46p1 - 2p2
11、+ 46p34 + 46p1 - 3p2 + 22p36 - 4p1 - 4p2 + 44p36 - 4p1 - 5p2 + 20p36 - 44p1 - 4p2 + 20p38 - 6p1 - 6p2 + 18p3The best choice of indexes (name and launched) has an average cost of 6 - 4p1 - 5p2 + 20p3 per operation.Section 5Exercise 8.5.1Updates to movies that involves title or yearUPDATE MovieProd SE
12、T title = newTitle where title=oldTitle AND year = oldYear;UPDATE MovieProd SET year = newYear where title=oldYitle AND year = oldYear;Update to MovieExec involving cert#DELETE FROM MovieProd WHERE (title, year) IN (SELECT title, year FROM Movies, MovieExec WHERE cert# = oldCert# AND cert# = produce
13、rC#);INSERT INTO MovieProd SELECT title, year, name FROM Movies, MovieExec WHERE cert# = newCert# AND cert# = producerC#;Exercise 8.5.2Insertions, deletions, and updates to the base tables Product and PC would require a modification of the materialized view.Insertions into Product with type equal to
14、 pc:INSERT INTO NewPC SELECT maker, model, speed, ram, hd, price FROM Product, PC WHERE Product.model = newModel and Product.model = PC.model;Insertions into PC:INSERT INTO NewPC SELECT maker, newModel, newSpeed, newRam, newHd, newPrice FROM Product WHERE model = newModel;Deletions from Product with type equal to pc:DELETE FROM NewPC WHERE maker = deletedMaker AND model=deletedModel;Deletions from PC:DELETE FROM NewPC WHERE model = deletedModel;Updates to PC:Update NewPC SET speed=PC.speed, ram=PC.ram, hd=PC.hd, price=PC.price FROM PC where model=
