数据库系统概论

关系数据模型

关系模型基础

  • 关系:数据以一张二维表格描述

  • 属性:关系的列,是关系特征的描述。

  • 模式:关系名和其属性的集合 movies(title, year,length, genre)

  • 元组:除属性行外,关系的每一行对应一个元组。

  • 域:属性的数据类型及取值范围。

关系的性质:

  • 不能出现相同的行
  • 不能出现相同的列名
  • 列是不可分割的最小数据项

    属性相同,属性域相同才可以进行并交差的运算,投影运算$\pi_{A_1A_2…A_n}(R)$从关系R中取出这些列生成一个新的关系(表),$\sigma_{c}(R)$从关系R中取出满足条件C的元组生成一个新的关系

关系代数

笛卡儿积运算:关系R和关系S的乘运算,记作R×S,不需要相同部分,每一行去选另一个关系的一行

关系R 关系S R×S
属性个数 i j i+j
元组个数 m n m×n
元组 r s <r,s>

image-20250220151755298

自然连接:R$\bowtie$S,需要有相同的部分

image-20250220153521415

$\theta$连接:关系R和关系S满足条件c的自然连接连接表示为R$\bowtie_{c}$S

image-20250220153853953

    重命名:$\rho_{S}(A_1,A_2,\dots,A_n)(R)$对关系R进行重命名,拥有相同的元组,关系名字变成了S,各属性分别命名为$A_n$,如果属性名不变的话可写作$\rho_s(R)$

关系上的约束

    关系中的某一属性或属性组的值能唯一标识一个元组,则称该属性或属性组为关系的键(码)

sql里的主键(id),唯一标识

    一个关系中存在多个键,这些键统称为候选键,从候选键中选一个键作为关键的键,这个键被称为主键,候选键中的属性统称为主属性

完整性约束:

    若属性A是关系R的主键,则属性A不能是空值NULL

外键(外码)与参照关系:

    设F是关系R的一个或一组属性,G是关系S的键,F不是关系R的主键,如果FG,则称:

  • F是关系R的外键
  • 关系R为参照关系
  • 关系S为被参照关系

因此F的取值要么取空值即没有对应关系,要么等于某一个G的值

$$
\pi_{CNAME}([\rho_{SC1}(SC)\bowtie_{SC1.S井号=SC2.S井号and SC1.C井号 > SC2.C井号 }\rho_{SC2}(SC)]\bowtie_{SC1.S井号=s.s井号}S )
$$

$$
\pi_{name}(\sigma_{major=’softwarre engineering’}(Scholarship\bowtie_{Scholarship.studentid=Student.sid} Student))
$$

$$
\pi_{amount,year}(\sigma_{name=’Hu Xia’}(Scholarship\bowtie_{Scholarship.studentid=Student.sid} Student))
$$

$$
\pi_{name}([\rho_{S1}(Scholarship)\bowtie_{S1.studentid=S2.studentid ,and,s1.year > S2.year}\rho_{S2}(Scholarship)]\bowtie_{S1.studentid=Student.sid}Student )
$$

$$
\pi_{name}Student-\pi_{name}(Student\bowtie_{Scholarship.studentid=Student.sid}Scholarship)
$$

第三章 关系数据库设计理论

函数依赖

    如果关系R的两个元组在属性$A_1,A_2,\dots,A_n$上的值相同,那么它们必定在其它属性$B_1,B_2,\dots,B_n$上的值相同,记作$A_1,A_2,\dots,A_n->B_1,B_2,\dots,B_n$或$A=(A_1,A_2,\dots,A_n),B=(B_1,B_2,\dots,B_n)$,称作,A决定B,或者B依赖于A。$A->A$

A B A->B
相同 相同 成立
相同 不同 不成立
不同 相同 成立
不同 不同 成立

    如果$C->B,C->A$,那么$C->AB,C->ABC$成立

例题:

image-20250310160738240

image-20250310160938582

函数依赖的规则

image-20250310161624028

若两个FD集合S与T满足:

    关系的每个实例在满足T中依赖关系的同时也满足S中的函数依赖关系,称S蕴含于T,如果同时T也蕴含于S,那么S和T等价

例:T={A->B},S={A->$B_1$},则S蕴含于T

