2022年2022年计算机数据库三级设计与应用题 .pdf

《2022年2022年计算机数据库三级设计与应用题 .pdf》由会员分享，可在线阅读，更多相关《2022年2022年计算机数据库三级设计与应用题 .pdf（17页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、设计与应用题1、设某教学管理系统，其查询模块需要提供如下功能：查询系信息，列出各系编号、系名和系办公电话；查询某系教师的信息，列出教师号、教师名、工资和聘用日期；查询某教师讲授的课程信息，列出课程号、课程名和学分；查询讲授某门课程的教师信息，列出教师名和职称；查询某门课程的先修课程信息，列出先修课程号和先修课程名。系统有如下业务规则：一个系可聘用多名教师，一名教师只能受聘于一个系；一名教师可讲授多门课程，一门课程可由多名教师讲授；一门课程可以有多门先修课程，也可以没有先修课程。(1)请根据以上查询功能与业务规则，用ER图描述该系统的概念模型。（5 分）(2)将 ER图转换为满足3NF的关系模式

2、，并说明每个关系模式的主码和外码。（5 分）(1)【解题思路】E-R 图也称实体-联系图，提供了表示实体类型、属性和联系的方法，用来描述现实世界的概念模型。为了简化 E-R 图的处置，现实世界的事物能作为属性对待的则尽量作为属性对待。实体与属性的划分给出如下两条规则：作为属性，不能再具有需要描述的性质，属性 必须是不可分的数据项，不能包含其它属性。属性 不能与其它实体有联系，即E-R 图中所表示的联系是实体之间的联系。本题中一个系可以聘用多名教师，一名教师只能受聘于一个系，所以系实体与教师实体有联系；一名教师可以讲授多门课程，一门课程可由多名教师讲授，所以教师实体与课程实体有联系，一门课程可以

3、有多门先修课程，所以课程间也有联系。(2)【解题思路】要想使转换生成的关系模式满足3NF，则必须满足关系模式中每一个非主属性既不部分依赖于码也不传递依赖于码。2、设有商品表（商品号，商品名，单价）和销售表（销售单据号，商品号，销售时间，销售数量，销售单价）。其中，商品号代表一类商品，商品号、单价、销售数量和销售单价均为整型。请编写查询某年某商品的销售总毛利的存储过程，毛利=销售数量（销售单价单价）。要求商品号和年份为输入参数，总毛利用输出参数返回。（10 分）【解题思路】存储过程是由PL/SQL语句书写的过程，这个过程经编译和优化后存储在数据库服务器中，使用时只要调用即可。使用存储过程具有以下

4、优点：其已经编译和优化过了，所以运行效率高，提供了在服务器端快速执行 SQL语句的有效途径；存储过程降低了客户端和服务器之间的通信量；方便实施企业规则，当企业规则发生变化时只要修改存储过程，而无需修改其他应用程序。创建存储过程：create procedure 过程名 参数名类型,参数名类型 /*过程首部*/As Declare/*as下面对应的 块为过程体*/.begin.end 名师资料总结-精品资料欢迎下载-名师精心整理-第 1 页，共 17 页 -如上所示，存储过程包括过程首部和过程体。过程名是数据库服务器合法的对象标识；参数列表：用名字来标识调用时给出的参数值，必须指定值的数据类型。

5、参数可以是输入参数或输出参数，默认为输入参数。3、设某全国性的运输企业建立了大型OLTP系统，并在该系统之上建立了数据仓库。OLTP系统和数据仓库中有如下数据表：运输明细表（运输单ID，发送站 ID，终到站 ID，货物 ID，货物重量，运输价格，发货日期）汇总表 1（发送站 ID，终到站 ID，货物 ID，发货日期，总重，总运价）汇总表 2（发送站 ID，终到地区ID，货物 ID，发货日期，总重，总运价）汇总表 3（发送站 ID，终到站 ID，货物 ID，发货月份，总重，总运价）汇总表 4（发送地区ID，终到地区 ID，货物类别ID，发货日期，总重，总运价）该企业管理的货运站约有100 个，货

