Section 1
Exercise 8.1.1
a)
CREATE VIEW RichExec AS
SELECT * FROM MovieExec WHERE netWorth >= 10000000;
Exercise 8.1.2
a)
SELECT name from ExecutiveStar WHERE gender = ‘f’;
Section 2
Exercise 8.2.1
The views RichExec and StudioPres are updatable; however, the StudioPres view needs to be
created with a subquery.
Exercise 8.2.2
b)
CREATE TRIGGER DisneyComedyInsert
c)
CREATE TRIGGER DisneyComedyUpdate
INSTEAD OF UPDATE ON DisneyComedies
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
c)
CREATE TRIGGER NewPCUpdate
INSTEAD OF UPDATE ON NewPC
d)
CREATE TRIGGER NewPCDelete
INSTEAD OF DELETE ON NeePC
Section 3
Exercise 8.3.1
a)
CREATE INDEX NameIndex on Studio(name);
Section 4
Exercise 8.4.1
Action
No Index
Star Index
Movie Index
Both Indexes
Q1
100
4
100
4
Q2
100
4
4
6
Average
4 + 96 p2
Exercise 8.4.2
Q1 = SELECT * FROM Ships WHERE name = n;
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
2
1
Q3
50
50
26
50
26
2
4
4
6
6
8
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 to MovieExec involving cert#
DELETE FROM MovieProd
WHERE (title, year) IN (
SELECT title, year
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
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
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) –
Insertions into Ship:
Update ShipStat SET
displacement=((displacement*count) +
(SELECT displacement FROM Classes
WHERE class=’InsertedShipClass’)
displacement = (displacement * count) – (DeletedClassDisplacement *
NumRowsDeleted)) / (count – NumRowsDeleted),
Exercise 8.5.4
Queries that can be rewritten with the materialized view:
Names of stars of movies produced by a certain producer
Movies produced by a certain producer
SELECT title, year
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 stars that have starred in movies produced by a certain producer