数据库练习

目录

第2章 E-R模型

第3章 关系数据模型

 第4章 ER模型到关系模型的转换

第5章 SQL语言 

第6章 数据库安全性

第8章 查询处理与查询优化

第9章 并发控制

第10章 数据库恢复

第11章 关系数据理论

---

第2章 E-R模型

+第4章 E-R->关系

1. 给定一个学生-课程-教师管理系统，存在如下模式：S(Sno, Sname, Sex, Age, Dno)，SC(Sno, Cno, Grade, Year)，C(Cno, Cname, Lhour, Credit, Semester)，T(Tno,Tname,Dno)，D (Dno,Dname)。其中S描述了学生的学号、姓名、性别、年龄和所在系，SC描述了学号、课号、成绩和选课年份，C描述了课号、课程名、学时、学分和学期,T描述了教师的教师编号、姓名、所在系，D描述了系的系号和系名，一名教师只属于一个系，每个系必有教师和学生，有一名教师担任系主任，课程分为必修课和选修课，一门课可由多名教师讲授，且教师必须讲课，学生必上必修课，但最多选修5门选修课。

要求：根据上面描述，请画出E-R模型，并标注主要属性和候选码。

2.（1）学校中有若干系，每个系有若干班级和教研室，每个教研室有若干教员，其中有的教授和副教授每人各带若干研究生，每个班有若干学生，每个学生选修若干课程，每门课可由若干学生选修。请用E-R图画出此学校的概念模型。

（2）某工厂生产著干产品，每种产品由不同的零件组成，有的零件可用在不同的产品上。这些零件由不同的原材料制成，不同零件所用的材料可以相同。这些零件按所属的不同产品分别放在仓库中，原材料按照类别放在若干仓库中。请用E-R图画出此工厂产品、零件、材料、仓库的概念模型。

（3）试着把习题（1）和习题（2）中的E-R模型图转换为关系模式

（4）试用规范化理论中有关范式的概念分析习题7设计的关系模型中各个关系模式的候选码，他们分别属于第几范式？会产生什么更新异常？

（1）
关系模型：
系别（系别号 ，系别名，学校名）
班级（班级号，班级名，系别号）
教研室（教研室编号，教研室名，系别号）
学生（学号，姓名，学历，班级号，导师职工号）
课程（课程号，课程名）
教员（职工号，姓名，职称，科研室编号）
选课（学号，课程号，成绩）

加下划线标注的即为候选码
而且均只有一个码，且都是决定因素，无函数依赖，都是BCNF。
由于均是BCNF，在更新时不会有异常。
（2）
关系模型：
产品（产品号，产品名，仓库号）
零件（零件号，零件名）
材料（材料号，材料名，类别，仓库号，存放量）
仓库（仓库号，仓库名）
产品组成（产品名，零件号，使用零件量）
零件存储（零件号，仓库号，存储量）
零件制造（零件号，材料号，使用材料量）

总结：
简单来讲，对于联系：
如果是1：1或者1：n，就合并；m：n或者三个及以上，就转换成独立的关系模式。
 

第3章 关系数据模型

1.给定一个学生-课程管理系统，存在如下模式：S(Sno, Sname, Sex, Age, Dno)，SC(Sno, Cno, Grade, Year)，C(Cno, Cname, Lhour, Credit, Semester)。其中S描述了学生的学号、姓名、性别、年龄和所在系，SC描述了学号、课号、成绩和选课年份，C描述了课号、课程名、学时、学分和学期。

要求：根据下列要求，分别写出关系代数表达式。

1.查询年龄在18-20之间的学生姓名及选课信息。

2.查询至少选修了“95001”（某人学号）所选修课程的学生学号及姓名。

3.查询选修了“数据库原理”及“操作系统”的学生学号。

4.查询全体女生的基本信息，并将结果模式名改为“女生汇总表”，将结果中的属性名Sno改为“学号”。

 2.

 （1）求供应工程J1零件的供应商号码SNO

