数据库系统及应用教程(SQL Server 2008)习题1-8章答案

习题1

1．名词解释：

DB：是长期存储在计算机内、有组织的、统一管理的相关数据的集合。

DBMS：是位于用户与0S之间的一层数据管理软件，它为用户或应用程序提供访问DB的方法。

DBS：是实现有组织地、动态地存储大量关联数据、方便多用户访问的计算机硬件、软件和数据资源组成的系统，即采用数据库技术的计算机系统。

外模式：是用户用到的那部分数据的描述。

概念模式：数据库中全部数据的整体逻辑结构的描述。 内模式：DB在物理存储方面的描述。

实体：客观存在、可以相互区别的事物称为实体。 属性：实体有很多特性，每一个特性称为一个属性。

实体标识符：能惟一标识实体的属性或属性集，称为实体标识符。

分布式数据库：是由一组数据组成的，这组数据分布在计算机网络的不同计算机上，网络中的每个结点具有独立处理的能力（称为场地自治），可以执行局部应用。同时，每个结点也能通过网络通信子系统执行全局应用。

2．文件系统阶段的数据管理有哪些特点？

答：文件系统阶段主要有5个特点：数据以“文件”形式长期保存；数据的逻辑结构与物理结构有了区别；文件组织已多样化；数据面向应用；对数据的操作以记录为单位。

3．文件系统阶段的数据管理有些什么缺陷？试举例说明。 答：主要有3个缺陷：数据冗余；数据不一致性；数据联系弱。

例如学校里教务处、财务处、保健处建立的文件中都有学生详细资料，如联系电话，家庭住址等。这就是“数据”冗余；如果某个学生搬家，就要修改3个部门文件中的数据，否则会引起同一数据在3个部门中不一致；产生上述问题的原因是这3个部门的文件中数据没有联系。

4． 数据库阶段的数据管理有哪些特色?

答：主要有5个特点：采用数据模型可以表示复杂的数据结构；有较高的数据独立性； 为用户提供了方便的用户接口；提供了4个方面的数据控制功能；对数据的操作以数据项为 单位，增加了系统的灵活性。

5．实体之间联系有哪几种？分别举例说明？

答：1:1联系：如果实体集El中每个实体至多和实体集E2中的一个实体有联习，反之亦然，那么El和E2的联系称为“l:1联系”。例如：电影院的座位和观众实体之间的联系。

1:N联系：如果实体集El中每个实体可以与实体集E2中任意个（零个或多个)实体有联系，而E2中每个实体至多和El中一个实体有联系，那么El和E2的联系是“1:N联系”。 例如：部门和职工两个实体集之间的联系。

M:N联系：如果实体集El中每个实体可以与实体集E2中任意个（零个或多个)实体有联系，反之亦然，那么El和E2的联系称为“M:N联系”。例如：工程项目和职工两个实体集之间的联系。

6．分析层次模型、网状模型和关系模型的特点。

答：层次模型的数据结构为树结构，记录之间联系通过指针实现，查询较快，但DML属于过程化的语言，操作复杂。

1

网状模型的数据结构为有向图，记录之间联系通过指针实现，查询较快，并且容易实现M：N联系，但DML属于过程化的语言，编程较复杂。

关系模型的数据结构为二维表格，容易为初学者理解。记录之间联系通过关键码实现。DML属于非过程化语言，编程较简单。

面向对象模型能完整描述现实世界的数据结构，具有丰富的表达能力，能表达嵌套、递归的数据结构。但涉及的知识面较广，用户较难理解，这种模型尚未普及。

7．简述数据库系统的两级映像和数据独立性之间的关系。

答：为了能够在系统内部实现外部级、概念级和内部级3个抽象层次的联系和转换，数据库管理系统在这三级模式之间提供了两层映像：

外模式/模式映像定义通常包含在各自外模式的描述中，保证了数据与程序的逻辑独立性，简称数据的逻辑独立性，应用程序是依据外模式编写的；

模式/内模式映像包含在模式描述中，此映像是唯一的，它定义了数据全局逻辑结构与存储结构之间的对应关系，它保证了数据与程序的物理独立性，所以称为数据的物理独立性。

8．分析分布式数据库的体系结构。

答： 分布式DBS的体系结构分为四级：全局外模式、全局概念模式、分片模式和分配模式。

