全国计算机等级考试历年真题

全国计算机等级考试历年真题

08年四月

二、简答题

1、数据库应用系统功能设计包括哪两个方面？每个方面主要由哪联

设计步骤组成？（10分）

答案：数据库应用系统功能设计包括数据库事务设计和应用程序

设计。与数据库应用系统概念设计、逻辑设计、物理设计相对应，

应用程序（软件）设计分为总体设计、概要设计和详细设计，数据库

事务设计则由事务概要设计和事务详细设计组成

2、数据库系统处理死锁•般采用哪两类方法？简述每类方法的基本

原理（10分）

答案：解决死锁主要采用死锁预防和死锁检测与恢复两类方法。a.死

锁预防利用死锁预防协议，通过破坏死锁产生的必要条件来避免系统

进入死锁状态，防止死锁发生。b.死锁检测与恢复则是允许系统进

入死锁状态，才旦定期检查系统是否发生死锁。当发现系统发生死锁

后，采取相应的恢发机制使系统摆脱死锁状态.

3、请按要求解答卜列问题：

①在设计数据即应用系统时.需要对系统所需的存储空间进行规划。

规划时需要考虑多种因素，请列出其中的4种。（4分）

②设有商品的售系统，该系统每天最多产生I力条梅品徜化汜录.

用商品销售数据表保存销售记录.系统需要保存3年的销售记录.

每年按365大计算。商品销售数据我包含的属性及各属性占用的字

节分别为：肉品号（10个字节）、销售数量（8个字节）、价格（8个

字节）、金额（8个字节）、收银价编号（4个字节）、销售员编号（4

个字节）、销售时间（8个字节）.保存该衣的数据库管理系统的数

据存储单位是数据页，每页大小为8KB（共8192个字节）.该数据库

笆理系统规定数据页空间全部用丁保存销售数据.并“存储精售数

据时单条记录不允许跨数据页存放。请计算在数据昨中保存商品销售

数据表需要的数据页数H,要求写出计算过程。(6分)

答案：①1.单条数据的长度：2.每年、每天、每小时等数据所增长的

大小；3.备份和冗余备份空间：4.系统文件的大小：5.占用内存空间

的大小等等。

②单条商品销售记录占用字节：10+8+8+844+4+8=50字节。单数据

页可存放销售记录条数：163条

(163*50=8,150字节：164*50：8,120字节)。保存三年的销售总

记录数

：3*365*10,000=10,950,000条。需要的数据页数目

：67.178(67,178*163=10,950,014；67.177*163=10.949.851).

三、应用与设计题

1、设有高校选课系统，需要对学校的系信息、教师信息、课程信息、

学生信息、学生选课信息进行管理.己知系(DEPT)信息包括系编

号(DeptNO),系名称(DeptName),教师(Teacher)信息包拈教师

号(TNO)、教师名CTname),课程(Course)信息包括课程号(CNO)、

课程名(CName)、课程学分(Credit),学生(Student)信息包括学

号(SNO)、学生姓名(Sname)、学生性别(Sex)。

选课系统的管理规则如下：

I、个系可聘用多名教师.•个教师只受聘于个系：

【I、个系可有多名学生，个学生只属于个系;

川、名教师可讲授多门课程，门课程可被多个学生选修；

IV、•名学生可选修多门课程，•门课程可被多名学生选修；

V、学生选修完课程后，可获得相应课程的成绩。

针对以上描述，完成下列设计内容：

（I）构建选修课系统的ER图。（要求图中的实体集名用试卷中给出

的英文名，联系名用联系所关联的实体集名的苜字字母间用“一”

或“”连接，大小写不限）（10分）

（2）根据所构建的ER图，设计满足3NF的关系模式，并标出每个

关系模式的主码和外码。（要求关系模式名同实体集名或联系名，属

性名用试卷中给出的英文名，大小写不限）（10分）

现有如下两个关系模式：

Employees（Eid.Name.DeptNO）

Departments（DeptNO,DeptName.TotalNumber）

Employees关系模式描述了职工编号、姓名和所在部门编号：

Departments关系模式描述了部门编号、名称和职工总人数。

请按SQLServer所采用的Tansact-SQL语法格式编写实现具有功能

的后触发型触发器：每当在Employees表中插入一•行数据时，相应部

门的职工总人数就加I。（10分）

答案：createtriggersqltrionemployeesforinsert

as

declare@eidvarchar(II)declare@namevarehar(20)declare@

deptnovarchar(II)declareyjycursorfor

select*

frominsertedopenyjy

fetchnextfromyjyinto@uid,@name,@deptnowhile@@

fetchstatus=O

begin

updatedepartments

settotalnumber-totalnuniber-i-1

wheredcptno=@dcptno