（2）求供应工程J1零件P1的供应商号码SNO

（3）求供应工程J1零件为红色的供应商号码SNO

（4）求没有使用天津供应商生产的红色零件的工程号JNO

（5）求至少使用了供应商S1所供应的全部零件的工程号JNO

 第4章 ER模型到关系模型的转换

一、填空题

1. 在实体集转换关系中，关系表的主关键字映射自实体集的   主关键字  

2. 一对一联系转换为关系模式可以有     3     种转换方法

3. 弱实体转换为关系表后主关键字由 强实体集的主关键字 和　弱实体集的部分码 构成

二、选择题

1. 多对多联系转换后的关系表属性描述错误的是   C    

A 包括参加联系的实体集的主关键字属性

B 所有外键构成该关系表的主关键字

C 包含实体集的一般属性

D 包含联系集本身的一般属性

2. 关于联系转换为关系模式，描述错误的是    C     

A　一对一联系可以转换为独立的关系表

B　一对多联系可以与“多”端实体集合并为一个关系表

C　一对多联系转换位独立关系表能够节省存储空间

D　多对多联系只能转换为独立关系表

３.　弱实体转换为关系表后的属性中不包括　　D　　　

A　强实体集的主关键字

B　弱实体集属性

C　联系的一般属性

D　强实体集的一般属性

　

三、思考题

１.　类层次两种转换方式各自的优缺点是什么？

解：方法一：每个实体集对应一张关系表

    优点：（1）当查询仅涉及到父类的属性时则在父类的关系上进行即可;

（2）另一个优点是可以存储非子类的实体

缺点：当查询涉及到子类的属性和其它一些细节属性时需要连接操作

方法二：n个实体集转化为 n-1张关系表

优点：仅涉及到子类的查询，仅在一个关系上进行即可,不需要额外的连接操作；但涉及到父类的查询则需要在两个关系上进行;

缺点：无法存储非子类的实体

四、ER模型设计与关系转换

1. 画出ER图并转换为关系模型

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

科室：科名，科地址，科电话，

病房：病房号，病房位置， 

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

病人：病历号，姓名，性别，诊断，

病人家属：姓名，电话

其中，一个科室有多个病房，多个医生，一个病房只能属于一个科室，一个医生只属于一个科室，但可负责多个病人的诊治，一个病人的主管医生只有一人且只住在一间病房，多名医生可以组成专家组诊治病人，一个病人可以被一个专家组诊治，系统不记录已经离开病房的病人家属。

２.　 画出ER图并转换为关系模型

IT公司数据库信息

部门具有部门编号、部门名称、办公地点；

部门员工具有员工编号、姓名、级别等属性，员工只在一个部门工作；

每个部门有唯一一个部门员工作为经理；

实习生具有实习编号、姓名、年龄等属性，只在一个部门实习；

项目具有项目编号、项目名称、开始日期、结束日期；

每个项目由一名员工负责，由多名员工、实习生参与；

一名员工只负责一个项目，可以参与多个项目，在每个项目具有工作时间比；

每个实习生只参与一个项目。

 

第5章 SQL语言 

1 使用SQL语句定义下列关系表

Doctor：医生表，用来存放医生的信息。
字段	数据类型/长度	含义	备注
DID	char/4	医生编号	主键
DName	char/20	医生姓名	非空
DRate	char /8	医生职别
Department	char /20	所属科室
DOffice	char /20	医生诊室
DSalary	Money类型	医生工资
其他约束： Drate可空，但如果有值必须取自（专家，主任医师，副主任医师，主治医师，住院医师 ）

create table Doctor

(DID char(4) primary key,

 DName nchar(20) not null,

 DRate char(10) check (DRate in('专家','主任医师','副主任医师','主治医师','住院医师')),

 Department char(20),

 DOffice char(20),

 DSalary Money)

