2020年计算机三级数据库分析真题.doc

xx年计算机三级数据库分析真题 Q1: 设某全国性的运输企业建立了大型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的被访问频率过高，导致系统整体性能下降，而其它汇总表被访问频率较低。在不增加汇总表和索引的情况下，请给出一个改善系统服务性能的优化方案，并简要说明理由。 A1: 由于汇总表1和视图的模式访问频率都很高，而且视图的数据源汇总表1，又因为其他汇总表的访问率较低，所以只需要将视图的数据源绑定为汇总表3，因为汇总表3也可以满足视图的输出模式。这样不仅提升了汇总表3的数据访问率，而且降低了汇总表1的数据访问率，系统性能和服务性能得到了很大的优化。又因为货物约有500种，共10类，可以再建立一个视图绑定数据源为汇总表4，这样就可以充分利用汇总表4的数据信息，从而可以进一步完善系统性能的优化。 Q2: 在进行某学校教务管理系统的数据库设计时，数据库设计人员设计了如下几个关系模式： 系(系号，系名)，系号为主码 学生(学号，姓名，所在系号)，学号为主码 课程(课程号，课程名，开课系号)，课程号为主码 选课(学号，课程号，选课时间)，学号和课程号为主码(8分) 开发人员在将关系模式实施到SQLServerxx的教务数据库时，使用了如下表结构定义语句： CREATETABLE系( 系号varchar(10)NOTNULL, 系名varchar(100) CREATETABLE学生( 学号varchar(50)NOTNULL, 姓名varchar(50), 所在系号varchar(10) CREATETABLE课程( 课程号varchar(50)NOTNULL, 课程名varchar(100), 开课系号varchar(10) CREATETABLE选课( 学号varchar(50)NOTNULL, 课程号varchar(50)NOTNULL, 选课时间datetime) 在执行如下查询语句时发现执行效率很低： SELECT*FROM选课JOIN学生ON学生.学号=选课.学号 JOIN系ON系.系号=学生.所在系号 JOIN课程ON课程.课程号=选课.课程号 WHERE系.系号=012 ANDconvert(varchar(10),选课时间,120)=xx-01-01 (1)在查找原因时发现建表语句有问题。请指出问题并说明该问题是否会影响此查询语句的执行效率。(4分) (2)设已在选课表的选课时间列及学生表的所在系号列上建立了索引。请问这两个索引是否能够提高该查询语句的执行效率?如果不能，请说明原因。(4分) A2: (1)【解题思路】 本题中查询语句的功能是得到12系全体学生在xx年1月1日后的选课情况的汇总表。在每个数据表的定义时都必须严格定义表中的完整性约束条件，包括主键的设置，否则之后会出现主键有相同值的情况，破坏了数据的完整性。 【参考答案】 建表时没有设置主键，也没有说明外键。但不会影响此查询语句的执行效率 (2)【解题思路】 建立索引是加快查询速度的有效手段，用户可以根据应用环境的需要建立一个或多个索引，以提供多种存取路径，加快查找速度。索引就像书的目录一样为我们将记录按规定的列进行了排序，这样当我们要访问满足这些列的某些条件的记录时，索引会为我们减少查询数据库中的地址范围，大大节省了时间。所以根据WHERE条件后的字段对表建立索引对于提高查询效率是有帮助的。 【参考答案】 选课表的选课时间列上建立了索引，从而能够提高执行效率。经常出现在Where子句中的字段，特别是大表的字段，应该建立索引。索引的作用就类似于书的目录，即会按照章节的顺序排列。因此如果在一本数百页的书里面查找某个章节位置的时候，就可以只扫描书的目录。扫描的范围缩了n倍，查询的效率自然就会提高。另外，在SQLServer内存够用的情况下，索引会被放到内存中，在内存中查找自然又会提高效率，所以必须合理利用索引。 Q3: 某商场商品经营管理系统使用SQLServerxx数据库管理系统，此系统上线运行1年后，业务人员使用某统计功能(此功能每月使用一次)时发现速度很慢。该统计功能主要执行的SQL语句如下： SELECT商品号,SUM(销售数量*销售价格)销售额 FROM销售明细 GROUPBY商品号; 该销售明细表的建表语句如下： CREATETABLE销售明细( 序列号intIDENTITY(1,1)NOTNULL, 商品号intNOTNULL, 销售日期datetimeNULL, 销售数量intNOTNULL, 销售价格intNOTNULL ); 并在销售明细表上建有如下索引： CREATEindexix_销售明细_商品号on销售明细(商品号); 某技术人员提出通过执行下述语句以提高此查询的运行效率： CREATEVIEW商品销售额视图 WITHSCHEMABINDING AS SELECT商品号,SUM(销售数量*销售价格)销售额, COUNT_BIG(*)t FROMdbo.销售明细 GROUPBY商品号; CREATEUNIQUECLUSTEREDINDEXix_商品销售额 ON商品销售额视图(商品号);(10分) (1)请分析该技术人员给出的语句功能以及对原有查询语句的性能影响，并给出原因。 (2)此商场的销售量很大，每天有大量数据插入到销售明细表中。请从数据库整体性能角度分析，此技术人员提出的优化方法是否合适，并给出原因。 A3: (1)【解题思路】 该技术人员使用了带有索引的视图，将所关心的数据(商品号，销售额，该商品号在表中出现的次数)从销售明细表中提取出来建立视图，并对该视图建立按商品号排序的聚簇索引，这样大大减少了在搜索不同商品的销售额时调用的数据表的规模，从而提高了查询效率。由于表的数据规模很大，建立该视图后，同一种商品不会多次出现在表中，而是通过一个计数变量t表示，这样在检索时大大减少了检索规模。创建索引时，UNIQUE关键字表明此索引的每一个索引值只对应唯一的数据记录。CLUSTER表示要建立的索引是聚簇索引，所谓聚簇索引是指索引项的顺序与表中记录的物理顺序一致的索引组织。 【参考答案】 语句功能：建立包含(商品号，销售额，该商品表中出现次数)的带索引的视图，并建立按商品对应销售额UNIQUE聚簇排序的索引，大大缩小了查询语句的查询范围，提高了查询效率。原因：视图中将间接相关的属性列(序列号，销售日期，商品号，销售数量，销售价格)转换成了目标属性列，减少了搜索空间;同时建立UNIQUECLUSTERED索引，使查询商品号的数据记录唯一，降低了搜索范围，提高了搜索效率。 (2)【解题思路】 由于视图是不实际存储数据的虚表，因此对视图的更新最终要转换为对基本表的更新。而用户通过视图对数据进行增加、删除、修改时，有