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, # FROM MovieExec, Studio # = Studio.presC#;
c)
CREATE VIEW ExecutiveStar (name, address, gender, birthdate, cert#, netWorth) AS SELECT star.name, star.address, der, star.birthdate, #, execWorth
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 ExecutiveStarWorth  >= 50000000 AND
<# = #;
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 MovieExec.name, MovieExec.address, # FROM MovieExec # 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, ar, 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 = ar 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, del, ‘pc’))
(INSERT INTO PC del, 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 = del;
d)
CREATE TRIGGER NewPCDelete
INSTEAD OF DELETE ON NeePC
REFERENCING OLD ROW AS OldRow
FOR EACH ROW
(DELETE FROM Product WHERE model = del)
(DELETE FROM PC where model = del);
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
Action No Index Star Index Movie Index Both Indexes Q110041004
Q210010044
I2446 Average  2 + 98p1 + 98p2  4 + 96 p2  4 + 96 p1  6 – 2 p1 – 2 p2 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 Actions None Name Class Launched Name &
Class
Name &
Launched
Class &
Launched
Three
Indexes
Q1502505022502 Q21121212  2 Q35050502650262626 I24446668
Average  2 +
48p1 -
p2 +
48p3
4 +
46 p3
- 2 p1
-
3 p2
4 +
46p1 -
2p2 +
46p3
4 + 46p1
- 3p2 +
22p3
6 - 4p1
- 4p2 +
44p3
6 - 4p1 -
5p2 + 20p3
6 - 44p1 -
4p2 + 20p3
8 - 6p1 -
6p2 + 18p3
The best choice of indexes (name and launched) has an average cost of 6 - 4p1 - 5p2 + 20p3 per operation.
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
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 del;
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 Classes WHERE class=’DeletedShipClass’); Insertions into Ship:
Update ShipStat SET
delete indisplacement=((displacement*count) +
(SELECT displacement FROM Classes
WHERE class=’InsertedShipClass’)
) / (count + 1),
count = count + 1
WHERE
country = (SELECT country FROM Classes WHERE classes=’InsertedShipClass); Deletes from Classes:
NumRowsDeleted  = SELECT count(*) FROM ships WHERE class = ‘DeletedClass’; UPDATE ShipStats SET

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。