1、计算机三级数据库技术-53 及答案解析(总分：99.00，做题时间：90 分钟)一、设计与应用题(总题数：3，分数：99.00)1.已知有如下关系模式：R1(a, b, c), R2(c, d, a), R3(e, f), R4(a, e, g)，其中标下划线的属性是主码。请将上述关系模式用适当的 ER 图表示出来，并用下划线标注出作为实体主码的属性。 （分数：33.00）_2.设在 SQL Server 2008 某数据库中有商品表和销售表，两个表的定义如下： CREATE TABLE 商品表( 商品号 char(10)PRIMARY KEY, 商品名 varchar(40), 类别 var
2、char(20), 进货单价 int ) CREATE TABLE 销售表( 商品号 char(10), 销售时间 datetime, 销售数量 int, 销售单价 int, PRIMARY KEY(商品号,销售时间) 下面是一个用户定义的多语句表值函数，它接受类别作为输入参数，返回该类别下的每种商品在 2012 年的销售总利润，并将结果按照销售总利润的降序输出。请补全该函数定义代码。 CREATE FUNCTION f_Profit (lb char(10) _ ProfitTable _ ( 商品号 char(10), 总利润 int ) AS BEGIN INSERT INTO Prof
3、itTable _ _ END （分数：33.00）_3.某教务管理系统使用 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。 近一个学期以来，用户反映系统运行缓慢，经程序员定位，确定
4、为数据库服务器响应缓慢，需要进行调优。（分数：33.00）_计算机三级数据库技术-53 答案解析(总分：99.00，做题时间：90 分钟)一、设计与应用题(总题数：3，分数：99.00)1.已知有如下关系模式：R1(a, b, c), R2(c, d, a), R3(e, f), R4(a, e, g)，其中标下划线的属性是主码。请将上述关系模式用适当的 ER 图表示出来，并用下划线标注出作为实体主码的属性。 （分数：33.00）_正确答案：()解析：可以通过以下 ER 图来表示。a、c 为 A 的候选码，可任选其一做主码。 解析 ER 模型和关系模式相互转换的一般规则如下： (1)将每一个实
5、体类型转换成一个关系模式，实体的属性为关系模式的属性。 (2)对于二元联系，按各种情况处理，如下表格所示。 2.设在 SQL Server 2008 某数据库中有商品表和销售表，两个表的定义如下： CREATE TABLE 商品表( 商品号 char(10)PRIMARY KEY, 商品名 varchar(40), 类别 varchar(20), 进货单价 int ) CREATE TABLE 销售表( 商品号 char(10), 销售时间 datetime, 销售数量 int, 销售单价 int, PRIMARY KEY(商品号,销售时间) 下面是一个用户定义的多语句表值函数，它接受类别作为
6、输入参数，返回该类别下的每种商品在 2012 年的销售总利润，并将结果按照销售总利润的降序输出。请补全该函数定义代码。 CREATE FUNCTION f_Profit (lb char(10) _ ProfitTable _ ( 商品号 char(10), 总利润 int ) AS BEGIN INSERT INTO ProfitTable _ _ END （分数：33.00）_正确答案：()解析：(1)RETURNS (2)table (3)a SELECT a.商品号,SUM(销售数量*(销售单价-进货单价) AS 总利润 FROM 销售表 a JOIN 商品表 b ON a. 商品号=
7、b. 商品号 WHERE a.商品号 IN(SELECT 商品号 FROM 商品表 WHERE 类别=lb) GROUP BY a. 商品号 ORDER BY 总利润 DESC (4)RETURNRrofit Table 解析 用户定义的多语句表值函数的命令格式为： CREATE FUNCTION schema_name. function name ( parameter_name AStype_schema_nameparameter_data_type = default ,.n ) RETURNS return_variable TABLE table_type_definition
8、WITH function option , .n AS BEGIN function_body RETURN END ; table_type_definitions:= (column_definitioncolumn_constraint |computed_column_definition table_constraint,n ) 题目要求返回指定类别下的每种商品在 2012 年的销售总利润，并且将结果按照销售总利润的降序输出。采用复合 SQL 语句的格式，先查出指定类别的所有商品号：SELECT 商品号 FROM 商品表 WHERE 类别=lb，其中lb 为函数的传入参数，然后在销
9、售表中用 GROUP BY 对商品号进行分组，并采用 SUM 计算每个分组的总和。3.某教务管理系统使用 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、运行缓慢，经程序员定位，确定为数据库服务器响应缓慢，需要进行调优。（分数：33.00）_正确答案：()解析：根据 SQL Server 2008 数据库的特性以及题目中的条件，综合给出以下的调优方案。 (1)表结构优化：重新优化数据库设计结构，规范数据库逻辑设计；设计主键和外键；设计合适大小的字段。 (2)硬件优化：购买一块同样大小的硬盘，将硬盘做成 RAID5,用以提高数据库读写速度；增加服务器 CPU个数；扩大服务器的内存。 (3)索引优化：采用对经常作为条件查询的列设计索引，在查询中经常用到的列上建立非聚簇索引，在频繁进行范围查询、排序、分组的列上建立聚簇索引，对于有频繁进行删除、插入操
11、作的表不要建立过多的索引。 (4)采用视图：合理使用视图和分区视图，在需要更新和删除操作不多、查询操作频繁的表上建立索引视图。 (5)SQL 语句优化：选择运算应尽可能先做，并在对同一个表进行多个选择运算时，选择影响较大的语句放在前面，较弱的选择条件写在后面，这样就可以先根据较严格的条件得出数据较少的信息，再在这些信息中根据后面较弱的条件得到满足条件的信息。应避免使用相关子查询，把子查询转换成联结来实现。字段提取按照“需多少，提多少”的原则，避免“SELECT *”，“SELECT *”需要数据库返回相应表的所有列信息，这对于一个列较多的表无疑是一项费时的操作，采用存储过程，使用存储过程提高数
12、据处理速度。解析 数据库性能优化的基本原则就是通过尽可能少的磁盘访问获得所需要的数据。SQL SERVER 性能优化一般从数据库设计、应用程序编码、硬件优化、数据库索引、SQL 语句、事务处理几个方面入手考虑问题。 (1) 分析阶段：在系统分析阶段往往有太多需要关注的地方，系统各种功能性、可用性、可靠性、安全性需求吸引了我们大部分的注意力，但必须注意的是，性能往往是很重要的非功能性需求，必须根据系统的特点确定其实时性需求、响应时间的需求、硬件的配置等。最好能有各种需求量化的指标。 (2) 设计阶段：例如数据库逻辑设计规范化；合理的冗余；主键的设计；外键的设计；字段的设计；数据库物理存储和环境的
13、设计；数据库的物理存储、操作系统环境及网络环境的设计，皆使得我们的系统在将来能适应较多用户的并发操作和较大的数据处理量。 这里需要注意文件组的作用，适用文件组可以有效的把 I/O 操作分散到不同的物理硬盘，提高并发能力。 (3) 系统设计：整个系统的设计，特别是系统结构的设计对性能具有很大的影响。对于一般的 OLTP 系统，可以选择 C/S 结构、三层的 C/S 结构等，不同的系统结构其性能的关键也有所不同。 系统设计阶段应归纳些业务逻辑在数据库编程阶段实现，数据库编程包括数据库存储过程、触发器和函数。用数据库编程实现业务逻辑的好处是减少网络流量并能更充分利用数据库的预编译和缓存功能；索引设计
14、阶段可以根据功能和性能的需求进行初步的索引设计，这里需要根据预计的数据量和查询来设计索引，可能与将来实际使用时有所区别。 (4) 编码阶段：编码阶段首先需要所有程序员具备优化意识，也就是在实现功能的同时具备考虑优化性能的思想。数据库是能进行集合运算的工具，所谓集合运算实际是批量运算，即是尽量减少在客户端进行大数据量的循环操作，而用 SQL 语句或者存储过程代替。这个阶段主要是注意在 SQL 语句等方面的优化，如：尽量少做重复的工作，用 SELECT 后跟需要的字段代替 SELECT *语句，注意事务和锁 ，注意临时表和表变量的用法，慎用游标和触发器，尽量使用索引等。 (5) 硬件优化：RAID
15、 (独立磁盘冗余阵列)是由多个磁盘驱动器(一个阵列)组成的磁盘系统。通过将磁盘阵列当作一个磁盘来对待，基于硬件的 RAID 允许用户管理多个磁盘。使用基于硬件的 RAID 与基于操作系统的 RAID 相比较可知，基于硬件的 RAID 能够提供更佳的性能，如果使用基于操作系统的 RAID，那么它将占据其他系统需求的 CPU 周期，通过使用基于硬件的 RAID， 用户在不关闭系统的情况下能够替换发生故障的驱动器。利用数据库分区技术，可均匀地把数据分布在系统的磁盘中，平衡 I/O 访问，避免 I/O 瓶颈等。 (6) 事务处理调优：数据库的日常运行过程中，可能面临多个用户同时对数据库的并发操作而带来的数据不一 致的问题，如：丢失更新、脏读和不可重复读等。并发控制的主要方法是封锁，锁的含义即是在一段时间内禁止用户做某些操作以避免产生数据不一致。对于事务性能的调优，要考虑到事务使用的锁的个数(在所有其他条件相同的情况下，使用的锁个数越少，性能越好)、锁的类型 (读锁对性能更有利)以及事务持有锁的时间长短(持有时间越短，性能越好)等情形。