6、物约有 500 种共 10 类，各汇总表都建有主码，且各表有合理的维护策略，在每次维护后数据能保持一致。设有视图V，该视图的访问频率很高，其查询结果模式为（发送地区ID，终到站 ID，发货月份，总重，总运价），该视图现以汇总表1 为计算数据源。经监控发现，汇总表1的被访问频率过高，导致系统整体性能下降，而其它汇总表被访问频率较低。在不增加汇总表和索引的情况下，请给出一个改善系统服务性能的优化方案，并简要说明理由。（10 分）【解题思路】计算机系统中存在着两类不同的数据处理工作：操作型处理和分析型处理，也称作OLTP(联机事务处理)和OLAP(联机分析处理)。操作型处理也叫事务处理，是指对数据库

7、联机的日常操作，通常是对一个或一组纪录的查询或修改，例如火车售票系统、银行通存通兑系统、税务征收管理系统等。这些系统要求快速响应用户请求，对数据的安全性、完整性以及事务吞吐量要求很高。结合本题中存在的问题，视图本身的访问量很高，而又仅仅以汇总表1 为计算数据源，而其它汇总表访问率低，导致了资源利用不合理。因此本题考察了联机事务处理中的资源调度问题。4、设在 SQL Server2008 某数据库中有商品表和销售表，两个表的定义如下：(新增题库一)CREATE TABLE 商品表(商品号 char(10)PRIMARY KEY,商品名 varchar(40),类别 varchar(20),进货单

8、价 int);CREATETABLE 销售表(商品号 char(10)REFERENCES 商品表(商品号),销售时间 datetime,销售数量 int,销售单价 int,PRIMARYKEY(商品号,销售时间);现要创建一个存储过程：查询指定类别的每种商品当前年销售总金额（销售总金额=销售单价*销售数量）。请补全下列代码。CREATEPROC p_TotalProfit lb varchar(20)AS SELECT 商品名,(SELECT【1】FROM 销售表 t1 WHERE【2】and【3】=year(Getdate()AS 销售总金额FROM 商品表 t2 WHERE【4】名师资料

9、总结-精品资料欢迎下载-名师精心整理-第 2 页，共 17 页 -【解题思路】整个 select语句的含义是：根据where 子句的条件表达式，从From子句指定的基本表或视图中找出满足条件的元组，再按select子句中的目标列表达式，选出元组中的属性值形成结果表。该题中(SELECT FROM 销售表 t1 WHERE and =year(Getdate()是个嵌套，别名叫做销售总金额。语句执行过程：?先从商品表中按照第二个where 形成一个初步查询结果。由于只有商品表中有类别，因此可知第四空填写：t2.类别=lb(t2 是商品表的别名，t1 是销售表的别名)。由此可知该步是按给定类别查询

10、。?销售总金额语句就应该在第一部的基础上求某年的总额。因此可以推断第三空是获得销售时间语句。因此该空填写 t1.销售时间。由于是在第一步基础上的查询，所以应该实行连表查询，第二空应该填写连接的条件，即 t1.商品号=t2.商品号。?第一空应该是求总额的语句。销售总金额=销售单价*销售数量，即销售单价*SUM（销售数量)。【参考答案】【第 1 空】销售单价*SUM（销售数量)【第 2 空】t1.商品号=t2.商品号【第 3 空】t1.销售时间【第 4 空】t2.类别=lb 5、设某超市经营管理系统使用SQL Server 2008 数据库管理系统，此数据库服务器有2 颗 CPU、16GB内存、2

11、TB磁盘。上线运行1 年后，用户在生成每天每个销售人员及每个收银台的总销售额报表时速度缓慢。经技术人员分析，发现速度缓慢的原因为销售单据表和销售单据明细表数据量合计已经达到60GB。已知这两个表结构如下：销售单据表（销售单据编号，销售时间，销售人员编号，收银台编号）销售单据明细表（销售单据编号，商品编号，单价，数量）在进行此报表计算时数据库服务器CPU消耗非常高。为了优化此操作，某工程师建议在销售单据表中增加付款总金额 属性，取值由触发器自动计算。请从磁盘空间使用、销售操作时对数据库服务器的影响、数据一致性以及对总销售额计算速度影响方面分析此方案优劣。请判断此方案是否可行，并从时空代价和优化效

12、果方面分析原因。【解题思路】在数据库设计阶段，主要强调的是高效率利用存储空间，减少数据的冗余，减少数据的不一致性，这个过程也就是规范化的过程。但是在数据库运行阶段要考虑到高效率的进行数据处理。完全规划化的数据库会产生很多表，对于一个频繁使用的查询，如果它要求操作多个相关表中的数据，则每次为生成需要的查询结果而在连接多个表中相关行时，数据库管理系统就会消耗更多的计算资源，因为连接操作非常耗时。而反规范化是将规范化的关系转换为非规范化的关系的过程，目的是提高查询的效率。常见的方法有增加派生冗余列，增加冗余列，重新组表，分割表和新增汇总表等方法。该题解决的方式就是增加派生冗余列-付款总金额 。派生性

13、冗余列是指表中增加的列由表中的一些数据项经过计算而成，它的作用是查询时减少连接操作，避免使用聚合函数。例如销售单据明细表（销售单据编号，商品编号，单价，数量）中增加付款总金额 ，因为付款总金额=单价*数量得到，说明 付款总金额是派生性冗余列。如果不要该字段，那么每次使用总价时，都要先执行代码计算后才能使用，如果商品数量较多，而且要频繁使用 付款总金额 时，计算 付款总金额 时执行的次数也会随着增加，这显然会影响数据库的执行效率。若增加付款总金额 这个派生性冗余字段，虽然破坏了规范化原则，但只要执行一次计算 付款总金额 就可以把商品金额存在数据库中，以后不管什么时候使用付款总金额 字段，只需要提

14、名师资料总结-精品资料欢迎下载-名师精心整理-第 3 页，共 17 页 -取其值就可以了，不必在执行代码了。因此增加付款总金额 ，虽然提高了磁盘空间的使用，但是可以提高系统执行的效率，达到以空间换时间的目的。由此可以看出，在数据单据表中增加付款总金额 不是正确的优化方法，应该在数据单据明细表中增加付款总金额 ，这样才能提高查询效率。【参考答案】（新增题库一）此方案不可行。触发器可以通过数据库中的相关表进行层叠更改，这比直接把代码写在前台的做法更安全合理，保证了数据的一致性，但同时增加了磁盘空间的消耗。在超市经营中要批量操作、多次触发的情况下，触发器的效率低，因为它相当于每次都执行一段SQL语句

15、，使 cpu 的消耗更高。因而从时空代价角度来说并不能达到优化的效果，故该方案不可行。6、设某连锁商店数据库中有关系模式R：R（商店编号，商品编号，库存数量，部门编号，负责人）如果规定：每个商店的每种商品只在一个部门销售，每个商店的每个部门只有一个负责人，每个商店的每种商品只有一个库存数量。（10 分）（1）请根据上述规定，写出关系模式R的函数依赖集；（2）请给出关系模式R 的候选码；（3）请说明关系模式R 属于第几范式，并给出理由；（4）请将 R分解成满足3NF的关系模式。（1）【解题思路】函数依赖定义：设R(U)是属性集 U上的关系模式，X，Y是 U的子集，若对于R（U）的任意一个可能的关

16、系 r，r 中不可能存在两个元组在X上的属性值相等，在Y上的属性值不等，则称X函数确定 Y或 Y函数依赖 X，记作 X-Y。函数依赖是指关系R的一切关系均要满足的约束条件。（2）【解题思路】设 K 为 R中的属性或属性组合，若U完全依赖于K，则 K为 R的候选码。（3）【解题思路】关系数据库是要满足一定要求的。满足最低要求的叫第一范式，在第一范式中满足进一步要求的为第二范式，其余以此类推。显然该关系模式满足第一范式，接下来检查其是否满足第二范式。在第二范式中，要求关系模式中不存在部分依赖，每一个非主属性完全依赖于码，而根据第一空可得如下依赖关系：（部门编号，商店编号）-负责人，所以属于第一范式

17、。它的非主属性有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 fo

18、reign key references T2(a3);名师资料总结-精品资料欢迎下载-名师精心整理-第 4 页，共 17 页 -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 图推导出上述表定义，其中实体和联系的名称可以自定，实体的主码属性请使用下划线标明。【参考答案】名师资料总结-精品资料欢迎下载-名师精心整理-第 5 页，共 17 页 -（新增题库二）8

19、、设某超市经营管理系统使用SQL Server 2008数据库管理系统。为了保证数据库可靠运行，数据库管理员设置了每天夜间对数据库一次全备份，备份数据保留2 个月的备份策略。上线运行1 年后，SQL Server数据库中数据已经达到近200GB。每天夜间要运行3 个小时才能将数据库进行一次全备份，影响了夜间统计等业务正常运行。同时，备份空间也非常紧张。请解释出现此现象的原因，并提出优化的方法。【参考答案】产生此现象的原因是系统采用了全备份策略，随着业务的开展，需要备份数据量逐渐增大，备份时间越来越长，占用了系统的资源，从而影响了其它业务。解决的方法是采用全备份+差异备份+日志备份组合策略备份数

20、据库。全备份+差异备份+日志备份组合策略是指在全备份中加一些差异备份，比如每周日 0:00进行一次全备份，然后每天 0:00 点进行一次差异备份，然后再两次差异备份之间增加一些日志备份。这样做备份和恢复的速度都比较快，而当系统出现故障时，丢失的数据也很少。备份示意图如下：如果系统在周二的差异备份之前出现故障，则应首先尝试备份活动日志（日志尾部），然后再按顺序恢复全备份 1，差异备份1，日志备份 3 和日志备份4，然后再恢复备份的尾部日志。如果尾部日志备份成功，则数据库可以还原到故障点。这种备份策略虽然备份频率高，但是备份时间短，占用的备份空间也小，而且不会产生数据丢失。如果系统在周二的差异备份

21、之前出现故障，则应首先尝试备份活动日志（日志尾部），然后再按顺序恢复全备份 1，差异备份1，日志备份 3 和日志备份4，然后再恢复备份的尾部日志。如果尾部日志备份成功，则数据库可以还原到故障点。这种备份策略虽然备份频率高，但是备份时间短，占用的备份空间也小，而且不会产生数据丢失。1、设有商品表（商品号，商品名，单价）和销售表（销售单据号，商品号，销售时间，销售数量，销售单价）。其中，商品号代表一类商品，商品号、单价、销售数量和销售单价均为整型。请编写查询某年某商品的销售总毛利的存储过程，毛利=销售数量（销售单价单价）。要求商品号和年份为输入参数，总毛利用输出参数返回。（10分）CREATE P

22、ROCEDURE PRODUCT 商品号 int，年份 int，毛利 int output AS DECLARE 某商品销售量 int,某商品进价 int，某商品销售单价 int /*中间变量定义*/BEGIN Select 某商品进价=单价 from 商品表 where 商品号=商品号 Select 某商品销售单价=销售单价，某商品销售量=count(*)from 销售表 where 商品号=商品号 and 销售时间=年份IF 某商品进价 is NULL THEN /*判断该商品是否存在*/ROLLBACK;RETURN;名师资料总结-精品资料欢迎下载-名师精心整理-第 6 页，共 17 页

23、 -END IF IF 某商品销售单价 is NULL THEN /*判断该商品是否可卖*/ROLLBACK;RETURN;END IF SET 毛利=（某商品销售单价-某商品进价）*某商品销售量GO 2、在 SQL Server 2008 中，设某数据库中有商品表（商品号，商品名，进货价格），商品号为主码；销售表（商品号，销售时间，销售数量，销售价格，本次利润），商品号和销售时间为主码，销售价格为本次销售商品的单价。现要求每当在销售表中插入前4列数据时（假设一次只插入一行数据），系统自动计算本次销售产生的利润，并将该利润赋给销售表的第5列本次利润 。请编写实现上述功能的后触发型触发器代码。（

24、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）WHERE 商品号=new.商品号AND 销售时间=new.销售时间/*因为是行级触发器，所以可以使用更新后的新值，用new*/END3、设在 SQL Ser

25、ver 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 RangePS1 as partition RangePF1 to(fg1,fg1,fg1,fg2)（2）请使用 RangePS1 创建一个分区表 P

26、artitionT(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 商品表(商

27、品号 char(10）PRIMARY KEY,名师资料总结-精品资料欢迎下载-名师精心整理-第 7 页，共 17 页 -商品名 varchar(40),类别 varchar(20),进货单价 int)CREATE TABLE 销售表(商品号 char(10),销售时间 datetime,销售数量 int,销售单价 int,PRIMARY KEY(商品号,销售时间）)下面是一个用户定义的多语句表值函数，它接受类别作为输入参数，返回该类别下的每种商品在2012年的销售总利润，并将结果按照销售总利润的降序输出。请补全该函数定义代码。（10分）CREATE FUNCTION f_Profit(lb c

28、har(10)【1】ProfitTable【2】(商品号 char(10),总利润 int)AS BEGIN 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 Table 5、设有图

29、书管理数据库，包含三张表：图书明细表（图书编号，图书类别，图书名称，作者，出版社，出版日期，定价）；读者表（借书证号，姓名，系别，办证日期）；借出信息表（借出编号，借书证号，图书编号，借书日期）；完成下列操作：（10分）定义一个多语句表值函数，用于查询学生借书情况，只需提供参数：借书证号，就可以通过调用函数返回此学生的借书情况，若有借书籍，则返回所借书籍的编号、书籍名称、定价和借书日期；若没有在借书籍，则返回记录为空。设函数名为：f_BorrowBook(jszh char(20)。CREATE FUNCTION BorrowBook(jszh char(20)RETURNS jsqkb TA

30、BLE(书籍编号 char(20),书籍名称 char(50),定价 float,借书日期 datetime)AS BEGIN INSERT jsqkb SELECT 图书明细表.图书编号,图书名称,定价,借出信息表.借书日期 FROM,借出信息表,图书明细表WHERE借出信息表.图书编号=图书明细表.图书编号 AND借出信息表.借书证号=jszh 名师资料总结-精品资料欢迎下载-名师精心整理-第 8 页，共 17 页 -RETURN END6、某书店采用了SQL Server 2008 数据库管理系统，该书店有一个需求，需要统计指定年份中每一本书的销售总额，例如：查询2012年所有书的销售总

31、额。已知图书结构如下：图书表(书号 BOOK_ID，书名 BOOK_NAME，单价 BOOK_PRICE)销售表(书号 BOOK_ID，销售时间 SALE_TIME，销售数量 SALE_NUM)。假设单价和销售数量均为int 型，书号和书名均为varchar(50)类型，销售时间为datetime 型。请给出满足如下要求的多语句表值函数，该函数统计指定年份中每本书的销售总额。（10分）设函数名为：BOOK_PROFIT(year int)，函数的返回结果格式如下：书号销售总额B001 60000 A004 50000 CREATE FUNCTION BOOK_PROFIT(year int)R

32、ETURNS f_ BOOK_PROFIT table(书号 varchar(50)，销售总额 int)AS BEGIN INSERT INTO f_ BOOK_PROFIT SELECT a.书号，SUM(a.单价*b.销售数量)FROM 图书表 a JOIN 销售表 b ON a.书号=b.书号WHERE year(b.销售时间)=year GROUP BY a.书号RETURN END 7、设在采用 SQL Server 2008 数据库的图书馆应用系统中有三个基本表，表结构如下所示，请用SQL 语句完成下列两个查询：名师资料总结-精品资料欢迎下载-名师精心整理-第 9 页，共 17 页

33、 -（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.借

34、书证号=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，货物类

35、别 ID，发货日期，总重，总运价）该企业管理的货运站约有100个，货物约有 500种共 10类，各汇总表都建有主码，且各表有合理的维护策略，在每次维护后数据能保持一致。设有视图V，该视图的访问频率很高，其查询结果模式为（发送地区ID，终到站 ID，发货月份，总重，总运价），该视图现以汇总表1为计算数据源。经监控发现，汇总表1的被访问频率过高，导致系统整体性能下降，而其它汇总表被访问频率较低。在不增加汇总表和索引的情况下，名师资料总结-精品资料欢迎下载-名师精心整理-第 10 页，共 17 页 -请给出一个改善系统服务性能的优化方案，并简要说明理由。（10分）由于汇总表 1和视图的模式访问频率都

36、很高，而且视图的数据源来自汇总表1，又因为其他汇总表的访问率较低，所以只需要将视图的数据源绑定为汇总表3，因为汇总表 3也可以满足视图的输出模式。这样不仅提升了汇总表 3的数据访问率，而且降低了汇总表1的数据访问率，系统性能和服务性能得到了很大的优化。又因为货物约有 500种，共10类，可以再建立一个视图绑定数据源为汇总表4，这样就可以充分利用汇总表4的数据信息，从而可以进一步优化系统性能。9、在进行某学校教务管理系统的数据库设计时，数据库设计人员设计了如下几个关系模式：系（系号，系名），系号为主码学生（学号，姓名，所在系号），学号为主码课程（课程号，课程名，开课系号），课程号为主码选课（学号

37、，课程号，选课时间），学号和课程号为主码（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(

38、50)NOT NULL,课程号 varchar(50)NOT NULL,选课时间 datetime )在执行如下查询语句时发现执行效率很低：SELECT*FROM 选课 JOIN 学生 ON 学生.学号 =选课.学号JOIN 系 ON 系.系号 =学生.所在系号 JOIN 课程 ON 课程.课程号 =选课.课程号WHERE 系.系号=012AND convert(varchar(10),选课时间,120)=2010-01-01（1）在查找原因时发现建表语句有问题。请指出问题并说明该问题是否会影响此查询语句的执行效率。（4分）（2）设已在 选课 表的 选课时间 列及 学生 表的 所在系号 列上建

39、立了索引。请问这两个索引是否能够提高该查询语句的执行效率？如果不能，请说明原因。（4分）建表时没有设置主键，也没有说明外键，但不会影响此查询语句的执行效率。选课 表的 选课时间 可以建立索引，从而提高查询效率，而学生 表的 所在系号 建立索引不会提高查询效率。索引的意义就是将记录按目标关键字顺序排列，这样查找某个目标关键字的对应值的位置就缩小名师资料总结-精品资料欢迎下载-名师精心整理-第 11 页，共 17 页 -了查找范围。选课时间 的重复率低，所以可以作为索引，而学生所在系号 的重复率太高，则不会提高查询效率。10、某商场商品经营管理系统使用SQL Server 2008 数据库管理系统

40、，此系统上线运行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 销售明细(商品号);某技

41、术人员提出通过执行下述语句以提高此查询的运行效率：CREATE VIEW 商品销售额视图WITH SCHEMABINDING AS SELECT 商品号,SUM(销售数量*销售价格)销售额,COUNT_BIG(*)cnt FROM dbo.销售明细GROUP BY 商品号;CREATE UNIQUE CLUSTERED INDEX ix_商品销售额ON 商品销售额视图(商品号);（10分）（1）请分析该技术人员给出的语句功能以及对原有查询语句的性能影响，并给出原因。语句功能：建立包含(商品号，销售额，该商品表中出现次数)带索引的视图，并建立按商品号对应销售额UNIQUE 聚簇排序的索引，大大缩

42、小了查询语句的查询范围，提高了查询效率。原因：视图中将间接相关的属性列（序列号，销售日期，商品号，销售数量，销售价格）转换成了目标属性列，减少了搜索空间；同时建立 UNIQUE CLUSTERED索引，使查询商品号的数据记录唯一，降低了搜索范围，提高了搜索效率。（2）此商场的销售量很大，每天有大量数据插入到销售明细表中。请从数据库整体性能角度分析，此技术人员提出的优化方法是否合适，并给出原因。不合适，每天大量的插入操作使得在修改表的同时也要对视图进行修改，增加了系统的负担，然而该统计功能一个月才用一次，这样导致系统的利用率也较为低下。11、某教务管理系统使用SQL Server 2008 数据

43、库管理系统，数据库软硬件配置信息如下：数据库运行在两路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 数据库的特性以及题目中的条件，综合给出以下的调优方案

44、。名师资料总结-精品资料欢迎下载-名师精心整理-第 12 页，共 17 页 -(1)表结构优化：重新优化数据库设计结构，规范数据库逻辑设计；设计主键和外键；设计合适大小的字段。(2)硬件优化：购买一块同样大小的硬盘，将硬盘做成RAID5,用以提高数据库读写速度；增加服务器 CPU个数；扩大服务器的内存。(3)索引优化：采用对经常作为条件查询的列设计索引，在查询中经常用到的列上建立非聚簇索引，在频繁进行范围查询、排序、分组的列上建立聚簇索引，对于有频繁进行删除、插入操作的表不要建立过多的索引。(4)采用视图：合理使用视图和分区视图，在需要更新和删除操作不多、查询操作频繁的表上建立索引视图。(5)

45、SQL 语句优化：选择运算应尽可能先做，并在对同一个表进行多个选择运算时，选择影响较大的语句放在前面，较弱的选择条件写在后面，这样就可以先根据较严格的条件得出数据较少的信息，再在这些信息中根据后面较弱的条件得到满足条件的信息。应避免使用相关子查询，把子查询转换成联结来实现。字段提取按照 需多少，提多少的原则，避免 SELECT*，SELECT*需要数据库返回相应表的所有列信息，这对于一个列较多的表无疑是一项费时的操作，采用存储过程，使用存储过程提高数据处理速度。12、某采购中心采购了一套商品批发查询管理系统，此系统采用SQL Server 2008数据库管理系统，该系统需要经常处理百万级以上的

46、数据查询。同时该系统提供了第三方人员开发的SQL 接口，第三方人员可以根据自己的需要开发自己的应用程序来访问数据库中的相关数据。（10分）(1)系统在使用的过程中，业务人员反应系统操作速度很慢。经过工程师检查测试后，数据库系统本身及网络传输过程中存在着一些问题，请给出针对数据库系统本身及网络传输过程中可能的一些调优方案。(2)工程师在完成数据库系统本身和网络问题的优化后，发现第三方开发人员的SQL 查询语句存在很多没有优化的问题，请从第三方开发人员角度给出一些优化方案。(1)把数据、日志、索引放到不同的I/O 设备上，增加读取速度，数据量(尺寸)越大，提高 I/O 越重要。纵向、横向分割表，减

47、少表的尺寸。升级硬件，扩大服务器的内存,Windows 2000 和 SQL Server 2000 能支持 4-8G的内存。配置虚拟内存，虚拟内存大小应基于计算机上并发运行的服务进行配置。增加服务器 CPU个数。分布式分区视图可用于实现数据库服务器联合体。联合体是一组分开管理的服务器，但它们相互协作分担系统的处理负荷。这种通过分区数据形成数据库服务器联合体的机制能够扩大一组服务器，以支持大型的多层 Web 站点的处理需要。重建索引：DBCC REINDEX,DBCC INDEXDEFRAG；收缩数据和日志：DBCC SHRINKDB,DBCC SHRINKFILE。设置自动收缩日志，对于大的

48、数据库不要设置数据库自动增长，它会降低服务器的性能。优化锁结构。(2)对查询进行优化，尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。应尽量避免在 where 子句中使用!=或操作符，否则引擎将放弃使用索引而进行全表扫描。任何地方都不要使用 select*from 进行全表扫描，用具体的字段列代替*，不要返回冗余字段。避免频繁创建和删除临时表，以减少系统表资源的消耗。尽量避免使用游标，因为游标的效率较差，如果游标操作的数据超过1万行，那么就应该考虑改写。尽量避免大事务操作，提高系统并发能力。13、6-32008 数据库管理系统。此系统上线运行前，需将商场原

49、有的数据导入到新系统中。原有系统使用SQL Server 2000，数据结构与新系统不完全一致。因此需要把数据从SQL Server 2000 导入到 SQL Server 2008中，为了保证数据一致性，数据导入过程中要求暂停业务且必须在3小时内完成。（1）在原有数据导入新系统的过程中，实施人员发现原有数据量很大，导入数据需要四小时，业务无法接受。经分析某工程师认为，数据导入过程中的数据库I/O 很高，但导入数据的程序本身对系统资源占用率很低。该工程师建议将数据导入过程中的数据恢复模式从完整 模式改为 简单 模式以提高数据导入速度；而另一位工程师则认为此方法未必能提高数据导入速度，而且还可能

50、导致数据丢失，不建议使用此方名师资料总结-精品资料欢迎下载-名师精心整理-第 13 页，共 17 页 -法。请分析此方法是否能够提高数据导入速度并给出理由，同时分析此操作的数据丢失风险。（5分）（2）在成功导入历史数据后，此系统顺利上线运行。在上线运行的第一周，发现数据库服务器的CPU 使用率很高，达到近90%，高峰期间达到100%，且系统内存占用率达到90%，但系统 I/O 很轻。业务人员反应系统操作速度很慢。为了提高系统运行速度。在不修改应用程序的前提下，两位工程师提出了不同的解决办法：.为服务器增加 2颗CPU，缓解 CPU 使用率很高的问题；.为服务器增加一倍内存，缓解内存使用率很高的