1、数据库系统概论复习资料：第一章假设教学管理规定：一个学生可选修多门课，一门课有若干学生选修；一个教师可讲授多门课，一门课只有一个教师讲授；一个学生选修一门课，仅有一个成绩。学生的属性有学号、学生姓名；教师的属性有教师编号，教师姓名；课程的属性有课程号、课程名。要求：根据上述语义画出 ER 图，要求在图中画出实体的属性并注明联系的类型；解答：第 2 章关系数据库1、设有如下所示的关系 S(S#,SNAME,AGE,SEX)、C(C#,CNAME,TEACHER)和 SC(S#,C#,GRADE)，试用关系代数表达式表示下列查询语句：(1)检索“程军”老师所授课程的课程号(C#)和课程名(CNAM
2、E)。(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
3、#)和姓名(SNAME)。解：本题各个查询语句对应的关系代数表达式表示如下：(1). C#,CNAME(TEACHER=程军(C)(2). S#,SNAME(AGE21SEX=”男”(C)成绩教师编号 教师姓名学生 选修m 课程教师讲授nn1学号姓名课程号 课程名(3). SNAMEs S#,C#（sc）C#(TEACHER= 程军(C)(4). C#(C)- C#(SNAME=李强(S) SC)(5). S#(1=425 (SC SC)(6). C#,CNAME(C (S#,C#(sc)S#(S)(7). S#(SC C#(TEACHER=程军(C)(8). S#,C#（sc）C#(C#=k
4、1 C#=k5(C)(9). SNAMEs S#,C#（sc）C#(C)(10). S#,C#（sc）C#(S#=2(SC)(11). S#,SNAMEs S#(SC CNAME=C 语言(C)2、关系 R 和 S 如下图所示，试计算 RS。RA B C Da b c da b e fa b h kb d e fb d d lc k c dc k e f3、已知关系 r、s、w，如下图所示，计算 T=( (r s)w) S。r 关系 s 关系 w 关系A B C1 b 22 a 31 b 13 a 2A B C2 b 13 b 13 a 13 a 2B Ca 2b 1答：步骤 1：计算出新关系
5、 R1=r sR1=A B C1 b 22 a 31 b 13 a 22 b 13 b 13 a 1步骤 2：计算出新关系 R2=R1w R2=A3SC Dc de f步骤 3：计算出新关系 R3=R2 s R3=A B C3 b 13 b 23 a 2即 T= R3=(r s)w) s4、设有一个 SPJ 数据库，包括 S，P，J，SPJ 四个关系模式(具体见教材 74 页第 5 题)：1）求供应工程 J1 零件的供应商号码 SNO：Sno(Jno=J1 （SPJ）)2）求供应工程 J1 零件 P1 的供应商号码 SNO：Sno(Jno=J1Pno=P1(SPJ)3）求供应工程 J1 零件为
6、红色的供应商号码 SNO：Sno(Pno=P1 （COLOR=红 （P）SPJ）)4）求没有使用天津供应商生产的红色零件的工程号 JNO：Jno(SPJ)- JNO（city=天津Color=红 （SSPJP）5）求至少用了供应商 S1 所供应的全部零件的工程号 JNO：Jno，Pno(SPJ) Pno（Sno=S1 （SPJ） ）第 3 章关系数据库标准语言 SQL1、设有如下关系表 R：R(No，NAME，SEX，AGE，CLASS)主关键字是 NO其中 NO 为学号，NAME 为姓名，SEX 为性别，AGE 为年龄，CLASS 为班号。写出实现下列功能的 SQL 语句。插入一个记录(25
7、， “李明” ， “男” ，21， “95031”)；插入“95031”班学号为 30、姓名为“郑和”的学生记录；将学号为 10 的学生姓名改为“王华” ；将所有“95101”班号改为“95091” ；删除学号为 20 的学生记录；删除姓“王”的学生记录；答案：INSERT INTO R VALUES(25， “李明” ， “男” ，21， “95031”)INSERT INTO R(NO，NAME，CLASS) VALUES(30， “郑和” ， “95031”)UPDATE R SET NAME=“王华”WHERE NO10UPDATE R SET CLASS“95091”WHERE CL
8、ASS“95101”DELETE FROM R WHERE NO=20DELETE FROMR WHERE NAME LIKE“王”2、设学生课程数据库中有三个关系：学生关系 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 之
9、间（含 18 和 20）的女生的学号、姓名和年龄（4）将一个新学生元组（学号：200215128；姓名：王丽；年龄：19；性别：女）插入到 S 表中（5）将选修课程号为“C1”的同学成绩加 5 分（6）找出每个学生超过他选修课程平均成绩的课程号（7）查询查询选修“C2”号课程且成绩在 90 分以上的所有学生。（8）查询所有姓刘的学生的姓名、学号和性别（9）检索平均成绩超过 80 分的学生学号和平均成绩（10）检索选修了全部课程的学生姓名（11）检索选修了三门课以上的学生的姓名（12）创建视图 stu_info，包括选修“C1”号课程所有学生的学号、姓名、性别、年龄、选修课程号、课程名、成绩答案
10、：（1）SELECT SNAME,AGEFROM S,SC,CWHERE S.S#=SC.S#AND C.C#=SC.C#AND CNAME= MATHS(2) SELECT S#FROM SCWHERE CNO=C1 AND S# IN( SELECT S#FROM SCWHERE CNO=C2)(3)SELECT S#,SNAME,AGEFROM SWHERE AGE BETWEEN 18 AND 20(4) INSERT INTO S(S#,SNAME,AGE,SEX) VALUES(“200215128”,”王丽”,19,”女”)；(5) UPDATE SCSET GRADE = GR
11、ADE+5WHERE C#=C1;(6) SELECT S#,C#FROM S X WHERE GRADE=(SELECT AVG(GRADE) FROM C Y WHERE Y.S#=X.S#);(7) SELECT S.S#,SNAME FROM S,SC WHERE S.S#=SC.S# AND SC.C#=C2 AND SC.GRADE90;(8) SELECT SNAME,S#,SEX FROM S WHERE LIKE 刘%;(9) SELECT S# ,AVG(GRADE) 平均成绩FROM SCGROUP BY S#HAVING AVG(GRADE)80(10) SELECT
12、SNAMEFROM SWHERE NOT EXISTS(SELECT *FROM CWHERE NOT EXISTS(SELECT *FROM SCWHERE S#=S.S# AND C#=C.C#) (11) SELECT SNAMEFROM S,SCWHERE S.S#=SC.S#GROUP BY SNAMEHAVING COUNT(*)3 (12)CREATE VIEW STU_INFO AS SELECT S.SNO,SNAME,SEX，AGE，C.CNO，CNAME，GRADEFROM S，SC，CWHERE S.SNO=SC.SNO AND C.CNO= SC.CNO AND C.
13、CNO=C13、设学生-课程数据库中包括三个表： 学生表：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 姓名、S
14、ex 性别;（3）通过上面第 2 题创建的视图修改数据，把王平的名字改为王慧平（4）创建一选修数据库课程信息的视图，视图名称为 datascore_view，包含学号、姓名、成绩。答案：(1) SELECT DISTINCT SNOFROM SC SCXWHERE NOT EXISTS(SELECT *FROM SC SCYWHERE SCY.SNO = 1042 ANDNOT EXISTS(SELECT *FROM SC SCZWHERE SCZ.SNO=SCX.SNO ANDSCZ.CNO=SCY.CNO)；(2) CREATE VIEW S_CS_VIEW ASSELECT SNO,SN
15、AME,SEXFROM STUDENTWHERE SDEPT=CS(3)UPDATE S_CS_VIEWSET SNAME= 王慧平WHERE SNAME= 王平(4) CREATE VIEW DATASCORE_VIEWASSELECT SNO 学号、SNAME 姓名、GRADE 成绩FROM STUDENT,SC,COURSEWHERE STUDENT.SNO=SC.SNOAND COURSE.CNO=SC.CNOAND CNAME= 数据库第 6 章关系数据理论1、设有关系模式 S-L-C(Sno,Sdept,Sloc,Cno,Grade),其中属性分别表示：学号、所在系、学生住处、课程
16、号、成绩。假定每个系的学生住在同一个地方，每个学生可选修多门课程，每门课程可被多个学生选修，请根据语义写出该关系模式的主码及基本函数依赖，并通过模式分解，使其分解为与之等价的 3NF 模式集。答：主码为(Sno，Cno)。基本函数依赖：(Sno，Cno) GradeSno Sdept，(Sno，Cno) SdeptSno Sloc，(Sno，Cno) Sloc，Sdept Sloc分解结果(3NF)：SC(Sno，Cn，Grade)S-D(Sno，Sdept)D-L(Sdept，Sloc)2、已知学生关系模式S(Sno，Sname，SD，Sdname，Course，Grade)其中：Sno 学
17、号、Sname 姓名、SD 系名、Sdname 系主任名、Course 课程、Grade 成绩。(1)写出关系模式 S 的基本函数依赖和主码。(2)原关系模式 S 为几范式？为什么？分解成高一级范式，并说明为什么? (3)将关系模式分解成 3NF，并说明为什么? (1)写出关系模式 S 的基本函数依赖和主码。答: 关系模式 S 的基本函数依赖如下：SnoSname，SDSdname，SnoSD，(Sno，Course) Grade关系模式 S 的码为：（Sno，Course） 。(2)原关系模式 S 为几范式？为什么？分解成高一级范式，并说明为什么?答: 原关系模式 S 是属于 1NF 的，码
18、为(Sno，Course)，非主属性中的成绩完全依赖于码，而其它非主属性对码的函数依赖为部分函数依赖，所以不属于 2NF。消除非主属性对码的函数依赖为部分函数依赖，将关系模式分解成 2NF 如下：S1(Sno，Sname，SD，Sdname)S2(Sno，Course，Grade) (3)将关系模式分解成 3NF，并说明为什么?答: 将上述关系模式分解成 3NF 如下：关系模式 S1 中存在 SnoSD，SDSdname，即非主属性 Sdname 传递依赖于 Sno，所以 S1 不是 3NF。进一步分如下：S11(Sno，Sname,SD) S12(SD，Sdname)分解后的关系模式 S11
19、、S12 满足 3NF。对关系模式 S2 不存在非主属性对码的传递依赖，故属于 3NF。所以，原模式S(Sno，Sname，SD，Sdname，Course，Grade)按如下分解满足 3NF。S11(Sno，Sname，SD) S12(SD，Sdname)S2(Sno，Course，Grade) PP3、建立一个关于系、学生、班级、学会等诸信息的关系数据库。学生：学号、姓名、出生年月、系名、班号、宿舍区。班级：班号、专业名、系名、人数、入校年份。系：系名、系号、系办公地点、人数。学会：学会名、成立年份、办公地点、人数。语义如下：一个系有若干专业，每个专业每年只招一个班，每个班有若干学生。一个
20、系的学生住在同一宿舍区。每个学生可参加若干学会，每个学会有若干学生。学生参加某学会有一个入会年份。请给出关系模式，指出是否存在传递函数依赖，对于函数依赖左部是多属性的情况讨论函数依赖是完全函数依赖，还是部分函数依赖。指出各关系模式的候选码、外部码，有没有全码存在?解：(1)关系模式如下：学生：S(Sno，Sname，Sbirth，Dept，Class，Rno)班级：C(Class，Pname，Dept，Cnum，Cyear)系：D(Dept，Dno，Office，Dnum)学会：M(Mname，Myear，Maddr，Mnum)(2) 传递依赖如下：A、关系模式 S 中，由于 SnoDept，
21、而 DeptSno ，DeptRno（宿舍区）所以 Sno 与 Rno 之间存在着传递函数依赖。由于 ClassDept，Dept Class，Dept Rno所以 Class 与 Rno 之间存在着传递函数依赖。由于 SnoClass，ClassSno，Class Dept所以 Sno 与 Dept 之间存在着传递函数依赖。B、关系模式 C 中，由于 ClassPname，PnameClass，PnameDept所以 C1ass 与 Dept 之间存在着传递函数依赖。C、关系模式 D 中，Dept 与 Office，Dept 与 Dnum 之间不存在传递依赖。D、关系模式 M 中不存在传递依
22、赖。(3)各关系模式的候选码、外部码，全码如下：A、学生 S 候选码：Sno；外部码：Dept、Class；无全码B、班级 C 候选码：Class；外部码：Dept；无全码C、系 D 候选码：Dept 或 Dno；无外部码；无全码D、学会 M 候选码：Mname；无外部码；无全码第 7 章数据库设计1、现有一局部应用，包括两个实体：“出版社”和“作者” ，这两个实体是多对多的联系，请读者自己设计适当的属性，画出 E 一 R 图，再将其转换为关系模型（包括关系名、属性名、码和完整性约束条件） 。答：关系模型为：作者（作者号，姓名，年龄，性别，电话，地址）出版社（出版社号，名称，地址，联系电话）出
23、版（作者号，出版社号，书的数量）出版关系的主码作者号，出版社号分别参照作者关系的主码作者号和出版社关系的主码出版社号。2、设有如下实体：学生：学号、单位、姓名、性别、年龄、选修课程名 课程：编号、课程名、开课单位、任课教师号 教师：教师号、姓名、性别、职称、讲授课程编号 单位：单位名称、电话、教师号、教师名上述实体中存在如下联系：（1） 一个学生可选修多门课程，一门课程可为多个学生选修；（2） 一个教师可讲授多门课程，一门课程可为多个教师讲授；（3） 一个单位可有多个教师，一个教师只能属于一个单位。试完成如下工作：（1） 分别设计学生选课和教师任课两个局部信息的结构 E-R 图。 （2） 将上
24、述设计完成的 E-R图合并成一个全局 E-R 图。 （3） 将该全局 E-R 图转换为等价的关系模型表示的数据库逻辑结构。解：（1） 学生选课、教师任课局部 E-R 图如下所示。开课单位课程选修学生拥有姓名 性别学号 课程名教师号编号单位名年龄11mm nm学生选课局部 E-R 图（2） 合并后的全局 E-R 图如下所示。为避免图形复杂，下面给出各实体属性：单位：单位名、电话学生：学号、姓名、性别、年龄教师：教师号、姓名、性别、职称课程：编号、课程号（3） 该全局 E-R 图转换为等价的关系模型表示的数据库逻辑结构如下：单位（单位名，电话）教师（教师号，姓名，性别，职称，单位名）课程（课程编号，课程名，单位名）学生（学号，姓名，性别，年龄，单位名）讲授（教师号，课程编号）教师课程讲授单位属于单位名 电话编号姓名 性别教师号 年龄m nm1教师授课局部 E-R 图属于单位课程选修学生拥有教师开课 讲授111mmmnnmm全局 E-R 图
