数据库系统概论复习题（关系代数，SQL语句，函数依赖、范式、并发控制）

本文为个人整理学习，如有错误，欢迎指正！
整理的并不完整，因为考完也不想看了(ง •_•)ง

关系代数

运算符		含义
集合运算符	∪	并
	-	差
	∩	交
	×	笛卡尔积
专门的关系 运算符	σ	选择
	∏	投影
	▷◁	连接
	÷	除

笛卡儿积（cartesian product）
这里的笛卡儿积严格的讲应该是广义的笛卡儿积，因为这里笛卡儿积的元素是元组。
两个分别为n目和m目的关系R和S的笛卡尔积是一个（n+m）列的元组的集合。元组前n列是关系R的一个元组，后m列是关系S的一个元组。若R有 k₁个 元组，S有 k₂ 个元组，则关系R和关系S的笛卡儿积有k₁xk₂个元组。

专门的关系运算符

1.选择
选择又称限制,它是在关系R中选择满足给定条件的诸元组。
记作 σ_F（R) = {t|t∈∧F(t)=‘真‘}
逻辑表达式F的基本形式为X₁θY₁
θ可以是比较运算符（＞≥＜≤＝◇）也可以是逻辑运算符（┐∧∨）
例如：查询信息系（IS系）全体学生 σ_{Sdept=’IS’}(Student)
           查询年龄小于20岁的男生：σ_Sage<20(Student)
2.投影
关系R上的投影是从R中选择出若干属性列组成新的关系。记作∏_A（R）={t[A] | t∈R} 其中A为R的属性列。
投影操作是从列的角度进行的运算。
例如：查询学生的姓名和所在系 ∏_Sname,Sdept(Student)
           查询学生关系Student都有哪些系 ∏_Sdept(Student)
3.连接
连接也成为θ连接，它是从两个关系的笛卡儿积中选择属性间满足一定条件的元组。
记作R▷◁S
连接运算中有两种最为重要也最为常用的运算，一种是等值连接，另一种是自然连接。
θ等于”=“的连接称为等值连接，它是从关系R和S的广义笛卡儿积中选取A、B属性组相等的那些元组。
自然连接是一种特殊的等值连接，他要求两个关系中进行比较的分量必须是同名的属性组，并且在结果中把重复的属性列去掉。
此外，还有外连接，左外连接，右外连接。
注：这个视频让我看懂了！
4.除运算
设关系R除以关系S的结果为关系T，则T包含所有在R但不在S中的属性及其值，则T的元组与S的元组的所有组合都在R中。
例如：查询选修了全部课程的学生号码和姓名 ∏_Sno,Cno(SC)÷∏_Cno(Course)▷◁∏_Sno,Sname(Student)
注：课本P55，P56的例2.9真的说的很详细了！！！

