2014年3月计算机三级数据库大题汇总

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

2、关系模式的主码和外码。（5分）2设有商品表（商品号，商品名，单价）和销售表（销售单据号，商品号，销售时间，销售数量，销售单 价九其中，商品号代表一类商品，商品号、单价、销售数量和销售单价均为整型。请编写查询某年某商品的销售总毛利的存储过程，毛利=销售数量X（销售单价一单价）。要求商品号和年 份为输入参数，总毛利用输出参数返回。（10分）3设某全国性的运输企业建立了大型OLTP系统，并在该系统之上建立了数据仓库。OLTP系统和数据仓库中 有如下数据表：运输明细表（运输单ID，发送站ID，终到站ID，货物ID，货物重量，运输价格，发货日期）汇总表1（发送站ID，终到站ID，货物ID，发货日期，总重

3、，总运价）汇总表2 （发送站ID，终到地区ID，货物ID，发货日期，总重，总运价）汇总表3 （发送站ID，终到站ID，货物ID，发货月份，总重，总运价）汇总表4 （发送地区ID，终到地区ID，货物类别ID，发货日期，总重，总运价）该企业管理的货运站约有100个，货物约有500种共10类，各汇总表都建有主码，且各表有合理的维护策略， 在每次维护后数据能保持一致。设有视图V，该视图的访问频率很高，其查询结果模式为（发送地区ID，终 到站ID，发货月份，总重，总运价），该视图现以汇总表1为计算数据源。经监控发现，汇总表1的被访问频 率过高，导致系统整体性能下降，而其它汇总表被访问频率较低。在不增加汇

4、总表和索引的情况下，请给 出一个改善系统服务性能的优化方案，并简要说明理由。（10分）1已知某教务管理系统的设计人员在需求分析阶段收集到下列原始数据表格:教师教师号教师名职称工资上吸领导教师号9868王文华教授8000null9983李_斌副教授6000986899R5T-讲师400098680783王润洋讲师40009868。柳欧阳丹妮浙师40009868课程课程号课程名学分救材号教材名出版社名任课教师号C2006计算机原理311计算机原理清华大学出版社9868C2006计算机原理312计算机原理与应用高等教育出版社9868C2004数据结构313数据结构清华大学出版社9868C2010数据

5、库原理314数据库原理清华大学出版社98680201。数据库原理315数据库原理与技术高等救育出版社986853001音乐欣赏216音乐欣翠清华大学出版社9983已知该业务系统存在如下规则：每个教师有唯一的教师号，每个教师号对应唯一的一名教师；每门课程有唯一的课程号，每个课程号对应唯一的一门课程；每本教材有唯一的教材号，每个教材号对应唯一的一本教材；每个教师最多只有一个上级领导，也可以没有上级领导；V一门课程仅由一名教师讲授；一本教材仅用于一门课程。（12分）（1）请根据原始数据表及业务规则，给出该系统的关系模式，保证每个关系模式满足3NF，并说明每个关 系模式的主码和外码。（2）画出该系统的

6、ER图，ER图中需给出每个实体集的属性，主码属性用下划线标识。2在SQL Server 2008中，设某数据库中有商品表（商品号，商品名，进货价格），商品号为主码；销售表 （商品号，销售时间，销售数量，销售价格，本次利润）商品号和销售时间为主码，销售价格为本次销售 商品的单价。现要求每当在销售表中插入前4列数据时（假设一次只插入一行数据），系统自动计算本次销 售产生的利润，并将该利润赋给销售表的第5列本次利润。请编写实现上述功能的后触发型触发器代码。（10 分）3在进行某学校教务管理系统的数据库设计时，数据库设计人员设计了如下几个关系模式：系(系号，系名)，系号为主码学生(学号，姓名，所在系号

7、)，学号为主码课程(课程号，课程名，开课系号)，课程号为主码选课(学号，课程号，选课时间)，学号和课程号为主码(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),开课系号var

