数据库三级填空+应用（2）

sysadmin、dbcreator

数据是面向主题的（2）、集成的、非易失的、随时间不断变化的数据集合，

数据字典

【答案】完整性约束
数据模型成分

33【解析】顺序图主要用于描述系统内对象之间的消息发送和接收序列。

34如果把舍弃的元组也保存在结果关系中，而在其它属性上填空值(Null)，那么这种连接就叫外连接。

41【答案】隔离
【解析】产生上述三类数据的不一致性主要原因就是并发操作破坏了事务的隔离性

组件图提供系统的物理视图

COUNT(DISTINCT 选课表.学号)
GROUP BY 所在系名

自连接是SQL语句中经常要用的连接方式

UNDO

日志

设某教学管理系统，其查询模块需要提供如下功能：
Ⅰ．查询系信息，列出各系编号、系名和系办公电话；
Ⅱ．查询某系教师的信息，列出教师号、教师名、工资和聘用日期；
Ⅲ．查询某教师讲授的课程信息，列出课程号、课程名和学分；
Ⅳ．查询讲授某门课程的教师信息，列出教师名和职称；
Ⅴ．查询某门课程的先修课程信息，列出先修课程号和先修课程名。
系统有如下业务规则：
Ⅰ．一个系可聘用多名教师，一名教师只能受聘于一个系；
Ⅱ．一名教师可讲授多门课程，一门课程可由多名教师讲授；
Ⅲ．一门课程可以有多门先修课程，也可以没有先修课程。

(1)请根据以上查询功能与业务规则，用ER图描述该系统的概念模型。（5分）
(2)将ER图转换为满足3NF的关系模式，并说明每个关系模式的主码和外码。（5分）

（2）【参考答案】
系（系编号，系名，系办公室电话），无外码
教师（教师号，教师名，职称，工资，聘用日期，系编号），外码：系编号
先修课程（先修课程号，先修课程名）
课程（课程号，课程名，学分，先修课程号），外码：先修课程号
讲授（教师号，课程号）

因为转换过来的关系模式中，不存在属性依赖于其他非主属性，因此对转换的结果不需要优化。
【解题思路】
在将ER图向关系模式转换时规则如下：
①1:1（一对一）联系。
方法一：联系转换为独立的关系模式；模式的属性由联系本身的属性及两个实体的键构成；主键由两个实体中的任意一个键构成。
方法二：联系与一端的实体的关系模式合并，即将联系的属性加入到实体的关系模式内，主键不变。
②1:m（一对多）联系。
方法一：联系转换为独立的关系模式；模式的属性由联系本身的属性及两个实体的键构成；主键由m端实体的键组成。
方法二：与m端的实体的关系模式合并，即将联系的属性加入到实体的关系模式内，主键不变。
③m:n（多对多）联系转换成新的独立的模式，模式的属性由联系本身的属性及两个实体的键构成，主键由两端实体的键组合而成。
满足3NF，则必须满足关系模式中每一个非主属性既不部分依赖于码也不传递依赖于码

设某全国性的运输企业建立了大型OLTP系统，并在该系统之上建立了数据仓库。OLTP系统和数据仓库中有如下数据表：
运输明细表（运输单ID，发送站ID，终到站ID，货物ID，货物重量，运输价格，发货日期）
汇总表1（发送站ID，终到站ID，货物ID，发货日期，总重，总运价）
汇总表2（发送站ID，终到地区ID，货物ID，发货日期，总重，总运价）
汇总表3（发送站ID，终到站ID，货物ID，发货月份，总重，总运价）
汇总表4（发送地区ID，终到地区ID，货物类别ID，发货日期，总重，总运价）
该企业管理的货运站约有100个，货物约有500种共10类，各汇总表都建有主码，且各表有合理的维护策略，在每次维护后数据能保持一致。设有视图V，该视图的访问频率很高，其查询结果模式为（发送地区ID，终到站ID，发货月份，总重，总运价），该视图现以汇总表1为计算数据源。经监控发现，汇总表1的被访问频率过高，导致系统整体性能下降，而其它汇总表被访问频率较低。在不增加汇总表和索引的情况下，请给出一个改善系统服务性能的优化方案，并简要说明理由。

