数据库原理及应用期末考试复习题库+2017(1)(3) 下载本文

A a1 a2 a3 a4 NULL B b1 b2 b1 b4 b2 C 3 2 8 7 4 D d1 d1 d2 d1 d2 E e1 e2 e2 e2 e1

A a1 a2 a3 a4 a1 a4 B b1 b2 b1 b4 b2 b3 C 3 2 8 7 5 4 D d1 d1 d2 d1 NULL NULL E e1 e2 e2 e2 NULL NULL e1 NULL b2 4 d2

3.设有一个数据库,包括以下一些基本资料:

零件表P 由零件代码(PNO)、零件名(PNA)、颜色(COL)、重量(WEI)组成; 供应情况表SPJ 由供应商代码(SNO)、零件代码(PNO)、供应数量(QTY)组成,

表示某供应商供应某种零件的数量为QTY。

求Π SNO,PNO(SPJ)÷ Π PNO(σ COL= ‘红’(P))的结果,并描述其查询功能。

Π PNO(σ COL= ‘红’(P))结果:{P1,P3}

Π SNO,PNO(SPJ)中S1象集{P1,P2,P3}; S2象集{P1,P3}; S3象集{P1,P6}; Π SNO,PNO(SPJ)÷ Π PNO(σ COL= ‘红’(P))结果:{S1,S2} 功能是查询至少供应了所有红色零件的供应商号。

4.设有一个数据库,包括以下一些基本资料:

课程表C 由课程号(CNO)、课程名(CNA)、授课教师工号(TNO)、学分(CRE)组成; 选修情况表SC 由学生学号(SNO)、课程号(CNO)、成绩(GRA)组成。 求Π SNO , CNO(SC)÷ Π CNO(σ TNO = ‘ T4 ‘(C))的结果,并描述其查询功能。

Π CNO(σ TNO = ‘ T4 ‘(C))结果:{C4,C5}

Π SNO,PNO(SC)中S1象集{C1,C4,C5}; S2象集{C4,C6}; S3象集{C4,C5}; Π SNO , CNO(SC)÷ Π CNO(σ TNO = ‘ T4 ‘(C))结果:{S1,S3} 功能是查询至少选修了T4号老师教授的所有课程的学生学号。

5. 已知关系模式R(ABCD),F={A→B, B→C, D→B},求出属性集闭包A+,(AD)+和(BD)+,并给出一候选键。

解:A+=ABC, (AD)+ =ABCD, (BD)+ =BCD L:AD R:C LR:B N:空唯一候选键:AD

- 21 -

6.已知关系模式R, U={A,B,C,D,E,G}, F={AC→B, CB→D, A→BE,E→GC},求AB,BC,AC是否为关系R的候选码。

解:设K为R中的属性或属性组合。若KU,则K称为R的侯选码。 BC不是候选码,AB、AC是超码。

AB+=ABCDEG=U, AC+=ABCDEG=U, BC+=BCD,可以推出BC不是候选码; 进一步分析,A+=ABCDEG =U,即AB和AC都不是候选码的最小集,只是超码; 候选码应该是A。

7. 设有关系模式R(A,B,C,D,E)和R的函数依赖集F={ A→BC,CD→E,B→D,E→A },求R的所有候选码。

解:A,B,C,D,E都是LR类,并令Y=ABCDE。

A+=ABCDE B+=BD C+=C D+=D E+=EABCD 候选码有A、E,令Y=BCD BC+=ABCDE BD+=BD CD+=ABCDE BC、CD也为候选码 R的所有候选码有A、E、BC、CD。

8.设有关系模式R(U,F),其中:U={A,B,C,D,E,G},F={ BG→C,BD→E,DG→C,DAG→CB,AG→B,B→D },求F的最小依赖集Fmin。 解:

BG→C BG+=BGDEC 冗余 BD→E BD+=BDE

+

DG→C DG=DGC

+

DAG→C DAG=DAGBEC 冗余 DAG→B DAG+=DAGBEC 冗余 AG→B AG+=AGBCDE

+

B→D B=BDE 消除BD→E冗余属性可得B→E

Fmin={ B→E, DG→C , AG→B , B→D }