Patient：患者表，用来存放患者的信息。
字段	数据类型/长度	含义	备注
PID	char/8	患者编号	主键
PName	char /20	患者姓名	非空
PSex	char /2	患者性别	非空
PAge	char /3	患者年龄
PAdd	char /40	患者地址
PAttribute	char /8	患者类型
MobilePhone	char /11	患者电话
其他约束： （1）性别或者为‘男’，或者为‘女’。 （2）患者类型可空，但如果有值必须取自（普通，贫困，特困） （3）MobilePhone要求不能重复；

create table Patient

(PID char(8) primary key,

 PName nchar(20) not null,

 Psex char(2) not null check (Psex in ('男','女')),

 PAge char(3),

 PAdd char(40),

 PAttribute char(8) check (PAttribute in ('普通','贫困','特困')),

 MobilePhone char(11) unique ))

Treat：就诊表，用来存放患者看诊的信息。
字段	数据类型/长度	含义	备注
PID	char/3	患者编号	主属性
DID	char /4	医生编号	主属性
TDate	Date	就诊日期	主属性
Diagnose	char /200	诊断
Price	Money类型	付账金额
约束： (PID，DID，TDate)为主码； PID为引用患者表的外码，DID为引用医生表的外码。

create table Treat
(PID char(8),
 DID char(4),
 TDate date,
 Diagnose varchar(200),
 Price Money,
 primary key(PID,DID,TDate),
 foreign key (DID) references Doctor(DID),
 foreign key (PID) references Patient(PID))

2 使用SQL语句完成下列查询

1.查询‘呼吸科’医生的情况

Select * 
From Doctor
Where Department=’呼吸科’

2.查询患有’胃’类疾病的患者的姓名

select * 
from Patient, Treat 
where Patient.pid = Treat.pid and diagnose like '%胃%'

3.查询‘特困’患者的姓名，看诊医生姓名和付账金额

select Pname, Dname, Payment
from Doctor, Patient, Treat
where Doctor.DID=Treat.DID and Patient.PID=Treat.PID and PAttribute=’特困’

4.查询共享同一间办公室的两个医生的姓名.

select A.Dname,B.Dname
from Doctor A , Doctor B 
where A.DOffice=B.DOffice and A.DName<>B.DName and A.DName>B.Dname

5.查询所有医生信息及其出诊信息

select Doctor.*,Treat.*
from Doctor left outer join Treat on Doctor.DID=Treat.DID

6.查询人数大于5人的科室和医生的人数

select Department, count(*)
from Doctor
group by department
having count (*)>5

7.查询工资大于同级别平均工资的医生工号和姓名

select A.DID,A.Dname
from Doctor A
where A.DSalary>(select AVG(Dsalary) 
			from Doctor B
				where B.DRate=A.Drate
				group by DRate)

8.查询只去过“儿科”的患者的患者编号.

select Pid
from Treat A, Doctor 
where A.DID=Doctor.DID and Department='儿科' 
 and not exists (select PID
						from Treat B, Doctor 
						where B.DID=Doctor.DID and Department<>'儿科' and  A.PID=B.PID)

9.查询至少去过‘P0001’患者去就诊过的所有科室的患者编号

select P.PID
from Patient P
where P.PID <> 'P0001' and not exists
            (select *
             from Doctor DA, Treat TA
             where DA.DID=TA.DID and TA.PID='P0001'
                   and not exists
                   (select *
                    from Doctor DB, Treat TB
                    where DB.DID=TB.DID and TB.PID=P.PID and DB.Department=DA.Department))

10.删除从未就医的患者信息

delete from Patient 
where PID not in (select PID from Treat)

# 1
select * from S where A=10
# 2
select A,B from S
# 3
select * from S,T where S.C=T.C and S.D=T.D
# 4
select * from S,T where S.C=T.C
# 5
select * from S,T where S.A<T.E
# 6
select S.C, S.D, T.* from S, T