【参考答案】
由于汇总表1和视图的模式访问频率都很高，而且视图的数据源来自汇总表1，又因为其他汇总表的访问率较低，所以只需要将视图的数据源绑定为汇总表3，因为汇总表3也可以满足视图的输出模式。这样不仅提升了汇总表3的数据访问率，而且降低了汇总表1的数据访问率，系统性能和服务性能得到了很大的优化。又因为货物约有500种，共10类，可以再建立一个视图绑定数据源为汇总表4，这样就可以充分利用汇总表4的数据信息，从而可以进一步完善系统性能的优化。

建表时没有设置主键，也没有说明外键。但不会影响此查询语句的执行效率

完整性约束条件

"选课"表的"选课时间"列上建立了索引，从而能够提高执行效率。经常出现在Where子句中的字段，特别是大表的字段，应该建立索引。索引的作用就类似于书的目录，即会按照章节的顺序排列。因此如果在一本数百页的书里面查找某个章节位置的时候，就可以只扫描书的目录。扫描的范围缩了n倍，查询的效率自然就会提高。另外，在SQL Server内存够用的情况下，索引会被放到内存中，在内存中查找自然又会提高效率，所以必须合理利用索引。

根据WHERE条件后的字段(only)对表建立索引对于提高查询效率是有帮助的。

CREATE UNIQUE CLUSTERED INDEX Idx1 ON T（c2,c1）

IDENTITY作约束性定义，使C1可以成为标识列。

sysadmin

三级加锁

完全转储、差量转储和增量转储

数据是面向主题的、集成的、非易失的、随时间不断变化的数据集合，

应用1.5

【参考答案】
教师（教师号，教师名，上级领导号，职称名）
主码：教师号；外码：上级领导号、职称名
职称（职称名，工资）
主码：职称名
课程（课程号，课程名，学分，教材号，任课教师号）
主码：课程号；外码：教材号，任课教师号
教材（教材号，教材名，出版社）
主码：教材号；外码：无
【解题思路】
考虑到其有进一步描述的特性，所以把职称作为一个关系而不是教师的一个属性，而且教师号、职称、工资之间存在传递依赖，不满足3NF。

后触发器？

在SQL Server 2008中，设某数据库中有商品表（商品号，商品名，进货价格），商品号为主码；销售表（商品号，销售时间，销售数量，销售价格，本次利润），商品号和销售时间为主码，销售价格为本次销售商品的单价。现要求每当在销售表中插入前4列数据时（假设一次只插入一行数据），系统自动计算本次销售产生的利润，并将该利润赋给销售表的第5列"本次利润"。请编写实现上述功能的后触发型触发器代码。（10分）

CREATE TRIGGER calcu_product
AFTER INSERT ON 销售表
FOR EACH ROW
AS BEGIN
DECLARE @PurchasePrise float   /*对应商品的进价的参数*/
SELECT @PurchasePrise=进货价格 FROM 商品表 WHERE 商品号=new.商品号
UPDATE 销售表 SET 本次利润=new.销售数量*（new.销售价格-@PurchasePrise <mailto:new.销售价格-@PurchasePrise>）
WHERE 商品号=new.商品号AND 销售时间=new.销售时间
/*因为是行级触发器，所以可以使用更新后的新值，用new*/
END

触发器的创建格式：
    CREATE TRIGGER ［Schema-name.］trigger-name
ON{table｜view}
    {FOR|AFTER｜INSTEAD OF}
    {［INSERT］［,］［UPDATE］［,］［DELETE］}
    AS{Sql-statement}
   ［;］
对应于本题中，触发事件为在销售表中插入前四列，条件为无条件执行，触发体动作为计算本次利润并插入表中。