【数据库系统概论复习】关系数据库与关系代数笔记

基本概念

数据库基本概念

DB 数据库， 为了存用户的各种数据，我们要建很多关系（二维表），所以把相关的关系（二维表）放在一起，形成一个库
DBMS 数据库管理系统 ，数据库要放硬盘，好麻烦，牛的程序员给我们做了一个软件， 让我们可以方便的建库， 建表，做权限控制等，这就是DBMS
DBS 数据库系统

关系数据结构

元组（Tuple）

关系中的每一行叫作一个n元组（n-tuple）或简称元组(Tuple)。

属性（Attribute）

关系中的每一列为一个属性。

分量（Component）

一个元组（d1，d2，…，dn）中的每一个项di叫作一个分量，即属性值。

域（Domain）

一组具有相同数据类型的值的集合。

基数（Cardinal number）

元组个数

关系模式

关系是元组的集合，其具有如下性质

• 列是同质的（Homogeneous）
• 不同的列可出自同一个域
• 列的顺序无所谓，列的次序可以任意交换
• 任意两个元组的key（码）值不能相同
• 行的顺序无所谓，行的次序可以任意交换
• 分量必须取原子值，每一个分量都必须是不可分的数据项

关系是具有相同结构的元组的集合；
关系模式对关系的结构等的抽象描述；
关系数据库是关系的集合；

外码

设F是基本关系R的一个或一组属性，但不是关系R的码。K是基本关系S的主码。如果F的取值源自基本关系S中的主码K对应的域，则称F是基本关系R的外码。

外码的使用

• 目标关系S的主码K 和参照关系R的外码F必须定义在同一个（或一组）域上；
• 外码并不一定要与相应的主码同名；
• 关系R和S可以是同一个关系。

完整性约束

实体完整性规则
参照完整性规则
用户定义的完整性规则

实体完整性规则（Entity Integrity）

若属性A是基本关系R的主属性，则A不能取空值（NULL）

参照完整性规则

若属性（或属性组）F是基本关系R的外码，则F的值：
或者取空值（F的每个属性的取值均为空值）；
或者等于S中某个元组的主码值。

关系代数

1. 集合操作：并

2. 集合操作：差

3. 集合操作：交

4. 选择

5. 投影

6. 重命名

命名操作简化

7. 链接运算符

8. 笛卡尔积

9. θ连接（ θ Join）

10. 内连接

注意：等值连接表示先做笛卡尔积(×)之后，对相应列进行选择或等值关联后的结果(仅筛选行、不筛选列)
注2：自然连接表示两个关系中若有相同名称的属性，则自动作为关联条件，且仅列出一列

11. 外连接

例题

12. 除运算

例题

13. 去重操作符

14. 排序操作符

默认是升序，小的在上面

• ASC ：升序（默认值）
• DESC：降序

15. 分组操作符

L中有属性就分组，如果只有聚合函数，就是简单的聚合函数，不会分组，如题11

16. 聚集操作符

关系代数练习

答案

课堂练习

1. select sno from SC where cno = '2';
2. select sno,sname from Student where sdept = 'IS';
3. select sno,sname from Student where ssex = '女';
4. select cname,grade from SC join Course as c on SC.cno = C.cno where SC.sno = 
(select sno from Student where sname = '李勇');
5. select s.sno from Student as s where s.sno not in (select SC.sno from SC);
6. select C.cno,C.cname from Course as C where cno not in (select SC.cno from SC);
SELECT C.cno, C.cname
FROM Course AS C
LEFT JOIN SC ON C.cno = SC.cno
WHERE SC.cno IS NULL;
7. select sno,sname from Student where sdept = (select sdept from Student where sname = '张立') and sname <> '张立';
8. select s.sno,s.sname from Student as s where s.sno in 
(select SC.sno from SC join Course as c on SC.cno = c.cno where c.canme = '数学课');
9. select s.sno,s.sname from Student as s where s.sno not in
(select SC.sno from SC join Course as c on SC.cno = c.cno where c.canme = '操作系统')

数据库：

