1、计算机三级数据库技术-88 (1)及答案解析(总分：100.00，做题时间：90 分钟)一、设计与应用题(总题数：3，分数：100.00)设有某商业单位需要建立商务数据库用以处理销售记账，它记录的数据包括：顾客姓名，所在单位及电话号码；商品名称，型号，产地及单价；某顾客购买某商品的数量及日期。假定无同名顾客，无同型号商品，电话公用，顾客可在不同日期买同一商品。（分数：40.00）(1).请画出该单位的商务 ER 图模型并优化。（分数：20.00）_(2).将 ER 模型换成关系模型并规范化到 3NF。 和一个顾客-商品购买关系，并且根据实体的属性可以画出相应 ER 图。（分数：20.00）_设
2、在采用 SQL Server 2008 数据库的图书馆应用系统中有三个基本表，表结构如下所示，请用 SQL 语句完成下列两个查询： （分数：40.00）(1).检索至少借了 5 本书的同学的借书证号、姓名、系名和借书数量。（分数：20.00）_(2).检索借书和王丽同学所借图书中的任意一本相同的学生姓名、系名、书名和借书日期。（分数：20.00）_1.在 SQL Server 2008 中，设有教师表(教师号， 姓名，所在部门号，职称)和部门表(部门号，部门名，高级职称人数)。请编写满足下列要求的后触发型触发器(设触发器名字为 tri_zc)。 每当在教师表中插入一名具有高级职称(“教授”或“
3、副教授”)的教师时，或者将非高级职称教师的职称更改为高级职称时，均修改部门表中相应部门的高级职称人数。(假设一次操作只插入或更改一名教师的职称) （分数：20.00）_计算机三级数据库技术-88 (1)答案解析(总分：100.00，做题时间：90 分钟)一、设计与应用题(总题数：3，分数：100.00)设有某商业单位需要建立商务数据库用以处理销售记账，它记录的数据包括：顾客姓名，所在单位及电话号码；商品名称，型号，产地及单价；某顾客购买某商品的数量及日期。假定无同名顾客，无同型号商品，电话公用，顾客可在不同日期买同一商品。（分数：40.00）(1).请画出该单位的商务 ER 图模型并优化。（分
4、数：20.00）_正确答案：()解析：ER 模型如下所示： (2).将 ER 模型换成关系模型并规范化到 3NF。 和一个顾客-商品购买关系，并且根据实体的属性可以画出相应 ER 图。（分数：20.00）_正确答案：()解析：将 ER 模型转换为关系模型，并规范到 3NF： 顾客(姓名，单位，电话号码)，主键：姓名 商品(型号，名称，单价，产地)，主键：型号 购买(姓名，型号，数量，日期)，主键：姓名+型号 解析 ER 模型转换为关系模式的规则 a把 ER 模型中的每一个实体集转换为同名的关系，实体集的属性就是关系的属性，实体集的码就是关系的码。 b把 ER 模型中的每一个联系转换成一个关系，
5、与该联系相连的各实体集的码以及联系的属性转换为关系的属性。关系的码根据下列情况确定： 若联系为 1:1，则每个是实体集码均是该关系的候选码。 若联系为 1:n，则关系的码为 n 端实体集的码。 若联系为 m:n，则关系的码为各实体集码的组合。 c合并具有相同码的关系。 根据规则，把一个 ER 模型转换为关系模式，一般经历下面两个步骤： 1)标识 ER 模型中的联系。 2)依次转换与每个联系项关联的实体集及联系。 本题中从 ER 模型转换成关系模型，由两个实体和一个关系分别可以转换成三个关系模式。设在采用 SQL Server 2008 数据库的图书馆应用系统中有三个基本表，表结构如下所示，请用
6、 SQL 语句完成下列两个查询： （分数：40.00）(1).检索至少借了 5 本书的同学的借书证号、姓名、系名和借书数量。（分数：20.00）_正确答案：()解析：SELECT LOANS.借书证号, 姓名, 系名, COUNT(*) AS 借书数量 FROM BORROWER, LOANS WHERE BORROWER.借书证号=LOANS.借书证号 GROUP BY LOANS.借书证号 HAVING COUNT(*)=5; 解析 采用两表联合查询。以两表“借书证号”为相等条件，在结果集中用GROUP BY 按照“借书证号”来分类，并且用 HAVING 关键字统计出符合条件的记录数。(2
7、).检索借书和王丽同学所借图书中的任意一本相同的学生姓名、系名、书名和借书日期。（分数：20.00）_正确答案：()解析：SELECT 姓名，系名，书名，借书日期 FROM BORROWER, LOANS, BOOKS WHERE BORROWER.借书证号=LOANS.借书证号 AND LOANS.图书登记号=BOOKS.图书登记号 AND 索书号 IN ( SELECT 索书号 FROM BORROWER, LOANS, BOOKS WHERE BORROW.借书证号=LOANS.借书证号 AND LOANS.图书馆登记号=BOOKS.图书登记号 AND 姓名=“王丽“ ) 解析 采用 I
8、N 关键字进行两表联合查询，在 BORROWER 和 LOANS 联合的结果集中查找满足第三个表指定的条件。1.在 SQL Server 2008 中，设有教师表(教师号， 姓名，所在部门号，职称)和部门表(部门号，部门名，高级职称人数)。请编写满足下列要求的后触发型触发器(设触发器名字为 tri_zc)。 每当在教师表中插入一名具有高级职称(“教授”或“副教授”)的教师时，或者将非高级职称教师的职称更改为高级职称时，均修改部门表中相应部门的高级职称人数。(假设一次操作只插入或更改一名教师的职称) （分数：20.00）_正确答案：()解析：CREATE TRIGGER tri_zc ON 教师
9、表 AFTER INSERT,UPDATE AS BEGIN DECLATEzc varchar(10),dept varchar(30) SELECT dept=所在部门号,2c=职称 FROM inserted IF zc =“教授“ or “副教授“ Update 部门表 SET 高级职称人数=高级职称人数+1 Where 部门号=dept End 解析 创建触发器的 SQL 语句为：CREATE TRIGGER，其语法格式为： CREATE TRIGGER 触发器名称 ON 表名|视图名 FOR | AFTER | INSTEAD OF INSERT , DELETE , UPDATE
10、 AS IF UPDATE(column) AND | OR UPDATE(column) SQL 语句 其中 AFTER 指定触发器为后触发型触发器，INSERT, UPDATE 和 DELETE 为指定引发触发器执行的操作。根据原题要求，insert 触发器会在 inserted 表中添加一条刚插入的记录，update 触发器会在更新数据后将更新前的数据保存在 deleted 表中，更新后的数据保存在 inserted 表中。在教师表中插入或者更新的时候，都会在 inserted 表中增加一条记录，所以只需在触发器查询 inserted 表中查询有没有“教授”或者“副教授”的记录，如果有，则触发修改相应部门的高级职称人数即可。
