mysql数据库原理及应用 数据库原理与应用(第3版)答案

mysql数据库原理及应用 数据库原理与应用（第3版）答案

《数据库原理与应用》（第三版）习题参考答案 第 1 章 数据库概述 1． 试说明数据、数据库、数据库管理系统和数据库系统的概念。

答：数据是描述事物的符号记录。

数据库是长期存储在计算机中的有组织的、可共享的大量数据的集合。

数据库管理系统是一个专门用于实现对数据进行管理和维护的系统软件。

数据库系统是指在计算机中引入数据库后的系统，一般由数据库、数据库管理系统（及相关的实用工具）、应用程序、数据库管理员组成。

2． 数据管理技术的发展主要经历了哪几个阶段？ 答：文件管理和数据库管理。

3． 与文件管理相比，数据库管理有哪些优点？ 答：与文件系统管理数据相比，数据库系统管理数据带来了如下好处：将相互关联的数据集成在一起，较少的数据冗余，程序与数据相互独立，保证数据的安全可靠，最大限度地保证数据的正确性，数据可以共享并能保证数据的一致性。

第 1 页 共 51 页

4． 在数据库管理方式中，应用程序是否需要关心数据的存储位置和存储结构？为什么？ 答：不需要。因为数据库管理系统提供了逻辑独立性和物理独立性。

5． 在数据库系统中，数据库的作用是什么？ 答：数据库是数据的汇集，它以一定的组织形式保存在存储介质上。

6． 在数据库系统中，应用程序可以不通过数据库管理系统而直接访问数据文件吗？ 答：不能 7． 数据独立性指的是什么？它能带来哪些好处？ 答：数据独立性是指应用程序不会因数据的物理表示方式和访问技术的改变而改变，即应用程序不依赖于任何特定的物理表示方式和访问技术，它包含两个方面：逻辑独立性和物理独立性。

物理独立性是指当数据的存储位置或存储结构发生变化时，不影响应用程序的特性；

逻辑独立性是指当表达现实世界的信息内容发生变化时，不影响应用程序的特性。

8． 数据库系统由哪几部分组成，每一部分在数据库系统中的作用大致是什么？ 答：数据库系统一般包括数据库、数据库管理系统（及相应的实用工具）、应用程序和数据库管理员四个部分。数据库是数据的汇集，它以一定的组织形式保存在存储介质上；数据库管理系统是管理数据库的系统软件，它可以实现数据库系统的各种功能；应用程序专指以数据库数据为基础的程序，数据库管理员负责整个数据库系统的正常运行。

第 2 页 共 51 页

第2章 数据模型与数据库结构 1． 解释数据模型的概念，为什么要将数据模型分成两个层次？ 答：答：数据模型是对现实世界数据特征的抽象。数据模型一般要满足三个条件：第一是数据模型要能够比较真实地模拟现实世界；第二是数据模型要容易被人们理解；第三是数据模型要能够很方便地在计算机上实现。由于用一种模型来同时很好地满足这三方面的要求在目前是比较困难的，因此在数据库系统中就可以针对不同的使用对象和应用目的，采用不同的数据模型。根据模型应用的不同目的，将这些模型分为两大类：概念层数据模型和组织层数据模型，以方便对信息的描述。

2． 概念层数据模型和组织层数据模型分别是针对什么进行的抽象？ 答：概念层数据模型是对现实世界的抽象，形成信息世界模型，组织层数据模型是对信息世界进行抽象和转换，形成具体的DBMS支持的数据组织模型。

3． 实体之间的联系有哪几种？请为每一种联系举出一个例子。

答：实体之间的联系有一对一、一对多和多对多三种。例如：系和正系主任是一对一联系（假设一个系只有一个正系主任），系和教师是一对多联系（假设一个教师只在一个系工作），教师和课程是多对多联系（假设一个教师可以讲授多门课程，一门课程可由多个教师讲授）。

4． 说明实体-联系模型中的实体、属性和联系的概念。

第 3 页 共 51 页

5．指明下列实体间联系的种类：

（1）教研室和教师（设一个教师只属于一个教研室，一个教研室可有多名教师）。

（2）商品和顾客。

（3）国家和首都（假设一个国家的首都可以变化）。

（4）飞机和乘客。

（5）银行和账户。

（6）图书和借阅者。

（设一个借阅者可同时借阅多本书，可在不同时间对同一本书借阅多次） 6．数据库系统包含哪三级模式？试分别说明每一级模式的作用？ 答：数据库系统包含的三级模式为：内模式、模式和外模式。外模式是对现实系统中用户感兴趣的整体数据结构的局部描述，用于满足不同数据库用户需求的数据视图，是数据库用户能够看见和使用的局部数据的逻辑结构和特征的描述，是对数据库整体数据结构的子集或局部重构。模式是数据库中全体数据的逻辑结构和特征的描述，是所有用户的公共数据视图。内模式是对整个数据库的底层表示，它描述了数据的存储结构。

7． 数据库管理系统提供的两级映像的作用是什么？它带来了哪些功能？ 答：数据库系统的两级映象是模式与内描述间的映象和外模式与模式间的映象。模式／内模式的映象带来了物理独立性，即如果数据库的存储结构改变了，可通过调整模式／内模式的映象，使模式能够保持不变。外模式／概念模式间的映象带第 4 页 共 51 页

来了逻辑独立性，当概念模式的结构可发生改变时，也可通过调整外模式/模式间的映象关系，使外模式可以保持不变。

8．数据库三级模式划分的优点是什么？它能带来哪些数据独立性？ 答：数据库的三级模式的划分实际上将用户、逻辑数据库与物理数据库进行了划分，使彼此之间的相互干扰减到最少。这三个模式的划分实际上带来了两个数据独立性：物理独立性和逻辑独立性。这使得底层的修改和变化尽量不影响到上层。

第3章 关系数据库 1． 试述关系模型的三个组成部分。

答：关系数据结构、关系操作集合和关系完整性约束。

2． 解释下列术语的含义：

（1） 笛卡尔积：设D1，D2，…，Dn为任意集合，定义笛卡尔积D1，D2，…，Dn为：

D1×D2× …×Dn ＝{(d1，d2，…，dn) | di ∈Di，i＝1，2，…，n } （2） 主键：也称主码为或主关键字，是表中的属性或属性组，用于惟一地确定一个元组。

（3） 候选键：如果一个属性或属性集的值能够惟一标识一个关系的元组而又不包含多余的属性，则称该属性或属性集为候选键。

（4） 外键：设F是关系R的一个或一组属性，如果F与关系S的主键相对应，则称F是关系R的外键。

（5） 关系：关系就是简单二维表。

（6） 关系模式：二维表的结构称为关系模式。

第 5 页 共 51 页

（7） 关系数据库：对应于一个关系模型的所有关系的集合称为关系数据库。

3． 关系数据库的三个完整性约束是什么？各是什么含义？

答：实体完整性、参照完整性和用户定义的完整性。

实体完整性是保证关系中的每个元组都是可识别的和惟一的。

参照完整性也称为引用完整性，用于表达现实世界中的实体之间的关联关系。

用户定义的完整性也称为域完整性或语义完整性，用于保证数据库中存储的值与现实世界相符。

4． 连接运算有哪些？等值连接和自然连接的区别是什么？

答：连接运算中最重要也是最常用的连接有两个，一个是等值连接，一个是自然连接。

自然连接与等值连接的差别为：