fetchnextfromyjyinto@eid»@namc,@dcptnoend

closeyjydeallocateyjy

【解析】此题考直了后触发型触发器的Transact-SQL语句。其语法

格式为：

CREATETRIGGER触发器名称

ON|表名|视图名|

[WITHENCRYPTION]AS

SQL语句AFTER和FOR,指定触发器只有在引发的SQL语句中指

定的操作都一成功执行，并旦所有的约束检查也成功完成后，才执

行此触发器。即后触发型触发器。

08年9月

二、简答题（每题10分，共20分）

请使用蓝、黑色钢笔或网珠笔将答案写在答题纸的相应位置上，否则

无效。

（I）设有商场经营管理系统，系统中的部分数据表和视图的相关信息

如卜一所示

赛或艇访襄军MW

«Ti《有品标识.时值）・储傅柜台.长■,华馀.金我）10000财霞胸1崂

«n（悲丛标国时（弧商店标谀・息段■,思金累）1000啦T1

»T3（■品标识.月份.商店标识.型簟・,招金B）5000次庆T2

100次庆T3

3000次庆n

表中T4的数据源是T3表明T4中的数据是根据T1的数据汇总计算

而来的，并假定各个表的数据都得到了及时计算，现需对系统进行优

化，以提高与视图vl有关的查询效率，设有如下优化方案：

I为T1建立主索引

II为T2建立主索引

m为T3建立主索引

IV重写视图VI,将其数据源调整为T4,并为T4建立主索引

V重写视图VI,将其数据源调整为T2,并为T2建立主索引

请针对每一个优化方案，说明该优化方案对实现目标是否有效,

并简要说明原因.

（2）卜图为关系表Students（专业,姓名,学号,年龄），在对应的数

据文件中的物理存储结构小意图：

专：ik姓名。学号。年龄0

电子张家力，D-002^13

计茸机,苏永军♦、J-100^

自动化•邓秀娟•二05“23"

计算机•叶浩伟”L2W加

①写出"专业"属性上建立聚集索引的SQL语句,索引值按升序排序

（索引名为ZY_ind）°

②假设索引按稠密索引方式组织数据，画出在"专业"属性上建立聚

集索引后该表对应的索引文件和数据文件的物理存储结构示意图（以

"专业"为查找码，按查找为定义的首字母升自定义的

三、设计与应用题（40分）

请使用蓝、黑色钢笔或圆珠笔将答案写在答题纸的相应位置匕否则

无效。

（1）有商品表（商品号，商品名，分类，单价）,请编写个实现更改商

品单价的存储过程（存储过程名为pUpdate）,更改规则如下："电脑"

类商品降价10%，"电视"类商品降价6%,"冰箱"类商品降价3%,其

他商品不降价。以商品的分类作为输入参数，假设"分类"为字符串类

型，长度最多为6个汉字。如果商品女中没有用户指定的分类，则用

输记参数返IE字符申"指定的分类不存在"；如果用户指定的分类存在，

则用输出参数返回字符串"修改已成功"。（10分）

（2）现有某图书销售数据库,其关系表结构如下:

图书表（图书编号，图书名称，出版社编号，出版社名称，出版时间，

出版数量，版次）

图书销售表（图书编号,销售日期,销售数量,书店编号,读者编

号，读者姓名，读者电话）

书店表（书店编号，联系电话,所在城市编号,城市名称）

I.系统所涉及的数据存在如下约束

出版社可以出版多本图书，一木图书只能在一个出版社出版，在该系

统的记录的图书出版信息包括出版时间、版次及出版数量信息。

II.一个书店可以出售多本图M给多个读者，每位读者可以从多个

回占购买多本图书，本图书可以通过多个书店出售给读者，书店把

图书出售给读者后会在系统中记录售书日期和售书数量信息：

HL每个书店只能位于个城市,•个城市可以有多个书店。

①请根据以上信息画出合理的图/销售数据库的概念模型（用ER

图表示）。（8分）

②以图书销售衣为例说明原数据库设计的不合理之处。（4分）

③给出该数据库符合3NF要求的全部关系模式，并指出关系模式中

的全部主码和外码。（8分）

（3）事务Tl、T2和T3按如下调度方式并发地对数据项A,B,C进行

访问，假设A,B,C的初位分别为A=20,B=30,C=40o

①在事务T2刚完成提交后，数据库中A,B,C的值各是多少？（3

分）

②当事务T3结束后，给出该并发调度对应的FI志文件，并说明

此时A,B,C的值各是多少？（7分）

T1T2T3Q

Begin-trans(II)*1

Read(A)»

Begin-trans(T2)/

Read(B)**

Write

Conniw

Begln-trans(T3)^,