第5章剩余课后习题及答案

第6章 数据库安全性

1、对下列两个关系模式：
学生（学号，姓名，年龄，性别，家庭住址，班级号）
班级（班级号，班级名，班主任，班长）

Stu（Sno，Sname，age，sex，address，Cno）
Class（Cno，Cname，Hmater，Mon）

使用GRANT语句完成下列授权功能：
(1)授予用户U1对两个表的所有权限，并可给其他用户授权。

grant all privileges
on Stu, Class
to U1
with grant option;

(2)授予用户U2对学生表具有查看权限，对家庭住址具有更新权限。

grant select, update(address)
on Stu
to U2;

(3)将对班级表查看权限授予所有用户。

grant select
on Class
to public;

(4)将对学生表的查询、更新权限授于角色R1。

grant select, update
on Stu
to R1;

(5)将角色R1授予用户U1,并且U1可继续授权给其他角色。

grant R1
to U1
with admin option;

7、今有以下两个关系模式:
职工（职工号，姓名，年龄，职务，工资，部门号）
部门（部门号，名称，经理名，地址，电话号）

Emp (eNo., ename, age, position, salary, dnu)
Dep (dnu, dname, mname, address, num)

请用SQL的GRANT和REVOKE语句(加上视图机制)完成以下授权定义或存取控制功能：

（1）用户王明对两个表有SELECT权限。
 

grant select
on Emp, Dep
to 王明;

（2）用户李勇对两个表有INSERT和DELETE权限。

grant insert, delete
on Emp, Dep
to 李勇;

（3）每个职工只对自己的记录有SELECT权限。

grant select
on Emp
when user() = ename
to all;

（4）用户刘星对职工表有SELECT权限，对工资字段具有更新权限。

grant select, update(salary)
on Emp
to 刘星;

（5）用户张新具有修改这两个表的结构的权限。

grant alter
on Emp, Dep
to 张新;

（6）用户周平具有对两个表的所有权限(读、插、改、删数据)，并具有给其他用户授权的权限。

grant all privileges
on Emp, Dep
to 周平
with grant option;

（7）用户杨兰具有从每个部门职工中SELECT最高工资、最低工资、平均工资的权限，他不能查看每个人的工资。

create view Dep_salary(DNO, MaxSalary, MinSalary, AvgSalary)
as
select Dep.dname, MAX(salary), MIN(salary), AVG(salary)
from Emp, Dep
where Emp.dnu = Dep.dnu
grop by Dep.dnu;

grant select
on Dep_salary
to 杨兰;

8、针对习题7中（1）~（7）的每一种情况，撤销各用户所授予的权限：

（1）

revoke select
on Emp, Dep
from 王明;

（2)

revoke insert, delete
on Emp, Dep
from 李勇;

（3）

revoke select
on Emp
when user() = ename
from all;

（4）

revoke select, update(salary)
on Emp
from 刘星;

（5）

revoke alter
on Emp, Dep
from 张新;

（6）

revoke all privileges
on Emp, Dep
from 周平;

（7）

revoke select
on Dep_salary
from 杨兰;

Drop view Dep_salary;

三、写出SQL语句

1 把Employee表中修改Ename属性和Dno属性的权限授给用户Smith

2. 把Department表中全部权限授权给用户Lucy，并允许该用户授权给他人

3. 已知有学生选课表SC（Sno，Cno，Grade）和课程表Course（Cno，Cname，Credit），请通过SQL语句实现允许用户Lee访问每名学生获得总学分数，但不允许其访问学生选修的课程及对应的成绩。（成绩低于60不计学分）

第8章 查询处理与查询优化

1.假设关系R(A,B)和S(B,C,D)情况如下：R有20000个元组，S有1200个元组，一个块能装40个R元组或30个S元组。估算下列操作需要多少次磁盘块读。

（1）R上没用索引，select * from R

