数据库期末考试复习题及答案 综合题.docx
《数据库期末考试复习题及答案 综合题.docx》由会员分享,可在线阅读,更多相关《数据库期末考试复习题及答案 综合题.docx(44页珍藏版)》请在冰豆网上搜索。
数据库期末考试复习题及答案综合题
数据库期末考试复习题及答案-综合题
试题一
四、设计题
(第1题4分,第2题6分,第3题3分,第4题4分,
第5题8分,共25分)
1.设教学数据库中有三个基本表:
学生表S(SNO,SNAME,AGE,SEX),其属性分别表示学号、学生姓名、年龄、性别。
课程表C(CNO,CNAME,TEACHER),其属性分别表示课程号、课程名、上课教师名。
选修表SC(SNO,CNO,GRADE),其属性分别表示学号、课程号、成绩。
有如下SQL查询语句:
SELECTCNO
FROMC
WHERECNONOTIN
(SELECTCNO
FROMS,SC
WHERES.SNO=SC.SNO
ANDSNAME='张三');
请完成下列问题:
(1)用汉语句子阐述上述SQL语句的含义;
(2)用等价的关系代数表达式表示上述SQL查询语句。
解:
(1)查询张三同学没有选修的课程的课程号。
(2分)
(2)πCNO(C)-πCNO(σSNAME='张三'(S)
SC)或
πCNO(C)-πCNO(σSNAME='张三'(S
SC))(2分)
2.设有如图3所示的三个关系。
其中各个属性的含义如下:
A#(商店代号)、ANAME(商店名)、WQTY(店员人数)、CITY(所在城市)、B#(商品号)、BNAME(商品名称)、PRICE(价格)、QTY(商品数量)。
AB
A#
ANAME
WQTY
CITY
101
韶山商店
15
长沙
204
前门百货商店
89
北京
256
东风商场
501
北京
345
铁道商店
76
长沙
620
第一百货公司
413
上海
B#
BNAME
PRICE
1
毛笔
21
2
羽毛球
784
3
收音机
1325
4
书包
242
AB
A#
B#
QTY
101
1
105
101
2
42
101
3
25
101
4
104
204
3
61
256
1
241
256
2
91
345
1
141
345
2
18
345
4
74
620
4
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#!
='256'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:
CREATEVIEWVIEW6
AS
SELECTENO,ENAMEFROMDEPT,EMP
WHEREMGR_ENO=ENOANDSEX='女'(4分)
5.设有关系R和函数依赖F:
R(A,B,C,D,E),F={ABC→DE,BC→D,D→E}。
试求下列问题:
(1)关系R的侯选码是什么?
R属于第几范式?
并说明理由。
(3分)
(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图;(5分)
(2)将该E-R模型转换为关系模型;(5分)
(要求:
1:
1和1:
n的联系进行合并)
(3)指出转换结果中每个关系模式的主码和外码。
(5分)
(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)用关系代数查询没有使用天津供应商生产的红色零件的工程号;(3分)
πJNO(J)-πJNO(CITY='天津'(S)SPJCOLOR='红'(P))
(2)用关系代数查询至少使用了供应商S1所供应的全部零件的工程号JNO;
(3分)
πPNO,JNO(SPJ)÷πPNO(SNO='S1'(SPJ))
(3)用SQL查询供应工程J1零件为红色的工程号JNO;(2分)
SELECTDISTINCTJNO
FROMSPJ,P
WHERESPJ.PNO=P.PNOAND
COLOR='红'AND
JNO='J1';
或SELECTDISTINCTSNO
FROMSPJ
WHEREJNO='J1'AND
PNOIN(SELECTPNO
FROMP
WHERECOLOR='红');
(4)用SQL查询没有使用天津供应商生产的零件的工程号;(3分)
SELECTJNO
FROMJ
WHEREJNONOTIN(SELECTJNO
FROMSPJ
WHERESNOIN
(SELECTSNO
FROMS
WHERECITY='天津'));
或SELECTJNO
FROMJ
WHERENOTEXISTS
(SELECT*
FROMSPJ,S
WHERESPJ.SNO=S.SNOAND
SPJ.JNO=J.JNOAND
CITY='天津');
或SELECTJNO
FROMJ
WHERENOTEXISTS
(SELECT*
FROMSPJ
WHERESPJ.JNO=J.JNOANDEXISTS
(SELECT*
FROMS
WHERES.SNO=SPJ.SNOANDCITY='天津'));
(5)用SQL语句将全部红色零件改为蓝色;(2分)
UPDATEPSETCOLOR='蓝'WHERECOLOR='红';
(6)用SQL语句将(S2,P4,J6,400)插入供应情况关系。
(2分)
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属于第几范式?
并说明理由。
(3分)
关系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。
得分
五、综合题(15分)
某企业集团有若干工厂,每个工厂生产多种产品,且每一种产品可以在多个工厂生产,每个工厂按照固定的计划数量生产产品;每个工厂聘用多名职工,且每名职工只能在一个工厂工作,工厂聘用职工有聘期和工资。
工厂的属性有工厂编号、厂名、地址,产品的属性有产品编号、产品名、规格,职工的属性有职工号、姓名。
(1)根据上述语义画出E-R图;(5分)
2)将该E-R模型转换为关系模型;(5分)(要求:
1:
1和1:
n的联系进行合并)
转化后的关系模式如下:
工厂(工厂编号,厂名,地址)
产品(产品编号,产品名,规格)
职工(职工号,姓名,工产编号,聘期,工资)
生产(工产编号,产品编号,计划数量)
(3)指出转换结果中每个关系模式的主码和外码。
(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
A
C
a1
40
a2
50
a3
55
试用SQL语句实现:
(1)查询属性C>50时,R中与相关联的属性B之值。
(3分)
SELECTB
FROMR,S
WHERER.A=S.AANDC>50
(2)当属性C=40时,将R中与之相关联的属性B值修改为b4。
(5分)
UPDATER
SETB=’b4’
WHEREAIN
(SELECTA
FROMS
WHEREC=40)
4.设有关系R和函数依赖F:
R(W,X,Y,Z),F={X→Z,WX→Y}。
试求下列问题:
(1)关系R属于第几范式?
(3分)
R是1NF。
侯选码为WX,则Y,Z为非主属性,又由于X→Z,因此F中存在非主属性对侯选码的部分函数依赖。
(2)如果关系R不属于BCNF,请将关系R逐步分解为BCNF。
(5分)
要求:
写出达到每一级范式的分解过程,并指明消除什么类型的函数依赖。
将关系分解为:
R1(W,X,Y),F1={WX→Y}
R2(X,Z),F2={X→Z}
消除了非主属性对码的部分函数依赖。
F1和F2中的函数依赖都是非平凡的,并且决定因素是候选码,所以上述关系模式是BCNF。
得分
阅卷人
复查人
五、综合题(15分)
某医院病房管理系统中,包括四个实体型,分别为:
科室:
科名,科地址,科电话
病房:
病房号,病房地址
医生:
工作证号,姓名,职称,年龄
病人:
病历号,姓名,性别
且存在如下语义约束:
①一个科室有多个病房、多个医生,一个病房只能属于一个科室,一个医生只属于一个科室;
②一个医生可负责多个病人的诊治,一个病人的主管医生只有一个;
③一个病房可入住多个病人,一个病人只能入住在一个病房。
注意:
不同科室可能有相同的病房号。
完成如下设计:
(1)画出该医院病房管理系统的E-R图;(5
某医院病房管理系统的基本E-R图
(2)将该E-R图转换为关系模型;(5分)
(要求:
1:
1和1:
n的联系进行合并)
转化后的关系模式如下:
科室(科名,科地址,科电话)
病房(病房号,病房地址,科名)
医生(工作证号,姓名,职称,年龄,科名)
病人(病历号,姓名,性别,主管医生,病房号,科名)
(3)指出转换结果中每个关系模式的主码和外码。
(5分
每个关系模式的主码、外码如下:
科室:
主码是科名;
病房:
主码是科名十病房号,外码是科名;
医生:
主码是工作证号,外码是科名;
病人:
主码是病历号,外码是科名十病房号。
得分
四、设计题
(第1题20分,第2题10分,共30分)
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)用关系代数查询没有使用天津供应商生产的红色零件的工程号;(3分)
πJno(J)-πJno(City='天津'(S)SPJColor='红'(P))
(2)用关系代数查询至少使用了供应商S1所供应的全部零件的工程号JNO;
(3分)
πPno,Jno(SPJ)÷πPno(Sno='S1'(SPJ))
(3)用ALPHA语言查询供应工程J1零件为红色的供应商号SNO;(2分)
RANGEPPX
GETW(SPJ.Sno):
SPJ.Jno='J1'∧PX(SPJ.Pno=PX.Pno∧PX..Color='红'))
(4)用T-SQL语句建立“供应商”S表(主码必须定义);(2分)
CREATETABLES
(SnoCHAR(6)PRIMARYKEY,
SnameCHAR(10),
StatusINT,
CityCHAR(20));
(5)用SQL查询工程J1使用的各种零件的名称和使用数量;(3分)
SELECTPname,TotalQty
FROM(SELECTPno,SUM(Qty)TotalQty
FROMSPJ
WHEREJno='J1'
GROUPBYPno)X,P
WHEREP.Pno=X.Pno;
(6)用SQL查询没有使用天津供应商生产的零件的工程号;(3分)
SELECTJno或:
SELECTJno
FROMJFROMJ
WHEREJnoNOTINWHERENOTEXISTS
(SELECTJno(SELECT*
FROMSPJFROMSPJ,S
WHERESnoINWHERESPJ.Sno=S.Sno
(SELECTSnoANDSPJ.Jno=J.Jno
FROMSANDCity='天津');
WHERECity='天津')
);
或:
SELECTJno
FROMJWHERENOTEXISTS
(SELECT*
FROMSPJ
WHERESPJ.Jno=J.Jno
ANDEXISTS
(SELECT*
FROMS
WHERES.Sno=SPJ.SnoANDCity='天津')
);
(7)用SQL语句将全部红色零件改为蓝色;(2分)
UPDATEPSETColor='蓝'WHEREColor='红';
(8)用SQL语句将(S2,P4,J6,400)插入供应情况关系。
(2分)
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属于第几范式?
(3分)
关系STUDENT是1NF,因为F中存在非主属性SNAME,SDEPT,MNAME对侯选码(S#,CNAME)的部分函数依赖。
(2)如果关系STUDENT不属于BCNF,请将关系STUDENT逐步分解为巧
BCNF。
(7分)
要求:
写出达到每一级范式的分解过程,并指明消除什么类型的函数依赖。
首先消除部分函数依赖(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。
得分
五、综合题(15分)
某医院病房管理系统中,包括四个实体型,分别为:
科室:
科名,科地址,科电话
病房:
病房号,病房地址
医生:
工作证号,姓名,职称,年龄
病人:
病历号,姓名,性别
且存在如下语义约束:
①一个科室有多个病房、多个医生,一个病房只能属于一个科室,一个医生只属于一个科室;
②一个医生可负责多个病人的诊治,一个病人的主管医生只有一个;
③一个病房可入住多个病人,一个病人只能入住在一个病房。
注意:
不同科室可能有相同的病房号。
完成如下设计:
(1)画出该医院病房管理系统的E-R图;(5分)
(2)将该E-R图转换为关系模型;(5分)
(要求:
1:
1和1:
n的联系进行合并)
(3)指出转换结果中每个关系模式的主码和外码。
(5分)
(1)本题的E-R图如下图所示。
某医院病房管理系统的基本E-R图
(2)转化后的关系模式如下:
科室(科名,科地址,科电话)
病房(病房号,病房地址,科名)
医生(工作证号,姓名,职称,年龄,科名)
病人(病历号,姓名,性别,主管医生,病房号,科名)
(3)每个关系模式的主码、外码如下:
科室:
主码是科名;
病房:
主码是科名十病房号,外码是科名;
医生:
主码是工作证号,外码是科名;