Read(C)^

Write

C:-C*2P

ConnniW

Write(C)^

rollback♦'

二、简答题答案

（1）答案：略,详见解析。

（2）答案：

①CREATECLUSTEREDINDEXZYindONStudents（专业）

②索引文件的物理存储结构示意图：

数据文件的物理存储结构示意图:

②图书销售表中，送者信息应该单独新建•张表，否则就会造成大量

的数据冗余。

③出版社表（出版社编号，出版社名称）主码：出版社编号

出版表（出版社编号，图竹编号，出版时间，出版数量，版次）

主码：（出版社编号，图书编号）

图书表（图书编号,图书名称）主码:图书编号

书店表（书店编号,联系电话,所在城市编号）主码：书店编号

城市表（所在城市编号,城市名称）主码：所在城市编号

读者表（读者编号，读者姓名，读者电话）主码：读书•编号

图书销售表（图书编号,读者编号,销售日期,销售数量，书店

编号）主码:（图书编号,读者编号）

（3）

答案：①A=30；B-20；C-40；

②日志记录：302080

A=30；B=20；C=40；

二、应用与设计题

1.分析：简答题解析

⑴解析：

1：试图不能建立索引,无效。

H：视图VI建立在T3匕T2的索引优化对VI没有影响。

III：可以在•定程度上提高VI的查询效率，但是从T3的每行记录

粒度为"日"查询到粒度"季"，大约每次查询涉及90行。而从T4的每

行记录粒度为"月"行询到粒度"季"，大约每次查询只涉及3行，显然

后者的修制效率更高。另外T2的访问频率相当高，若是VI建立在

T2上，显然会更加增加其查询频率,降低查询效率。

IV：为5种方案中最优方案。每次查泡涉及行数最少（3条），而H

T4的访问频率最低。

V：每次查询牵涉记录行数比方案HI要多，虽然T2查询频率较T3

少，但是总体性能不如方案H1.

<2）解析：本题考察的是聚集索引的创建及数据文件的物理存储

结构。

2.分析：三、设计与应用题解析

3）解析：存储过程的建立使用CREATEPROCEDURE,对存储

过程中变量的命名要用到@变量名，

CreateprocedurepUpdale

@flaschar（6）,

@jgasvarchar（20）output

As

Ifnotcxists（Sclect*from商品表where分类=@fl）

BEGIN

If脑"Update商品表set单价=单价*0.9where分类="电脑

Else

BEGIN

"电视")Update商品表set单价=单价*0.94where分类="电

视“

Else

BEGIN

IK@fl="冰箱")Update商品表set单价=单价*0.97where分类="冰

箱”

END

END

Scljg="修改」成功"

END

Else

Setj歹”指定的分类不存在"

(2)解析：本题考察的主要是数据库设计优化，关系数据模型转

换及规范化处理。一木图书只能在个出版社出版，即出版社与图书

是对多的关系，个书店可以出售多木图书给多个读者，每位读者

可以从多个书店购买多本图书,一本图书可通过多个书店出售读者,

即有图书，读者与书店都是多对多的关系，它们之间通过出售实体来

联系。图书捎售表中存在太多的冗余，应该化为更高的范式来消除不

必要的冗余。

(3)解析：本题考察的是事务的调度与I可滚。T1,T2,T3的整个调

度次序是先是TI把A加h10,此时A还是初位20,直到T3把A

写入，此时A的他为30.而T2只是读了B,B的值未发生变化；之

后的Bcgin-trans（T3）,T2时B进行了改写并写入，所以B的值发生

变化，但是TI虽然改变了C的位，但是却没有写入，事务在T2的

时候就已经提交，所以C的位未变。

09年3月

二、简答题（每题10分，共20分）

请使用蓝、黑色钢笔或阿珠笔将答案写在答题纸的相应位置上，否则

无效。

（1）有如下2个关系表

Emp（eid,ename.age,did.salary）,其各列含义为:职工号,

姓名，年龄，部门号，工资

Depl（did.dname,mgrid）,其各列含义为：部门号,部门名

称，部门经理职工号

写出条SQL语句，查询工资大于10000,只与他所在部门的

经理年龄相同的职工姓名。（6分）

（2）有学生表〔学好，姓名，年龄，性别，系名，专业名，班号），

设个系可有多个专业，每个［业可有多个班，各班班号不重复，-

个学生只在•个班学习，现经常需要按"系名"和"班号"进行查询，为

提高查询效率，需要为"系名"和"班号"两个列建立个非聚集索引，

建立此索弓I有下列两种方法：

方法1:索引列顺序为（系名，班号）

方法2:索引列顺序为（班号,系名）

①这两种方法哪个更合理？请简要说明原因。（4分）

