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 |
2=1 |
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 |
=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 |
| ||||||||||
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 | S: | D E |
计算 π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不是无损联接分解。
根据算法
|
| ||||||||||||||||||||||||||||||||||||
(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。