计算机数据库三级设计与应用题资料.doc

设计与应用题1、设某教学管理系统，其查询模块需要提供如下功能：查询系信息，列出各系编号、系名和系办公电话；查询某系教师的信息，列出教师号、教师名、工资和聘用日期；查询某教师讲授的课程信息，列出课程号、课程名和学分；查询讲授某门课程的教师信息，列出教师名和职称；查询某门课程的先修课程信息，列出先修课程号和先修课程名。系统有如下业务规则：一个系可聘用多名教师，一名教师只能受聘于一个系；一名教师可讲授多门课程，一门课程可由多名教师讲授；一门课程可以有多门先修课程，也可以没有先修课程。(1)请根据以上查询功能与业务规则，用ER图描述该系统的概念模型。（5分）(2)将ER图转换为满足3NF的关系模式，并说明每个关系模式的主码和外码。（5分）(1)【解题思路】E-R图也称实体-联系图，提供了表示实体类型、属性和联系的方法，用来描述现实世界的概念模型。为了简化E-R图的处置，现实世界的事物能作为属性对待的则尽量作为属性对待。实体与属性的划分给出如下两条规则：作为属性，不能再具有需要描述的性质，属性必须是不可分的数据项，不能包含其它属性。属性不能与其它实体有联系，即E-R图中所表示的联系是实体之间的联系。本题中一个系可以聘用多名教师，一名教师只能受聘于一个系，所以系实体与教师实体有联系；一名教师可以讲授多门课程，一门课程可由多名教师讲授，所以教师实体与课程实体有联系，一门课程可以有多门先修课程，所以课程间也有联系。(2)【解题思路】要想使转换生成的关系模式满足3NF，则必须满足关系模式中每一个非主属性既不部分依赖于码也不传递依赖于码。2、设有商品表（商品号，商品名，单价）和销售表（销售单据号，商品号，销售时间，销售数量，销售单价）。其中，商品号代表一类商品，商品号、单价、销售数量和销售单价均为整型。请编写查询某年某商品的销售总毛利的存储过程，毛利=销售数量（销售单价单价）。要求商品号和年份为输入参数，总毛利用输出参数返回。（10分）【解题思路】存储过程是由PL/SQL语句书写的过程，这个过程经编译和优化后存储在数据库服务器中，使用时只要调用即可。使用存储过程具有以下优点：其已经编译和优化过了，所以运行效率高，提供了在服务器端快速执行SQL语句的有效途径；存储过程降低了客户端和服务器之间的通信量；方便实施企业规则，当企业规则发生变化时只要修改存储过程，而无需修改其他应用程序。创建存储过程：create procedure 过程名参数名 类型,参数名 类型 /*过程首部*/AsDeclare/*as下面对应的块为过程体*/.begin.end如上所示，存储过程包括过程首部和过程体。过程名是数据库服务器合法的对象标识；参数列表：用名字来标识调用时给出的参数值，必须指定值的数据类型。参数可以是输入参数或输出参数，默认为输入参数。 3、设某全国性的运输企业建立了大型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的被访问频率过高，导致系统整体性能下降，而其它汇总表被访问频率较低。在不增加汇总表和索引的情况下，请给出一个改善系统服务性能的优化方案，并简要说明理由。（10分）【解题思路】计算机系统中存在着两类不同的数据处理工作：操作型处理和分析型处理，也称作OLTP(联机事务处理)和OLAP(联机分析处理)。操作型处理也叫事务处理，是指对数据库联机的日常操作，通常是对一个或一组纪录的查询或修改，例如火车售票系统、银行通存通兑系统、税务征收管理系统等。这些系统要求快速响应用户请求，对数据的安全性、完整性以及事务吞吐量要求很高。结合本题中存在的问题，视图本身的访问量很高，而又仅仅以汇总表1为计算数据源，而其它汇总表访问率低，导致了资源利用不合理。因此本题考察了联机事务处理中的资源调度问题。4、设在SQL Server2008某数据库中有商品表和销售表，两个表的定义如下： (新增题库一)CREATE TABLE 商品表(商品号 char(10) PRIMARY KEY,商品名 varchar(40),类别 varchar(20),进货单价 int );CREATETABLE 销售表(商品号 char(10) REFERENCES 商品表(商品号),销售时间 datetime,销售数量 int,销售单价 int,PRIMARYKEY(商品号,销售时间) );现要创建一个存储过程：查询指定类别的每种商品当前年销售总金额（销售总金额=销售单价* 销售数量）。请补全下列代码。CREATEPROC p_TotalProfitlb varchar(20)ASSELECT 商品名,(SELECTFROM 销售表 t1 WHEREand= year(Getdate() AS 销售总金额FROM 商品表 t2 WHERE【解题思路】整个select语句的含义是：根据where子句的条件表达式，从From子句指定的基本表或视图中找出满足条件的元组，再按select子句中的目标列表达式，选出元组中的属性值形成结果表。该题中(SELECT FROM 销售表 t1 WHERE and = year(Getdate()是个嵌套，别名叫做销售总金额。语句执行过程：先从商品表中按照第二个where形成一个初步查询结果。由于只有商品表中有类别，因此可知第四空填写：t2.类别=lb(t2是商品表的别名，t1是销售表的别名)。由此可知该步是按给定类别查询。销售总金额语句就应该在第一部的基础上求某年的总额。因此可以推断第三空是获得销售时间语句。因此该空填写t1.销售时间。由于是在第一步基础上的查询，所以应该实行连表查询，第二空应该填写连接的条件，即t1.商品号=t2.商品号。第一空应该是求总额的语句。销售总金额=销售单价* 销售数量，即销售单价*SUM（销售数量)。【参考答案】【第1空】销售单价*SUM（销售数量)【第2空】t1.商品号=t2.商品号【第3空】t1.销售时间【第4空】t2.类别=lb5、设某超市经营管理系统使用SQL Server 2008数据库管理系统，此数据库服务器有2颗CPU、16GB内存、2TB磁盘。上线运行1年后，用户在生成每天每个销售人员及每个收银台的总销售额报表时速度缓慢。经技术人员分析，发现速度缓慢的原因为销售单据表和销售单据明细表数据量合计已经达到60GB。已知这两个表结构如下：销售单据表（销售单据编号，销售时间，销售人员编号，收银台编号）销售单据明细表（销售单据编号，商品编号，单价，数量）在进行此报表计算时数据库服务器CPU消耗非常高。为了优化此操作，某工程师建议在销售单据表中增加付款总金额属性，取值由触发器自动计算。请从磁盘空间使用、销售操作时对数据库服务器的影响、数据一致性以及对总销售额计算速度影响方面分析此方案优劣。请判断此方案是否可行，并从时空代价和优化效果方面分析原因。【解题思路】在数据库设计阶段，主要强调的是高效率利用存储空间，减少数据的冗余，减少数据的不一致性，这个过程也就是规范化的过程。但是在数据库运行阶段要考虑到高效率的进行数据处理。完全规划化的数据库会产生很多表，对于一个频繁使用的查询，如果它要求操作多个相关表中的数据，则每次为生成需要的查询结果而在连接多个表中相关行时，数据库管理系统就会消耗更多的计算资源，因为连接操作非常耗时。而反规范化是将规范化的关系转换为非规范化的关系的过程，目的是提高查询的效率。常见的方法有增加派生冗余列，增加冗余列，重新组表，分割表和新增汇总表等方法。该题解决的方式就是增加派生冗余列-付款总金额。派生性冗余列是指表中增加的列由表中的一些数据项经过计算而成，它的作用是查询时减少连接操作，避免使用聚合函数。例如销售单据明细表（销售单据编号，商品编号，单价，数量）中增加付款总金额，因为付款总金额=单价*数量得到，说明付款总金额是派生性冗余列。如果不要该字段，那么每次使用总价时，都要先执行代码计算后才能使用，如果商品数量较多，而且要频繁使用付款总金额时，计算付款总金额时执行的次数也会随着增加，这显然会影响数据库的执行效率。若增加付款总金额这个派生性冗余字段，虽然破坏了规范化原则，但只要执行一次计算付款总金额就可以把商品金额存在数据库中，以后不管什么时候使用付款总金额字段，只需要提取其值就可以了，不必在执行代码了。因此增加付款总金额，虽然提高了磁盘空间的使用，但是可以提高系统执行的效率，达到以空间换时间的目的。由此可以看出，在数据单据表中增加付款总金额不是正确的优化方法，应该在数据单据明细表中增加付款总金额，这样才能提高查询效率。【参考答案】 （新增题库一）此方案不可行。触发器可以通过数据库中的相关表进行层叠更改，这比直接把代码写在前台的做法更安全合理，保证了数据的一致性，但同时增加了磁盘空间的消耗。在超市经营中要批量操作、多次触发的情况下，触发器的效率低，因为它相当于每次都执行一段SQL语句，使cpu的消耗更高。因而从时空代价角度来说并不能达到优化的效果，故该方案不可行。6、设某连锁商店数据库中有关系模式R：R（商店编号，商品编号，库存数量，部门编号，负责人）如果规定：每个商店的每种商品只在一个部门销售，每个商店的每个部门只有一个负责人，每个商店的每种商品只有一个库存数量。（10分）（1）请根据上述规定，写出关系模式R的函数依赖集；（2）请给出关系模式R的候选码；（3）请说明关系模式R属于第几范式，并给出理由；（4）请将R分解成满足3NF的关系模式。（1）【解题思路】函数依赖定义：设R(U)是属性集U上的关系模式，X，Y是U的子集，若对于R（U）的任意一个可能的关系r，r中不可能存在两个元组在X上的属性值相等，在Y上的属性值不等，则称X函数确定Y或Y函数依赖X，记作X-Y。函数依赖是指关系R的一切关系均要满足的约束条件。（2）【解题思路】设K为R中的属性或属性组合，若U完全依赖于K，则K为R的候选码。（3）【解题思路】关系数据库是要满足一定要求的。满足最低要求的叫第一范式，在第一范式中满足进一步要求的为第二范式，其余以此类推。显然该关系模式满足第一范式，接下来检查其是否满足第二范式。在第二范式中，要求关系模式中不存在部分依赖，每一个非主属性完全依赖于码，而根据第一空可得如下依赖关系：（部门编号，商店编号）-负责人，所以属于第一范式。它的非主属性有3个（不包含在任何候选码中的属性）：部门编号、负责人、库存量，并都完全函数依赖于主码。将（商店编号、商品编号）记作X，（商店编号、部门编号）记作Y，负责人记作Z，由此可以看出，存在传递依赖，故不属于第三范式。（4）第三范式中要求每一个属性既不部分依赖于码也不传递依赖于码。7、在某数据库中，相关表的建表语句如下： （新增题库二）create tableT1( a1 int primary key, a2 int, a3 int foreign key references T2(a3);create tableT2( a3 int primary key, a4 int, a5 int foreign key references T2(a3);create tableT3( a1 int, a3 int, a6 int,primary key (a1, a3),a1 foreign key references T1(a1),a3 foreign key references T2(a3);请画出相应的E-R图，使得可以从该E-R图推导出上述表定义，其中实体和联系的名称可以自定，实体的主码属性请使用下划线标明。【参考答案】（新增题库二）8、设某超市经营管理系统使用SQL Server 2008数据库管理系统。为了保证数据库可靠运行，数据库管理员设置了每天夜间对数据库一次全备份，备份数据保留2个月的备份策略。上线运行1年后，SQL Server数据库中数据已经达到近200GB。每天夜间要运行3个小时才能将数据库进行一次全备份，影响了夜间统计等业务正常运行。同时，备份空间也非常紧张。请解释出现此现象的原因，并提出优化的方法。【参考答案】产生此现象的原因是系统采用了全备份策略，随着业务的开展，需要备份数据量逐渐增大，备份时间越来越长，占用了系统的资源，从而影响了其它业务。解决的方法是采用全备份+差异备份+日志备份组合策略备份数据库。全备份+差异备份+日志备份组合策略是指在全备份中加一些差异备份，比如每周日0:00进行一次全备份，然后每天0:00点进行一次差异备份，然后再两次差异备份之间增加一些日志备份。这样做备份和恢复的速度都比较快，而当系统出现故障时，丢失的数据也很少。备份示意图如下：如果系统在周二的差异备份之前出现故障，则应首先尝试备份活动日志（日志尾部），然后再按顺序恢复全备份1，差异备份1，日志备份3和日志备份4，然后再恢复备份的尾部日志。如果尾部日志备份成功，则数据库可以还原到故障点。这种备份策略虽然备份频率高，但是备份时间短，占用的备份空间也小，而且不会产生数据丢失。如果系统在周二的差异备份之前出现故障，则应首先尝试备份活动日志（日志尾部），然后再按顺序恢复全备份1，差异备份1，日志备份3和日志备份4，然后再恢复备份的尾部日志。如果尾部日志备份成功，则数据库可以还原到故障点。这种备份策略虽然备份频率高，但是备份时间短，占用的备份空间也小，而且不会产生数据丢失。1、设有商品表（商品号，商品名，单价）和销售表（销售单据号，商品号，销售时间，销售数量，销售单价）。其中，商品号代表一类商品，商品号、单价、销售数量和销售单价均为整型。请编写查询某年某商品的销售总毛利的存储过程，毛利=销售数量（销售单价单价）。要求商品号和年份为输入参数，总毛利用输出参数返回。（10分）CREATE PROCEDURE PRODUCT 商品号 int，年份 int，毛利 int outputAS DECLARE 某商品销售量 int,某商品进价 int，某商品销售单价 int /*中间变量定义*/BEGIN Select 某商品进价=单价 from 商品表 where 商品号=商品号 Select 某商品销售单价=销售单价，某商品销售量=count(*) from 销售表 where 商品号=商品号 and 销售时间=年份 IF 某商品进价 is NULL THEN /*判断该商品是否存在*/ROLLBACK;RETURN;END IFIF 某商品销售单价 is NULL THEN /*判断该商品是否可卖*/ROLLBACK;RETURN;END IFSET 毛利=（某商品销售单价-某商品进价）*某商品销售量GO2、在SQL Server 2008中，设某数据库中有商品表（商品号，商品名，进货价格），商品号为主码；销售表（商品号，销售时间，销售数量，销售价格，本次利润），商品号和销售时间为主码，销售价格为本次销售商品的单价。现要求每当在销售表中插入前4列数据时（假设一次只插入一行数据），系统自动计算本次销售产生的利润，并将该利润赋给销售表的第5列本次利润。请编写实现上述功能的后触发型触发器代码。（10分）CREATE TRIGGER calcu_productAFTER INSERT ON 销售表FOR EACH ROWAS BEGINDECLARE PurchasePrise float /*对应商品的进价的参数*/SELECT PurchasePrise=进货价格 FROM 商品表 WHERE 商品号=new.商品号 UPDATE 销售表 SET 本次利润=new.销售数量*（new.销售价格-PurchasePrise） WHERE 商品号=new.商品号AND 销售时间=new.销售时间 /*因为是行级触发器，所以可以使用更新后的新值，用new*/ END3、设在SQL Server 2008某数据库中，已建立了四个文件组：fg1、fg2、fg3和fg4，以及一个分区函数RangePF1。RangePF1的定义代码如下： CREATE PARTITION FUNCTION RangePF1(int) AS RANGE LEFT FOR VALUES(100,200,300)（1） 请基于RangePF1创建一个分区方案RangePS1，每个分区对应一个文件组。（5分）create partition scheme RangePS1as partition RangePF1to (fg1, fg1, fg1, fg2)（2）请使用RangePS1创建一个分区表PartitionT(Gid, GoodName, Price)，该表基于Gid列创建分区。其中Gid：int类型，取值大于等于1；GoodName：统一字符编码可变长类型，最多可存储20个汉字；Price：定点小数类型，小数部分1位，整数部分到千位。（5分）创建分区表：create table orders(GID int identity(1,1) primary key,GoodName varchar(40),Price float)on RangePS1（GID）4、 设在SQL Server 2008某数据库中有商品表和销售表，两个表的定义如下：CREATE TABLE 商品表(商品号 char(10）PRIMARY KEY,商品名 varchar(40),类别 varchar(20),进货单价 int )CREATE TABLE 销售表(商品号 char(10),销售时间 datetime,销售数量 int,销售单价 int,PRIMARY KEY(商品号,销售时间）)下面是一个用户定义的多语句表值函数，它接受类别作为输入参数，返回该类别下的每种商品在2012年的销售总利润，并将结果按照销售总利润的降序输出。请补全该函数定义代码。（10分）CREATE FUNCTION f_Profit (lb char(10) 【1】ProfitTable【2】(商品号 char(10),总利润 int )ASBEGIN INSERT INTO ProfitTable 【3】【4】END第一空：RETURNS第二空：table第三空：a SELECT a.商品号,SUM(销售数量*(销售单价-进货单价) AS总利润 FROM 销售表 a JOIN商品表b ON a. 商品号=b. 商品号 WHERE a.商品号 IN(SELECT 商品号 FROM 商品表 WHERE 类别=lb) GROUP BY a. 商品号ORDER BY 总利润 DESC第四空：RETURNRrofit Table5、设有图书管理数据库，包含三张表： 图书明细表（图书编号，图书类别，图书名称，作者，出版社，出版日期，定价）；读者表（借书证号，姓名，系别，办证日期） ；借出信息表（借出编号，借书证号，图书编号，借书日期）；完成下列操作：（10分）定义一个多语句表值函数，用于查询学生借书情况，只需提供参数：借书证号，就可以通过调用函数返回此学生的借书情况，若有借书籍，则返回所借书籍的编号、书籍名称、定价和借书日期；若没有在借书籍，则返回记录为空。设函数名为：f_BorrowBook(jszh char(20)。CREATE FUNCTION BorrowBook (jszh char(20)RETURNS jsqkb TABLE (书籍编号 char(20), 书籍名称 char(50), 定价 float, 借书日期 datetime)ASBEGIN INSERT jsqkb SELECT图书明细表.图书编号,图书名称,定价,借出信息表.借书日期 FROM,借出信息表,图书明细表 WHERE借出信息表.图书编号=图书明细表.图书编号 AND借出信息表.借书证号=jszh RETURN END6、某书店采用了SQL Server 2008数据库管理系统，该书店有一个需求，需要统计指定年份中每一本书的销售总额，例如：查询2012年所有书的销售总额。已知图书结构如下：图书表(书号BOOK_ID，书名BOOK_NAME，单价BOOK_PRICE)销售表(书号BOOK_ID，销售时间SALE_TIME，销售数量SALE_NUM)。假设单价和销售数量均为int型，书号和书名均为varchar(50)类型，销售时间为datetime型。请给出满足如下要求的多语句表值函数，该函数统计指定年份中每本书的销售总额。（10分）设函数名为：BOOK_PROFIT(year int)，函数的返回结果格式如下：书号 销售总额B001 60000A004 50000CREATE FUNCTION BOOK_PROFIT (year int)RETURNS f_ BOOK_PROFIT table(书号 varchar(50)，销售总额 int)ASBEGININSERT INTO f_ BOOK_PROFITSELECT a.书号，SUM(a.单价*b.销售数量)FROM 图书表 a JOIN 销售表 b ON a.书号=b.书号WHERE year(b.销售时间)=yearGROUP BY a.书号RETURNEND7、设在采用SQL Server 2008数据库的图书馆应用系统中有三个基本表，表结构如下所示，请用SQL语句完成下列两个查询：（1）SELECT LOANS.借书证号，姓名，系名，COUNT(*) AS 借书数量FROM BORROWER,LOANSWHERE 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 姓名=王丽)8、设某全国性的运输企业建立了大型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的被访问频率过高，导致系统整体性能下降，而其它汇总表被访问频率较低。在不增加汇总表和索引的情况下，请给出一个改善系统服务性能的优化方案，并简要说明理由。（10分）由于汇总表1和视图的模式访问频率都很高，而且视图的数据源来自汇总表1，又因为其他汇总表的访问率较低，所以只需要将视图的数据源绑定为汇总表3，因为汇总表3也可以满足视图的输出模式。这样不仅提升了汇总表3的数据访问率，而且降低了汇总表1的数据访问率，系统性能和服务性能得到了很大的优化。又因为货物约有500种，共10类，可以再建立一个视图绑定数据源为汇总表4，这样就可以充分利用汇总表4的数据信息，从而可以进一步优化系统性能。9、在进行某学校教务管理系统的数据库设计时，数据库设计人员设计了如下几个关系模式：系（系号，系名），系号为主码学生（学号，姓名，所在系号），学号为主码课程（课程号，课程名，开课系号），课程号为主码选课（学号，课程号，选课时间），学号和课程号为主码（8分）开发人员在将关系模式实施到SQL Server 2008的教务数据库时，使用了如下表结构定义语句：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(50) NOT NULL ,选课时间 datetime )在执行如下查询语句时发现执行效率很低：SELECT * FROM 选课 JOIN 学生 ON 学生.学号 = 选课.学号JOIN 系 ON 系.系号 = 学生.所在系号 JOIN 课程 ON 课程.课程号 = 选课.课程号WHERE 系.系号=012 AND convert(varchar(10),选课时间,120) = 2010-01-01（1）在查找原因时发现建表语句有问题。请指出问题并说明该问题是否会影响此查询语句的执行效率。（4分）（2）设已在选课表的选课时间列及学生表的所在系号列上建立了索引。请问这两个索引是否能够提高该查询语句的执行效率？如果不能，请说明原因。（4分）建表时没有设置主键，也没有说明外键，但不会影响此查询语句的执行效率。选课表的选课时间可以建立索引，从而提高查询效率，而学生表的所在系号建立索引不会提高查询效率。索引的意义就是将记录按目标关键字顺序排列，这样查找某个目标关键字的对应值的位置就缩小了查找范围。选课时间的重复率低，所以可以作为索引，而学生所在系号的重复率太高，则不会提高查询效率。10、某商场商品经营管理系统使用SQL Server 2008数据库管理系统，此系统上线运行1年后，业务人员使用某统计功能（此功能每月使用一次）时发现速度很慢。该统计功能主要执行的SQL语句如下：SELECT 商品号,SUM(销售数量*销售价格) 销售额 FROM 销售明细 GROUP BY 商品号;该销售明细表的建表语句如下：CREATE TABLE 销售明细(序列号 intIDENTITY(1,1) NOT NULL,商品号 intNOT NULL,销售日期 datetime NULL,销售数量 intNOT NULL,销售价格 intNOT NULL);并在销售明细表上建有如下索引：CREATE index ix_销售明细_商品号 on 销售明细(商品号);某技术人员提出通过执行下述语句以提高此查询的运行效率：CREATE VIEW 商品销售额视图 WITH SCHEMABINDING AS SELECT 商品号,SUM(销售数量*销售价格) 销售额,COUNT_BIG(*) cntFROM dbo.销售明细 GROUP BY 商品号;CREATE UNIQUE CLUSTERED INDEX ix_商品销售额 ON 商品销售额视图(商品号);（10分）（1） 请分析该技术人员给出的语句功能以及对原有查询语句的性能影响，并给出原因。语句功能：建立包含(商品号，销售额，该商品表中出现次数)带索引的视图，并建立按商品号对应销售额UNIQUE聚簇排序的索引，大大缩小了查询语句的查询范围，提高了查询效率。原因：视图中将间接相关的属性列（序列号，销售日期，商品号，销售数量，销售价格）转换成了目标属性列，减少了搜索空间；同时建立UNIQUE CLUSTERED索引，使查询商品号的数据记录唯一，降低了搜索范围，提高了搜索效率。（2）此商场的销售量很大，每天有大量数据插入到销售明细表中。请从数据库整体性能角度分析，此技术人员提出的优化方法是否合适，并给出原因。不合适，每天大量的插入操作使得在修改表的同时也要对视图进行修改，增加了系统的负担，然而该统计功能一个月才用一次，这样导致系统的利用率也较为低下。11、某教务管理系统使用SQL Server 2008数据库管理系统，数据库软硬件配置信息如下： 数据库运行在两路Intel Xeon E5-2609 2.4GHz CPU（每路CPU4核心），128GB内存、2块300GB 15000转SAS硬盘（RAID 1）的服务器上；服务器操作系统为Windows 2003 32位企业版，安装SP2补丁；数据库为SQL Server 2008 Enterprise（32 位），数据库中总数据量近130GB。近一个学期以来，用户反映系统运行缓慢，经程序员定位，确定为数据库服务器响应缓慢，需要进行调优。（10分）根据SQL Server 2008数据库的特性以及题目中的条件，综合给出以下的调优方案。(1) 表结构优化：重新优化数据库设计结构，规范数据库逻辑设计；设计主键和外键；设计合适大小的字段。(2) 硬件优化：购买一块同样大小的硬盘，将硬盘做成RAID5,用以提高数据库读写速度；增加服务器 CPU个数；扩大服务器的内存。(3) 索引优化：采用对经常作为条件查询的列设计索引，在查询中经常用到的列上建立非聚簇索引，在频繁进行范围查询、排序、分组的列上建立聚簇索引，对于有频繁进行删除、插入操作的表不要建立过多的索引。(4)采用视图：合理使用视图和分区视图，在需要更新和删除操作不多、查询操作频繁的表上建立索引视图。(5) SQL语句优化：选择运算应尽可能先做，并在对同一个表进行多个选择运算时，选择影响较大的语句放在前面，较弱的选择条件写在后面，这样就可以先根据较严格的条件得出数据较少的信息，再在这些信息中根据后面较弱的条件得到满足条件的信息。应避免使用相关子查询，把子查询转换成联结来实现。字段提取按照需多少，提多少的原则，避免SELECT *，SELECT *需要数据库返回相应表的所有列信息，这对于一个列较多的表无疑是一项费时的操作，采用存储过程，使用存储过程提高数据处理速度。12、某采购中心采购了一套商品批发查询管理系统，此系统采用SQL Server 2008数据库管理系统，该系统需要经常处理百万级以上的数据查询。同时该系统提供了第三方人员开发的SQL接口，第三方人员可以根据自己的需要开发自己的应用程序来访问数据库中的相关数据。（10分）(1)系统在使用的过程中，业务人员反应系统操作速度很慢。经过工程师检查测试后，数据库系统本身及网络传输过程中存在着一些问题，请给出针对数据库系统本身及网络传输过程中可能的一些调优方案。(2)工程师在完成数据库系统本身和网络问题的优化后，发现第三方开发人员的SQL查询语句存在很多没有优化的问题，请从第三方开发人员角度给出一些优化方案。(1)把数据、日志、索引放到不同的I/O设备上，增加读取速度，数据量(尺寸)越大，提高I/O越重要。纵向、横向分割表，减少表的尺寸。升级硬件，扩大服务器的内存,Windows 2000和SQL Server 2000能支持4-8G的内存。配置虚拟内存，虚拟内存大小应基于计算机上并发运行的服务进行配置。增加服务器 CPU个数。分布式分区视图可用于实现数据库服务器联合体。联合体是一组分开管理的服务器，但它们相互协作分担系统的处理负荷。这种通过分区数据形成数据库服务器联合体的机制能够扩大一组服务器，以支持大型的多层 Web 站点的处理需要。重建索引：DBCC REINDEX ,DBCC INDEXDEFRAG；收缩数据和日志：DBCC SHRINKDB,DBCC SHRINKFILE。设置自动收缩日志，对于大的数据库不要设置数据库自动增长，它会降低服务器的性能。优化锁结构。(2)对查询进行优化，尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。应尽量避免在 where 子句中使用!=或操作符，否则引擎将放弃使用索引而进行全表扫描。任何地方都不要使用 select * from 进行全表扫描 ，用具体的字段列代替*，不要返回冗余字段。避免频繁创建和删除临时表，以减少系统表资源的消耗。尽量避免使用游标，因为游标的效率较差，如果游标操作的数据超过1万行，那么就应该考虑改写。尽量避免大事务操作，提高系统并发能力。13、6-32008数据库管理系统。此系统上线运行前，需将商场原有的数据导入到新系统中。原有系统使用SQL Server 2000，数据结构与新系统不完全一致。因此需要把数据从SQL Server 2000导入到SQL Server 2008中，为了保证数据一致性，数据导入过程中要求暂停业务且必须在3小时内完成。（1）在原有数据导入新系统的过程中，实施人员发现原有数据量很大，导入数据需要四小时，业务无法接受。经分析某工程师认为，数据导入过程中的数据库I/O很高，但导入数据的程序本身对系统资源占用率很低。该工程师建议将数据导入过程中的数据恢复模式从完整模式改为简单模式以提高数据导入速度；而另一位工程师则认为此方法未必能提高数据导入速度，而且还可能导致数据丢失，不建议使用此方法。请分析此方法是否能够提高数据导入速度并给出理由，同时分析此操作的数据丢失风险。（5分）（2）在成功导入历史数据后，此系统顺利上线运行。在上线运行的第一周，发现数据库服务器的CPU使用率很高，达到近90%，高峰期间达到100%，且系统内存占用率达到90%，但系统I/O很轻。业务人员反应系统操作速度很慢。为了提高系统运行速度。在不修改应用程序的前提下，两位工程师提出了不同的解决办法：.为服务器增加2颗CPU，缓解CPU使用率很高的问题；.为服务器增加一倍内存，缓解内存使用率很高的问题。考虑成本，现阶段只能按照一种方案实施。请指出在现有情况下，哪种方案更合理并给出理由。（5分）（1）此方法能够提高数据导入速度。原因：此系统I/O很高，修改恢复模式后，系统最大限度减少日志开销，可提高导入速度。由于仅在数据导入过程中修改恢复模式，所以并无数据丢失风险。（2）第一种方案比较合理。原因：SQL Server 2008采用将数据缓冲在内存的方式，因此内存的使用率比较高是正常情况，且现阶段I/O并不存在问题，表明内存满足需求。此阶段CPU使用率很高，表明CPU计算资源不足，因此增加CPU数量对解决问题有效。14、在SQL Server 2008中，设有教师表(教师号, 姓名, 所在部门号, 职称)和部门表(部门号, 部门名, 高级职称人数)。请编写满足下列要求的后触发型触发器(设触发器名字为tri_zc)。每当在教师表中插入一名具有高级职称(教授或副教授)的教师时，或者将非高级职称教师的职称更改为高级职称时，均修改部门表中相应部门的高级职称人数。(假设一次操作只插入或更改一名教师的职称)（10分）CREATE TRIGGER tri_zcON 教师表AFTER INSERT,UPDATEASBEGINDECLATEzc varchar(10),dept varchar(30)SELECT dept=所在部门号，2c=职称 FROM insertedIF zc =教授 or 副教授Update 部门表SET 高级职称人数 = 高级职称人数 + 1Where 部门号 = deptEnd15、已知某教务管理系统的设计人员在需求分析阶段收集到下列原始数据表格：已知该业务系统存在如下规则：I每个教师有唯一的教师号，每个教师号对应唯一的一名教师；II每门课程有唯一的课程号，每个课程号对应唯一的一门课程；III每本教材有唯一的教材号，每个教材号对应唯一的一本教材；IV每个教师最多只有一个上级领导，也可以没有上级领导；V一门课程仅由一名教师讲授；VI一本教材仅用于一门课程。(12分)（1）请根据原始数据表及业务规则，给出该系统的关系模式，保证每个关系模式满足3NF，并说明每个关系模式的主码和外码。（2)画出该系统的ER图，ER图中需给出每个实体集的属性，主码属性用下划线标识。（1）【解题思路】关系的描述称为关系模式，它可以形式化地表示为R(U,D,DOM,F)。要想使转换生成的关系模式满足3NF，则必须满足关系模式中每一个非主属性既不部分依赖于码也不传递依赖于码。根据题目中的表中的数据和业务系统的规则可知，共有四个实体存在，分别为教师、课程、教材、职称，职称作为实体而不是教师的属性是因为职称与工资挂钩，考虑到其有进一步描述的特性，所以把职称作为一个关系而不是教师的一个属性，而且教师号、职称、工资之间存在传递依赖，不满足3NF。（2）【解题思路】E-R图也称实体-联系图，提供了表示实体类型、属性和联系的方法，用来描述现实世界的概念模型。为了简化E-R图的处置，现实世界的事物能作为属性对待的，尽量作为属性对待。实体与属性的划分给出如下两条规则：（1）作为属性，不能再具有需要描述的性质，属性必须是不可分的数据项，不能包含其它属性。（2）属性不能与其它实体有联系，即E-R图中所表示的联系是实体之间的联系。题目中教师与课程有讲授关系，课程和教材有使用关系，教师与职称有聘用关系。16、已知有如下关系模式：T1(a1,a2, a3, a5)，T2(a3,a4)，T3(a5, a6),T4(a3, a5, a7)，其中带下划线的属性标识为所在关系模式的主码，T1中的a3是参照T2的外码，T1中的a5已知有如下关系模式：T1(a1,a2, a3, a5)，T2(a3,a4)，T3(a5, a6),T4(a3, a5, a7)，其中带下划线的属性标识为所在关系模式的主码，T1中的a3是参照T2的外码，T1中的a5是参照T3的外码，T4中的a3是参照T2的外码，T4中的a5是参照T3的外码。请将上述关系模式用适当的ER图表示出来，并用下划线标注出作为实体主码的属性。【解析思路】 ER图向关系模式转换涉及到两方面：实体的转换；实体间联系的转换。实体的转换：在从ER图转换为关系模式时，一个实体就转换成一个关系模式，实体的属性就是关系模式的属性，实体的键就是关系的主键。实体间联系的转换：实体间存在三种联系,即1:1（一对一）,1:m（一对多），m:n（多对多）。在从ER向关系模式转换规则如下：1:1（一对一）联系方法一：联系转换为独立的关系模式；模式的属性由联系本身的属性及两个实体的键构成；主键由两个实体中的任意一个键构成。方法二：联系与一端的实体的关系模式合并，即将联系的属性加入到实体的关系模式内，主键不变。1:m（一对多）联系方法一：联系转换为独立的关系模式；模式的属性由联系本身的属性及两个实体的键构成；主键由n端实体的键组成。方法二：与n端的实体的关系模式合并，即将联系的属性加入到实体的关系模式内，主键不变。m:n（多对多）联系多对多联系转换成新的独立的模式时，模式的属性由联系本身的属性及两个实体的键构成，主键由两端实体的键组合而成。该题的解题过程是ER图向关系模式转换过程的反向推算。由于T4中的a3, a5分别是T2和T3的主码，根据m:n（多对多）联系转换规则知道，T2模式代表的实体（假设实体名为t2）和T3模式代表的实体(假设实体名为t3)之间存在m:n（多对多）联系（假设联系名为t4），且a7是联系的属性。由于a4和a6没有在T4中，因此可以推论出a4和a6不是联系的属性，而是实体t2和实体t3的属性。由于关系模式T1中的a3, a5分别是T2和T3的主码，a1、a2不属于T2、T3、T4，因此可以推断a2不是实体T2、T3和T4的属性。只能是另外一个实体（假设实体t1）的属性。而t2和t1之间存在1：m关系，t2和t1之间存在1：m关系。【参考答案】