（2）嵌套训练连接 R∞S

（3）归并连接R∞S，区分R与S在B属性上有序和无序两种情况。

内存有k块的话 有一块放sc 其他的放students

 2.对学生-课程数据库，查询信息系学生选修了的所有课程名称。

select Cname
from Student, Course, SC
where Student.Sno = SC.Sno and Course.Cno = SC.Cno and Student.Sdept = 'IS';

试画出用关系代数表示的语法树，并用关系代数表达式优化算法对原始的语法树进行优化处理，画出优化后的标准语法树。

 

3.对于下面的数据库模式：
Teacher(Tno​,Tname,Tage,Tsex);
Department(Dno​,Dname,Tno);
Work(Tno​,Dno,Year,Salary);
假设Teacher的Tno属性，Department的Dno属性以及Work的Year属性上有B+树索引，说明下列查询语句的一种较优的处理方法。
（1）select * from Teacher where Tsex=‘女’;
Tsex属性没有索引，而且不是码
全盘扫描算法
（2）select * from Department where Dno<301;
Department的Dno属性是码，且有B+树索引
索引扫描算法
（3）select * from Work where Year<>2000;
不等于2000，那么结果集已经接近全表，所以全表扫描更省时
全表扫描算法
（4）select * from Work where Year>2000 and Salary<5000;
先索引查出符合year条件的，再判断Salary属性是否满足
索引扫描算法
（5）select * from Work where Year<2000 or Salary<5000;
Salary属性没有索引
全盘扫描算法

4.对于题3 的数据库模式有如下查询：

select Tname
from Teacher,Department,Work
where Teacher.Tno=Work.Tno and Department .Dno=Work.Dno and Department.Dname='计算机系' and Salary>5000

画出语法树以及用关系代数表示的语法树，并对关系代数语法树进行优化，画出优化后的语法树。

第9章 并发控制

1.已知：事务T1和T2执行前，A=100,B=50,求：

时间	T1	T2
t1 t2 t3 t4 t5	Xlock(B) Read(B) B:=B-50 Write(B) Unlock(B)
t6 t7 t8 t9 t10 t11 t12 t13		Slock(A) Read(A) Unlock(A) Xlock(B) Read(B) B:=100 Write(B) Unlock(B)
t14 t15 t16 t17 t18	Xlock(A) Read(A) A:=A+50 Write(A) Unlock(A)

1）按照如下并发执行图(图1)执行后的A和B的值；

答：A=150; B=100;

2）该调度是采用两段封锁协议吗？

答:不是采用两段封锁协议

3）请找出T1和T2 两个事务的冲突操作；

R1(B)W1(B)R2(A)R2(B)W2(B) R1(A)W1(A)

冲突操作：
R1(B)<W2(B)
W1(B)<R2(B)
W1(B)<W2(B)
R2(A) <W1(A)

4）该调度是否是可串行化调度？为什么？

答:不是可串行化调度。因为冲突对即存在O1<O2,也存在O2<O1。

课后习题及答案
 

第10章 数据库恢复

请考虑下图所示的日志记录：

序号	日志	序号	日志
1	T1, start	13	checkpoint
2	T1, A, -, 4	14	T7, start
3	T2, start	15	T3, commit
4	T2, B, 20, 5	16	T4, rollback
5	T3, start	17	T5, B, -, 90
6	T1, commit	18	T8, start
7	T2, rollback	19	T6, A, -, 20
8	T3, C, 50, 7	20	T6, commit
9	T4, start	21	T8, A, -, 50
10	T4, A, -, 80	22	T8, commit
11	T5, start	23	T7, C, -, 25
12	T6, start

（1）如果系统故障发生在23之后，说明系统如何进行恢复

T3, T6, T8 redo; T4,T5,T7 undo; T1, T2: 不操作

<T7,C,-,7>

<T7,abort>

<T5,B,-,20>