CREATE TABLE Student(  Sno  CHAR(9) PRIMARY KEY，Sname CHAR(8) NOT NULL，                     Ssex  CHAR(2)  CHECK ( Ssex IN (‘男’，‘女’) ) ，                Sage  SMALLINT CHECK ( sage >0) ，Sdept  CHAR(20),FOREIGN KEY (Sdept) REFERENCES depart(dptno) );
CREATE TABLE SC(  Sno    CHAR(9)  NOT NULL， Cno     CHAR(4)  NOT NULL，  Grade    SMALLINT，PRIMARY KEY (Sno， Cno)， FOREIGN KEY (Sno) REFERENCES Student(Sno)，  FOREIGN KEY (Cno) REFERENCES Course(Cno)    
);

例题1某职工社团管理系统有如下3个基本表：

• 职工( 职工号 ，姓名，年龄，性别)
• 社会团体( 编号 ，名称，负责人，活动地点)
• 参与( 职工号，编号 ，参与日期)

其中：
职工表的主码为职工号，职工姓名不能为空。
社会团体表的主码为编号，外码为负责人，被参照表为职工表，参照属性为职工号，社会团体名称不能为空。
参与表的主码为职工号和编号，职工号为外码，被参照表为职工表，参照属性为职工号，编号为外码，被参照表为社会团体表，参照属性为编号。
试用SQL语句实现以下操作。
(1)定义职工表、社会团体表和参与表，并说明其主码和参照关系。
(2)定义两个视图：
社团负责人(编号，名称，负责人职工号，负责人姓名，负责人性别)
参与人情况(职工号，姓名，社团编号，社团名称，参与日期)
(3)查询参与羽毛球队、秧歌队或冬泳队的职工号和姓名。
(4)查询参与冬泳队且年龄大于50岁的职工的职工号和姓名。
(5)查询还没有确定负责人的社会团体编号和名称。
(6)查询没有参与任何社会团体的职工情况。
(7)查询姓“李”且参与至少一个社会团体的男员工的平均年龄。
(8)查询与“肖波”至少参与一个相同社会团体的所有职工信息。
(9)查询年龄在45～55岁之间的女职工或年龄在50～60岁之间的男职工的所有信息。
(10)查询参与了全部社会团体的职工情况。
(11)查询参与了职工号为“0001”的职工所参与的全部社会团体的职工号。
(12)查询与“秧歌队”负责人年龄相同的职工的姓名。
(13)查询比“秧歌队”中所有职工年龄都大的职工的姓名。
(14)查询并统计每个社会团体的名称和参与人数。
(15)查询并统计参与人数最少的社会团体的名称和参与人数。
(16)查询并统计参与人数超过20人的社会团体的名称和负责人。
答案：