②针对你认为合理的方法，写出创建该索引的SQL语句。（3分）

(3)考虑定义在事务集(T1,T2,T3)卜一的调度S1和S2,SI与S2

是否是冲突等价的？为什么？(7分)

S3S2/

TP3T3PTIPT2PT3一

read(P)read(Q)>♦砂

read(Q)♦>read(P)♦府

write(Q)*write(Q)・♦o

write(Q)“♦Jownte(Q)o

write(Q)“dwrite(P)♦J

Pwrite(P)♦♦wnte(Q)>

write(P)-uine(P)♦

read<P)♦lead(P)~

三、设计与应用题(40分)

请使用蓝、黑色钢笔或圆珠笔将答案写在答题纸的相应位置上，否则

无效。

(1)某汽车维修公司需建立•个汽车维修数据库，该数据库中需要

存储和管理下列信息：

车辆信息:车牌号，车型,发动机号,行驶里程，车辆所有人,

联系电话

维修项II：项目号，项目名称，维修费

汽车备件：备件号，备件名称，备件单价，库存数量

以上数据之间存在下列约束:

可以对个车辆进行多个维修项目，每个维修项目可用于多个车

辆，维修项II完成后要在数据库中记录维修时间

种备件可用于多个维修项目，每个维修项口最多只使用种备

件，但每种备件的数量可以是多个。

①根据以上需求构建该数据库的概念模型［画ER图）。（8分）

②假设车辆信息有如下约束

车牌号：标识属性,取值形式为:第1个字符是"京"，第2个字

符为"A"到"Z"的字母，第3到第7个字符均是"0"到"9"的数字。此列

采用普通编码定长字符型，非空：

车型：普通编码定长字符型,长度为6,默认值为"轿车";

发动机号：普遍编码定长字符型：长度为6,非空；

行驶里程：整型，取值大于等于0:

车辆所有人：普通编码定长字符型，长度为8,非空；

联系电话：普通编码定长字符型，长度为13,取值唯。

写出创建满足上述要求的车辆信息表的SQL语句。（注：表名、

列名均用题中给出的中文名，SQL语句中的大、小写字母均可。）

（8分）

（2）现有关系模式:

教师授课（教师号，姓名，职称，课程号，课程名，学分，教科

书名）

其函数依赖集为：

｛教师号一姓名，教师号一职称，课程号一课程名，课程号一学

分，课程号一教科书名｝

①指出这个关系模式的主码。（2分）

②这个关系模式是第儿范式，为什么？（4分）

③将其分解为满足3NF要求的关系模式（分解后的关系模式名

自定）（6分）

(3)为在某种程度上保证某数据库系统可靠地运行，在SQLServer

2000环境中，对其制定有如下三种备份策略。假设对此数据库进行

•次完全备份需要4小时，差异备份需要2小时，R志备份需要1小

时。设所有备份都是从凌晨1:00开始。

策略I:每周周日进行•次完全备份，每周周至周六每天进行

次日志备份：

策略2:每周周日进行次完全备份，每周周一至周六每天进行

次差异备份：

策略3:每天进行次完全备份。

①假设需要保留一年的备份数据，比较三种备份策略所占用的

空间。

②比较每种备份策略的备份和恢复速度。

二、简答题答案

答案：

(1)selecta.*fromEmpa,Deptb,Empcwherea.did=b.didand

b.mgrid-c.cidanda.salary>10000anda.agc=c.agc

(2)①方法I更加合理，理由如下：更加有利于减少索引层次，提高

查询效率：更加符合使用习惯：更加便于(系、班)进行统计。

②createindexindex1on学生表(系名，班号)

(3)不是等价冲突，因为T1的write(Q)和T2的write(Q)是冲

突的，不可交换。三、设计与应用题答案

答案：

(1)①概念模型如下:

