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

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