(1)全局外模式：它们是全局应用的用户视图，是全局概念模式的子集。

(2)全局概念模式：全局概念模式定义了分布式数据库中所有数据的逻辑结构。

(3)分片模式：分片模式定义片段以及定义全局关系与片段之间的映象。这种映象是一对多的，即每个片段来自一个全局关系，而一个全局关系可分成多个片段。

(4)分配模式：片段是全局关系的逻辑部分，一个片段在物理上可以分配到网络的不同场地上。分配模式根据数据分配策略的选择定义片段的存放场地。

9．当前主要的几种新型数据库系统各有什么特点？用于什么领域？

答：主要有：分布式数据库系统、面向对象数据库系统、专家数据库系统、多媒体数据库系统、空间数据库系统、工程数据库系统。

分布式数据库系统分布式数据库系统是地理上分布在计算机网络的不同结点，逻辑上属于同一系统的数据库系统，它不同于将数据存储在服务器上供用户共享存取的网络数据库系统，分布式数据库系统不仅能支持局部应用，存取本地结点或另一结点的数据，而且能支持全局应用，同时存取两个或两个以上结点的数据。分布式数据库的主要特点是：数据是分布的；数据是逻辑相关的；结点的自治性。分布式数据库系统广泛地应用于大企业,多种行业及军事国防等领域。

面向对象数据库（简称OODB）有以下特点：

使用对象数据模型将客观世界按语义组织成由各个相互关联的对象单元组成的复杂系统 。对象可以定义为对象的属性和对象的行为描述，对象间的关系分为直接和间接关系；语义上相似的对象被组织成类，类是对象的集合，对象只是类的一个实例，通过创建类的实例实现对象的访问和操作；对象数据模型具有“封装”、“继承”、“多态”等基本概念；方法实现类似于关系数据库中的存储过程，但存储过程并不和特定对象相关联，方法实现是类的一部分。面向对象数据库可以实现一些带有复杂数据描述的应用系统，如时态和空间事务、多媒体数据管理等。

专家数据库系统（Expert DataBase System，EDBS）是人工智能与数据库技术相结合的产物。它具有两种技术的优点，而避免了它们的缺点。它是一种新型的数据库系统，它所涉及的技术除了人工智能和数据库以外还有逻辑、信息检索等多种技术和知识。

多媒体数据库系统随着信息技术的发展，数据库应用从传统的企业信息管理扩展到计算机辅助设计（Computer Aided Design，CAD）、计算机辅助制造

2

（Computer Aided Manufacture，CAM）、办公自动化（Office Automation，OA）、人工智能（Artificial Intelligent，AI）等多种应用领域。这些领域中要求处理的数据不仅包括传统的数字、字符等格式化数据，还包括大量多种媒体形式的非格式化数据，如图形、图像、声音等。多媒体数据库系统（Multimedia Database System，MDBS）是能存储和管理多种媒体的数据库系统。

空间数据库系统的特点：数据量庞大。空间数据库面向的是地理学及其相关对象，而在客观世界中它们所涉及的往往都是地球表面信息、地质信息、大气信息等及其复杂的现象和信息，所以描述这些信息的数据容量很大，容量通常达到 GB级；具有高可访问性。空间信息系统要求具有强大的信息检索和分析能力， 这是建立在空间数据库基础上的，需要高效访问大量数据；空间数据模型复杂。空间数据库存储的不是单一性质的数据，而是涵盖了几乎所有与地理相关的数据类型。空间数据库系统有效地利用卫星遥感资源迅速绘制出各种经济专题地图。

工程数据库系统特点：主要数据库是图形和图象数据；数据库规模庞大；设计处理的状态是直观和暂时的；设计的多次版本信息都要予以保存；事务是长寿的，从设计到生产周期较长；数据要求有序性；数据项可多达几百项。工程数据库系统主要用于CAD/CAM领域。

习题2

1．名词解释：

超键：能惟一标识元组的属性或属性集，称为关系的超键。 候选键：不含有多余属性的超键，称为候选键。 实体完整性规则：实体的主键值不允许是空值。 参照完整性规则：依赖关系中的外键值或者为空值，或者是相应参照关系中某个主键值。 函数依赖：设有关系模式R(U)，X和Y是属性集U的子集，若对于R(U)的任意一个可能的关系r，r中不可能存在两个元组在X上的属性值相等，而在Y上的属性值不等，则称X函数确定Y或Y函数依赖（Functional Dependency，简记为FD）于X，记作X→Y。