②Createtable车辆信息(

车牌号char(7)NotNULLCHECK车牌号like("京[A-Z][0-9][0-9]

[0-9][0-9]")

车型char(6)default"轿车"，

发动机号char(6)NOTNULL,

行驶里程intCHECK(行驶里程＞=0),

车辆所有人char(8)NOTNULL,

联系电话char(13)UNIQUE,

PRIVATEKEY(车牌号)

)

（2）①主码为：（教师号、课程号）

②笫1范式,因为存在部分依赖。

③分布方法为：

教师表（教师号，姓名，职称）

课程表（课程号，课程名，学分,教科书名）

教师授课表（教师号，课程号）

（3）①占用空间量从大到小排序为：策略3,策略2,策略1

②备份速度从快到慢排序为：策略1,策略2,策略3

恢复速度从快到慢排序为：策略3,策略2,策略I

三、答案解析

一、选择题

1.分析：技术可行性是根据用户提出的系统功能、性能及实现系

统的各项的约束条件，对系统软件、系统硬件、技术方案做出评

估和选择建议，它属于规划与分析阶段的可行性分析。

二、应用与设计题

1.分析：简答题解析

解析：冲突等价调度是指一个调度S,通过调换S中的不

冲突操作次序所得到的新调度称为S的冲突等价调度。不冲突操

作有两类:一是对同一数据均为读操作,即Ri(x),Rj(x);对不同

数据对象的•组操作中可以有写的操作，即Ri(x),Wj(y)或

Wi(x)町(y).显然TI和T2对数据对象Q进行了相同的写操作,所

以是冲突的，所以S1和S2不是冲突等价的.

2.分析：设计与应用题解析

解析：⑴由题一个车辆可进行多个项目的维修，一个维修可

用于多车辆，可知实体车辆信息与维修项目是多对多的关系；

种配件可用于多个维修项目，但•个维修项目只能最多用•种配

件,可知配件与维修项目是一对多的关系。车牌号的取值形式限

定可用CHECK来表示。

(2)由给定的依赖关系可知，教师号和课程号能够决定所有的属

性，即教师号和课程号是主码。由于每一个非主属性都部分的依

赖于码，所以不满足2NF,所以它为1NF

(3)完全备份是备份全部选中的文件夹，并不依赖于文件的存档

属性来决定备份I哪写文件；

差异备份是针对完全备份的，它是备份上­•次完全备份后发生变

化的所方文件，恢复时需要完全数据库备份和最近的差异数据库

备份

H志备份需要有完全数据库备份；备份自上次backuplog语句到

当前H志尾之间的所有数据库修改；截断事务H志这样备份的数

据量少，只备份上次备份到现在的修改：恢复时需要完全数据库

备份和所有的日志备份

09年9月

三、设计与应用题(30分)

请使用蓝、黑色钢笔或圆珠笔将答案写在:答题纸的相应位置上，否则

无效。

<1)石两张关系表：

Students(SID.SName.Sex.Dept)

Prizing(SID.PName,Type.PDate)

Students及描述的是学生基木信息,Prizing我描述的是学生获奖信息。

①若在应用程序中经常使用如下查询语句：

SELECTS.SID,SName,PNamc,TypeFROMStudentsASSJOIN

PrizingASPONS.SID=P.SID

请问能否使用聚集文件提高此直向的性能？并给出原因。

②为了提高查询性能，数据库管理员在Students表的SName列上建

立了个非聚集索引SNameJnd.如果应用程序使用如下语句查询数

据：

SELECT*FROMStudentsWHERESNamelike'%华'

请问SNameind索引是否能够提高语句的查询速度,并给出原因。

(2)某公司需建立产品信.息数据库，经调查其业务要求如下：

I、种产品可以使用多个不同种类的部件，也可以使用多个不同种

类的零件；一种部件至少被用在一种产品中；一种部件可以由多个不

同种类的零件组成；一种零件至少被用在种产品或•种部件中；

II、对于种具体的部件，要记录这种部件使用的各种零件的数疥：

III、对于种具体的产品.要记录这种产品使用的各种部件数量和各

种零件数量，但部件所使用的零件数量不计入该种产品的零件数量：

IV、一个供应商可供应多种零件，但一种零件只能由一个供应商供应。

根据以上业务要求，某建模人员构建了如下ER图：

NUP团山的7•侏M山枇之做E7:伏曲左以1号叶左/:不企础》〃卜

①请)新构建合理的ER图,使之符合业务要求，且信息完整。

②根据重构的ER图给出符合3NF的关系模式，并标出每个关系

模式的主码和外码。

(3)在SQLServer2000中，石教师表Teachers(TeacherlD,Name»

LeaderlD),其中TeacherlD是主码，类型是长度为4的普通编码定长

字符出，旦每位是0-9的数字字符；Name的类型是长度为10的普通

编码可变长字符串；LeaderlD是每个教师的领导的TeacherlD。

①写出创建Teachers及的SQL语句，要求语句中包含所有的约束。

②现要查询TcachcrID为"1234"的教师的领导的领导的TcachcrID,

请给出相应的SQL语句，要求只使用条SQL语句实现，」L此语句

中不允许包含了查询。

三、设计与应用题答案

（1）答案：①可以用聚集文件提高此查询性能。因为聚集文件本身

存储不同类型表的联系，能够减少表间的连接操作，提高查询性能。

②此索引不能提高该语句的查询速度。因为该索引是建立在SNAME

上的非聚集索引，以SNAME首字母排序，而查询语句却要求查询名

字最后一个是"华"的SNAME,还是需要全部扫描students表,因此

不能提高该语句的看询速度。

