索引与视图
- 格式：doc
- 大小：29.00 KB
- 文档页数：2
机械三P209
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);
P213 8.4.1和8.4.2的习题解答见PPT
P204 8.1.1/P217 5.8.1
a)
CREATE VIEW RichExec AS
SELECT * FROM MovieExec WHERE netWorth >= 10000000;
b)
CREATE VIEW StudioPres (name, address, cert#) AS
SELECT , MovieExec.address, MovieExec.cert# FROM MovieExec, Studio WHERE MovieExec.cert# = Studio.presC#;
8.1.2 /5.8.3
b)
SELECT from RichExec, StudioPres where = ;
P207
习题8.2.2 如果创建视图
CREATE VIEW DisneyComedies AS
SELECT title, year, length
FROM Movies
WHERE studioName=‘Disney’ AND genre=‘comedy’;
a)该视图是可更新的吗？
是可更新视图。
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)
V ALUES(NewRow.title, NewRow.year, NewYear.length, ‘Disney’, ‘comedy’);
c)写一个替换触发器用于处理在视图中修改一部电影（给出了属性title和year）的长
度。
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’;。
