数据库期末考试复习题及答案 综合题.docx

数据库期末考试复习题及答案 综合题.docx

《数据库期末考试复习题及答案 综合题.docx》由会员分享，可在线阅读，更多相关《数据库期末考试复习题及答案 综合题.docx（43页珍藏版）》请在冰豆网上搜索。

数据库期末考试复习题及答案综合题

试题一

四、设计题

（第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）每个关系模式的主码、外码如下：

科室：

主码是科名；

病房：

主码是科名十病房号，外码是科名；

医生：

主码是工作证号，外码是科名；

病人：

主码是病历号，外码是科名十病房号。