（2）答案：①重构ER图如下:

②符合3NF的关系模式：（带卜划线的为主:码属件）

产品（产品号（PK）,产品名称，型号，装配图号）

部件（部件号（PK）,部件名称）

零件（零件号（PK）,零件名称，供应商号（FK））

产品_部件（产品号（FK）,部件号（FK）,部件数量）

产品—零件（产品号（FK）,零件号（FK）,零件数量）

部件零件（部件号（FK）,零件号（FK）,零件数量）

供应商（供应商号（PK）,供应商名称，联系电话）

(3)答案：

©createtableTeachers(

TeacherlDchar(4)check(TcachcrlDlike"[0-9][0-9][0-9]

Namevarcharf10),

LeaderlDchar(4),

primarykeyTeacherlD,

foreignkeyLeaderlDreferencesTeachcrs(TcachcrlD)

)

②selectT2.LeaderIDfromTeachersasTljoinTeachersasT2on

T1.LeaderlD=T2.TeacherlDwhereT1.TeacherID=,1234,

2.分析：三、设计与应用题解析

（1）解析：本题考查的是优化查询速度的方法等。聚集文件木

身存储不同类型表的联系，能够减少表间的连接操作，提高查询

性能。而非聚集索引，对于以SWME首字母排序时有效。

（2）解析：本题考查的是ER图和关系模式的知识。实体设计

中存在明显的不合理之处，比如供应商应该是一个独立的实体。

此外，对于产品和零件I'L接的联系未能正确表现出来。•种产品

可以使用多个不同种类的部件，也可以使用多个不同种类的零件。

说明产品与零件是直接有关系的，必须用个实体来把它们之间

的关系表示出来，否则，设计出来的关系模式存在有大量的数据

冗余。

（3）解析：创建表格使用CREATE语句。对于查询表中领导的

领导信息可以通过对该表进行自我连接，1231的条件约束可以通

过在WHERE广句中来实现。

10年3月

三、设计与应用题（共30分）

1.设有某简化的网上购物系统，其ER图如卜.:

该系统数据之间存在卜列约束:

I.个客户（编号唯一）可以拥有多个订单,每个订单仅属于个客

户；

II.一个订单（编号唯）可以包含多个订购细目，每个订购细目只属

于一个订单：

in.一种商品可以出现在多个订购细目中，一个订购细目只包含•利

商品；

IV.个商品类别可以包含多种商品，种商品只属于个商品类别。

①请根据以上约束，标出ER图中各实体集之间联系的基数。(2分)

②请将ER图转换成符合3NF的关系模式，并给出各关系模式的土码

和外码。(4分)

③请修改所设计的关系模式使之支持下列功能(仅列出发生修改的关

系模式)。(4分)

I.使客户能方便了储每个订购细目的处理状态和到货时间；

H.使客户能方便查询商品供应商的编号、名称、所在城市、联系电

话和信誉。(限定一个供应商可以供应多种商品，一种商品只选自一

个供应商)

答案：②uscrInfb(uscrlD,username,uscrTcl,uscrEmail,ordcr【D)主码

userID,外码orderlD:

orderInfb(orderID,orderDate,itemID)主码orderlD.外码itemID：

itemInfb(ilemID,itemNum,itemSum)主码itemID；

commodityInfb(comID,comName,comPrice,comDate,typelD,itemID)

主码comlD,外码typelD,itemlD：

comType(type【D,typeName,com【D)主:码typelD.外码comlD；

③在订购细目中添加发货时间和到货时间;添加供应商关系模式，修

改商品关系模式

businessInfb(businessID,businessName,businessCity,businessTel,busines

sCredil)主码

businessID

comniodityInfb(comID,comNamc,coniPricc,comDatc,typeID,itcmID,busi

nesslD)主

码comID.外码typeID,itemID,businessID；

2.某学校的学籍管理系统实现了对学生学籍信息的管理，其中学生

表结构为：学生表（学号

，姓名,性别,系号,是否有学籍），此表中除学号列有唯一索引外,

其它列均无索引。

①设学籍管理系统中经常执行如下形式的操作：

SELECT*FROM学生表

WHERE姓名=，张三，AND系号=*10'AND是否有学籍=，有'

系统运行中发现这类操作的效率比较低，系统工程师建议：由于查询

条件中使用了“系号”、

“姓名”和“是否有学籍”三列，建议在这三个列上分别建立索引，

以提高查询效率.

请问其建议建立的三个索弓I是否都能提高查询效率?请简要说明原

因。（6分）

②设学生表中有下列数据：（见下表）

学号姓名性别系号是否有学籍

2008337工二;•;'二另一有

生」-

20083381席彬彬10冒