(1)
CREATE TABLE 职工(职工号 CHAR(8) PRIMARY KEY,姓名 CHAR (8) NOT NULL,年龄 SMALLINT,性别 CHAR (2),CONSTRAINT C1 CHECK (性别 IN ("男","女")))
CREATE TABLE 社会团体 (编号 CHAR(8) PRIMARY KEY,名称 CHAR(8) NOT NULL,负责人 CHAR(8),活动地点 VARCHAR(50),CONSTRAINT C2 FOREIGN KEY(负责人) REFERENCES 职工(职工号))
CREATE TABLE 参与 (职工号 CHAR(8),编号 CHAR(8),参与日期 DATETLME,CONSTRAINT C3 PRIMARY KEY(职工号,编号),CONSTRAINT C4 FOREIGN KEY (职工号) REFERENCES 职工(职工号))
(2)
CREATE VIEW 社团负责人(编号,名称,负责人职工号,负责人姓名,负责人性别)
AS SELECT 编号,名称,负责人,姓名,性别
FROM 社会团体,职工
WHERE 社会团体.负责人=职工.职工号
CREATE VIEW 参与人情况(职工号,姓名,社团编号,社团名称,参与日期)
AS SELECT 职工.职工号,姓名,社会团体.编号,名称,参与日期
FROM 职工,社会团体,参与
WHERE 职工.职工号=参与.职工号 AND 参与.编号=社会团体.编号
(3)
SELECT DISTINCT 职工.职工号,姓名
FROM 职工,社会团体,参与
WHERE 职工.职工号=参与.职工号 AND 参与.编号=社会团体.编号
AND 社会团体.名称 IN ("羽毛球队","秧歌队","冬泳队")
(4)
SELECT 职工号,姓名
FROM 职工
WHERE 职工号 IN
(SELECT 职工号FROM 参与WHERE 编号 IN(SELECT 编号FROM 社会团体WHERE 名称="冬泳队"))
AND 年龄＞50
(5)
SELECT 编号,名称
FROM 社会团体
WHERE 负责人 IS NULL
(6)
SELECT *
FROM 职工
WHERE NOT EXISTS (SELECT *FROM 参与WHERE 参与.职工号=职工.职工号)
(7)
SELECT AVG(年龄)
FROM 职工
WHERE 姓名 LIKE "李%" AND性别="男" AND EXISTS (SELECT *FROM 参与WHERE 参与.职工号=职工.职工号)
(8)
SELECT *
FROM 职工 AS E1
WHERE E1.姓名＜＞"肖波" AND EXISTS
(SELECT J1.编号FROM 参与 AS J1WHERE J1.职工号=E1.职工号 AND J1.编号=ANY(SELECT J2.编号FROM 职工 AS E2, 参与 AS J2WHERE E2.职工号=J2. 职工号 AND E2.姓名="肖波"))
(9)
SELECT *
FROM 职工
WHERE ((年龄 BETWEEN 45 AND 55) AND 性别="女") OR
((年龄 BETWEEN 50 AND 60) AND 性别="男")
(10)
SELECT *
FROM 职工
WHERE NOT EXISTS (SELECT *FROM 参与WHERE NOT EXISTS (SELECT *FROM 社会团体WHERE 参与.职工号=职工.职工号 AND 参与.编号=社会团体.编号))
(11)
SELECT 职工号
FROM 职工
WHERE NOT EXISTS (SELECT *FROM 参与 AS 参与 1WHERE 参与 1.职工号="0001" AND NOT EXISTS (SELECT *FROM 参与 AS 参与 2WHERE 参与 2.编号=参与1.编号 AND 参与2.职工号=职工.职工号))
(12)
SELECT 姓名
FROM 职工
WHERE 年龄＞(SELECT 年龄FROM 职工,社会团体WHERE 社会团体.负责人=职工.职工号 AND 社会团体.名称="秧歌队")
(13)
SELECT 姓名
FROM 职工
WHERE 年龄＞ALL(SELECT 年龄FROM 职工,社会团体,参与WHERE 社会团体.编号=参与.编号 AND 职工.职工号=参与.职工号AND 社会团体.名称="秧歌队")
(14)
SELECT 社会团体.名称,COUNT(参与.职工号)
FROM 社会团体,参与
WHERE 社会团体.编号=参与.编号
GROUP BY 参与.编号,社会团体.名称
(15)
SELECT 社会团体.名称,COUNT(参与.职工号)
FROM 社会团体,参与
WHERE 社会团体.编号=参与.编号
GROUP BY 参与.编号,社会团体.名称
HAVING COUNT(参与.职工号)＜=ALL (SELECT COUNT(参与.职工号)FROM 参与GROUP BY 参与.编号)
(16)
SELECT 社会团体.名称,职工.姓名
FROM 职工,社会团体,参与
WHERE 社会团体.编号=参与.编号
AND 社会团体.负责人=职工.职工号
GROUP BY 参与.编号,社会团体.名称,职工.姓名
HAVING COUNT (参与.编号)＞20

