数据库综合题复习题

1.设有关系R和S，其值如下：

        R   A   B   C       S   B   C   D

            2   4   6           4   5   6

            3   6   9           4   6   8

            3   4   5           6   8   0

                                6   9   2

2=1

       试求 R⋈ S、 R⋈ S的值。

2=1

解： R⋈ S       A   B   C   D                R⋈ S       A  R.B  R.C S.B S.C  D 

                            2     4     6     8                                 2     4     6     4     5     6

                3   6   9   2                   2   4   6   4   6   8  

                3   4   5   6                   3   6   9   6   8   0

                                                3   6   9   6   9   2  

                                                3   4   5   4   5   6

                                                3   4   5   4   6   8      

2.对于教学数据库的三个基本表
　　 　　学生 S(S#,SNAME,AGE,SEX)
　　 　　学习 SC(S#,C#,GRADE)
　　　　 课程 C(C#,CNAME,TEACHER)
 (1)试用SQL语句完成：在基本表S中检索每一门课程成绩都大于等于80分的学生学号、姓名和性别，并把检索到的值送往另一个已存在的基本表STUDENT（S＃，SNAME，SEX）。

　　 　INSERT INTO STUDENT(S#,SNAME,SEX)

　　 　　SELECT S#,SNAME,SEX

　　 　　FROM S WHERE 　NOT EXISTS

　　　　　　(SELECT * FROM SC WHERE

　　　　　　 GRADE<80 AND S.S#=SC.S#)

(2)在以上的基本表中，完成以下操作：检索选修课程包含LIU老师所授课的学生学号。

　　 SELECT DISTINCT S#
　　 　　FROM SC

　　 　　WHERE C# IN

　　 　　　(SELECT C#

　　 　　　FROM C

　　 　　　WHERE TEACHER='LIU')) 　　

(3)在以上的基本表中，写出与下列关系代数表达式一致的SQL语句：

πcno(C)-πcno(σsname='LIU'(SC C))

　

  SELECT CNO

   FROM C

   WHERE CNO NOT IN

    (  SELECT CNO

       FROM S,SC

       WHERE S.SNO=SC.SNO

         AND SNAME='LIU');

3.对于教学数据库的三个基本表

S（SNO,SNAME,AGE,SEX,SDEPT）（学号，姓名，年龄，性别，系别）

SC(SNO,CNO,GRADE)（学号，课程号，成绩）

C(CNO,CNAME,CDEPT,TNAME)（课程号，课程名，归属系，教师名）

试用SQL查询语句表示下列查询语句：

(1) 求选修4号课程的女学生的平均年龄。

SELECT AVG(AGE)

FROM S,SC

  WHERE S.S#=SC.S# AND C#=’4’AND SEX=’F’

(2) 求年龄大于女同学平均年龄的男学生姓名和年龄。

Select sname,age

from s

where sex=’m’ and age>(select avg(age) from s where sex=’F’)

(3) 求LIU老师所授课程的每门课的平均成绩。

Select c.cno,avg(grade)

from sc,c

where sc.cno=c.cno and teacher=’LIU’ group by c.cno

(4) 检索姓名以L打头的所有学生的姓名和年龄。

Select sname,age from s where sname like ‘L%’

（5）在表SC中检索成绩为空的学生学号和年龄

Select sno,cno

from sc

where grade is null

4.设有关系R和S，如下图所示，请计算：

B<C

计算 σ_A

 

_=C(R×S),R     S,R     S

R							S
A		B					B		C
a		b					b		c
c		b					e		a
d		e					b		d
R    S					B<C      R     S
A	B		C		A	B		C
a	b		c		a	b		c
a	b		d		a	b		d
c	b		c		c	b		c
c	b		d		c	b		d
d	e		a		σ A   =C(R×S)
					A	R.B		S.B		C
					a	b		e		a
					c	b		b		c
					d	e		b		d

 

5. 对于教学数据库的三个基本表

　　 　　学生 S(S#,SNAME,AGE,SEX)

　　 　　学习 SC(S#,C#,GRADE)

　　　　 课程 C(C#,CNAME,TEACHER)

(1)试用SQL语句完成：在基本表S中检索每一门课程成绩都大于等于80分的学生学号、姓名和性别，并把检索到的值送往另一个已存在的基本表STUDENT（S＃，SNAME，SEX）。（4分）

　　 　INSERT INTO STUDENT(S#,SNAME,SEX)

　　 　　SELECT S#,SNAME,SEX

　　 　　FROM S WHERE 　NOT EXISTS

　　　　　　(SELECT * FROM SC WHERE

　　　　　　 GRADE<80 AND S.S#=SC.S#)

(2)在以上的基本表中，完成以下操作：检索选修课程包含LIU老师所授课的学生学号。（3分）　　 　

　　 SELECT DISTINCT S#

　　 　　FROM SC

　　 　　WHERE C# IN

　　 　　　(SELECT C#

　　 　　　FROM C

　　 　　　WHERE TEACHER='LIU')) 　　

(3)在以上的基本表中，写出与下列关系代数表达式一致的SQL语句：（3分）

π_cno(C)-π_cno(σ_sname='LIU'(SCC))

SELECT CNO

      FROM C

     WHERE CNO NOT IN

       (  SELECT CNO

          FROM S,SC

          WHERE S.SNO=SC.SNO

             AND SNAME='LIU');

 

6. 有一关系模式P（A,B,C,D,E,F,G,H,I,J），根据语义定义有如下函数依赖集：F={ABD->E，AB->G，B->F，C->J，C->I，G->H}。

试问关系模式R最高已经达到第几范式？为什么？并请将该关系模式分解至BCNF范式。

候选码：ABCD

非主属性：E,F,G,H,I,J

因非主属性E,F,G,H,I,J均非完全依赖于候选码，所以关系模式最高达到1NF。（2分）

第一步(转化为第二范式)（2分）	第二步(转化为第三范式，同时也为BCNF范式)（6分）
R1（ABDE）ABD->E	R1（ABDE）ABD->E
R2(ABGH) AB->G,G->H	R21(ABG) AB->G  R22(GH) G->H
R3(BF) B->F	R3(BF) B->F
R4(CIJ) C->I,C->J	R4(CIJ) C->I,C->J
R5(ABCD)	R5(ABCD)

 

7. 学生与教师教学模型

（1）有若干班级，每个班级包括：班级号、班级名、专业、人数、教室

（2）每个班级有若干学生，学生只能属于一个班，学生包括：学号、姓名、性别、年龄

（3）有若干教师，教师包括：编号、姓名、性别、年龄、职称

（4）开设若干课程，课程包括：课程号、课程名、课时、学分

（5）一门课程可由多名教师任教，一名教师可任多门课程。

（6）一门课程有多名学生选修，每名学生可选多门课，但选同一门课时，只能选其中一名教师。

解题要求：

(1)画出每个实体及其属性关系、实体间实体联系的（E-R）图。

(2)根据试题中的处理要求：完成数据库逻辑模型，包括各个表的名称和属性。

　

答：(1)

(2)

班级（班级号，班级名，专业，人数，教室），主键：班级号

学生（学号，姓名，性别，年龄，班级号），主键：学号       外键：班级号

教师（编号，姓名，性别，年龄，职称），主键：编号

课程（课程号，课程名，课时，学分），主键：课程号

选课（课程号，学号，成绩），主键：课程号，学号       外键：课程号；学号

任课（课程号，编号，时间），主键：课程号，编号       外键：课程号；编号

8. 设有两个基本表R（A，B，C）和S（D，E，F），试用SQL查询语句表达下列关系代数表达式：

　　（1）π_A(R)　　(2)σ_B='17'(R) 　　(3)R×S　　(4))π_A,F(σ_C=D(R×S))

(1)SELECT A FROM R     (2)SELECT * FROM R WHERE B='17'

(3)SELECT A,B,C,D,E,F FROM R,S    (4)SELECT A,F FROM R,S WHERE R.C=S.D

2、（本题10分）设有三个关系：

S(S#,SNAME,AGE,SEX)

SC(S#,C#,GRADE)

C(C#,CNAME,TEACHER)

试用关系代数表达式表示下列查询语句。

(1)检索至少选修LIU老师所授课程中一门课程的女学生的姓名。

p  sname (stname=’liu’ Ùsex=‘女’(S ⋈ SC ⋈ C))

(2)检索WANG同学不学的课程号。

p cno (C) - p cno (ssname=’wang’(S ⋈ SC))

(3)检索至少选修两门课程的学生学号。

p  sno (s1=4 Ù2≠5(SC ⋈ SC))

(4)检索全部学生都选修课程的课程号与课程名。

p  cno, cname (C ⋈ (p sno,cno(SC ) ÷p  sno(S)))

(5)检索年龄大于23岁的男学生的学号与姓名

p  sno, sname (sage>23 Ùsex=‘男’(S))

9. 设有关系R与S

R：

A  B  C 2  3  4 4  5  6 7  8  9

S：

D  E 7  5 4  8

计算 π_3，2(R),  σ_A=D(R×S)

　 π_3，2(R):                 σ_A=D(R×S) :   

 C    B                         A    B    C     D    E

 4   3                           7    8    9    7    5

 6   5                           4    5    6    4    8

9         8    

10. 设R=ABCD,R上的F={A→C，D→C，BD→A}, 试证明ρ={AB,ACD,BCD}相对于F不是无损联接分解。

   根据算法

A B C D AB a1 a2 b13 b14 ACD a1 b22 a3 a4 BCD b31 a2 a3 a4	A B C D a1 a2 a3 b14 a1 b22 a3 a4 b31 a2 a3 a4
(1) 构造表	(2)根据A→C，D→C，BD→A进行处理

   没有一行都是a，所以，ρ相对于F不是无损联接分解。

11. 某医院病房计算机管理中需要如下信息：

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

病房：病房号，床位号，所属科室名

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

病人：病历号，姓名，性别，诊断，主管医生，病房号

其中，一个科室有多个病房、多个医生，一个病房只能属于一个科室，一个医生只属于一个科室，但可负责多个病人的诊治，一个病人的主管医生只有一个。

完成如下设计：

（1）设计该计算机管理系统的E－R图；

（2）将该E-R图转换为关系模型结构；

（3）指出转换结果中每个关系模式的候选码。

解：（1）本题的E-R图如图2所示。

    （2）对应的关系模型结构如下：

     科室（科名，科地址，科电话）

     病房（病房号，床位号，科室名）

     医生（工作证号，姓名，职称，科室名，年龄）

     病人（病历号，姓名，性别，诊治，主管医生，病房号）

    （3）每个关系模式的候选码如下：

     科室的候选码是科名；

     病房的候选码是科室名十病房号；

     医生的候选码是工作证号；

     病人的候选码是病历号。

 

n

病人

入住

病房

1

病房号

床位号

工作证号

姓  名

性  别

病历号

n

医生

从属

科室

1

科  名

科电话

科地址

职  称

年  龄

姓  名

诊治

组成

n

1

n

1

图2  E－R图

12. 设有关系R和函数依赖F：

R（W，X，Y，Z），F = { X→Z，WX→Y }。

试求下列问题：

（1）关系R属于第几范式？

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

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

解：R是1NF。侯选码为WX，则Y，Z为非主属性，又由于X→Z，因此F中存在非主属性对侯选码的部分函数依赖。

W

XY

Y

ZY

 

 

 

 

将关系分解为：

R1（W，X，Y），F1 = { WX→Y }

R2（X，Z），F2 = { X→Z }

消除了非主属性对码的部分函数依赖。

F1和F2中的函数依赖都是非平凡的，并且决定因素是候选码，所以上述关系模式是BCNF。

13.数据模型分析，关系模型R（ABCDEG）F={AC→E，CB→G，BCD→AG，BD→A，AB→G,A→C}

(1) 求此模型的最小函数依赖集。

(2) 求出关系模式的候选码。

(3) 此关系模型最高属于哪级范式。

依照题意，得出：

(1) 解：

F中的FD右部变为单属性， F={AC→E，BC→G，BCD→A，BCD→G，BD→A，AB→G，A→C}

消去左边的冗余属性：F={A→E，BC→G，BD→A，BC→G，BD→A，AB→G，A→C}

消去冗余的函数依赖：Fmin={A→E，BC→G，BD→A，A→C}

                  也可以为：  Fmin={A→E，AB→G，BD→A，A→C}

(2) 候选码：BD

(3) R中每一个非主属性完全函数依赖于R的候选键BD；但C,G都传递依赖于R的候选键BD，也就是说，R满足2NF的要求，而不满足3NF的要求。此关系模型最高属于2NF。