l 自然连接要求相等的分量必须有共同的属性名，等值连接则不要求；

l 自然连接要求把重复的属性名去掉，等值连接却不这样做。

5． 对参与并、交、差运算的两个关系R、S有什么要求？

答：必须结构相同，且相应的属性值取自同一个值域。

第 6 页 共 51 页

6． 对参与除运算的两个关系（R÷S）有什么要求？除运算的结果关系中包含哪些属性？ 答：R中必须包含S的全部或部分属性，除运算的结果包含的属性是只属于R不属于S的属性。

7． 对参与自然连接和等值连接操作的两个关系R、S有什么要求？ 答：有语义相同的属性。

8． 投影操作的结果关系中是否有可能存在重复的记录？为什么？ 答：不可能，因为投影运算会自动去掉投影后重复的记录。

9．利用表3-10至3-12所示的三个关系，写出实现如下查询要求的关系代数表达式。

（1）查询“信息系”学生的选课情况，列出学号、姓名、课程号和成绩。

∏Sno, Sname, Cno, Grade（σSdept=‘信息系’（SC

Student）） 或：∏Sno, Sname, Cno, Grade（SC σSdept=‘信息系’（Student）） （2） 查询“VB”课程的考试情况，列出学生姓名、所在系和考试成绩。

∏Sname, Sdept, Grade（σCname=‘VB’（Coure SC

Student）） 或：∏Sname, Sdept, Grade（σCname=‘VB’（Coure） SC Student） （3） 查询考试成绩高于90分的学生的姓名、课程名和成绩。

∏Sname, Cname, Grade（σGrade>90（Coure SC

Student）） 或：∏Sname, Cname, Grade（Coure σGrade>90第 7 页 共 51 页

（SC） Student） （4） 查询至少选修了0512号学生所选的全部课程的学生的姓名和所在系。

∏Sname, Sdept（Student （SC ÷ ∏Cno（σsno=’0512’（SC））)) （5） 查询至少选了“C01”和“C02”两门课程的学生的姓名、所在系和所选的课程号。

∏Sname, Sdept, Cno（Student （SC ÷ ∏Sno（σcno=’C01’ V Cno=’C02’（SC））)) （6） 查询没有选修第1学期开设的全部课程的学生的学号、姓名和所选的课程号。

∏Sno, Sname, Cno（Student SC (∏sno（SC) - ∏sno（σsemester=1（Course)SC))) （7） 查询计算机系和信息系选了VB课程的学生姓名。

∏Sname（σsdept=’计算机系’V sdept=’信息系’）∧

cname=’vb’（StudentSC Course)) 或：∏Sname（σsdept=’计算机系’V sdept=’信息系’（Student）SC σcname=’vb’（Course)) 第4章 SQL Server 20__基础 1． 安装SQL Server

20__对硬盘及内存的要求分别是什么？ 答：SQL Server 20__实际硬盘空间需求取决于系统配置和您决定安装的功能，一般应确保系统驱动器中是否有至少 6.0 GB 的可用磁盘空间。

内存：E_press 版本：最少512 MB；其他版本：最少1 GB。

2． SQL Server实例的含义是什么？实例名的作用是什么？

答：一个实例代表一个独立的数据库管理系统。实例名是实例的第 8 页 共 51 页

标识，用户通过“计算机名/实例名”的方式可访问指定的命令实例。

3． SQL Server 20__的核心引擎是什么？ 答：SQL Server（MSSQLSERVER） 4． SQL Server 20__提供的设置服务启动方式的工具是哪个？ 答：配置管理器 5． 在SQL Server 20__中，每个数据库至少包含几个文件？ 答：2个文件，一个数据文件，一个日志文件 6． SQL Server 20__数据库文件分为几类？每个文件有哪些属性？ 答：主要数据文件和次要数据文件，属性有：物理文件名及其位置、逻辑文件名、初始大小、增长方式、最大大小。

第 5 章 数据类型及关系表创建 1． Tinyint数据类型定义的数据的取值范围是多少？ 答：0-255 2． SmallDatatime类型精确到哪个时间单位？ 答：分钟 3． 定点小数类型numeric中的p和q的含义分别是什么？ 答：p代表整数位数+小数位数的和值，q代表小数位数。

4． Char(n)、nchar(n)的区别是什么？它们各能存放多少个字符？ 答：Char(n)中的n代表能存放n个字节的字符，如果是字母可以存放n个，如果是汉字可存放n/2个。Nchar(n)中的n代表能存放的字符个数。

5． Char(n)和varchar(n)的区别是什么？ 答：Char(n)是定长存储，一定占n个字节的空间。Varchar(n)代表最多占n个字节的空间。

第 9 页 共 51 页

6． 数据完整性约束的作用对象有哪些？ 答：表和列

7． CHECK约束的作用是什么？ 答：限制列的取值范围。

8． UNIQUE约束的作用是什么？ 答：限制列取值不重。

9． DEFAULT约束的作用是什么？ 答：提供列的默认值。

上机练习 1． 在第4章创建的Students数据库中，写出创建如下三张表的SQL语句，要求在定义表的同时定义数据的完整性约束：

（1）“图书”表结构如下：

书号：统一字符编码定长类型，长度为6，主键；

书名：统一字符编码可变长类型，长度为30，非空；

第一作者：普通编码定长字符类型，长度为10，非空；

出版日期：小日期时间型；

价格：定点小数，小数部分1位，整数部分3位。

create table 图书( 书号 nchar(6) primary key, 书名 nvarchar(30) not null, 第一作者 char(10) not null,

出版日期 smalldate, 价格 numeric(4,1) ) （2）“书店”表结构如下：

书店编号：统一字符编码定长类型，长度为6，主键；

店名：统一字符编码可变长类型，长度为30，非空；

电话：普通编码定长字符类型，8位长，每一位的取值均是0～9的数字；

地址：普通编码可变长字符类型，40位长。

第 10 页 共 51 页

邮政编码：普通编码定长字符类型，6位长。

create table 书店( 书店编号 nchar(6) primary key,

店名 nvarchar(30) not null 电话 char(8) check(电话

like ＇[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]＇), 地址 varchar(40), 邮政编码 char(6) ) （3）“图书销售”表结构如下：

书号：统一字符编码定长类型，长度为6，非空；

书店编号：统一字符编码定长类型，长度为6，非空；

销售日期：小日期时间型，非空；

销售数量：小整型，大于等于1。

主键为（书号，书店编号，销售日期）；

其中“书号”为引用“图书表”的“书号”的外键；

“书店编号”为引用“书店表”的“书店编号”的外键。

create table 图书销售( 书号 nchar(6) not null, 书店编号 nchar(6) not null, 销售日期 samlldate, 销售数量 smallint check(销售数量 >= 1), primary key(书号, 书店编号, 销售日期), foreign key(书号) references 图书(书号), foreign key(书店编号) references 书店(书店编号) )

2． 为图书表添加“印刷数量”列，类型为整数，同时添加取值大于等于1000的约束。

Alter table 图书 add 印刷数量 int check(印刷数量 >=

1000) 3． 删除书店表中的“邮政编码”列。

第 11 页 共 51 页

Alter table 书店 drop column 邮政编码 4． 将图书销售表中的“销售数量”列的数据类型改为整型。

Alter table 图书 alter column 销售数量 int 第 6 章 数据操作语句 1.

简单说明SELECT语句中，FROM、WHERE、GROUP BY、HAVING子句的作用。

