【计算机类职业资格】计算机三级数据库技术-50及答案解析.doc

1、计算机三级数据库技术-50 及答案解析(总分：100.00，做题时间：90 分钟)一、设计与应用题(总题数：3，分数：100.00)设某连锁商店数据库中有关系模式 R： R(商店编号，商品编号，库存数量，部门编号，负责人) 如果规定：每个商店的每种商品只在一个部门销售，每个商店的每个部门只有一个负责人，每个商店的每种商品只有一个库存数量。（分数：50.00）(1).请根据上述规定，写出关系模式 R的函数依赖集；（分数：12.50）_(2).请给出关系模式 R的候选码；（分数：12.50）_(3).请说明关系模式 R属于第几范式，并给出理由；（分数：12.50）_(4).请将 R分解成满足 3N

2、F的关系模式。（分数：12.50）_设在 SQL Server 2008某数据库中，已建立了四个文件组：fg1、fg2、fg3 和 fg4，以及一个分区函数RangePF1。RangePF1 的定义代码如下： CREATE PARTITION FUNCTION RangePF1(int) AS RANGE LEFT FOR VALUES(100,200,300)（分数：25.00）(1).请基于 RangePF1创建一个分区方案 RangePS1，每个分区对应一个文件组。（分数：12.50）_(2).请使用 RangePS1创建一个分区表 PartitionT(Gid, GoodName, P

3、rice)，该表基于 Gid列创建分区。其中 Gid：int 类型，取值大于等于 1；GoodName：统一字符编码可变长类型，最多可存储 20个汉字；Price：定点小数类型，小数部分 1位，整数部分到千位。（分数：12.50）_某商场商品经营管理系统使用 SQL Server 2008数据库管理系统，此系统上线运行 1年后，业务人员使用某统计功能(此功能每月使用一次)时发现速度很慢。该统计功能主要执行的 SQL语句如下： SELECT 商品号, SUM(销售数量*销售价格) 销售额 FROM 销售明细 GROUP BY 商品号; 该销售明细表的建表语句如下： CREATE TABLE 销售

