数据库期末复习笔记

第1章 绪论

1.1术语

1.数据（Data）

数据是数据库中存储的基本对象。定义为描述事务的符号记录。数据的含义称为语义，数据与其语义是不可分的。

2.数据库（DataBase，DB）

长期存储在计算机内，有组织的，可共享的大量数据的集合。

数据库基本特征：1.永久存储  2.有组织  3.可共享 4.冗余度小 5.易扩展

3.数据库管理系统（DataBase Management System,DBMS）（core）

位于用户与操作系统之间的一层数据管理软件

数据库管理系统和操作系统一样是计算机的基础软件。

主要功能：提供数据定义语言（DDL）、数据操纵语言（DML）等

4.数据库系统（DataBase System,DBS）

数据库系统由数据库（DB），数据库管理系统(DBMS)，应用程序和数据库管理员(DBA)组成的存储、管理、处理和维护数据的系统。

1.2重要概念

数据库管理技术的发展过程（三个阶段）

1.人工管理阶段：

在计算机出现之前，人们运用常规的手段从事记录、存储和对数据加工，也就是利用纸张来记录和利用计算工具（算盘、计算尺）来进行计算，并主要使用人的大脑来管理和利用这些数据。

特点:

(1)计算机系统不提供对用户数据的管理功能；

(2)数据不能共享；

(3)不保存数据。

2.文件系统阶段：

在这一阶段（20世纪50年代后期至60年代中期）计算机不仅用于科学计算，还利用在信息管理方面。随着数据量的增加，数据的存储、检索和维护问题成为紧迫的需要，数据结构和数据管理技术迅速发展起来。此时，外部存储器已有磁盘、磁鼓等直接存取的存储设备。软件领域出现了操作系统和高级软件。操作系统中的文件系统是专门管理外存的数据管理软件，文件是操作系统管理的重要资源之一。

特点:

数据以“文件”形式可长期保存在外部存储器的磁盘上。由于计算机的应用转向信息管理，因此对文件要进行大量的查询、修改和插入等操作，这些操作由文件系统提供。   

缺点:

数据冗余;不一致性;数据独立性差。

3.数据库系统阶段：

20世纪60年代后期以来 ，计算机性能得到进一步提高，更重要的是出现了大容量磁盘，存储容量大大增加且价格下降。在此基础上，才有可能克服文件 系统管理数据时的不足，而满足和解决实际应用中多个用户、多个 应用程序共享数据的要求，从而使数据能为尽可能多的 应用程序服务，这就出现了数据库这样的数据管理技术。

特点:

（1）数据结构化。

（2）数据共享性高、冗余少且易扩充。

（3）数据独立性高。

（4）数据由DBMS统一管理和控制。

1.3 数据库体系结构

1.3.1 外部架构（External Architecture）：

• 单用户结构、主从结构、分布式结构、客户/服务器结构。
• 优点：提高了系统性能和效率，减少了通过网络传输的数据量。

外部架构涉及了数据库系统与用户或应用程序之间的交互。以下是四种主要的外部架构类型：

1.3.1.1 单用户结构（Single-user Structure）

• 描述：这种结构指一个数据库系统只支持一个用户访问。通常用于简单的桌面数据库应用，如Microsoft Access
• 优点：实现简单，无需处理多用户并发问题
• 缺点：无法支持多用户同时访问，不适用于大型或企业级应用

1.3.1.2 主从结构（Master-slave Structure）

• 描述：主从结构包括一个主数据库和多个从数据库。主数据库接受所有的写操作，并将更新传播到从数据库。从数据库主要用于读操作
• 优点：通过分离读写操作，提高了系统的性能和可扩展性
• 缺点：需要额外的管理和配置以确保主从数据库之间的数据一致性

1.3.1.3 分布式结构（Distributed Structure）

• 描述：分布式结构将数据库分布在多个物理位置，这些位置通过网络连接。每个节点可以独立处理查询并参与全局事务
• 优点：提高了系统的可靠性和可用性，数据可以分布在不同地理位置以提高访问速度和容错能力
• 缺点：实现和管理复杂，需要处理数据同步、分布式事务和网络延迟等问题

1.3.1.4 客户/服务器结构（Client-server Structure）

• 描述：在客户/服务器结构中，客户端负责用户接口和应用逻辑，服务器负责数据存储和管理。客户端向服务器发送查询，服务器执行查询并返回结果
• 优点：分离了数据管理和应用逻辑，提升了系统的性能和扩展性。减少了网络数据传输量，因为只传输查询结果而不是全部数据
• 缺点：需要网络支持，可能存在网络延迟问题

1.3.2 内部架构（Internal Architecture）：

内部架构主要关注数据库的模式和实例两个核心概念：

模式与实例（Schema and Instance）

1.模式（Schema）

• • 描述数据结构的定义（Type），例如：学生的名字、性别、出生年份、籍贯、系别、入学时间。
  • 模式是数据库设计过程中的一部分，并且不经常变化。
  • 模式是数据库的描述或结构的描述。
  • 模式代表数据库的内涵（Intension）。

2.实例（Instance）

• • 实例是实际存储在数据库中的数据（Value），例如：李明，男，2003年，浙江，计算机科学，2023年入学。
  • 实例是数据库在某一时刻的数据，可能经常变化。
  • 实例是数据库的外延（Extension）。
  • 同一个数据库模式可以对应多个不同的数据库实例。

总结

模式和实例分别代表了数据库系统中的两个层次：模式是对数据库结构的静态描述，是固定的设计蓝图；而实例则是对数据库内容的动态描述，是具体的数据。模式定义了数据的类型和格式，而实例则是符合这些类型和格式的具体数据。理解这两个概念，有助于更好地设计和管理数据库系统。

1.3.3 三层体系结构（Three-level Architecture）

• 外部层（External Level）：由多个用户视图组成，每个视图包含用户相关的数据库部分，不同视图对同一数据可能有不同表示。
• 概念层（Conceptual Level）：包含数据库的逻辑表示，支持各个用户视图，定义了所有实体、属性及其关系、数据约束、安全性和完整性信息。
• 内部层（Internal Level）：包含数据库的物理表示，管理数据的存储方式和空间分配等。

详细解释:

1.外部层（External Level）

• • 外部层由多个用户视图（external schema外模式）组成。
  • 每个用户视图包含与该用户相关的数据库部分。这意味着每个用户只能看到他们需要的数据，而看不到其他不相关的数据。
  • 用户不知道他们视图之外的数据存在。这种隔离确保了数据的安全性和隐私性。
  • 不同的视图可以对相同的数据有不同的表示方式。也就是说，不同用户可以根据自己的需求看到不同形式的同一数据。

2.概念层（Conceptual Level）

• • 概念层是数据库的核心和关键（Core and key）。
  • 它定义了数据库的整体结构和约束条件，而不涉及具体的物理存储细节。
  • 概念层模式（conceptual schema）统一了外部层的多个视图，并与内部层的存储细节相连接。

3.内部层（Internal Level）

• • 内部层定义了数据在数据库内部的物理存储方式。
  • 它包括内部模式（internal schema），描述了数据的存储路径和访问方法。
  • 内部层是实际的数据存储层，负责具体的物理数据组织。

总结

数据库的三层体系结构通过将数据的外部视图、概念表示和内部存储分离，提供了高度的抽象和独立性。这种结构使得数据库系统能够更好地管理和控制数据，确保数据的一致性、安全性和高效的访问。外部层通过多个用户视图为不同的用户提供了个性化的数据访问方式，概念层作为中间层连接外部层和内部层，统一管理数据结构，而内部层则处理具体的数据存储和访问细节。理解这一结构有助于设计更高效和安全的数据库系统。

1.3.4 映像（Mapping）

在数据库体系结构中，映像是指不同层次之间的对应关系：

1.外模式/概念模式映像（External/Conceptual Mapping）

• 这种映像定义了用户视图（外模式）与概念模式之间的对应关系，使得用户能够以自己的视角访问和操作数据 。

2.概念模式/内模式映像（Conceptual/Logical Mapping）

• 这种映像定义了概念模式与内部模式之间的对应关系，确保逻辑数据结构与物理存储结构之间的兼容性。

1.3.5 数据独立性（Data Independence）

数据独立性是指当数据库的某一层次发生变化时，不需要对其他层次进行相应的修改。数据独立性分为两种：

逻辑数据独立性（Logical Data Independence）

定义：外模式不受概念模式变化的影响。这意味着，当我们在概念模式中添加或删除某些属性时，不需要修改用户的视图或应用程序。

重要性：逻辑数据独立性使得数据库系统更具灵活性和扩展性，因为我们可以在不影响用户的情况下修改数据库的逻辑结构。

实现：通过外部/概念映像来实现，即将用户视图映射到概念模式上。

物理数据独立性（Physical Data Independence）

定义：概念模式不受内部模式变化的影响。这意味着，当我们改变数据的物理存储方式时，不需要修改数据库的逻辑结构或用户视图。

重要性：物理数据独立性提高了系统的效率和性能，因为可以在不影响逻辑结构和用户视图的情况下优化数据存储和访问方法。

实现：通过概念/内部映像来实现，即将概念模式映射到内部模式上。

第二章 关系模型

2.1 数据模型概述

2.1.1 数据模型的定义

—— 数据模型是对现实世界数据的抽象。

• 用于描述数据、组织数据和操作数据
• 模拟现实世界
• 所有当前的数据库系统（DBS）都是基于某种类型的数据模型
• 数据模型是数据库系统的核心和基础

2.1.2 数据模型的要求

• 能够模拟现实世界的现实性
• 易于理解
• 能够在计算机上实现

2.1.3 数据模型的三要素

• 数据结构：描述数据库中数据对象的结构及其相互关系
• 数据操作：一组操作，用于处理数据库中的所有数据对象
• 完整性约束：一组规则，确保数据结构和数据操作的正确性，以保证数据的一致性和准确性

2.2 关系模型

2.2.1 关系模型的起源与功能

• 关系模型是从表格及表格处理方法中抽象出来的。
• 它是三种经典数据模型之一 （层次模型、网状模型和关系模型）。
• SQL 是基于关系模型提出和开发的。
• 它是许多数据库理论的基础。

2.2.2 关系模型的组成部分

关系就是一个表格。

关系模型用于处理表格，由三个部分组成：

• 数据结构：表（Table）。
• 数据操作：关系代数和关系演算。
• 数据约束：完整性约束。

2.2.3 关系模型的基本概念

• 关系（Relation）/ 表格（Table）：一个关系对应通常说的一张表。
• 属性（Attribute）/ 列（Column）/字段（Field）：表中的一列即为一个属性。列可以存储的数据类型。
• 模式（Schema）：模式是表格结构的定义，包括表格的名称、每列的名称和数据类型等。模式决定了表格的结构和约束条件。
• 元组（Tuple）/ 行（Row）：表中的一行即为一个元组。每行中的数据按照列的定义进行存储。
• 码（key）：也称码键。表中的某个属性组，它可以唯一确定一个元组。
• 域（domain）：列/属性值的范围。

• 域是一组值的集合，所有值具有相同的数据类型。
• 例如：一组整数、一组字符字符串、一组所有学生。
• 集合中元素的数量称为基数（Cardinality）。

例如，D1 中有两个元素，所以它的基数是2

• 域（Domain）定义了表中某一列可以接受的数据类型和范围。例如，学生年龄的域可以定义为0到150之间的整数。域确保了数据的一致性和正确性，避免错误的数据类型输入。基数表示域中可能的值的数量，有助于理解数据的规模和范围。

2.2.4 笛卡尔积与关系

2.2.4.1 笛卡尔积

• 笛卡尔积（Cartesian Product）是域上面的一种集合运算，是将多个域组合起来生成所有可能的值组合。
• 给定域集合 D1, D2, ......, Dn，其笛卡尔积表示为：

• 笛卡尔积的元素 

  

   称为n-元组（n-tuple）。
• 元组

  

   中的每个

  

  称为一个组件（component）。
• 如果域 

  

   的基数是

  

  ，则笛卡尔积的基数，即元组的数量，是

  

2.2.4.2 关系的定义

• 正式定义：给定域的集合D1, D2, ..., Dn，一个关系 r 是D1

  

   D2

  

   ...

  

   Dn的一个子集，因此一个关系是

  

  -元组 (d1, d2, ..., dn) 的集合，其中每个di

  

   Dn (d1 = 1, 2, ..., n)。
• 并不是笛卡尔积中的所有元组都是有意义的，所以只有那些有意义的元组被用来形成关系。
• 关系的不同列可能来自相同的域，所以有必要给每列赋予不同的名称，我们称之为列名/属性名。

2.2.4.3 关系的表示方法

• 关系表示为R(A1:D1, A2:D2, ..., An:Dn) ，或简要表示为R(A1, A2, ..., An) ，我们也称这种关系为关系模式（Relation Schema）。
• R是关系的名称，Ai是属性，Di是属性的域，n是关系的度（degree），关系中的元组数称为关系的基数（Cardinality of Relation）。

• 度是关系中属性的数量。例如，家庭关系有三个属性：丈夫、妻子和孩子，所以它的度是3。
• 基数是关系中的元组数量。例如，如果一个家庭关系有3个家庭记录，那么它的基数是3。

• 在关系模式中，每个属性 (Ai) 必须是独特的，但是不同的属性可以来自相同的域。例如，多个属性可以都是名字，但它们代表不同的角色，如丈夫和妻子。

2.2.4.4 关系的例子

在许多DBMS中，关系模式 R(A1:D1, A2:D2, ..., An:Dn) 中从属性到域的映射通常描述为属性的类型和长度。

属性类型和长度：

在实际数据库中，属性的定义不仅包括名称，还包括数据类型和长度。例如，学生表的学号（Sno）可以定义为字符型（char）且长度为10。

例子：

• 学生表：Student(Sno char(10), Sname char(20), Gender char(2), Age integer, Dno char(2), Class char(8))
• 课程表：Course(Cno char(3), Cname char(30), Hours integer, Credit float(1), Tno char(3))
• 成绩表：SC(Sno char(10), Cno char(3), Score float(1))

这些定义展示了如何在关系模式中描述属性及其数据类型和长度。

2.2.4.5 关系（关系实例）和关系模式

• 一个关系模式可以有许多不同的关系（关系实例）。
• 关系模式是关系的结构，关系（关系实例）是在特定时间点的数据关系模式的快照。
• 关系模式通常是稳定的，但关系会随着时间的推移而变化。

例子：

• 学生表的关系模式：Student(Sno char(10), Sname char(20), Gender char(2), Age integer, Dno char(2), Class char(8))
• 在特定时间点，这张表格可能包含不同的学生记录，如表中展示的具体数据。

2.3 关系的特征

1.同质性（Homogeneity）：

列/属性的同质性：每列的所有值来自同一域，具有相同的数据类型。

2.属性（列）的顺序：

在关系中，列的顺序无关紧要。

3.元组（行）的顺序：

在关系中，元组的顺序无关紧要。

4.元组的唯一性：

每个元组/行必须是唯一的，即关系中不允许有两个完全相同的元组。

5.属性的原子性：

属性值是不可再分的，这一特征也称为第一范式（1NF）。

2.4 键的类型

2.4.1 候选键（Candidate Key）

• 候选键的定义：候选键是关系中可以唯一标识一个元组的属性或属性组合。例如，学生表中的学号（Sno）可以唯一标识每个学生，因此是一个候选键。
• 多个候选键：一个关系可能有多个候选键。例如，学生表除了学号外，可能还有身份证号作为候选键，因为它们都能唯一标识学生。

2.4.2 主键与超键（Primary key & Super key）

• 主键的定义：主键是从候选键中选出的，用于唯一标识关系中的每个元组。例如，学生表中的 Sno 可以被选为主键。
• 超键的定义：超键是一个包含候选键的属性集合。例如，学生表中的 Sno 是候选键，那么 Sno + Sname 就是一个超键。
• 例子解释：

• 在学生表中，Sno 是主键，而 Sno + Sname 是超键。
• 在员工表中，EmpID 是主键，而 EmpID + Mobile 是超键。

2.4.3 单键/全键与主属性/非主属性（Single key / All key & Primary attribute / Non-primary attribute）

• 单键（Single Key）：只有一个属性的候选键。
• 全键（ALL Key）：关系中所有属性的集合是候选键。
• 主属性（Primary attribute）：出现在任何候选键中的属性。
• 非主属性（Non-primary attribute）：除所有主属性外的其他属性。

1. 单键和全键：