答：FROM子句指定数据来自的表，WHERE子句指定行数据的筛选条件，GROUP BY子句指定用于分组的列，HAVING子句用于对分组后的统计值进行筛选。

2.

简单说明COUNT(_)与COUNT(列名)的区别。

答：COUNT(_)用于统计行的个数，并且不忽略NULL；COUNT(列名)用于统计该列中非空值个数，这个函数会去掉NULL。

3.

在聚合函数中，不忽略空值的函数是哪个？ 答：COUNT(_)

4.

哪些数据类型的列可以使用SUM(列名)和AVG(列名)函数？

答：数值类型的列 5.

外连接和内连接的区别是什么？ 答：外连接结果会包含连接中一个表的全部数据，内连接结果是只包含两个表中满足连接条件的数据。

6.

第 12 页 共 51 页

TOP子句的作用是什么？ 答：限定取查询结果中的前若干行数据。

7.

DISTINCT子句的作用是去掉表中的重复行数据，这个说法对吗？ 答：应该是去掉查询结果中的重复行数据。

8.

简单说明嵌套子查询的执行顺序。

答：嵌套子查询是先执行子查询，然后在根据子查询结果执行外层查询。

上机练习 1． 查询学生选课表中的全部数据。

答：select _

from SC 2． 查询计算机系的学生姓名、年龄。

答：select sname, sage from student where sdept = ＇计算机系＇ 3． 查询成绩在70～80分之间的学生学号、课程号和成绩。

答：select sno,cno,grade from sc where grade between

70 and 80 4． 查询计算机系年龄在18～20且性别为“男”的学生姓名、年龄。

答：select sname,sage from student where sdept

= ＇计算机系＇ and sage between 18 and 20 and sse_ = ＇男＇ 5． 查询“c01”课程最高分。

第 13 页 共 51 页

答：select ma_(grade) from sc where cno = ＇C01＇

6． 查询计算机系学生的最大年龄和最小年龄。

答：select ma_(sage) as ma__age, min(sage) as min_age

from student where sdept = ＇计算机系＇ 7． 统计每个系的学生人数。

答：select sdept, count(_) 学生人数 from student

group by sdept 8． 统计每个学生的选课门数和考试总成绩，并按选课门数升序显示结果（不包括没选课的学生）。

答：select sno,count(_) 选课门数, sum(grade) 考试总成绩 from sc group by sno order by count(_) asc 9． 查询总成绩超过20__分的学生，列出学号、总成绩。

答：select sno,sum(grade) 总成绩 from sc group by

sno having sum(grade) > 20__

10． 查询选了“c02”课程的学生姓名和所在系。

答：select sname,sdept from student s join sc on

= where cno = ＇C02＇ 11． 查询成绩80分以上的学生姓名、课程号和成绩，并按成绩降序排列结果。

答：select sname,cno,grade from student s join sc on

= where grade > 80 order by grade desc

12． 查询哪些学生没有选课，要求列出学号、姓名和所在系。

第 14 页 共 51 页

答：select , sname, sdept from student s left

join sc on = Where is null 13． 统计每门课程的选课人数，列出课程号和选课人数。

（ 包括没人选的课程） 答：select co,count(sco) 选课人数 from course c left join sc on co = sco Group by co

14． 查询与VB在同一学期开设的课程的课程名和开课学期。

答：select c2ame,er from course c1 join

course c2 on er = er Where c1ame = ＇VB＇ and c1ame != ＇VB＇ 15． 查询与李勇年龄相同的学生的姓名、所在系和年龄。

答：select ,, from student s1

join student s2 on = Where = ＇李勇＇ and != ＇李勇＇ 16． 查询计算机系年龄最小的2名学生的姓名和年龄。

答：select top 2 with ties sname, sage from student

Where sdept = ＇计算机系＇ Order by sage asc 17． 查询VB成绩最高的前2名学生的姓名、所在系和VB成绩，包括并列的情况。

答：select top 2 with ties sname, sdept, grade from

student s join sc on = Join course c on co =

sco Where cname = ＇VB＇ Order by grade desc 18． 查询选课门数最多的前2名学生的学号和选课门数，包括并列的情况。

第 15 页 共 51 页

答：select top 2 with ties sno, count(_) 选课门数

from sc Group by sno Order by count(_) desc 19． 查询学生人数最多的系，列出系名和人数。

答：select top 1 with ties sdept, count(_) 人数 from

student Group by sdept Order by count(_) desc 20． 用子查询实现如下查询: （1） 查询选修了“c01”号课程的学生姓名和所在系。

答：select sname, sdept from student where sno

in( select sno from sc where cno = ＇ C01＇) （2）

查询数学系成绩80分以上的学生学号、姓名、课程号和成绩。

答：select sno,sname,cno,grade from student s join sc

on = where in( select sno from sc

where grade > 80) and sdept = ＇数学系＇ （3） 查询计算机系考试成绩最高的学生姓名。

答：select sname from student where sno

in( select top 1 sno from sc join student s on

= where sdept = ＇计算机系＇ Order by grade desc )

（4） 查询数据结构考试成绩最高的学生姓名、所在系、性别和成绩。

答：select sname,sdept,sse_,grade from student s

join sc on = where in( Select top 1

第 16 页 共 51 页

sno from sc join course c on co = sco Where cname

= ＇数据结构＇ Order by grade desc ) 21.

查询没选VB课程的学生姓名和所在系。

答：select sname,sdept from student Where sno

not in ( Select sno from sc join course c on co =

sco Where cname = ＇VB＇ ) 22.

查询计算机系没选课的学生的姓名和性别。

答：select sname,sse_ from student Where sdept

= ＇计算机系＇ And sno not in ( Select sno from sc )

23.

查询计算机系考试平均成绩最低的学生的姓名及所选的课程名。

答：select sname,cname from student s join sc on

= Join course c on co = sco where

in ( Select top 1 sno from sc Where

sdept = ＇计算机系＇ Group by sno Order by

avg(grade) asc ) 24.

查询1～5学期中，选课人数最少的课程的课程名、开课学期和学分。

答：select cname,semester,credit from course

Where semester between 1 and 5 And cno in ( Select top 1

cno from sc Group by cno Order by count(_) desc ) 25.

第 17 页 共 51 页

查询计算机系每个学生的考试情况，列出姓名、课程名和考试成绩，并将查询结果保存到一个新表中。新表名为：puter_Dept 答：select sname,cname,grade into puter_Dept

from student s join sc on = Join course c on

co = sco Where sdept = ＇计算机系＇ 26.

创建一个新表，表名为test_t，其结构为：（COL1, COL 2,

COL 3），其中：

COL1：整型，允许空值。

COL2：普通编码字符型，长度为10 ，不允许空值。

COL3：普通编码字符型，长度为10 ，允许空值。

试写出按行插入如下数据的语句（空白处表示空值）。

COL1 COL2 COL3 B1 1 B2 C2 2 B3 答：create table

test_t( Col1 int, Col2 char(10) not null,

Col3 char(10) ) Insert into test_t values(null,＇B1＇,null) Insert into test_t values(1,＇B2＇,＇C2＇)

Insert into test_t values(2,＇B3＇’,null) 27.

删除考试成绩低于50分的学生的选课记录。

答：delete from sc where grade < 50 28.

删除没有人选的课程。

答：delete from course where cno not in( Select

cno from sc ) 29.

删除计算机系VB成绩不及格学生的VB选课记录。

第 18 页 共 51 页

答：delete from sc From student s join sc on =