T={A->B},S={A->$B_1$,A->$B_2$},则T与S等价

FD集合推导规则

  • 若两个FD集合等价,用一个FD集合替换另一个FD集合
  • 从原有FD集合中推断出新的FD集合

    若$A_1A_2\dots A_n->B_1B_2\dots B_m$,等价于$A_1A_2\dots A_n->B_i$

    传递规则:设$A=[A_1A_2\dots A_n],B=[B_1B_2\dots B_m],C=[C_1C_2\dots C_k]$为关系R的属性集,若满足FD:A->B且B->C,则A->C成立

设$A=[A_1A_2\dots A_n],B=[B_1B_2\dots B_m],C=[C_1C_2\dots C_k]$:

  • 如果$B\subseteq A$,那么A->B,自反率,平凡依赖
  • 如果A->B,那么AC->BC,增广率,非平凡依赖
  • A->B且B->C,则A->C,传递依赖

一个FD集合S,与S等价的任何FD集合都称为S的基本集

若给定的基本集B满足:

  • B中所有FD的右边都是单一属性
  • 从B中任何一个FD中删除左边的一个属性或多个属性,B不再是基本集
  • 从B中删除任何一个FD,B不再是基本集

则称为最小基本集(最小基)

一个关系可能存在多个最小基本集

例题:

image-20250312195634130

image-20250312195644525

闭包及作用

    设$A=(A_1,A_2,\dots,A_n),B=(A_1,A_2,\dots,A_n,B_1,B_2,\dots,B_n)$都是关系R的属性集,S是R上的一个FD集,如果从S中可以推出A->B,称B是A的闭包,记作$A^+$

例题:

image-20250313152717641

image-20250313160813575

image-20250313161636854

image-20250313162023541

BC范式

关系模式R是BCNF的充要条件:R中每一个非平凡FD的左边是超键

如何分解BF范式

image-20250314133238259

例题:

image-20250314134324602

另一种分解

image-20250314134519579

BC范式分解结果不唯一

无损连接:指将一个关系模式分解为多个子关系模式后,通过自然连接等操作能够恢复成原来的关系模式,且恢复后的关系与原关系相比既不丢失信息,也不增加多余信息

表格法判断无损连接

image-20250314140256633

第三范式

关系R上的任一非平凡FD:A->B,满足A是超键或B由候选键中的属性组成

是BF范式一定是第三范式

例题
image-20250319200851637

表格法检验

image-20250319200935343

第一范式:关系的每个属性都是不能再分的

第二范式:每一个非主属性都完全函数依赖于R的候选键

范式对比

image-20250319225155562

第四章 高级数据库模型

ER图

  • 实体集:矩形
  • 属性:椭圆形及指向实体集的连线
  • 联系:棱形及指向实体集的连线

概念设计

  • 忠实性
  • 避免冗余
  • 简单性
  • 选择正确的联系
  • 选择正确的元素种类

逻辑结构设计

转换规则:

  • 一个实体集转化成一个关系模式
  • 实体集的属性转化成关系的属性
  • 实体集的键转化成关系的键

一对一的联系

①单独转化成一个关系模式,关系的键为两个实体集的键的组合,联系的属性转化为关系的属性。

创建一个中间表,通过中间表进行相互索引

将联系与任意一端实体集所对应的关系模式合并,需要在该关系模式的属性中加入另一端实体集的键和联系本身的属性。

外键,通过这个键去索引另一个表

多对一的联系

通常是从多的那端添加外键去索引一,若一去索引多则会冗长

image-20250327154628780

多对多的联系

创建一个中间表进行索引

isa联系

image-20250327154640399

弱实体联系转化为关系

image-20250327154721513

第五章 代数和逻辑查询语言

包上的关系操作

  • 集合:元素的一种聚集形式,不允许重复元素出现

  • 包:元素的一种聚集形式,允许重复元素出现

包的并运算

image-20250402181649407

包的交运算

image-20250402181702331

包的差运算

image-20250402181720380

包的投影运算

image-20250402181753912

包的选择运算

image-20250402181814754

包的笛卡尔积的运算

image-20250402181836235

包的连接运算

image-20250402181906102

关系代数的扩展操作符