8、char(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在查找原因时发现建表语句有问题。请指出问题并说明该问题是否会影响此查询语句的执行效率。(4分)设已在选课”表的选课时

9、间列及学生表的所在系号列上建立了索引。请问这两个索引是否能够 提高该查询语句的执行效率？如果不能，请说明原因。（4分）1设某连锁商店数据库中有关系模式R：R （商店编号，商品编号，库存数量，部门编号，负责人）如果规定：每个商店的每种商品只在一个部门销售，每个商店的每个部门只有一个负责人，每个商店的每种商品只有一个库存数量。（10分）（1）请根据上述规定，写出关系模式R的函数依赖集；（2）请给出关系模式R的候选码；（3）请说明关系模式R属于第几范式，并给出理由；（4）请将R分解成满足3NF的关系模式。2设在SQL Server 2008某数据库中，已建立了四个文件组：fgl、fg2、fg3和fg

10、4，以及一个分区函数 RangePFl。RangePFl的定义代码如下：CREATE PARTITION FUNCTION RangePFl（int）AS RANGE LEFT FOR VALUES（100,200,300）（1）请基于RangePF1创建一个分区方案RangePS1，每个分区对应一个文件组。（5分）（2）请使用RangePS1创建一个分区表PartitionT（Gid, GoodName, Price），该表基于Gid列创建分区。其 中Gid： int类型，取值大于等于1； GoodName：统一字符编码可变长类型，最多可存储20个汉字；Price： 定点小数类型，小数部分1

11、位，整数部分到千位。（5分）3某商场商品经营管理系统使用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）;并在销售明细表上建有如下索引

12、：CREATE index ix_销售明细商品号on销售明细（商品号）；某技术人员提出通过执行下述语句以提高此查询的运行效率：CREATE VIEW商品销售额视图WITH SCHEMABINDINGASSELECT商品号,SUM（销售数量*销售价格）销售额，COUNT_BIG（*） cntFROM dbo.销售明细GROUP BY商品号；CREATE UNIQUE CLUSTERED INDEX ix_ 商品销售额ON商品销售额视图（商品号）；（10分）（1）请分析该技术人员给出的语句功能以及对原有查询语句的性能影响，并给出原因。（2）此商场的销售量很大，每天有大量数据插入到销售明细表中。请从

13、数据库整体性能角度分析，此技术 人员提出的优化方法是否合适，并给出原因。1已知有如下关系模式：R1(a, b, c), R2(c, d, a), R3(e, f), R4(a, e, g)，其中标下划线的属性是 主码。请将上述关系模式用适当的ER图表示出来，并用下划线标注出作为实体主码的属性。(10分)2设在SQL Server 2008某数据库中有商品表和销售表，两个表的定义如下：CREATE TABLE 商品表(商品号 char(10)PRIMARY KEY,商品名 varchar(40),类别 varchar(20),进货单价int )CREATE TABLE 销售表(商品号char(1

14、0),销售时间datetime,销售数量int,销售单价int,PRIMARY KEY(商品号，销售时间)下面是一个用户定义的多语句表值函数，它接受类别作为输入参数，返回该类别下的每种商品在2012年的销售总利润，并将结果按照销售总利润的降序输出。请补全该函数定义代码。(10分)CREATE FUNCTION f_Profit (lb char(10)【1】ProfitTable 【2】(商品号char(10),总利润int )ASBEGININSERT INTO ProfitTable【3】【4】END3某教务管理系统使用SQL Server 2008数据库管理系统，数据库软硬件配置信息如下

15、：I .数据库运行在两路 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 分）1现有论文和作者两个实体，论文实体的属性包括题目、期刊名称、年份、期刊号；作者实体的属性包括 姓名、单位、地址；一篇论文可以

16、有多个作者，且每一位作者写过多篇论文，在每一篇论文中有作者的顺 序号。请完成以下操作：（10分）（1）画出E-R图。（2）将E-R图转换成关系模式，并指出每个关系模式的主键（加下划线）和外键（加波浪线）。2设有图书管理数据库，包含三张表：图书明细表（图书编号，图书类别，图书名称，作者，出版社，出版日期，定价）；读者表（借书证号，姓名，系别，办证日期）；借出信息表（借出编号，借书证号，图书编号，借书日期）；完成下列操作：（10分）定义一个多语句表值函数，用于查询学生借书情况，只需提供参数：借书证号，就可以通过调用函数返回 此学生的借书情况，若有借书籍，则返回所借书籍的编号、书籍名称、定价和借书日

17、期；若没有在借书籍， 则返回记录为空。设函数名为：f_BorrowBook（jszh char（20）。3某采购中心采购了一套商品批发查询管理系统，此系统采用SQL Server 2008数据库管理系统，该系统 需要经常处理百万级以上的数据查询。同时该系统提供了第三方人员开发的SQL接口，第三方人员可以根据 自己的需要开发自己的应用程序来访问数据库中的相关数据。（10分）（1）系统在使用的过程中，业务人员反应系统操作速度很慢。经过工程师检查测试后，数据库系统本身及网 络传输过程中存在着一些问题，请给出针对数据库系统本身及网络传输过程中可能的一些调优方案。（2）工程师在完成数据库系统本身和网络问

18、题的优化后，发现第三方开发人员的SQL查询语句存在很多没有 优化的问题，请从第三方开发人员角度给出一些优化方案。1设有高校选课系统，需要对学校的系信息、教师信息、课程信息、学生信息、学生选课信息进行管理。 已知系（DEPT）信息包括系编号（DeptNO）、系名称（DeptName）;教师（Teacher）信息包括教师号（TNO）、 教师名（Tname）;课程（Course）信息包括课程号（CNO）、课程名（CName）、课程学分（Credit）;学生 （Student）信息包括学号（SNO）、学生姓名（Sname）、学生性别（Sex）。选课系统的管理规则如下：一个系可聘用多名教师，一个教师只受

19、聘于一个系；一个系可有多名学生，一个学生只属于一个系；一名教师可讲授多门课程，一门课程可由多名教师讲授；一名学生可选修多门课程，一门课程可被多名学生选修；学生选修完课程后，可获得相应课程的成绩。针对以上描述，完成下列设计内容：（1）构建选修课系统的ER图。（要求图中的实体集名用试卷中给出的英文名，联系所关联的实体集名的首字 母，字母间用-或_连接，大小写不限九（6分）（2）根据所构建的ER图，设计满足3NF的关系模式，并标出每个关系模式的主码和外码。（要求关系模式名同 实体集名或联系名，属性名用试卷中给出的英文名，大小写不限）（4分）2某书店采用了 SQL Server 2008数据库管理系统

20、，该书店有一个需求，需要统计指定年份中每一本书的 销售总额，例如：查询2012年所有书的销售总额。已知图书结构如下：图书表（书号BOOK_ID，书名BOOK_NAME，单价BOOK_PRICE）销售表（书号BOOK_ID，销售时间SALE_TIME，销售数量SALE_NUM）。假设单价和销售数量均为int型，书号和书名均为varchar（50）类型，销售时间为datetime型。请给出满足如下要求的多语句表值函数，该函数统计指定年份中每本书的销售总额。（10分）设函数名为：BOOK_PROFIT（year int），函数的返回结果格式如下：书号销售总额B001 60000A0045000032

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

22、则认为此方法未必能提高数据导入速度，而且还可能导致数据丢失，不建议使用此方法。 请分析此方法是否能够提高数据导入速度并给出理由，同时分析此操作的数据丢失风险。（5分）（2）在成功导入历史数据后，此系统顺利上线运行。在上线运行的第一周，发现数据库服务器的CPU使用 率很高，达到近90%，高峰期间达到100%，且系统内存占用率达到到90%，但系统I/O很轻。业务人员反应系统 操作速度很慢。为了提高系统运行速度。在不修改应用程序的前提下，两位工程师提出了不同的解决办法：I.为服务器增加2颗CPU，缓解CPU使用率很高的问题；II .为服务器增加一倍内存，缓解内存使用率很高的问题。考虑成本，现阶段只能按照一种方案实施。请指出在现有情况下，哪种方案更合