20JS339马宏伟Ka21020100襟

2008556王斌男20有

2008558张伟男20.有丁五代P西

2008559丁二女20兆ucityc「

并“己经在学生表的姓名列上建立了个非聚集索弓1（按姓名拼音升

序排序）。请给出查找码“姓名”对应的稠密索引示意图。（4分）

答案：①不能。使用索引是需要付出代价的，增加了系统维护时间和

占用了系统时间。学籍、系

号这两个字段重复值比较多，因此在这两个字段上添加索引不会显著

的增加杳询速度，减少用户

的响应时间。相反，因为需要占用空间，反而会降低数据库的整体性

能。

3.在SQLServer2000中,设有教师表（教帅号,姓名，所在部门号,

职称）和部门表（部门号

，部门名，高级职称人数）。请编写满足下列要求的后触发型触发器（设

触发器名字为lri_zc）。

（10分）

每当在教师表中插入一名具有高级职称（“教授”或“副教授”）的教

师时，或者将洋高级职称教师的职称更改为高级职称时，均修改部门

表中相应部门的高级职称人数。（假设•次操作只插入或更改名教

师的职称）

答案：CREATETRIGGERtric_zc

ON教师表

AFTERINSERT,UPDATE

AS

BEGIN

DECLARE@zcvarchar(10),@deptvarchar(30)

SET@zc=(SELECT职称FROMinserted)

SET@dept-(SELECT所在部门号FROMinserted)

IF@zc='教授'OR'副教授'

UPDATE部门表

SET高级职称人数-高级职称人数+1

WHERE部门号-@depl

END

10年9月

三、设计与应用题(共30分)

（1）已知某教务管理系统有下列关系模式（带下划线的属性是

主码）:

系（系号,系名）

学生（系号，姓名，性别，入学FI期，正常毕业日期，所在系号）

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

选课（学号，课程号，成绩）

教师（教师号，姓名,职称，所在系号）

①请根据给出的关系模式画出该系统的ER图，图中忽略实体集

的属性，但如果实体集之间的联系有属性则需要给出联系的属性。（6

分）

②随着教学改革的需要，该系统需要满足下列需求：

I.需为课程增加先修课信息（门课程可有多门先修课程）：

IL•门课程可由多名教师讲授，一名教师可以讲授多门课程。

请根据以上需求修改关系模式，仅列出有改动的关系模式，并使

每个关系模式满足3NF.（4分）

（2）在SQLServer2000的某数据库中右如下两张关系表：

学生表（学号，姓名，性别，系号），学号为主码

系表（系号，系名，系简称）.系号为主码

①在数据库中执行如下T-SQL代码：

DECLARE@DePtIDvarchar（10）

DECLARE@cntint

set@cnt=O

DECLAREcursor1cursorFORSELEET系号FROM系表

WHERE系名LIKE%电％，

OPENcursorl

FETCHNEXTFROMcursorlINTO@DePtID

WHILE@@FETCHSTATUS-O

BEGIN

DECLARE@tcmpentint

SELECT@temp_cnt=COUNT（*）FROM学生表WHERE系号

=@DeptID

set@Cnt=@cnt+@lempent

FETCHNEXTFROMcursor1INTOODePtiD

END

CLOSEcursor1

DEALLOCATEcursor1

SELECT@cnt

执行过程中发现速度比较慢，为了解决性能问题，需在功能不变

的情况下，将此T-SQL代码改为一条SQL语句，请写出此SQL语

句（语句中不能含有子查询）并说明为什么此种修改可以提高性能。

（5分）

②设在学生表的（姓名，系号）列上建有一•个复合索引,该索引

是否有助于提高卜冽两个语句的查询效率？并说明原因。（5分）

SELECT*FROM学生表WHERE系号=T;

SELEET*FRoM学生表WHERE姓名=，张三、

（3）在SQLserver2000中,设有图书表（书号，书名，类别,

单价）和图书卬刷表（书号，期，印刷数量，印次），每个书

号代表种图书。书号、书名和类别的数据类型均为varehar（20）,

单价、印刷数量和印次的数据类型均为int,卬刷n期的数据类型大

smalldatetime.现需查询指定类别的图书在指定年份后(含指定年份)

累计印刷总数量。请写出实现该功能的标量函数。(10分)

答案

①

②修改“课程”关系模式为：课程(课程号，课程名，学分，开课系号，教师号，先修课号)，主码为

“课程号。

新增“授课”关系模式：授课(教师号，课程号)，主码为“教师号”、“课程号。

①SELECTCOUNT(*)FROM学生表WHERE系名LIKE电%，

②第一句不能，笫二句能，系名的重良率太高，姓名的重复率相当低C

3.

CREATEFUNCTIONfun一定义函数名为fun