• 单键（Single key）是只有一个属性的候选键，例如课程表中的Cno。
• 全键（All key）是指关系中所有属性的集合，例如家庭表中的 Husband, Wife和 Children，所有这些属性一起构成一个候选键。

2. 主属性和非主属性：

• 主属性（Primary attribute）是指在任何候选键中出现的属性。例如，学生表中的 Sno 是一个主属性，因为它出现在候选键中。
• 非主属性（Non-primary attribute）是指除了主属性外的其他属性。例如，学生表中的 Sname, Gender, Age, Dno, Class, Address 都是非主属性。

2.4.4 外键（Foreign Key）

• 外键（Foreign key）：一个关系R1中可能包含一组属性，这些属性不是R1的候选键，而是另一个关系R2的候选键，这组属性称为R1的外键。外键显示了不同关系之间的关系。
• 引用关系（Referencing Relation）：R1
• 被引用关系（Referenced Relation）：R2

1. 外键的定义：

• 外键用于建立两个关系之间的连接。例如，学生表中的 Dno 不是学生表的候选键，但它是系表（Dept）的候选键。因此，Dno 是学生表的外键，表示学生属于某个系。

2. 引用关系和被引用关系：

• 引用关系是包含外键的关系，例如学生表。
• 被引用关系是外键所引用的关系，例如系表。

2.4.5 键类型总结

• 候选键：可以唯一标识关系中元组的属性或属性组合。
• 主键：从候选键中选出的用于唯一标识关系中元组的主要键。
• 超键：包含候选键的属性集合。
• 单键：只有一个属性的候选键。
• 全键：关系中的所有属性集合。
• 主属性：出现在任何候选键中的属性。
• 非主属性：除了主属性外的其他属性。
• 外键：用于建立不同关系之间连接的属性。

2.5 完整性约束

2.5.1 完整性约束的组成

关系模型用于处理表格，由三个部分组成：

1.数据结构（表格）

2.数据操作（关系代数和关系演算）

3.数据约束（完整性约束）

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

2.5.2 实体完整性

实体完整性：候选键的属性值不允许为空（NULL）。

实体完整性规则规定，每个表的候选键中的所有值都不能为空。这确保了每一行记录在候选键上的唯一性和可辨识性。

NULL值：表示未知值或不存在的值，通常用于表示数据缺失或不可用。

NULL的影响：聚合、算术表达式、比较等。

• 在计算和比较中，NULL会影响结果。例如，任何数与NULL相加，结果仍然是NULL。
• 在逻辑操作中，NULL的处理需要特别注意，以避免错误。

2.5.3 参照完整性

参照完整性：如果Fk是关系R1的外键，Pk是关系R2的候选键，那么Fk的值必须等于Pk的值或为空（NULL）。

参照完整性确保一个表中的外键值必须在被引用表中存在，或为空。这维护了不同表之间的数据一致性。

• 学生表中的系编号（Dno）是外键，引用了系表中的系编号（Dno）。学生表中的每个系编号必须在系表中存在。

用户定义的完整性

2.5.4 用户定义的完整性

用户定义的完整性：根据不同的应用程序，用户可以定义的完整性约束。

用户定义的完整性是指根据特定应用需求，由用户定义的约束规则。这些规则可以保证数据符合特定业务逻辑。

• 学生表中的性别（Gender）必须是“M”或“F”。
• 年龄（Age）必须在12到35岁之间。

2.6 本章术语回顾

• 域（Domain）：属性值的集合。
• 笛卡尔积（Cartesian Product）：两个或多个集合的组合。
• 元组（Tuple）：关系中的一行。
• 关系（Relation）：数据表。
• 关系实例（Relation Instance）：在特定时刻关系的具体数据。
• 关系模式（Relation Schema）：关系的结构定义。
• 属性（Attribute）：数据表中的列。
• 度（Degree）：关系中的属性数量。
• 基数（Cardinality）：关系中的元组数量。
• 候选键、主键、超键、外键：用于标识和引用关系中数据的键。
• 主属性/非主属性：候选键中的属性/其他属性。
• 实体完整性、参照完整性、用户定义的完整性约束：用于确保数据一致性和完整性的规则。
• NULL：表示未知或不存在的值。
• 模式图：展示数据库结构和表间关系的图。

第三章 形式化关系查询语言

3.1 查询语言 (Query Language)

查询语言是用户从数据库中请求信息的语言。

3.1.1 语言分类 (Categories of languages)

过程化语言 (procedural)：用户需要描述如何获取数据。

非过程化语言 (non-procedural)：用户只需描述需要什么数据，而不需要描述如何获取数据。

3.1.2 “纯”语言 ("Pure" languages)

“纯”语言指只包含数据操作元素的语言。

过程化语言 (Procedural)

• 关系代数 (Relational Algebra)

非过程化语言 (Non-procedural)

• 元组关系演算 (Tuple Relational Calculus)
• 域关系演算 (Domain Relational Calculus)

纯语言的作用

纯语言构建了查询语言的基础，查询语言是用户常用的语言。

3.2 关系代数 (Relational Algebra)

关系代数是一种过程化语言，提供了一系列的操作，包括：

• 并 (Union, ∪)：用于合并两个关系的所有元组。
• 交 (Intersection, ∩)：用于获取两个关系中共同的元组。
• 差 (Difference, -)：用于获取在第一个关系中但不在第二个关系中的元组。
• 笛卡尔积 (Cartesian Product, ×)：将两个关系的所有元组合并。
• 投影 (Project, π)：用于选择关系中的某些列。
• 选择 (Select, σ)：用于选择满足特定条件的元组。
• 连接 (Join, ⨝)：用于合并两个关系，根据某些条件匹配元组。
• 除 (Division, ÷)：用于查找在一个关系中但不在另一个关系中的元组。

关系代数的运算符操作基于集合论，输入一个或多个关系，输出一个新的关系。这些操作是关系数据库管理系统（RDBMS）中SQL查询的基础。

3.2.1 关系代数概览

关系代数提供了一系列基于集合的操作，包括并、交、差、笛卡尔积、投影、选择、连接和除。操作符接受一个或多个关系作为输入，并输出一个新的关系。

3.2.1.1 传统关系代数操作 (Traditional Relational Algebra Operations)

这些操作只涉及行：

• 并 (Union, ∪)：

记号：R

 S

含义：返回两个关系中所有不重复的元组。

运算规则：所有在关系 R 或 S 中的元组。

• 交 (Intersection, ∩)：

记号：R

 S

含义：返回两个关系中共有的元组。

运算规则：所有在关系 R 和 S 中的元组。

• 差 (Difference, -)：

记号：R - S

含义：返回在关系 R 中但不在关系 S 中的元组。

运算规则：所有在关系 R 中但不在关系 S 中的元组。

• 笛卡尔积 (Cartesian Product, ×)：

记号：R

 S

含义：返回关系 R 和 S 的所有可能组合。

运算规则：所有关系 R 的元组和关系 S 的元组的组合。

3.2.1.2 特殊关系代数操作 (Special Relational Algebra Operations)

这些操作涉及行和列：

• 投影 (Project, π)：

记号：

 

含义：从关系 R 中选择指定的列。

运算规则：返回关系 R 的某些列，并去重。

• 选择 (Select, σ)：

记号：

 

含义：关系 R 中选择满足条件 p 的元组。

运算规则：返回关系 R 中所有满足条件 p 的元组。

• 连接 (Join, ⨝)：

记号：

 

含义：将关系 R 和 S 中的元组基于某个条件连接。

运算规则：返回关系 R 和 S 的所有满足连接条件的组合。

• 除 (Division, ÷)：

记号：R ÷ S

含义：从关系 R 中找出在 S 中出现的所有元组。

运算规则：返回关系 R 中在 S 中出现的元组。

3.2.2 关系的相容性 (Compatibility for the Relational Algebra)

要保证并、交、差操作的有效性，参与操作的关系必须相容。

相容条件：

• 关系 ( R ) 和 ( S ) 必须有相同的元数（属性数量相同）
• 属性域必须兼容（例如，关系 ( R ) 的第二列与关系 ( S ) 的第二列处理相同类型的值 / 域）

3.3 关系代数（具体）：

3.3.1 并 (Relational Algebra (1): Union)

记号：R

 S

定义：( R

 S =

 )

要求：( R ) 和 ( S ) 必须相容。

性质：( R

 S = S

 R )

3.3.2 差、交同理

3.3.3 笛卡尔积 (Cartesian Product)

记号：R

 S

定义：( R

 S = { t q | t

 R

 q

 S } )

假设：假设 ( R ) 和 ( S ) 的属性不相交。

性质：( R

 S = S

 R )

• 若 ( R ) 的度为 ( n )，( S ) 的度为 ( m )，则 ( R

  

   S ) 的度为 ( n + m )。
• 若 ( R ) 的基数为 ( n )，( S ) 的基数为 ( m )，则 ( R

  

   S ) 的基数为 ( n

  

   m )。

示例：

关系 ( R ) 和 ( S ) 的笛卡尔积结果展示了所有可能的组合。

3.3.4 选择 (Select)

记号：

   ( p ) 被称为选择谓词。

定义：

 =

其中 ( p ) 是命题演算中的公式，由 (

 )（和）、(

 )（或）、(

 )（非）等连接词组成。

示例

关系 R：Student，选择年龄小于20岁的学生：

3.3.4.1 操作符优先级

操作符的优先级顺序如下：

1. 括号：( )
2. 比较运算符：=，≠，>，≥，<，≤
3. 逻辑非：¬
4. 逻辑与：∧
5. 逻辑或：∨

3.3.5 投影 (Project)

记号：

   ( A1, A2, ..., Ak ) 是属性名称，( R ) 是关系名称。

定义：

投影的结果是通过删除未列出的列而获得的具有 ( k ) 列的关系。

由于关系是集合，因此结果中重复的行会被删除。

示例：

关系 R：Student

投影得到学生的名字和年龄：

投影得到学生的名字和部门编号：

组合操作：先选择部门编号为“03”且年龄大于19岁的学生，再投影得到学号和名字：

3.3.6 连接 (Join)

为什么使用连接 (Why use Join?)

• 连接操作用于合并两个关系，通过特定条件将相关联的元组合并。
• 示例查询：选择所有修读过“传感器网络”课程且成绩超过90分的学生姓名。

记号： R

 S

定义： R

• 

  ，其中

  

• 

  ，其中

  

• t

  

   R, s

  

   S
• A 和 B 需要兼容

• 

   可以是比较运算符，如

  

θ-连接 通常与选择和投影一起使用。

连接操作示例：

示例 1

展示了关系R和S的笛卡尔积与连接的区别。

示例 2

选择学历不低于岗位要求的员工，找出所有员工的姓名及其可能竞聘职位的名称。

步骤 1：笛卡尔积

合并员工和职位两个关系。

步骤 2：选择

过滤出学历不低于岗位要求的员工。

步骤 3：投影

最终结果仅保留员工编号、姓名和职位名称。

3.3.7 更名 (Rename)

记号：

 

定义：

将一个关系重命名为另一个不同的名称。

复制一个关系并赋予新名称。

，其中

和

关系名不同，但结构相同。

示例

选择既修读过“2015030101”又修读过“2015040101”的课程编号列表。

3.3.8 各类连接

3.3.8.1 等值连接（Equal-Join）

记号：

定义：

• 

  ，其中

  

• 

  ，其中

  

• 

• A 和 B 需要兼容

等值连接是 θ-连接的一种特殊情况，其中连接条件是相等比较（=）。

示例：

展示了关系 ( R ) 和 ( S ) 的等值连接：

3.3.8.2 自然连接（Natural-Join）

记号：

定义：

自然连接要求 R 和 S 具有相同的属性，且在结果中删除重复的列。

自然连接是等值连接的一种特殊情况，它不仅检查相等条件，还自动去除重复的属性。

示例：

展示了关系 R 和 S 的自然连接：

3.3.8.3 外连接（Outer Join）

查询：列出所有教师的全部信息。

查询语句：

问题：只使用内连接时，编号为“003”的教师的信息丢失了。

为什么使用外连接

• 避免信息丢失：外连接是连接操作的一种扩展，避免信息丢失。
• 操作描述：计算连接操作，并将另一关系中不匹配的元组添加到连接结果中。
• 使用空值 (null)：空值 (null) 表示值未知或不存在。

外连接类型

• 内连接 (Inner Join)：

只返回两个关系中匹配的元组。

• 左外连接 (Left Outer Join)：

主要用于保留左表中的所有记录，即使在右表中没有对应的匹配记录。

示例：列出所有教师的信息，即使他们没有教授任何课程。

• 右外连接 (Right Outer Join)：

主要用于保留右表中的所有记录，即使在左表中没有对应的匹配记录。

示例：列出所有课程的信息，即使这些课程没有任何教师教授。

• 全外连接 (Full Outer Join)：

返回左右关系中的所有元组，不论是否存在匹配。

示例：列出所有教师和所有课程的信息，不论是否存在匹配。

3.3.9 除法（Division）

符号表示：

关系模式：

• 

• 

• 

  ：模式 ( S ) 中的每一个属性也是模式 ( R ) 中的属性。

除法运算结果：

• 

   的结果是一个关系，其模式包含所有在 R 中但不在 S 中的属性。

适用于包含“对于所有...”的查询。

数学定义：

示例：通过除法找出选了全部课程的同学的学号

3.4 元组关系演算 (2024不考)

3.4.1 定义与基础

元组关系演算公式：

关系：

元组关系演算基于谓词演算（Predicate Calculus），用于数理逻辑中的查询表达。

元组关系演算的定义

非过程化查询语言：

元组关系演算是一种非过程化查询语言，每个查询形式为

。

术语解释：

• t：元组变量。
• t[A]：元组 t 在属性 A 上的值。
• t ∈ R：元组 t 在关系 R 中。
• P：谓词演算中的公式。

3.4.2 谓词演算公式的构成

组成部分：

• 属性和常量的集合。
• 比较操作符集合（如 <, ≤, =, ≠, >, ≥）
• 连接词集合，如 and (∧), or (∨), not (¬)
• 量词：

• 存在量词：( ∃ t ∈ R (P(t)) ) 表示存在一个元组 t 使得 P(t) 为真。
• 全称量词：( ∀ t ∈ R (P(t)) ) 表示对于所有元组 t，P(t) 都为真。

3.4.3 原子公式

元组在关系中：

• 示例：( t ∈ R )。
• 查询：

  

属性值比较：

• 示例：( t[A] θ c )
• 查询：

  

示例：( t[A] θ u[B] )

查询：找出所有不是最年轻的学生。

查询语句：

3.4.4 连接词

连接词的定义：

• 如果 F 是一个公式，则

  

   也是一个公式。
• 如果

  

   和

  

   是公式，则

  

   也是一个公式。
• 如果

  

   和

  

   是公式，则

  

   也是一个公式。

连接词的使用：

查询示例：

查询1：找出所有年龄小于20岁的男学生。

查询2：找出所有03号系年龄小于20岁的男学生。

查询3：找出所有不在03号系的学生。

查询4：找出所有不属于年龄小于20岁的男学生的学生。

3.4.5 量词

量词的定义：

如果 F 是一个公式，则 ( ∃ t ∈ R (F(t)) ) 也是一个公式。

如果 F 是一个公式，则 ( ∀ t ∈ R (F(t)) ) 也是一个公式。

量词的使用

查询示例：

查询1：找出所有不是最年轻的学生。

查询2：找出所有每门课成绩都大于60分的学生。

查询3：找出所有就读于“计算机科学”系的学生。

查询语句：

查询4：找到所有年龄比“Sun Mei”小的学生。

查询5：找到选修了所有课程的学生。

查询6：找到每个学生的分数都大于60分的系信息。

3.4.6 运算符的优先级

优先级顺序：

( ) 、 ¬ 、∧ 、∨ 、∃ 、∀

示例查询：

3.4.7 关系代数与元组关系演算的结合

查询 1：列出选修了由教师编号为“001”的教师授课的至少一门课程的所有学生的名字。

关系代数：

元组关系演算：

查询 2：列出没有选修由教师编号为“001”的教师授课的课程的所有学生的名字。

关系代数：

元组关系演算：

查询 3：列出选修了由教师编号为“001”的教师授课的所有课程的学生的名字。

关系代数：

元组关系演算：

查询 4：列出没有选修由教师编号为“001”的教师授课的至少一门课程的所有学生的名字。

关系代数：

元组关系演算：

核心概念

存在量词 (∃)：表示存在至少一个元组满足条件。

全称量词 (∀)：表示所有元组都满足条件。

