数据库模拟题综合整理Word格式文档下载.docx
《数据库模拟题综合整理Word格式文档下载.docx》由会员分享，可在线阅读，更多相关《数据库模拟题综合整理Word格式文档下载.docx（15页珍藏版）》请在冰豆网上搜索。
createtable订购单（
仓库号char（3）notnullprimarykey,
城市char（30）,
面积intcheck（面积>
0and面积<
1000））
3.给订购单表的增加“订购金额”字段,类型为money
altertable订购单addcolumn订购金额money
4.回收名为wang的用户对订购单关系的更新和删除权限
revokeupdate,deleteon订购单fromwang
5.检索在北京仓库工作的职工信息。
SELECT*FROM仓库，职工WHERE仓库.仓库号=职工.仓库号and地址=’北京’
6.检索发给供应商S6的订购单号。
SELECT订购单号FROM订购单WHERE供应商号=’S6’
7.检索出有最高工资的职工所有的仓库信息。
SELECT*FROM仓库WHERE仓库号in
（SELECT仓库号from职工where工资=（selectMAX（工资）FROM职工））
8.检索出每个城市的供应商个数。
SELECT地址,COUNT（*）FROM供应商GROUPBY地址
9.插入一个供应仓库记录，仓库号为WH1，城市为南宁，面积为560。
INSERTINTO仓库VALUES（‘WH1’,'
南宁'
560）
10.定义一个视图V_bj，包含北京仓库的职工信息
createviewv_bjas
select*from职工,仓库
where职工.仓库号=仓库.仓库号
and城市=’北京’
四、综合应用题（共20分）
1.（12分）设大学环境有学生实体,其属性有学号,姓名,性别,年龄和住址;
有实体类型系,有属性系编号,系名称,办公室地址,电话号码;
并有教师实体,有属性教师编号,姓名,性别,年龄,职称.一个学生只能注册一个系,有入学日期;
一个教师必须且只能为一个系工作，有任职日期
1绘出上述环境的E—R图,该图应该表明关键字属性,并注明联系类型,如M:
N.
2.将E—R图转换成对应的关系模式,指出每个关系的主键和外键
3.设计一个SQL查询语句,显示教师数大于12的系的系名称,教师人数。
1.E-R图如下：
（5分）
2.转换后有三个实体关系,两个联系因为均属一对多联系，故均在多的一边加上一的主键作为外键，（5分）
系（系编号,系名称,办公室地址,电话,……）
教师（教师编号,姓名,性别，年龄,职称，系编号，任职日期，……）
学生（学号,姓名,性别,年龄,住址,系编号，入学日期，……）
3.（2分）
select系名称,count（教师编号）
from系,教师
where系.系编号=教师.系编号
groupby系名称
havingcount（教师编号）>
12
2.（8分）现有如下关系模式：
R（U,F）,U={A，B，C，D，E},F={AB→E，B→C，C→D}
问：
1.该关系模式满足2NF吗?
为什么?
2.如果将关系模式R分解为：
R1（A，B，E）
R2（B，C，D）
指出关系模式R2的码，并说明该关系模式最高满足第几范式?
（在1NF～BCNF之内）。
3.将关系模式R分解到BCNF。
（1）R不属于第2NF（1分），因为主关键字AB,存在部分函数依赖BC；
（1分）
（2）R2的码是B（1分），最高满足2NF（1分）,因为存在非主属性D对主属性B的传递依赖，
故不满足3NF（1分）
（3）分解R为第三范式:
R1（A,B,E）
R2（B,C）
R3（C,D）（2分）
二、填空题（每空1分，共15分）
1、为保证关系模式分解的正确性必须遵循的两个原则分别是：
_无损连接性___性和_保持函数依赖性_。
3.在SQLServer服务器中，发行服务器扮演的是“存储和转发”的角色，在发行服务器上有发行数据库，它存储来出出版服务器的出版数据，并将这些数据转发到订阅服务器
4、现实世界中，实体之间联系的类型有如下几种_一对一联系、一对多联系、多对多联系。
5.用关系（表格数据）表示数据与数据之间联系的模型称之为_关系模型__。
6.SQLServer有的三种备份类型是全备份__、差额备份、表备份。
7.SQLServer提供了三种安全管理模式，分别是标准主证模式、集成认证、混合认证模式。
8.收回授权的SQL命令为_REVOKE__.
9.无损连接是指_分解后的关系经过自然连接可以恢复成原来的关系_
10.要实现集合的并操作，可以用UNION_操作。
11.为了保证关系的主关键字关系的实体完整性，最常用的手段是__主键约束
__。
三、问答题（每小题5分，共10分）1.什么是数据完整性？
关系数据库中数据完整性约束的分类。
（1）数据的完整性是指存储在数据库中的数据的一致性和正确性（1分）。
关系模型数据完整性的分类有
·
域完整性：
列的值域的完整性。
实体完整性：
表中记录的完整性。
引用完整性：
被引用表中的主关键字和引用表中的外部主关键字之间的关系。
用户自定义完整性：
（1分）
2. 什么是关系？
关系有什么性质？
一个关系是一个二维表，表中的每一行也即元组保存属于事物或某些事物的一部分的数据，相当于一个记录，表的每一列包含关于属性的数据。
关系应满足以下性质：
a.表格中的每一列都是不可再分的基本属性（1分）
b.各列被指定一个相异的名字（1分）
c.各行相异，不允许重复（1分）
d.行、列次序均无关（1分）
四、程序设计题（每小题4分，共40分）
现有如下图所示的关系数据库
（一）用关系代数表达式实现下列1—2小题
1.检索北京的供应商的名称
π供应商名（σ地址=‘北京’（供应商））
2.检索和北京的所有供应商都有联系的职工信息
R1＝σ地址＝'
北京'
（供应商）
R2＝Π供应商号（R1）
R3＝Π职工号，供应商号（订购单）
R4＝R3÷
R2
R5＝R4职工
R6＝Π工资（R5）
（二）用SQL语言实现下列3—10小题：
3.分析订购单表的各属性及取值范围，写出建立职工表的SQL语句
createtable订购单
（职工号char（3）,
供应商号char（3）,
订购单号char（6）notnull,
订购日期datetime）
4.把对职工关系的查询权限给用户zhang
grantselecton职工tozhang
5.检索在广州的供应商信息
select*from供应商where地址='
广州'
6.插入一个新的职工元组（WH3，E7，280）
insertinto职工values（'
WH3'
'
E7'
280）
7.删除仓库号为"
WH2"
的仓库.
deletefrom仓库where仓库号='
WH2'
8.给低于所有职工平均工资的职工提高5%的工资
update职工set工资=工资*1.05
where工资<
（selectavg（工资）from职工）
9.求所有职工的工资都多于1210的仓库的平均面积。
selectavg（面积）from仓库
where仓库号notin（select仓库号from职工
where工资<
=1210）
10.检索出与职工E1、E3都有联系的北京的供应商信息
select*from供应商
where地址='
and供应商号in
（select供应商号from订购单where职工号='
E1'
）
and供应商号in
E3'
五、应用题（每小题5分，共10分）．
1、编写存储过程，要求存储过程带一个参数，该存储过程可以查询指定城市的职工工资。
并执行该存储过程，查询指定城市在北京的职工工资。
createprocedureuspGetSalary（1分）
@citychar（10）（1分）
As
Select职工号，工资
from职工
Where职工号in
（select职工号from仓库where城市=@city）（2分）
ExecuteuspGetSalary’北京’
2、有关系模式R（U,F），属性集U={A,B,C,D,E}，函数依赖集合F={AB→C,C→D,B→E}，请回答：
关系R的主关键字是什么？
R最高属于第几范式？
说明理由；
将R转换为第三范式。
（1）R最高属于第一范式，因为存在部分函数依赖；
（2分）
（2）R的候选码是：
（A,B）（A,C）（1分）
（2分）
R1（A,B,C）
R2（C,D）
R3（B,E）
六、综合题（10分）
完成如下设计:
请设计某单位的库存管理数据库系统，系统要求如下：
存储供应商信息，库存零件信息，仓库信息，仓库管理员的信息。
该单位有多个仓库，每个仓库可以存放多种零件，一种零件可以存放在多个仓库中；
每个仓库只有一个仓库管理员，一个仓库管理员只能管理一个仓库；
每个供应商可以供应多种零件，同一种零件可以由多个供应商供应。
要求完成如下设计工作：
（1）画出该系统的E-R模型图，包括实体及实体之间的联系，并标出：
（a）实体的主要属性,（b）实体之间联系的主要属性；
（2）把E-R模型图转换为关系表（标明主关键字）。
（3）请定义一个视图，该视图的列信息包括零件名称以及零件所在仓库名称的信息。
E-R图如下：
（3分）
*实体关系表，下划线为主码：
（1）供应商表：
（供应商号，供应商名，……………）
（2）零件表：
（零件号，零件名，……………）
（3）仓库表：
（仓库号，仓库名，管理员号，…………）
（4）仓库管理员表：
*两个多对多的联系转换为两个关系表：
（1）供应表：
（供应商号，零件号，供应量，……………）
（2）存放表：
（仓库号，零件号，存放量，……………）
*定义一个视图，该视图的列信息包括零件名称以及零件所在仓库名称，
SQL语句如下：
CREATEVIEWpart
AS
SELECT零件名，仓库名
FROM零件表,仓库表，存放表
WHERE零件表.零件号=存放表.零件号
AND仓库表.仓库号=存放表.仓库号
1）检索在北京的供应商的名称。
SELECT*FROM供应商WHERE地址=’北京’
2）检索发给供应商S6的订购单号。
SELECT订购单号FROM订购单WHERE供应商号=’S6’
3）检索出职工E6发给供应商S6的订购单信息。
SELECT*FROM订购单WHERE供应商号=’S6’AND职工号=’E6’
4）检索出向供应商S3发过订购单的职工的职工号和仓库号。
SELECT职工号,仓库号FROM职工WHERE职工号IN
（SELECT职工号FROM订购单WHERE供应商号=’S3’）
5）检索出目前与S3供应商没有联系的职工信息。
SELECT*FROM职工WHERE职工号NOTIN
6）检索出目前没有任何订购单的供应商信息。
SELECT*FROM供应商WHERENOTEXISTS
（SELECT*FROM订购单WHERE供应商号=供应商.供应商号）
7）检索出和职工E1、E3都有联系的北京的供应商信息。
SELECT*FROM供应商WHERE供应商号IN
（SELECT供应商号FROM订购单WHERE职工号=’E1’）
AND供应商号IN
（SELECT供应商号FROM订购单WHERE职工号=’E3’）
8）检索出目前和华通电子公司有业务联系的每个职工的工资。
SELECT职工号,工资FROM职工WHERE职工号IN
（SELECT职工号FROM订购单WHERE供应商号IN
（SELECT供应商号FROM供应商WHERE供应商名=’华通电子公司’））
9）检索出与工资在1220元以下的职工没有联系的供应商的名称。
SELECT供应商名FROM供应商WHERE供应商号IN
（SELECT供应商号FROM订购单WHERE职工号NOTIN
（SELECT职工号FROM职工WHERE工资<
1220））
10）检索出向S4供应商发出订购单的仓库所在的城市。
SELECT城市FROM仓库WHERE仓库号IN
（SELECT仓库号FROM职工WHERE职工号IN
（SELECT职工号FROM订购单WHERE供应商号=’S4’））
11）检索出在上海工作并且向S6供应商发出了订购单的职工号。
SELECT职工号FROM职工WHERE仓库号IN
（SELECT仓库号FROM仓库WHERE城市=’上海’）AND职工号IN
（SELECT职工号FROM订购单WHERE供应商号=’S6’））
12）检索出在广州工作并且只向S6供应商发出了订购单的职工号。
（SELECT仓库号FROM仓库WHERE城市=’广州’）AND职工号IN
（SELECT职工号FROM订购单WHERE供应商号=’S6’）AND职工号NOTIN
（SELECT职工号FROM订购单WHERE供应商号!
=’S6’）
13）检索出由工资多于1230元的职工向北京的供应商发出的订购单号。
SELECT订购单号FROM订购单WHERE职工号IN
（SELECT职工号FROM职工WHERE工资>
1230）AND供应商号IN
（SELECT供应商号FROM供应商WHERE地址=’北京’）
14）检索出仓库的个数。
SELECTCOUNT（*）FROM仓库
15）检索出有最大面积的仓库信息。
SELECT*FROM仓库WHERE面积=（SELECTMAX（面积）FROM仓库）
16）检索出所有仓库的平均面积。
SELECTAVG（面积）FROM仓库
17）检索出向S4供应商发出订购单的那些仓库的平均面积。
SELECTAVG（面积）FROM仓库WHERE仓库号IN
18）检索出每个城市的供应商个数。
SELECT地址,COUNT（*）FROM供应商GROUPBY地址
19）检索出每个仓库中工资多于1220元的职工个数。
SELECT仓库号,COUNT（*）FROM职工WHERE工资>
1220GROUPBY仓库号
20）检索出和面积最小的仓库有联系的供应商的个数。
SELECTCOUNT（*）FROM供应商WHERE供应商号IN
（SELECT供应商号FROM订购单WHERE职工号IN
（SELECT职工号FROM职工WHERE仓库号IN
（SELECT仓库号FROM仓库WHERE面积=
（SELECTMIN（面积）FROM仓库））））
21）检索出工资低于本仓库平均工资的职工信息。
SELECT*FROM职工outWHERE工资<
（SELECTAVG（工资）FROM职工inneWHERE仓库号=out.仓库号）
第三章习题
2.设有关系模式R（U,F），U={A,B,C,D,E}，F={AB→E,DE→B,B→C,C→E,E→A}
（1）计算所有函数依赖左部关于函数依赖集F的属性集闭包；
（AB）+={A,B,C,E}
（DE）+={A,B,C,D,E}
B+={A,B,C,E}
C+={A,C,E}
E+={A,E}
（2）确定关系模式R上的所有侯选关键字；
DE
（3）求F的所有最小覆盖。
Fm=F-{AB->
E}
3.假设有一个名为“参加”的关系，该关系有属性：
职工（职工名）、工程（工程名）、时数（花费在工程上的小时数）和工资（职工的工资）；
一个“参加”记录描述一个职工花费在一个工程上的总时数和他的工资；
另外，一个职工可以参加多个工程，多个职工可以参加同一个工程。
请回答如下各问题：
用A、B、C、D分别代表属性职工、工程、时数和工资，则：
（1）确定这个关系的关键字；
AB
（2）找出这个关系中的所有函数依赖；
AB->
C,A->
D
（3）指出这个关系上的哪些函数依赖会带来操作异常现象；
D对关键字AB的部分函数依赖可能会带来如下问题：
数据冗余：
一个职工参加多个工程，则职工的工资值会重复；
更新异常：
当改变职工的工资时，可能会只修改了一部分，从而造成数据不一致；
插入异常：
当一个职工尚未承担工程，但要插入职工信息（如工资）则不允许（因为没有完整的关键字）；
删除异常：
当某个工程结束，删除工程信息时，可能会将职工信息（如工资）一同删除（如果职工只参加了一项工程）。
（4）这个关系是第几范式关系？
1NF
（5）计算该关系上函数依赖集的最小覆盖；
Fm={AB->
C,A->
D}
（6）将该关系分解成尽可能高的范式，并指明是第几范式？
分解为R1（A,B,C）和R2（A,D）
结果为4NF
4.一个关系有4个字段A、B、C、D，这里A和B构成复合关键字，问满足下列函数依赖的关系是第几范式？
（1）A、B、C、D都函数依赖于AB。
BCNF或4NF
（2）A、B、C、D都函数依赖于AB，而D还函数依赖于C。
2NF
（3）A、B、C、D都函数依赖于AB，而D还函数依赖于B。
（4）A、B、C、D都函数依赖于AB，而B还函数依赖于C。
3NF
4.设有关系模式R（A,B,C,D,E），并有函数依赖{AB→D,AC→E,BC→D,D→A,E→B}，现将R分解成关系模式S（A,B,C）和其他一些关系模式，请给出在关系模式S上成立的函数依赖。
AC→B，BC→A
升级会员 