无损分解：当对关系模式R进行分解时，R的元组将分别在相应属性集进行投影而产生新的关系。如果对新的关系进行自然连接得到的元组集合与原关系完全一致，则称该分解为无损分解。

2NF：如果关系模式R属于1NF，且它的每一个非主属性都完全函数依赖于R的候选键，则称R属于第二范式，简记为R∈2NF。

3NF：如果关系模式R属于1NF，且每个非主属性都不传递依赖于R的候选键，那么称R属于第三范式，简记为R∈3NF。

2．为什么关系中的元组没有先后顺序，且不允许有重复元组？

答：由于关系定义为元组的集合，而集合中的元素是没有顺序的，因此关系中的元组也就没有先后的顺序(对用户而言)。这样既能减少逻辑排序，又便于在关系数据库中引进集合论的理论。

3．笛卡尔积、等值连接和自然连接三者之间有什么区别？

答：笛卡儿积是一个基本操作，而等值连接和自然连接是组合操作。 设关系R的元数为r，元组个数为m；关系S的元数为s。，元组个数为n。 那么，R×S的元数为r+s，元组个数为m×n；

R S的元数也是r+s，但元组个数小于等于m×n；

iθj 3

R S的元数小于等于r+s，元组个数也小于等于m×n： 4．设有关系R和S，如图2.17所示。 R A B C S A B C 计算R∪S，R-S3 ，R∩S6 ，R×7 S，π3,2(S)，σB<'5'3 (R)，4 R 5 S，R S。 答： 2 5 7 7 2 2<2 3 R

7 2 3

∪S A B 4 C 4 R-S3 A B C R×S R.A R.B R.C S.A S.B S.C 3 6 7 3 3 6 7 3 4 5

2 5 7 图2.17 关系6 R和7 S 2 5 7 3 6 7 7 2 3

7 2 3 4 4 3 2 5 7 3 4 5 4 4 3 R∩S B C 2 5 7 7 2 3 3 4 5 A 7 2 3 3 4 5 7 2 3 π 7 2 3 7 2 3 3,2(S) C B

5 4 σB<’5’(R) A B C 4 4 3 3 4 5 4 4 3 7 2 3 3 2 7 2 3 4 4 3 R S A B C R S R.A R.B R.C S.A S.B S.C 2<2 7 2 3

7 2 3 3 4 5 5．设教学管理数据库中有三个关系 S(SNO,SNAME,AGE,SEX,SDEPT) SC(SNO,CNO,GRADE)

C(CNO,CNAME,CDEPT,TNAME) 试用关系代数表达式表示下列查询语句：

(1) 检索LIU老师所授课程的课程号、课程名。 (2) 检索年龄大于23岁的男学生的学号与姓名。

(3) 检索学号为S3学生所学课程的课程名与任课教师名。 (4) 检索至少选修LIU老师所授课程中一门课的女学生姓名。 (5) 检索WANG同学不学的课程的课程号。 (6) 检索至少选修两门课程的学生学号。 解：

⑴ πCNO,CNAME(σTEACHER=’LIU’(C)) ⑵ πSNO,SNAME(σAGE>’23’∧SEX=’M’(SC)) ⑶ πCNAME,TEACHER(σSNO=’S3’(SC C)) ⑷ πSNAME(σSEX=’F’∧TEACHER=’LIU’(S SC C)) ⑸ πCNO(C)-πCNO(σSNAME=’WANG’(S SC)) ⑹ π1(σ1=4∧2≠5(SC×SC))

4

6．设关系模式R(ABCD)，F是R上成立的FD集，F={A→B，C→B}，则相对于F，试写出关系模式R的候选键。并说明理由。

解：R的关键码为ACD。因为从已知的F，A→B，只能推出ACD→ABCD。 7．设关系模式R(ABCD)，F是R上成立的FD集，F={AB→CD，A→D}。 (1) 试说明R不是2NF模式的理由。

(2) 试把R分解成2NF模式集。