逻辑运算符：

与 (∧)：表示两个条件都为真。

或 (∨)：表示至少一个条件为真。

非 (¬)：表示条件为假。

应用场景

复杂查询：用于描述需要复杂逻辑判断的查询。

多表关联：能够处理多表之间的关联和过滤条件。

第四章 结构化查询语言

4.1 SQL 概述 (Overview of SQL)

4.1.1 SQL 操作

• DDL（数据定义语言）：

创建 (Create)

修改 (Alter)

删除 (Drop)

• DML（数据操纵语言）：

插入 (Insert)

更新 (Update)

删除 (Delete)

查询 (Select)

• DCL（数据控制语言）：

授权 (Grant)

收回 (Revoke)

4.1.2 数据定义语言 (DDL)

允许指定一组关系及每个关系的详细信息，包括：

1. 创建数据库
2. 创建关系模式（表）
3. 定义每个属性的域值
4. 创建完整性约束
5. 创建视图、索引、表空间（关系在磁盘上的物理存储结构）
6. 安全和授权信息
7. 等等……

4.1.3 SQL 中的域类型

• char(n)：固定长度字符字符串，用户指定长度 n
• varchar(n)：可变长度字符字符串，用户指定最大长度 n
• int：整数（机器相关的有限整数子集）
• float(n)：浮点数，用户指定精度至少为 n 位
• numeric(p, d)：固定点数，用户指定精度 p 位，其中 d 位为小数点右边的位数

4.1.4 SQL 中的日期和时间类型

• date：包含年、月和日的日期（4 位数字年份）。

例如：date '2001-7-27'

• time：包含小时、分钟和秒的时间。

例如：time '09:00:30' 或 time '09:00:30.75'

• datetime：日期加时间。

例如：datetime '2012-3-13 08:25:30.75'

*其他：所有域类型允许使用空值（null），声明属性为 not null 则禁止该属性为空

4.2 SQL-DDL/DML：创建数据库 (Create Database)

4.2.1 SCT 数据库示例

Student 表：( Sno ) char(10), ( Sname ) char(20), ( Gender ) char(2), ( Age ) integer, ( Dno ) char(2), ( Class ) char(8)

Dept 表：( Dno ) char(2), ( Dname ) char(20), ( Dean ) char(20)

Course 表：( Cno ) char(3), ( Cname ) char(30), ( Hours ) integer, ( Credit ) float(1), ( Tno ) char(3)

Teacher 表：( Tno ) char(3), ( Tname ) char(30), ( Dno ) char(2), ( Salary ) float(2)

SC 表：( Sno ) char(10), ( Cno ) char(3), ( Score ) float(1)

4.2.2 创建数据库

命令：Create database db_name;

示例：Create database SCT;

创建表

命令：Create table R (A1 D1, A2 D2, ..., An Dn, (integrity-constraint1), ..., (integrity-constraintk));

解释：

• ( R ) 是关系或表的名称。
• 每个

  

   是关系模式中属性的名称。

• 

   是属性

  

   的数据类型。

示例：

Create Table Student (Sno char(10) Not Null, Sname char(20), Gender char(2), Age integer, Dno char(2), Class char(8));

4.2.3 创建表中的完整性约束

• Not NULL：非空约束，确保字段不能为NULL。
• Primary Key：主键约束，用于唯一标识表中的每一行。
• Check：检查约束，用于验证数据满足特定条件。
• 示例：

