2022年数据库系统基础教程答案ch .pdf

《2022年数据库系统基础教程答案ch .pdf》由会员分享，可在线阅读，更多相关《2022年数据库系统基础教程答案ch .pdf（7页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、数据库系统基础教程答案ch8 Section 1 Exercise 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

2、VIEW ExecutiveStar (name, address, gender, birthdate, cert#, netWorth) AS SELECT 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.2 a) SELECT name from ExecutiveStar W

3、HERE gender = f ;b) SELECT RichExec.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 2 Exercise 8.2.1 The views RichExec and StudioPres are

4、 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); Exercise 8.2.2 a) Yes, the view is updatable.

5、 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 1 页，共 7 页 - - - - - - - - - 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.ti

6、tle, 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;

7、Exercise 8.2.3 a) No, the view is not updatable since it is constructed from two different relations. b) CREATE TRIGGER NewPCInsert INSTEAD OF INSERT ON NewPC REFERENCING NEW ROW AS NewRow FOR EACH ROW (INSERT INTO Product VALUES(NewRow.maker, NewRow.model, pc) (INSERT INTO PC VALUES(NewRow.model, N

8、ewRow.speed, NewRow.ram, NewRow.hd, NewRow.price); c) CREATE TRIGGER NewPCUpdate INSTEAD OF UPDATE ON NewPC REFERENCING NEW ROW AS NewRow FOR EACH ROW UPDATE PC SET price = NewPC.price where model = NewPC.model; d) CREATE TRIGGER NewPCDelete INSTEAD OF DELETE ON NeePC REFERENCING OLD ROW AS OldRow F

9、OR EACH ROW (DELETE FROM Product WHERE model = OldRow.model) (DELETE FROM PC where model = OldRow.model); Section 3 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 2 页，共 7 页 - - - - - - - - - Exercise 8.3.1 a) CREATE INDEX NameIndex on Studio(name); b) CREATE INDEX Ad

10、dressIndex on MovieExec(address); c) CREATE INDEX GenreIndex on Movies(genre, length); Section 4 Exercise 8.4.1 Action No Index Star Index Movie Index Both Indexes Q1 100 4 100 4 Q2 100 100 4 4 I 2 4 4 6 Average 2 + 98p1 + 98p2 4 + 96 p24 + 96 p16 2 p1 2 p2Exercise 8.4.2 Q1 = SELECT * FROM Ships WHE

11、RE name = n; Q2 = SELECT * FROM Ships WHERE class = c; Q3 = SELECT * FROM Ships WHERE launched = y; I = Inserts Indexes Actions None Name Class Launched Name & Class Name & Launched Class & Launched Three Indexes Q1 50 2 50 50 2 2 50 2 Q2 1 1 2 1 2 1 2 2 Q3 50 50 50 26 50 26 26 26 I 2 4 4 4 6 6 6 8

12、Average 2 + 48p1 -p2 + 48p3 4 + 46 p3- 2 p1 - 3 p24 + 46p1 - 2p2 + 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. 名师资料总结 - - -精品资料欢迎下载 -

13、- - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 3 页，共 7 页 - - - - - - - - - Section 5Exercise 8.5.1 Updates to movies that involves title or year UPDATE MovieProd SET title = newTitle where title=oldTitle AND year = oldYear; UPDATE MovieProd SET year = newYear where title=oldYitle AND year

14、= oldYear;Update to MovieExec involving cert# DELETE FROM MovieProd WHERE (title, year) IN ( SELECT title, year FROM Movies, MovieExec WHERE cert# = oldCert# AND cert# = producerC# ); INSERT INTO MovieProd SELECT title, year, name FROM Movies, MovieExec WHERE cert# = newCert# AND cert# = producerC#;

15、 数据库系统基础教程答案ch8数据库系统基础教程答案ch8数据库系统基础教程答案ch8数据库系统基础教程答案ch8数据库系统基础教程答案ch8数据库系统基础教程答案ch8数据库系统基础教程答案ch8数据库系统基础教程答案ch8数据库系统基础教程答案ch8数据库系统基础教程答案ch8数据库系统基础教程答案ch8数据库系统基础教程答案ch8数据库系统基础教程答案ch8数据库系统基础教程答案ch8数据库系统基础教程答案ch8数据库系统基础教程答案ch8数据库系统基础教程答案ch8数据库系统基础教程答案ch8数据库系统基础教程答案ch8数据库系统基础教程答案ch8数据库系统基础教程答案ch8数据库系统