消除重复

数据库语言

在SQL中定义关系模式

nvarchar

SQL中的简单查询

1
SELECT ... FROM ... WHERE ... GROUP BY ... ORDER BY ...

触发器

是一种特殊的数据库对象,它在指定的数据库事件(如 INSERTUPDATEDELETE)发生时自动执行预定义的操作。触发器常用于实现数据一致性约束、审计日志记录、级联更新/删除等业务逻辑。

特性 说明
触发事件 INSERTUPDATEDELETE(可单独或组合使用)。
触发时机 BEFORE(操作前执行)或 AFTER(操作后执行)。
触发粒度 行级触发器(每行操作触发一次)或 语句级触发器(整个语句触发一次)。
适用场景 数据校验、自动填充字段、维护业务规则、记录审计日志等。
1
2
3
4
5
6
7
CREATE TRIGGER trigger_name
{BEFORE | AFTER} {INSERT | UPDATE | DELETE}
ON table_name
FOR EACH ROW -- 行级触发器(默认)
BEGIN
-- 触发器逻辑(如 SQL 或存储过程代码)
END;

触发器的常见应用场景

示例 1:数据校验(BEFORE INSERT)

确保插入的订单金额大于 0:

1
2
3
4
5
6
7
8
9
CREATE TRIGGER validate_order_amount
BEFORE INSERT ON orders
FOR EACH ROW
BEGIN
IF NEW.amount <= 0 THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = '订单金额必须大于 0';
END IF;
END;
  • 效果:插入金额 ≤ 0 的订单会抛出错误。

示例 2:自动维护审计日志(AFTER UPDATE)

记录用户信息的修改历史:

1
2
3
4
5
6
7
CREATE TRIGGER log_user_changes
AFTER UPDATE ON users
FOR EACH ROW
BEGIN
INSERT INTO audit_log (user_id, action, old_email, new_email, change_time)
VALUES (OLD.id, 'UPDATE', OLD.email, NEW.email, NOW());
END;
  • 效果:每次更新用户邮箱时,自动记录到 audit_log 表。

示例 3:级联更新库存(AFTER INSERT)

插入订单后自动减少库存:

1
2
3
4
5
6
7
8
CREATE TRIGGER update_inventory
AFTER INSERT ON orders
FOR EACH ROW
BEGIN
UPDATE products
SET stock = stock - NEW.quantity
WHERE product_id = NEW.product_id;
END;
  • 效果:新订单插入后,对应产品的库存自动减少。

例子:

假设有一个 orders 订单表和一个 audit_log 审计日志表,需求是:每次订单状态更新时,自动记录修改日志

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
-- 订单表
CREATE TABLE orders (
order_id INT PRIMARY KEY,
customer_id VARCHAR(50),
status VARCHAR(20), -- 状态字段(如 'pending', 'shipped')
amount DECIMAL(10,2)
);

-- 审计日志表
CREATE TABLE audit_log (
log_id INT AUTO_INCREMENT PRIMARY KEY,
order_id INT,
old_status VARCHAR(20),
new_status VARCHAR(20),
change_time DATETIME
);

触发器代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
DELIMITER $$  -- 修改语句分隔符(仅MySQL需要)

CREATE TRIGGER after_order_status_update
AFTER UPDATE ON orders
FOR EACH ROW
BEGIN
-- 仅当 status 字段实际发生变化时记录日志
IF OLD.status <> NEW.status THEN
INSERT INTO audit_log (order_id, old_status, new_status, change_time)
VALUES (OLD.order_id, OLD.status, NEW.status, NOW());
END IF;
END$$

DELIMITER ; -- 恢复默认分隔符

DECLARE:声明变量

1
DECLARE 变量名 数据类型 [DEFAULT 默认值];
  • 作用:声明一个变量,并指定其数据类型(如 INTVARCHARDECLIMAL 等)。
  • 适用场景:存储过程、函数、触发器或批处理脚本中。
1
2
DECLARE user_count INT;          -- 声明一个整数变量
DECLARE user_name VARCHAR(50) DEFAULT '匿名用户'; -- 声明并设置默认值

注意:

  • 作用域:变量仅在声明它的代码块(如 BEGIN...END)内有效。
  • 默认值:若不指定 DEFAULT,变量初始值为 NULL