1.  Table Student (

•   Sno char(10) Not NULL,
•   Sname char(20),
•   Gender char(2),
•   Age integer,
•   Dno char(2),
•   Class char(8),
•   Primary Key (Sno),
•   Check (Age > 12)

这里定义了Student表，其中Sno为主键且不能为空，Age字段必须大于12。

4.2.4 插入数据和删除数据

插入数据：

使用 INSERT INTO 命令：

Insert into R [(A1, A2, ..., An)]

values (value1, value2, ..., valuen);

示例：

Insert into Student values ('2015030101', 'Li Jian', 'M', 20, '03', '20150301');

注意：如果违反任何完整性约束，插入操作将失败。

删除数据：

使用 DELETE FROM 命令删除表中的所有元组：

1. 1.  From R;

示例：

1. 1.  From Student;

注意：后续我们将学习如何删除特定的元组。

4.3 SQL-DML：SELECT 语句

SQL 基于集合和关系操作，经过一定的修改和增强。

典型的 SQL 查询形式：

1.  A1, A2, ..., An

1.  R1, R2, ..., Rm
2.  P;

这个查询等价于关系代数表达式：

SQL 查询的结果是一个关系。

4.3.1 SELECT 子句

SELECT 子句列出查询结果中所需的属性，对应于关系代数中的投影操作。

示例：

找出所有学生的名字：

Select Sname From Student;

在关系代数中，这个查询等价于：

注意：SQL 名称不区分大小写，可以使用大写或小写字母。

星号 (*) 表示选择所有属性：

Select * From Student;

SQL 允许关系中有重复值。

去除重复值，在 SELECT 后插入关键字 DISTINCT：

示例：找出成绩大于95的所有学生学号，去除重复：

elect distinct Sno From SC Where score > 95;

保留重复值，关键字 ALL 表示不去除重复值：

示例：

Select all Sno From SC Where score > 95;

SELECT 子句中可以包含算术表达式：

• • 例如：选择教师的编号、姓名和两倍的工资：

1. 1.  Tno, Tname, Salary * 2 From Teacher;

4.3.2 WHERE 子句

WHERE 子句指定查询结果必须满足的条件，对应于关系代数中的选择谓词。

找出所有在 '03' 号系且年龄小于20岁的学生姓名：

Select Sname

From Student

Where Dno = '03' and Age < 20;

比较结果可以使用逻辑连接词 and, or, not 组合。

SQL 包含 BETWEEN 比较操作符：

示例：找出工资在3000到4000之间的教师姓名：

Select Tname

From Teacher

Where Salary between 3000 and 4000;

示例：找出年龄在19到21岁的学生姓名：

Select Sname

From Student

Where Age between 19 and 21;

注意使用 and, or, not 时的优先顺序：

Select Tname

From Teacher

Where Salary < 2000 or (Salary > 2500 and Dno = '03');

列出选修了课程 '001' 或 '002' 的所有学生的学号：

Select Sno

From SC

Where Cno = '001' or Cno = '002';

4.3.3 FROM 子句

FROM 子句列出查询中涉及的关系，对应于关系代数中的笛卡尔积操作。

示例：

找出 Student 和 SC 表的笛卡尔积：

Select *

From Student, SC;

找出选修了课程编号为 '002' 的所有学生的姓名：

Select Sname

From Student, SC

Where Student.Sno = SC.Sno and Cno = '002';

4.3.4 重命名操作

SQL 允许使用 AS 子句对关系和属性进行重命名：

示例：

old_name as new_name

找出所有学生的名字、学号和年龄，并将列名 Sno 重命名为 Student_ID：

Select Sname, Sno as Student_ID, Age

From Student;

示例：找出选修了课程编号为 '003' 的所有学生的名字和成绩：

Select T.Sname, S.score

From Student as T, SC as S

Where T.Sno = S.Sno and S.Cno = '003';

4.3.5字符串操作：LIKE 和 NOT LIKE

SQL 包含用于字符串比较的字符串匹配操作符。

• 百分号 (%)：匹配零个或多个字符。
• 下划线 (_)：匹配单个字符。
• 反斜杠 ()：转义字符，表示后面的字符按字面意义解释。
• LIKE 子句：

示例：找出姓氏为 '刘' 的所有学生的名字和年龄：

Select Sname, Age

From Student

Where Sname like '刘%';

查询名字是“刘某某”的学生的名字和年龄：

Select Sname, Age

From Student

Where Sname like '刘_ _';

说明：使用LIKE操作符和下划线（_）进行字符串匹配，其中每个下划线代表一个字符。

查询姓氏不是“刘”的学生的名字和年龄：

Select Sname, Age

From Student

Where Sname not like '刘%';

说明：使用NOT LIKE操作符和百分号（%）进行字符串匹配，百分号代表任意多个字符。

4.3.6 结果排序：ORDER BY

有时候需要对查询结果进行重新排序。

ORDER BY 子句：

1.  by column_name [asc | desc];

• asc：升序排列，默认值。
• desc：降序排列。

示例：按学号升序排列显示所有学生的学号和姓名：

Select Sno, Sname

From Student

Order by Sno asc;

示例：按成绩降序排列显示成绩大于90并选修了课程'002'的学生学号：

Select Sno

From SC

Where Cno = '002' and Score > 90

Order by Score desc;

4.3.7 多表连接操作

4.3.7.1 等值连接 (Equal-Join)

多表连接的基本结构：

Select A1, A2, ..., An

From R1, R2, ..., Rm

Where P;

关系代数表示为：

示例：找出选修了课程'001'的所有学生的名字，并按成绩降序排列：

Select Sname

From Student, SC

Where Student.Sno = SC.Sno and SC.Cno = '001'

Order by Score DESC;

示例：三表连接，找出选修了数据库课程的所有学生的名字，并按成绩降序排列：

Select Sname

From Student, SC, Course

Where Student.Sno = SC.Sno and SC.Cno = Course.Cno and Cname = 'Database'

Order by Score DESC;

4.3.7.2 不等值连接 (Not Equal-Join)

示例：找出薪水不同的两位教师的名字：

Select T1.Tname, T2.Tname

From Teacher T1, Teacher T2

Where T1.Salary > T2.Salary;

示例：找出年龄不同的两位学生的名字：

Select S1.Sname as Stud1, S2.Sname as Stud2

From Student S1, Student S2

Where S1.Age < S2.Age;

示例：列出选修了课程'001'和'002'的所有学生的学号：

Select S1.Sno

From SC S1, SC S2

Where S1.Sno = S2.Sno and S1.Cno = '001' and S2.Cno = '002';

示例：列出同时选修了课程'001'和'002'，且'001'课程成绩高于'002'课程成绩的学生学号：

Select S1.Sno

From SC S1, SC S2

Where S1.Sno = S2.Sno and S1.Cno = '001' and S2.Cno = '002' and S1.Score > S2.Score;

示例：列出未选修任何由“李敏”教授课程的所有学生的名字：

Select Sname

From Student S, SC, Course C, Teacher T

Where S.Sno = SC.Sno and SC.Cno = C.Cno and C.Tno = T.Tno and T.Tname <> 'Li Min';

4.3.8 嵌套子查询

• SQL 提供了嵌套子查询的机制。
• 嵌套子查询是一个嵌套在另一个查询中的 SELECT-FROM-WHERE 表达式。
• 常见用途：

• 执行集合成员测试
• 进行集合比较
• 确定集合基数

4.3.8.1 嵌套子查询：[not] in

基本语法：

1. [not] in (subquery);

Expression 通常是列名或常量。

示例：列出“刘刚”和“张强”的所有信息：

Select *

From Student

Where Sname in ('Liu Gang', 'Zhang Qiang');

示例：列出选修课程号为'001'的所有学生的学号和名字：

Select Sno, Sname

From Student

Where Sno in (Select Sno From SC Where Cno = '001');

示例：列出选修了课程'001'和'002'的所有学生的学号：

Select Sno

From SC

Where Cno = '001' and Sno in (Select Sno From SC Where Cno = '002');

示例：列出未选修任何由“李敏”教授的课程的所有学生的名字：

Select Sname

From Student

Where Sno not in (Select Sno

     From SC, Course C, Teacher T

     Where SC.Cno = C.Cno and C.Tno = T.Tno and T.Tname = 'Li Min');

4.3.8.2 相对子查询与非相对子查询

示例：包括外层查询和内层查询：

如果内层查询可以独立执行且不需要从外层查询中导入参数值，则称为非相对子查询。

有时内层查询需要从外层查询中导入参数值，称为相对子查询。

示例：列出未通过任何课程考试的所有学生的名字：

Select Sname

From Student S1

Where 60 > all 

(Select Score

From SC

Where SC.Sno = S1.Sno);

4.3.8.3 嵌套子查询：some / all

基本语法：

Expression θ some (subquery);

Expression θ all (subquery);

其中 θ 为比较操作符（>, >=, <, <=, =, <>）。

解释：

Expression θ some (subquery) 等价于存在某个子查询结果使得表达式为真。

Expression θ all (subquery) 等价于所有子查询结果使得表达式为真。

示例：

(5 < some (5, 6)) 为真，因为 5 < 6。

(5 = some (5, 6)) 为真，因为 5 = 5。

示例：

(5 < all (5, 6)) 为假，因为 5 不小于 5。

(5 = all (5)) 为真，因为 5 等于 5。

找出工资最低的教师姓名：

Select Tname

From Teacher T1

Where T1.Salary <= all (Select Salary From Teacher);

找出选修了课程'001'但成绩不是最高的所有学生的学号：

Select Sno

From SC

Where Cno = '001' and Score < some (Select Score From SC Where Cno = '001');

1. 列出未通过任何课程考试的所有学生的姓名：

Select Sname

From Student S1

Where 60 > all (Select Score From SC Where SC.Sno = S1.Sno);

1. 找到选修'001'课程并且得分最高的所有学生的姓名：

Select Sno

From SC

Where Cno = '001' and Score = (Select Max(Score) From SC Where Cno = '001');

1. 找到'2015030101'学生选修的分数最低的课程数量：

Select Count(*)

From SC

Where Score = (Select Min(Score) From SC Where Sno = '2015030101');

1. 找到'李建'选修的分数最低的课程数量：

Select Count(*)

From SC

Where Score = (Select Min(Score) From SC Where Sno = '2015030101' and Sname = '李建');

• 表达式 = some (子查询) 等价于 表达式 in (子查询)：

Select Sno, Sname

From Student

Where Sno in (Select Sno From SC Where Cno = '001');

Select Sno, Sname

From Student

Where Sno = some (Select Sno From SC Where Cno = '001');

• 表达式 not in (子查询) 等价于 表达式 <> some (子查询)：

Select Sno, Sname

From Student

Where Sno not in (Select Sno From SC Where Cno = '001');

Select Sno, Sname

From Student

Where Sno <> some (Select Sno From SC Where Cno = '001');

4.3.8.4 嵌套子查询：[not] Exists

基本语法：

• [not] exists (子查询)：exists 构造返回 true 如果子查询不为空，返回 false 如果子查询为空。
• exists (子查询) ≡ (子查询 ≠ Ø)
• not exists (子查询) ≡ (子查询 = Ø)

列出由'李明'教师教授的所有学生的姓名：

Select distinct Sname

From Student S, SC, Course C, Teacher T

Where exists (Select * From SC, Course, Teacher

              Where SC.Sno = S.Sno and SC.Cno = C.Cno and C.Tno = T.Tno and T.Tname = '李明');

not exists 在某些查询中非常重要和必要：

列出所有选修了'2015030101'学生所选的所有课程的学生数量（至少，多于）：

Select DISTINCT Sno From SC SC1

Where not exists

      ( Select * From SC SC2

        Where SC2.Sno = '2015030101' and

              not exists

              ( Select * From SC SC3

                Where SC3.Cno = SC2.Cno and SC3.Sno = SC1.Sno));

例子解析：

列出所有选修了'2015030101'学生所选课程的学生数量（至少，多于）：

• 找出所有课程Cy，如果'2015030101'学生选了它，那么学生Sx也选了它。
• 谓词P：'2015030101'学生选了课程Cy。
• 谓词Q：学生Sx选了课程Cy。
• 逻辑公式转换：

(∀Cy)(P → Q) ≡ ¬∃Cy(¬(P → Q))

≡ ¬∃Cy(¬(¬P ∨ Q)) ≡ ¬∃Cy(P ∧ ¬Q)

列出所有选修了'2015030101'学生所选的所有课程的学生数量（至少，多于）：

Select DISTINCT Sno From SC SC1

Where not exists

      ( Select * From SC SC2

        Where SC2.Sno = '2015030101' and

              not exists

              ( Select * From SC SC3

                Where SC3.Cno = SC2.Cno and SC3.Sno = SC1.Sno));

列出由'001'教师教授的所有学生的姓名：

1.  Sname From Student S

1.  not exists

•       ( Select * From Course C
•         Where C.Tno = '001' and
•               not exists
•               ( Select * From SC
•                 Where SC.Sno = S.Sno and SC.Cno = C.Cno));

列出未选修由'李明'教师教授的任何课程的所有学生的姓名：

Select Sname From Student S

Where not exists

      ( Select * From Course, SC, Teacher

        Where SC.Sno = S.Sno and SC.Cno = Course.Cno and Course.Tno = Teacher.Tno and Teacher.Tname = '李明');

列出所有使用'S1'供应的所有零件的项目编号：

Select DISTINCT Jno From SPJ SPJ1

Where not exists

      ( Select * From SPJ SPJ2

        Where SPJ2.Sno = 'S1' and

              not exists

              ( Select * From SPJ SPJ3

                Where SPJ3.Pno = SPJ2.Pno and SPJ3.Jno = SPJ1.Jno));

4.3.9 结果计算和聚合函数

SELECT 子句不仅仅可以包含列名，还可以包含计算表达式或聚合函数：

例子：如果两个教师的工资不同，请列出他们的姓名和工资差：

Select T1.Tname as TR1, T2.Tname as TR2, T1.Salary - T2.Salary

From Teacher T1, Teacher T2

Where T1.Salary > T2.Salary;

SQL 中的聚合函数：

• Count：计算出现的次数。
• Sum：求和。
• Avg：求平均值。
• Max：求最大值。
• Min：求最小值。

例子：

列出所有教师的工资总和：

Select SUM(Salary) From Teacher;

列出学生的数量：

Select Count(*) From Student;

列出'数据库'课程的平均分数：

Select Avg(Score)

From Course C, SC

Where C.Cname = 'database' and C.Cno = SC.Cno;

Select 子句不仅遵循列名，还包含一些计算表达式或聚集函数。

Select 列名 | 表达式 | 聚集函数(列名) [...]

From R1, R2, ..., Rm

Where P

例如，如果两个老师的工资不同，请列出他们的姓名和工资差额。

Select T1.Tname as TR1, T2.Tname as TR2, T1.Salary - T2.Salary

From Teacher T1, Teacher T2

Where T1.Salary > T2.Salary

4.3.9.1 聚集函数

聚集函数在SQL中

这些函数操作关系中某列的多值集合，返回一个值。

列出所有老师工资的总和。

Select SUM(Salary) From Teacher

列出学生的数量。

Select Count(*) From Student

列出“数据库”课程的平均分。

Select Avg(Score)

From Course C, SC

Where C.Cname='database' and C.Cno=SC.Cno

Select MAX(Score) Select MIN(Score)

4.3.10 group by分组与Having子句

分组与Having子句

1. 列出每门课程的平均分。

Select Cno, Avg(Score) From SC Group by Cno

1. 列出每个学生的平均分。

Select Sno, Avg(Score) From SC Group by Sno

注意：Select子句中除聚集函数外的属性必须出现在Group by列表中。

1. 按学号分组的示例和按课程号分组的示例。

1. 找出失败课程总数大于二的学生数量。

Select Sno

From SC

Where Score < 60 

Group by Sno 

having Count(*) > 2

1. 找出失败课程总数大于二的学生数量并列出其平均分。

Select Sno,Avg(Score)

From SC

Where Sno in (Select Sno

From SC

Where Score<60 

Group by Sno 

Having Count(*)>2

) 

Group by Sno 

4.3.11 集合操作(union/intersect/except)

集合操作

• 集合操作（union、intersect和except）操作关系并对应于关系代数操作∪、∩、－。
• 上述每个操作自动消除重复项；要保留所有重复项，请使用相应的多集版本union all、intersect all和except all。
• 假设一个元组在关系R中出现m次，在关系S中出现n次，那么在不同集合操作中的出现次数为：
  • 在R和S的union all操作中出现m + n次。
  • 在R和S的intersect all操作中出现min(m, n)次。
  • 在R和S的except all操作中出现max(0, m - n)次。

示例：

列出修了‘002’或‘003’课程的学生人数。

Select Sno From SC Where Cno = '002' 

Union [all]

Select Sno From SC Where Cno = '003'

解释：

在SQL中，集合操作如UNION、INTERSECT和EXCEPT用于合并或比较两个或多个结果集。UNION操作符用于合并两个结果集，并自动消除重复项。INTERSECT操作符返回两个结果集中共有的行，而EXCEPT操作符返回在第一个结果集中但不在第二个结果集中的行。

4.3.12 空值

空值

• 元组可能有一个空值，用null表示，表示一个未知的值或不存在的值。
• 谓词is null（is not null）可用于检查空值。

• 空值参与的任何算术表达式结果都是空值。

示例：5 + null 结果为 null；100 * null 结果为 null；等等。

• 与空值的任何比较返回 false 或 unknown。

示例：5 < null 结果为 false；null = null 结果为 unknown；null < null 结果为 unknown；

• 所有聚合操作（除了 count(*) 以外）都会忽略包含空值的元组。

示例：

Select Avg(Score) From SC;

Select Count(*) From SC;

4.3.13 关系连接操作

连接操作主要包含三个要素：连接类型（Join type）、连接条件（Join condition）和最终结果。

4.3.13.1 连接类型（Join Type）

关系连接（Join operation）是关系数据库中最基本且最重要的操作之一。它用于将两个关系（表）结合起来，生成一个新的关系作为结果。其主要目的是从多个表中获取相关数据。

连接类型决定了在连接过程中如何处理在一个关系中但不在另一个关系中的元组。主要的连接类型包括：

• 内连接（Inner Join）：只返回两个表中满足连接条件的记录。
• 左外连接（Left Outer Join）：返回左表中的所有记录，以及右表中满足连接条件的记录。不满足条件的右表记录以NULL填充。
• 右外连接（Right Outer Join）：返回右表中的所有记录，以及左表中满足连接条件的记录。不满足条件的左表记录以NULL填充。
• 全外连接（Full Outer Join）：返回两个表中的所有记录，不满足连接条件的记录以NULL填充。

4.3.13.2 连接条件（Join Condition）

连接条件定义了两个表中哪些记录匹配，以及连接结果中包含哪些属性。常见的连接条件有：

• 自然连接（Natural Join）：自动匹配两个表中同名的列，并返回这些列匹配的记录。
• ON ：使用明确的谓词定义连接条件。
• USING (A1, A2, ..., An)：使用指定的列进行连接。

实例讲解：

关系Loan

Loan_number	Branch_name	Amount
L-170	Downtown	3000
L-230	Redwood	4000
L-260	Perryridge	1700

关系Borrower

Customer_name	Loan_number
Jones	L-170
Smith	L-230
Hayes	L-155

4.3.13.4 内连接（Inner Join）

SELECT *

FROM Loan

INNER JOIN Borrower ON Loan.Loan_number = Borrower.Loan_number;

结果：

Loan_number	Branch_name	Amount	Customer_name
L-170	Downtown	3000	Jones
L-230	Redwood	4000	Smith

4.3.13.5 自然内连接（Natural Inner Join）

SELECT *

FROM Loan

NATURAL JOIN Borrower;

结果：

Loan_number	Branch_name	Amount	Customer_name
L-170	Downtown	3000	Jones
L-230	Redwood	4000	Smith

4.3.13.6 左外连接（Left Outer Join）

SELECT *

FROM Loan

LEFT OUTER JOIN Borrower ON Loan.Loan_number = Borrower.Loan_number;

结果：

Loan_number	Branch_name	Amount	Customer_name
L-170	Downtown	3000	Jones
L-230	Redwood	4000	Smith
L-260	Perryridge	1700	NULL

4.3.13.7 右外连接（Right Outer Join）

SELECT *

FROM Loan

RIGHT OUTER JOIN Borrower ON Loan.Loan_number = Borrower.Loan_number;

结果：

Loan_number	Branch_name	Amount	Customer_name
L-170	Downtown	3000	Jones
L-230	Redwood	4000	Smith
NULL	NULL	NULL	Hayes

4.3.13.8 全外连接（Full Outer Join）

SELECT *

FROM Loan

FULL OUTER JOIN Borrower ON Loan.Loan_number = Borrower.Loan_number;

结果：

Loan_number	Branch_name	Amount	Customer_name
L-170	Downtown	3000	Jones
L-230	Redwood	4000	Smith
L-260	Perryridge	1700	NULL
NULL	NULL	NULL	Hayes

连接查询实例

假设我们需要列出所有借款人和贷款的详细信息，并按贷款金额排序：

SELECT Customer_name, Loan_number, Branch_name, Amount

FROM Loan

FULL OUTER JOIN Borrower USING (Loan_number)

ORDER BY Amount;

4.4 SQL-DML：INSERT/DELETE/UPDATE

概述:

数据操作语言 (DML) 是SQL语言的一部分，用于查询和修改数据库中的数据。主要包括四种操作：插入 (INSERT)、删除 (DELETE)、更新 (UPDATE) 和选择 (SELECT)。以下将重点介绍插入、删除和更新操作。

4.4.1 插入操作 (INSERT)

插入操作用于向表中添加一条或多条记录。基本语法如下：

INSERT INTO 表名 (列1, 列2, ..., 列N) VALUES (值1, 值2, ..., 值N);

示例：

向学生表中插入一条新的学生记录：

INSERT INTO Student VALUES ('2015030101', 'Li Jian', 'M', 20, '03', '20150301');

高级插入：

将一个查询结果插入到新表中。例如，我们创建一个新的表 St，其结构为 St(Sno, Sname, avgScore)，并将 Student 和 SC 两个表的查询结果插入到 St 表中：

INSERT INTO St (Sno, Sname, avgScore)

SELECT Sno, Sname, AVG(Score)

FROM Student, SC

WHERE Student.Sno = SC.Sno

GROUP BY Student.Sno;

4.4.2 删除操作 (DELETE)

删除操作用于从表中删除一条或多条记录。基本语法如下：

DELETE FROM 表名 WHERE 条件;

示例：

从 SC 表中删除记录：

DELETE FROM SC;

根据特定条件删除记录：

DELETE FROM Student WHERE Sno = '2015030102';

高级删除：

删除所有至少有四门课程不及格的学生：

DELETE FROM Student

WHERE Sno IN (

  SELECT Sno

  FROM SC

   WHERE Score < 60

  GROUP BY Sno

HAVING COUNT(*) >= 4

);

4.4.3 更新操作 (UPDATE)

更新操作用于修改表中的记录。基本语法如下：

UPDATE 表名 SET 列1 = 值1, 列2 = 值2, ... WHERE 条件;

示例：

将教师表中所有教师的工资提高10%：

UPDATE Teacher

SET Salary = Salary * 1.1;

根据条件更新工资：

UPDATE Teacher

SET Salary = Salary * 1.2

WHERE Dno IN (

     SELECT Dno

FROM Dept

WHERE Dname = 'Computer Science'

);

高级更新：

若学生成绩低于平均分，则将其成绩提高5%：

UPDATE SC

SET Score = Score * 1.05

WHERE Cno = '001' AND Score < (

SELECT AVG(Score)

FROM SC

WHERE Cno = '001'

);

将 Li Ji 的成绩更新为其班级的平均分：

UPDATE SC

SET Score = (

SELECT AVG(SC2.Score)

FROM SC SC1, Student S1, SC SC2, Student S2

WHERE S1.Class = S2.Class

AND SC1.Sno = S1.Sno

AND SC2.Sno = S2.Sno

AND S1.Sname = 'Li Ji'

AND SC1.Cno = '001'

AND SC1.Cno = SC2.Cno

)

WHERE Cno = '001' AND Sno IN (

SELECT Sno

FROM Student

WHERE Sname = 'Li Ji'

);

4.5 数据库视图详解

视图 (VIEW) 概述

视图是数据库中的一种虚拟表，它通过SQL查询从一个或多个表中提取数据并生成结果集。视图不存储实际数据，只存储查询结果，这使得视图非常灵活且高效。

视图的优点

• 简化查询：视图可以简化复杂的查询语句，使得查询更加简洁易读。
• 数据安全：通过视图可以限制用户访问表中的某些列或某些行，增强数据的安全性。
• 数据独立性：视图可以隐藏底层表的复杂结构，提供一种更友好的数据表示方式。

视图的创建

创建视图的基本语法如下：

CREATE VIEW 视图名 [(列名1, 列名2, ...)] AS 子查询;

示例：

创建一个视图 CompStud，包含所有计算机科学系学生的信息：

CREATE VIEW CompStud AS

SELECT *

FROM Student

WHERE Dno IN (

  SELECT Dno

  FROM Dept

  WHERE Dname = 'Computer Science'

);

创建一个视图 Teach，包含教师教授课程的信息：

CREATE VIEW Teach AS

SELECT T.Tname, C.Cname, Credit

FROM Teacher T, Course C

WHERE T.Tno = C.Tno;

视图的查询

通过视图，我们可以执行与普通表相同的查询操作。例如：

• 查找教授课程为“Network”的教师姓名：

1.  T.Tname

1.  Teach T
2.  T.Cname = 'Network';

• 列出所有计算机科学系学生的信息：

1.  *

1.  CompStud;

• 列出年龄小于20岁的计算机科学系学生的信息：

1.  *

1.  CompStud
2.  Age < 20;

视图的更新

大多数SQL实现只允许在简单视图（不含聚合）的情况下更新。视图可以更新的条件包括：

• FROM 子句只能包含一个表。
• SELECT 子句包含表的主键。
• SELECT 子句只包含表的属性名，不包含表达式、聚合或 DISTINCT 规范。

示例：

创建一个视图 ClassStud，包含学生的姓名和班级信息：

CREATE VIEW ClassStud (Sname, Sclass) AS

SELECT Sname, Class

FROM Student;

向 ClassStud 插入数据：

INSERT INTO ClassStud

VALUES ('Qian San', '20150402');

更新视图 S_G 中的平均成绩：

UPDATE S_G

SET AvgS = 85

WHERE Sno = '2015030102';

数据定义语言 (DDL) 操作详解

删除操作 (DROP)

删除操作用于移除数据库中的视图、表或数据库。

语法：

• 删除视图：

1.  VIEW 视图名;

• 删除表：

1.  TABLE 表名;

• 删除数据库：

1.  DATABASE 数据库名;

示例：

• 删除视图 CompStud：

1.  VIEW CompStud;

• 删除表 Teacher：

1.  TABLE Teacher;

• 删除数据库 SCT：

1.  DATABASE SCT;

修改操作 (ALTER)

修改操作用于修改数据库表的结构，如添加、删除或修改列。

语法：

• 添加列：

1.  TABLE 表名

1.  列名 数据类型;

• 删除列：

1.  TABLE 表名

1.  COLUMN 列名;

• 修改列：

1.  TABLE 表名
2.  COLUMN 列名 数据类型;

示例：

• 向学生表中添加地址和身份证号列：

1.  TABLE Student

1.  Saddr CHAR(40), PID CHAR(18);

• 修改学生表中姓名列的类型：

1.  TABLE Student
2.  Sname CHAR(30);

• 删除学生表中的唯一约束：

1.  TABLE Student

1.  UNIQUE(Sname);

第五章 数据库设计

5.1 数据库设计概述

数据库设计的定义：

数据库设计是创建一个详细的数据模型的过程。

数据库设计 — 模型化

什么是模型？

模型是对实际系统（如企业）的目标、结构和行为的抽象描述。现实世界的某个部分视观察者的观点和任务设置而定。

数据库设计：

世界上每个对象都有很多信息。

应该涉及多少以及什么样的信息？

这取决于要解决的问题。

例如：

• 一头牛
• 一头黑色的牛
• 一头有四条腿的牛
• 公牛，不是奶牛
• 等等

设计阶段：

1.描述数据需求

2.概念设计

• 实体-关系模型
• 功能需求规范

3.数据库实施

• 逻辑设计阶段
• 物理设计阶段

设计替代方案：

必须避免两大陷阱：

• 冗余：不良设计可能会重复信息。
• 不完整性：不良设计可能会使企业的某些方面难以或不可能建模。

5.2 实体-关系模型

5.2.1 实体（集合）

实体：现实世界中与其他对象区别开来的“事物”或“对象”。

实体集：同类型实体的集合，它们具有相同的属性。

实体的第一步是找出相关应用领域中的所有实体。

例如，图书管理中的实体有：读者、书籍、书架。

5.2.2 属性

属性：一个实体由一组属性表示，属性是每个实体成员拥有的描述性特征。

值：每个实体都有其属性的值。

示例

• 读者
• 实体集按类型表示：LB_ID、姓名、年龄、性别、地址
• 实体按类型值表示：001，李华，25，男，北京

键：实体的键是一组属性，这些属性足以区分实体。

例如，读者的键是LB_ID，因为LB_ID的值是唯一的，其他属性的值可能会重复。

简单属性与复合属性(Simple and Composite attribute)

• 简单属性：未分为子部分。
• 复合属性：可以分为子部分。例如，地址可以分为街道、城市、州、邮政编码等。

单值属性与多值属性(Single-valued and Multi-valued)

• 单值属性：对于特定实体只有一个值。
• 多值属性：对于特定实体有一组值。
• 例如，电话号码：教师和学生可能有多个电话号码。

派生属性(Derived attribute)：可以从其他相关属性或实体的值中导出。

例如，生日可以导出年龄。

空值属性与非空值属性：当实体没有属性值时，属性取空值。

弱实体与强实体(Weak Entity and Strong Entity)

弱实体:依赖于其他实体存在的实体。

强实体:不依赖于其他实体存在的实体。

例子

• 父母（强实体）/ 孩子（弱实体）
• 书（强实体）/ 版本（弱实体）
• 公司（强实体）/ 产品（弱实体）

合同（强实体）和合同条目（弱实体）的关系图。

实体分类

分类是识别、区分和理解对象（实体）的过程。分类意味着将对象（实体）分为某些特定用途的类别。

学生分类：本科生、研究生、硕士、博士。

关系是多个实体之间的关联。

• 参与（Participation）：实体之间的关联称为参与。
• 度（Degree）：关系中实体的数量称为关系的度。
• 关系的度可以是1度、2度或多度。
• 大多数数据库系统中的关系是2度关系。

3度关系：供应商、零件和项目。

1度关系：零件与部门之间的关系。

角色（Role）

实体在关系中扮演的功能称为实体的角色。

描述性属性

关系也可以有称为描述性属性的属性。

例如：借书关系的描述性属性包括借书时间和还书时间。

描述性属性的例子包括供应数量和供应时间。

映射基数

• 映射基数表示通过关系可以与另一个实体关联的实体数量。
• 映射基数在描述2度关系时最有用。
• 对于2度关系，映射基数类型包括：

• 一对一（1:1）
• 一对多（1:m）
• 多对一（m:1）
• 多对多（m:n）

示例

• 例子：画家（A）与画作（B）的关系。
• 一对一：每个画家对应一个画作。
• 一对多：每个画家可以有多个画作。

• 例子：学生（A）与课程（B）的关系。
• 多对一：每个学生可以选修多门课程。
• 多对多：多个学生可以选修多个课程。

通常，参与关系的一个实体在关系中有一个最小基数和最大基数，表示为（MinCard..MaxCard）

完全参与（Full Participation）

至少有一个实体参与关系，最小基数为1。

部分参与（Partial Participation）

允许没有实体参与关系，最小基数为0。

5.3实体-关系图

E-R建模过程

1.识别实体

2.确定每个实体的属性

3.为每个实体选择主键

4.建立实体之间的关系

5.绘制E-R模型

E-R图

常用的E-R图方法有：

• Chen方法
• Crow’s Foot方法
• IDEF1X方法
• UML
• ......

5.3.1 Chen方法

• 矩形：实体
• 菱形：关系
• 椭圆形：属性
• 双线椭圆：多值属性
• 虚线椭圆：派生属性

• 实线：连接带有属性的实体
• 虚线：连接带有属性的关系
• 下划线：主键
• 同数字下划线：多属性的主键

• 箭头线：从关系到一方
• 非箭头线：从关系到多方
• 单线：部分参与
• 双线：完全参与

• 带有1标记的线：从关系到一方
• 带有m或n标记的线：从关系到多方
• 带有1..1、0..1、0..m等标记的线：表示部分参与和完全参与

读者、书籍、书架之间的借阅关系。

图书管理系统中的实体及其关系，包括借书和保管关系。

复合属性、多值属性和派生属性的示例。

带有文字标记的线：表示实体在关系中的角色。

实际案例分析：企业员工、部门、项目之间的关系。

Chen方法：弱实体

• 双矩形：弱实体
• 双菱形：弱实体与强实体之间的关系
• 虚线下划线：弱实体的键

三角形带有isa文字：表示分类。

5.3.2 Crow’s Foot方法

• 矩形：实体
• 矩形通过一条线分为上下两部分

• 实体名称在上线
• 属性名称在下线

• 主键用下划线表示

• 关系用菱形表示
• 在线上直接写关系名称

映射基数

• 1：一对一
• 1或多：一对多
• 0或1：零或一
• 多：多对多

示例

• 学生与课程的关系。

• 教授与班级的关系。

• 零件由多个部件组成的关系。
• 员工与其配偶的婚姻关系。

 

分类

示例

车辆分类为汽车和卡车的关系图。

IDEF1X 方法案例

展示了订单、产品、服务等的关系和属性。

概念设计阶段

1.识别并列出实体。

2.识别业务规则，这些规则有助于识别实体间的关系，并同时找到约束条件。

3.识别实体的属性和主键。

4.检查实体间的关系。

5.绘制ER图。

5.3.3 业务规则

1.描述特定组织内的政策、程序或原则。

2.描述在组织环境内创建/执行操作的规则。

3.必须书面形式。

4.必须易于理解和广泛传播。

业务规则的来源

1.公司经理。

2.政策制定者。

3.部门经理。

4.书面文档（程序、标准、操作手册）。

5.与最终用户的直接访谈。

概念设计案例研究

1. 深入分析信息源：

• 包括研究结果、观察分析结果、原系统输出（报表和屏幕）、原系统的输入（数据的形式和屏幕）、原系统数据库/文件的说明。

1. 提取实体：

• 实体可能是由若干实体合并后形成的，也可能是从源中按实体规则提取出来的。

1. 业务规则：

• 客户名称为公司名称，必填项，长度<=50个字符。
• 客户地址必须包含省、市（县）、街道（镇/乡）及具体地址，必填项，长度<=100个字符。
• 法人，必填项，长度<=20个字符。
• 电话号码可以填写一个座机号码和/或一个手机号码，必填项，长度<=13个字符。
• 传真为座机号码，非必填项，长度<=13个字符。
• 等等

1. 绘制E-R图：

• 例子中展示了客户、产品、客户项目、客户跟踪等实体及其关系。

概念设计案例研究

展示了员工登记表，包括员工编号、姓名、部门、职位、入职日期、学历、培训经历、工作经历等信息。

5.4 转换ER模型到关系模式的步骤

以下是从ER模型（实体-关系模型）转换到关系模式的详细步骤和解释：

目标:

1.从概念数据模型中派生关系集合：

• • 了解如何从概念数据模型中提取一组关系。

2.使用规范化技术验证这些关系：

• • 学习如何通过规范化来验证派生的关系，以确保其结构正确。

3.验证逻辑数据模型是否支持所需的事务：

• • 确保逻辑数据模型能够支持系统所需的各种事务。

4.将多个用户视图合并为全局逻辑数据模型：

• • 将多个用户视图合并成一个代表所有用户视图的全局逻辑数据模型。

详细步骤:

5.4.1 从逻辑数据模型派生关系

• • 实体到关系：
    • 将每个实体转换为一个关系。
    • 例如，实体“Book”转换为关系：Book(ID, Name, Pub_date, Publisher)。
  • 实体的属性到关系的属性：
    • 将实体的每个属性转换为关系的属性。
  • 实体的键到关系的主键：
    • 将实体的主键转换为关系的主键。

1.复合属性

• • 复合属性的处理：
    • 复合属性（如日期中的年、月、日）在关系中分解为简单属性。
    • 例如，实体“Student”有一个复合属性“Birthday”，分解为：Student(ID, Name, Year, Month, Day)。

2.多值属性

• • 多值属性的处理：
    • 为多值属性创建一个新的关系，并将该属性及其对应实体的主键作为新的关系的外键。
    • 例如，实体“Teacher”有一个多值属性“Phone”，转换为：Contact_Info(ID, Phone)。

3.一对一（1:1）关系

• • 部分参与的关系：
    • 如果两个实体之间的关系是部分参与（0..1），则创建一个新关系，并将两个实体的主键组合为新关系的主键。
    • 例如：Employee(ID, Name)和Spouse(Husband_EMPID, Wife_EMPID)。
  • 完全参与的关系：
    • 如果一个实体完全参与关系，则将另一个实体的主键作为外键添加到这个实体中。
    • 例如：Employee(Emp_ID, Name, Dept_ID)。

4.一对多（1:n）关系

• • 一对多关系的处理：
    • 将“多”方的实体的主键作为外键添加到“多”方的关系中。
    • 例如：Parent(Parent_ID)和Child(Child_ID, Child_name, Parent_ID)。

5.多对多（m:n）关系

• • 多对多关系的处理：
    • 创建一个新的关系来表示多对多关系，并包括参与关系的实体的主键作为外键。
    • 例如：Student(S_ID, ...)和Course(C_ID, ...)通过关系CourseTaken(S_ID, C_ID, Score)。

6.弱实体

• • 弱实体的处理：
    • 创建一个关系，并将弱实体的所有简单属性及其主实体的主键作为新的关系的复合主键。
    • 例如：Company(CID, Cname, Address)和Product(CID, PID, Pname, Price)。

7.多度关系

• • 多度关系的处理：
    • 创建一个关系来表示多度关系，并包括所有参与实体的主键作为外键。
    • 例如：Supplier(S_ID, S_Name)和Part(P_ID, P_Name)通过关系Supply(S_ID, P_ID, Sup_Time, Quantity)。

这些步骤确保了从ER模型到关系模式的转换过程清晰、准确，并且最终的关系模式能够有效支持数据库系统的所有事务和操作。

分类实体（Classified Entity）

在ER模型中，分类实体通常包含一个高层实体和一个或多个低层实体。转换时需要注意以下几点：

• 高层和低层实体分别转换成不同的关系：
• 高层实体和低层实体都会转换成独立的关系。
• 低层实体继承高层实体的主键作为自己的主键和外键：
• 例如，实体“Student”有两个子类实体“Undergraduate”和“Graduate”。转换后，关系如下：

1. ID, Name, Gender, Age)
2. ID, Military_Train)
3. ID, Dissertation)

