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

试题一

四、设计题

( 第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查询语句： SELECT CNO FROM C

WHERE CNO NOT IN (SELECT CNO FROM S,SC

WHERE S.SNO=SC.SNO

AND SNAME='张三');

请完成下列问题：

（1）用汉语句子阐述上述SQL语句的含义；

（2）用等价的关系代数表达式表示上述SQL查询语句。

解：（1）查询张三同学没有选修的课程的课程号。（2分） （2）πCNO(C)- πCNO (?SNAME='张三' (S)

πCNO(C)- πCNO (?SNAME='张三' (S

SC) 或

SC)) （2分）

2. 设有如图3所示的三个关系。其中各个属性的含义如下：A＃（商店代号）、

ANAME（商店名）、WQTY（店员人数）、CITY（所在城市）、B＃（商品号）、BNAME（商品名称）、PRICE（价格）、QTY（商品数量）。

A# 101 204 256

ANAME 韶山商店 A B

WQTY CITY 15 89 501 长沙 北京 北京 1

前门百货商店 东风商场 B＃

BNAME PRICE 毛笔 羽毛球 收音机 书包 21 784 1325 242 1 2 3 4 345 620 铁道商店 第一百货公司 76 413 长沙 上海 AB

A＃ 101 101 101 101 204 256 256 345 345 345 620 B＃ 1 2 3 4 3 1 2 1 2 4 4 图3

QTY 105 42 25 104 61 241 91 141 18 74 125 试用SQL语言写出下列查询：

（1）找出店员人数不超过100人或者在长沙市的所有商店的代号和商店名。 （2）找出至少供应了代号为’256’的商店所供应的全部商品的其它商店的商

店名和所在城市。

解：（1）SELECT A#, ANAME FROM A

WHERE WQTY<=100 OR CITY='长沙'; （2分） （2）SELECT ANAME,CITY FROM A

WHERE NOT EXISTS (SELECT * FROM B WHERE EXISTS

(SELECT * FROM AB AB1

WHERE A#='256' AND B#=B.B#) AND NOT EXISTS

(SELECT * FROM AB AB2

WHERE A#!='256' AND A#=A.A# AND B#=B.B#)); （4分）

3. 设有职工基本表：EMP（ENO，ENAME，AGE，SEX，SALARY），其属性

分别表示职工号、姓名、年龄、性别、工资。为每个工资低于1000元的女职工加薪200元，试写出这个操作的SQL语句。

UPDATE EMP

SET SALARY=SALARY+200

2

WHERE SALARY<1000 AND SEX='女';（3分）

4. 设某工厂数据库中有两个基本表：

车间基本表：DEPT（DNO，DNAME，MGR_ENO），其属性分别表示车间编号、车间名和车间主任的职工号。

职工基本表：ERP（ENO，ENAME，AGE，SEX，SALARY，DNO），其属性分别表示职工号、姓名、年龄、性别、工资和所在车间的编号。 建立一个有关女车间主任的职工号和姓名的视图，其结构如下：

VIEW6（ENO，ENAME）。试写出创建该视图VIEW6的SQL语句。 参考答案1：

CREATE VIEW VIEW6 AS

SELECT ENO, ENAME FROM EMP WHERE SEX='女' AND ENO IN (SELECT MGR_ENO FROM DEPT)

参考答案2：

CREATE VIEW VIEW6 AS

SELECT ENO, ENAME FROM DEPT, EMP

WHERE MGR_ENO=ENO AND SEX='女' （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中不存在非平凡的函数依赖

3

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图如下图所示。 工产编号 厂名 m 地址 产品编号 n 生产 产品名 规格 工厂 产品 1 计划数量 聘用 n 职工 聘期 工资 职工号 姓名 4

（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分） SELECT DISTINCT JNO FROM SPJ,P

WHERE SPJ.PNO=P.PNO AND COLOR='红'AND JNO='J1';

5

五、综合题（共20分）

1、 仓库号(1分) 零件号（1分） 2、 建立仓库表

Create table warehouse

(

wno varchar(10) primary key, wname varchar(20), square number(10,2), cou int) 建立零件表

Create table material

(

mno varchar2(10) primary key, mname varchar2(20), type varchar2(10), unit varchar2(10), cusnum varchar2(10), price number(10,2)) 建立库存表

Create table storage

(

wno varchar2(10) foreign key references warehouse(wno), mno varchar2(10) foreign key references material(mno), storenumber number(10,2),

primary key(wno,mno)) 评分标准：

3个primary key各1分，2个foreign key各1分，其它1分，根据实际情况斟情扣分。

3、 用SQL表示：

select material.mno,wno,storenumber （1分，mno前无前缀不给分） from material,storage （1分，2个表任少一个不给分） where material.mno=storage.mno and mname=’螺丝（’1分，条件少一个不给分）

31

用关系代数表示：

πmno,wno,storenumber(?mname='螺丝' (material)storage)

1分 1分 1分 4、

Create view VIEW1（1分，少关键字或错写不给分） As

select warehouse.wno, wname, material.mno, mname, price, storenumber （1分，wno前无前缀不给分，少属性不给分） from warehouse, material, storage

（2分，少1个表扣1分，少2个表不给分）

where warehouse.wno=storage.wno and material.mno=storage.mno （2分，1个条件1分）

试题十

四、设计题

（本大题共2小题，第1小题9分，第2小题15，共 24分）

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分）

（2）用SQL查询供应工程J1零件为红色的工程号JNO（不重复）；（3分）

（3）用SQL查询没有使用天津供应商生产的零件的工程号；（3分）

32

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分）

（2）如果关系STUDENT不属于BCNF，请将关系STUDENT逐步分解为巧

BCNF。（7分）

要求：写出达到每一级范式的分解过程，并指明消除什么类型的函数依赖

得 分 五、综合题（共16分）

设有商店和顾客两个实体，“商店”有属性商店编号、商店名、地址、电话，“顾客”有属性顾客编号、姓名、地址、年龄、性别。假设一个商店有多个顾客购物，一个顾客可以到多个商店购物，顾客每次去商店购物有一个消费金额和日期，而且规定每个顾客在每个商店里每天最多消费一次。试画出E-R图，注明属性和联系类型，并将E-R模型转换成关系模式，要求关系模式主码加下划线 表示，外码用波浪线﹏﹏﹏﹏。(E-R模型7分，关系模型9分)

四、设计题（共2小题，第1题9分，第2题15分，共24分）

１、

（1）参考答案：

πJNO(J)- πJNO (?CITY='天津' (S)

评分标准：

SPJ

?COLOR='红' (P))

SPJ

P1分，少任意一个关系不

两个关系的差1分；三个表的自然连接S

（2）

参考答案1：

SELECT DISTINCT JNO

FROM SPJ,P

33

给分；?CITY='天津'和?COLOR='红'两个条件1分，任意一个错误不给分。

WHERE SPJ.PNO=P.PNO AND

COLOR='红'AND JNO='J1';

评分标准：

SELECT DISTINCT JNO FROM SPJ,P 1分，少DISTINCT扣0.5分，SPJ，P中少任一表不给分；WHERE SPJ.PNO=P.PNO AND COLOR='红'AND JNO='J1'2分，3个条件每错一个扣1分，扣完2分为止。 参考答案2：

SELECT DISTINCT SNO

FROM SPJ

WHERE JNO='J1' AND

PNO IN

(SELECT PNO FROM P

WHERE COLOR='红');

评分标准：

SELECT DISTINCT JNO FROM SPJ 1分，少DISTINCT扣0.5分；WHERE JNO='J1' AND

PNO IN

(SELECT PNO FROM P

WHERE COLOR='红')2分，无JNO='J1'、PNO IN、SELECT

PNO FROM P WHERE COLOR='红'均扣1分，扣完2分为止。 （3）

参考答案1：

SELECT JNO

FROM J

WHERE JNO NOT IN (SELECT JNO FROM SPJ

WHERE SNO IN (SELECT SNO FROM S

WHERE CITY='天津')); 评分标准：

SELECT JNO

34

FROM J

WHERE JNO NOT IN 和

SELECT JNO FROM SPJ

WHERE SNO IN

和

SELECT SNO FROM S

WHERE CITY='天津'

三层嵌套各1分，各嵌套内有错误则该项均不给分。 参考答案2：

SELECT JNO FROM J

WHERE NOT EXISTS (SELECT * FROM SPJ,S

WHERE SPJ.SNO=S.SNO AND

SPJ.JNO=J.JNO AND CITY='天津');

评分标准：

SELECT JNO FROM J

WHERE NOT EXISTS 1分； SELECT *

FROM SPJ,S 1分，SPJ,S少一个表不给分； WHERE SPJ.SNO=S.SNO AND

SPJ.JNO=J.JNO AND

CITY='天津'1分，三个条件少一个不给分。

参考答案3：

SELECT JNO

FROM J

WHERE NOT EXISTS (SELECT * FROM SPJ

WHERE SPJ.JNO=J.JNO AND EXISTS (SELECT *

35

FROM S

WHERE S.SNO=SPJ.SNO AND

CITY='天津'));

评分标准：

SELECT JNO

FROM J

WHERE NOT EXISTS 和

SELECT * FROM SPJ

WHERE SPJ.JNO=J.JNO AND EXISTS

和

(SELECT * FROM S

WHERE S.SNO=SPJ.SNO AND

CITY='天津'))

三层嵌套各1分，各嵌套内有错误则该项均不给分。

2、参考答案：

（1）关系STUDENT是1NF，因为F中存在非主属性 SNAME,SDEPT,MNAME对侯选码（S#,CNAME）的部分函数依赖。 （2）首先消除部分函数依赖（S#,CNAME）→SNAME,SDEPT,MNAME

将关系分解为：

R1(S#,SNAME,SDEPT,MNAME)，S#为候选码

F1 = { S#→SNAME,SDEPT,MNAME} R2(S#,CNAME,GRADE)，（S#，CNAME）为候选码

F2={（S#,CNAME）→GRADE}

在关系R1中存在非主属性对候选码的传递函数依赖S#→SDEPT，所以将R1进一步分解：

R11(S#,SNAME,SDEPT) ，S#为候选码

F11 = { S#→SNAME,SDEPT}

R12(SDEPT,MNAME) ，SDEPT为候选码

F12 = { SDEPT→MNAME}

在R2,R11,R12关系模式中函数依赖都是非平凡的，并且决定因素均是候选码，所以上述三个关系模式均是BCNF。

评分标准：

36

（1）回答R是1NF并正确说明理由3分，没有正确回答出理由扣1分。 （2）首先正确将R分解为R1(S#,SNAME,SDEPT,MNAME)（3分）和

R2(S#,CNAME,GRADE)（3分），再将R1正确分解为R11(S#,SNAME,SDEPT)（3分）和R12 SDEPT,MNAME))（3分），其中分解属性正确1分，候选码指定正确1分，函数依赖集书写正确1分。分解过程的叙述斟情扣分。

五、综合题（共16分）

E-R模型：（共7分） 顾客编号 姓名 地址 年龄 性别 商店编号 顾客 m 消费金额 购物 n 商店 日期 商店名 地址 电话 （两个实体型各2分，属性错扣1分，购物联系的两个属性各1分，联系类型1分）

关系模型：（共9分）

顾客（顾客编号，姓名，地址，年龄，性别）（2分，主码1分，其它1分） 商店（商店编号，商店名，地址，电话）（2分，主码1分，其它1分） 购物（顾客编号，商店名称，日期，消费金额）

（5分，主码2分，两上外码各1分，其它1分）

37

本文来源：https://www.bwwdw.com/article/vyj5.html