练习题1：
现在如下三个关系，分别描述学生、课程和选修信息：
S(S#,SNAME,AGE,SEX)
C(C#,CNAME,TEACHER)
SC(S#,C#,GRADE)
请用关系代数完成下列查询：
（1）检索“程军”老师所授课程的课程号(C#)和课程名(CNAME)；
（2）检索年龄大于 21 的男学生学号(S#)和姓名(SNAME)；
（3）检索至少选修“程军”老师所授全部课程的学生姓名(SNAME)；
（4）检索李强同学未选修课程的课程号(C#)。

(1)∏_C#,CNAME(σ_{TEACHER=‘程军’}（C）)
(2)∏_S#,SNAME(σ_{SEX='男’∧AGE>21}（S）)
(3)∏_SNAME(S▷◁[∏_C#,S#(SC)÷∏_C#(σ_{TEACHER=‘程军’}（C）)])
(4)∏_C#（C) - ∏_C#(σ_{Sname=‘李强’}（S)▷◁SC)

练习题2：
本题用到下面三个关系表：
Card借书卡（Cno 卡号，Name姓名，Class 班级）
Book图书（Bno 书号，Bname 书名，Author作者，Price单价）
Borrow借书记录（Cno借书卡号，Bno书号，Bcount借书本数，Day 借书天数）
要求使用关系代数完成以下问题：
（1）查询单价大于30元的图书的详细信息
（2）查询"01"班所有学生的姓名
（3）查询有借书天数超过30天的学生姓名
（4）查询卡号为"S03"的学生借了几本书
（5）查询没有借过书的学生的卡号

（1）σ_Price>30(Book)
（2）∏_Name(σ_{Class=“01”}(Card))
（3）∏_Name(σ_Day>30(Borrow)▷◁Card)
（4）∏_Bcount(σ_{Cno=“S03”}(Borrow))
（5）∏_Cno(Card) - ∏_Cno(Borrow)

SQL语句

WHERE子句中不能用聚集函数作为条件表达式。聚集函数只能用于SELECT子句中与GROUP BY中的HAVING子句中

带有EXISTS谓词的子查询

EXISTS代表存在量词，带有EXISTS谓词的子查询不返回任何数据，只产生逻辑真值或逻辑假值。因此由EXISTS引出的子查询，其目标列表表达式通常都用*。
使用存在量词EXISTS后，若内存查询结果非空，则外层的WHERE字句返回真值。

例如：1.查询了选修了全部课程的学生姓名

SELECT Sname FORM Student WHERE NOT EXISTS (SELECT * FROM Course WHERE NOT EXISTS (SELECT * FROM SC WHERE Sno=Student.Sno AND Cno=Course.Cno));

2.查询至少选修了学生201215122选修的全部课程的学生号码

SELECT DISTINCT Sno FROM SC SCX WHERE NOT EXISTS (SELECT * FROM SC SCY WHERE SCY.Sno=‘201215122’ AND NOT EXISTS (SELECT * FROM SC SCZ WHERE SCZ.Sno =SCX.Sno AND SCZ.Cno=SCY.Cno));

可以康康这个视频吖~
练习题：
1.假设一教务系统数据库包含三个表，结构图如下：
Student

Sno(学号)	Sname(姓名)	Sage(年龄)	Ssex(性别)	Dept(院系)
S1	张三	19	男	计算机
...

Course

Cno(课程号)	Cname(课程名)	Teacher(任课教师)
C1	数据库原理	刘飞
...

SC

Sno(学号)	Cname(课程名)	Grade(成绩)
S1	C1	86
...

用SQL语句进行如下操作：
（1）创建 Student 和 SC 表（定义相应的主外键约束）
（2）向 SC 表中插入一条选课记录（‘S2’，‘C2’）；
（3）查询每个院系的平均学生年龄；
（4）查询姓‘刘’的所有学生信息，按年龄降序排列；
（5）查询‘计算机系’年龄大于 22 岁的学生姓名；
（6）查询‘数据库原理’课程的最高考试成绩；
（7）把每个院系和对应的学生人数插入到已存在表 DepCount（Dept， Count）中； （8）统计所有选修了课程的学生人数；
（9）查询选修人数超过 60 人的所有课程号及对应的课程名；
（10）查询没有选修课程号为‘C1’的学生学号；
（11）查询至少选修了学号为 S1 的同学所修全部课程的学生学号；
（12）将‘计算机系’所有学生的成绩加 5 分；
（13）定义一个视图 Stu_Avg（sno，sname，avg），它由学生的平均成绩组成(avg 表示每个学 生的平均成绩)；
（14）把对 Student、Course 和 SC 三个表查询的权力授予给所有用户；
（15）删除学生张三的全部信息。

(1)CREATE TABLE Student(Sno CHAR(2) PRIMARY KEY, Sname CHAR(20) UNIQUE, Sage SMALLINT, Ssex CHAR(2), Sdept CHAR(20));

CREATE TABLE SC(Sno CHAR(2) NOT NULL,Cno CHAR(2) NOT NULL, Grade SAMALLINT, PRIMARY KEY(Sno, Cno),FOREIGN KEY(Sno)REFERENCES Student(Sno),FOREIGN KEY(Cno)REFERENCES Course(Cno));

(2)INSERT INTO SC(Sno, Cno) VALUES ('S2, ‘C2’);

(3)SELECT Dept,AVG(Sage) FROM Student GROUP BY Dept;

(4)SELECT * FROM Student WHERE Sname LIKE ‘刘%’ ORDER BY SAGE DESC;

(5)SELECT Sname FROM Student WHERE Dept=‘计算机系’ AND Sage>22;

(6)SELECT MAX(Grade) FROM SC WHERE Cno IN (SELECT Cno FROM Course WHERE Cname=‘数据库原理’);

(7)INSERT INTO DepCounT(Dept, Count) SELECT Dept, COUNT( *) FROM Student GROUP BY Dept;

(8)SELECT DISTINCT Sno FROM SC;

(9)SELECT SC,Cno,Cname FROM Course,SC WHERE SC.Cno=Course.Cno GROUP BY SC.Cno,Cname HAVING COUNT(*)>60;

(10)SELECT Sno FROM Student WHERE Sno NOT IN (SELECT Sno FROM SC WHERE Cno=‘C1’);

(11)SELECT Sno FROM Student WHERE NOT EXISTS (SELECT * FROM SC X WHERE Sno=‘S1’ AND NOT EXISTS (SELECT * FROM SC y WHERE Y.Sno=Student.Sno AND Y.Cno=X.Cno));

(12)UPDATE SC SET Grade=Grade+5 WHERE Sno IN IN (SELECT Sno FROM Student WHERE Dept=‘计算机’);

(13)CREATE VIEW Stu_Avg(Sno,Sname,Avg) AS SELECT SC.Sno, Sname, AVG(Grade) FROM SC,Student WHERE SC,Sno = Student.sno group by sc.sno,sname

(14)GRANT SELECT ON TABLE Student, Course, SC TO PUBLIC;
(15)DELETE FROM SC WHERE Sno IN (SELECT Sno FROM Student WHERE Sname=‘张三’);

DELETE FORM Students WHERE Sname='张三‘；

注：9，13；对EXISTS还有待了解。。。

函数依赖、范式、并发控制

函数依赖若R(U)是属性集U上的关系模式，X,Y是U的子集。若对于R(U)的任意一个可能的关系r,r中不可能存在两个元组在X上的属性值相等，而在Y上的属性值不等，则称X函数确定于Y或Y函数依赖于X，记作X→Y。
有：非平凡的函数依赖，平凡的函数依赖，完全函数依赖，部分函数依赖，传递函数依赖等。
码是关系模式中的一个重要概念。
设K为R<U,F>中的属性或属性组合，若K—F→U,则K为R的候选码。
一般地，如果U函数依赖于K，即K→U,则K为超码。
若候选码多于一个，则选定其中地一个为主码。
包含在任何一个候选码中的属性为主属性；不包含在任何候选码中的属性称为非主属性或非码属性。最简单的情况，单个属性是码；最极端的情况，整个属性组是码，称为全码。
关系模式R中的属性或属性组X并非R的码，但X是另一个关系模式的码，则称X是R的外部码，也称外码。

关系数据库中的关系是要满足一定要求的，满足不同程度要求的为不同范式。
一个低一级范式的关系模式通过模式分解可以转换为若干个高一级范式的关系模型的集合，这种过程就叫规范化。
若R∈1NF，且每一个非主属性完全函数依赖于任何一个候选码，则R∈2NF。
设关系模式R<U,F>∈1NF，若R中不存在这样的码X，属性组Y及非主属性Z（Z∉Y）使得X—/→Y，则称<R,U>∈3NF。可以证明，若R∈3NF,则每一个非主属性既不传递依赖于码，也不部分依赖于码。也就是说如果R属于3NF,则必有R属于2NF。
此外，还有BCNF,4NF,5NF等。

求解候选码
对于给定的关系R(A1,A2,A3,…An)和函数依赖集，可以将其属性分为4类：
L类：仅出现在F的函数依赖左部的属性
R类：仅出现在F的函数依赖右边的属性
N类：在F的函数依赖左右两边均未出现的属性
LR类：在F的函数依赖左右两边均出现的属性
对于给定的关系模式R及其函数依赖集F，若X（X∈R）是L类属性，则X必为R的任一候选码的成员
对于给定的关系模式R及其函数依赖集F，若X（X∈R）是L类属性，且X⁺包含了R的全部属性，则X必为R的唯一候选码
对于给定的关系模式R及其函数依赖集F，若X（X∈R）是R类属性，则X不在任何候选码中。
对于给定的关系模式R及其函数依赖集F，若X（X∈R）是N类属性，则X必包含在R的任一候选码中。

1. 设某商业集团数据库中有一关系模式 R 如下： R （商店编号，商品编号，数量，部门编号，负责人），如果规定：
   （1） 每个商店的每 种商品只在一个部门销售；
   （2） 每个商店的每个部门只有一个负责人；
   （3） 每个商店 的每种商品只有一个库存数量。
   请回答下列问题：
   （1）根据上述规定，写出关系模式 R 的基本函数依赖；
   （2）找出关系模式 R 的候选码；
   （3）如果 R 不属于 3NF，请将 R 分解成 3NF 模式集。

（1）（商店编号，商品编号）->部门编号     （商店编号，部门编号）->负责人   （商店编号，商品编号）->库存数量
（2）（商店编号，商品编号）
（3）由于R中存在非主属性“负责人”对候选码（商店编号，商品编号）的传递函数依赖，所以R属于2NF，而不属于3NF.。
将R分解成：R1（商店编号，商品编号，数量，部门编号）
R2（商店编号，商品编号，负责人）

2. 设有一关系模式 R（A,B,C,D,E,F,G,H），在该关系模式上有函数依赖集： {AB→C，A→DE，B→F,F→GH }，请回答：
   （1）试求(AB)+；
   （2）求 R 的侯选码。
   (1)（AB)⁺={A,B,C,D,E,F,G,H}
   (2)唯一候选码：AB

3. 设有以下两个事务 T1和 T2，设 A 的初值为 1： T1：读 A；A=A+1；写回 A T2：读 A；A=A*2；写回 A
   （1）写出这二个事务串行执行的所有可能结果；
   （2）请给出一个可串行化的并发调度。
   答案暂无。