具体实例：

图中展示了一个ER图的转换示例：

• Person 实体具有属性 name, street, city
• Person 实体被分为 Employee（员工）和 Customer（顾客），分别具有特有属性 salary 和 credit-rating

有两种转换方式：

1.方式1：

1. city, street)
2. credit-rating)

1. salary)

• employee或customer的详细信息时需要访问两个表。

2.方式2：

1. city, street)
2. street, city, credit-rating)

1. street, city, salary)

5.4.2 使用规范化验证关系

• 验证逻辑数据模型中的关系是否经过规范化：
• 规范化是一种确保关系模式结构正确且无冗余的方法.

5.4.3 验证关系是否支持用户事务

确保逻辑数据模型中的关系支持所需的用户事务：

5.4.4 检查完整性约束

• 确保逻辑数据模型中表示了完整性约束：

主要包括：

• • 必要数据（Required data）
  • 属性域约束（Attribute domain constraints）
  • 多重性（Multiplicity）
  • 实体完整性（Entity integrity）
  • 参照完整性（Referential integrity）
  • 一般约束（General constraints）

5.4.5 与用户一起审查逻辑数据模型

• 与用户一起审查逻辑数据模型：
• 确保用户认为模型是企业或组织数据需求的真实表示。

5.4.6 合并逻辑数据模型到全局模型

• 这是一个可选步骤：
  • 将多个逻辑数据模型合并为一个全局逻辑数据模型，代表数据库的所有用户视图。
• 合并逻辑数据模型中的冲突：
  • 属性冲突（Attribute conflict）：例如，不同数据类型、不同值范围等。
  • 命名冲突（Naming conflict）：例如，同名异物（Homonymy），同物异名（Synonymy）。
  • 结构冲突（Structure conflict）：例如，同一对象在不同应用领域具有不同的抽象级别。

5.5 规范化（Normalization）

本部分的目标是学习以下内容：

1.规范化的目的：

• • 理解为什么要进行数据库规范化，即规范化的意义和作用。

2.没有规范化的潜在问题：

• • 了解如果不进行规范化，数据库设计可能出现的问题。

3.函数依赖的概念：

• • 掌握函数依赖的概念，这描述了属性之间的关系。

4.如何进行规范化过程：

• • 学习规范化的步骤和方法。

5.如何识别第一范式（1NF），第二范式（2NF），第三范式（3NF）和BC范式（BCNF）：

• • 理解各个范式的定义和特点。

6.违反1NF、2NF、3NF和BCNF规则的关系所存在的问题：

• • 了解不符合这些规范化规则的关系会导致哪些问题。

5.5.1 为什么规范化（Why normalize）

5.5.1.1 不良数据库设计的例子

以下是一个公司的订单系统信息表的示例，展示了不良数据库设计：

Cno   Cname  Caddr      Ono    Pno   Pname  Pprice  Qty

C001  Kris   New York    O001   P001  iPhone  600     3

C001  Kris   New York    O001   P002  iTouch  350     2

C001  Kris   New York    O002   P003  iPad    900     5

C002  Jenny  Washington  O003   P001  iPhone  600     8

字段解释：

• Cno：顾客编号
• Cname：顾客姓名
• Caddr：顾客地址
• Ono：订单编号
• Pno：产品编号
• Pname：产品名称
• Pprice：产品价格
• Qty：产品数量

问题：

• 如果删除订单'0002'，则产品'P003'将不存在。
• 如果没有订单，产品将不会被插入。
• 顾客、产品和订单的信息冗余，可能导致数据不一致。

导致上述问题的原因：

• 存在一些数据依赖：
  • 该关系的主键为（Cno, Pno, Ono）。
  • 产品名称和价格可以由产品编号（Pno）确定，而不是由（Cno, Pno, Ono）确定。
  • （Cname, Caddr）？

5.5.1.2 解决方案：规范化

通过规范化，将上述关系拆分为以下三个关系：

1.Customer(Cno, Cname, Caddr)

2.Product(Pno, Pname, Pprice)

3.Order(Ono, Cno, Pno, Qty)

5.5.1.3 规范化

定义：

• 规范化是一种用于生成适合企业/组织数据需求的关系集的技术。

适合的关系特征：

• 最少的属性数：支持企业/组织的数据需求的最少属性数。
• 紧密的逻辑关系：具有紧密逻辑关系的属性应在同一个关系中。
• 最小的冗余：每个属性只表示一次，重要例外是构成或部分构成外键的属性。

5.5.1.4 规范化的好处

• 更容易访问和维护数据：用户可以更方便地访问和维护数据。
• 最小的存储空间：占用最小的存储空间。

5.5.1.5 数据冗余和更新异常

具有冗余信息的关系可能会产生更新异常，主要有三种类型：

1.修改异常：因遗漏更新而导致的不一致性。

2.插入异常：由于某些信息无法插入而引发的问题。

3.删除异常：因删除导致的非预期数据丢失。

例子：

1. 数据冗余：客户、产品和订单信息冗余，导致数据不一致。
2. 删除异常：删除订单'0002'，则产品'P003'将不存在。
3. 插入异常：如果没有订单，产品将不会被插入。
4. 修改异常：若更新'C001'为'Chris'，只更新第一个记录，会导致数据不一致。

5.5.1.6更新异常的原因

• 依赖关系：单一关系模式中的某些属性之间的依赖关系。
• 函数依赖：
  • 部分函数依赖（Partial functional dependency）
  • 传递函数依赖（Transitive functional dependency）

5.5.1.7 各种范式

• 第一范式（1NF）：消除重复组，每个属性值都是原子的。
• 第二范式（2NF）：消除部分依赖，非主属性完全依赖主键。
• 第三范式（3NF）：消除传递依赖，非主属性不依赖于其他非主属性。
• BC范式（BCNF）：每个决定因素都是候选键。

通过这些规范化步骤，可以有效减少数据冗余，避免更新异常，从而提高数据库的完整性和一致性。

5.5.2 函数依赖（Functional Dependencies）

5.5.2.1 函数依赖的定义：

• • 设

    

     为一个关系模式，

    

     且

    

    。
  • 当且仅当对

    

     的所有合法关系

    

     的任何两个元组

    

     和

    

     来说，如果

    

    ，则

    

    。
  • 用符号表示为：

    

    ，表示“

    

     函数依赖于

    

    ”。
  • 反之：如果

    

     不函数依赖于

    

    ，则记作：

    

    。

例子

学生关系：

• • 学生信息：Student (Sno, Sname, Gender, Age, Address)。
  • 函数依赖：

1. 1. → Sname
   2. → Age
   3. → Gender
   4. → Address

例子分析

考虑一个关系

 的实例：

  A  B

• ----

1.  4
2.  5
3.  7
   • 在这个实例中，

     

      不成立，但

     

      成立。

5.5.2.2 常见的函数依赖类型