sco Join course c on co = sco where sdept = ＇计算机系＇

and cname = ＇VB＇ and grade < 60 30.

删除VB考试成绩最低的学生的VB选课记录。

答：delete from sc From course c join sc on co = sco

Where cname = ＇VB＇ And grade = ( Select

min(grade) from sc Join course c on co = sco

Where cname = ＇VB＇) 31.

将第2学期开设的所有课程的学分增加2分。

答：update course set credit = credit + 2 Where

semester = 2 32.

将VB课程的学分改为3分。

答：update course set credit = 3 Where cname

= ＇VB＇ 33.

将计算机系学生的年龄增加1岁。

答：update student set sage = sage + 1 where sdept

= ＇计算机系＇ 34.

将信息系学生的“计算机文化学”课程的考试成绩加5分。

答：update sc set grade = grade + 5 From

student s join sc on = Join course c on

co = sco Where sdept = ＇信息系＇ And cname

= ＇计算机文化学＇ 35.

第 19 页 共 51 页

将选课人数最少的课程的学分降低1分。

答：update course set credit = credit - 1 Where

cno in ( Select top 1 cno from sc Group by cno

Order by count(_) asc ) 第7章 索引和视图 1． 索引的作用是什么？ 答：索引可以加快数据的查询效率。

2． 索引分为哪几种类型？分别是什么？它们的主要区别是什么？ 答：分为聚集索引和非聚集索引两种。聚集索引会对数据进行物理排序，非聚集索引不对数据进行物理排序。

3． 在一个表上可以创建几个聚集索引？可以创建多个非聚集索引吗？ 答：1个聚集索引。可以。

4． 聚集索引一定是唯一性索引，对吗？反之呢？ 答：不对。反之也不对。

5． 在建立聚集索引时，数据库管理系统是真正将数据按聚集索引列进行物理排序。对吗？ 答：对。

6． 在建立非聚集索引时，数据库管理系统并不对数据进行物理排序。对吗？ 答：对。

7． 不管对表进行什么类型的操作，在表上建立的索引越多越能提高操作效率。对吗？ 答：不对。

8． 经常对表进行哪类操作适合建立索引？适合在哪些列上建立索引？ 答：

l 返回范围值的查询：BETWEEN AND、>、>=、< 和 <= ；

l 不返回大型结果集的查询。

第 20 页 共 51 页

l 经常被用作连接的列，一般来说，这些列是外键列。

l ORDER BY或GROUP BY操作。

适合建立索引的情况：

l 包含大量非重复值的列。

l 在WHERE子句中经常用于进行BETWEEN AND、>、>=、< 和

<=等操作的列。

l 经常被用作连接操作的列。

l ORDER BY或GROUP BY子句中涉及的列。

9．使用第5章建立的Student、Course和SC表，写出实现下列操作的SQL语句。

（1）在Student表上为Sname列建立一个非聚集索引，索引名为：SnameId_。

答：Create inde_ SnameId_ on student(Sname) （2） 在Course表上为Cname列建立一个唯一的非聚集索引，索引名为：CNId_ 答：Create unique inde_ CNId_ on Course(Cname)

（3） 在SC表上为Sno和Cno建立一个组合的聚集索引，索引名为：SnoCnoId_。

答：Create clustered inde_ SnoCnoId_ on SC(Sno,Cno)

（4）删除Sname列上建立的SnoId_索引。

答：drop inde_ SnoId_ on Student 10．试说明使用视图的好处。

第 21 页 共 51 页

答：利用视图可以简化客户端的数据查询语句，使用户能从多角度看待同一数据，可以提高数据的安全性，视图对应数据库三级模式中的外模式，因此提供了一定程度的逻辑独立性。

11.

使用视图可以加快数据的查询速度，这句话对吗？为什么？

答：不对，因为通过视图查询数据时，比直接针对基本表查询数据多了一个转换过程，即从外模式到模式的转换。

12．使用第5章建立的Student、Course和SC表，写出创建满足下述要求的视图的SQL语句。

（1）查询学生的学号、姓名、所在系、课程号、课程名、课程学分。

答：Create view v1 As Select

,sname,sdept,co,cname,credit From student s join

sc on = Join course c on co = sco （2）查询学生的学号、姓名、选修的课程名和考试成绩。

答：Create view v2 As Select

,sname,cname,grade From student s join sc on

= Join course c on co = sco （3）统计每个学生的选课门数，要求列出学生学号和选课门数。

答：Create view v3 As Select sno,count(_) as total

From sc group by sno （4） 统计每个学生的修课总学分，要第 22 页 共 51 页

求列出学生学号和总学分（说明：考试成绩大于等于60才可获得此门课程的学分）。

答：Create view v4 As Select sno,sum(credit) as

total_credit From sno join course c on co = sco

Where grade >= 60 Group by sno 13．利用第12题建立的视图，完成如下查询：

（1）查询考试成绩大于等于90分的学生的姓名、课程名和成绩。

答：Select sname,cname,grade From v2 where grade >=

90 （2）查询选课门数超过3门的学生的学号和选课门数。

答：Select _

from v3 where total >= 3 （3）查询计算机系选课门数超过3门的学生的姓名和选课门数。

答：Select sname,total from v3 join student s on

= Where sdept = ＇计算机系＇ and

total >= 3 （4）查询修课总学分超过10分的学生的学号、姓名、所在系和修课总学分。

答：Select ,sname,sdept,total_credit From

v4 join student s on = Where

total_credit >= 10 （5）查询年龄大于等于20岁的学生中，修课总学分超过10分的学生的姓名、年龄、所在系和修课总学分。

第 23 页 共 51 页

答：Select sname,sage,sdept,total_credit From

v4 join student s on = Where sage >=

20 and total_credit >= 10 14．修改12题（4）定义的视图，使其查询每个学生的学号、平均成绩以及总的选课门数。

答：

Alter view v4 As Select sno,avg(grade) as

avg_grade,count(_) as total_cno Group by sno 第8章 关系数据库理论 1.

关系规范化中的操作异常有哪些？它是由什么引起的？解决的办法是什么？ 答：增、删、改异常。数据冗余引起。解决办法：模式分解。

2.

第一范式、第二范式和第三范式的定义分别是什么？ 答：第一范式：每个列都是原子项。第二范式：第一范式且不含部分函数依赖。第三范式：第二范式且不含传递函数依赖。

3.

什么是部分函数依赖？什么是传递函数依赖？请举例说明。

答：如果_→Y，并且对于_的一个任意真子集_/ 都有_/ —/→Y，则称Y完全函数依赖于_，记作_Y；如果_/ →Y成立，则称Y部分函数依赖于_，记作_Y。

第 24 页 共 51 页

示例：学生修课（学号，姓名，性别，选的课程号，课程名，考试成绩） 主键：（学号，课程号） 因为学号姓名，因此（学号，课程号）姓名 4.

第三范式的关系模式是否一定不包含部分函数依赖？ 答：是

5.

对于主键只由一个属性组成的关系模式，如果它是第一范式的，则它是否一定也是第二范式的？ 答：是。

6． 设有关系模式：学生修课（学号，姓名，所在系，性别，课程号，课程名，学分，成绩）。设一个学生可以选多门课程，一门课程可以被多名学生选。一个学生有唯一的所在系，每门课程有唯一的课程名和学分。请指出此关系模式的候选键，判断此关系模式是第几范式的，若不是第三范式的，请将其规范化为第三范式关系模式，并指出分解后的每个关系模式的主键和外键。