答：设关系模式R（ABCD），F是R上成立的FD集，F={AB→CD，A→D}。 ⑴ 试说明R不是2NF模式的理由。

⑵ 试把R分解成2NF模式集。

答：⑴ 从已知的函数依赖集F，可知R的候选键是AB。另外，由AB→CD可推出AB→D，再由A→D可知AB→D是部分（局部）函数依赖，因此R不是2NF模式。

⑵ 如果将R分解成{AD，ABC}，则是2NF模式集。

8．设有关系模式R（职工编号，日期，日营业额，部门名，部门经理），该模式统计商店里每个职工的日营业额，以及职工所在的部门和经理信息。如果规定：每个职工每天只有一个营业额；每个职工只在一个部门工作；每个部门只有一个经理。试回答下列问题： (1) 根据上述规定，写出模式R的基本FD和候选键。

(2) 说明R不是2NF的理由，并把R分解成2NF模式集。 (3) 进而分解成3NF模式集。 解：

⑴ 基本的FD有三个：

（职工编号，日期）→ 日营业额 职工编号 → 部门名 部门名 → 部门经理 R的关键码为：（职工编号，日期）。 ⑵ R中有两个这样的FD：

（职工编号，日期）→（部门名，部门经理） 职工编号 →（部门名，部门经理）

可见前一个FD是局部依赖，所以R不是2NF模式。 R应分解Rl（职工编号，部门名，部门经理） R2（职工编号，日期，日营业额） 此处，Rl和R2都是2NF模式。 ⑶ R2已是3NF模式。

在R1中，存在两个FD：职工编号 → 部门名 部门名 → 部门经理

因此，“职工编号 → 部门经理”是一个传递依赖，Rl不是3NF模式。 R1应分解成R11（职工编号，部门名） R12（部门名，部门经理）

这样，ρ={R11，Rl2，R2}是一个3NF模式集。

9．设有关系模式R（运动员编号，比赛项目，成绩，比赛类别，比赛主管），如果规定：每个运动员每参加一个比赛项目，只有一个成绩；每个比赛项目只属于一个比赛类别；每个比赛类别只有一个比赛主管。试回答下列问题：

(1) 根据上述规定，写出模式R的基本FD和候选键。 (2) 说明R不是2NF的理由，并把R分解成2NF模式集。 (3) 进而分解成3NF模式集。

5

解：⑴ 基本的FD有3个：

（运动员编号，比赛项目）→ 成绩

比赛项目 → 比赛类别 比赛类别 → 比赛主管 R的关键码为（运动员编号，比赛项目）。 ⑵ R有两个这样的FD：

（运动员编号，比赛项目）→ （比赛类别，比赛主管） 比赛项目 → （比赛类别，比赛主管） 可见，前一个FD是部分（局部）函数依赖，所以R不是2NF模式。 如果把R分解成R1（比赛项目，比赛类别，比赛主管） R2（运动员编号，比赛项目，成绩） 这里，R1和R2都是2NF模式。 ⑶ R2已是3NF模式。

在R1中，存在两个FD：比赛项目 → 比赛类别 比赛类别 → 比赛主管

因此，“比赛项目 → 比赛主管”是一个传递依赖，R1不是3NF模式。 R1应分解为R11（比赛项目，比赛类别） R12（比赛类别，比赛主管） 这样，ρ={R11，R12，R2}是一个3NF模式集。

习题3

1．名词解释

数据库设计：是指对于给定的软、硬件环境，针对现实问题，设计一个较优的数据模型，建立相应的数据库结构和数据库应用系统。

数据流图：是从“数据”和“对数据的加工”两方面表达数据处理系统工作过程的一种图形表示法。具有直观、易于被用户和软件人员双方都能理解的一种表达系统功能的描述方式。

数据字典：数据字典提供了对数据库数据描述的集中管理，它的功能是存储和检索各种数据描述，如叙述性的数据定义等，并且为DBA提供有关的报告。对数据库设计来说，数据字典是进行详细的数据收集和数据分析所获得的主要成果。数据字典中通常包括数据项、数据结构、数据流、数据存储和处理过程五个部分。

弱实体：一个实体对于另一些实体(父实体)具有很强的依赖联系，而且该实体主键的部分或全部从其父实体中获得，则称该实体为弱实体。