<T5,abort>

<T4,A,-,4>

<T4,abort>

（2）如果系统故障发生在19和20之间，说明系统如何进行恢复

T3 redo; T4,T5,, T6, T7, T8 undo; T1, T2: 不操作

<T6,A,-,80>

<T8,abort>

<T5,B,-,20>

<T7,abort>

<T6,abort>

<T5,abort>

<T4,A,-,4>

<T4,abort>

 课后习题

第11章 关系数据理论

BCNF分解算法

1.考虑关系模式R（A,B,C,D,E）,回答下面各个问题

（1）若A是R的候选码，具有函数依赖BC-->DE，那么在什么条件下R是BCNF？

（2）如果存在依赖：A -->B，BC-->D，DE -->A，列出R的所有码。

（3）如果存在依赖：A -->B，BC-->D，DE-->A，R属于3NF还是BCNF？

答：（1）属性BC包含码

       （2）ACE,DEC,BCE

       （3）因为ABCDE都是主属性，所以R是3NF，因为所有函数依赖的决定因素A、BC、DE都不含吗，R不是BCFN

2.（1）对于R(A,B,C,D), 设AB是关键字，给定一个函数依赖集FD，使得R∈1NF,但R ∉ 2NF

答：若A->C, 则非主属性C对码AB是部分依赖的，因此不属于2NF。

（2）对于R(A,B,C,D), 设AB是关键字，给定一个函数依赖集FD，使得R∈2NF,但R ∉ 3NF

答：若C->D, 则非主属性D对码AB是传递依赖的，因此不属于3NF。
（3）对于R(A,B,C), B->C∈F, 设A是一个候选关键字，R∈BCNF可能吗？若可能需要什么条件？若不可能则为什么？

答：可能。B也得是候选码。若不可能，则B不是候选码。

（4）对于R(ABCDE), F={A->B, BC ->E, ED->A}
         求出R的所有候选关键字
         R∈3NF？
         R∈BCNF？

答：候选码CDA、CDB、CDE

       R∈3NF但R ∉ BCNF，因为主属性传递ED->A,A->B

3.对于R(ABCDEFGHI), 分解得到下面的关系
R1(ABCDE), A->B, C->D, AC->E
R2(ABF), A->B, B->F
R3(AD), 
R4(DCGH), D->G, G->H, C->D
R5(AICE), A->I, I->A, 
问题：
（1）上述子关系模式最高范式级别？
（2）如果不满足BCNF，进行分解

答：1）Key=(AC)，存在非主属性对码部分依赖，所以R1属于1NF。分解为（AB）（CD）（ACE）。

2）Key=(A)，存在非主属性对码传递依赖，所以R1属于2NF。分解为（AB）（BF）。

3）Key=(AD)，BCNF。

4）Key=(C)，存在非主属性对码传递依赖，所以R1属于2NF。分解为（DG）（GH）（CD）。

5）Key=(ACE)=(ICE)，3NF。分解为（ACE），（AI）或者（AI），（ICE）

4.对于R(ABCD), 对于下面的函数依赖
(1)C->D, C->A, B->C 
(2)B->C, D->A
(3)ABC->D, D->A
(4)A->B, BC->D, A->C
(5)AB->C, AB->D, C->A, D->B
问题：
上述每种情况的所有候选关键字
上述每种情况的最高范式级别？
如果不满足BCNF，进行分解

(1)Key=(B)非主属性传递FD∈ 2NF

    (BC)(ACD)

(2)Key=(BD) 非主属性部分FD∈ 1NF

    (BC)(AD)(BD)

(3)Key=(ABC)=(BCD) 主属性传递 ∈3NF

    (BCD)(AD)

(4)Key=(A) 非主属性传递FD∈2NF

    (ABC)(BCD)

(5)Key=(CD)=(AB)=(AD)=(BC) 主属性传递∈3NF

    (AC)(BD)(CD)