数据库期末考试复习题及答案 综合题Word文档下载推荐.docx
《数据库期末考试复习题及答案 综合题Word文档下载推荐.docx》由会员分享，可在线阅读，更多相关《数据库期末考试复习题及答案 综合题Word文档下载推荐.docx（44页珍藏版）》请在冰豆网上搜索。
前门百货商店
89
北京
256
东风商场
501
345
铁道商店
76
620
第一百货公司
413
上海
B＃
BNAME
PRICE
1
毛笔
21
2
羽毛球
784
3
收音机
1325
4
书包
242
AB
A＃
QTY
105
42
25
104
61
241
91
141
18
74
125
图3
试用SQL语言写出下列查询：
（1）找出店员人数不超过100人或者在长沙市的所有商店的代号和商店名。
（2）找出至少供应了代号为’256’的商店所供应的全部商品的其它商店的商店名和所在城市。
（1）SELECTA#,ANAMEFROMA
WHEREWQTY<
=100ORCITY='
长沙'
;
（2分）
（2）SELECTANAME,CITYFROMA
WHERENOTEXISTS
（SELECT*FROMB
WHEREEXISTS
（SELECT*FROMABAB1
WHEREA#='
256'
ANDB#=B.B#）
ANDNOTEXISTS
（SELECT*FROMABAB2
WHEREA#!
='
ANDA#=A.A#ANDB#=B.B#））;
（4分）
3.设有职工基本表：
EMP（ENO，ENAME，AGE，SEX，SALARY），其属性分别表示职工号、姓名、年龄、性别、工资。
为每个工资低于1000元的女职工加薪200元，试写出这个操作的SQL语句。
UPDATEEMP
SETSALARY=SALARY+200
WHERESALARY<
1000ANDSEX='
女'
（3分）
4.设某工厂数据库中有两个基本表：
车间基本表：
DEPT（DNO，DNAME，MGR_ENO），其属性分别表示车间编号、车间名和车间主任的职工号。
职工基本表：
ERP（ENO，ENAME，AGE，SEX，SALARY，DNO），其属性分别表示职工号、姓名、年龄、性别、工资和所在车间的编号。
建立一个有关女车间主任的职工号和姓名的视图，其结构如下：
VIEW6（ENO，ENAME）。
试写出创建该视图VIEW6的SQL语句。
参考答案1：
CREATEVIEWVIEW6
AS
SELECTENO,ENAMEFROMEMP
WHERESEX='
ANDENOIN
（SELECTMGR_ENOFROMDEPT）
参考答案2：
AS
SELECTENO,ENAMEFROMDEPT,EMP
WHEREMGR_ENO=ENOANDSEX='
5.设有关系R和函数依赖F：
R（A，B，C，D，E），F={ABC→DE，BC→D，D→E}。
试求下列问题：
（1）关系R的侯选码是什么？
R属于第几范式？
并说明理由。
（2）如果关系R不属于BCNF，请将关系R逐步分解为BCNF。
（5分）
要求：
写出达到每一级范式的分解过程，并指明消除什么类型的函数依赖。
（1）关系R的候选码是（A，B，C），R∈1NF，因为R中存在非主属性D，E对候选码（A，B，C）的部分函数依赖。
（3分）
（2）首先消除部分函数依赖
将关系分解为：
R1（A，B，C）（A，B，C）为候选码，
R1中不存在非平凡的函数依赖
R2（B，C，D，E），（B，C）为候选码，
R2的函数依赖集为：
F2={（B，C）→D，D→E}
在关系R2中存在非主属性E对候选码（B，C）的传递函数依赖，所以将R2进一步分解：
R21（B，C，D），（B，C）为候选码，
R21的函数依赖集为：
F21={（B，C）→D}
R22（D，E），D为候选码，
R22的函数依赖集为：
F22={D→E}
在R1中已不存在非平凡的函数依赖，在R21、R22关系模式中函数依赖的决定因素均为候选码，所以上述三个关系模式均是BCNF。
五、综合题（15分）
某企业集团有若干工厂，每个工厂生产多种产品，且每一种产品可以在多个工厂生产，每个工厂按照固定的计划数量生产产品；
每个工厂聘用多名职工，且每名职工只能在一个工厂工作，工厂聘用职工有聘期和工资。
工厂的属性有工厂编号、厂名、地址，产品的属性有产品编号、产品名、规格，职工的属性有职工号、姓名。
（1）根据上述语义画出E-R图；
（2）将该E-R模型转换为关系模型；
（要求：
1:
1和1:
n的联系进行合并）
（3）指出转换结果中每个关系模式的主码和外码。
（1）本题的E-R图如下图所示。
（2）转化后的关系模式如下：
工厂（工厂编号，厂名，地址）
产品（产品编号，产品名，规格）
职工（职工号，姓名，工厂编号，聘期，工资）
生产（工厂编号，产品编号，计划数量）
（3）每个关系模式的主码、外码如下：
工厂：
主码是工厂编号，无外码；
产品：
主码是产品编号，无外码；
职工：
主码职工号，外码是工厂编号；
生产：
主码是（工厂编号，产品编号），
外码是工厂编号、产品编号。
试题二
四、设计题（第1题15分，第2题10分，共25分）
1.设有一个工程供应数据库系统，包括如下四个关系模式：
●S（SNO，SNAME，STATUS，CITY）；
●P（PNO，PNAME，COLOR，WEIGHT）；
●J（JNO，JNAME，CITY）；
●SPJ（SNO，PNO，JNO，QTY）；
供应商表S由供应商号、供应商名、状态、城市组成；
零件表P由零件号、零件名、颜色、重量组成；
工程项目表J由项目号、项目名、城市组成；
供应情况表SPJ由供应商号、零件号、项目号、供应数量组成；
（1）用关系代数查询没有使用天津供应商生产的红色零件的工程号；
πJNO（J）-πJNO（CITY='
天津'
（S）SPJCOLOR='
红'
（P））
（2）用关系代数查询至少使用了供应商S1所供应的全部零件的工程号JNO；
（3分）
πPNO,JNO（SPJ）÷
πPNO（SNO='
S1'
（SPJ））
（3）用SQL查询供应工程J1零件为红色的工程号JNO；
SELECTDISTINCTJNO
FROMSPJ,P
WHERESPJ.PNO=P.PNOAND
COLOR='
AND
JNO='
J1'
或SELECTDISTINCTSNO
FROMSPJ
WHEREJNO='
AND
PNOIN（SELECTPNO
FROMP
WHERECOLOR='
（4）用SQL查询没有使用天津供应商生产的零件的工程号；
SELECTJNO
FROMJ
WHEREJNONOTIN（SELECTJNO
FROMSPJ
WHERESNOIN
（SELECTSNO
FROMS
WHERECITY='
））;
或SELECTJNO
WHERENOTEXISTS
（SELECT*
FROMSPJ,S
WHERESPJ.SNO=S.SNOAND
SPJ.JNO=J.JNOAND
CITY='
或SELECTJNO
WHERENOTEXISTS
WHERESPJ.JNO=J.JNOANDEXISTS
（SELECT*
WHERES.SNO=SPJ.SNOANDCITY='
（5）用SQL语句将全部红色零件改为蓝色；
UPDATEPSETCOLOR='
蓝'
（6）用SQL语句将（S2，P4，J6，400）插入供应情况关系。
INSERTINTOSPJVALUES（'
S2'
'
P4'
J6'
400）;
2．设有关系STUDENT（S#,SNAME,SDEPT,MNAME,CNAME,GRADE），（S#,CNAME）为候选码，设关系中有如下函数依赖：
（S#,CNAME）→SNAME,SDEPT,MNAME
S#→SNAME,SDEPT,MNAME
（S#,CNAME）→GRADE
SDEPT→MNAME
（1）关系STUDENT属于第几范式？
关系STUDENT是1NF，因为F中存在非主属性SNAME，SDEPT，MNAME对侯选码（S#,CNAME）的部分函数依赖。
（2）如果关系STUDENT不属于BCNF，请将关系STUDENT逐步分解为巧
BCNF。
（7分）
要求：
①首先消除部分函数依赖（S#,CNAME）→SNAME,SDEPT,MNAME将关系分解为：
R1（S#,SNAME,SDEPT,MNAME），S#为候选码，
R1的函数依赖集为：
F1={S#→SNAME,SDEPT,MNAME，SDEPT→MNAME}
R2（S#,CNAME,GRADE），S#为候选码，
R2的函数依赖集为：
F2={（S#,CNAME）→GRADE}
②在关系R1中存在非主属性MNAME对候选码S#的传递函数依赖S#→MNAME，所以将R1进一步分解：
R11（S#,SNAME,SDEPT），S#为候选码，
R11的函数依赖集为：
F11={S#→SNAME,SDEPT}
R12（SDEPT,MNAME），SDEPT为候选码，
R12的函数依赖集为：
F12={SDEPT→MNAME}
在R2、R11、R12关系模式中函数依赖都是非平凡的，并且决定因素均是候选码，所以上述三个关系模式均是BCNF。
得分
2）将该E-R模型转换为关系模型；
（5分）（要求：
转化后的关系模式如下：
职工（职工号，姓名，工产编号，聘期，工资）
生产（工产编号，产品编号，计划数量）
每个关系模式的主码、外码如下：
主码是工产编号；
主码是产品编号；
职工号，外码是工厂编号；
主码是（工产编号，产品编号），
外码是工产编号、产品编号。
试题三
（第1题4分，第2、3、4题各8分，共28分）
1.设有学生选课关系SC（学号，课程号，成绩），试用SQL语句检索每门课程的最高分。
SELECT课程号,MAX（成绩）
FROMSC
GROUPBY课程号
2.关系模式如下：
商品P（PNO，PN，COLOR，PRICE）
商店S（SNO；
SN，CITY）
销售SP（PNO，SNO，QTY）
分别用关系代数和SQL写出查询语句：
查询销售商品"
TV”的商店名SN。
关系代数：
πSN（PN='
P1'
（SSPP））
SQL：
SELECTSN
FROMS,SP,P
WHERES.SNO=SP.SNO
ANDP.PNO=SP.PNO
ANDPN='
TV'
3．设有关系R和S如图2所示。
RS
A
B
a1
b1
a2
b2
a3
b3
C
40
50
55
试用SQL语句实现：
（1）查询属性C>
50时，R中与相关联的属性B之值。
SELECTB
FROMR,S
WHERER.A=S.AANDC>
（2）当属性C=40时，将R中与之相关联的属性B值修改为b4。
UPDATER
SETB=’b4’
WHEREAIN
（SELECTA
FROMS
WHEREC=40）
4.设有关系R和函数依赖F：
R（W，X，Y，Z），F={X→Z，WX→Y}。
（1）关系R属于第几范式？
R是1NF。
侯选码为WX，则Y，Z为非主属性，又由于X→Z，因此F中存在非主属性对侯选码的部分函数依赖。
将关系分解为：
R1（W，X，Y），F1={WX→Y}
R2（X，Z），F2={X→Z}
消除了非主属性对码的部分函数依赖。
F1和F2中的函数依赖都是非平凡的，并且决定因素是候选码，所以上述关系模式是BCNF。
阅卷人
复查人
某医院病房管理系统中，包括四个实体型，分别为：
科室：
科名，科地址，科电话
病房：
病房号，病房地址
医生：
工作证号，姓名，职称，年龄
病人：
病历号，姓名，性别
且存在如下语义约束：
①一个科室有多个病房、多个医生，一个病房只能属于一个科室，一个医生只属于一个科室；
②一个医生可负责多个病人的诊治，一个病人的主管医生只有一个；
③一个病房可入住多个病人，一个病人只能入住在一个病房。
注意：
不同科室可能有相同的病房号。
完成如下设计：
（1）画出该医院病房管理系统的E－R图；
（5
某医院病房管理系统的基本E-R图
（2）将该E-R图转换为关系模型；
转化后的关系模式如下：
科室（科名，科地址，科电话）
病房（病房号，病房地址，科名）
医生（工作证号，姓名，职称，年龄，科名）
病人（病历号，姓名，性别，主管医生，病房号，科名）
（5分
科室：
主码是科名；
病房：
主码是科名十病房号，外码是科名；
医生：
主码是工作证号，外码是科名；
病人：
主码是病历号，外码是科名十病房号。
（第1题20分，第2题10分，共30分）
●S（Sno，Sname，Status，City）；
●P（Pno，Pname，Color，Weight）；
●J（Jno，Jname，City）；
●SPJ（Sno，Pno，Jno，Qty）；
πJno（J）-πJno（City='
（S）SPJColor='
πPno,Jno（SPJ）÷
πPno（Sno='
（3）用ALPHA语言查询供应工程J1零件为红色的供应商号SNO；
RANGEPPX
GETW（SPJ.Sno）:
SPJ.Jno='
∧PX（SPJ.Pno=PX.Pno∧PX..Color='
））
（4）用T-SQL语句建立“供应商”S表（主码必须定义）；
CREATETABLES
（SnoCHAR（6）PRIMARYKEY,
SnameCHAR（10）,
StatusINT,
CityCHAR（20））;
（5）用SQL查询工程J1使用的各种零件的名称和使用数量；
SELECTPname,TotalQty
FROM（SELECTPno,SUM（Qty）TotalQty
WHEREJno='
GROUPBYPno）X,P
WHEREP.Pno=X.Pno;
（6）用SQL查询没有使用天津供应商生产的零件的工程号；
SELECTJno或：
SELECTJno
FROMJFROMJ
WHEREJnoNOTINWHERENOTEXISTS
（SELECTJno（SELECT*
FROMSPJFROMSPJ,S
WHERESnoINWHERESPJ.Sno=S.Sno
（SELECTSnoANDSPJ.Jno=J.Jno
FROMSANDCity='
WHERECity='
）
或：
FROMJWHERENOTEXISTS
（SELECT*
FROMSPJ
WHERESPJ.Jno=J.Jno
ANDEXISTS
FROMS
WHERES.Sno=SPJ.SnoANDCity='
（7）用SQL语句将全部红色零件改为蓝色；
UPDATEPSETColor='
WHEREColor='
（8）用SQL语句将（S2，P4，J6，400）插入供应情况关系。
400）；
关系STUDENT是1NF，因为F中存在非主属性SNAME,SDEPT,MNAME对侯选码（S#,CNAME）的部分函数依赖。
首先消除部分函数依赖（S#,CNAME）→SNAME,SDEPT,MNAME将关系分解为：
R1（S#,SNAME,SDEPT,MNAME），
F1={S#→SNAME,SDEPT,MNAME}R2（S#,CNAME,GRADE），F2={（S#,CNAME）→GRADE}
在关系R1中存在非主属性对候选码的传递函数依赖S#→SDEPT，所以将R1进一步分解：
R11（S#,SNAME,SDEPT），F11={S#→SNAME,SDEPT}R12（SDEPT,MNAME），F12={SDEPT→MNAME}
在R2,R11,R12关系模式中函数依赖都是非平凡的，并且决定因素均是候选码，所以上述三个关系模式均是BCNF。
某医院病房管理系统中，包括四个实体型，分别为：
（1）本题的E-R图如下图所示。
某医院病房管理系统的基本E-R图
升级会员 