(@classVARCHAR(20),©orderyearCHAR(4))一声明参数

RETURNSINT-声明返回值类型

AS

BEGIN

--定义一个SMALLDATN1ME类型的中间变量,用于存放转换后的年份

DECLARE@lcmSMALLDATATIME

••定义一个INT类型的变量，用于存放卬刷总书目

DECLARE@cntINT

一把传进来的CHAR(4)类型的年份转换成SMALLDATATIME类型，并存入中间变量

SET@iem=CONVERT(SMALLDATETIME,@ordcTyear)

-SQL

SELECT@cnt=SUM(印刷数目)

FROM图书表JOIN图书卬刷表

ON图书表.书号=图书卬刷表.书号

WHERE类别=@classAND日期＞=@tem

RETURN@cnt-返回把印刷总数量

END

11年3月

三、设计与应用题（（D题20分，（2）题10分，共30分。）

1.已知某教务管理系统的设计人员在需求分析阶段收集到卜列原始

数据表格：

答案：（I）①教师（教师号.教师名，职称名，上级领导教师号）

职称（职称号，工资）

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

教材（教材号，教材名，出版社名，课程号）

②ER图

2.在SQLServer2000中»设某数据库中有商品表（商品号，商品名,

进货价格），商品号为主码；销售表（商品号，销售时间，销售数量，

销售价格，本次利润），商品号和销售时间为主码，销售价格为本次

销售商品的单价。现要求每当在销售表中插入前4列数据时（假设•次

只插入•行数据），系统自动计算木次销售产生的利润，并将该利润

赋给销售表的第5列“本次利润:请编写实现上述功能的后触发型触

发器代码。（10分）

答案：CREATETRIGGERupdatc_salc

ON销售衣FORUPDATE

FOREACHROW

ASBEGIN

UPDATETablesaleSET木次利润=销售价格-(SELECT进货价格

FROM产品表)

END

CREATETRIGGERInsertsale

ON销售发FORINSERT

FOREACHROW

ASBEGIN

INSERTINTO销伐表VALUES(new.商品号,new.销售时间，new.销售

数量,new.销售价格)

END

3.在进行某学校教务管理系统的数据库设计时，数据库设计人员设

计了如下儿个关系模式：

系(系号，系名)，系号为主码

学生(学号，姓名，所在系号)，学号为主码

课程(课程号，课程名，开课系号)，课程号为主码

选课(学号，课程号，选课时间)，学号和课程号为主码

开发人员在将关系模式实施到SQLServer2000的“教务”数据库时，使

用了如下表结构定义语句：

CREATETABLE系(系号varchar(10)NOTNULL,系名varchar(lOO))

CREATETABLE学生(学号varchar(50)NOTNULL.姓名varchar(50),

所在系号varchar(10))

CREATETABLE课程(课程号varchar(50)NOTNULL,课程名

varchar(100),开课系varchar(10))

CREATETABLE选课（学号varchar（50）NOTNULL,课程号

varchar（50）NOTNULL,选datetime）

在执行如下查制语句时发现执行效率很低：

SELECT*FROM选课JOIN学生ON学生.学号=选课.学号

JOIN系ON系.系号=学生.所在系号

JOIN课程ON课程.课程号=选课.课程号

WHERE系.系号一012

ANDconvcrt（varchar（10）.选课时间,120）＞=2010-01-01

①在杏找原因时发现建表语句有问题。请指出问题并说明该问题是否

会影响此查询语句的执行效率。（4分）

②设已在“选课”表的“选课时间”列及“学生”表的“所在系号”列上建立

了索引。请问这两个索引是否能够提高该告制语句的执行效率?如果

不能，请说明原因。（4分）

三、设计与应用题（（1）题2（）分，（2）题10分,共30分。）

1.已知某教务管理系统的设计人员在需求分析阶段收集到下列原始数据表格：

答案：（1）①教师（教师号，教师名，职称名，上级领导教师号）

职称（职称号，工资）

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

教材（教材号，教材名，出版社名，课程号）

②ER图

2.在SQLServer2000中，设某数据库中有商品表（商品号，商品名，进货价格），

商品号为主码：销售表（商品号，销售时间，销售数量，销售价格，本次利润），

商品号和销售时间为主码，销售价格为本次销售商品的单价。现要求每当在销售

表中插入前4列数据时（假设一次只插入•行数据），系统自动计算本次销售产生然后针

对各个数据的利泗，并将该利涧赋给销售表的第5列“本次利润”。请编写实现上述功

能的

后触发型触发揣代码.(10分)

答案：CREATETRIGGERupdatesale

ON销售表FORUPDATE

FOREACHROW

ASBEG1N

UPDATETable_saleSET