数据库原理与应用第三版何玉洁第七章课后习题答案

1 提高数据的查询效率 2 聚集索引，非聚集索引， 聚集索引首先按聚集索引列的值对数据进行物理排序，然后在此基础上直接建立索引B树， 非聚集索引系统直接再现有数据存储顺序的基础之上直接建立索引B树，B树的索引项是有序的。 3 一个聚集索引，多个非聚集索引 4 聚集索引和非聚集索引都可以是唯一索引 5 是 6 是 7 不是 8 操作 经常返回范围值的查询 不返回大型结果集的查询 经常被用作连接的列 order by 或 group by 操作 情况 非重复值多的列 不返回大型结果集的查询 经常被用作连接的列 order by 或 group by 操作 9 1 Create nonclustered index SnameIdx on Student(Sname)

2 Create nonclustered index CNIdx on Course(Cname)

3 Create clustered index SnoCnoIdx on SC(Sno,Cno)

4 Drop index SnoIdx 10 1 视图提供了一定程度的数据逻辑独立性 2 增加了数据的安全性 3 封装了复杂的查询 4 简化了客户端访问数据库数据的变成 11 不对 多了一个外模式到模式的转换 12 1 create view v1(Sno,Sname,Sdept,Cno,Cname,Credit) AS select Student.Sno,Sname,Sdept,SC.Cno,Cname,Credit from Student join SC on Student.Sno = SC.Sno join Course on SC.Cno = Course.Cno 2 create view v2(Sno,Sname,Cname,Cno,Grade) AS select Student.Sno,Sname,Cname,Grade from Student join SC on Student.Sno = SC.Sno join Course on SC.Cno = Course.Cno 3 create view v3(Sno,Cnum) AS select Student.Sno,count(*) from Student join SC on Student.Sno = SC.Sno group by Student.Sno 4 create view v4(Sno,SCredit) AS select Sno,Sum(Credit) from SC join Course on SC.Cno = Course.Cno where SC.Cno in ( select Cno from SC where Grade >= 60 ) group by Sno

drop view v3 select * from v4 13 1 select Sname,Cname,Grade from v2 2 select Sno,Cnum from v3 where Cnum >= 3 3 select distinct Sname,Cnum from v3 join v1 on v3.Sno = v1.Sno where Sname in ( select Sname from v1 where Sdept = ‘计算机系’ ) and Cnum >= 3 4 select v4.Sno,Sname,Sdept,SCredit from v4 join v1 on v4.Sno = v1.Sno where SCredit >= 10 5 select Sname,Sage,Sdept,SCredit from Student join v4 on Student.Sno = v4.Sno where sage >= 20 14 alter view v4(Sno,SCredit,SUMC) AS select Sno,SUM(Credit),count(*) from SC join Course on SC.Cno = Course.Cno group by Sno