16、基础教程答案ch8数据库系统基础教程答案ch8数据库系统基础教程答案ch8数据库系统基础教程答案ch8数据库系统基础教程答案ch8数据库系统基础教程答案ch8数据库系统基础教程答案ch8数据库系统基础教程答案ch8数据库系统基础教程答案ch8数据库系统基础教程答案ch8数据库系统基础教程答案ch8数据库系统基础教程答案ch8Exercise 8.5.2 Insertions, deletions, and updates to the base tables Product and PC would require a modification of the materialized view

17、. Insertions into Product with type equal to pc:INSERT INTO NewPC SELECT maker, model, speed, ram, hd, price FROM Product, PC WHERE Product.model = newModel and Product.model = PC.model; 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 4 页，共 7 页 - - - - - - - - - Inser

18、tions 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 m odel = del

19、etedModel ;Updates to PC: Update NewPC SET speed=PC.speed, ram=PC.ram, hd=PC.hd, price=PC.price FROM PC where model=pc.model; Update to the attribute model needs to be treated as a delete and an insert. Updates to Product: Any changes to a Product tuple whose type is pc need to be treated as a delet

20、e or an insert, or both. Exercise 8.5.3 Modifications to the base tables that would require a modification to the materialized view: inserts and deletes from Ships, deletes from class, updates to a Class displacement. Deletions from Ship: UPDATE ShipStats SET displacement=(displacement * count) (SEL

21、ECT displacement FROM Classses WHERE class = DeletedShipClass) / (count 1), count = count 1 WHERE country = (SELECT country FROM Classes WHERE class= DeletedShipClass);Insertions into Ship: Update ShipStat SET displacement=(displacement*count) + (SELECT displacement FROM Classes WHERE class= Inserte

22、dShipClass)名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 5 页，共 7 页 - - - - - - - - - ) / (count + 1), count = count + 1 WHERE country = (SELECT country FROM Classes WHERE classes=InsertedShipClass);Deletes from Classes: NumRowsDeleted = SELECT count(*) FROM ships WH

23、ERE class = DeletedClass;UPDATE ShipStats SET displacement = (displacement * count) - (DeletedClassDisplacement * NumRowsDeleted) / (count NumRowsDeleted), count = count NumRowsDeleted WHERE country = DeletedClassCountry;Update to a Class displacement:N = SELECT count(*) FROM Ships where class = Upd

24、atedClass ;UPDATE ShipsStat SET displacement = (displacement * count) + (oldDisplacement newDisplacement) * N)/count WHERE country = UpdatedClassCountry;Exercise 8.5.4 Queries that can be rewritten with the materialized view: Names of stars of movies produced by a certain producer SELECT starName FR

25、OM StarsIn, Movies, MovieExec WHERE movieTitle = title AND movieYear = year AND producerC# = cert# AND name = Max Bialystock;Movies produced by a certain producer SELECT title, year FROM Movies, MovieExec Where producerC# = cert# AND name = George Lucas ; Names of producers that a certain star has w

26、orked with SELECT name 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 6 页，共 7 页 - - - - - - - - - FROM Movies, MovieExec, StarsIn Where producerC#=cert# AND title=movieTitle AND year=movieYear AND starName=Carrie Fisher;The number of movies produced by given producer

27、 SELECT count(*) FROM Movies, MovieExec WHERE producerC#=cert# AND name = George Lucas ;Names of producers who also starred in their own movies SELECT name FROM Movies, StarsIn, MovieExec WHERE producerC#=cert# AND movieTitle = title AND movieYear = year AND MovieExec.name = starName; The number of

28、stars that have starred in movies produced by a certain producer SELECT count(DISTINCT starName) FROM Movies, StarsIn, MovieExec WHERE producerC#=cert# AND movieTitle = title AND movieYear = year AND name George Lucas ;The number of movies produced by each producer SELECT name, count(*) FROM Movies, MovieExec WHERE producerC#=cert# GROUP BY name 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 7 页，共 7 页 - - - - - - - - -