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

1-1设某教学管理系统，其查询模块需要提供如下功能：

Ⅰ．查询系信息，列出各系编号、系名和系办公电话；

Ⅱ．查询某系教师的信息，列出教师号、教师名、工资和聘用日期；

Ⅲ．查询某教师讲授的课程信息，列出课程号、课程名和学分；

Ⅳ．查询讲授某门课程的教师信息，列出教师名和职称；

Ⅴ．查询某门课程的先修课程信息，列出先修课程号和先修课程名。

系统有如下业务规则：

Ⅰ．一个系可聘用多名教师，一名教师只能受聘于一个系；

Ⅱ．一名教师可讲授多门课程，一门课程可由多名教师讲授；

Ⅲ．一门课程可以有多门先修课程，也可以没有先修课程。

(1)请根据以上查询功能与业务规则，用ER图描述该系统的概念模型。（5分）

(2)将ER图转换为满足3NF的关系模式，并说明每个关系模式的主码和外码。（5分）

1-2设有商品表（商品号，商品名，单价）和销售表（销售单据号，商品号，销售时间，销售数量，销售单价）。其中，商品号代表一类商品，商品号、单价、销售数量和销售单价均为整型。

请编写查询某年某商品的销售总毛利的存储过程，毛利=销售数量×（销售单价－单价）。要求商品号和年份为输入参数，总毛利用输出参数返回。（10分）

1-3设某全国性的运输企业建立了大型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的被访问频率过高，导致系统整体性能下降，而其它汇总表被访问频率较低。在不增加汇总表和索引的情况下，请给出一个改善系统服务性能的优化方案，并简要说明理由。（10分）

2-1已知某教务管理系统的设计人员在需求分析阶段收集到下列原始数据表格：

已知该业务系统存在如下规则：

I．每个教师有唯一的教师号，每个教师号对应唯一的一名教师；

II．每门课程有唯一的课程号，每个课程号对应唯一的一门课程；

III．每本教材有唯一的教材号，每个教材号对应唯一的一本教材；

IV．每个教师最多只有一个上级领导，也可以没有上级领导；

V．一门课程仅由一名教师讲授；

VI．一本教材仅用于一门课程。(12分)

（1）请根据原始数据表及业务规则，给出该系统的关系模式，保证每个关系模式满足3NF，并说明每个关系模式的主码和外码。

（2)画出该系统的ER图，ER图中需给出每个实体集的属性，主码属性用下划线标识。

2-2在SQL Server 2008中，设某数据库中有商品表（商品号，商品名，进货价格），商品号为主码；销售表（商品号，销售时间，销售数量，销售价格，本次利润），商品号和销售时间为主码，销售价格为本次销售商品的单价。现要求每当在销售表中插入前4列数据时（假设一次只插入一行数据），系统自动计算本次销售产生的利润，并将该利润赋给销售表的第5列"本次利润"。请编写实现上述功能的后触发型触发器代码。（10分）

2-3在进行某学校教务管理系统的数据库设计时，数据库设计人员设计了如下几个关系模式：

系（系号，系名），系号为主码

学生（学号，姓名，所在系号），学号为主码

课程（课程号，课程名，开课系号），课程号为主码

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

（1）在查找原因时发现建表语句有问题。请指出问题并说明该问题是否会影响此查询语句的执行效率。（4分）

（2）设已在"选课"表的"选课时间"列及"学生"表的"所在系号"列上建立了索引。请问这两个索引是否能够