数据库系统基础教程第八章答案.doc

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 (name, 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 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 2Exercise 8.2.1The views RichExec and StudioPres are updatable; however, the StudioPres 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 INSTEAD 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 NewRowFOR 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 NewPCREFERENCING 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 = 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 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 = SELECT * 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 + 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 SET 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# = producerC#);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 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=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 delete or an insert, or both.Exercise 8.5.3Modifications 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 SETdisplacement=(displacement * count) (SELECT displacement FROM Classses WHERE class = DeletedShipClass) / (count 1),count = count 1WHEREcountry = (SELECT country FROM Classes WHERE class=DeletedShipClass);Insertions into Ship:Update ShipStat SETdisplacement=(displacement*count) + (SELECT displacement FROM Classes WHERE class=InsertedShipClass) / (count + 1),count = count + 1WHEREcountry = (SELECT country FROM Classes WHERE classes=InsertedShipClass);Deletes from Classes:NumRowsDeleted = SELECT count(*) FROM ships WHERE class = DeletedClass;UPDATE ShipStats SETdisplacement = (displacement * count) - (DeletedClassDisplacement * NumRowsDeleted) / (count NumRowsDeleted),count = count NumRowsDeletedWHERE country = DeletedClassCountry;Update to a Class displacement:N = SELECT count(*) FROM Ships where class = UpdatedClass;UPDATE ShipsStat SETdisplacement = (displacement * count) + (oldDisplacement newDisplacement) * N)/countWHEREcountry = UpdatedClassCountry;Exercise 8.5.4Queries that can be rewritten with the materialized view:Names of stars of movies produced by a certain producer SELECT starNameFROM StarsIn, Movies, MovieExecWHERE movieTitle = title AND movieYear = year AND producerC# = cert# AND name = Max Bialystock;Movies produced by a certain producerSELECT title, year FROM Movies, MovieExecWhere producerC# = cert# AND name = George Lucas;Names of producers that a certain star has worked withSELECT nameFROM Movies, MovieExec, StarsInWhere producerC#=cert# AND title=movieTitle AND year=movieYear AND starName=Carrie Fisher;The number of movies produced by given producerSELECT count(*) FROM Movies, MovieExecWHERE producerC#=cert# AND name = George Lucas;Names of producers who also starred in their own moviesSELECT name FROM Movies, StarsIn, MovieExecWHERE producerC#=cert# AND movieTitle = title AND movieYear = year AND MovieExec.name = starName;The number of stars that have starred in movies produced by a certain producerSELECT count(DISTINCT starName)FROM Movies, StarsIn, MovieExecWHERE producerC#=cert# AND movieTitle = title AND movieYear = year AND name George Lucas;The number of movies produced by each producerSELECT name, count(*) FROM Movies, MovieExecWHERE producerC#=cert# GROUP BY name