概念结构设计：概念结构设计就是将需求分析得到的用户需求抽象为信息结构即概念模型的过程，是对信息世界进行建模，常用的概念模型是E-R模型。

逻辑结构设计：是将概念结构设计阶段所得到的概念模型转换为具体DBMS所能支持的数据模型（即逻辑结构），并对其进行优化。

物理结构设计：数据库最终是要存储在物理设备上的。为一个给定的逻辑数据模型选取一个最适合应用环境的物理结构的过程，就是数据库的物理设计。

2．什么是数据库设计目标？数据库设计的基本步骤有哪些？

6

答：数据库设计的主要目标有：最大限度地满足用户的应用功能需求、获得良好的数据库性能、对现实世界模拟的精确度要高、数据库设计应充分利用和发挥现有DBMS的功能和性能、符合软件工程设计要求。

数据库设计的基本步骤是：需求分析阶段、概念结构设计阶段、逻辑结构设计阶段、物理结构设计阶段、数据库实施阶段、数据库运行和维护阶段。

3．数据库设计的需求分析阶段是如何实现的？任务是什么？

答：需求分析阶段的工作由下面4步组成：分析用户活动，产生用户活动图；确定系统范围，产生系统范围图；分析用户活动所涉及的数据，产生数据流图；分析系统数据，产生数据字典。

需求分析阶段的任务是对系统的整个应用情况作全面的、详细的调查，确定企业组织的 目标，收集支持系统总的设计目标的基础数据和对这些数据的要求，确定用户的需求；并把这些要求写成用户和数据库设计者都能接受的文档。

4．概念设计的具体步骤是什么？

答：概念设计的主要步骤为：进行数据抽象、设计局部概念模式；将局部概念模式综合成全局概念模式；评审。

5．简述采用E-R方法的数据库概念设计过程。

答：利用ER方法进行数据库的概念设计，可分成三步进行：首先设计局部ER模式，然后把各局部ER模式综合成一个全局ER模式，最后对全局ER模式进行优化，得到最终的ER模式，即概念模式。

6．逻辑设计的目的是什么？试述逻辑设计过程的输入和输出环境。 答：逻辑设计的目的是把概念设计阶段设计好的基本ER图转换为与选用的具体机器上的DBMS所支持的数据模型相符合的逻辑结构（包括数据库模式和外模式）。

逻辑设计过程中的输入信息有：独立于DBMS的概念模式，即概念设计阶段产生的所有局部和全局概念模式；处理需求,即需求分析阶段产生的业务活动分析结果；约束条件，即完整性、一致性、安全性要求及响应时间要求等；DBMS特性，即特定的DBMS特性，即特定的DBMS所支持的模式、子模式和程序语法的形式规则。

逻辑设计过程输出的信息有：DBMS可处理的模式；子模式；应用程序设计指南；物理设计指南。

7．规范化理论对数据库设计有什么指导意义？ 答：在概念设计阶段，已经把关系规范化的某些思想用作构造实体类型和联系类型的标准， 在逻辑设计阶段，仍然要使用关系规范化的理论来设计模式和评价模式。 规范化的目的是减少乃至消除关系模式中存在的各种异常，改善完整性，一致性和存储效率。

8．什么是数据库结构的物理设计？主要包含哪几方面的内容？

答：数据库结构的物理设计是指对一个给定的逻辑数据模型选取一个最适合应用环境的物理结构的过程， 所谓数据库的物理结构主要指数据库在物理设备上的存储结构和存取方法。物理设计的步骤为：

(1) 设计存储记录结构，包括记录的组成、数据项的类型和长度，以及逻辑记录到存储记录的映射；

(2) 确定数据存储安排；

(3) 设计访问方法，为存储在物理设备上的数据提供存储和检索的能力； (4) 进行完整性和安全性的分析、设计； (5) 程序设计。

9．数据库实施阶段主要做哪几件事情？

7

答：数据库实现阶段的主要工作有以下几点：建立实际数据库结构、试运行、装入数据。

10．数据库系统投入运行后，有哪些维护工作？ 答：数据库系统投入运行后，主要维护工作有：

(1) 维护数据库的安全性与完整性控制及系统的转储和恢复； (2) 性能的监督、分析与改进； (3) 增加新功能；

(4) 发现错误，修改错误。

