数据库原理应用实践题

一.应用题（含答案）

1.某医院病房计算机管理中需如下信息。

科室：科名、科地址、科电话、医生姓名

病房：病房号、床位数、所属科室名

医生：姓名、职称、所属科室名、年龄、工作证号

病人：病历号、姓名、性别、诊断医生、病房号

其中，一个科室有多个病房、多个医生；一个病房只能属于一个科室：一个医生只属于一个科室,

但可负责多个病人的诊治；一个病人的主治医生只有一个。设计该计算机管理系统的E-R图。

答对应的E-R图如图2.1所示。

图24E-R图

8.有如下的关系R：

（1）求出R所有的候选关魅字。

（2）列出R中的函数依赖，

（3）R属于笫几范式？

R

ADE

Aidie：

A2dge：

Ases

2.学校有若干个系，每个系有若干名教师和学生：每个教师可以教授若干门课程，并参加多个

项目；每个学生可以同时选修多门课程。请设计该学校的教学管理的E-R模型，要求给出每个实

体、联系的属性。

答该学校的教学管理E-R模型有以下实体：系、教师、学生、项目、课程。各实体属性如下：

系（系编号，系名，系主任）

教师（教师编号，教师姓名，职称）

学生（学号，姓名，性别，班号）

项目（项目编号，名称，负责人）

课程（课程编号，课程名，学分）

各实体之间的联系如下：

教师担任课程的1：n“任课”联系

教师参加项目的n：m“参加”联系

学生选修课程的n：m“选修”联系

系、教师和学生之间的所属关系的1：m：n“领导”联系

对应的E-R模型如图2.2所示。

比目毋号

图2.2E-R图

3.设有关系R和S：

Rs

ABBC

abbc

cbea

debd

计算RXS、RXS和。A=C(RXS).

B<C

答计算结果如下:

RXS

R凶S

ABcB<C

aA=c(RxS)

abcAR.BSBC

abdabbcAR.BSBC

cbcabbdabea

cbdcbbccbbc

deacbbddebd

4.设有关系R、S：

R

ABCS

abcABc

bafbaf

cbddaf

计算Ri=R-S、氐=RUS、R3=RQS和R*=RxS.

答计菖结果如下：

&

R：R.AR.BR.CS.ASBs.c

ABCbcbaf

2bcbcdaf

Ribafbafbaf

ABCcbd氏bsfdaf

abcdafABccbdbaf

cbdABCbafcbddaf

5.下面给出一个数据集，判断它是否可直接作为关系数据库中的关系，若不行，则改造成为尽

可能好的并能作为关系数据库中关系的形式，同时说明进行这种改造的理由。

系名课程名教师名

计篁机系DB李军，刘强

机械系CAD金山，宋海

造船系CAM王华

自控系CTY张红，曾建

答因为关系模式至少是INF关系，即不包含重复组，并且不存在嵌套结构，给出的数

据集显然不可直接作为关系数据库中的关系，改造为1NF的关系如下：

系名谭程名她庙名

计篁机系DB李军

计篁机系DB刘强

机械系CAD金山

机械系CAD宋海

造船系CAM王华

自控系CTY张红

自控系CTY曾健

6.下面给出的关系R为第几范式？是否存在操作弁常？若存在，则将其分解为高一级范式。分

解完成的高级范式中是否可以避免分解前关系中存在的操作异常？

工程号材料号鼓量开工日期完工日期价格

PiIi498059902250

PiI：698059902300

Pih1598059902ISO

P：Ii698119912250

P：14IS98119912350

答它为1NF,

因为该关系的候选关键字为（工程号，材料号），而非主履性（开工日期和完工日期）部分函数

依赖于候选关键字的子集工程号，即：

（工程号，材料号）二♦开工日期

（工程号，材料号）一U完工日期

所以它不是2NFo

它存在操作异常。如果工程项目确定后，若暂时未用到材料，则该工程的数据因缺少关键字的一

部分（材料号）而不能进入到数据库中，出现插入异常。若某工程卜.马，则删去该工程的操作也

可能丢失材料方面的信息。

将其中的部分函数依赖分解为一个独立的关系，则产生如下的两个2NE关系子模式:

Ri

工程号材料号鼓量价格

P1114250

P1I:6300

P1b15180工程号开工日期完工日期

P:Ii6250Pi98059902

P:14IS350P19S119812

分解后，新工程确定后,尽管还未用到材料,该工程数据可在关系后一入一—.整时..'R2拇

作.不会丢失“科方面的信•&.

7.设有如下所示的关系R：

R

课程名教师名教师地址

Ci马千里D】

C2于得水D1

Ci余快Da

C4于得水D）

（1）它为第几范式？为什么？

