作业及答案:
第一、七章
1、百货公司管辖若干连锁商店,每家商店经营若干商品,每家商店有若干职工,
但每个职工只能服务于一家商店。有以下几个实体:
“商店”的属性有:商店编号、店名、店址、店经理
“商品”的属性有:商品编号、商品名、单价、产地
“职工”的属性有:职工编号、职工名、性别、工资
1)根据上述语义画出ER图,图中应反映出职工到某商店开始工作的时间,某
家商店销售某种商品的月销售量。
2)将(1)中的ER图转换成关系模型,并说明每个关系模式的主码和外码。
职工(职工编号,职工名,性别,工资,商店编号)主码:职工编号外码:商店编号
商店(商店编号,店名,店址,店经理)主码:商店编号
商品(商品编号,商品名,单价,产地)主码:商品编号
销售(商店编号,商品编号,月销售量)
主码:商店编号+商品编号外码分别是商店编号、商品编号
2、某企业集团有若干工厂,每个工厂生产多种产品,且每一种产品可以在多个
工厂生产,每个工厂按照固定的计划数量生产产品;每个工厂聘用多名职工,且
每名职工只能在一个工厂工作,工厂聘用职工有聘期和工资。工厂的属性有工厂
编号、厂名、地址,产品的属性有产品编号、产品名、规格,职工的属性有职工号、姓名。
1)根据上述语义画出ER 图
2)将(1)中的ER 图转换成关系模型,并说明每个关系模式的主码和外码。 工厂(工厂编号、厂名、地址) 主码:工厂编号 产品(产品编号、产品名、规格)主码:产品编号
职工(职工号、姓名,工厂编号,聘期,工资)主码:职工号 外码:工厂编号
生产(工厂编号,产品编号,计划数量)主码:工厂编号+产品编号 外码:工厂编号、产品编号
第二章
1.已知R 与S ,求下列运算结果
2.关系R与S,求下列运算结果
3. 设有三个关系:学生S(S#, SNAME, AGE, SEX ), 选课SC(S#, C#, GRADE), 课程C(C# , CNAME, TEACHER) ,试用关系代数表达式表示下列查询语言: a) 检索 TEACHER 为’张兵’的课程号和课程名;
#,''(())C CNAME TEACHER C πσ=张兵
Select C#,CNAME From C
Where teacher=’张兵’
b) 检索AGE 在20到22之间学生的学号和姓名
ΠS#,SNAME (σAGE>=20∧AGE<=22(S))
Select s#,sname
From s
Where age>=20 and age<=22
c) 检索学号为’S1’的学生所选课程的课程名称和任课教师
ΠCNAME,TEACHER (σS#=’S1’
C)
Select cname,teacher
From c,sc
Where c.c#=sc.c# and s#=’s1’
d) 检索至少选修了一门由’张兵’老师教授的课程的女生姓名
''''(((())(()))SNAME SEX TEACHER S SC C πσσ== 女张兵
Select sname
From s,sc,c
Where c.c#=sc.c# and s.s#=sc.s# and sex=’女’ and teacher=’张兵’
e) 检索选修了’01’课程且成绩(GRADE )大于80的学生的姓名
ΠSNAME σC#=’01’ ∧ GRADE>80(SC))
Select sname
From s,sc
Where s.s#=sc.s# and c#=’01’ and grade>80
f) 检索’王露’同学没有选修过的课程的编号
##''()()(())C C SNAME C SC S ππσ=- 王露
Select c#
From c
Where c# not in ( select c#
From s,sc
Where s.s#=sc.s# and sname=’ 王露’)
第三章
在jxgl 数据库完成以下查询: (1)查询03物流1学生的详细信息
select * from students
where class='03物流1'
(2)查询“入学成绩”在350到400分之间的学生的姓名和班级(用两种方法)
select sname,class from students
where mgrade between 350 and 400 或
select sname,class from students
where mgrade >= 350 and mgrade <=400
(3)查询students 表中现有的班级(要求班级名称不要重复)
select distinct class from students
(4)查询具有“教授”或“副教授”职称的教师的教师编号和姓名(用两种方法)
select tno,tname,ps
from teachers
where ps in('教授','副教授') 或
select tno,tname,ps from teachers
where ps='教授' or ps='副教授'
(5)查询姓“陈”,且籍贯是“宁波”的学生的姓名,出生日期,入学成绩select sname,bday,mgrade
from students
where sname like '陈%' and bplace='宁波'
(6)查询students表中“09级”的学生信息
select *
from students
where sno like '09%'
(7)查询姓名中包含“菲”字的学生的学号、姓名、性别、班级select sno,sname,ssex,class
from students
where sname like '%菲%'
(8)查询教师上课情况表中还没有安排好上课教师的班级和对应的课程号select class,cno
from tc
where tno is null
(9)查询全体学生情况,查询结果按所在班级名升序排列,同一班级中的学生按出生日期降序排列
select *
from students
order by class desc,bday desc
(10)统计03物流1学生“入学成绩”的平均分、最高分、最低分select avg(mgrade) 平均分,max(mgrade),min(mgrade)
from students
where class='03物流1'
(11)统计students表中的学生人数
select count(*)
from students
*(12)统计各个班级的学生人数,按统计结果做降序排列
select class,count(sno)
from students
group by class
order by count(sno) desc
(13)统计各门课程的平均成绩,结果显示课程号和平均成绩
select cno,avg(grade)
from sc
group by cno
(14)查询平均成绩80以上的课程编号
select cno,avg(grade)
from sc
group by cno
having avg(grade)>=80
(15)查询平均成绩75分以上的学生的学号
select sno,avg(grade)
from sc
group by sno
having avg(grade)>=80
*(16)查询教师人数在5人以上的部门,查询结果显示部门名称和教师人数,查询结果按教师人数作降序排列
select dept,count(tno)
from teachers
group by dept
having count(tno)>5
order by count(tno) desc
(1)查询选修了课程学生的学号、姓名、所选课程号、课程名称以及取得的成绩
select SC.sno,sname,https://www.wendangku.net/doc/cb9712539.html,o,https://www.wendangku.net/doc/cb9712539.html,ame,grade
from SC,Students,Course
where SC.sno=Students.sno and https://www.wendangku.net/doc/cb9712539.html,o=https://www.wendangku.net/doc/cb9712539.html,o;
(2)查询选修了‘计算机基础’这门课程的学生的学号、姓名、班级、成绩select SC.sno,sname,class,grade
from SC,Students,Course
where SC.sno=Students.sno and https://www.wendangku.net/doc/cb9712539.html,o=https://www.wendangku.net/doc/cb9712539.html,o and cname=’计算机基础’;
(4)查询和“刘涛”在一个班级的学生的信息
select *
from Students
where class in(
select class
from Students
where sname='刘涛' );
(5)查询没有选修过任何课程的学生的信息(学号、姓名、班级)
select *
from Students
where sno not in ( select sno from SC);
(6)查询“姚明”给哪些个班级上课?
select class
from tc,teachers
where tname='姚明'and tc.tno=teachers.tno
(7)查询“姚明”给上哪些课程?
select cno
from tc,teachers
where tname='姚明'and tc.tno=teachers.tno
(8)查询学号为“0311102”学生选修课程的学分总和
select sum(ccredit)
from course,sc
where sno='0311102' and https://www.wendangku.net/doc/cb9712539.html,o=https://www.wendangku.net/doc/cb9712539.html,o
(10)查询既选修了“计算机基础”又选修了“C语言基础”的学生的学号select sno
from Course,SC
where https://www.wendangku.net/doc/cb9712539.html,o=https://www.wendangku.net/doc/cb9712539.html,o and cname='计算机基础'
intersect
select sno
from Course,SC
where https://www.wendangku.net/doc/cb9712539.html,o=https://www.wendangku.net/doc/cb9712539.html,o and cname='C语言基础'
(11)查询既选修了“计算机基础”或者选修了“C语言基础”的学生的学号select sno
from Course,SC
where https://www.wendangku.net/doc/cb9712539.html,o=https://www.wendangku.net/doc/cb9712539.html,o and cname='计算机基础'
union
select sno
from Course,SC
where https://www.wendangku.net/doc/cb9712539.html,o=https://www.wendangku.net/doc/cb9712539.html,o and cname='C语言基础'
(1)向course表添加一个新的元组。(元组的可自定义)
insert into Course(cno,cname,ccredit)
values('0000015','标准日本语','2');
(2)将“0301102”学生所选的“0000002”课程的成绩改为100,绩点改为2 update SC
set grade=100,point=2
where sno='0301102' and cno='0000002';
(3)修改“黄圣依”所选的“0000002”课程的成绩,在原成绩上减去10分update sc
set grade=grade-10
where sno=(select sno
from students
where sname='黄圣依') and cno='0000002'
(4)删除(1)在course表中添加的元组
delete from Course
where cno='0000015';
(5)定义视图V_TC,该视图包含教师的编号、姓名、上课的课程号、课程名称及班级。
create view V_TC
as
select Teachers.tno, tname,https://www.wendangku.net/doc/cb9712539.html,o, cname, class
from TC,Course,Teachers
where https://www.wendangku.net/doc/cb9712539.html,o=https://www.wendangku.net/doc/cb9712539.html,o a nd TC.tno=Teachers.tno;
综合实验的内容,不再列举。
第四章
8.请用SQL的GRANT 和REVOKE语句(加上视图机制)完成以下授权定义或存取控制功能:
( a)用户王明对两个表有SELECT 权力。
GRANT SELECT ON TABLE 职工,部门
TO 王明
( b)用户李勇对两个表有INSERT 和DELETE 权力。
GRANT INSERT,DELETE ON TABLE 职工,部门
TO 李勇
( d) 用户刘星对职工表有SELECT 权力,对工资字段具有更新权力。
GRANT SELECT,UPDATE(工资) ON TABLE职工
TO 刘星
( e) 用户张新具有修改这两个表的结构的权力。
GRANT ALTER TABLE ON TABLE职工,部门
TO 张新;
( f) 用户周平具有对两个表所有权力(读,插,改,删数据),并具有给其他用户授权的权力。
GRANT ALL PRIVILIGES ON TABLE 职工,部门
TO 周平
WITH GRANT OPTION;
( g ) 用户杨兰具有从每个部门职工中SELECT 最高工资、最低工资、平均工资的权力,他不能查看每个人的工资。
CREATE VIEW 部门工资(部门号,最高工资,最低工资,平均工资)
AS
SELECT职工.部门号,MAX(工资),MIN(工资),A VG(工资)
FROM 职工,部门
WHERE 职工.部门号=部门.部门号
GROUP BY 职工.部门号
GRANT SELECT ON 部门工资
TO 杨兰;
9 .把习题8 中(1)---(7)的每一种情况,撤销各用户所授予的权力
(1) REVOKE SELECT ON TABLE职工,部门FROM 王明;
(2) REVOKE INSERT , DELETE ON TABLE 职工,部门FROM 李勇;
(4) REVOKE SELECT , UPDA TE(工资) ON TABLE 职工
FROM 刘星;
(5) REVOKE ALTER TABLE ON TABLE 职工,部门
FROM 张新;
(6) REVOKE ALL PRIVILIGES ON TABLE 职工,部门
FROM 周平;
(7) REVOKE SELECT ON 部门工资
FROM 杨兰;
DROP VIEW 部门工资;
第五章
6 .假设有下面两个关系模式:职工(职工号,姓名,年龄,职务,工资,部门号),其中职工号为主码;部门(部门号,名称,经理名,电话),其中部门号为主码。用SQL 语言定义这两个关系模式,要求在模式中完成以下完整性约束条件的定义:
(1)定义每个模式的主码;(2)定义参照完整性;(3)定义职工年龄不得超过60 岁。
答:
CREATE TABLE DEPT
(Deptno CHAR(5) RIMARY KEY,
Deptname V ARCHAR(10),
Manager V ARCHAR(10),
PhoneNumber Char(12));
CREATE TABLE EMP
( Empno CHAR(10),
Ename V ARCHAR(10),
Age INTCHECK ( Aage<=60) ,
Job V ARCHAR(9),
Sal NUMERIC(7,2),
Deptno NUMBER(2),
FOREIGN KEY(Deptno) REFFERENCES DEPT (Deptno));
第六章
1、现有如下关系模式:R(A#,B#,C,D,E),其中:A#B#组合为候选码,R上存在的函数依赖有A#B#→E,B#→C,C→D。请完成:
1)该关系模式满足2NF吗?为什么?
不满足。在关系R中,A#B#组合为候选码,属性C函数依赖于B#,那么非主属性C 部分函数依赖于候选码,不符合2NF的要求。
2)如果将关系模式R分解为:R1(A#,B#,E),R2(B#,C,D),指出关系模式R2的码,并说明关系模式R1,R2最高满足第几范式?(在1NF~BCNF之内)
R2的码是B#
R1满足BCNF要求。
R2中存在B#→C,C→D即存在非主属性D传递函数依赖于候选码,所以R2满足2NF 要求。
3)将关系模式R分解到3NF
可将R分解为:R1(A#,B#,E),R2(B#,C),R3(C,D)
2、设有关系模式R(职工号,日期,日营业额,部门名,部门经理)。现利用该模式统计商店里每个职工的日营业额,职工所在的部门和部门经理。规定如下:每个职工每天只能有一个营业额;每个职工只在一个部门工作;每个部门只有一个经理。
1)写出模式R的基本函数依赖和候选码
根据已知条件,可写出基本的函数依赖关系有三个:
职工号→ 部门名
部门名→ 部门经理
(职工号,日期)→营业额
从上述三个函数依赖,可知R的候选码为(职工号,日期)。
2)判断是否是2NF,如果不是如何进行模式分解?
从上述三个函数依赖,可推出下列函数依赖成立:
(职工号,日期)→部门名
(职工号,日期)→部门经理
这两个都是非主属性对码的部分函数依赖。因此R不满足2NF要求。
R应分解成R1(职工号,日期,营业额)
R2(职工号,部门名,部门经理)
这两个模式都是2NF模式。
3)判断是否是3NF,如果不是如何进行模式分解?
R1已是3NF模式,但R2不是3NF模式。因为在R2中的基本函数依赖有两个:职工号→部门名,部门名→部门经理。
显然,R2存在传递依赖:职工号→部门经理。
因此R2不满足3NF要求。
R2应分解成R21(职工号,部门名)
R22(部门名,部门经理)