SET:变量赋值

1
2
3
SET 变量名 = 值;  -- 通用写法

SELECTINTO 变量名; -- 通过查询赋值(需确保查询返回单值)
  • 作用:为已声明的变量赋值。
  • 区别
    • SET 直接赋值。
    • SELECT INTO 从查询结果中赋值(要求结果必须为单行单列)。
1
2
3
4
5
6
-- 示例1:直接赋值
SET user_count = 100;
SET user_name = '张三';

-- 示例2:通过查询赋值
SELECT COUNT(*) INTO user_count FROM users WHERE age > 18;
1
SET user_count = 10, user_name = '李四';  -- 同时为多个变量赋值

DECLARE 和 SET 的完整流程

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
-- 声明变量
DECLARE adult_count INT;
DECLARE result_text VARCHAR(100);

-- 赋值:查询成年用户数量
SELECT COUNT(*) INTO adult_count FROM users WHERE age >= 18;

-- 根据数量生成结果描述
IF adult_count > 0 THEN
SET result_text = CONCAT('成年用户数量:', adult_count);
ELSE
SET result_text = '没有符合条件的用户';
END IF;

-- 输出结果
SELECT result_text;

执行结果

result_text
成年用户数量:5

t-sql

T-SQL (Transact-SQL) 是 Microsoft SQL Server 使用的 SQL 扩展语言,它在标准 SQL 基础上增加了许多功能

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
-- 变量声明
DECLARE @var1 INT, @var2 VARCHAR(50)

-- 变量赋值
SET @var1 = 10
SELECT @var2 = Name FROM Employees WHERE ID = 1

-- 控制流
IF @var1 > 5
BEGIN
PRINT '值大于5'
END
ELSE
BEGIN
PRINT '值小于等于5'
END

-- 循环
DECLARE @counter INT = 0
WHILE @counter < 5
BEGIN
PRINT '计数器: ' + CAST(@counter AS VARCHAR)
SET @counter = @counter + 1
END

存储过程

存储过程(Stored Procedure)是预编译的 T-SQL 语句集合,存储在数据库中

1
2
3
4
5
6
7
8
CREATE PROCEDURE [schema_name.]procedure_name
@parameter1 datatype [ = default_value ] [ OUTPUT],
@parameter2 datatype [ = default_value ] [ OUTPUT],
...
AS
BEGIN
-- T-SQL 语句
END

例子:

简单查询存储过程

1
2
3
4
5
6
7
8
9
CREATE PROCEDURE GetEmployeeByID
@EmployeeID INT
AS
BEGIN
SELECT * FROM Employees WHERE ID = @EmployeeID
END

-- 执行
EXEC GetEmployeeByID @EmployeeID = 1

带输出参数的存储过程

1
2
3
4
5
6
7
8
9
10
11
12
13
14
CREATE PROCEDURE GetEmployeeCountByDepartment
@DeptID INT,
@EmployeeCount INT OUTPUT
AS
BEGIN
SELECT @EmployeeCount = COUNT(*)
FROM Employees
WHERE DepartmentID = @DeptID
END

-- 执行
DECLARE @Count INT
EXEC GetEmployeeCountByDepartment @DeptID = 2, @EmployeeCount = @Count OUTPUT
SELECT @Count AS '员工数量'

带条件逻辑的存储过程

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
CREATE PROCEDURE UpdateEmployeeSalary
@EmployeeID INT,
@IncreaseAmount DECIMAL(10,2)
AS
BEGIN
DECLARE @CurrentSalary DECIMAL(10,2)

SELECT @CurrentSalary = Salary
FROM Employees
WHERE ID = @EmployeeID

IF @CurrentSalary + @IncreaseAmount > 100000
BEGIN
RAISERROR('薪资调整后不能超过100000', 16, 1)
RETURN
END

UPDATE Employees
SET Salary = Salary + @IncreaseAmount
WHERE ID = @EmployeeID

PRINT '薪资已更新'
END

单词表

intersection 交集


数据库系统概论
http://2819461143wp.github.io/数据库系统概论/
作者
cwdp.sky
发布于
2025年2月19日
许可协议