11．设某商业集团数据库中有三个实体集。一是“商店”实体集，属性有商店编号、商店名、地址等；二是“商品”实体集，属性有商品号、商品名、规格、单价等；三是“职工”实体集，属性有职工编号、姓名、性别、业绩等。

商店与商品间存在“销售”联系，每个商店可销售多种商品，每种商品也可放在多个商店销售，每个商店销售每一种商品，有月销售量；商店与职工间存在着“聘用”联系，每个商店有许多职工，每个职工只能在一个商店工作，商店聘用职工有聘期和月薪。

试画出ER图，并在图上注明属性、联系的类型。再转换成关系模式集，并指出每个关系模式的主键和外键。

解：ER图如下图所示。 商品号 商品名 规格 单价 商品 M

月销售量 销售

商店编号 N

商店 商店名

1 聘期 地址 聘用 月薪 N

职工

职工编号 姓名 性别 业绩

所转换成的关系：

商品（商品号，商品名，规格，单价） 商店（商店编号，商店名，地址） 销售（商店编号，商品号，月销售量）

职工（职工编号，姓名，性别，业绩，聘期，月薪，商店编号）

8

12．设某商业集团数据库中有三个实体集。一是“公司”实体集，属性有公司编号、公司名、地址等；二是“仓库”实体集，属性有仓库编号、仓库名、地址等；三是“职工”实体集，属性有职工编号、姓名、性别等。

公司与仓库间存在“隶属”联系，每个公司管辖若干仓库，每个仓库只能属于一个公司管辖；

仓库与职工间存在“聘用”联系，每个仓库可聘用多个职工，每个职工只能在一个仓库工作，仓库聘用职工有聘期和工资。

试画出E-R图，并在图上注明属性、联系的类型。再转换成关系模式集，并指出每个关系模式的主键和外键。

解：ER图及属性、联系图如下：

公司编号 公司名 地址

公司

1

隶属 仓库编号 N

仓库 仓库名 1 聘期 地址

聘用

工资 N

职工

职工编号 姓名 性别

将ER图转换成为关系模式集为： 公司（公司编号，公司名，地址）

仓库（仓库编号，仓库名，地址，公司编号）

职工（职工编号，姓名，性别，仓库编号，聘期，工资）

13．设某商业集团数据库中有三个实体集。一是“商品”实体集，属性有商品号、商品名、规格、单价等；二是“商店”实体集，属性有商店号、商店名、地址等；三是“供应商”实体集，属性有供应商编号、供应商名、地址等。

供应商与商品间存在“供应”联系，每个供应商可供应多种商品，每种商品可向多个供应商订购，供应商供应每种商品有月供应量；商店与商品间存在“销售”联系，每个商店可销售多种商品，每种商品可在多个商店销售，商店销售商品有月计划数。

试画出E-R图，并在图上注明属性、联系的类型。再转换成关系模式集，并指出每个关系模式的主键和外键。

解：ER图及属性、联系图为：

9

商品号 商品名 规格

商品 N N

月供应量

供应

M

供应商

供应商编号 供应商名 地址 商店号

这个ER图转换的关系模式如下：

商品（商品号，商品名，规格，单价） 供应商（供应商编号，供应商名，地址） 商店（商店号，商店名，地址）

供应（商品号，供应商编号，月供应量）

单价 月计划数 销售 M 商店 商店名 地址 销售（商品号，商店号，月计划数）

14．假设要为银行的储蓄业务设计一个数据库，其中涉及到储户、存款、取款等信息，试设计E-R模型。

解:储蓄业务主要是存款、取款业务，设计的ER图如下所示。

账号 身份证号 姓名 地址 存款余额

储户

1 1

存款日期 取款日期 存款 取款 N N 存款单 取款单

存款单号 存款方式 金额 取款单号 取款方式 金额 15．假设某超市公司要设计一个数据库系统来管理该公司的业务信息。该超市公司的业务管理规则如下：

(1) 该超市公司有若干仓库，若干连锁商店，供应若干商品。

(2) 每个商店有一个经理和若干收银员，每个收银员只在一个商店工作。 (3) 每个商店销售多种商品，每种商品可在不同的商店销售。

10

(2) 查询年龄大于20岁的男学生的学号和姓名。