9.对如下关系R,指出是否存在多值依赖C→→HR? 为什么?

答:设R(U)是一个属性集U上的一个关系模式,X、Y和Z是U的子集,并且Z=U-X-Y。关系模式R(U)中多值依赖 X→→Y成立,当且仅当对R(U)的任一关系r,给定的一对(x,z)值,有一组Y的值,这组值仅仅决定于x值而与z值无关

上图关系中不存在多值依赖C→→HR。

根据定义,C对应X, (H,R)对应Y,则Z为(T,S,G)

当C取C1,(T,S,G)取(T1,S1,G1)时候,得(H,R)一组值{(H1,R1),(H2,R2)} 当C取C1,(T,S,G)取(T1,S2,G2)时候,得(H,R)一组值{(H1,R1)} 即与多值依赖定义矛盾。

若加一条元组(C1,T1,H2,R2,S2,G2)可满足C→→HR。

五、应用题

1.设有3个关系:

S(SNO,SNAME,AGE,SEX) SC(SNO,CNO,CNAME) C(CNO,CNAME,TEACHER)

试用关系代数表达式表示下列查询语句: 1)检索LIU老师所授课程的课程号和课程名。 ∏CNO,CNAME(σTEACHER=‘LIU’(C))

- 22 -

2)检索年龄大于23岁的男学生的学号和姓名。 ∏SNO,SNAME(σAGE>23∧SEX=‘男’(S))

3)检索学号为S3学生所学课程的课程名与任课教师名。 ∏CNAME,TEACHER(σSNO=‘S3’(SC∞C))

4)检索选修了LIU老师所授课程的女学生姓名。 ∏SNAME(σSEX=‘女’∧TEACHER=‘LIU’(S∞SC∞C)) 5)检索WANG同学不学的课程的课程名。

∏CNAME(C)-ΠCNAME(σSNAME=‘WANG’(S∞SC∞C)) 6)检索全部学生都选修的课程的课程号与课程名。

∏CNO,CNAME(C∞(πSNO,CNO(SC)÷πSNO(S)))

7)检索至少选修了LIU老师所授全部课程的学生学号。

∏SNO,CNO(SC)÷∏CNO(σTEACHER=‘LIU’(C))

2.设有下列四个关系模式:

S(SNO,SNAME,CITY)

P(PNO,PNAME,COLOR,WEIGHT) J(JNO,JNAME,CITY)

SPJ(SNO,PNO,JNO,QTY)

其中,供应商表S由供应商号(SNO)、供应商姓名(SNAME)、供应商所在城市(CITY)组成,记录各个供应商的情况;零件表P由零件号(PNO)、零件名称(PNAME)、零件颜色(COLOR)、零件重量(WEIGHT)组成,记录各种零件的情况;工程项目表J由项目号(JNO)、项目名(JNAME)、项目所在城市(CITY)组成,记录各个工程项目的情况;供应情况表SPJ由供应商号(SNO)、零件号(PNO)、项目号(JNO)、供应数量(QTY)组成,记录各供应商供应各种零件给各工程项目的数量。分别用关系代数和SQL语言完成下列查询: 1)求供应工程项目号为J1工程零件的供应商号SNO

∏SNO(σJNO=‘J1’(SPJ))

SELECT SNO FROM SPJ WHERE JNO=‘J1’; 2)求供应工程项目号为J1工程零件号为P1的供应商号SNO

∏SNO(σJNO=‘J1’∧PNO=‘P1’(SPJ))

SELECT SNO FROM SPJ WHERE JNO=‘J1’ AND PNO=‘P1’; 3)求供应工程项目号为J1工程红色零件的供应商号SNO

∏SNO(σJNO=‘J1’∧COLOR=‘红’(SPJ∞P))

SELECT SNO FROM SPJ,P WHERE SPJ.PNO=P.PNO AND P.COLOR=‘红’AND JNO=‘J1’; 4)求没有使用天津供应商生产的红色零件的工程号JNO

∏JNO(SPJ)-∏JNO(σcity=‘天津’∧COLOR=‘红’(SPJ∞P∞S)) SELECT JNOFROM JWHERE JNO NOT IN (

SELECT JNOFROM JSP, S, P WHERE JSP.SNO=S.SNOAND JSP.PNO=P.PNO AND S.CITY=‘天津’AND P.COLOR=‘红’);

