1、计算机三级数据库技术-51 及答案解析(总分：100.00，做题时间：90 分钟)一、设计与应用题(总题数：3，分数：100.00)已知某教务管理系统的设计人员在需求分析阶段收集到下列原始数据表格： （分数：40.00）(1).请根据原始数据表及业务规则，给出该系统的关系模式，保证每个关系模式满足 3NF，并说明每个关系模式的主码和外码。（分数：20.00）_(2).画出该系统的 ER图，ER 图中需给出每个实体集的属性，主码属性用下划线标识。（分数：20.00）_1.在 SQL Server 2008中，设某数据库中有商品表(商品号，商品名，进货价格)，商品号为主码；销售表(商品号，销售时间
2、，销售数量，销售价格，本次利润)，商品号和销售时间为主码，销售价格为本次销售商品的单价。现要求每当在销售表中插入前 4列数据时(假设一次只插入一行数据)，系统自动计算本次销售产生的利润，并将该利润赋给销售表的第 5列“本次利润”。请编写实现上述功能的后触发型触发器代码。 （分数：20.00）_在进行某学校教务管理系统的数据库设计时，数据库设计人员设计了如下几个关系模式： 系(系号，系名)，系号为主码 学生(学号，姓名，所在系号)，学号为主码 课程(课程号，课程名，开课系号)，课程号为主码 选课(学号，课程号，选课时间)，学号和课程号为主码 开发人员在将关系模式实施到 SQL Server 20
3、08的“教务”数据库时，使用了如下表结构定义语句： CREATE TABLE 系 ( 系号 varchar(10) NOT NULL, 系名 varchar(100) ) CREATE TABLE 学生 ( 学号 varchar(50) NOT NULL, 姓名 varchar(50), 所在系号 varchar(10) ) CREATE TABLE课程 ( 课程号 varchar(50) NOT NULL, 课程名 varchar(100), 开课系号 varchar(10) ) CREATE TABLE 选课 ( 学号 varchar(50) NOT NULL, 课程号 varchar(5
4、0) NOT NULL, 选课时间 datetime ) 在执行如下查询语句时发现执行效率很低： SELECT * FROM 选课 JOIN 学生 ON 学生.学号 = 选课.学号 JOIN 系 ON 系.系号 = 学生.所在系号 JOIN 课程 ON 课程.课程号 = 选课.课程号 WHERE 系.系号=“012“ AND convert(varchar(10), 选课时间, 120) =“2010-01-01“（分数：40.00）(1).在查找原因时发现建表语句有问题。请指出问题并说明该问题是否会影响此查询语句的执行效率。（分数：20.00）_(2).设已在“选课”表的“选课时间”列及“学
5、生”表的“所在系号”列上建立了索引。请问这两个索引是否能够提高该查询语句的执行效率? 如果不能，请说明原因。（分数：20.00）_计算机三级数据库技术-51 答案解析(总分：100.00，做题时间：90 分钟)一、设计与应用题(总题数：3，分数：100.00)已知某教务管理系统的设计人员在需求分析阶段收集到下列原始数据表格： （分数：40.00）(1).请根据原始数据表及业务规则，给出该系统的关系模式，保证每个关系模式满足 3NF，并说明每个关系模式的主码和外码。（分数：20.00）_正确答案：()解析：教师(教师号，教师名，上级领导教师号，职称) 主码：教师号；外码：上级领导教师号、职称 职
6、称(职称，工资) 主码：职称；外码：无 课程(课程号，课程名，学分，教材号，任课教师号) 主码：课程号；外码：教材号，任课教师号 教材(教材号，教材名，出版社) 主码：教材号；外码：无 解析 关系的描述称为关系模式，它可以形式化地表示为 R(U,D,DOM,F)。要想使转换生成的关系模式满足 3NF，则必须满足关系模式中每一个非主属性既不部分依赖于码也不传递依赖于码。根据题目中的表中的数据和业务系统的规则可知，共有四个实体存在，分别为教师、课程、教材、职称，职称作为实体而不是教师的属性是因为职称与工资挂钩，考虑到其有进一步描述的特性，所以把职称作为一个关系而不是教师的一个属性，而且教师号、职称
7、、工资之间存在传递依赖，不满足 3NF。(2).画出该系统的 ER图，ER 图中需给出每个实体集的属性，主码属性用下划线标识。（分数：20.00）_正确答案：()解析：解析 E-R 图也称实体-联系图，提供了表示实体类型、属性和联系的方法，用来描述现实世界的概念模型。为了简化 E-R图的处置，现实世界的事物能作为属性对待的，尽量作为属性对待。实体与属性的划分给出如下两条规则：(1)作为“属性”，不能再具有需要描述的性质，“属性”必须是不可分的数据项，不能包含其它属性。(2)“属性”不能与其它实体有联系，即 E-R图中所表示的联系是实体之间的联系。题目中教师与课程有讲授关系，课程和教材有使用关系
8、，教师与职称有聘用关系。1.在 SQL Server 2008中，设某数据库中有商品表(商品号，商品名，进货价格)，商品号为主码；销售表(商品号，销售时间，销售数量，销售价格，本次利润)，商品号和销售时间为主码，销售价格为本次销售商品的单价。现要求每当在销售表中插入前 4列数据时(假设一次只插入一行数据)，系统自动计算本次销售产生的利润，并将该利润赋给销售表的第 5列“本次利润”。请编写实现上述功能的后触发型触发器代码。 （分数：20.00）_正确答案：()解析：CREATE TRIGGER calcu_product AFTER INSERT ON 销售表 FOR EACH ROW AS B
9、EGIN DECLARE PurchasePrise float /*对应商品的进价的参数*/ SELECT PurchasePrise=进货价格 FROM 商品表 WHERE 商品号=new.商品号 UPDATE 销售表 SET 本次利润=new.销售数量*(new.销售价格-PurchasePrise) WHERE 商品号=new.商品号 AND 销售时间=new.销售时间 /*因为是行级触发器，所以可以使用更新后的新值，用 new*/ END 解析 触发器是用户定义在关系表上的一类由事件驱动的特殊过程。一旦定义，任何用户对表的增、删、改操作均由服务器自动激活相应的触发器，在 DBMS核心
10、层进行集中的完整性控制。触发器类似于约束，但比约束更加灵活，可以实施比 FOREIGN KEY约束、CHECK 约束更为复杂的检查和操作，具有更精细和强大的数据控制能力。 触发器的创建格式： CREATE TRIGGER Schema-name. trigger-name ONtable|view FOR|AFTER|INSTEAD OF INSERT,UPDATE,DELETE ASSql-statement ; 对应于本题中，触发事件为在销售表中插入前四列，条件为无条件执行，触发体动作为计算本次利润并插入表中。在进行某学校教务管理系统的数据库设计时，数据库设计人员设计了如下几个关系模式：
11、系(系号，系名)，系号为主码 学生(学号，姓名，所在系号)，学号为主码 课程(课程号，课程名，开课系号)，课程号为主码 选课(学号，课程号，选课时间)，学号和课程号为主码 开发人员在将关系模式实施到 SQL Server 2008的“教务”数据库时，使用了如下表结构定义语句： CREATE TABLE 系 ( 系号 varchar(10) NOT NULL, 系名 varchar(100) ) CREATE TABLE 学生 ( 学号 varchar(50) NOT NULL, 姓名 varchar(50), 所在系号 varchar(10) ) CREATE TABLE课程 ( 课程号 va
12、rchar(50) NOT NULL, 课程名 varchar(100), 开课系号 varchar(10) ) CREATE TABLE 选课 ( 学号 varchar(50) NOT NULL, 课程号 varchar(50) NOT NULL, 选课时间 datetime ) 在执行如下查询语句时发现执行效率很低： SELECT * FROM 选课 JOIN 学生 ON 学生.学号 = 选课.学号 JOIN 系 ON 系.系号 = 学生.所在系号 JOIN 课程 ON 课程.课程号 = 选课.课程号 WHERE 系.系号=“012“ AND convert(varchar(10), 选课
13、时间, 120) =“2010-01-01“（分数：40.00）(1).在查找原因时发现建表语句有问题。请指出问题并说明该问题是否会影响此查询语句的执行效率。（分数：20.00）_正确答案：()解析：建表时没有设置主键，也没有说明外键，但不会影响此查询语句的执行效率。解析 本题中查询语句的功能是得到 12系全体学生在 2010年 1月 1日后的选课情况的汇总表。在每个数据表的定义时都必须严格定义表中的完整性约束条件，包括主键的设置，否则之后会出现主键有相同值的情况，破坏了数据的完整性。(2).设已在“选课”表的“选课时间”列及“学生”表的“所在系号”列上建立了索引。请问这两个索引是否能够提高该
14、查询语句的执行效率? 如果不能，请说明原因。（分数：20.00）_正确答案：()解析：“选课”表的“选课时间”可以建立索引，从而提高查询效率，而“学生”表的“所在系号”建立索引不会提高查询效率。索引的意义就是将记录按目标关键字顺序排列，这样查找某个目标关键字的对应值的位置就缩小了查找范围。“选课时间”的重复率低，所以可以作为索引，而学生“所在系号”的重复率太高，则不会提高查询效率。解析 “选课”表的“选课时间”列上建立了索引，从而能够提高执行效率。经常出现在 Where子句中的字段，特别是大表的字段，应该建立索引。索引的作用就类似于书的目录，即会按照章节的顺序排列。因此如果在一本数百页的书里面查找某个章节位置的时候，就可以只扫描书的目录。扫描的范围缩了 n倍，查询的效率自然就会提高。另外，在 SQL Server内存够用的情况下，索引会被放到内存中，在内存中查找自然又会提高效率，所以必须合理利用索引。