(3) 查询学号为S6的学生所学课程的课程名和任课教师名。

(4) 查询至少选修“王志强”老师所授课程中一门课程的女学生姓名。 (5) 查询“李小刚”同学不学的课程的课程号。

(6) 查询至少选修两门课程的学生学号。 解：

(1) USE JXGL

GO

SELECT CNO,CNAME FROM C

WHERE TNAME='王志强' GO

(2) USE JXGL

GO

SELECT SNO,SNAME FROM S

WHERE SEX='M' AND AGE>20 GO

(3) USE JXGL

GO

SELECT CNAME,TNAME

FROM S JOIN SC ON S.SNO=SC.SNO AND S.SNO='S6'

JOIN C ON SC.CNO=C.CNO

GO

(4) USE JXGL

GO

SELECT SNAME FROM S WHERE SNO IN

(

SELECT SNO FROM SC WHERE CNO IN (SELECT CNO FROM C

WHERE TNAME='王志强' ) )

GO

(5) USE JXGL

GO

SELECT CNO FROM C EXCEPT

16

SELECT CNO

FROM S JOIN SC ON S.SNO=SC.SNO AND S.SNAME='李小刚' GO

(6) USE JXGL

GO

select SNO,count(CNO) as 选修门数 from sc

group by SNO having count(CNO)>1 GO

3. 试用T-SQL查询语句表达下列对习题2数据库中三个基本表S、SC、C的查询： (1) 统计有学生选修的课程门数。

(2) 求选修C4号课程的学生的平均年龄。

(3) 求“王志强”老师所授课程的每门课程的学生平均成绩。 (4) 统计每门课程的学生选修人数（超过10人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列。 (5) 查询姓“王”的所有学生的姓名和年龄。

(6) 在SC中查询成绩为空值的学生学号和课程号。

(7) 查询年龄大于女同学平均年龄的男学生姓名和年龄。 解：

(1) USE JXGL

GO

SELECT COUNT(DISTINCT CNO) FROM SC GO

(2) USE JXGL

GO

SELECT AVG(AGE)

FROM S JOIN SC ON S.SNO=SC.SNO AND CNO='C4' GO

(3) USE JXGL

GO

SELECT SC.CNO,AVG(GRADE)

FROM SC JOIN C ON SC.CNO=C.CNO AND TNAME='王志强' GROUP BY SC.CNO GO

(4) USE JXGL

GO

SELECT CNO,COUNT(SNO) FROM SC

GROUP BY CNO HAVING COUNT(*)>10 ORDER BY 2 DESC,1 GO

(5) USE JXGL

GO

17

SELECT SNAME,AGE FROM S

WHERE SNAME LIKE '王%' GO

(6) USE JXGL

GO

SELECT SNO,CNO FROM SC

WHERE GRADE IS NULL GO

(7) USE JXGL

GO

SELECT SNAME,AGE

FROM S

WHERE SEX='M' AND AGE>(SELECT AVG(AGE) FROM S

WHERE SEX='F')

GO

4. 试用T-SQL更新语句表达对习题6-2中数据库中三个基本表S、SC、C的各个更新操作：

(1) 在基本表S中检索每一门课程成绩都大于等于80分的学生学号、姓名和性别，并把检索到的值送往另一个已存在的基本表STUDENT(SNO,SNAME,SEX)。 (2) 在基本表SC中删除尚无成绩的选课元组。

(3) 把“张成民”同学在SC中的选课记录全部删去。

(4) 把选修“高等数学”课程中不及格的成绩全部改为空值。

(5) 把低于总平均成绩的女同学成绩提高5%。 解：

(1) 建表：

USE JXGL GO

CREATE TABLE STUDENT(SNO CHAR(9) NOT NULL,

SNAME CHAR(8) NOT NULL, SEX CHAR(2))

GO

查询结果插入：

USE JXGL GO

INSERT INTO STUDENT(SNO,SNAME,SEX) SELECT SNO,SNAME,SEX FROM S

WHERE SNO IN (SELECT SNO

FROM SC

GROUP BY SNO HAVING MIN(GRADE)>80)

GO

18

(2) USE JXGL

GO

DELETE FROM SC

WHERE GRADE IS NULL

GO

(3) USE JXGL

GO DELETE

FROM SC