5)求至少用了S1供应商所供应的全部零件的工程号JNO(本题不需要用SQL语言完成)

∏JNO,PNO(SPJ)÷∏PNO(σSNO=‘S1’(SPJ))

3.设有两个关系模式:

职工(职工号,姓名,性别,年龄,职务,工资,部门号) 部门(部门号,部门名称,经理名,地址,电话) 依据此关系回答下面问题:

(1)试用关系代数表达式查询’销售部’女职工的职工号和姓名。 π职工号,姓名(σ部门名称=‘销售部’?性别=‘女’(职工∞部门))

(2)试用SQL语句将年龄大于60岁的’策划部’的职工工资上调1000元。 UPDATE 职工

SET 工资=工资+1000

WHERE 年龄>60 AND 部门号=

(SELECT 部门号 FROM 部门 WHERE 部门名称=‘策划部’ (3)试用SQL语句统计每个部门的人数。 SELECT部门号,COUNT(职工号) FROM 职工

GROUP BY 部门号

- 23 -

(4)试用SQL语句检索’人事部’姓’王’的职工姓名和年龄,按年龄降序排列。 SELECT 姓名,年龄 FROM 职工

WHERE 姓名 LIKE ‘王%’ AND 部门号=

(SELECT 部门号FROM 部门WHERE 部门名称=‘人事部’) ORDER BY 年龄 DESC;

(5)试用SQL语句定义一个包含姓名、性别、工资、职务和部门名称的视图IS_A。 CREATE VIEW IS_A(姓名,性别,工资,职务,部门名称) AS SELECT 姓名,性别,工资,职务,部门名称 FROM 职工,部门

WHERE职工.部门号=部门.部门号;

4.已知有如下三个关系: 学生(学号,姓名,专业)

项目(项目号,项目名称,报酬) 参加(学号,项目号,工时)

其中,报酬是指参加该项目每个工时所得报酬。 依据此关系回答下面问题:

(1)试用关系代数表达式查询’王明’同学所参加项目的名称。 π项目名称(σ姓名=‘王明’(学生∞参加∞项目))

(2)试用SQL语句将报酬最高的项目名称前加上’重点:’前缀。 UPDATE 项目

SET 项目名称=‘重点:’+项目名称

WHERE 报酬=(SELECT MAX (报酬)FROM 项目)

(3)试用SQL语句查询报酬大于800元(包括800元)的项目名称。 SELECT 项目名称 FROM 项目

WHERE 报酬>=800

(4)试用SQL语句查询每个专业所有学生参加各个项目所获得的总报酬。 SELECT 系别号,SUM(报酬*工时) FROM 学生,项目,参加

WHERE 学生.学号=参加.学号AND 项目.项目号=参加.项目号 GROUP BY 专业

(5)试用SQL命令创建视图IS_A,该视图包含的属性有:学号,姓名和项目名称。 CREATE VIEW IS_A(学号,姓名,项目名称) AS

SELECT 学生.学号,姓名,项目名称 FROM 学生,项目,参加

WHERE 学生.学号=参加.学号AND 项目.项目号=参加.项目号 (6)在项目表的项目号字段上按降序建立聚簇索引。

CREATE CLUSTERED INDEX a1ON 项目(项目号DESC);

5.设有一个S-T数据库,包括STUDENTS,COURSES,REPORTS三个关系模式,

学生表STUDENTS (学号SNO,姓名SNAME,生日SBIRTH,性别SSEX,所在系SDEPT); 课程表COURSES (课程号CNO,课程名CNAME,先行课PRE_CNO,学分CREDITS); 学生选课表REPORTS (学号SNO,课程号CNO,成绩GRADE); 试用SQL语言完成如下操作:

(1)按如下要求创建REPORTS表;

表名 列名 数据类型 宽度 约束 外码 SNO VARCHAR 3 主码

外码 REPORTS CNO VARCHAR 20

取值在0~100之间 GRADE INT

CREATE TABLE REPORTS

( SNOVARCHAR(3) REFERENCES STUDENTS(SNO),

- 24 -