答：（1）候选码：（学号，课程号） （2）学号→姓名，学号→所在系，学号→性别，课程号→课程名，课程号→学分，

（学号，课程号）→成绩 （3）属于第二范式，因为存在部分函数依赖：学号→姓名。

（4）第三范式关系模式：

学生（学号，姓名，所在系，性别） 课程（课程号，课程名，学分） 考试（学号，课程号，成绩），学号为引用学生的外码，课程号为引用课程的外码。

第 25 页 共 51 页

7． 设有关系模式：学生（学号，姓名，所在系，班号，班主任，系主任），其语义为：一个学生只在一个系的一个班学习，一个系只有一个系主任，一个班只有一名班主任，一个系可以有多个班。请指出此关系模式的候选键，判断此关系模式是第几范式的，若不是第三范式的，请将其规范化为第三范式关系模式，并指出分解后的每个关系模式的主键和外键。

答：（1）候选码：学号 （2）学号→姓名，学号→所在系，学号→班号，班号→班主任，所在系→系主任 （3）第二范式，因为有：学号→班号，班号→班主任，因此存在传递函数依赖：

学号班主任 （4）第三范式关系模式：

学生（学号，姓名，所在系，班号），班号为引用班的外码，所在系为引用系的外码。

班（班号，班主任） 系（系名，系主任） 8．设有关系模式：教师授课（课程号，课程名，学分，授课教师号，教师名，授课时数），其语义为：一门课程（由课程号决定）有确定的课程名和学分，每名教师（由教师号决定）有确定的教师名，每门课程可以由多名教师讲授，每名教师也可以讲授多门课程，每名教师对每门课程有确定的授课时数。指出此关系模式的候选键，判断此关系模式属于第几范式，若不属于第三范式，请将其规范化为第三范式关系模式，并指出分解后的每个关系模式的主键和外键。

第 26 页 共 51 页

答：（1）候选码：（课程号，授课教师号） （2）课程号→课程名，课程号→学分，授课教师号→教师名，（课程号，授课教师号）→授课时数 （3）属于第一范式。因为有：课程号→课程名，因此存在部分函数依赖关系：

（课程号，授课教师号）课程名 （4）第三范式关系模式：

课程（课程号，课程名，学分） 教师（教师号，教师名）

授课（课程号，教师号，授课时数），课程号为引用课程的外码，教师号为引用教师的外码。

第9章 事务与并发控制 1．试说明事务的概念及四个特征。

答：原子性、隔离性、一致性、持久性。

2． 事务处理模型有哪两种？ 答：T-SQL事务处理模型、T-SQL事务处理模型。

3． 在数据库中为什么要有并发控制？ 答：为避免多个事务之间的相互干扰。

4． 并发控制的措施是什么？ 答：加锁。

5．设有三个事务：T1、T2和T3，其所包含的动作为：

T1：A = A + 2；T2：A = A _

2；T3：A = A - 1 设A的初值为3，若这三个事务并行执行，则可能的调度策略有几种？A的最终结果分别是什么？ 答：

T1àT2àT3：A = 9 T1àT3àT2：A = 8 T2àT1àT3：A = 7

T2àT3àT1：A = 9 T3àT1àT2：A = 8 T3àT2àT1：A = 6

5． 当某个事务对某段数据加了S锁之后，在此事务释放锁之第 27 页 共 51 页

前，其他事务还可以对此段数据添加什么锁？ 答：S锁 6． 什么是死锁？预防死锁有哪些方法？ 答：相互等待对方释放资源。

7． 如何诊断和解除死锁？ 答：诊断死锁方法：一般使用超时法和事务等待图法。

解除死锁：通常采用的方法是选择一个处理死锁代价最小的事务，将其撤销，释放此事务所持有的全部锁，使其他事务可以继续运行下去。

8． 怎样保证多个事务的并发执行是正确的？ 答：遵守两阶段锁协议。

9． 一级封锁协议对读和写分别加什么锁？加锁范围分别是什么？能避免哪些干扰？ 答：写全程加_锁，读不加锁。能避免丢失修改。

10． 二级封锁协议对读和写分别加什么锁？加锁范围分别是什么？能避免哪些干扰？ 答：写全程加_锁，读前加S锁，读完释放S锁。能避免丢失修改和不读脏数据。

11． 三级封锁协议对读和写分别加什么锁？加锁范围分别是什么？能避免哪些干扰？ 答：写全程加_锁，读全程加S锁。能避免丢失修改、不读脏数据和不可重复读。

第10章 数据库设计 1．试说明数据库设计的特点。

答：综合性、结构设计和行为设计相分离。

2． 简述数据库的设计过程。

答：设计分为如下几个阶段。

第 28 页 共 51 页

l 需求分析。

l 结构设计，包括概念结构设计、逻辑结构设计和物理结构设计。

l 行为设计，包括功能设计、事务设计和程序设计。

l 数据库实施，包括加载数据库数据和调试运行应用程序。

l 数据库运行和维护阶段。

3． 数据库结构设计包含哪几个过程？ 答：包括概念结构设计、逻辑结构设计和物理结构设计。

4． 需求分析中发现事实的方法有哪些？ 答：检查文档、面谈、观察操作中的业务、研究和问卷调查等。

5． 概念结构应该具有哪些特点？ 答：

· 有丰富的语义表达能力。能表达用户的各种需求，包括描述现实世界中各种事物和事物与事物之间的联系，能满足用户对数据的处理需求。

· 易于交流和理解。概念结构是数据库设计人员和用户之间的主要交流工具，因此必须能通过概念模型和不熟悉计算机的用户交换意见，用户的积极参与是数据库成功的关键。

· 易于更改。当应用环境和应用要求发生变化时，能方便地对概念结构进行修改，以反映这些变化。

· 易于向各种数据模型转换，易于导出与DBMS有关的逻辑模型。

第 29 页 共 51 页

6． 概念结构设计的策略是什么？ 答：概念结构设计的策略主要有如下几种：

· 自底向上。先定义每个局部应用的概念结构，然后按一定的规则把它们集成起来，从而得到全局概念结构。

· 自顶向下。先定义全局概念结构，然后再逐步细化。

· 由里向外。先定义最重要的核心结构，然后再逐步向外扩展。

· 混合策略。将自顶向下和自底向上方法结合起来使用。先用自顶向下设计一个概念结构的框架，然后以它为框架再用自底向上策略设计局部概念结构，最后把它们集成起来。

7． 什么是数据库的逻辑结构设计？简述其设计步骤。

答：逻辑结构设计的任务是把在概念结构设计中设计的基本E-R模型转换为具体的数据库管理系统支持的组织层数据模型，也就是导出特定的DBMS可以处理的数据库逻辑结构（数据库的模式和外模式），这些模式在功能、性能、完整性和一致性约束方面满足应用要求。

逻辑结构设计一般包含两个步骤：

l 将概念结构转换为某种组织层数据模型。

l 对组织层数据模型进行优化。

8． 把E-R模型转换为关系模式的转换规则有哪些？ 答：转换的一般规则如下：

第 30 页 共 51 页

一个实体转换为一个关系模式。实体的属性就是关系的属性，实体的码就是关系的主键（主码）。

对于实体间的联系有以下不同的情况：

（1）1∶1联系可以与任意一端实体所对应的关系模式合并，合并时只需在被合并的关系模式的属性中加入另一个实体的码和联系本身的属性。