WHERE SNO IN(SELECT SNO FROM S

WHERE SNAME='张成民')

GO

(4) USE JXGL

GO

UPDATE SC

SET GRADE=NULL

WHERE GRADE<60 AND CNO IN(SELECT CNO FROM C

WHERE CNAME='高等数学')

GO

(5) USE JXGL

GO

UPDATE SC

SET GRADE=GRADE*1.05 WHERE SNO IN(SELECT SNO FROM S

WHERE SEX='F')

AND GRADE<(SELECT AVG(GRADE) FROM SC)

GO

5. 假设某“仓库管理”关系模型有下列五个关系模式： 零件PART(PNO,PNAME,COLOR,WEIGHT) 项目PROJECT(JNO,JNAME,JDATE)

供应商SUPPLIER(SNO,SNAME,SADDR) 供应P_P(JNO,PNO,TOTAL)

采购P_S(PNO,SNO,QUANTITY)

试用T-SQL DDL语句定义上述五个基本表，并说明主键和外键。 解：

CREATE TABLE PART

(PNO CHAR(6),PNAME CHAR(10) NOT NULL,COLOR CHAR(6),WEIGHT FLOAT(6),PRIMARY KEY(PNO));

CREATE TABLE PROJECT

19

(JNO CHAR(6),JNAME CHAR(12)NOT NULL,DATE DATE,PRIMARY KEY(JNO)); CREATE TABLE SUPPLIER

(SNO CHAR(8),SNAME CHAR(12)NOT NULL,SADDR VARCHAR(30),PRIMARY KEY(SNO));

CREATE TABLE P_P

(JNO CHAR(6),PNO CHAR(6),TOTAL INTEGER,PRIMARY KEY(JNO,PNO); FOREIGN KEY(JNO) REFERENCES PROJECT(JNO), FOREIGN KEY(PNO) REFERENCES PART(PNO)); CREATE TABLE P_S

(PNO CHAR(6),SNO CHAR(8),QUANTITY INTEGER,PRIMARY KEY(PNO,SNO) FOREIGN KEY(PNO) REFERENCES PART(PNO),

FOREIGN KEY(SNO) REFERENCES SUPPLIER(SNO)); 6．利用T-SQL语句声明一个游标，查询习题2数据库S表中所有男生的信息，并读取数据。

(1) 读取最后一条记录。 (2) 读取第一条记录。 (3) 读取第5条记录。

(4) 读取当前记录指针位置后第3条记录。

解：USE JXGL

GO

DECLARE S_Cursor SCROLL CURSOR FOR SELECT *

FROM S

WHERE SEX='M'

OPEN S_Cursor

FETCH LAST FROM S_Cursor FETCH PRIOR FROM S_Cursor FETCH ABSOLUTE 5 FROM S_Cursor CLOSE S_Cursor DEALLOCATE S_Cursor GO

习 题 7

1．名词解释：

视图 索引 聚集索引 唯一索引 答：

视图：是从基本表或其他视图中导出的表，它本身不独立存储在数据库中，也就是数据库中只存放视图的定义而不存放视图的数据。

索引：是对数据库表中一列或多列的值进行排序的一种结构，使用索引可快速访问数据库表中的特定信息。

聚集索引：该索引中键值的逻辑顺序决定了表中相应行的物理顺序。这种索引对查询非

20

CREATE FUNCTION C_MAX (@C_NAME CHAR(8)) RETURNS REAL AS

BEGIN

DECLARE @S_MAX REAL SELECT @S_MAX=MAX(GRADE)

FROM SC JOIN C ON SC.CNO=C.CNO AND C.CNAME=@C_NAME RETURN @S_MAX

END GO

10．在教学管理数据库中，创建用户定义函数SNO_INFO，根据输入的课程名称，输出选修该门课程的学生学号、姓名、性别、系部、成绩。

解：

USE JXGL GO

CREATE FUNCTION SNO_INFO (@C_NAME CHAR(8)) RETURNS TABLE AS

RETURN(SELECT S.SNO,SNAME,SEX,SDEPT,GRADE

FROM S JOIN SC ON S.SNO=SC.SNO JOIN C ON SC.CNO=C.CNO AND C.CNAME=@C_NAME)

GO

26

本文来源：https://www.bwwdw.com/article/lvz7.html