数据库 考试大题.docx

数据库 考试大题.docx

《数据库 考试大题.docx》由会员分享，可在线阅读，更多相关《数据库 考试大题.docx（19页珍藏版）》请在冰豆网上搜索。

数据库考试大题

假设教学管理规定：

①一个学生可选修多门课，一门课有若干学生选修；

②一个教师可讲授多门课，一门课只有一个教师讲授；

③一个学生选修一门课，仅有一个成绩。

学生的属性有学号、学生姓名；教师的属性有教师编号，教师姓名；课程的属性有课程号、课程名。

要求：

根据上述语义画出ER图，要求在图中画出实体的属性并注明联系的类型；

解答：

三、应用题：

设有如下所示的关系S（S#,SNAME,AGE,SEX）、C（C#,CNAME,TEACHER）和SC（S#,C#,GRADE），试用关系代数表达式表示下列查询语句：

（1）检索“程军”老师所授课程的课程号（C#）和课程名（CNAME）。

（2）检索年龄大于21的男学生学号（S#）和姓名（SNAME）。

（3）检索至少选修“程军”老师所授全部课程的学生姓名（SNAME）。

（4）检索”李强”同学不学课程的课程号（C#）。

（5）检索至少选修两门课程的学生学号（S#）。

（6）检索全部学生都选修的课程的课程号（C#）和课程名（CNAME）。

（7）检索选修课程包含“程军”老师所授课程之一的学生学号（S#）。

（8）检索选修课程号为k1和k5的学生学号（S#）。

（9）检索选修全部课程的学生姓名（SNAME）。

（10）检索选修课程包含学号为2的学生所修课程的学生学号（S#）。

（11）检索选修课程名为“C语言”的学生学号（S#）和姓名（SNAME）。

解：

本题各个查询语句对应的关系代数表达式表示如下：

（1）.∏C#,CNAME（σTEACHER=‘程军’（C））

（2）.∏S#,SNAME（σAGE>21∧SEX=”男”（C））