（2）1∶n联系可以与n端所对应的关系模式合并，合并时只需在n端的关系模式中加入1端实体的码以及联系本身的属性。

（3）m∶n联系应该转换为一个独立的关系模式。与该联系相连的各实体的码以及联系本身的属性均转换为联系所对应关系模式的属性，且该关系模式的主键包含各实体的码。

（4）三个或三个以上实体间的一个多元联系应该转换为一个关系模式。与该多元联系相连的各实体的码以及联系本身的属性均转换为联系所对应的关系模式的属性，而此关系模式的主键包含各实体的码。

（5）具有相同主键的关系模式可以合并。

9． 数据模型的优化包含哪些方法？ 答：（1）确定各属性间的函数依赖关系。根据需求分析阶段得出的语义，分别写出每个关系模式的各属性之间的函数依赖以及不同关系模式中各属性之间的数据依赖关系。

（2）对各个关系模式之间的数据依赖进行极小化处理，消除冗余的联系。

第 31 页 共 51 页

（3）判断每个关系模式的范式，根据实际需要确定最合适的范式。

（4）根据需求分析阶段得到的处理要求，分析这些模式对于这样的应用环境是否合适，确定是否要对某些模式进行分解或合并。

10．将下列给定的E-R图转换为符合3NF的关系模式，并指出每个关系模式的主键和外键。

（1）图10-15所示为描述图书、读者以及读者借阅图书的E-R图。

图10-15 图书借阅E-R图 答：

图书（书号，书名，出版日期，作者名） 读者（读者号，读者名，联系电话，所在单位） 借阅（书号，读者号，借书日期，还书日期），书号为引用图书关系模式的外码，读者号为引用读者关系模式的外码。

（2）图10-16所示为描述商店从生产厂家订购商品的E-R图。

图10-16 商品订购E-R图 答：

商店（商店编号，商店名，联系电话）∈3NF 商品（商品编号，商品名称，库存量，商品分类）∈3NF 厂家（厂家编号，联系地址，联系电话）∈3NF 订购（商店编号，厂家编号，商品编号，订购日期，订购数量），商店编号为引用商店关系模式的外第 32 页 共 51 页

码，厂家编号为引用厂家关系模式的外码，商品编号为引用商品关系模式的外码。

（3）图10-17为描述学生参加学校社团的E-R图。

图10-17 学生参加社团E-R图 答：下列各关系模式中用下划线标识主码。

社团（社团号，社团名，电话，性质）∈3NF 学生（学号，姓名，性别，专业，社团号，参加日期），社团号为引用社团的外码。∈3NF 10． 根据下列描述，画出相应的E-R图，并将E-R图转换为满足3NF的关系模式，指明每个关系模式的主键和外键。现要实现一个顾客购物系统，需求描述如下：一个顾客可去多个商店购物，一个商店可有多名顾客购物；每个顾客一次可购买多种商品，但对同一种商品不能同时购买多次，但在不同时间可购买多次；每种商品可销售给不同的顾客。对顾客的每次购物都需要记录其购物的商店、购买商品的数量和购买日期。需要记录的“商店”信息包括：商店编号、商店名、地址、联系电话；需要记录的顾客信息包括：顾客号、姓名、住址、身份证号、性别。需要记录的商品信息包括：商品号、商品名、进货价格、进货日期、销售价格。

答：

第11章 存储过程和触发器 习题 1． 存储过程的作用是什么？为什么利用存储过程可以提高数据的操作效率？ 答：（1）第 33 页 共 51 页

允许模块化程序设计 （2）改善性能 （3）减少网络流量 （4）可作为安全机制使用 因为系统对存储过程是预编译的。

2． 在定义存储过程的语句中是否可以包含数据的增、删、改语句？ 答：可以。

3． 用户和存储过程之间如何传递数据？ 答：可通过输入、输出参数。或者 4． 存储过程的参数有几种形式？ 答：有输入和输出两种。

5． 触发器的作用是什么？ 前触发和后触发的主要区别是什么？ 答：实现复杂的处理逻辑和商业规则，增强了数据完整性约束的功能。

前触发器是在引发触发器执行的操作之前先执行触发器；后触发器是在引发触发器执行的操作执行完后再执行触发器。

6． 插入操作产生的临时工作表叫什么？它存放的是什么数据？ 答：inserted，存放新插入的数据。

7． 删除操作产生的临时工作表叫什么？它存放的是什么数据？ 答：deleted，存放被删除的数据。

8． 更改操作产生的两个临时工作表叫什么？其结构分别是什么，它们分别存放的是什么数据？ 答：inserted和deleted，结构同定义触发器的表，分别存放更新前和更新后的数据。

上机练习 1．利用第11章建立的students数据库以及Student、Coures、SC表，创建满足下述要求的存储过程，并查看存储过程的执行结果。

第 34 页 共 51 页

（1） 查询每个学生的修课总学分，要求列出学生学号及总学分。

create proc p1 as select sno,SUM(credit) as 总学分

from SC group by sno （2） 查询学生的学号、姓名、修的课程号、课程名、课程学分，将学生所在的系作为输入参数，执行此存储过程，并分别指定一些不同的输入参数值。

create proc p2 @dept varchar(20) = ＇计算机系＇ as

select ,sname,co,cname,credit from Student s

join SC on = join Course c on co=SCo where

Sdept = @dept 执行示例1：E_EC P2 执行示例2：E_EC P2 ＇通信工程系＇ （3） 查询指定系的男生人数，其中系为输入参数，人数用输出参数返回。

create proc p3 @dept varchar(20),@rs int output as

select @rs = COUNT(_) from Student where Sdept =

@dept and Sse_ = ＇男＇ （4） 查询考试平均成绩超过指定分值的学生学号和平均成绩。

create proc p4 @_ int as select sno,avg(grade)

from sc group by sno having avg(grade) > @_

（5） 查询查询指定系的学生中，选课门数最多的学生的选课门数和平均成绩，要求系为输入参数，选课门数和平均成绩用输出参数返回。

第 35 页 共 51 页

create proc p5 @dept varchar(30),@cnt int

output,@avg int output as select top 1 @cnt =

count(_) ,@avg = avg(grade) from sc join student s

on = where sdept = @dept group by

order by count(_) desc （6） 删除指定学生的指定课程的修课记录，其中学号和课程号为输入参数。

create proc p6 @sno char(7),@cno char(10) as

delete from SC where Sno = @sno and cno = @cno （7） 修改指定课程的开课学期。输入参数为：课程号和修改后的开课学期,开课学期的默认值为2。如果指定的开课学期不在1~8范围内，则不进行修改。

create proc p7 @cno char(10) , @new_semester int =

2 as if @new_semester between 1 and 8 update

course set semester = @new_semester where cno =

@cno 3.

修改第1题（1）的存储过程，使之能够查询指定系中，每个学生选课总门数、总学分和考试平均成绩。

alter proc p1 @dept varchar(30) as select

,count(_) 选课总门数,SUM(credit) as 总学分,

Avg(grade) 考试平均成绩 from SC join student s on

= group by 4．利用第11章建立的第 36 页 共 51 页

students数据库以及Student、Coures、SC表，创建满足如下要求的触发器，并检测触发器的功效。

（1）限制考试成绩必须在0~100范围内。

create trigger tri1 on sc after insert,update as if

e_ists(select _

from inserted where grade not between 0 and 100)

rollback （2）限制学生所在系的取值必须在｛计算机系，信息系，物理系，数学系｝范围内。