例题2某教学管理数据库中有如下3个基本表。
学生：S( S# ,SNAME,AGE,SEX)，属性分别表示学号、姓名、年龄和性别。
学习：SC( S#,C# ,GRADE)，属性分别表示学号、课程号和成绩。
课程：C( C# ,CNAME,TEACHER)，属性分别表示课程号、课程名和教师姓名。
试用SQL语句完成下列操作：
(1)查询年龄大于20岁的男学生的学号和姓名。
(2)查询选修课程名为“操作系统”课程的学生的平均年龄。
(3)查询教师“王明”所授课程的课程号和课程名。
(4)查询选修课程包含教师“王明”所授课程的学生学号。
(5)查询教师“王明”所授课程的每门课程的学生平均成绩。
(6)统计每门课程的学生选修人数(超过10人的课程才统计)，要求输出课程号和选修人数，查询结果按选修人数降序排列，若人数相同，则按课程号升序排列。
(7)查询姓张的所有学生的姓名和年龄。
(8)查询成绩为空值的学生学号和课程号。
(9)查询年龄大于女学生平均年龄的男学生姓名和年龄。
(10)查询年龄大于所有女学生年龄的男学生姓名和年龄。
(11)查询选修课程门数超过学号为S0001的学生选修门数的所有学生信息。
(12)查询每个学生的学号、选修课程门数(COUNT_C)和总成绩(TOTAL_G)，将查询结果保存在一个名为RESULT的新表中。
(13)将SC中尚无成绩的选课记录删除。
(14)将学生“张三”的学习选课信息全部删去。
(15)将选修“操作系统”课程且不及格的成绩全改为空值。
(16)将低于总平均成绩的女学生成绩提高10%。

(1)
SELECT S#, SNAME
FROM S
WHERE (AGE＞20) AND (SEX="男")
(2)
SELECT AVG(AGE)
FROM S
WHERE S# IN
(SELECT S#
FROM SC
WHERE C# IN
(SELECT C#
FROM C
WHERE CNAME="操作系统"
)
)
(3)
SELECT C#, CNAME
FROM C
WHERE TEACHER="王明"
(4)
SELECT DISTINCT S#
FROM SC
WHERE C# IN
(SELECT C#
FROM C
WHERE TEACHER="王明"
)
(5)
SELECT CNAME,AVG(GRADE)
FROM SC JOIN C ON SC. C#=C. C#
WHERE TEACHER="王明"
GROUP BY C#
(6)
SELECT DISTINCT C#, COUNT(S#)
FROM SC
GROUP BY C#
HAVING COUNT(S#)＞10
ORDER BY 2 DESC, C# ASC
(7)
SELECT SNAME,AGE
FROM S
WHERE SNAME LIKE "张%"
(8)
SELECT S#, C#
FROM SC
WHERE GRADE IS NULL
(9)
SELECT SNAME, AGE
FROM S AS X
WHERE X.SEX="男" ND X.AGE＞
(SELECT AVG(AGE)
FROM S AS Y
WHERE Y.SEX="女")
(10)
SELECT SNAME, AGE
FROM S AS X
WHERE X.SEX="男" AND X.AGE＞ALL
(SELECT AGE
FROM S AS Y
WHERE Y.SEX="女")
(11)
SELECT *
FROM S
WHERE S# IN
(SELECT S#
FROM SC
GROUP BY S# HAVING COUNT(S#)＞
(SELECT COUNT(*)
FROM SC
WHERE S#="S0001"
)
)
(12)
SELECT S.S#, COUNT(S.S#) AS COUNT C, SUM(GRADE) AS TOTAL G
INTO RESULT
FROM S JOIN SC ON S.S#=SC. S#
GROUP BY S.S#
(13)
DELETE FROM SC
WHERE GRADE IS NULL
(14)
DELETE FROM SC
WHERE S# IN
(SELECT S#
FROM S
WHERE SNAME="张三")
(15)
UPDATE SC
SET GRADE=NULL
WHERE GRADE＜60 AND C# IN
(SELECT C#
FROM C
WHERE CNAME="操作系统")
(16)
UPDATE SC
SET GRADE=GRADE*1.1
WHERE GRADE＜(SELECT AVG(GRADE) FROM SC. AND
S# IN(SELECT S# FROM S WHERE SEX="女")

语法树

查询优化：先做选择，运用投影去除多余属性等等。语法树(尽量提前做选择操作；在每个操作后，应做个投影操作，去掉不用的属性值)。

:::info

1. 构造查询树：

查询树是一种表示关系代数表达式的树形结构。
在一个查询树中，叶子结点表示关系，内结点表示关系代数操作。
查询树以自底向上的方式执行：当一个内结点的操作分量可用时，这个内结点所表示的操作启动执行，执行结束后用结果关系代替这个内结点。

2. 优化查询树

• 利用等价转换规则反复地对查询表达式进行尝试性转换，将原始的语法树转換成“优化”的形式
• 对每一个选择，利用等价变换规则尽可能把它移到树的叶端。目的是使选择操作尽早执行
• 对每一个投影利用等价变换规则尽可能把它移向树的叶端。目的是使投影操作尽早执行
• 对每个叶节点加必要的投影操作，以消除对查询无用的属性。
• 如果笛卡尔乘积后还须按连接条件进行选择操作,可将两者组合成连接操作选择下沉，投影随后
  :::
  例题1