（3）.∏SNAME{s[∏S#,C#（sc）÷∏C#（σTEACHER=‘程军’（C））]}

（4）.∏C#（C）-∏C#（σSNAME=‘李强’（S）SC）

（5）.∏S#（σ[1]=[4]∧[2]≠[5]（SC×SC））

（6）.∏C#,CNAME（C（∏S#,C#（sc）÷∏S#（S）））

（7）.∏S#（SC∏C#（σTEACHER=‘程军’（C）））

（8）.∏S#,C#（sc）÷∏C#（σC#=’k1’∨C#=’k5’（C））

（9）.∏SNAME{s[∏S#,C#（sc）÷∏C#（C）]}

（10）.∏S#,C#（sc）÷∏C#（σS#=’2’（SC））

（11）.∏S#,SNAME{s[∏S#（SCσCNAME=‘C语言’（C））]}

3、设有如下关系表R：

R（No，NAME，SEX，AGE，CLASS）

主关键字是NO

其中NO为学号，NAME为姓名，SEX为性别，AGE为年龄，CLASS为班号。

写出实现下列功能的SQL语句。

①插入一个记录（25，“李明”，“男”，21，“95031”）；。

②插入“95031”班学号为30、姓名为“郑和”的学生记录；。

③将学号为10的学生姓名改为“王华”；。

④将所有“95101”班号改为“95091”；。

⑤删除学号为20的学生记录；。

⑥删除姓“王”的学生记录；。

答案：

①INSERTINTORVALUES（25，“李明”，“男”，21，“95031”）

②INSERTINTOR（NO，NAME，CLASS）VALUES（30，“郑和”，“95031”）

③UPDATERSETNAME=“王华”WHERENO＝10

④UPDATERSETCLASS＝“95091”WHERECLASS＝“95101”

⑤DELETEFROMRWHERENO=20

⑥DELETEFROMRWHERENAMELIKE“王％”

1、设学生课程数据库中有三个关系：

学生关系S（S#，SNAME，AGE，SEX）

学习关系SC（S#，C#，GRADE）

课程关系C（C#，CNAME）

其中S#、C#、SNAME、AGE、SEX、GRADE、CNAME分别表示学号、课程号、姓名、年龄、性别、成绩和课程名。

用SQL语句表达下列操作

（1）检索选修课程名称为“MATHS”的学生的学号与姓名

（2）检索至少学习了课程号为“C1”和“C2”的学生的学号

（3）检索年龄在18到20之间（含18和20）的女生的学号、姓名和年龄

（4）检索平均成绩超过80分的学生学号和平均成绩

（5）检索选修了全部课程的学生姓名

（6）检索选修了三门课以上的学生的姓名

答案：

（1）SELECTSNAME,AGE

FROMS,SC,C

WHERES.S#=SC.S#

ANDC.C#=SC.C#

ANDCNAME=’MATHS’

（2）SELECTS#

FROMSC

WHERECNO=’C1’ANDS#IN（SELECTS#

FROMSC

WHERECNO=’C2’）

（3）SELECTS#,SNAME,AGE

FROMS

WHEREAGEBETWEEN18AND20

（4）SELECTS#,AVG（GRADE）‘平均成绩’

FROMSC

GROUPBYS#

HAVINGAVG（GRADE）>80

（5）SELECTSNAME

FROMS

WHERENOTEXISTS

（SELECT*

FROMC

WHERENOTEXISTS

（SELECT*

FROMSC

WHERES#=S.S#ANDC#=C.C#

）

）

（6）SELECTSNAME

FROMS,SC

WHERES.S#=SC.S#

GROUPBYSNAME

HAVINGCOUNT（*）>3

2、设学生-课程数据库中包括三个表：

学生表：

Student（Sno，Sname，Sex，Sage，Sdept）

课程表：

Course（Cno，Cname，Ccredit）

学生选课表：

SC（Sno，Cno，Grade）

其中Sno、Sname、Sex、Sage、Sdept、Cno、Cname、Ccredit、Grade分别表示学号、姓名、性别、年龄、所在系名、课程号、课程名、学分和成绩。

试用SQL语言完成下列项操作：

（1）查询选修课程包括“1042”号学生所学的课程的学生学号

（2）创建一个计科系学生信息视图S_CS_VIEW，包括Sno学号、Sname姓名、Sex性别;

（3）通过上面第2题创建的视图修改数据，把王平的名字改为王慧平

（4）创建一选修数据库课程信息的视图，视图名称为datascore_view，包含学号、姓名、成绩。

答案：

（1）SELECTDISTINCTSNO

FROMSCSCX

WHERENOTEXISTS

（SELECT*

FROMSCSCY

WHERESCY.SNO='1042'AND

NOTEXISTS

（SELECT*

FROMSCSCZ

WHERESCZ.SNO=SCX.SNOAND

SCZ.CNO=SCY.CNO））；

（2）CREATEVIEWS_CS_VIEW

AS

SELECTSNO,SNAME,SEX

FROMSTUDENT

WHERESdept=’CS’

（3）UPDATES_CS_VIEW

SETSNAME=’王慧平’

WHERESNAME=’王平’

（4）CREATEVIEWdatascore_view

AS

SELECTSNO学号、SNAME姓名、GRADE成绩

FROMSTUDENT,SC,COURSE

WHERESTUDENT.SNO=SC.SNO

ANDCOURSE.CNO=SC.CNO

ANDCNAME=’数据库’

三、设计题

在学生课程管理数据库中创建一触发器，当向学生选课表插入记录时，检查该记录的学号在学生表中是否存在，检查该记录的课程号在课程表中是否存在，及选课成绩是否在0到100范围，若有一项为否，则不允许插入。

答案：

createtriggerstu_ins_tri

onsc

forinsert

as

begin

declare@s#char（6）,@c#char（5）,@gradeint

select@s#=sno,@c#=cno,@grade=score

frominserted

if（@s#notin（selectsnofromstudent））or（@c#notin（selectcnofromcourse））or（@gradenotbetween0and100）

rollbacktransaction

else

print'成功插入'

end

四、综合练习

1、已知学生关系模式

S（Sno，Sname，SD，Sdname，Course，Grade）

其中：

Sno学号、Sname姓名、SD系名、Sdname系主任名、Course课程、Grade成绩。

（1）写出关系模式S的基本函数依赖和主码。

（2）原关系模式S为几范式？

为什么？

分解成高一级范式，并说明为什么?

（3）将关系模式分解成3NF，并说明为什么?

（1）写出关系模式S的基本函数依赖和主码。

答:

关系模式S的基本函数依赖如下：

Sno→Sname，SD→Sdname，Sno→SD，（Sno，Course）→Grade

关系模式S的码为：

（Sno，Course）。

（2）原关系模式S为几范式？

为什么？

分解成高一级范式，并说明为什么?

答:

原关系模式S是属于1NF的，码为（Sno，Course），非主属性中的成绩完全依赖于码，而其它非主属性对码的函数依赖为部分函数依赖，所以不属于2NF。

消除非主属性对码的函数依赖为部分函数依赖，将关系模式分解成2NF如下：

S1（Sno，Sname，SD，Sdname）

S2（Sno，Course，Grade）

（3）将关系模式分解成3NF，并说明为什么?

答:

将上述关系模式分解成3NF如下：

关系模式S1中存在Sno→SD，SD→Sdname，即非主属性Sdname传递依赖于Sno，所以S1不是3NF。

进一步分解如下：

S11（Sno，Sname,SD）S12（SD，Sdname）

分解后的关系模式S11、S12满足3NF。

对关系模式S2不存在非主属性对码的传递依赖，故属于3NF。

所以，原模式S（Sno，Sname，SD，Sdname，Course，Grade）按如下分解满足3NF。

S11（Sno，Sname，SD）

S12（SD，Sdname）

S2（Sno，Course，Grade）

2、设有如下关系R

（1）它为第几范式?

为什么?

（2）是否存在删除操作异常?

若存在，则说明是在什么情况下发生的?

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

（1）解:

它是2NF。

因为R的候选关键字为“课程名”。

依赖关系:

课程名→教师名，教师名→课程名，教师名→教师地址，所以课程名→教师地址。

即存在非主属性“教师地址”对候选关键字课程名的传递函数，因此R不是3NF。

但：

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

（2）解:

存在。

当删除某门课程时会删除不该删除的教师的有关信息。

（3）解:

分解为高一级范式如图所示。

R1如下:

R2如下:

分解后，若删除课程数据时，仅对关系R1操作，教师地址信息在关系R2中仍然保留，不会丢失教师方面的信息。

3、设某商业集团数据库中有一关系模式R如下：

R（商店编号，商品编号，数量，部门编号，负责人）

如果规定：

（1）每个商店的每种商品只在一个部门销售；

（2）每个商店的每个部门只有一个负责人；（3）每个商店的每种商品只有一个库存数量。

试回答下列问题：

（1）根据上述规定，写出关系模式R的基本函数依赖；

答:

关系模式S的基本函数依赖如下：

（商店编号，商品编号）→部门编号，（商店编号，部门编号）→负责人，（商店编号，商品编号）→数量

（2）找出关系模式R的候选码；答:

关系模式R的码为：

（商店编号，商品编号,部门编号）。

（3）试问关系模式R最高已经达到第几范式？

为什么？

答:

原关系模式R是属于1NF的，码为（商店编号，商品编号,部门编号），非主属性对码的函数依赖全为部分函数依赖，所以不属于2NF。

消除非主属性对码的函数依赖为部分函数依赖，将关系模式分解成2NF如下：

R1（商店编号，商品编号,部门编号,数量）

R2（商店编号，部门编号,负责人）

（4）如果R不属于3NF，请将R分解成3NF模式集。

答：

将R分解为

R1（商店编号，商品编号,部门编号,数量）

R2（商店编号，部门编号,负责人）

分解后的R不存在传递的函数依赖，所以分解后的R已经是第3NF

三、应用题

设有如下实体：

学生：

学号、单位、姓名、性别、年龄、选修课程名课程：

编号、课程名、开课单位、任课教师号教师：

教师号、姓名、性别、职称、讲授课程编号单位：

单位名称、电话、教师号、教师名

上述实体中存在如下联系：

（1）．一个学生可选修多门课程，一门课程可为多个学生选修；

（2）．一个教师可讲授多门课程，一门课程可为多个教师讲授；（3）．一个单位可有多个教师，一个教师只能属于一个单位。

试完成如下工作：

（1）．分别设计学生选课和教师任课两个局部信息的结构E-R图。

（2）．将上述设计完成的E-R图合并成一个全局E-R图。

（3）．将该全局E-R图转换为等价的关系模型表示的数据库逻辑结构。

解：

（1）．学生选课、教师任课局部E-R图如下所示。

学生选课局部E-R图

教师授课局部E-R图

（2）．合并后的全局E-R图如下所示。

全局E-R图

为避免图形复杂，下面给出各实体属性：

单位：

单位名、电话

学生：

学号、姓名、性别、年龄

教师：

教师号、姓名、性别、职称

课程：

编号、课程号

（3）．该全局E-R图转换为等价的关系模型表示的数据库逻辑结构如下：

单位（单位名，电话）

教师（教师号，姓名，性别，职称，单位名）

课程（课程编号，课程名，单位名）

学生（学号，姓名，性别，年龄，单位名）

讲授（教师号，课程编号）

选修（学号，课程编号）

四、简答题

数据库设计一般分为哪几个阶段，每个阶段的主要任务是什么？

解答：

（1）数据库设计分为6个阶段：

需求分析、概念结构设计、逻辑结构设计、物理结构设计、数据库实施、数据库运行和维护。

（2）各阶段任务如下：

①需求分析：

准确了解与分析用户需求（包括数据与处理）。

②概念结构设计：

通过对用户需求进行综合、归纳与抽象，形成一个独立于具体DBMS的概念模型。

③逻辑结构设计：

将概念结构转换为某个DBMS所支持的数据模型，并对其进行优化。

④数据库物理设计：

为逻辑数据模型选取一个最适合应用环境的物理结构（包括存储结构和存取方法）。

⑤数据库实施：

设计人员运用DBMS提供的数据语言、工具及宿主语言，根据逻辑设计和物理设计的结果建立数据库，编制与调试应用程序，组织数据入库，并进行试运行。

⑥数据库运行和维护：

在数据库系统运行过程中对其进行评价、调整与修改。

设计题

1、今有两个关系模式：

职工（职工号，姓名，年龄，职务，工资，部门号）Person（pno,pname,page,ptitle,psal,dno）部门（部门号，名称，经理名，地址，电话号）Depart（dno,dname,dmanager,daddress,dtel）请用SQL的GRANT和REVOKE语句（加上视图机制）完成以下授权定义或存取控制功能：

（1）用户yong对两个表有INSERT和DELETE权力。

GRANTINSERT,DELETEONPerson,Depart

TOyong

（2）用户liu对职工表有SELECT权力，对工资字段具有更新权力。

GRANTSELECT,UPDATE（psal）ONPerson

TOliu

（3）用户zhang具有修改这两个表的结构的权力。

GRANTALTERTABLEONPerson,Depart

TOzhang;

（4）用户yang具有从每个部门职工中SELECT最高工资、最低工资、平均工资的权力，他不能查看每个人的工资，并具有给其他用户授权的权力。

CREATEVIEWpsalAS

SELECTDepart.dname,MAX（psal）,MIN（psal）,AVG（psal）

FROMPerson,Depart

WHEREPerson.dno=Depart.dno

GROUPBYPerson.dno

GRANTSELECTONpsal

TOyangwithgrantoption;

（5）撤销各用户yang所授予的权力

REVOKEALLPRIVILIGESONPerson,DepartFROMYANG;

•2、假设有下面两个关系模式：

职工（职工号，姓名，年龄，职务，工资，部门

号），其中职工号为主码；部门（部门号，名称，经理名，电话），其中部门号

为主码。

用SQL语言定义这两个关系模式，要求在模式中完成以下完整性约束条

件的定义：

定义每个模式的主码；定义参照完整性；定义职工年龄不得超过60岁。

答

CREATETABLEDEPT

（DeptnoNUMBER

（2）,

DeptnameVARCHAR（10）,

ManagerVARCHAR（10）,

PhoneNumberChar（12）

CONSTRAINTPK_SCRIMARYKEY（Deptno））;

CREATETABLEEMP

（EmpnoNUMBER（4）,

EnameVARCHAR（10）,

AgeNUMBER

（2）,

CONSTRAINTC1CHECK（Aage<=60）,

JobVARCHAR（9）,

SalNUMBER（7,2）,

DeptnoNUMBER

（2）,

CONSTRAINTFK_DEPTNO

FOREIGNKEY（Deptno）

REFFERENCESDEPT（Deptno））;

•3、设某商业集团关于商店销售商品的数据库中有三个基本表：

•商店SHOP（S#，SNAME，AREA，MGR_NAME）其属性是商店编号，商店名称，区域名，经理姓名。

销售SALE（S#，G#，QUANTITY）其属性是商店编号，商品编号，销售数量。

商品GOODS（G#，GNAME，PRICE）

•其属性是商品编号，商品名称，单价。

•

（1）试写出下列查询的关系代数表达式、元组表达式和关系逻辑规则：

检索销售“冰箱”的商店的编号和商店名称。

解：

关系代数表达式：

πS#，SNAME（σGNAME='冰箱'（SHOP⋈SALE⋈GOODS））

元组表达式：

{t|（u）（v）（w）（SHOP（u）∧SALE（v）∧GOODS（w）

∧u[1]=v[1]∧v[2]=w[1]∧w[2]='冰箱'∧t[1]=u[1]∧t[2]=u[2]）}

关系逻辑规则：

W（u1,u2）SHOP（u1,u2,u3,u4）∧SALE（u1,v2,v3）∧GOODS（v2,'冰箱',w3）

•

（2）试写出上面第

（1）问的SELECT语句表达形式。

解：

SELECT语句如下：

SELECTA.S#，SNAME

FROMSHOPA，SALEB，GOODSC

WHEREA.S#=B.S#ANDB.G#=C.G#ANDGNAME='冰箱'；

•（3）试写出下列操作的SQL语句：

从SALE表中，把“开开商店”中销售单价高于1000元的商品的销售元组全部删除。

解：

DELETEFROMSALE

WHERES#IN（SELECTS#

FROMSHOP

WHERESNAME='开开商店'）

ANDG#IN（SELECTG#

FROMGOODS

WHEREPRICE>1000）；

•（4）写一个断言，要求区域名为“EAST”的商店里销售商品的单价不能低于100元。

解：

CREATEASSERTIONASSE8CHECK

（100<=ALL（SELECTPRICE

FROMSHOPA，SALEB，GOODSC

WHEREA.S#=B.S#ANDB.G#=C.G#ANDAREA='EAST'））；

或CREATEASSERTIONASSE8CHECK

（NOTEXISTS（SELECT*

FROMSHOPA，SALEB，GOODSC

WHEREA.S#=B.S#ANDB.G#=C.G#

ANDAREA='EAST'ANDPRICE<100））；

•（5）试写出下列操作的SQL语句：

•统计区域名为“EAST”的所有商店销售的每一种商品的总数量和总价值。

•要求显示（G#，GNAME，SUM_QUANTITY，SUM_VALUE），其属性为商品编号、商品名称、销售数量、销售价值。

解：

SELECTC.G#，GNAME，SUM（QUANTITY）ASSUM_QUANTITY，

PRICE*SUM（QUANTITY）ASSUM_VALUE

FROMSHOPA，SALEB，GOODSC

WHEREA.S#=B.S#ANDB.G#=C.G#ANDAREA='EAST'

GROUPBYC.G#，GNAME；

（注：

SELECT子句中的属性C.G#，GNAME应在分组子句中出现）

4、今有一个层次数据库实例，试用子女一兄弟链接法和层次序列链接法画出它的存储结构示意图。

WelcomeTo

Download!

!

!

欢迎您的下载，资料仅供参考！