1.平凡依赖与非平凡依赖：

• • 平凡依赖：

    

     且

    

    。
  • 非平凡依赖：

    

     且

    

    。

2.例子：

• • 关系 SC (Sno, Cno, Score)：
    • 非平凡依赖：

      

    • 平凡依赖：

      

       和

      

5.5.2.3 部分函数依赖与完全函数依赖

1.部分函数依赖：

• • 如果

    

    ，但存在

    

     的真子集

    

     使得

    

    ，则称

    

    部分函数依赖于

    

    。

2.完全函数依赖：

• • 如果

    

    ，且不存在

    

     的真子集

    

     使得

    

    ，则称

    

     完全函数依赖于

    

    。

3.例子：

• • 关系 SC (Sno, Cno, Score)：

    

     

    

     为完全函数依赖。

5.5.2.4 候选键与非主属性

1.候选键：

• • 能唯一标识关系中的元组的属性或属性组。
  • 例如：Teaching (Sno, Age, Address, Cno, Cname, Score, Tno, Tname)，候选键：

    

    。

2.主属性：

• • 出现在任何候选键中的属性。

3.非主属性：

• • 不出现在任何候选键中的属性。

5.5.2.5 传递依赖

1.传递函数依赖：

• • 如果

    

    ，且

    

    ，则称

    

     传递依赖于

    

    。

2.例子：

• • 关系 Student (Sno, Dno, Dname)：

1. 1. → Dno
   2. → Dname
      • Dname 传递依赖于 Sno。

5.5.3 第一范式（1NF）

1.定义：

• • 如果关系的每个属性值都是原子的（不可再分的），则该关系满足第一范式。

2.例子：

• • 结构化类型的属性不满足 1NF：

1. 1.  |  name  |  fname  |  class |  telephone

• •  |  -------   |  -------   | ------- |   ----------

1. 1.    | Jones   |  Allan   |   2   | 555-234
   2.    | Smith   |  John   |   3   | 555-431
   3.    | Brown   |  Harry  |   2   | 555-1122
   4.    | White   | Edward  |   3   | 555-334

5.5.4 第二范式（2NF）

1.定义：

• • 如果关系中的每个非主属性完全函数依赖于任意候选键，则该关系满足第二范式。

2.如何满足 2NF：

• • 分析关系中的所有函数依赖。
  • 如果存在非主属性对候选键的部分函数依赖，则将关系分解为两个新关系。

3.例子：

• • 关系 Std_Dept (Sno, Cno, Score, SDept, Dname)：

1. 1. (Sno, Cno) → Score
   2. Sno → SDept
   3. Sno → Dname
   4. (Sno, Cno) → SDept
   5. (Sno, Cno) → Dname

• • 通过分解形成两个关系：

1. 1. Cno, Score)
   2. SDept, Dname)

这些步骤和概念是理解和应用数据库规范化的基础，帮助我们设计更高效和可靠的数据库。

5.5.5 第三范式（3NF）

第三范式 (3NF)：对于关系

，如果每个非主属性都不传递依赖于候选键，那么我们称关系

 满足 3NF。

如何使关系满足 3NF

1.分析关系的所有函数依赖。

2.如果有些非主属性传递依赖于候选键，则将关系分解为两个新关系。

例子

• 关系 Std_Dept (Sno, Cno, Score, SDept, Dname) 已经满足 2NF，现在我们进一步分解：

[1] Sno → SDept

[2] SDept → Dname

[3] Sno → Dname (传递依赖)

分解后的关系：

SC(Sno, Cno, Score)

SD(Sno, SDept)

Dept(SDept, Dname)

5.5.6 Boyce-Codd 范式（BCNF）

Boyce-Codd 范式 (BCNF)：对于关系

，如果每个类似于

 的函数依赖中，

 必须是候选键，那么我们称关系

 满足 BCNF。

如何使关系满足 BCNF

1.分析关系的所有函数依赖。

2.如果有些函数依赖不满足 BCNF，则将关系分解为两个新关系。

例子

• 关系 STC (Sno, Cno, Tno)：
  • 业务规则：每位老师只教授一门课程，每门课程只有一位老师教授。
  • 候选键：

    

    、

    

  • 非主属性：无
  • 函数依赖：

[1] (Sno, Cno) → Tno

[2] (Sno, Tno) → Cno

[3] (Cno) → Tno

[4] (Tno) → Cno

• • 

     和

    

     不满足 BCNF。

SC(Sno, Cno)

CT(Cno, Tno)

规范化与连接操作的权衡

• 规范化的必要性：
  • 减少数据冗余
  • 防止删除异常
  • 防止插入异常
  • 防止修改异常
• 规范化带来的问题：
  • 需要更多的连接操作进行数据查询和操作。

SC(Sno, Cno, Score)

SD(Sno, SDept)

Dept(SDept, Name)

• • 这些分解后的关系需要通过连接操作才能恢复到原始数据形式。

结论

数据库设计的意义和阶段： 

• • 需求分析
  • 概念设计
  • 逻辑设计
  • 物理设计

E-R 模型

• 实体（强实体、弱实体、分类实体）
• 关系（度、角色、映射基数）
• 属性（键、简单属性、复合属性、单值属性、多值属性、派生属性、描述属性）
• E-R 图（Chen、Crow's Foot、IDEF1X、UML 等）

从 E-R 模型到关系模式的转换

• 步骤1 到 步骤6 的详细转换过程

规范化

• 没有规范化的问题（数据冗余、三种异常）
• 函数依赖（非平凡函数依赖、部分函数依赖、完全函数依赖、传递函数依赖）
• 1NF、2NF、3NF、BCNF
• 从1NF到2NF到3NF到BCNF的分解方法

通过上述过程和方法，能够有效地设计和实现一个高效、无冗余且一致性强的数据库系统。

第六章：数据库的完整性

6.1 基本完整性概念

关系模型中的完整性

在特定应用的数据库设计中有许多完整性约束

1.如何在数据库中定义完整性约束

• • 实体完整性约束：确保每个实体有一个唯一的标识符，通常是主键。
  • 参照完整性约束：确保外键值在引用的表中存在，保持数据的一致性和完整性。
  • 用户定义的完整性约束：根据特定应用需求定义的其他约束，如特定字段的值范围、格式等。

2.完整性约束如何影响数据库

• • 完整性约束是数据库管理系统 (DBMS) 自动维护数据正确性和一致性的重要机制。
  • 定义的完整性约束规则可以自动检查和强制执行，如果有操作违反了这些约束，DBMS 会拒绝执行这些操作，从而保护数据库的完整性。

• 完整性：确保在任何时间和任何情况下数据的正确性和一致性。
• 引起完整性问题的原因：
  • 输入错误
  • 操作错误
  • 程序错误
• 如何确保数据库的完整性：
  • 定义一组完整性约束规则（通过 SQL-DDL）
  • 数据库管理系统 (DBMS) 会自动检查完整性约束规则，如果违反规则则不执行更新操作。

数据库完整性的控制机制

• 完整性约束的定义：数据模型的一个组成部分；约束数据的语义；存储在数据库中，作为模式的一部分。
• 完整性检查：检查操作要求是否违反完整性约束。
• 违约反应：如果操作要求违反完整性约束，则 DBMS 应采取措施保护数据的完整性。

完整性检查是基于完整性约束进行的，因此完整性约束的定义是数据库完整性控制机制的核心。

6.2 完整性约束的分类

6.2.1 按约束对象的粒度分类

1.列级约束：

• • 数据类型、值范围、是否允许为空等。

2.元组级约束：

• • 单个元组内列之间的关系。

3.关系级约束：

• • 不同元组之间的关系。

6.2.2 按约束状态分类

1.静态约束：

• • 数据在单一状态下的约束。

2.动态约束：

• • 数据在状态变化时的新旧数据均需满足的约束。

动态约束的例子

• 婚姻状态的动态约束：
  • 单身 → 已婚
  • 已婚 → 鳏寡
  • 已婚 → 离婚
  • 鳏寡 → 再婚
  • 单身 → 离婚（不允许）
  • 离婚 → 鳏寡（不允许）

6.2.3 静态列约束

• 数据类型约束：列的数据类型、长度、单位等。
• 数据格式约束：例如学生编号的格式、日期的格式等。
• 值范围约束：例如年龄范围、性别范围、成绩范围等。
• 空值约束：是否允许空值。
• 其他约束：例如列的排序描述等。

6.2.4 静态元组约束

• 元组级约束：单个元组内列之间的关系约束。
  • 例如：交货数量 <= 订单数量，在订单关系中。
  • 例如：教授的工资 >= 3600，在教师关系中。

6.2.5 静态关系约束

• 关系级约束：元组之间或关系之间的约束。
  • 实体完整性约束
  • 参照完整性约束
  • 函数依赖
  • 统计约束
  • 例如：2*员工的平均工资 <= 经理的工资 <= 5*员工的平均工资

6.2.6 动态列约束

• 动态列约束：修改列定义或值时的约束。
  • 修改列定义的约束：
    • 例如：当将一列的空值标记从“允许”改为“不允许”时，如果该列具有空值，则拒绝修改。
  • 修改值的约束：
    • 例如：年龄不允许减少。
    • 例如：工资只能增加。

6.2.7 动态元组约束

• 动态元组约束：修改元组值时，单个元组内不同属性应满足的约束。
  • 例如：当调整工资时，新的工资 >= 旧工资 + 1.5 * 工作年限。

6.2.7 动态关系约束

• 动态关系约束：关系在从一个状态变为另一个状态时应满足的约束。
  • 例如：事务的原子性和一致性。

6.3 完整性约束的表示方法

6.3.1 完整性约束的五元组表示法

完整性约束可以表示为五元组：( {D, O, A, C, P} )

• D（Data）：受约束作用的数据对象，可以是列、元组或关系。
• O（Operation）：触发约束检查的数据库操作，如插入、更新或删除。
• A（Assertion）：数据对象应满足的断言或语义约束。
• C（Condition）：为检查断言所预测的选定数据对象条件。
• P（Procedure）：如果约束被违反时触发的过程。

示例 01

约束：“学生编号不能为空” 在学生表中。

• D（Data）：学生编号（Sno）【属性】
• O（Operation）：插入或更新
• A（Assertion）：Sno 不能为空
• C（Condition）：对所有元组的 Sno 为空
• P（Procedure）：拒绝执行该操作（插入或更新）

示例 02

约束：“教授的工资不能低于 3600” 在教师表中。

• D（Data）：工资【属性】
• O（Operation）：插入或更新
• A（Assertion）：工资不能低于 3600
• C（Condition）：职称为“教授”
• P（Procedure）：拒绝执行该操作（插入或更新）

6.4 完整性约束的实现

6.4.1 DBMS 提供的功能

• DBMS 提供定义和检查实体完整性约束、参照完整性约束和用户定义的完整性约束的功能。
• 违反实体完整性约束和用户定义完整性约束的数据库操作会被拒绝。
• 违反参照完整性约束的数据库操作可以被拒绝，也可以执行操作并添加一些额外的操作以确保数据库的有效性。

6.4.2 参照完整性约束的实现

数据库“员工-部门”包括两个关系：EMP 和 DEPT

1.“Deptno”是 DEPT 的主键

2.EMP 的主键是“Empno”，Deptno 是外键

3.参照关系：EMP；被参照关系：DEPT

需要考虑的四种情况

1.外键是否允许为空

2.在被参照关系中删除元组

3.在参照关系中插入元组

4.更新主键的值

6.4.2.1 外键是否允许为空

• 取决于应用环境的语义
• 示例 1：
  • 关系“Dept”和“Student”
  • Dept 是被参照关系，其主键为 Dno
  • Student 是参照关系，其主键为 Sno，外键为 Dno
  • Dno 是否允许为空
• 示例 2：
  • 关系“Student”和“SC”
  • Student 是被参照关系，其主键为 Sno
  • SC 是参照关系，其外键为 Sno
  • Sno 是否允许为空

6.4.2.2 在被参照关系中删除元组

• 违反情况：当要删除被参照关系中的元组时，参照关系中存在一些元组，其外键值等于被删除元组中的主键值。
• 违约处理：根据应用环境，三种策略

1.级联删除：删除被参照关系中的元组及其在参照关系中的对应元组。

2.受限删除：系统拒绝删除被参照关系中的元组。

3.置空删除：删除被参照关系中的元组，并将参照关系中对应元组的外键值设为空。

6.4.2.3 在参照关系中插入元组

• 违反情况：当插入参照关系中的元组时，相应的元组在被参照关系中不存在。
• 违约处理：根据应用环境，两个策略

1.受限插入：系统拒绝将元组插入参照关系中。

2.递归插入：首先插入被参照关系中的相应元组，然后再将元组插入参照关系中。

6.4.2.4 更新主键的值

• 违反情况：如果被参照关系中主键值发生变化，但参照关系中存在一些元组，其外键值等于被参照关系中旧的主键值。
• 违约处理：根据应用环境，三种策略

1.级联更新：更新被参照关系中的主键值，并将参照关系中对应元组的外键值也进行相应更新。

2.受限更新：系统拒绝更改被参照关系中的主键值。

3.置空更新：更新被参照关系中的主键值，并将参照关系中对应元组的外键值设为空。

例子：主键值的更新

• 例子：将 Student 表中 Sno 从 '950001' 改为 '960123'，并且 SC 表中有 4 个元组的 Sno 为 '950001'。

1.级联更新：更新 SC 表中元组的 Sno 为 '960123'。

2.受限更新：系统拒绝更改 Student 表中的 Sno。

3.置空更新：将 Student 表中的 Sno 更新为 '960123'，并将 SC 表中对应元组的 Sno 设为空。

通过这些方法和策略，可以确保在执行数据库操作时，参照完整性约束得以维持，从而保持数据的一致性和完整性。

好的，接下来我将对这些幻灯片的内容进行翻译并讲解。内容涉及SQL完整性约束和触发器的相关知识。我们按照每页进行讲解。

6.5 SQL语句用于完整性约束

SQL-DDL 提供了多种定义完整性约束的方法，包括如下的“创建表”语句：

CREATE TABLE tablename

(

    colname datatype [DEFAULT {default value | NULL}] [Constraint {col_constr...}],

    colname datatype [DEFAULT {default value | NULL}] [Constraint {col_constr...}],

    ...

    table_constr

);

6.5.1 列约束

列约束（col_constr）：单列上的完整性约束

{ NOT NULL | CONSTRAINT constraintname { UNIQUE | PRIMARY KEY | CHECK (search_cond) | REFERENCES tablename [(colname)] [ON DELETE CASCADE] }}

列约束是在单个列上施加的限制条件。常见的列约束包括：

• NOT NULL：确保列不能包含NULL值。
• UNIQUE：确保列中的所有值都是唯一的。
• PRIMARY KEY：定义列为主键，主键值必须唯一且不能为NULL。
• CHECK：定义列值必须满足的条件。
• REFERENCES：定义外键约束，确保列值存在于另一张表的特定列中，ON DELETE CASCADE表示当引用的行被删除时，当前行也会被删除。

Create Table Student

(

    Sno char(8) primary key,

    Sname char(10),

    Gender char(2) constraint ctsGender check (Gender='M' or Gender='F'),

    Age int check (Age>=12 and Age<=50),

    Dno char(2) references Dept(Dno) on delete cascade,

    Class char(6)

);

Create Table Course

(

    Cno char(3) primary key,

    Cname char(12),

    Hours int,

    Credit float(1) constraint ctcredit check (Credit>=0.0 and Credit<=5.0),

    Tno char(3) references Teacher(Tno) on delete cascade

);

这里展示了如何在创建表时使用列约束。Student表和Course表的示例展示了各种列约束的应用：

• Sno和Cno定义为主键。
• Gender列使用CHECK约束限制只能是'M'或'F'。
• Age列使用CHECK约束限制在12到50之间。
• Dno和Tno列使用REFERENCES约束引用另一个表，并定义了ON DELETE CASCADE行为。

6.5.2 表约束

表约束（table_constr）：表级别的完整性约束

[CONSTRAINT constraintname]

{

    UNIQUE (colname {, colname...}),

    PRIMARY KEY (colname {, colname...}),

    CHECK (search_condition),

    FOREIGN KEY (colname {, colname...}) REFERENCES tablename [(colname {, colname...})] [ON DELETE CASCADE]

}

表约束是在整个表级别施加的限制条件，常见的表约束包括：