4、明细( 序列号 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(*) cnt FROM dbo.销售明细 GRO

5、UP BY 商品号; CREATE UNIQUE CLUSTERED INDEX ix_商品销售额 ON 商品销售额视图(商品号);（分数：25.00）(1).请分析该技术人员给出的语句功能以及对原有查询语句的性能影响，并给出原因。（分数：12.50）_(2).此商场的销售量很大，每天有大量数据插入到销售明细表中。请从数据库整体性能角度分析，此技术人员提出的优化方法是否合适，并给出原因。（分数：12.50）_计算机三级数据库技术-50 答案解析(总分：100.00，做题时间：90 分钟)一、设计与应用题(总题数：3，分数：100.00)设某连锁商店数据库中有关系模式 R： R(商店编号，商品编

6、号，库存数量，部门编号，负责人) 如果规定：每个商店的每种商品只在一个部门销售，每个商店的每个部门只有一个负责人，每个商店的每种商品只有一个库存数量。（分数：50.00）(1).请根据上述规定，写出关系模式 R的函数依赖集；（分数：12.50）_正确答案：()解析：(商店编号，商品编号)-部门编号，(商店编号，部门编号)-负责人，(商店编号，商品编号)-库存量。解析 函数依赖定义：设 R(U)是属性集 U上的关系模式，X，Y 是 U的子集，若对于 R(U)的任意一个可能的关系 r，r 中不可能存在两个元组在 X上的属性值相等，在 Y上的属性值不等，则称 X函数确定 Y或 Y函数依赖 X，记作

7、X-Y。函数依赖是指关系 R的一切关系均要满足的约束条件。(2).请给出关系模式 R的候选码；（分数：12.50）_正确答案：()解析：(商店编号，商品编号)解析 设 K为 RU,F中的属性或属性组合，若 U完全依赖于 K，则 K为R的候选码。(3).请说明关系模式 R属于第几范式，并给出理由；（分数：12.50）_正确答案：()解析：第一范式解析 关系数据库是要满足一定要求的。满足最低要求的叫第一范式，在第一范式中满足进一步要求的为第二范式，其余以此类推。显然该关系模式满足第一范式，接下来检查其是否满足第二范式。在第二范式中，要求关系模式中不存在部分依赖，每一个非主属性完全依赖于码，而根据第

8、一空可得如下依赖关系：(部门编号，商店编号)-负责人，所以属于第一范式。它的非主属性有 3个(不包含在任何候选码中的属性)：部门编号、负责人、库存量，并都完全函数依赖于主码。将(商店编号、商品编号)记作 X，(商店编号、部门编号)记作 Y，负责人记作 Z，由此可以看出，存在传递依赖，故不属于第三范式。(4).请将 R分解成满足 3NF的关系模式。（分数：12.50）_正确答案：()解析：R1(商店编号、商品编号、部门编号、库存量)；R2(商店编号、部门编号、负责人)XY、YZ。解析 第三范式中要求每一个属性既不部分依赖于码也不传递依赖于码。设在 SQL Server 2008某数据库中，已建立

9、了四个文件组：fg1、fg2、fg3 和 fg4，以及一个分区函数RangePF1。RangePF1 的定义代码如下： CREATE PARTITION FUNCTION RangePF1(int) AS RANGE LEFT FOR VALUES(100,200,300)（分数：25.00）(1).请基于 RangePF1创建一个分区方案 RangePS1，每个分区对应一个文件组。（分数：12.50）_正确答案：()解析：create partition scheme RangePS1 as partition RangePF1 to (fg1, fg1, fg1, fg2) 解析 数据实际

10、上是依附于表而存在，我们将表放入到文件组中，而文件组是一个逻辑的概念，其实体是辅助数据库文件(ndf)，所以就等于将我们指定的数据放入到了指定的辅助数据库文件中，然后将这些辅助数据库文件放入不同的磁盘分区中，就可以有针对性的对相应的数据实现性能的优化。(2).请使用 RangePS1创建一个分区表 PartitionT(Gid, GoodName, Price)，该表基于 Gid列创建分区。其中 Gid：int 类型，取值大于等于 1；GoodName：统一字符编码可变长类型，最多可存储 20个汉字；Price：定点小数类型，小数部分 1位，整数部分到千位。（分数：12.50）_正确答案：()

11、解析：创建分区表： create table orders ( GID int identity(1,1) primary key, GoodName varchar(40), Price float ) on RangePS1(GID) 解析 创建分区表可通过以下几个步骤实现： 创建分区函数 创建分区方案 使用分区方案创建表某商场商品经营管理系统使用 SQL Server 2008数据库管理系统，此系统上线运行 1年后，业务人员使用某统计功能(此功能每月使用一次)时发现速度很慢。该统计功能主要执行的 SQL语句如下： SELECT 商品号, SUM(销售数量*销售价格) 销售额 FROM 销

12、售明细 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(销售

13、数量*销售价格) 销售额, COUNT_BIG(*) cnt FROM dbo.销售明细 GROUP BY 商品号; CREATE UNIQUE CLUSTERED INDEX ix_商品销售额 ON 商品销售额视图(商品号);（分数：25.00）(1).请分析该技术人员给出的语句功能以及对原有查询语句的性能影响，并给出原因。（分数：12.50）_正确答案：()解析：语句功能：建立包含(商品号，销售额，该商品表中出现次数)带索引的视图，并建立按商品号对应销售额 UNIQUE聚簇排序的索引，大大缩小了查询语句的查询范围，提高了查询效率。原因：视图中将间接相关的属性列(序列号，销售日期，商品号，销

14、售数量，销售价格)转换成了目标属性列，减少了搜索空间；同时建立 UNIQUE CLUSTERED索引，使查询商品号的数据记录唯一，降低了搜索范围，提高了搜索效率。解析 该技术人员使用了带有索引的视图，将所关心的数据(商品号，销售额，该商品号在表中出现的次数)从销售明细表中提取出来建立视图，并对该视图建立按商品号排序的聚簇索引，这样大大减少了在搜索不同商品的销售额时调用的数据表的规模，从而提高了查询效率。由于表的数据规模很大，建立该视图后，同一种商品不会多次出现在表中，而是通过一个计数变量 cnt表示，这样在检索时大大减少了检索规模。创建索引时，UNIQUE 关键字表明此索引的每一个索引值只对应

15、唯一的数据记录。CLUSTER 表示要建立的索引是聚簇索引，所谓聚簇索引是指索引项的顺序与表中记录的物理顺序一致的索引组织。(2).此商场的销售量很大，每天有大量数据插入到销售明细表中。请从数据库整体性能角度分析，此技术人员提出的优化方法是否合适，并给出原因。（分数：12.50）_正确答案：()解析：不合适，每天大量的插入操作使得在修改表的同时也要对视图进行修改，增加了系统的负担，然而该统计功能一个月才用一次，这样导致系统的利用率也较为低下。解析 由于视图是不实际存储数据的虚表，因此对视图的更新最终要转换为对基本表的更新。而用户通过视图对数据进行增加、删除、修改时，有意或无意地对不属于视图范围内的基本表数据进行操作，会破坏数据的一致性。而且视图中的数据本身就是冗余的，每次对表进行修改时，同时也要对相应的视图进行修改，这大大增加了系统的负担。