（2）是否存在删除操作异常？若存在，则说明是在什么情况下发生的。

（3）将它分解为高一级范式，分解后的关系是如何解决分解前可能存在的删除操作的异常问题

的？

答（1）它是2NF。

因为R的候选关键字为课程名，而“课程名一教师名”，“教师名一课程名”不成立，教师名一

教师地址，所以课程名t—教师地址，即存在非主属性教师地址对候选关键字课程名的传递

函数依赖，因此R不是3NF。又因为不存在非主属性对候选关键字的部分函数依赖，所以R是2NF。

（2）存在删除操作异常。当删除某门课程时会删除不该删除的教师的有关信息。

（3）分解为高一级范式如卜.：

R]

课程名教师名Ri

Ci马千里教师名教师地址

Ci于得水马千里Di

C3余快于得水Di

C4于得水余快D：

答(1)R的候选关键字为A和DEo

(2)R中的函数依赖有：A-*DE,DE-A。

(3)R是BCNF。

9.设有函数依赖集F={AB-*CE,A-*C,GP-*B,EP-*A,CDE-*P,1IB-*P,D-HG,ABC-*PG},计算属性集

n*

D关于F的闭包D，

答☆X=D,X(0)=Do

在F中找出左边是D子集的函数依赖，其结果是D-HG,所以K(1)=X(O)HG=DGH,显然有X(1)HX(O)。

在F中找出左边是DGH子集的函数依赖，未找到,则X(2)=DGH。由于X(2)=X(1)，因此DDF=DGH(>

10.设有关系框架R(A,B,C,D,E)及其上的函数相关性集合F={A-C,B-D,C-*D,DE-*C,CE-A),

试问：分解P={AD,AB,BE,CDE,AE}是否为R的无损联接分解?

答P的无损联接性判断表如下，由此判断不具有无损联接性。

RABCDE

ADa】

AB3]a：

BEa：a：

CDE2324a：

AE21a*

11.假定一个部门的数据库包括以下信息。

职工的信息：职工号、姓名、地址和所在部门.

部门的信息：部门所有职工、部门名、经理和销售的产品。

产品的信息：产品名、制造商、价格、型号及产品内部编号.

制造商的信息：制造商名称、地址、生产的产品名和价格.

试画出这个数据库的E-R图。

制造商

图5」一个E-R团

12.如图5.2所示给出图（a）、（b）和（c）3个不同的局部模型，将其合并成一个全局信息

结构，并设置联系实体中隹属性（允许增加认为必要的属性，也可将有关基本实体的属性选作联

系实体的属性）。

各实体构成如下。

部门：部门号、部门名、电话、地址

职员：职员号、职员名、耿务（干部/工人）、年龄、性别

设备处：单位号、电话、地址

工人：工人编号、姓名、年龄、性别

设备：设备号、名称、规格、价格

零件：零件号、名称、规格、价格

厂商：单位号、名称、电话、地址

(b)

(c)

图5.2局部的E-R图

答汇总后的E-R图如图5.3所示。

各类实体的属性如下。

部门：部门号、部门名、电话、地址

取员：职员号、取员名、职务、年龄、性别

设备：设备号、名称、规格、价格

零件：零件号、名称、规格、价格

图5.3汇总后的E-R图

13.编写一个程序，输出所有学生的学号和平均分，并以口均分递增排序。

解：对应的程序如下:

USEschool

GO字号平均分

SELECTsnoAS，学号)AVG(degree)AS，平均分，1侬72

FROMscore2101745

GROUPBYano310678

ORDERBYAVG(degree)4105815

GO510786

610389

程序执行结果如图:

14.编写一个程序，判断school数据库中是否存在student表。

解：对应的程序如下：

USEschool

GO

IFEXISTS(SELECT*FROMsyaobjectsWHEREname=*student*ANDtype=*U*)

PRINT*存在student表,

ELSE

PRINT'不存在student;表，

GO

15.编写一个程序，查询所有同学参加考试的课程的信息。

解：对应的程序如下：

USEschool

GO

SELECT*

FROMcourse

WHEREEXISTS

(SELECTenoFROMscoreWHEREo=score.^noANDdegreeISNOTNULL)

GO

程序执行结果如下：।飞qI

2凌弊WI

3suerrnm«I

16.编写一个程序，查询所有成绩高于该涯程平均分的记录，且按涯程号有序排列.

解：对应的程序如下：

USEschool

GO

SELECT*

FROMscorea

WHEREdegree>(SELECTAVG(degree)FROMscorebWHEREo=o)

ORDERBYeno

GO