• UNIQUE：确保指定的多列组合唯一。
• PRIMARY KEY：定义多列组合为主键。
• CHECK：定义表级别的条件限制。
• FOREIGN KEY：定义多列组合为外键，并且可以引用另一张表的多列组合，ON DELETE CASCADE表示当引用的行被删除时，当前表中的相关行也会被删除。

Create Table Student

(

    Sno char(8),

    Sname char(10),

    Gender char(2) constraint ctsGender check (Gender='M' or Gender='F'),

    Age int check (Age>=12 and Age<=50),

    Dno char(2) references Dept(Dno) on delete cascade,

    Class char(6),

    primary key(Sno)

);

Create Table Course

(

    Cno char(3),

    Cname char(12),

    Hours int,

    Credit float(1) constraint ctcredit check (Credit>=0.0 and Credit<=5.0),

    Tno char(3) references Teacher(Tno) on delete cascade,

    primary key(Cno),

    constraint ctcc check (Hours*Credit = 20)

);

这个示例展示了如何在表级别使用约束条件。与列级别约束不同，表级别约束可以同时涉及多个列，例如：

• Student表的主键定义在表级别。
• Course表的CHECK约束ctcc定义了多列之间的条件，确保Hours乘以Credit等于20。

6.5.3复合约束示例

6.5.3.1 表约束（table_constr）：复合约束示例

Create Table SC

(

    Sno char(8),

    Cno char(3),

    Score float(1) constraint ctscore check (Score>=0.0 and Score<=100.0),

    primary key (Sno, Cno),

    foreign key (Sno) references Student(Sno) on delete cascade,

    foreign key (Cno) references Course(Cno) on delete cascade

);

这个示例展示了一个包含复合主键和多个外键的表SC。该表：

• 定义了主键为Sno和Cno的组合。
• 使用外键约束分别引用Student和Course表，并且定义了ON DELETE CASCADE行为，确保数据一致性。

6.5.3.2 修改和删除约束

修改和删除约束

列约束和表约束可以使用Alter table语句添加、修改和删除。

ALTER TABLE tblname

    [ADD ({colname datatype [DEFAULT {default_const | NULL}] [col_constr {col_constr...} | table_constr] {colname...}})]

    [DROP {COLUMN colname | (colname {, colname...})}]

    [ALTER COLUMN colname datatype [DEFAULT {default_const | NULL}] [NOT NULL] [col_constr {col_constr...} | table_constr]]

    [ADD CONSTRAINT constr_name]

    [DROP CONSTRAINT constr_name]

使用ALTER TABLE语句，我们可以在表创建后对表的结构进行修改，包括添加、删除和修改列约束和表约束。例如，可以添加新的列，删除现有的列，修改列的数据类型和约束条件，还可以添加和删除表级别的约束。

示例

修改和删除约束：示例

Alter Table SC

    DROP CONSTRAINT ctscore;

Alter Table SC

    ALTER COLUMN Score float(1) constraint ctscore check (Score>=1.0 and Score<=150.0);

这个示例展示了如何使用ALTER TABLE语句删除和修改约束。在第一个语句中，删除了SC表上的ctscore约束。在第二个语句中，修改了Score列的数据类型，并重新定义了ctscore约束，使其范围变为1.0到150.0。

6.6 触发器概述

6.6.1 触发器用于动态完整性约束

-- 为什么我们需要触发器

典型应用：银行ATM系统

（问题：张三的账户余额没有自动变化）

触发器是一种特殊的存储过程，它在某个事件（如插入、更新或删除）发生时自动执行。这里以银行ATM系统为例，当用户进行存取款操作时，账户余额需要自动更新。通过触发器，我们可以自动执行相关操作，确保数据的动态一致性。

• “创建表”用于定义静态完整性约束，触发器用于动态完整性约束。
• 触发器是一种由系统自动执行的语句，通常用于插入、更新和删除操作。
• 触发器是一个事务，它可以回滚。

要设计一个触发器机制，我们必须：

• 指定触发器执行的条件。
• 指定触发器执行的操作。

触发器用于在数据操作发生时自动执行特定的操作。与静态完整性约束不同，触发器可以处理更复杂的逻辑。设计触发器时，需要明确触发器触发的条件以及具体执行的操作，以确保数据的一致性和完整性。

6.6.2 触发器类型

触发器类型：

• 插入触发器
• 删除触发器
• 更新触发器

当在一个表上执行插入、删除、更新操作时，DBMS将自动执行触发器语句，并确保数据操作符合触发器的规则。

触发器根据触发的事件类型分为插入触发器、删除触发器和更新触发器。当相关操作发生时，DBMS会自动执行预定义的触发器逻辑，以确保数据操作的规则和约束得到遵守。

6.6.3 插入表和删除表

触发器用于动态完整性约束

-- 插入表和删除表

当触发器执行时，系统将在内存中自动创建插入表和/或删除表。

• 插入表是只读的，不能被更新。
• 插入表和删除表将在触发器完成时自动删除。

插入表：

• 在INSERT或UPDATE操作后存储临时记录/元组。

删除表：

• 在DELETE或UPDATE操作前存储临时记录/元组。

当触发器执行时，DBMS会创建临时的插入表和删除表，用于存储相关的数据变更。插入表存储新插入的数据，而删除表存储将被删除的数据。这些表是只读的，并且在触发器执行完毕后会自动删除。

触发器用于动态完整性约束

-- 插入表和删除表

存储在插入表和删除表中的信息

操作	插入表	删除表
INSERT	存储新插入的记录	-----
DELETE	-----	存储删除前的记录
UPDATE	存储更新操作后的记录	存储更新操作前的记录

这张表详细展示了在不同操作下插入表和删除表中存储的信息。在插入操作中，插入表存储新插入的记录；在删除操作中，删除表存储将被删除的记录；在更新操作中，插入表存储更新后的记录，而删除表存储更新前的记录。

6.6.4 创建触发器语句

触发器用于动态完整性约束

-- 创建触发器语句

CREATE TRIGGER trigger_name

ON table_name

[WITH ENCRYPTION]

FOR {DELETE, INSERT, UPDATE}

AS

T-SQL Statement

GO

WITH ENCRYPTION：用于加密触发器定义的SQL文本。

这展示了如何创建触发器。CREATE TRIGGER语句用于定义触发器，包括触发器名称、作用的表、触发的事件类型（DELETE, INSERT, UPDATE）以及触发后执行的T-SQL语句。WITH ENCRYPTION选项用于加密触发器的定义。

6.6.5 插入触发器的工作原理

-- 插入触发器工作原理

1.执行INSERT语句，插入新记录。

2.执行插入触发器，将新记录从transInfo表插入到inserted表。

3.触发器检查inserted表中的新记录，并决定回滚或执行相关操作。

这解释了插入触发器的工作流程。当执行INSERT语句时，新记录首先插入到transInfo表。然后，插入触发器将新记录复制到临时的inserted表，并对新记录进行检查，决定是否回滚或执行其他操作。

-- 插入触发器示例

[需求] 为解决银行ATM系统中的问题，当向transInfo表插入新记录时，应自动更新bank表中的currentMoney值。

[分析]

1.在transInfo表上创建INSERT触发器。

2.从插入的临时表中获取新记录的副本。

3.检查交易类型。

4.根据交易类型更新bank表中的currentMoney值。

这个示例详细解释了如何设计和实现一个插入触发器，以解决银行ATM系统中的自动更新问题。具体步骤包括创建触发器，获取新插入的数据，检查交易类型，并根据交易类型更新账户余额。

CREATE TRIGGER trig_transInfo

ON transInfo

FOR INSERT AS

DECLARE @type char(4), @transMoney MONEY

DECLARE @myCardID char(10)

SELECT @type=transType, @transMoney=transMoney, @myCardID=cardID FROM inserted

IF (@type='withdraw')

    UPDATE bank SET currentMoney=currentMoney-@transMoney WHERE cardID=@myCardID

ELSE

    UPDATE bank SET currentMoney=currentMoney+@transMoney WHERE cardID=@myCardID

GO

这个示例展示了一个完整的插入触发器代码。当在transInfo表上发生插入操作时，触发器会执行以下操作：

1.获取插入记录的交易类型、金额和卡号。

2.如果交易类型为提款，更新bank表，减少对应账户的余额。

3.如果交易类型为存款，更新bank表，增加对应账户的余额。

通过这个触发器，可以实现自动更新账户余额的功能，确保数据的一致性和准确性。

好的，继续对这些幻灯片内容进行翻译和讲解，主要讲述的是删除触发器和更新触发器的内容。我们按页进行讲解。

6.6.6 删除触发器的工作原理

-- 删除触发器

工作原理：

1.执行DELETE语句，删除一条记录。

2.执行删除触发器，将从transInfo表删除的记录插入到deleted表中。

3.触发器将检查deleted表中的已删除记录，并决定回滚或执行其他相关操作。

删除触发器用于在记录从表中删除时自动执行一些操作。例如，在删除记录时，触发器会将该记录复制到临时的deleted表中，以便后续检查或恢复操作。这可以确保在删除操作中数据的一致性和完整性。

[需求] 当从transInfo表中删除记录时，应将已删除的记录备份到bk_transInfo表中。

[分析]

1.在transInfo表上创建DELETE触发器。

2.获取已删除记录的副本。

这个示例展示了删除触发器的应用。当从transInfo表中删除记录时，触发器会将删除的记录备份到另一个表中。这是一种常见的数据保护措施，防止重要数据在删除时丢失。

CREATE TRIGGER trig_delete_transInfo

ON transInfo

FOR DELETE AS

BEGIN

    PRINT 'Start to backup data, please wait....';

    IF NOT EXISTS (SELECT * FROM sysobjects WHERE name='bk_transInfo')

        SELECT * INTO bk_transInfo FROM deleted;

    ELSE

        INSERT INTO bk_transInfo SELECT * FROM deleted;

    PRINT 'Backup data complete, the data in backup table is as following:';

    SELECT * FROM bk_transInfo;

END

GO

这个触发器代码示例展示了如何在删除操作时备份数据。触发器首先检查bk_transInfo表是否存在，如果不存在，则创建该表并插入已删除的记录。如果存在，则直接插入删除的记录。这样可以确保每次删除操作的记录都被备份。

6.6.7 更新触发器的工作原理

工作原理：

1.执行UPDATE语句。

2.执行更新触发器，将旧记录插入到deleted表中，将新记录插入到inserted表中。

3.触发器将检查旧记录和新记录，然后决定回滚或执行其他相关操作。

更新触发器在更新操作发生时自动执行。触发器会将更新前的旧记录保存到deleted表中，并将更新后的新记录保存到inserted表中。这种机制可以帮助追踪数据的变化，确保更新操作的完整性和一致性。

[需求] 追踪客户的交易，如果交易金额超过20000，则取消交易并给出通知。

[分析]

1.在bank表上创建UPDATE触发器。

2.获取旧记录的副本。

3.获取新记录的副本。

这个示例展示了更新触发器的实际应用。在银行系统中，如果客户的交易金额超过20000，触发器会自动取消交易并通知客户。这有助于防止大额错误交易，保护客户的资金安全。

CREATE TRIGGER trig_update_bank

ON bank

FOR UPDATE AS

BEGIN

    DECLARE @beforeMoney MONEY, @afterMoney MONEY;

    SELECT @beforeMoney=currentMoney FROM deleted;

    SELECT @afterMoney=currentMoney FROM inserted;

    IF ABS(@afterMoney - @beforeMoney) > 20000

    BEGIN

        PRINT 'Transaction Amount: ' + CONVERT(varchar(8), ABS(@afterMoney - @beforeMoney));

        RAISERROR ('Each transaction will not be more than 20000, Transaction failed', 16, 1);

        ROLLBACK TRANSACTION;

    END

END

GO

这个代码示例展示了一个更新触发器，当交易金额超过20000时自动取消交易。触发器首先获取更新前后的金额，如果差额超过20000，则触发错误并回滚事务。这种方式有效地控制了单笔交易的最大金额。

6.6.8 列更新触发器

更新触发器还可以用于检查某一列的值是否被更新。

[需求] 交易日期由系统创建，且设置为当前日期时间，禁止被更新。

[分析]

使用系统函数UPDATE(column_name)来测试column_name是否被更新。

列更新触发器用于确保某些特定列的值不能被更改。例如，交易日期通常由系统生成，不允许用户修改。通过列更新触发器，可以检测到任何对交易日期的修改，并采取相应的措施。

CREATE TRIGGER trig_update_transInfo

ON transInfo

FOR UPDATE AS

BEGIN

    IF UPDATE(transDate)

    BEGIN

        PRINT 'Transaction failed ....';

        RAISERROR ('Security Alert: Transaction datetime cannot be updated', 16, 1);

        ROLLBACK TRANSACTION;

    END

END

GO

这个代码示例展示了如何使用列更新触发器来防止交易日期被修改。触发器检测到transDate列被更新时，会触发错误并回滚事务，从而确保交易日期保持不变。

第七章：数据库安全

7.1 安全性的基本概念

7.1.1 问题的提出

• • 数据库的一个重要特点是数据可以共享。
  • 但是数据共享带来了安全问题。
  • 数据库系统内的数据共享不能是无条件的共享。

例子:

• 军事机密
• 国家机密
• 新产品实验数据
• 市场需求分析
• 营销策略
• 销售方案
• 客户档案
• 医疗档案
• 银行数据等

7.1.2 非法使用数据库的方法:

• 用户编写合法程序绕过数据库管理系统，通过操作系统直接访问、修改或备份数据库中的数据。
• 编写应用程序执行非授权操作。
• 通过合法多次查询数据库推断出某些秘密数据。

安全性:

• 保护数据免受恶意尝试窃取或修改的保护。

7.1.3 计算机系统安全问题的类型

1.技术安全:

• • 计算机系统使用某些安全硬件和软件来保护其系统和数据。在攻击下系统正常运行且不丢失数据。

2.管理安全:

• • 通过意外的软件或硬件故障、意外事故或不良管理造成的计算机设备和数据介质的物理损害。

3.政策法律:

• • 政府应建立关于计算机犯罪和数据安全的政策和道德标准。

7.1.4 安全控制模型

7.2 访问控制（DAC 和 MAC）

访问控制:

• 数据库安全关注数据库管理系统的访问控制机制。数据安全的最重要一点是确保只有合格的用户才能访问数据库。

访问控制机制的组成:

• 定义访问权限:
  • 在数据库系统中，确保用户只能访问其有权限访问的数据，必须为每个用户预先定义访问权限。
• 检查访问权限:
  • 对合法用户，系统根据其访问权限控制其请求，确保其只能执行合法操作。

常用方法:

• 自主访问控制 (DAC):
  • 同一用户对不同数据对象有不同的访问权限。
  • 不同用户对同一对象有不同的权限。
  • 用户还可以将权限转移给其他用户。
• 强制访问控制 (MAC):
  • 每个数据对象都被标记为某种保密级别。
  • 每个用户也被赋予一定的权限级别。
  • 只有具有合法许可证的用户才能访问对象。

7.2.1 自主访问控制（DAC）

• 数据库管理系统基于权限和用户的访问规则。

访问规则:

• 访问规则的定义包括：
  • 用户 (S)
  • 对象（授权粒度）(O)
  • 访问权限 (t)
  • 谓词 (P)
• 访问规则通常存储在数据字典中。

授权粒度:

• 数据库
• 关系/表
• 元组/行
• 属性/列

访问权限有不同级别:

• 级别 1: 选择
  • 读取数据库、表、元组、属性等。
• 级别 2: 修改
  • 插入、更新、删除。
• 级别 3: 创建
  • 创建表空间、表、索引、视图等。
• 高级权限自动包括低级权限（级别 3 > 级别 2 > 级别 1）。

示例:

• 员工（工号、姓名、生日、性别、薪水、部门号、部门负责人）有如下访问需求：
  • 系统管理员：可以访问数据库中的所有数据，拥有所有权限。
  • 保安：可以查询所有员工的“姓名”和“部门号”。
  • 雇主：可以查询自己的所有数据。
  • 部门经理：可以查询其部门内所有员工的数据。
  • 高级经理：可以查询所有数据。

访问规则:

• 员工（工号、姓名、生日、性别、薪水、部门号、部门负责人）的访问规则如下：
  • 系统管理员：读取、删除、插入、更新所有数据。
  • 保安：读取“姓名”和“部门号”。
  • 雇主：读取自己的数据。
  • 部门经理：读取部门内员工的数据。
  • 高级经理：读取所有数据。

在 SQL 中的授权命令:

• 授予所有权限或指定权限：
  • GRANT {all PRIVILEGES | privilege [,privilege...]} ON [TABLE] 表名 | 视图名 TO {public | user-id [, user-id...]} [WITH GRANT OPTION]；
  • WITH GRANT OPTION：将权限转移给他人。

实例：

• 用户A: 授予用户B在员工表上的选择权限，并允许转授。
• 用户A: 授予用户E在员工表上的选择权限，并允许转授。
• 用户B: 授予用户C在员工表上的选择权限。
• 用户E: 授予用户C在员工表上的选择权限。

在 SQL 中的撤销命令

REVOKE {all privileges | priv [, priv...]}

ON tablename | viewname

FROM {public | user [, user...]};

示例:

• UserA: 撤销 UserB 对 employee 表的选择权限；
• DBA → UserA → UserB → UserC；
• UserA → UserE。

7.2.2 强制访问控制 (MAC)

• 对于更高的安全性，系统根据 TDI/TCSEC 安全策略要求使用强制访问方法。
• 用户不能感知或控制 MAC。
• MAC 适用于对数据有严格分类的部门：
  • 军事部门
  • 政府部门

主体与客体:

• 在 MAC 中，所有实体分为主体和客体。
• 主体：系统中的活动实体，包括实际使用数据库管理系统的用户和用户的线程。
• 客体：系统中的被动实体，由主体控制，包括文档、基本表、索引和视图。

敏感度标签:

• 数据库管理系统为每个主体和客体分配敏感度标签。
• 几个级别：绝密、机密、秘密、公开。
• 主体的敏感度标签称为许可级别，客体的敏感度标签称为分类级别。
• 通过比较主体和客体的标签，MAC 机制确认主体是否可以访问客体。

MAC 规则:

• 当用户（或主体）登录系统时，系统要求其在访问客体时遵循以下两条规则：

1.只有当主体的许可级别大于或等于客体的分类级别时，主体才能读取客体。

2.只有当主体的许可级别小于或等于客体的分类级别时，主体才能写入客体。

MAC 实现:

• 扩展关系模式：
  • R (A1: D1, A2: D2, ..., An: Dn)
  • R (A1: D1, C1, A2: D2, C2, ..., An: Dn, Cn, TC)
  • C1, C2, Cn: 属性的敏感度级别
  • TC: 元组的敏感度级别

示例:

P#	C	Pname	C	Psalary	C	TC
Emp001	S	张三	U	10,000	S	S
Emp002	S	李四	U	8,000	S	C
Emp003	S	王五	U	4,000	C	C
Emp004	S	李六	U	2,000	C	C
Emp005	U	张四	U	1,000	S	U

7.3 视图和审计

• 视图
• 审计：
  • 使用专用审计日志记录用户在数据库中的所有操作。
  • 数据库管理员可以使用审计日志中的追踪信息找到非法访问数据的用户。
  • 审计需要大量时间和存储空间。

7.4 统计数据库安全

统计数据库的特征:

• 允许用户查询汇总信息。
• 不允许用户查询单个记录。

示例:

• “程序员的平均工资”是允许查询的。
• “程序员李强的工资”是不允许查询的。

问题:

• 如何在使用数据进行统计的同时确保个人隐私（合法查询推导出非法信息）。

示例 1: 以下两个查询是合法的

• 公司有多少女性高级程序员？
• 女性高级程序员的总工资是多少？

问题:

• 如果第一个查询的结果是1，那么第二个查询的结果就是程序员的工资。

解决方案 1:

• 每个查询相关的记录数量必须足够大（定义为N）。

示例 2: 用户A合法查询如下:

• 用户A和其他N个程序员的总工资是多少？
• 用户B和其他N个程序员的总工资是多少？

问题:

• 如果第一个结果是X，第二个是Y，因为用户A知道他的工资是Z，那么他知道用户B的工资是Y-(X-Z)。

解决方案 2:

• 两个查询中相同项目的数量不能超过M。

解决方案 3:

• 根据解决方案1和解决方案2，A想知道B的工资，他必须查询1+(N-2)/M次。
• 任意用户的查询次数不能超过1+(N-2)/M次。
• 但如果两个用户合作，这个解决方案将无效。

第八章：事务与并发控制

8.1 事务的基本概念

• 操作集合形成一个单一逻辑工作单元称为事务。
• 示例：从账户A向账户B转账$50:

1.读取(A)

2.A := A - 50

3.写入(A)

4.读取(B)

5.B := B + 50

6.写入(B)

• 需要处理的两个主要问题:
  • 各种故障，例如硬件故障和系统崩溃。
  • 多个事务的并发执行。

8.1.1 事务的必要属性

示例：从账户A向账户B转账$50:

1.读取(A)

2.A := A - 50

3.写入(A)

4.读取(B)

5.B := B + 50

6.写入(B)

1. 原子性要求:
   • 如果在步骤3之后和步骤6之前事务失败，钱将“丢失”，导致数据库状态不一致。
   • 故障可能是由于软件或硬件问题。
   • 系统应确保部分执行的事务更新不会反映在数据库中。
2. 持久性要求:
   • 一旦用户被通知事务已完成（即$50的转账已发生），事务对数据库的更新必须持久存在，即使存在软件或硬件故障。
3. 一致性要求:
   • 在上述示例中，A和B的总和不变。
   • 一般来说，一致性要求包括:
     • 显式指定的完整性约束，例如主键和外键。
     • 隐式完整性约束，例如所有账户的总和减去贷款总额必须小于现金值。
   • 当事务成功完成时，数据库必须一致。
   • 错误的事务逻辑可能导致不一致。
4. 隔离性要求:
   • 如果在步骤3和步骤6之间允许另一个事务T2访问部分更新的数据库，它将看到不一致的数据库（A和B的总和将小于预期）。
   • 隔离性可以通过串行运行事务来保证，即一个接一个地运行。

然而，并发执行多个事务具有显著的优势，这将在后面看到。

8.1.2 ACID 属性

• 事务是一个操作执行单元，访问并可能更新各种数据项。为了保持数据的完整性，数据库系统必须确保：
  • 原子性: 事务的所有操作要么全部反映在数据库中，要么全部不反映。
  • 一致性: 事务的执行保留数据库的一致性。
  • 隔离性: 虽然多个事务可以并发执行，但每个事务必须对其他并发执行的事务一无所知。中间事务结果必须对其他并发执行的事务隐藏。
  • 持久性: 事务成功完成后，其对数据库的更改必须持久存在，即使出现系统故障。

8.1.3 事务状态:

• 活动状态：初始状态；事务在执行时保持这种状态。
• 部分提交：最后一个语句执行后。
• 失败：发现无法继续正常执行后。
• 中止：事务回滚后，数据库恢复到事务开始前的状态。
  • 重新开始事务
  • 终止事务
• 提交：事务成功完成。

SQL 中的事务示例:

Begin Transaction

exec sql ...
...
exec sql ...
exec sql commit work | exec sql rollback work

End Transaction

8.2 并发控制

8.2.1 多事务的执行

1. 串行执行:
   • 一次只运行一个事务，其他事务必须等待该事务结束。
   • 不能完全共享系统资源。
2. 交叉并发:
   • 并发运行事务是将这些并发事务的并行操作交错进行。
   • 单处理器的并发性可以减少处理器空闲时间，提高系统效率。
3. 同时并发:
   • 在多处理器系统中，因为每个处理器可以运行一个事务，所以可以同时执行多个事务。
   • 完美的并发性，但受硬件限制。
   • 更复杂的并发控制机制。

8.2.2 并发执行

• 允许多个事务在系统中并发运行。优势包括：
  • 增加处理器和磁盘利用率，导致更好的事务吞吐量。例如，一个事务可以使用CPU，而另一个事务正在从磁盘读取或写入。
  • 减少事务的平均响应时间：短事务不必在长事务后面等待。
• 问题：隔离性可能不再得到保证。
  • DBMS必须提供并发控制方案。
  • 并发控制方案是DBMS性能的重要标志。
  • 并发控制方案：实现隔离性的机制。即控制并发事务之间的相互作用，防止其破坏数据库的一致性。

8.2.3 并发控制方案

• 并发控制方案的任务：
  • 正确的调度
  • 事务隔离
  • 数据库一致性
• 数据不一致的一个例子：
  • 火车票预订系统
  • 事务T2的修改覆盖了T1的修改。
• 并发操作中的数据不一致性：
  • 丢失更新
  • 不可重复读
  • 读取脏数据

8.2.3.1 丢失更新

• 如果事务T1和事务T2读取相同的数据并修改它们。由于事务T2破坏了事务T1的结果，事务T1的修改被丢失。
• 数据不一致的类型：
  • 丢失更新
  • 不可重复读
  • 脏读

8.2.3.2 不可重复读

• 事务T2在事务T1读取数据后执行更新操作，因此事务T1无法获得相同的查询结果。
• 示例：
  • T1: 读取(A)=50，读取(B)=100，总和(A+B)=150
  • T2: 读取(B)=100，B:= B*2，写入(B)=200
  • T1: 读取(A)=50，读取(B)=200，总和(A+B)=250

三种不可重复读

• 事务T1读取一些数据后，事务T2进行了以下操作：
  • 修改这些数据，因此事务T1在下一次读取时获得不同的值。
  • 删除这些数据，因此事务T1在下一次读取时找不到它们。
  • 插入一些记录，因此事务T1发现了更多的记录。
• 后两种情况也称为幻影。

8.2.3.3 脏读

• 事务T1修改一些数据并写回。然后事务T2读取相同的数据。事务T1回滚，数据恢复到原来的值。因此，事务T2读取了错误的数据。

示例：

1. T1读取(C)=100
   T1修改(C)=200
2. T2读取(C)=200
3. T1回滚
   T2读取(C)=100

并发调度的可串行化:

• 基本假设：每个事务保持数据库的一致性。
• 因此，串行执行一组事务保持数据库的一致性。
• 如果一个（可能并发的）调度与串行调度等价，则它是可串行化的。

如何确保并发调度的可串行化？——锁

8.3 锁机制

锁:

• 锁是一种控制并发访问数据项的机制。
• 锁请求由并发控制管理器处理。事务只有在请求被授予后才能进行操作。

8.3.1 基本锁类型

• 排它锁（X锁）：数据项可以被读取和写入。X锁通过lock-X指令请求。
• 共享锁（S锁）：数据项只能被读取。S锁通过lock-S指令请求。

8.3.2 基本锁相容性矩阵

• 一个事务可以在请求的锁与其他事务已持有的锁相兼容时获得该数据项的锁。
• 矩阵允许多个事务在一个数据项上持有共享锁，但如果任何事务持有排它锁，则其他事务不能持有该数据项的任何锁。
• 如果无法授予锁，请求事务必须等待所有不兼容的锁被释放后才能获得锁。

8.3.3 基于锁的协议

• 当在数据对象上设置X锁或S锁时，有一些规则：锁协议
  • 何时申请X锁和S锁
  • 锁的时间及何时释放
• 不同的锁协议在不同程度上保证并发操作的准确性。
• 常见的锁协议：三级锁协议（三级封锁协议）。

8.3.3.1 一级锁协议

• 事务T在修改数据R之前设置X锁，并在事务结束后释放锁。
  • 提交
  • 回滚
• 它可以防止丢失更新。
• 在这个协议中，如果读取数据，不需要锁。因此，它不能防止不可重复读和脏读。

示例：

1. T1读取(A)=16
2. T2读取(A)=16
3. T1修改(A)=15并写入
4. T2修改(A)=15并写入

预防丢失更新。

1. T1设置X锁并读取(A)=16
2. T2等待
3. T1修改(A)=15并提交，释放X锁
4. T2读取(A)=15

预防丢失更新

1. T1设置X锁并读取(A)=16
2. T2读取(A)=16
3. T1修改(A)=15并写入
4. T1回滚，释放X锁
5. T2读取(A)=15

产生脏读和不可重复读

8.3.3.2 二级锁协议

• 一级锁协议加上事务T在读取数据R之前设置S锁，并在读取后释放锁。
• 它可以防止丢失更新和脏读。
• 在这个协议中，因为读取后释放S锁，它不能防止不可重复读。

1. T1设置X锁并读取(C)=100
2. T1修改(C)=200并写入
3. T2读取(C)=200
4. T1回滚
5. T2读取(C)=100

预防脏读

1. T1设置S锁并读取(A)=50
2. T2等待
3. T1读取(B)=100并解锁
4. T2设置X锁并修改(B)=200并写入，提交并解锁
5. T1读取(A)=50和(B)=200，总和(A+B)=250

产生不可重复读

8.3.3.3 三级锁协议

• 一级锁协议加上事务T在读取数据R后释放锁的末尾设置S锁。
• 这个协议可以防止丢失更新、脏读和不可重复读。

1. T1设置S锁并读取(A)=50
2. T1读取(B)=100，总和(A+B)=150
3. T2设置X锁并等待
4. T1解锁S锁
5. T2修改(B)=200并写入，提交并解锁X锁
6. T1读取(A)=50和(B)=200，总和(A+B)=250

预防不可重复读

8.3.3.4 锁协议总结

• 三种协议的区别
  • 哪些操作需要申请锁
  • 哪种类型的锁
  • 何时申请和释放锁

·  一级锁协议：只对修改操作加锁，防止丢失更新，但不能防止不可重复读和脏读。

·  二级锁协议：对读取操作加共享锁，防止丢失更新和脏读，但不能防止不可重复读。

·  三级锁协议：在读取数据后设置共享锁，并在最后释放锁，防止丢失更新、脏读和不可重复读。

8.3.4 饥饿与死锁

• 不幸的是，锁定可能导致不理想的情况：
  • 死锁
  • 饥饿
• 在大多数锁协议中存在死锁的可能性。死锁是不可避免的。
• 如果并发控制管理器设计不佳，可能发生饥饿。
• 可以设计并发控制管理器以防止饥饿。

8.3.4.1 饥饿

• 当多个事务锁定同一个对象时，它们根据锁定顺序排队。
• 第一个事务在锁释放后获得锁。

示例：

1. T1锁定(R)
2. T2等待
3. T3等待
4. T1解锁(R)
5. T2锁定(R)
6. T4等待

如何防止饥饿：根据先来先服务策略。

8.3.4.2 死锁

• 解决死锁的方法：
  • 死锁预防
  • 死锁检测和解除

示例：

1. T1锁定(R1)
2. T2锁定(R2)
3. T1等待锁定(R2)
4. T2等待锁定(R1)

死锁预防:

• 如果一组事务中每个事务都在等待该组中另一个事务，则系统进入死锁状态。
• 死锁预防是消除死锁的条件。
• 死锁预防协议确保系统永远不会进入死锁状态。

一些预防策略：

• 每个事务在开始执行之前锁定其所有数据项（一次封锁法）。
• 对所有数据项施加部分排序，并要求事务按指定顺序锁定数据项（顺序封锁法）。

预声明协议（一次封锁法）:

• 每个事务必须锁定其使用的所有数据，否则无法执行。
• 预声明协议的问题：
  • 扩大锁定范围
  • 降低并发性
• 如果锁定所有使用的数据，则锁定范围必须扩大，从而降低并发性。
• 难以提前确认对象。

顺序封锁法:

• 定义锁定顺序，所有事务按此顺序锁定数据项。
• 顺序封锁法的问题：
  • 维护成本高
  • 难以实现

结论:

• 广泛用于操作系统的死锁预防策略不适用于数据库。
• 在数据库管理系统中通常使用死锁检测和解除。

死锁检测和解除:

• 允许死锁存在。
• 如何解决死锁：
  • 数据库管理系统的并发控制子系统检查系统中是否存在死锁。
  • 一旦检测到死锁，应解除死锁。

解除方法：

• 超时法
• 优先图法

超时法:

• 如果事务等待时间超过限制，则检测到死锁。

优点：

• 易于实现

缺点：

• 判断可能出错
• 如果限制过长，可能无法及时发现死锁。

优先图法:

• 使用优先图表示所有事务的等待状态：
  • 图中的顶点是事务。
  • 从T1指向T2的箭头表示T1在等待T2释放锁。
  • 如果图中存在循环，则出现死锁。

并发控制子系统定期检查优先图。如果图中存在循环，则系统中存在死锁。

选择成本最低的事务解除，并释放该事务持有的所有锁，以使其他事务继续进行

并发控制的其他问题:

• 锁的粒度
  • 逻辑单元：属性值、元组、关系、索引、数据库等。
  • 物理单元：页面（数据页或索引页）、物理记录等。
• 并发控制的可串行化
  • 两段锁协议（2PL）
  • 时间戳协议
  • 等等