三、设计与应用题(共30分)
1.设有某商业单位需要建立商务数据库用以处理销售记账,它记录的数据包括:顾客姓名,所在单位及电话号码;商品名称,型号,产地及单价;某顾客购买某商品的数量及日期。假定无同名顾客,无同型号商品,由话公用腼客可存不同日期买同一商品。
(1)请画出该单位的商务ER图模型并优化。(6分)
(2)将ER模型换成关系模型并规范化到3NF。(4分)
2.设在采用SQLServer 2008数据库的图书馆应用系统中有三个基本表,表结构如下所示,请用SQL语句完成下列两个查询:
BORROWER:
借书证号姓名系名班级
12011106蒋辉文计算机系12-1
12011107王丽计算机系12-1
12012113范园园信息系12-2
LOANS:
借书证号图书馆登记号借书日期
1201 1 106 T0010012012.01.02
120121 13 T0010262013.02.O6
BOOKS:
索书号书名作者图书登记号出版社价格
TP311.1数据库系统李明T001001科学19.00
TP311.2二级C语言王珊T001026人民32.00
(1)检索至少借了5本书的同学的借书证号、姓名、系名和借书数量。(5分)
(2)检索借书和王丽同学所借图书中的任意一本相同的学生姓名、系名、书名和借书日期。(5分)
3.在SQL Server 2008中,设有教师表(教师号,姓名,所在部门号,职称)和部门表(部门号,部门名,高级职称人数)。请编写满足下列要求的后触发型触发器(设触发器名字为tri_zc)。
每当在教师表中插入一名具有高级职称(“教授”或“副教授”)的教师时,或者将非高级职称教师的职称更改为高级职称时,均修改部门表中相应部门的高级职称人数。(假设一次操作只插入或更改一名教师的职称)(10分)
三、设计与应用题
1.【解题思路】
(1)ER图的画法
建立相应的ER图的过程如下:
第一,确定实体类型。本题有两个实体类型,即顾客实体和商品实体。
第二,确定联系类型。该题中只有顾客一商品模型。
第三,把实体类型和联系类型组合成ER图。
第四,确定实体类型和联系类型的属性。
顾客实体集属性:姓名、单位、电话号码
商品实体集属性:型号、名称、产地、单价由顾客和商品两个实体和一个顾客一商品购买关系,并且根据实体的属性可以画出相应ER图。
(2)ER模型转换为关系模式的规则
①把ER模型中的每一个实体集转换为同名的关系,实体集的属性就是关系的属性,实体集的码就是关系的码。
②把ER模型中的每一个联系转换成一个关系,与该联系相连的各实体集的码以及联系的属性转换为关系的属性。关系的码根据下列情况确定:
若联系为1:1,则每个是实体集码均是该关系的候选码。
若联系为1:n,则关系的码为n端实体集的码。
若联系为m:n,则关系的码为各实体集码的组合。
③合并具有相同码的关系。
根据规则,把一个ER模型转换为关系模式,一般经历下面两个步骤:
1)标识ER模型中的联系。
2)依次转换与每个联系相关联的实体集及联系。
本题中从ER模型转换成关系模型,由两个实体和一个关系分别可以转换成三个关系模式。
【参考答案】
(1)ER模型如下所示:
(2)将ER模型转换为关系模型,并规范到3NF:
顾客(姓名,单位,电话号码),主键:姓名
商品(型号,名称,单价,产地),主键:型号
购买(姓名,型号,数量,日期),主键:姓名+型号
2.【解题思路】
(1)采用两表联合查询。以两表“借书证号”为相等条件,在结果集中用GROUP BY按照“借书证号”来分类,并且用HAVING关键字统计出符合条件的记录数。
(2)采用IN关键字进行两表联合查询,在BORROWER和LOANS联合的结果集中查找满足第三个表指定的条件。
【参考答案】
(1)SELECT LOANS.借书证号,姓名,系名,COUNT(*)As借书数量
FROM BORROWER,LOANS
WHERE BORROWER.借书证号=LOANS.借书证号
GROUP BY LOANS.借书证号
HAVING COUNT(*)>=5;
(2)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姓名=”王丽”
)
3.【解题思路】
创建触发器的SQL语句为:CREATE TRIGGER,其语法格式为:
CREATE TRIGGER[schema_name.]trigger_name
ON{tableIview}
{FOR l AlZl'ER I INSTEAD OF}
{[INSERT][,][DELETE][,][UPDATE]}
AS{sql_statement}
[;]
其中AFFER指定触发器为后触发型触发器,INSERT,UPDATE和DELETE为指定引发触发器执行的操作。根据原题要求,insert触发器会在inserted表中添加一条刚插入的记录,update触发器会在更新数据后将更新前的数据保存在deleted表中,更新后的数据保存在inserted表中。在教师表中插入或者更新的时候,都会在inserted表中增加一条记录,所以只需在触发器查询inserted表中查询有没有“教授”或者“副教授”的记录,如果有,则触发修改相应部门的高级职称人数即可。
【参考答案】
CREATE TRIGGER tri_ZC
ON教师表
AL TER INSERT,UPDATE
AS
BEGIN
DECLATE@ZC varchar(10),@dept varchar(30)
SELECT@dept=所在部门号,@2c=职称FROM inserted
IF@ZC=’教授’0r’副教授7
Update部门表
SET高级职称人数=高级职称人数+1
Where部门号=@dept
End
2017年计算机三级《网络技术》设计与应用试题及答案4.doc