习题课
1. 设关系模式R(职工名,项目名,工资,部门名,部门经理)，若每个职工可参加多个项目，各领一份工资；每个项目只属于一个部门管理；每个部门只有一个经理。 ⑴试写出关系模式R的基本函数依赖和关键码。
⑵说明R不是2NF模式的理由，并把R分解成2NF模式集。 ⑶进而把R分解成3NF模式集。
（职工名，项目名）->工资，部门名，部门经理 项目名->部门名，部门名->部门经理 关键码：职工名，项目名 R1（职工名，项目名，工资） R2（项目名，部门名，部门经理） R21（项目名，部门名），R22(部门名，部门经理) 2.设某商业数据库中有3个实体集：一是“仓库”实体集，属性有仓库号、仓库名和地址等；二是“商店”实体集，属性有商店号、商店名、地址等；三是“商品”实体集，属性有商品号、商品名、单价。仓库与商品之间存在着“库存”联系，每个仓库可存储若干商品，每种商品存储在若干仓库中，每个仓库存储一种商品有个日期及存储量；商店与商品之间存在着“销售”联系，每个商店可销售若干商品，每种商品可在若干商店里销售，每个商店销售一种商品有月份和月销售量两个属性；仓库、商店、商品之间存在着“供应”联系，有月份和月供应量两个属性。
⑴试画出ER图，并在图上注明属性、联系类型、实体标识符。 ⑵将ER图转换成关系模型，并说明主码和外码。
商品m月份nn日期月销售量销售供应库存存储量npmm商店 仓库月份关系：
仓库（仓库号、仓库名，地址） 商店（商店号、商店名、地址） 商品（商品号、商品名、单价）
销售（商店号，商品号，月份，月销售量），外码：商店号，商品号 库存（仓库号，商品号，日期，存储量）外码：仓库号，商品号 供应（仓库号，商店号，商品号，月份，月供应量），外码：仓库号，商店号，商品号
月供应量
3.对于教学数据库的三个基本表
学生S(S#,SNAME,AGE,SEX)、学习SC(S#,C#,GRADE)、课程C(C#,CNAME,TEACHER) 试用SQL语句完成：求选修课程名为“ENGLISH”课程的学生的平均年龄。 select avg(age) 平均年龄 from s where s# in
(select s# from sc where c#=
(select c# from c where cname=’english’))
4.教学数据库的数据模式如上题。
试采用外键子句约束定义下列完整性约束：在关系SC中插入元组时，其S#值和C#值必须分别在S和C中出现。 Constraint c1 Foreign key(sno) references student(sno), Foreign key(cno) references c(cno)
5．设有关系R与S，计算R
2?2?S
R S
A 2 4 7 B 3 5 8 C 4 6 9 C 7 4 E 5 8 参考答案：
A 7 B 8 R.C 9 S.C 7 E 5
6. 设有三个关系：
S（S#，SNAME，AGE，SEX） C（C#，CNAME，TEACHER） SC（S#，C#，GRADE）
试用关系代数表达式表示下列查询语句：
（1）检索“陈军”老师所授课程的课程号（C#）和课程名（CNAME）。 （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#)和姓名(SNAME)。
5. 本题各个查询操作对应的关系代数表达式表示如下： （1）∏C#,CNAME(σTEACHER=’陈军’(C)) （2）∏S#,SNAME(σAGE>21∧SEX=’ 男’(S)) （3）∏SNAME(S?（∏S#,C#(SC)?∏C#(σ （4）∏C#(C)－∏C#(σ （5）∏S# (σ
TEACHER=’陈军’(C))
SNAME=’李强’(S) ?SC)
1=4∧2≠5(SC×SC)
（6）∏C#,CNAME(C?（∏S#,C#(SC)?∏S#(S)） （7）∏S#(SC?∏C#(σ
TEACHER=’陈军’(C))
C#=’k1’∨ C#=’k5’(C))
（8）∏S#, C# (SC)?∏C#(σ
（9）∏SNAME(S? (∏S#,C#(SC)?∏C#(C))) （10）∏S#,C# (SC)?∏C#(σ
S#=’2’(SC))
（11）∏S#,SNAME (S? (∏S#(SC? (σCNAME=’C语言’(C))))
7．
试用SQL语句完成下列查询：
（1）检索王立老师所授课程的课程号和课程名。
（2）检索学号为10001学生所学课程的课程名与任课教师。 （3）检索至少选修王立老师所授课程中一门课程的女学生姓名。 （4）检索张伟同学不学的课程的课程号。 （5）在表C中统计开设课程的教师人数。
（6）求LIU老师所授课程的每门课程的平均成绩。 （7）求选修C4课程的女学生的平均年龄。
5. 答：对应的SQL语句如下： （1） SELECT C#, CNAME FROM C
WHERE TEACHER='王立'; （2） SELECT CNAME, TEACHER FROM SC, C
WHERE SC.C#=C.C# AND S#＝'10001'; （3）采用连接查询方式：
SELECT SNAME FROM S, SC, C
WHERE S.S#=SC.S# AND SC.C#=C.C# AND SEX='女' AND TEACHER='王立'; （4） SELECT C# FROM C
WHERE NOT EXISTS （SELECT * FROM S, SC
WHERE S.S#=SC.S# AND SC.C#=C.C# AND SNAME='张伟'）; （5）在表C中统计开设课程的教师人数。 SELECT COUNT(distinct ctea) FROM Course （6）求LIU老师所授课程的每门课程的平均成绩。
SELECT avg(grade) FROM sc
WHERE C# IN (SELECT C# FROM C WHERE TEACHER='LIU')
（7）求选修C4课程的女学生的平均年龄。与第3题类似。