create trigger tri2 on student after insert,update as

if e_ists(select _

from student where sdept not in (＇计算机系＇,＇信息系＇,＇物理系＇,＇数学系＇)) Rollback （3）限制学生的选课总门数不能超过8门。

create trigger tri4 on sc after insert as if

e_ists(select _

from sc where sno in (select sno from inserted)

group by sno having count(_) > 8 ) rollback （4）限制不能删除考试成绩不及格学生的考试记录。

create trigger tri4 on sc after delete as if

e_ists(select _

第 37 页 共 51 页

from deleted where grade < 60 ) rollback 5．利用11.3节创建的工作表和职工表，定义满足如下要求的触发器，并检测触发器的功效。

（1）限制职工的基本工资和浮动工资之和必须大于等于20__0。

create trigger tri4 on 职工表 after insert,update as

if e_ists(select _

from inserted where (基本工资 + 浮动工资 ) <20__0 )

Rollback （2）限制工作表中最高工资不能低于最低工资的1.5倍。

create trigger tri5 on 工作表 after insert,update as

if e_ists(select _

from inserted where 最高工资 < 最低工资_1.5 )

Rollback （3）限制不能删除基本工资低于1500的职工。

create trigger tri6 on 职工表 after delete as if

e_ists(select _

from deleted where 基本工资 < 1500 ) Rollback

第12章 函数和游标 1． SQL Server 20__提供的日期和时间函数有哪些？ 答：getdate、dateadd、datediff、datename、day、month、year 2． SQL Server 20__提供的类型转换函数有哪些？其语法格式分别是什么？ 答：cast和convert 3． SQL

Server 20__支持的用户自定义函数有几种？每一种函数的函数体第 38 页 共 51 页

是什么？返回值是什么？ 答：标量函数、内联表值函数、多语句表值函数。

标量函数的函数体是可以是一系列SQL语句，返回值是一个标量值；

内联表值函数的函数体的一条查询语句，返回值是查询语句的执行结果（表）；

多语句表值函数的函数体是一系列SQL语句，返回值是一个表。

4． 利用系统提供的函数，完成下列操作：

（1） 计算从20__0年1月1日到当前日期的天数、月份数及年数。

答：select datediff(day,＇20__0-1-1＇,getdate) 天数,

datediff(month,＇20__0-1-1＇,getdate) 月份数,

datediff(year,＇20__0-1-1＇,getdate) 年数 （2） 分别计算系统当前日期加上40天和减去40天后的新日期。

答：select dateadd(day,40,getdate) 加40天,

dateadd(day,-40,getdate) 减40天 （3） 得到“You are a

student”字符串中从11开始，长度为7的子串。

答：select substring(＇You are a student＇,11,7)

（4） 分别计算“You are a student”和“我们是学生”字符串中字符的个数。

第 39 页 共 51 页

答：select len(＇You are a student＇),len(＇我们是学生＇) （5） 分别得到字符串“I am a teacher and you are

students” 中左边14个和右边16个字符组成的字符串。

答：select left(＇I am a teacher and you are

students＇,14), right(＇I am a teacher and you are

students＇,16) 5． 游标的作用是什么？其包含的内容是什么？ 答：游标提供了对查询结果集的定位操作功能。其包含内容是查询结果集。

6． 如何判断游标当前行指针指到了游标结果集之外？ 答：使用@@fetch_status全局变量。

7． 使用游标需要几个步骤？分别是什么？其中哪个步骤是真正产生游标结果集？ 答：五个主要。步骤，定义游标、打开游标、处理数据、关闭游标、释放游标资源。打开游标。

8． 关闭游标和释放游标在功能上的差别是什么？ 答：关闭游标并不真实释放游标占用的资源，也就是游标的定义还存在，还可以通过open语句再次打开该游标进行处理。释放游标是释放了与该游标有关的一切资源。

上机练习 1.

创建满足下述要求的用户自定义标量函数。

（1） 查询指定学生已经得到的修课总学分（考试及格的课程才能拿到学分），学号为输入参数，总学分为函数返回结果。第 40 页 共 51 页

并写出利用此函数查询9512学生的姓名、所修的课程名、课程学分、考试成绩以及拿到的总学分的SQL语句。

答：

CREATE FUNCTION dbo.f_Sum_Credit(@sno char(7))

RETURNS int AS BEGIN DECLARE @_ int SELECT @_ =

sum(credit) FROM SC join course c on co = sco WHERE

SNO = @sno and grade >= 60 RETURN @_ END 调用：

SELECT Sname, cname,credit, dbo.f_Sum_Credit()

AS 总学分 FROM Student s join sc on =

join course c on co = sco WHERE = ＇9512＇

（2） 查询指定系在指定课程(课程号)的考试平均成绩。

答：

CREATE FUNCTION dbo.f_Avg_Grade(@dept

varchar(20),@cno varchar(10)) RETURNS int AS BEGIN

DECLARE @_ int SELECT @_ = avg(grade) FROM SC join

student s on = where sdept = @dept and

cno = @cno RETURN @_ END （3） 查询指定系的男生中选课门数超过指定门数的学生人数。

答：

CREATE FUNCTION dbo.f_rs(@dept varchar(20),@cnt int)

RETURNS int AS BEGIN DECLARE @_ int SET @_ =

(select count(_) from student where sdept = @dept and

第 41 页 共 51 页

sse_ = ＇男＇ and sno in ( select sno from sc group

by sno having count(_) > @cnt )) RETURN @_ END 2.

创建满足下述要求的用户自定义内联表值函数。

（1） 查询选课门数在指定范围内的学生的姓名、所在系和所选的课程。

答：

CREATE FUNCTION dbo.f_count(@_ int) RETURNS TABLE

AS RETURN ( SELECT Sname, sdept, cno FROM

Student S JOIN SC ON = WHERE in

( select sno from sc group by sno having

count(_) > @_ )) （2） 查询指定系的学生考试成绩大于等于90的学生的姓名、所在系、课程名和考试成绩。并写出利用此函数查询计算机系学生考试情况的SQL语句，只列出学生姓名、课程名和考试成绩。

答：

CREATE FUNCTION dbo.f_dept(@dept varchar(20))

RETURNS TABLE AS RETURN ( SELECT Sname, sdept,

cname,grade FROM Student S JOIN SC ON =

join course c on co = sco WHERE sdept = @dept and

grade >= 90 ) 调用：

SELECT sname,cname,grade FROM dbo.f_dept(＇计算机系＇)

3.

第 42 页 共 51 页

创建满足下述要求的用户自定义多语句表值函数。

（1） 查询指定系年龄最大的前2名学生的姓名和年龄，包括并列的情况。

答：

CREATE FUNCTION f_TopAge(@dept varchar(20)) RETURNS

@retSType table( Sname char(10), Sage int ) AS

BEGIN INSERT INTO @retSType SELECT top 2 with ties

Sname, Sse_ FROM Student WHERE Sdept = @dept

order by sage desc RETURN END （2） 查询指定学生（姓名）的考试情况，列出姓名、所在系、修的课程名和考试情况，其中考试情况列的取值为：如果成绩大于等于90，则为“优”；如果成绩在80~89，则为“良好”；如果成绩在70~79，则为“一般”；如果成绩在60~69，则为“不太好”；如果成绩小于60，则为“很糟糕”。并写出利用此函数查询李勇的考试情况的SQL语句。

答：