1！8：>1(B0

2105338B

程序执行结果如下：318>105加

<18S

5W6。S

17.创建一个自定义函数maxscore,用于计算给定课程号的最高分，并用相关数据进行测试。

解：对应的程序如下:

USEschool

GO

CREATEFUNCTIONmaxscore(@nochar(5))一建立函数maxscore

RETURNS@stTABLE一返回表下面定义其表结构

snochar(5)r

enochar(5),

maxsfloat

)

AS

BEGIN

INSERT@3t(sno,eno,max3)一向中插入满足条件的记录

SELECTano,eno,degree

FROMscore

WHEREcno=@noANDdegree=(SELECTMAX(degree)

FROMscoreWHEREcno=@no)

RETURN

END

GO

SELECT*FROMmaxscore(•3-105•)

GO

moenoAM

程序执行结果如下:111033-105»

18.给出以下程序的执行结果。

USEschool

SELECTsno,eno,degree

FROMscore10332第ft

沁箝仿聋

WHEREsnoIN(103,105)

ORDERBYsno

COMPUTEAVG(degree)BYsno

GO■noeno

105>1»«

解：结果如下:1053X5n

19.给出以下程序的执行结果。

USEschool

SELECTteacher,tnameAS'教师'，student,sclassAS'班号',

AVG(score.degree)AS'平均分'

FROMstudent,course,score,teacher

WHEREstudent.sno=score.snoANDcourse.cno=score.enoANDcourse.tno=teacher,tno

GROUPBYteacher,tname,student,sclassWITHCUBE

解：结果如下:

□婚夏鲁

Q

6

S

手M

I

x

x

1然

.

£

2

z

w

£

J

l

4

糠通

因我

事如

IB

B

5王绘

创5

.

>

6

m

>

7器

8

lJU

182

818

101

818

601

m

NIJ

z

9

NIJ

33

333233

8083W

NIJ

uu

门，

10

果。

行结

的执

程序

以下

给出

20.

ol

scho

USE

GO

务

启动事

n--

ytra

ONM

ACTI

.ANS

NTR

BEGI

her

teac

NTO

RTI

INSE

）

机系'

'计算

授'，

,‘教

/05'

0/03

'196

‘男'，

英‘，

99/张

ES（9

VALL

录

师记

个教

入一

一插

点

一保存

ran

Myt

TION

NSAC

TRA

SAVE

her

teac

NTO

RTI

INSE

系'）

子工程

'，'电

教授

,'副

/04'

82/8

，‘19

’男‘

丽‘，

/胡

S（888

VALUE

录

师记

个教

入一

一插

ran

My:

TION

NSAC

TRA

BACK

ROLL

ON

ACTI

RANS

ITT

COMM

GO

录

表的记

her

teac

查询

er―

each

Mt

FRO

CT*

SELE

GO

录

的记

插入

删除

9'―

='99

tno

HERE

erW

each

TEt

DELE

GO

解：结果如下:

mo<TMMIMMt«*d4y

1n*u136»12«000100000

825王环女lyz-owsooaooooo汁■机系

831W男1S774B140OWOO0CD电子工电承

布合M男136WUOOOOOOOOO电子工整家

21.编写一个程序，查询最高分的课程名。

解采用子查询方式.程序如下：

USEschool

GO

SELECTcname

FRC»4course

WHEREcno=(SELECTeno

FROMscore

WHEREdegree=(SELECTMAX(degree)

FROMscore))

22.编写一个程序，查询95033班的最高分的学生的学号、姓名、班号、课程号和分数。

解：程序如下：

USEschool

GO

SELECTs.sno,s.sname,s.sclass,sc.cno,sc.degree

FROMstudents,scoresc

WHEREs.3no=sc.snoANDs.sclass=,95033,ANDsc.degree=

(SELECTMAX(degree)

FROMstudent,score

WHEREstudent.sno=score.snoANDstudent.sclass=*95033r

)

GO

23.编写一个程序，查询平均分高于所有平均分的课程号.

解：程序如下：

USEschool

GO

SELECTeno,AVG(degree)

FR(»4score

GROUPBYeno

HAVINGAVG(degree)>(SELECTAVG(degree)FROMscore)

GO

24.编写一个程序，创建一个新表stud,包含所有学生的姓名、课程名和分数，并以姓

名排序。

解：程序如下：

USEschool

GO

SELECTs.sname,ame,ac.degree

INTOstud

FROMstudents,coursec,scoresc

WHERE3.3no=3C.snoANDo=oANDsc.degreeISNOTNULL

ORDERBYs.sname

SELECT*FROMstud

GO

25.编写一个程序，输出每个班最高分的课程名和分数.

解：采用数据来源为SELECT查询结果的方法。程序如下：

USEschool.

GO

SELECTaclass,cname,MAX(degree