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

数据库系统基础教程第二章——十一章答案

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 http://www.77cn.com.cn, MovieExec.address, MovieExec.cert# FROM MovieExec, Studio WHERE MovieExec.cert# = Studio.presC#;

c)

CREATE VIEW ExecutiveStar (name, address, gender, birthdate, cert#, netWorth) AS SELECT http://www.77cn.com.cn, star.address, star.gender, star.birthdate, exec.cert#, http://www.77cn.com.cnWorth

FROM MovieStar star, MovieExec exec WHERE http://www.77cn.com.cn = http://www.77cn.com.cn AND

star.address = exec.address;

Exercise 8.1.2

a)

SELECT name from ExecutiveStar WHERE gender = ‘f’;

b)

SELECT http://www.77cn.com.cn from RichExec, StudioPres where http://www.77cn.com.cn = http://www.77cn.com.cn;

c)

SELECT http://www.77cn.com.cn from ExecutiveStar, StudioPres

WHERE http://www.77cn.com.cnWorth >= 50000000 AND

StudioPres.cert# = RichExec.cert#;

Section 2

Exercise 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 http://www.77cn.com.cn, MovieExec.address, MovieExec.cert# FROM MovieExec

WHERE MovieExec.cert# IN (SELECT presCt# from Studio);

Exercise 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’;

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, NewRow.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

FOR EACH ROW

(DELETE FROM Product WHERE model = OldRow.model)

(DELETE FROM PC where model = OldRow.model);

Section 3

数据库系统基础教程第二章——十一章答案

Exercise 8.3.1

a)

CREATE INDEX NameIndex on Studio(name);

b)

CREATE INDEX AddressIndex on MovieExec(address);

c)

CREATE INDEX GenreIndex on Movies(genre, length);

Section 4

Exercise 8.4.1

Exercise 8.4.2

Q1 = SELECT * FROM Ships WHERE name = n;

Q2 = SELECT * FROM Ships WHERE class = c;

Q3 = SELECT * FROM Ships WHERE launched = y;

I = Inserts

Indexes

None Name Class Launched Name & Name & Class & Three

数据库系统基础教程第二章——十一章答案

Section 5

Exercise 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 = 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.2

Insertions, 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.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) –

(SELECT displacement

FROM Classses

WHERE class = ‘DeletedShipClass’)

) / (count – 1),

count = count – 1

WHERE

country = (SELECT country FROM C lasses WHERE class=’DeletedShipClass’); Insertions into Shi …… 此处隐藏：5139字，全部文档内容请下载后查看。喜欢就下载吧 ……