CREATE FUNCTION f_E_am(@name varchar(20)) RETURNS

@retSType table( Sname char(10), Sdept varchar(20),

Cname varchar(20), grade varchar(8) ) AS BEGIN

INSERT INTO @retSType SELECT Sname, Sdept,Cname,case

when grade >= 90 then ＇优＇ when grade between 80 and

89 then ＇良好＇ when grade between 70 and 79 then ＇第 43 页 共 51 页

一般＇ when grade between 60 and 69 then ＇不太好＇

else ＇很糟糕＇ end FROM Student s join sc on

= join course c on co = sco WHERE

Sname = @name RETURN END 调用：

SELECT _

FROM f_E_am(＇李勇＇) 4.

创建满足下述要求的游标。

（1） 查询VB课程的考试情况，并按如下形式显示结果数据:

选了VB课程的学生情况：

姓名 所在系 成绩 李勇 计算机系

86 刘晨 计算机系 78 吴宾 信息系

75 张海 信息系 68 答：

declare @sname char(10),@dept char(14),@grade int

declare c1 SCROLL cursor for select sname,sdept,grade

from student s join sc on = join course c on

co = sco where cname = ＇vb＇ order by grade desc open c1

print ＇ 姓名 所在系 VB成绩＇ print ＇-----------------------------＇ fetch ne_t from c1 into

@sname ,@dept ,@grade while @@FETCH_STATUS = 0 begin

print @sname + @dept + cast(@grade as char(3)) fetch

ne_t from c1 into @sname ,@dept ,@grade End close c1

第 44 页 共 51 页

deallocate c1 （2） 统计每个系的男生人数和女生人数，并按如下形式显示结果数据。

系名 性别 人数 ==================== 计算机系

男 2 计算机系 女 1 数学系 男 1

数学系 女 1 信息系 男 2 信息系

女 1 答：

declare @dept char(13),@se_ char(8),@cnt int declare

c1 cursor for select sdept,sse_,count(_) from student

group by sdept,sse_ order by sdept print ＇系名 性别 人数＇ print ＇=======================＇ open c1

fetch ne_t from c1 into @dept,@se_,@cnt while

@@FETCH_STATUS = 0 begin print @dept + @se_ + cast(@cnt

as char(4)) fetch ne_t from c1 into @dept,@se_,@cnt end

close c1 deallocate c1 （3） 列出每个系的学生信息，要求首先列出一个系的系名，然后在该系名下列出本系学生的姓名和性别；再列出下一个系名，然后在此系名下再列出该系的学生姓名和性别；以此类推，直至列出全部系。要求按如下形式显示结果数据: 计算机系学生：

李勇 男 刘晨 男 王敏

计算机系 ===================== 数学系学生：

钱小平 数学系 王大力 数学系

===================== 信息系学生：

第 45 页 共 51 页

张立 信息系 吴宾 信息系 张海

信息系 ===================== 答：

declare @sname char(10),@se_ char(4),@dept char(20)

declare c1 cursor for select distinct sdept from student

open c1 fetch ne_t from c1 into @dept while

@@FETCH_STATUS = 0 begin print rtrim(@dept) + ＇学生：＇ declare c2 cursor for select sname,sse_

from student where sdept = @dept open c2 fetch ne_t

from c2 into @sname ,@se_ while @@FETCH_STATUS = 0

begin print @sname + @se_ fetch ne_t from c2 into

@sname ,@se_ end close c2 deallocate c2 print ＇==============＇ fetch ne_t from c1 into @dept end close

c1 deallocate c1 第13章 安全管理 习 题 1.

通常情况下，数据库中的权限划分为哪几类？ 答：通常情况下，将数据库中的权限划分为两类。一类是对数据库系统进行维护的权限，另一类是对数据库中的对象和数据进行操作的权限。

2.

数据库中的用户按其操作权限可分为哪几类，每一类的权限是什么？ 答：数据库中的用户按其操作权限的不同可分为如下三类：

（1） 系统管理员：在数据库服务器上具有全部的权限

（2） 数据库对象拥有者：对其所拥有的对象具有全部权限。

第 46 页 共 51 页

（3） 普通用户：只具有对数据库数据的增、删、改、查权限。

3.

SQL Server的登录名的来源有几种？分别是什么？ 答：两种，Windows身份验证的和SQL Server身份验证的。

4.

权限的管理包含哪些内容？ 答：权限管理包含：授予权限、收回权限和拒绝权限。

5.

什么是用户定义的角色，其作用是什么？ 答：用户自己建立的角色，其作用是简化数据库用的权限管理。

6.

在SQL Server中，用户定义的角色中可以包含哪些类型的成员？ 答：可以包含普通用户、用户定义的角色。

7.

写出实现下述功能到T-SQL语句。

(1) 建立一个Windows身份验证的登录名，Windows域名为：CS，登录名为：Win_Jone。

答：CREATE LOGIN [CS＼Win_jone] FROM WINDOWS; (2) 建立一个SQL Server身份验证的登录名，登录名为：SQL_Stu，密码为：3Wcd5sTap43K。

第 47 页 共 51 页

答：CREATE LOGIN SQL_Stu WITH PASSWORD = ＇3Wcd5sTap43K＇; (3) 删除Windows身份验证的登录名，Windows域名为：IS，登录名为：U1。

答：DROP LOGIN [IS＼U1] (4) 删除SQL Server身份验证的登录名，登录名为：U2。

答：DROP LOGIN U2 (5) 建立一个数据库用户，用户名为SQL_Stu，对应的登录名为SQL Server身份验证的SQL_Stu。

答：CREATE USER SQL_Stu; (6) 建立一个数据库用户，用户名为Jone，对应的登录名为Windows身份验证的Win_Jone，Windows域名为：CS。

答：CREATE USER Jone FOR Win_Jone (7) 授予用户u1具有对course表的插入和删除权。

答：grant insert, delete on course to u1 (8) 授予用户u1对Course表的删除权。

答：grant delete on course to u1 (9) 收回u1对course表的删除权。

答：revoke delete on course from u1 (10) 拒绝用户u1获得对Course表的更改权。

答：deny update on course to u1 (11) 授予用户u1具有创建表和视图的权限。

答：grant create table,create view to u1 (12) 收回用户u1创建表的权限。

第 48 页 共 51 页

答：revoke create table from u1 (13) 建立一个新的用户定义的角色，角色名为：NewRole。

答：create role NewRole (14) 为New_Role角色授予SC表的查询和更改权。

答：grant select, update on sc to New_Role (15) 将SQL Server身份验证的u1用户和Windows身份验证的Win_Jone用户添加到New_Role角色中。

答：E_EC sp_addrolemember ＇New_Role＇, ＇u1＇ E_EC

sp_addrolemember ＇New_Role＇, ＇Win_Jone＇ 上机练习

3．用log1建立一个新的数据库引擎查询，并在Students数据库中执行下述语句，能否成功？为什么？ SELECT _

FROM Course 答：不能，因为log1没有被授予Course表的查询权。

5．在SSMS中，用log2建立一个新的数据库引擎查询，执行下述语句，能否成功？为什么？ INSERT INTO Course VALUES(＇C＇，＇数据库基础＇，4，5) 答：能成功，因为被授予了插入权。

再执行下述语句，能否成功？为什么？ SELECT _

FROM Course 答：不能，因为log2没有被授予Course表的查询权。

6．在SSMS中，在log1建立的数据库引擎查询中，再次执行下述语句：

第 49 页 共 51 页