PSU-COMPSC431-数据库管理系统笔记-全-

PSU COMPSC431 数据库管理系统笔记（全）

001：数据库管理系统导论 - 第1部分

在本节课中，我们将要学习数据库管理系统的基础概念，包括其定义、重要性、与传统文件系统的对比，以及三种主要的数据模型。课程将以概述开始，逐步深入核心概念。

课程概述与规则

欢迎来到数据库管理系统课程。我是本学期的讲师Yuslin，是计算机科学与工程系的博士候选人。

课程时间为每周一、三、五上午8点至8点50分。除常规课程外，期中考试将在晚间进行，项目演示也将占用额外时间。

我的办公时间为周一和周三下课后。本课程有一位学习助理（LA）Douglas Jordan，他将主要协助项目相关工作。

课程评分构成如下：

考试（总计40%）：两次期中考试各占13%，期末考试占14%。
团队项目（总计35%）：分为三个阶段，分别占5%、10%和20%。
作业（总计15%）：共六次作业，取五次最高分计入总成绩。
课堂测验（总计10%）：将进行突击测验，无故缺席者得零分。

所有作业需使用标准信纸，并在左上角装订。不接受迟交。请通过Angel系统与我或学习助理沟通。所有课程将被录制并上传至网站供复习。

什么是数据库与数据库管理系统

上一节我们介绍了课程的基本规则，本节中我们来看看数据库的核心定义。

数据库是一个存储了大量、集成化数据的仓库。它用于对现实世界中的企业或系统进行建模，主要包含两个核心组件：实体和关系。

数据库管理系统是一个专门用于存储和管理数据库的软件包。

为何需要数据库管理系统

了解了基本定义后，我们自然会问：为什么需要专门的数据库管理系统？与传统的文件系统相比，它有哪些优势？

传统文件系统是应用程序访问的独立文件集合。使用文件系统管理数据存在以下主要缺点：

数据分离与孤立：文件之间缺乏关联，难以协调具有关系的数据。
数据冗余：为建立关联而重复存储数据，浪费空间且可能导致数据不一致。
程序依赖性与文件格式不兼容：数据存储格式与特定应用程序或操作系统紧密绑定，难以迁移。

相比之下，数据库管理系统具有显著优势：

数据独立性：应用程序开发者无需了解数据在磁盘上的具体存储方式。
高效访问：通过查询快速检索数据，无需遍历所有文件。
数据完整性：通过完整性约束确保数据符合业务规则（例如，部门预算足够才能雇佣新员工）。
安全性：更容易管理不同用户的访问权限。
并发控制：安全处理多用户同时访问和修改同一数据的情况（例如，避免航班座位重复预订）。
故障恢复：能更好地从系统崩溃中恢复数据。
减少应用开发时间：提供了标准化的数据访问接口。

数据模型简介

在认识到数据库管理系统的优势后，我们需要一种方法来描述和组织数据。这就是数据模型的作用。

数据模型是一组用于描述存储数据的高级概念，它连接了底层的存储细节。主要有三种数据模型：

网络数据模型：早期的数据模型，使用链接将不同类型的实体连接起来，结构类似图。
层次数据模型：数据以树形结构组织，具有清晰的层级关系。
关系数据模型：这是本课程的重点，也是当今使用最广泛的数据模型。其核心概念是关系，即一个由行和列组成的表格。

关系数据模型初探

上一节我们提到了三种数据模型，本节我们将初步了解本课程的核心——关系数据模型。

在关系数据模型中，关系基本上可以看作是一张表。每张表有一个模式，用于定义其结构。

以下是一个学生表的模式示例：

Students(sid: string, name: string, login: string, age: integer, gpa: real)

这个模式定义了Students表包含五个属性（列）：学号（字符串）、姓名（字符串）、登录名（字符串）、年龄（整数）和平均绩点（实数）。每一行代表一个学生的记录。

需要注意的是，在数据库设计中，像“年龄”这种每年都会变化的属性，存储“出生日期”并计算年龄通常是更好的选择。

课后任务与总结

本节课中我们一起学习了数据库和数据库管理系统的基本概念，比较了其与文件系统的优劣，并初步了解了三种数据模型，特别是关系模型。

以下是本节课后的任务：

注册GitHub和Asana账户（链接在课程网站）。
组建4-5人的项目团队，并在周五前提交名单。
阅读教材第1.1至1.5节。
阅读Angel系统上发布的详细项目描述。

我们将在下节课深入探讨关系数据模型。

002：数据库管理系统导论 - 第2部分

概述

在本节课中，我们将继续学习数据库管理系统的基础知识。我们将深入探讨关系数据模型、数据库的抽象层次、查询语言以及DBMS的整体结构。这些核心概念是理解后续课程内容的基础。

关系数据模型回顾

上一节我们介绍了数据库的基本概念，本节中我们来看看关系数据模型的具体细节。

关系数据模型的核心概念是关系。一个关系可以看作是一张表。每张表都有一个模式，它定义了表的结构，包括列名和数据类型。

关系：指整个表本身。
记录：指表中的每一行。
属性：指表中的每一列。

例如，一个“学生”表的关系模式可能定义为：学生(学号, 姓名, 登录邮箱, 年龄, 平均绩点)。其中，“学生”是关系名，括号内的是属性。每一行代表一名学生的具体信息，即一条记录。

数据库的抽象层次

为了管理复杂性和实现数据独立性，数据库系统通常采用三层抽象结构。

以下是数据库的三级模式结构：

外部模式：也称为视图层。这是用户能看到的数据层次。不同用户（如学生和教师）可以看到同一个数据库的不同视图，从而隐藏他们不需要的数据。
概念模式：也称为逻辑层。它描述了整个数据库的逻辑结构，包括有哪些实体、实体有哪些属性以及实体之间的关系。在关系模型中，概念模式由一组关系模式定义。
内部模式：也称为物理层。它描述了数据在物理存储设备（如磁盘）上实际是如何存储的，例如使用何种文件组织方式、如何建立索引等。

这种分层结构带来了关键优势：数据独立性。

逻辑数据独立性：修改概念模式（如增加新的实体或关系）时，无需修改外部应用程序。
物理数据独立性：修改内部模式（如更换存储设备或索引方法）时，无需修改概念模式。

查询与事务

数据库不仅仅是存储数据，更重要的是能够查询和更新数据。

查询语言

用户向数据库提出的问题称为查询。用于表达查询的语言是查询语言。最常用的查询语言是SQL。

以下是一个SQL查询示例，用于查找学号为“53688”的学生姓名：

SELECT name FROM students WHERE sid = ‘53688’;

事务

事务是数据库系统中一个逻辑工作单元，代表用户执行的一组操作（例如，从ATM取款、预订机票）。事务必须满足ACID属性以保证数据的正确性。

以下是事务的四个核心属性：

原子性：事务中的所有操作要么全部完成，要么全部不执行。不存在中间状态。
一致性：事务执行必须使数据库从一个一致状态转变到另一个一致状态，不违反任何数据完整性约束。
隔离性：并发执行多个事务时，每个事务的执行不应受其他事务干扰。最终结果应与串行执行这些事务的结果相同。
持久性：一旦事务成功提交，它对数据库的修改就是永久性的，即使系统发生故障也不会丢失。

数据库管理系统的结构

一个典型的DBMS由多个协同工作的模块组成。

以下是DBMS的主要组件结构：

查询处理引擎：负责解析、优化和执行用户查询。
- 解析器与翻译器：理解SQL查询的语法和语义。
- 优化器：生成一个高效执行查询的计划。
- 执行引擎：负责执行查询计划。
存储管理器：负责在磁盘上存储、检索和更新数据。
- 文件与存取方法管理器：管理数据库文件（可视为关系的集合）和索引结构。
- 缓冲区管理器：负责将数据从磁盘调入内存，以及将修改写回磁盘。
- 磁盘空间管理器：管理磁盘块的分配与回收。
事务管理器：确保事务的ACID属性。
- 并发控制管理器：通过加锁等机制管理并发事务，确保隔离性。
- 恢复管理器：通过日志等手段，在系统故障后能将数据库恢复到一致状态。

总结

本节课中我们一起学习了数据库管理系统的几个核心部分。我们明确了关系数据模型中的基本术语（关系、记录、属性），理解了三级抽象层次（外部、概念、内部）及其带来的数据独立性优势。我们还介绍了用于检索数据的查询语言和保证数据可靠性的ACID事务概念。最后，我们概览了DBMS的模块化结构，包括查询处理、存储管理和事务管理三大组件。掌握这些基础知识，将为后续深入学习数据建模、SQL语言和系统实现打下坚实的基础。

003：实体关系模型（ER模型）- 第2部分

概述

在本节课中，我们将继续学习实体关系模型（ER模型）的核心概念。我们将重点探讨参与约束、弱实体、类层次结构（ISA）以及聚合等高级主题，并通过实例练习来巩固理解。

回顾与课程安排

上一节我们介绍了ER模型的基本组件和键约束。本节中，我们来看看更复杂的约束和设计概念。

首先，关于课程项目，所有小组已组建完毕。项目第一阶段要求完成概念设计，本周讲授的知识已足够支持大家开始工作。请于规定日期前提交文档并进行演示。

上周的小测验已评分完毕。成绩和评分细则已公布。

参与约束

参与约束定义了实体在关系中的参与类型。主要有两种类型：完全参与和部分参与。

完全参与：实体集中的每一个实体都必须参与到关系集中。
部分参与：实体集中的部分实体参与到关系集中。

在ER图中，我们使用粗直线连接实体和关系来表示完全参与。请注意区分参与约束（粗直线）和键约束（箭头）。

练习：添加约束

考虑以下“雇员-部门”场景，初始图无任何约束。

要求1：每位雇员必须至少为一个部门工作（at least one，下限约束）。

解决方案：在雇员到工作于关系之间画一条粗直线。

要求2：每个部门必须至少有一名雇员。

解决方案：在部门到工作于关系之间画一条粗直线。

要求3：每个部门必须由恰好一名经理管理（exactly one，即“至少一个且至多一个”）。

解决方案：在部门到管理关系之间画一条粗直线（至少一个）并添加一个箭头（至多一个）。

弱实体

有些实体的存在依赖于其他实体，它们没有自己的键属性，被称为弱实体。例如，公司数据库中雇员的家属。家属信息可以通过其雇员的标识符（键）加上家属自身的某个属性（如姓名）来唯一确定。

标识所有者：弱实体所依赖的实体。
标识关系：弱实体与其标识所有者之间的关系。

在ER图中表示弱实体：

将弱实体的矩形框改为双线矩形。
将其标识关系改为双线菱形。
弱实体必须完全参与到标识关系中（即用粗直线连接）。
构成弱实体标识的属性（称为部分键）用虚线下划线标注。

公式/规则：

弱实体 E 的标识通常形式为：(标识所有者的主键, E的部分键)
标识关系是从标识所有者到弱实体的 1:N 关系。

类层次结构（ISA）

ISA关系模拟了面向对象编程中的继承概念，允许我们定义实体集的超类和子类。

特化：自顶向下，从一个实体集中提取出具有特殊特征的子集（如从雇员中特化出小时工和合同工）。
泛化：自底向上，从多个实体集中识别共同特征，并创建一个包含这些特征的超类。

在ER图中，使用三角形连接超类和子类，三角形指向超类。

设计ISA关系时需考虑两个约束：

重叠约束：是否允许一个实体同时属于多个子类？（例如，一个雇员能否同时是小时工和合同工？）
覆盖约束：子类中的实体是否包含了超类中的所有实体？（例如，小时工和合同工的集合是否等于全体雇员？）

使用ISA的原因包括：

为特定子类添加描述性属性。
定义仅适用于特定子类的关系。

练习：电影行业ER图设计

请根据以下需求设计ER图（仅需画出实体和关系）：

电影行业中的人可以是演员或导演。
人员参与电影工作。
每部电影必须至少有一人参与工作。
电影包含角色。每个角色只属于一部电影。如果电影未上映，角色也不复存在。
每个角色必须由恰好一位演员扮演。

设计要点分析：

需求1：使用ISA层次，人为超类，演员和导演为子类。
需求2 & 3：人与电影之间存在工作于关系，并在电影端设置完全参与约束（粗直线）。
需求4：角色是弱实体，依赖于电影。角色到属于关系为完全参与，且属于关系是1:N（从电影到角色）。
需求5：角色与演员（子类）之间存在扮演关系，并带有键约束（箭头）表示每个角色由至多一位演员扮演。此处是否为完全参与需根据业务逻辑判断：若角色必须由演员扮演，则是完全参与（粗直线）；若允许角色暂未分配演员，则是部分参与（细直线）。

聚合

聚合是一种高级建模概念，用于表示关系集与关系集之间的联系。它允许我们将一个关系集本身视为一个实体，以便与其他实体或关系建立联系。

在ER图中，用虚线矩形框将需要被聚合的关系集框起来。

使用场景示例：部门与项目之间存在赞助关系。现在想记录哪位雇员监控了哪一项赞助关系。这时，可以将赞助关系集聚合为一个整体，然后与雇员建立监控关系。

概念设计决策总结

至此，我们已学完ER模型的所有核心组件。在实际进行概念设计时，通常遵循以下思考步骤：

实体 vs 属性：一个概念应建模为实体还是属性？例如，地址是作为雇员的属性，还是作为一个独立的地址实体？这取决于业务需求（如是否需要存储多个地址、地址结构是否复杂）。
实体 vs 关系：一个概念应建模为实体还是关系？有时需要将关系（特别是带有属性的关系）提升为实体。例如，为了记录雇员在部门工作的多个时间段（开始日期, 结束日期），可以将工作于关系建模为实体工作期，并让其与雇员和部门分别建立关系。
二元 vs N元关系：关系涉及多少个实体？是否需要用聚合来表示更复杂的关系？

总结

本节课我们一起深入学习了ER模型的高级概念。

我们明确了参与约束（完全/部分）的表示方法。
我们理解了弱实体依赖于标识所有者，并通过双线框和双线菱形表示。
我们掌握了类层次结构（ISA） 用于表示特化与泛化，并通过三角形符号连接。
我们接触了聚合这一高级概念，用于处理关系之间的关系。
最后，我们通过综合练习和设计决策讨论，整合了ER模型的各种组件用法。

掌握这些概念将帮助你更准确、更灵活地进行数据库概念设计。下节课我们将进一步比较和巩固这些概念的用法。

004：实体关系模型（ER模型）- 第3部分与关系模型

在本节课中，我们将继续学习实体关系模型（ER模型）的设计，并探讨如何将ER模型转换为关系模型。我们将通过具体的例子来理解如何应用关键约束、弱实体集和聚合等概念，并最终介绍关系模型的基本结构。

从ER模型到关系模型

上一节我们介绍了ER模型中的基本概念和设计原则。本节中，我们来看看如何将设计好的ER图转换为关系模型中的表结构，并处理一些复杂的设计场景。

处理“管理”关系与预算属性

考虑一个“管理”的二元关系，它连接“员工”和“部门”实体集，并带有一个关键约束：一个部门最多只能由一名经理管理。

关系模式可以表示为：
Manages(employee_id, department_id)

然而，如果我们想为经理分配一个覆盖其所有管理部门的总预算，问题就出现了。预算属性应该附加在哪里？

附加在“员工”实体上？但并非所有员工都是经理。
附加在“管理”关系上？但一位经理可能管理多个部门，预算会重复存储。

解决方案是引入一个新的实体集，例如“经理”，作为“员工”的一个子类。这样，预算属性就可以附加在“经理”实体上。这种设计在概念上更清晰，尽管在实现上，“经理”实体可能通过一个外键与“员工”实体关联。

保单与家属的弱实体设计

另一个例子涉及“员工”、“保单”和“家属”。最初的设计可能让“家属”作为“员工”的弱实体。但业务规则可能要求：

一份保单不能由两名或以上员工共同拥有。
每份保单必须由一名员工持有。
每个家属由保单号和政策共同唯一标识（即家属是“保单”的弱实体）。

为了满足这些要求，我们需要重新设计。以下是关键步骤：

建立“员工-拥有-保单”关系：在“员工”和“保单”之间建立“拥有”关系。通过设置关键约束（从保单到员工为多对一）来满足规则1。通过设置完全参与约束（保单必须参与关系）来满足规则2。
建立“保单-受益人-家属”关系：将“家属”设计为“保单”的弱实体，通过“受益人”关系连接。弱实体的标识通过粗线和identifying relationship的标注来体现。

这种设计清晰地分离了概念，并满足了所有业务规则。

三元关系与聚合

有时我们会遇到涉及三个实体集的关系，例如“供应商-供应-零件”合同，并带有“数量”属性。这可以建模为一个三元关系Supplies。

三元关系模式可以表示为：
Supplies(supplier_id, part_id, project_id, quantity)

聚合是一种抽象，允许我们将一个关系及其关联的实体集视为一个整体，以便与另一个实体集建立关系。例如，如果我们想记录“公司”对“供应商-供应-零件”这个整体合同的“监控”情况，可以使用聚合。

然而，如果我们不关心“公司”的监控信息，就可以简化设计，直接从“项目”连接到“供应”关系，而不必使用聚合。是否使用聚合取决于具体的业务需求。

ER模型设计要点回顾

在进行ER模型概念设计时，需要关注以下几点：

以下是设计时需要考虑的关键元素：

基本构建块：识别实体集、属性、关系集。
约束类型：考虑键约束（一对一、一对多、多对多）、参与约束（完全/部分）。
实体类型：区分强实体与弱实体。
特殊概念：应用继承（ISA层次结构）、聚合等高级概念。
设计决策：决定是否使用属性、实体集或关系集来表示一个概念。有时，将属性提升为实体集（如“经理”）或关系集能带来更好的设计。

电影行业数据库设计练习

让我们通过一个电影行业数据库的设计练习来应用这些概念。需求如下：

电影行业人员可以是演员或导演（ISA层次结构）。
人员在电影中工作（“工作于”关系）。
每部电影至少有一名人员参与其中（从“电影”到“工作于”关系的完全参与约束）。
电影有角色（“有”关系）。
一个角色只属于一部电影（从“角色”到“有”关系的键约束，一对一）。
如果没有电影，角色就不能存在（从“角色”到“有”关系的完全参与约束）。
一个角色必须由恰好一名演员扮演（“扮演”关系，并带有从“角色”到“扮演”的键约束和完全参与约束）。

根据这些需求，我们可以绘制出相应的ER图，其中包含了ISA层次结构、多个二元关系以及各种约束。

关系模型介绍

将ER模型转换后，我们就进入了关系模型。关系模型是大多数现代数据库系统的理论基础。

基本概念

关系模型的核心是关系，可以直观地理解为一张表。

以下是关系模型中的核心术语：

关系：对应一张表。
元组：对应表中的一行。
属性：对应表中的一列。
域：每个属性允许取值的集合。
关系模式：关系的结构描述，包括关系名和属性名列表。例如：学生(学号, 姓名, 年龄)。
关系实例：在特定时刻，关系中所包含元组的集合。

关系模式的数学表示是属性的有序n元组：R(A1, A2, ..., An)
关系实例是域笛卡尔积的一个子集：r ⊆ dom(A1) × dom(A2) × ... × dom(An)

关系的性质

关系中的元组是无序的。我们通过主键（一个或多个能唯一标识元组的属性）来区分不同的元组。在物理存储层面，数据库系统可能会使用索引等数据结构来优化访问顺序和速度，但这并不改变关系在逻辑层面的无序性。

本节课中我们一起学习了如何将复杂的ER模型设计转换为关系模型，包括处理带有属性的关系、弱实体集以及三元关系。我们还介绍了关系模型的基本概念，包括关系、元组、属性、域和模式。理解这些概念是设计高效、合理数据库模式的基础。

005：关系模型（续）与SQL编程导论 - 第1部分

概述

在本节课中，我们将首先回顾实体-关系模型的核心概念，然后正式介绍关系模型中的域约束。接着，我们将开始学习SQL编程的基础部分——数据定义语言，包括如何创建表、插入、修改和删除数据。

ER模型快速回顾

上一节我们介绍了关系模型的基础，本节我们先来快速回顾一下ER模型的核心构造。

ER模型有三个基本构造块。

以下是三个基本构造块：

实体集：用矩形表示。
关系集：用菱形表示。
属性：用椭圆形表示。

除了基本构造，我们还有表示约束的符号。

以下是关于键约束的说明：

如果两个实体集之间是一条简单的直线，表示多对多关系。
如果关系线上有一个箭头从A指向B，表示对于B中的每个实体，至多对应A中的一个实体。这构成了从A到B的一对多关系。
如果关系线两端都有箭头，则表示一对一关系。

以上情况都没有强制每个实体都必须参与关系。要表示“必须参与”的约束，我们需要使用参与约束。

以下是关于参与约束的说明：

用粗线表示“完全参与”约束。例如，如果从A到B的连线在A端是粗线，则表示A中的每个实体都至少参与一个关系到B。
可以将键约束（箭头）和参与约束（粗线）结合使用。例如，A到B的连线在A端是粗线且带有指向B的箭头，则表示A中的每个实体都恰好参与一个关系到B。

除了基本构造和约束，ER模型还有一些特殊构造。

以下是三种特殊构造：

弱实体集：用双线矩形表示。弱实体集需要通过一个双线菱形表示的联系，依赖于一个常规实体集（标识实体集）而存在。弱实体集的分辨符（部分键）用虚下划线标明。
概化/特化：用三角形和连线表示。三角形指向超类，连线连接子类。读作“B是A”，“C是A”，“D是A”。
聚合：用虚线框表示。用于将实体及其之间的联系作为一个整体，再与其他实体建立联系。

域约束

回顾完ER模型，我们接着来看关系模型中一个重要的概念——域约束。

域约束是指：出现在某一列中的值必须取自于与该列关联的域。

我们可以用以下方式形式化地定义一个满足域约束的关系模式实例：
r(R) = {t1, t2, ..., tn} 其中，每个元组 ti 满足模式 R 上定义的域约束。

例如，一个学生表实例如下：
学生 = { (SID: 5000, name: ‘Dave’, age: 19), … }

在SQL中，我们通常称这些“关系”为“表”。

SQL数据定义语言导论

本节我们将开始学习SQL编程。SQL语言中负责定义和修改数据结构的部分称为数据定义语言。

DDL是SQL中用于创建、删除或修改数据库结构（如表）的部分。与之相对的是数据操作语言，用于查询和修改表中的数据。

以下是本节课将涉及的几个基本DDL命令：

CREATE TABLE：用于创建新表。
INSERT INTO：用于向表中插入新数据。
UPDATE：用于修改表中现有的数据。
DELETE FROM：用于从表中删除数据。
ALTER TABLE：用于修改现有表的结构。
DROP TABLE：用于删除整个表（包括结构和数据）。
TRUNCATE TABLE：用于删除表中的所有数据，但保留表结构。

创建表

我们首先学习如何创建表。创建表时需要指定表名和列的定义。

创建学生表的SQL语句如下：

CREATE TABLE student (
    sid CHAR(5),
    name VARCHAR(20),
    age INT
);

执行CREATE TABLE语句后，数据库中就创建了一个具有指定列和数据类型（域约束）的空表。注意，此时我们尚未指定主键等完整性约束。

插入数据

表创建好后，我们需要向其中添加数据。使用INSERT INTO语句可以插入新的数据行。

向学生表插入一条数据的SQL语句如下：

INSERT INTO student (sid, name, age)
VALUES (‘12345’, ‘Smith’, 20);

要查询插入的数据，可以使用SELECT语句：

SELECT * FROM student;

若要一次性插入多条数据，可以在VALUES子句中包含多个元组，用逗号分隔。

更新数据

如果我们需要修改表中已有的数据，可以使用UPDATE语句。

将学生Smith的年龄增加1岁的SQL语句如下：

UPDATE student
SET age = age + 1
WHERE name = ‘Smith’;

WHERE子句用于指定要更新哪些行。在涉及多表时，为避免列名歧义，可以使用表别名（如s.age）。

删除数据

要从表中移除数据，可以使用DELETE FROM语句。

删除名为Smith的学生的SQL语句如下：

DELETE FROM student
WHERE name = ‘Smith’;

此操作会删除所有满足WHERE条件的行。

修改表结构

有时在创建表后，需要添加新的列。这时可以使用ALTER TABLE语句。

为学生表添加一个first_year列的SQL语句如下：

ALTER TABLE student
ADD first_year INT;

对于表中已存在的行，新添加的列的值将是NULL（空值）。

删除表

最后，我们学习如何删除表。有两种方式：TRUNCATE和DROP。

清空学生表所有数据但保留表结构的SQL语句如下：

TRUNCATE TABLE student;

彻底删除学生表（包括结构和数据）的SQL语句如下：

DROP TABLE student;

TRUNCATE只删除数据，表定义仍在。DROP会完全移除表，之后该表就不存在了。

完整性约束

在结束之前，我们简要提一下完整性约束。域约束就是完整性约束的一种。

完整性约束是数据库在任何时候都必须满足的条件。它们主要在模式定义时（如CREATE TABLE）被声明，并在数据更新（如INSERT， UPDATE）时由数据库管理系统强制执行，以确保数据的一致性和正确性。

总结

本节课我们一起学习了以下内容：

回顾了ER模型的三个基本构造（实体、关系、属性）、键约束、参与约束以及弱实体、概化、聚合等特殊构造。
明确了关系模型中的域约束概念。
正式开始了SQL编程的学习，重点介绍了数据定义语言的基本命令，包括CREATE TABLE， INSERT INTO， UPDATE， DELETE FROM， ALTER TABLE， TRUNCATE TABLE和DROP TABLE的使用方法。
了解了完整性约束的基本概念和作用时机。

下节课我们将继续SQL编程的学习，并完成剩余的练习。

006：SQL编程导论 - 第2部分 🗄️

在本节课中，我们将继续学习SQL编程，重点探讨键约束、外键以及如何在MySQL中实际操作这些概念。我们将通过创建表、插入数据并理解约束如何影响数据库操作来巩固知识。

概述

上一节我们介绍了SQL的基本命令和表的创建。本节中，我们将深入探讨键约束（如主键和候选键）的概念，并学习如何定义表之间的关系，特别是通过外键来实现。我们还将实际操作MySQL，创建具有关系的表，并观察约束如何防止无效数据的插入。

键约束回顾

首先，我们来回顾一下键约束的核心概念。一个关系的键是一组字段，用于唯一标识该关系中的每个元组。

超键：是一组能唯一标识元组的字段，但它可能包含不必要的额外字段。
候选键：是最小化的超键，即它的任何真子集都不能唯一标识元组。一个表可以有多个候选键。
主键：是从候选键中选出的一个，作为表的主要唯一标识符。数据库管理系统（DBMS）通常会为主键自动创建索引以提高查询效率。

选择哪个候选键作为主键，通常基于直观性（如学号SID）或索引效率。

在SQL中，我们使用以下方式指定：

候选键：UNIQUE
主键：PRIMARY KEY

例如，创建学生表时指定主键：

CREATE TABLE Students (
    SID INT PRIMARY KEY,
    Name VARCHAR(50),
    Login VARCHAR(50),
    Age INT,
    GPA FLOAT
);

连接到MySQL并创建表

在开始编程前，我们需要连接到MySQL环境并创建基础表。

以下是连接到课程服务器MySQL的基本步骤：

通过SSH连接到指定的Linux机器（如 p218.insc）。
在终端中输入命令 mysql -u [用户名] -p，然后输入密码。
成功登录后，你将进入MySQL命令行界面。

进入MySQL后，可以执行以下基本命令查看环境：

SHOW DATABASES;：显示所有可用的数据库。
USE [数据库名];：选择要使用的数据库。
SHOW TABLES;：显示当前数据库中的所有表。

假设我们使用一个空数据库，首先创建Students表。

CREATE TABLE Students (
    SID INT PRIMARY KEY,
    Name VARCHAR(50),
    Login VARCHAR(50),
    Age INT,
    GPA FLOAT
) ENGINE=InnoDB;

注意：在课程服务器上，我们需要显式指定存储引擎为InnoDB，因为它支持外键等关系特性。

创建后，可以使用DESCRIBE Students;查看表结构，确认SID是主键且不允许为NULL。

插入数据与基本查询

表创建后，我们需要向其中插入数据。

以下是向Students表插入多条记录的示例：

INSERT INTO Students VALUES
(53666, 'Jones', 'jones@cs', 18, 3.4),
(53688, 'Smith', 'smith@ee', 18, 3.2),
(53650, 'Smith', 'smith@math', 19, 3.8);

插入后，使用SELECT * FROM Students;可以查看表中的所有行。

理解与实践外键约束

现在，我们来创建第二个表Enrolled，它表示学生选课的关系。这个表将包含一个指向Students表的外键。

Enrolled表的核心是StudID字段，它引用了Students表的SID字段。这确保了只有存在于Students表中的学生才能被记录为选课。

创建Enrolled表的SQL语句如下：

CREATE TABLE Enrolled (
    StudID INT,
    CID VARCHAR(20),
    Grade CHAR(2),
    PRIMARY KEY (StudID, CID),
    FOREIGN KEY (StudID) REFERENCES Students(SID)
        ON DELETE CASCADE
        ON UPDATE NO ACTION
) ENGINE=InnoDB;

在这个定义中：

FOREIGN KEY (StudID) REFERENCES Students(SID)：声明StudID是外键，引用Students表的SID字段。
ON DELETE CASCADE：当Students表中被引用的学生记录被删除时，Enrolled表中所有引用该学生的选课记录也将被自动删除（级联删除）。
ON UPDATE NO ACTION：当Students表中的SID更新时，拒绝此更新操作（如果会导致不一致）。

然后，我们可以向Enrolled表插入数据：

INSERT INTO Enrolled VALUES (53666, 'Carnatic101', 'C'),
                            (53666, 'Reggae203', 'B'),
                            (53650, 'Topology112', 'A');

约束如何生效：错误示例

定义了主键和外键约束后，数据库会阻止破坏这些规则的操作。

以下是几个会导致错误的操作示例：

违反主键唯一性：尝试插入一个SID已存在的学生。
```
INSERT INTO Students VALUES (53666, 'NewStudent', 'new@cs', 20, 3.0);
```
结果：操作被拒绝，因为主键53666已存在。
违反外键约束：尝试在Enrolled表中插入一个StudID在Students表中不存在的选课记录。
```
INSERT INTO Enrolled VALUES (99999, 'History105', 'A');
```
结果：操作被拒绝，因为Students表中没有SID为99999的学生。

引用完整性操作选项

当被引用的主键记录被删除或更新时，对于引用它的外键记录，我们可以定义不同的处理策略。这就是ON DELETE和ON UPDATE子句的作用。

主要选项有：

NO ACTION / RESTRICT：拒绝执行会破坏引用完整性的删除或更新操作（默认或常见行为）。
CASCADE：级联操作。如果主表记录被删除，则从表所有相关记录也被删除；如果主键值被更新，则从表外键值同步更新。
SET NULL：将外键字段设置为NULL（要求该外键字段允许为NULL）。
SET DEFAULT：将外键字段设置为默认值。

在我们的例子中，ON DELETE CASCADE意味着删除一个学生时，他的所有选课记录会自动清理，保持了数据的一致性。

总结

本节课中我们一起学习了SQL编程中关于键约束的核心内容。我们回顾了超键、候选键和主键的区别，并在MySQL中实践了如何创建带有主键和外键约束的表。我们重点理解了外键如何强制表间的引用完整性，并通过实例看到了违反约束时数据库如何阻止操作。最后，我们介绍了ON DELETE和ON UPDATE子句，它们定义了当被引用数据发生变化时，数据库应采取的维护引用完整性的策略。掌握这些概念是设计健壮、一致的关系型数据库模式的基础。

课程事务提醒：

作业一将于本周五提交，请使用Letter尺寸纸张打印。
项目报告将于下周五进行演示，演示时间约为4分钟。
请确保已配置好VPN或本地MySQL环境以便完成实验。

007：SQL编程导论 - 第3部分 🗄️

在本节课中，我们将继续学习SQL编程，重点包括如何为已创建的表添加外键约束、查看表结构、理解约束行为，以及将ER模型转换为SQL模式。我们还将通过一些练习来巩固这些概念。

课程公告与回顾

上一节我们介绍了如何创建基本的表结构。本节中，我们来看看如何修改和增强这些表。

首先是一些课程相关的公告：

作业一可以提交到指定表格。
新作业已发布，两周后截止。
项目报告需同时提交纸质版和电子版至指定Dropbox。
项目演示幻灯片需在演示前夜提交，以便统一编译。

关于上节课的SQL编程内容，我们创建了两个表：students 和 enroll。在 enroll 表中，我们尚未指定任何外键约束。

修改表与添加约束

今天，我们将学习如何修改已存在的表，特别是如何添加外键约束，而无需删除并重建整个表。

查看表结构

在修改之前，了解如何查看表的详细定义很有用。

使用 DESCRIBE <表名>; 可以查看表的简单模式，但它不会显示外键信息。
使用 SHOW CREATE TABLE <表名>; 可以查看完整的表创建语句，包括引擎设置和所有约束。

更改存储引擎

在CSE的MySQL中，默认存储引擎可能不支持外键功能。我们需要确保表使用InnoDB引擎。
你可以在创建表时指定引擎，也可以在之后修改：

ALTER TABLE <表名> ENGINE = InnoDB;

添加外键约束

为已存在的表添加外键需要几个步骤。以下是核心命令：

SET foreign_key_checks = 0; -- 临时禁用外键检查
ALTER TABLE <子表名>
ADD FOREIGN KEY (<外键字段>)
REFERENCES <父表名>(<主键字段>)
ON DELETE <操作> ON UPDATE <操作>; -- 例如 CASCADE 或 NO ACTION
SET foreign_key_checks = 1; -- 重新启用外键检查

注意：直接执行 ALTER TABLE 添加外键可能会因表被锁定而失败，因此通常需要先禁用外键检查。

外键约束行为实践

现在，我们基于已添加外键的 enroll 表（引用 students 表），来理解不同操作的结果。假设我们定义了 ON DELETE CASCADE 和 ON UPDATE NO ACTION。

以下是几个需要思考的操作场景：

从 enroll 表中删除学生ID为‘53650’的元组。
- 结果：仅 enroll 表中对应的行被删除。students 表中的记录保持不变。因为约束定义的是当students表发生删除时，才对enroll表进行级联操作。
向 enroll 表插入一个 students 表中不存在的学生ID。
- 结果：操作将被拒绝，因为它违反了引用完整性约束。
在 students 表中删除学生ID为‘53666’的元组。
- 结果：由于设置了 ON DELETE CASCADE，students 表中的该行被删除，同时 enroll 表中所有引用此学生ID的行也会被自动删除。
尝试将 students 表中学生ID‘53688’更新为‘53600’。
- 结果：由于设置了 ON UPDATE NO ACTION，如果 enroll 表中有行引用了‘53688’，则此更新操作会失败并报错。

关键理解：外键约束中定义的 ON DELETE 和 ON UPDATE 规则，指的是当父表（被引用的表）发生相应操作时，对子表（引用表）采取的行动。

基础查询：选择特定字段

到目前为止，我们主要使用 SELECT * 查询所有字段。如果我们只想返回部分字段，可以明确指定列名：

SELECT name, login FROM students WHERE age > 20;

我们也可以为表设置别名，这在多表查询时非常有用，可以避免歧义并简化语句：

SELECT S.name, E.cid
FROM students S, enroll E
WHERE S.sid = E.sid AND E.grade = ‘A’;

这条查询结合了 students 和 enroll 表，返回成绩为A的学生的姓名和课程ID。

从ER图到关系模式

在作业和项目后续阶段，你需要将ER图转换为SQL模式。以下是一些基本转换规则。

映射实体集

简单的实体集可以直接映射为包含所有属性的表，并指定主键。

映射关系集

将关系集转换为表时，表的属性必须包含：

所有参与实体集的主键（作为外键）。
关系集本身的任何描述性属性。
这些外键的组合通常构成该表的超键。在确定主键时，需要考虑关系的基数约束。

以下是不同情况的示例：

多对多关系：所有参与实体的主键组合起来作为关系表的主键。
一对一或一对多关系：主键可以是“一”方实体的主键。例如，如果每个部门最多由一位员工管理，那么manages关系表的主键可以是部门ID，因为一个部门ID最多只对应一个管理关系。有时，这种关系甚至可以合并到“一”方实体表中作为外键。

处理参与约束与弱实体

全参与约束：例如“每个部门必须有一位经理”，这通常通过在应用程序逻辑或更复杂的约束中确保外键不允许NULL值来实现。
弱实体集：弱实体没有独立的主键。其对应的表必须包含：
- 所有者实体的主键（作为外键）。
- 弱实体自身的部分键。
- 主键由所有者实体的主键和弱实体的部分键共同组成。
- 外键通常设置 ON DELETE CASCADE，表示当所有者实体被删除时，弱实体也随之删除。

课堂练习

请思考如何为以下场景设计表结构（可以尝试在MySQL中实现或写下方案）：

实体：Employee (ssn, name, ...), Policy (policyid, cost, ...)
关系：Dependent 是 Employee 的弱实体（partial key: name）。Beneficiary 是一个关系，连接 Policy 和 Dependent。
考虑所有标明的键约束和参与约束。

你需要创建哪些表？主键和外键应如何设计？

总结与提醒

本节课中我们一起学习了：

如何使用 ALTER TABLE 为现有表添加外键约束。
如何利用 SHOW CREATE TABLE 查看完整表定义。
理解了 ON DELETE 和 ON UPDATE 规则的实际影响。
学习了基础的选择查询和表别名使用。
探讨了将ER模型中的实体集、关系集（包括涉及基数约束和弱实体的情况）转换为SQL关系模式的基本方法。

请确保你的MySQL环境工作正常，因为后续作业需要你编写并执行SQL语句。如有任何设置问题，请及时寻求帮助。

008：关系代数 - 第1部分

在本节课中，我们将要学习关系代数的基本概念和核心操作符。关系代数是SQL等查询语言的理论基础，它提供了一种形式化的方法来描述如何从数据库中检索和操作数据。我们将从几个基本操作符开始，并通过实例来理解它们如何工作。

回顾与课程安排

上一节课我们讨论了如何根据ER图设计来创建SQL表。本节中，我们将快速回顾一个练习，并结束第三章关于“视图”的最后概念，然后进入第四章的核心内容——关系代数。

以下是关于上节课ER图练习的要点总结：

弱实体的处理：对于像dependent这样的弱实体，其主键应由其自身部分键和其标识所有者（如policy）的主键共同组成。例如：PRIMARY KEY (name, policy_id)。
外键约束：在弱实体的表中，需要设置引用所有者表的外键，并通常使用ON DELETE CASCADE，以确保所有者被删除时，弱实体也随之删除。
合并关系：对于“一对一”或“多对一”且具有完全参与约束的关系（如图中的purchases），其属性可以直接合并到实体表中，无需创建单独的关系表。

视图：概念简介

在深入关系代数之前，我们简要介绍第三章的最后一个概念：视图。

视图是一种虚拟表，其内容由查询定义。它不存储数据，而是基于一个或多个底层表（称为基表）的查询结果。视图可以用于简化复杂查询、提供数据安全性（例如隐藏敏感列）或计算派生数据。

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

CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;

例如，创建一个只显示成绩为B的学生的视图：

CREATE VIEW B_students AS
SELECT name, student_id, course
FROM students
WHERE grade = 'B';

关系代数导论

现在，我们正式进入第四章。关系代数和关系演算是两种形式化的关系查询语言。关系代数是一种过程性语言，它指定了查询执行的详细步骤（即“如何”获取数据）。相比之下，关系演算是声明性的，它只描述所需结果的条件（即“要什么”）。本章我们将重点学习关系代数。

在关系代数中：

查询应用于关系实例（即某个时刻表中的具体数据）。
查询的结果始终是一个新的关系实例（即一张新表）。
操作由一系列操作符构成，这些操作符接受一个或两个关系实例作为输入，并产生一个新的关系实例作为输出。

我们将使用一个关于水手预订船只的数据库示例。主要涉及两个关系模式：

Sailors(sid, sname, rating, age)
Reserves(sid, bid, day)

核心操作符

选择 (Selection)

选择操作符（σ）用于从关系中水平地筛选出满足特定条件的元组（行）。它是一个一元操作符。

公式：σ_condition (R)

condition 是一个布尔表达式，可以包含属性与常量的比较，或属性与属性的比较。
结果的关系模式与输入关系R的模式完全相同。

示例：从水手表S2中选出评级（rating）大于8的水手。
σ_(rating > 8) (S2)

投影 (Projection)

投影操作符（π）用于从关系中垂直地筛选出指定的属性（列），并去除重复的元组。它也是一元操作符。

公式：π_attribute_list (R)

attribute_list 指定要保留的属性列表。
结果的关系模式仅包含列表中指定的属性。

示例：获取所有水手的姓名和评级。
π_(sname, rating) (S2)

操作符的组合

选择与投影可以组合使用，形成更复杂的查询。运算顺序遵循从内到外的原则。

示例：找出评级大于8的水手的姓名和评级。
π_(sname, rating) ( σ_(rating > 8) (S2) )
该表达式首先执行选择操作（σ），然后对结果执行投影操作（π）。

集合操作符

以下操作符要求参与运算的两个关系是“并兼容”的，即它们具有相同数量的属性，且对应的属性域相同。

并集 (Union)

并集操作符（∪）返回出现在关系R或关系S中的所有元组，并自动去重。

公式：R ∪ S

交集 (Intersection)

交集操作符（∩）返回同时出现在关系R和关系S中的所有元组。

公式：R ∩ S

差集 (Set Difference)

差集操作符（-）返回出现在关系R中但不出现在关系S中的元组。顺序很重要。

公式：R - S

笛卡尔积与综合示例

笛卡尔积 (Cross Product)

笛卡尔积（×）返回两个关系所有可能的元组组合。它不要求关系并兼容。结果关系的模式是R的所有属性后接S的所有属性。

公式：R × S
如果R有m个元组，S有n个元组，则结果有m*n个元组。需要注意属性名可能冲突的问题。

综合查询示例

让我们运用以上操作符，构建一个查询：“找出预订了BID为101的船只的所有水手的姓名”。

思路分析：

首先，我们需要所有水手的信息。水手数据可能分布在多个实例（如S1和S2）中，因此第一步是取并集：Sailors_All = S1 ∪ S2。
为了将水手信息与预订信息关联，我们需要进行笛卡尔积：Temp1 = Sailors_All × Reserves。
从上一步得到的庞大组合中，筛选出实际有效的预订记录，即水手ID匹配的记录：Temp2 = σ_(Sailors_All.sid = Reserves.sid) (Temp1)。
进一步筛选出船只ID为101的预订记录：Temp3 = σ_(bid=101) (Temp2)。
最后，从结果中投影出水手的姓名：Result = π_(sname) (Temp3)。

整合的关系代数表达式：
π_(sname) ( σ_(bid=101) ( σ_(Sailors_All.sid = Reserves.sid) ( (S1 ∪ S2) × Reserves ) ) )

为了优化，可以将对bid的选择条件提前，以减少中间结果的规模：
π_(sname) ( σ_(Sailors_All.sid = Reserves.sid ∧ bid=101) ( (S1 ∪ S2) × Reserves ) )

总结

本节课中我们一起学习了关系代数的基本框架和核心操作符。

我们了解了关系代数作为过程性查询语言的角色。
我们掌握了四个关键的基本操作符：选择（σ）、投影（π）、并集（∪）、交集（∩）、差集（-） 和 笛卡尔积（×）。
我们通过一个综合示例，演示了如何将这些操作符组合起来，以表达一个完整的查询意图。

理解这些操作符是学习更复杂的关系代数操作和后续理解SQL查询执行的基础。下节课我们将继续学习重命名、连接等高级操作符。

009：关系代数 - 第3部分 🧮

在本节课中，我们将继续学习关系代数，重点介绍重命名、连接和除法这三个重要操作符。我们将通过具体的例子和步骤，帮助你理解这些概念如何在实际查询中应用。

上一节我们介绍了关系代数的基本操作符，如选择、投影和集合运算。本节中，我们来看看如何通过重命名解决属性名冲突，以及如何使用连接和除法来组合和筛选数据。

重命名操作

当进行关系运算（特别是笛卡尔积）时，来自不同关系的属性可能具有相同的名称，这会造成混淆。重命名操作可以解决这个问题，它允许我们为关系或属性指定新的名称。

重命名操作符是 ρ。其语法为：

ρ(R, E)

或

ρ(F, E)

其中：

E 是一个关系代数表达式。
R 是结果关系的新名称。
F 是一个重命名列表，格式为 旧名1 -> 新名1, 旧名2 -> 新名2, ...，用于指定属性的新名称。

以下是重命名的一个关键作用：

解决命名冲突：例如，对 S1 和 R1 做笛卡尔积后，两个关系都有 SID 属性。通过重命名，我们可以将它们区分为 SID1 和 SID2。
简化复杂表达式：可以将冗长复杂的关系代数表达式分解为多个步骤，并为中间结果命名，使逻辑更清晰。

连接操作

连接是组合两个或多个关系中信息的最常用、最有用的操作之一。它本质上是笛卡尔积后接选择操作的简写。

连接操作符是 ⋈。连接主要有三种类型：

条件连接

条件连接根据指定的条件 C 来组合两个关系 R 和 S。它等价于先做笛卡尔积再进行选择。

R ⋈_C S = σ_C (R × S)

例如，S1 ⋈_{S1.SID < R1.SID} R1 会连接 S1 和 R1，只保留 S1 中 SID 小于 R1 中 SID 的那些元组组合。

等值连接

等值连接是条件连接的一种特例，其连接条件 C 只包含相等比较。在结果模式中，用于相等比较的属性只保留一份。

R ⋈_{R.A = S.B} S

自然连接

自然连接是等值连接的进一步特例。它不指定任何条件，而是自动比较两个关系中所有同名的属性，要求它们在对应元组上相等。结果中同名属性只出现一次。

R ⋈ S

自然连接在实际中不如条件连接和等值连接常用。

使用连接操作而非单独的笛卡尔积和选择，主要优点是更简洁，并且能避免生成巨大的中间结果表，从而提高效率。

除法操作

除法操作用于查询“与所有...”相关的情形，例如“查找预订了所有船只的船员姓名”。

给定关系 A(X, Y) 和 B(Y)，A ÷ B 的结果是包含所有 X 值的集合，这些 X 值在关系 A 中与关系 B 中每一个 Y 值都有关联。

其形式化定义为：

A ÷ B = { x | ∀ y ∈ B, (x, y) ∈ A }

换句话说，结果中的 x 值，其关联的 Y 值集合必须包含 B 中所有的 Y 值。

我们可以用已学的基本操作符来表达除法操作，其思路是找出“不合格”的 X 值，然后从所有 X 值中排除它们。以下是使用基本操作实现 A ÷ B 的步骤：

计算所有可能的 (X, Y) 组合：π_X(A) × B
找出实际在 A 中不存在的组合：(π_X(A) × B) - A
从这些不存在的组合中，提取出“不合格”的 X 值：π_X((π_X(A) × B) - A)
从 A 的所有 X 值中排除这些“不合格”的 X 值，得到最终结果：π_X(A) - π_X((π_X(A) × B) - A)

这个推导过程展示了除法如何通过更基本的投影、笛卡尔积和差集运算来实现。

课程总结

本节课中我们一起学习了关系代数中三个重要的高级操作：

重命名：用于解决属性名冲突和管理复杂表达式。
连接：高效组合多个关系信息的核心操作，包括条件连接、等值连接和自然连接。
除法：用于处理“针对所有...”类型的查询，可以通过基本操作符组合实现。

理解这些操作符是掌握关系数据库查询基础的关键。下一讲我们将可能深入更多查询优化或实际应用的内容。

010：关系代数 - 第3部分

在本节课中，我们将通过一系列查询示例，深入学习如何综合运用关系代数的各种运算符。我们将基于一个包含水手、船只和预订信息的数据库模式，逐步构建查询表达式，并探讨如何优化这些表达式。

运算符回顾

在开始查询练习之前，我们先快速回顾一下目前已学过的九个关系代数运算符。理解每个运算符的功能是构建复杂查询的基础。

以下是九个运算符及其简要说明：

选择 (Selection - σ)：这是一个一元运算符。它根据给定的条件筛选出关系中满足条件的元组（行）。输入和输出的模式（Schema）相同。
- 公式：σ_条件(关系R)
投影 (Projection - π)：这是一个一元运算符。它从关系中移除不需要的属性（列），只保留指定的列。输入和输出的模式不同。
- 公式：π_属性列表(关系R)
并集 (Union - ∪)：这是一个二元运算符。它返回两个关系中的所有元组，重复元组只出现一次。参与运算的两个关系必须并兼容（即具有相同数量的属性且对应属性域相同）。
交集 (Intersection - ∩)：这是一个二元运算符。它返回同时存在于两个关系中的元组。参与运算的两个关系必须并兼容。
差集 (Set Difference - -)：这是一个二元运算符。它返回存在于第一个关系但不存在于第二个关系中的元组。参与运算的两个关系必须并兼容。
笛卡尔积 (Cross Product / Cartesian Product - ×)：这是一个二元运算符。它返回两个关系中所有可能的元组组合。结果的度（属性数）是两个关系度之和，基数（元组数）是两个关系基数之积。参与运算的关系不需要并兼容。
- 公式：若 degree(R) = m, degree(S) = n，则 degree(R × S) = m + n；若 |R| = x, |S| = y，则 |R × S| = x * y。

连接 (Join - ⋈)：连接本质上是笛卡尔积后接选择操作的组合。我们学过三种类型：
- 条件连接 (Theta Join)：基于任意条件进行连接。
- 等值连接 (Equijoin)：条件连接的特例，连接条件为相等比较。
- 自然连接 (Natural Join)：自动比较两个关系中所有同名的属性，并只保留一份同名属性。

重命名 (Renaming - ρ)：这是一个一元运算符。用于重命名一个关系或其属性，常用于解决连接操作中可能出现的属性名冲突。
除法 (Division - ÷)：这是一个二元运算符。对于关系 R(X, Y) 和 S(Y)，R ÷ S 的结果是满足以下条件的 X 值：对于 S 中所有的 Y 值，组合 (X, Y) 都出现在 R 中。

查询实践

现在，我们将基于以下数据库模式，运用上述运算符来解决十个具体的查询问题。模式包含三个关系：

Sailors(sid, sname, rating, age)
Boats(bid, bname, color)
Reserves(sid, bid, day)

查询 1：找出预订了船只 103 的水手姓名

我们的目标是找到预订了特定船只（bid=103）的水手姓名。思路是先从预订记录中筛选出目标船只的预订，然后关联到水手信息，最后提取姓名。

以下是构建查询的步骤：

从 Reserves 关系中选出 bid 为 103 的预订记录：σ_(bid=103)(Reserves)。
将上一步的结果与 Sailors 关系进行自然连接，通过共同的 sid 属性关联水手信息：(σ_(bid=103)(Reserves)) ⋈ Sailors。
从上一步连接后的结果中，投影出我们最终需要的 sname 属性：π_(sname)( (σ_(bid=103)(Reserves)) ⋈ Sailors )。

最终表达式：

π_sname( (σ_bid=103(Reserves)) ⋈ Sailors )

查询 2：找出预订了红色船只的水手姓名

这个查询需要找到预订了红色船只的水手。我们需要先确定哪些船只是红色的，然后找到预订了这些船只的水手。

构建查询的步骤如下：

从 Boats 关系中选出颜色为红色的船只：σ_(color=‘red’)(Boats)。
将红色船只与 Reserves 关系进行自然连接（通过 bid），得到红色船只的预订记录：(σ_(color=‘red’)(Boats)) ⋈ Reserves。
将上一步的结果与 Sailors 关系进行自然连接（通过 sid），关联水手信息：((σ_(color=‘red’)(Boats)) ⋈ Reserves) ⋈ Sailors。
投影出水手的姓名：π_(sname)( ((σ_(color=‘red’)(Boats)) ⋈ Reserves) ⋈ Sailors )。

最终表达式：

π_sname( ((σ_color='red'(Boats)) ⋈ Reserves) ⋈ Sailors )

优化提示：可以在连接 Reserves 后立即对 sid 进行投影，减少中间结果的属性数量，但最终结果不变。

查询 3：找出名为“Lubber”的水手所预订船只的颜色

这个查询需要根据水手名找到其预订船只的颜色。我们需要先找到名为 Lubber 的水手，然后追踪他的预订记录，最后找到船只颜色。

构建查询的步骤如下：

从 Sailors 关系中选出名为 ‘Lubber’ 的水手：σ_(sname=‘Lubber’)(Sailors)。
将 Lubber 的水手记录与 Reserves 关系进行自然连接（通过 sid），得到他的所有预订：(σ_(sname=‘Lubber’)(Sailors)) ⋈ Reserves。
将上一步的结果与 Boats 关系进行自然连接（通过 bid），得到所预订船只的详细信息：((σ_(sname=‘Lubber’)(Sailors)) ⋈ Reserves) ⋈ Boats。
投影出船只的颜色属性：π_(color)( ((σ_(sname=‘Lubber’)(Sailors)) ⋈ Reserves) ⋈ Boats )。

最终表达式：

π_color( ((σ_sname='Lubber'(Sailors)) ⋈ Reserves) ⋈ Boats )

查询 4：找出至少预订了一艘船的水手姓名

这个查询相对简单，目标是找出所有有过预订记录的水手。我们只需要查看 Reserves 关系中出现的 sid，然后找到对应的水手即可。

构建查询的步骤如下：

将 Sailors 关系与 Reserves 关系进行自然连接。连接会自动匹配 sid，结果中只包含那些在 Reserves 中有记录的水手：Sailors ⋈ Reserves。
从上一步的结果中投影出水手的姓名。注意，由于一个水手可能有多次预订，姓名可能会重复，但关系代数集合操作会自动去重：π_(sname)(Sailors ⋈ Reserves)。

最终表达式：

π_sname( Sailors ⋈ Reserves )

查询 5：找出预订了红色或绿色船只的水手姓名

此查询有两种实现思路：一种是在选择条件中使用 OR，另一种是使用并集 UNION 操作符。

方法一：使用 `OR` 条件

从 Boats 关系中选出颜色为红色或绿色的船只：σ_(color=‘red’ OR color=‘green’)(Boats)。
将这些船只与 Reserves 连接，再与 Sailors 连接：((σ_(color=‘red’ OR color=‘green’)(Boats)) ⋈ Reserves) ⋈ Sailors。
投影出水手姓名：π_(sname)( ((σ_(color=‘red’ OR color=‘green’)(Boats)) ⋈ Reserves) ⋈ Sailors )。

方法二：使用 `UNION` 操作

分别找出红色船只和绿色船只的预订记录对应的水手ID：
- 红色部分：π_(sid)( (σ_(color=‘red’)(Boats)) ⋈ Reserves )
- 绿色部分：π_(sid)( (σ_(color=‘green’)(Boats)) ⋈ Reserves )
对两个结果取并集，得到预订了红船或绿船的所有不重复水手ID：π_(sid)( (σ_(color=‘red’)(Boats)) ⋈ Reserves ) ∪ π_(sid)( (σ_(color=‘green’)(Boats)) ⋈ Reserves )。
将这个水手ID集合与 Sailors 关系连接以获取姓名：( π_(sid)( (σ_(color=‘red’)(Boats)) ⋈ Reserves ) ∪ π_(sid)( (σ_(color=‘green’)(Boats)) ⋈ Reserves ) ) ⋈ Sailors。
投影出水手姓名：π_(sname)( (…union expression…) ⋈ Sailors )。

方法一表达式（更简洁）：

π_sname( ((σ_color='red' OR color='green'(Boats)) ⋈ Reserves) ⋈ Sailors )

查询 6：找出预订了红色和绿色船只的水手姓名

这个查询的关键在于“和”，意味着我们要找的水手必须同时预订过红色船只和绿色船只。不能简单地将查询5中的 OR 改为 AND，因为一艘船不能同时是红色和绿色。正确的方法是找到预订了红船的水手集合与预订了绿船的水手集合的交集。

构建查询的步骤如下：

分别找出预订了红色船只和绿色船只的水手ID集合：
- 预订红船的水手ID：π_(sid)( (σ_(color=‘red’)(Boats)) ⋈ Reserves )
- 预订绿船的水手ID：π_(sid)( (σ_(color=‘green’)(Boats)) ⋈ Reserves )
对这两个集合取交集，得到同时预订了红船和绿船的水手ID：π_(sid)( (σ_(color=‘red’)(Boats)) ⋈ Reserves ) ∩ π_(sid)( (σ_(color=‘green’)(Boats)) ⋈ Reserves )。
将交集结果与 Sailors 关系连接以获取姓名：( π_(sid)( (σ_(color=‘red’)(Boats)) ⋈ Reserves ) ∩ π_(sid)( (σ_(color=‘green’)(Boats)) ⋈ Reserves ) ) ⋈ Sailors。
投影出水手姓名：π_(sname)( (…intersection expression…) ⋈ Sailors )。

重要提示：在取交集前对 sid 进行投影是必要的，因为我们需要基于水手ID进行比较，而连接后的结果可能包含其他无关属性（如 bid, day, color），这些属性会导致交集结果为空。

最终表达式：

π_sname(
    ( π_sid( (σ_color='red'(Boats)) ⋈ Reserves )
      ∩
      π_sid( (σ_color='green'(Boats)) ⋈ Reserves )
    ) ⋈ Sailors
)

总结

本节课中，我们一起学习了如何将关系代数的多个运算符组合起来，以解决复杂的数据库查询问题。我们从简单的单表条件查询开始，逐步深入到需要连接多个表、处理集合运算（并、交）的查询。

核心要点包括：

自然连接 (⋈) 是组合信息的强大工具，它隐式地完成了笛卡尔积和等值选择。
使用投影 (π) 在中间步骤减少不必要的数据，可以优化查询效率（这是查询优化器的工作之一）。
并集 (∪) 用于实现“或”逻辑，而交集 (∩) 用于实现“和”逻辑，但在使用交集时必须确保比较的是关键属性集合。
重命名 (ρ) 操作符有助于使复杂的表达式更清晰，尤其在分解步骤时。

通过这六个查询示例的练习，你应该对如何用关系代数思维拆解查询问题有了更深入的理解。这些基础将帮助我们后续学习 SQL 语言，因为 SQL 查询在数据库内部常常被转换为类似的关系代数表达式进行处理。

011：关系代数与关系演算

在本节课中，我们将学习关系代数的剩余部分，包括一个重要的新运算符，并初步了解关系演算的概念。课程内容将涵盖查询实践、聚合操作以及两种关系演算的简介。

作业提醒与答疑

本周五将截止提交第二次作业。如有任何疑问，可以在今天联系我或明天联系助教。

对于SQL部分，请确保提交输出结果的截图。

回顾与引入新运算符

上一节我们完成了大部分关系代数运算符的练习。本节我们将继续完成剩余的查询，并介绍一个之前未讨论的运算符——聚合运算符。

首先，我们回顾一下上节课使用的数据库模式。

查询实践：关系代数

以下是几个查询示例，我们将使用关系代数表达式来解决它们。

查询7：查找预订了至少两艘船的船员姓名

这个查询要求我们找出预订了至少两艘不同船只的船员。以下是解决此问题的一种方法。

首先，我们需要对 Reserves 关系进行自连接，以找出同一个船员预订了不同船只的记录。
为了避免混淆，我们需要重命名连接中涉及的属性。
最后，我们通过连接 Sailors 关系来获取船员的姓名。

具体的关系代数表达式如下：

π sname (σ (R1.bid ≠ R2.bid) (ρ R1(bid1, sid, day) (Reserves) ⨝ ρ R2(bid2, sid, day) (Reserves) ⨝ Sailors))

这个表达式的核心在于通过自连接和选择条件 bid1 ≠ bid2 来确保找到的是预订了至少两艘不同船只的船员。

查询8：查找年龄超过20岁且未预订任何船只的船员ID

这个查询涉及“未做某事”的逻辑，通常使用差集运算来处理。

首先，找出所有年龄超过20岁的船员ID。
然后，找出年龄超过20岁且预订了船只的船员ID。
最后，计算两个集合的差集。

具体的关系代数表达式如下：

π sid (σ age > 20 (Sailors)) - π sid (σ age > 20 (Sailors) ⨝ Reserves)

差集运算要求两个关系具有相同的模式（即相同的属性），因此我们在两个部分都只投影了 sid 属性。

查询9：查找预订了所有船只的船员姓名

当查询中出现“所有”这个关键词时，我们通常考虑使用除法运算。

除法运算要求被除数关系有两个属性（例如X和Y），除数关系有一个属性（Y）。
在本查询中，我们关心船员和船只的对应关系。因此，我们从 Reserves 关系中投影出 sid 和 bid 作为被除数。
从 Boats 关系中投影出 bid 作为除数。
除法运算的结果是那些与所有船只ID都关联的船员ID。
最后，连接 Sailors 表以获取船员姓名。

具体的关系代数表达式如下：

π sname ((π sid, bid (Reserves)) ÷ (π bid (Boats)) ⨝ Sailors)

查询10：查找预订了所有名为“Interlake”的船只的船员姓名

这个查询与查询9类似，但除数集合是通过选择条件（船名）动态生成的。

首先，从 Boats 表中选择船名为“Interlake”的船只，并投影出 bid，作为除数。
从 Reserves 表中投影出 sid 和 bid 作为被除数。
执行除法运算，得到预订了所有名为“Interlake”船只的船员ID。
连接 Sailors 表获取姓名。

具体的关系代数表达式如下：

π sname ((π sid, bid (Reserves)) ÷ (π bid (σ bname=‘Interlake’ (Boats))) ⨝ Sailors)

与查询1（查找预订了特定船只ID的船员）不同，当“所有”指向一个可能包含多个实体的集合（如同名船只）时，必须使用除法运算。

聚合运算符 (α)

到目前为止，我们学习的运算符主要用于检索和连接数据，不涉及计算（如计数、求平均值）。聚合运算符 α 填补了这一空白。

聚合运算符有两种形式。

形式一：无分组的聚合

这种形式对整个关系或某一列进行单一计算，例如求所有船员的平均年龄。

公式为：α_{func(attr)} (R)

func：聚合函数，如 avg, max, min, sum, count。
attr：要计算聚合函数的属性。
R：输入关系。

示例：求所有船员的平均年龄

α_{avg(age)} (Sailors)

结果是一个单行单列的关系，例如 | avg_age |。

示例：求预订了蓝色船只的船员数量

α_{count(sid)} (σ color=‘blue’ (Reserves ⨝ Boats))

首先通过连接和选择找到预订蓝色船只的记录，然后对船员ID进行计数。

形式二：带分组的聚合

这种形式先按一个或多个属性对关系进行分组，然后在每个组内进行聚合计算。

公式为：α_{grouping_attr, func(attr)} (R)

grouping_attr：用于分组的属性。
func(attr)：在每个组内执行的聚合函数。
R：输入关系。

示例：按评级分组，找出每个组内的最大年龄

α_{rating, max(age)} (Sailors)

结果是一个关系，包含 rating 和 max_age 两列，行数等于不同的评级数量。

示例：按颜色分组，统计每种颜色的船只数量

α_{color, count(bid)} (Boats)

结果是一个关系，包含 color 和 count 两列，行数等于不同的颜色数量。

聚合运算的结果始终是一个关系（表）。

关系演算简介

关系演算是描述查询结果的另一种方式，它更侧重于“要什么”，而不是“怎么做”。它分为两种变体。

元组关系演算

在TRC中，变量代表元组（即行），查询形式为：{ T | P(T) }，表示所有满足公式 P 的元组 T 的集合。

基本构件（原子公式）：

T ∈ R：元组T在关系R中。
T.a θ S.b 或 T.a θ constant：比较元组属性与另一个属性或常量。

公式组合：
可以使用逻辑连接词 ∧ (AND), ∨ (OR), ¬ (NOT) 以及量词 ∃ (存在), ∀ (所有) 来组合原子公式。

示例：查找评级大于7的所有船员

{ S | S ∈ Sailors ∧ S.rating > 7 }

这里，S 是一个元组变量，遍历 Sailors 关系，公式 P(S) 是 S.rating > 7。

域关系演算

在DRC中，变量代表属性的值（域），而不是整个元组。它的表达形式与TRC类似，但变量直接对应于字段值。

本节课我们主要介绍了元组关系演算的基本概念，后续课程会进行更深入的探讨。期中考试的重点将放在关系代数上。

总结

本节课我们一起学习了关系代数的剩余部分，包括通过多个查询示例巩固了连接、选择、投影、差集和除法运算。我们重点介绍了新的聚合运算符 (α)，它用于执行计数、求平均值、最大值等计算，并分为无分组和带分组两种形式。最后，我们简要介绍了关系演算的概念，特别是元组关系演算，它作为一种非过程化的查询描述方式，通过逻辑公式来定义结果集。

请记住完成本周五截止的作业，并准备下周一的复习课。期中考试相关信息也已公布，如有冲突请及时告知。

012：关系演算 - 第二部分

在本节课中，我们将继续学习关系演算，并通过一系列查询示例来练习如何将关系代数转换为关系演算。我们将重点关注涉及多个关系、选择和投影操作的查询。

概述

上一节我们介绍了关系演算的基本概念和形式。本节中，我们将通过具体的查询例子，学习如何用关系演算来表达更复杂的查询逻辑，并与关系代数进行对比。

查询12：查找评分高于7的水手姓名和年龄

首先，我们来看一个简单的查询。在关系代数中，这个查询很容易实现。

关系代数表达式：

π_{sname, age}(σ_{rating > 7}(Sailors))

现在，我们将其转换为关系演算。关系演算的思考方式更侧重于描述结果元组应满足的条件。

关系演算表达式：

{ P | ∃ S ∈ Sailors (S.rating > 7 ∧ P.sname = S.sname ∧ P.age = S.age) }

在这个表达式中：

P 是结果元组变量。
∃ S ∈ Sailors 表示存在一个属于 Sailors 关系的元组 S。
S.rating > 7 是选择条件。
P.sname = S.sname 和 P.age = S.age 是投影条件，指定结果中的字段来自元组 S。

查询13：查找每个预订的水手姓名、船只ID和预订日期

这个查询涉及两个关系：Sailors 和 Reserves。

关系代数表达式：

π_{sname, bid, day}(Sailors ⨝ Reserves)

在关系演算中，我们需要表达两个关系的连接以及结果的投影。

关系演算表达式：

{ P | ∃ S ∈ Sailors, ∃ R ∈ Reserves (S.sid = R.sid ∧ P.sname = S.sname ∧ P.bid = R.bid ∧ P.day = R.day) }

表达式解析：

∃ S ∈ Sailors, ∃ R ∈ Reserves 引入了两个元组变量。
S.sid = R.sid 是连接条件，相当于自然连接。
后续三个等式定义了结果元组 P 的三个字段分别来自 S 和 R。

回顾查询1：查找预订了103号船只的水手姓名

我们已经用关系代数解决过这个问题，现在用关系演算来表达。

关系代数表达式：

π_{sname}(σ_{bid=103}(Sailors ⨝ Reserves))

关系演算表达式：

{ P | ∃ S ∈ Sailors, ∃ R ∈ Reserves (S.sid = R.sid ∧ R.bid = 103 ∧ P.sname = S.sname) }

这个表达式清晰地分为三部分：

S.sid = R.sid 处理连接操作。
R.bid = 103 处理选择操作。
P.sname = S.sname 处理投影操作。

回顾查询2：查找预订了红色船只的水手姓名

这个查询涉及三个关系。关系代数有两种写法，我们先看第二种（可能非最优但更直观）。

关系代数表达式（写法二）：

π_{sname}(σ_{color=‘red‘}(Boats) ⨝ Reserves ⨝ Sailors)

对应的关系演算表达式如下：

关系演算表达式：

{ P | ∃ S ∈ Sailors, ∃ R ∈ Reserves, ∃ B ∈ Boats (S.sid = R.sid ∧ R.bid = B.bid ∧ B.color = ‘red‘ ∧ P.sname = S.sname) }

表达式解析：

同时引入三个关系的元组变量。
S.sid = R.sid 和 R.bid = B.bid 处理三个关系的连接。
B.color = ‘red‘ 是选择条件。
P.sname = S.sname 是投影条件。

需要注意的是，关系演算中条件的顺序并不重要，它只是声明性地描述结果，而不指定执行过程。

查询7：查找预订了至少两艘不同船只的水手姓名

这个查询的关键是“至少两艘”。在关系代数中，我们曾通过自连接Reserves关系并比较bid来实现。

在关系演算中，我们可以通过引入Reserves关系的两个实例变量来表达“至少两个”的概念。

关系演算表达式：

{ P | ∃ S ∈ Sailors, ∃ R1 ∈ Reserves, ∃ R2 ∈ Reserves (S.sid = R1.sid ∧ S.sid = R2.sid ∧ R1.bid ≠ R2.bid ∧ P.sname = S.sname) }

表达式解析：

∃ R1 ∈ Reserves, ∃ R2 ∈ Reserves 引入了Reserves关系的两个实例。
S.sid = R1.sid 和 S.sid = R2.sid 确保两个预订都属于同一个水手。
R1.bid ≠ R2.bid 确保预订的是两艘不同的船。
P.sname = S.sname 投影出水手的姓名。

另一种方法是使用聚合函数（计数），但这通常超出了基础关系演算的范围，更接近于SQL或扩展的关系代数。

查询9：查找预订了所有船只的水手姓名

这个查询的关键词是“所有”。在关系代数中，我们使用除法运算。在关系演算中，我们使用全称量词 ∀。

关系演算表达式：

{ P | ∃ S ∈ Sailors (∀ B ∈ Boats (∃ R ∈ Reserves (S.sid = R.sid ∧ R.bid = B.bid)) ∧ P.sname = S.sname) }

表达式解析（从外向内阅读）：

结果元组 P 满足：存在一个水手 S，并且对于所有船只 B，都存在一个预订记录 R，使得该预订记录属于水手 S 且预订了船只 B。
最后，P.sname = S.sname 将水手姓名投影到结果中。

这种嵌套的量词结构（∃ ... ∀ ... ∃ ...）是处理“所有”这类查询的典型模式，它直接而声明性地表达了查询意图。

总结

本节课中我们一起学习了如何为多种类型的查询编写关系演算表达式。

我们练习了从简单选择投影到多表连接、条件比较（至少两个）以及全称量词（所有）的转换。
关系演算是一种声明式语言，它描述“结果是什么”，而不关心“如何获取”，这与过程式的关系代数形成对比。
尽管在初学时常通过映射关系代数的步骤来理解关系演算，但理解其声明式的本质至关重要。

通过对比两种语言对同一查询的表达，我们加深了对关系数据查询理论基础的理解。

013：函数依赖与阿姆斯特朗公理

概述

在本节课中，我们将学习数据库模式精化中的一个核心概念：函数依赖。我们将了解什么是函数依赖，它可能引发哪些问题，以及如何使用阿姆斯特朗公理来推导和验证函数依赖。这对于优化数据库设计、减少数据冗余至关重要。

冗余引发的问题

在深入函数依赖之前，我们首先需要理解不良的数据库设计，特别是数据冗余，可能带来的问题。以下是四种主要的问题类型：

冗余存储：相同的信息在数据库中重复存储多次，浪费存储空间。
更新异常：当重复数据的一个副本被更新，而其他副本未被同步更新时，会导致数据不一致。
插入异常：由于某些字段之间存在依赖关系，可能无法插入某些信息，除非同时插入其他信息。
删除异常：删除某些数据时，可能会意外地丢失其他重要信息之间的关联。

函数依赖简介

上一节我们了解了冗余的危害，本节中我们来看看如何识别冗余。函数依赖是一种完整性约束，它描述了关系中属性之间的决定关系。

定义：设X和Y是关系模式R的属性子集。如果对于R的每一个合法实例r中的任意两个元组t1和t2，只要t1[X] = t2[X]，就必然有t1[Y] = t2[Y]，则称在R上X函数决定Y，或称Y函数依赖于X，记作 X → Y。

示例：在一个员工(SSN，姓名，部门ID，停车位)关系中，如果部门ID可以唯一决定停车位，那么存在函数依赖：部门ID → 停车位。同时，主键SSN可以决定所有其他属性：SSN → 姓名，部门ID，停车位。

函数依赖是基于业务逻辑的语义约束，不能仅通过观察有限的数据实例来断定，而必须根据对应用的理解来确定。

函数依赖与键

函数依赖与我们在关系模型中学习的“键”的概念紧密相关。让我们回顾并关联这两个概念：

超键：一个能唯一标识元组的属性集。超键隐含了一个函数依赖：超键 → 所有其他属性。但它不一定是最小的。
候选键：最小的超键，即其任何真子集都不能成为超键。候选键也隐含了函数依赖：候选键 → 所有其他属性。
主键：被选中的那个候选键。

通过分析属性间的函数依赖，我们可以推导出关系的候选键。

阿姆斯特朗公理

给定一组已知的函数依赖，我们如何推导出所有隐含的函数依赖呢？这需要使用推理规则。阿姆斯特朗公理是一组基本且完备的推理规则。

以下是三个基本公理：

自反律：如果Y是X的子集，则 X → Y。这是显而易见的，例如 {SSN，姓名} → {姓名}。
增广律：如果 X → Y，则对于任何属性集Z，有 XZ → YZ。这类似于在等式两边同时加上相同的项。
传递律：如果 X → Y 且 Y → Z，则 X → Z。这是最常用且直观的规则，例如 SSN → 部门ID 且 部门ID → 停车位，可推出 SSN → 停车位。

附加推理规则

除了基本公理，还有一些非常实用的衍生规则，它们可以由阿姆斯特朗公理证明：

合并规则：如果 X → Y 且 X → Z，则 X → YZ。
分解规则：如果 X → YZ，则 X → Y 且 X → Z。
伪传递规则：如果 X → Y 且 WY → Z，则 WX → Z。

这些规则简化了函数依赖的推导过程。

闭包与推导示例

闭包：设F是关系模式R上的一组函数依赖。F的闭包（记作F+）是指能被F根据阿姆斯特朗公理推导出的所有函数依赖的集合。

让我们通过一个例子来练习推导。假设有一个合同模式，属性为：合同号(C)，供应商(S)，项目(J)，部门(D)，零件(P)，数量(Q)，价值(V)。已知以下函数依赖：

C是主键：C → S， J， D， P， Q， V
J， P → C
S， D → P

推导1：由 J， P → C 和 C → S， J， D， P， Q， V，根据传递律，可得 J， P → S， J， D， P， Q， V。因此，{J， P}也是一个候选键。

推导2：由 S， D → P，根据增广律，在两边添加J，得到 S， D， J → P， J。

推导3：将推导2的结果 S， D， J → P， J 与已知的 J， P → S， J， D， P， Q， V 结合。注意，虽然J在两边都出现，但不能像代数等式那样直接“消去J”。我们需要谨慎应用传递律。实际上，S， D， J 可以决定所有属性，因此它也是一个超键。

总结

本节课我们一起学习了数据库模式精化的核心工具——函数依赖。我们首先了解了数据冗余可能导致的四种问题。然后，我们正式定义了函数依赖，并将其与键的概念联系起来。为了系统地处理函数依赖，我们介绍了阿姆斯特朗公理（自反律、增广律、传递律）及其衍生规则，它们构成了推导函数依赖闭包的基础。最后，我们通过一个示例演示了如何应用这些规则进行推导。理解并运用这些概念，是进行有效的数据库规范化设计、消除冗余和异常的关键步骤。

014：阿姆斯特朗公理与范式 🗂️

在本节课中，我们将要学习如何基于已知的函数依赖推导出更多依赖，并介绍几种范式，以帮助我们设计出更优化的数据库模式。

概述

上一讲我们介绍了函数依赖的概念。本节中，我们将继续学习阿姆斯特朗公理，并了解如何利用这些公理推导新的函数依赖。接着，我们将探讨几种范式，它们能帮助我们识别和解决数据库设计中的潜在问题。

阿姆斯特朗公理回顾

上次我们讨论了函数依赖，即在一个关系模式中，某些属性可以决定其他属性。例如，邮政编码可以决定城市和地址。另一个例子是，员工的评级可以决定其小时工资。

当我们谈论函数依赖时，我们思考如何基于已识别的基本依赖推导出更多的函数依赖。这就是阿姆斯特朗公理的作用。

阿姆斯特朗公理包含三条基本公理：

自反律：如果 Y 是 X 的子集，则 X 决定 Y。公式表示为：若 Y ⊆ X，则 X → Y。
增广律：如果 X 决定 Y，那么对于任意属性集 Z，XZ 决定 YZ。公式表示为：若 X → Y，则 XZ → YZ。
传递律：如果 X 决定 Y，且 Y 决定 Z，那么 X 决定 Z。公式表示为：若 X → Y 且 Y → Z，则 X → Z。

这三条公理是完备且可靠的。基于这三条基本公理，还可以推导出另外两条规则：合并规则和分解规则。

推导规则的证明

接下来，我们证明由基本公理推导出的合并规则。合并规则表述为：如果 X 决定 Y，且 X 决定 Z，那么 X 决定 YZ。

我们的目标是证明：若 X → Y 且 X → Z，则 X → YZ。

以下是证明步骤：

已知 X → Y。
根据增广律，在两边同时增加 X，得到 XX → XY。由于 XX 即 X，所以有 X → XY。
已知 X → Z。
根据增广律，在两边同时增加 Y，得到 XY → YZ。
现在我们有 X → XY 和 XY → YZ。
根据传递律，可得 X → YZ。

至此，合并规则得证。分解规则的证明可以课后自行尝试。

函数依赖闭包与属性闭包

我们学习阿姆斯特朗公理，是为了基于给定的函数依赖集 F，推导出所有可能的函数依赖。这个推导出的完整集合称为 F 的闭包，记作 F+。

然而，对于一个包含多个属性的模式，F+ 的大小可能是指数级的，直接计算所有依赖非常低效。

因此，我们通常采用一种更高效的方法：计算属性闭包。给定属性集 X 和函数依赖集 F，X 的属性闭包（记作 X+）是所有能被 X 决定的属性 A 的集合。

要判断一个函数依赖 X → Y 是否在 F+ 中，我们只需计算 X+，然后检查 Y 是否是 X+ 的子集。如果是，则 X → Y 成立。

以下是计算属性闭包的线性算法：

初始化结果集 closure = X。
循环检查 F 中的每一个函数依赖 U → V：
- 如果 U ⊆ closure，则将 V 加入 closure（即 closure = closure ∪ V）。
重复步骤 2，直到 closure 不再变化为止。此时的 closure 就是 X+。

让我们通过一个例子来理解这个算法。

属性闭包计算示例

假设我们有函数依赖集 F = {A → B, B → C, CD → E}。我们想判断 A → E 是否成立。

这等价于检查 E 是否在 A+ 中。以下是计算 A+ 的步骤：

初始 closure = {A}。
检查 F：
- A → B：左边 {A} ⊆ closure，将 B 加入 closure。closure = {A, B}。
- B → C：左边 {B} ⊆ closure，将 C 加入 closure。closure = {A, B, C}。
- CD → E：左边 {C, D} 不是 closure 的子集，跳过。
再次检查 F（closure 已更新）：
- A → B, B → C 条件依然满足，但 closure 已包含 B 和 C，无变化。
- CD → E：左边 {C, D} 仍不是 closure 的子集。
closure 不再变化，因此 A+ = {A, B, C}。
由于 E 不在 A+ 中，所以 A → E 不成立。

范式简介

学习了如何推导和验证函数依赖后，我们来看看如何将这些概念应用到数据库设计中，特别是你们的项目里。为了评估和优化设计，我们引入范式的概念。

范式定义了关系模式应满足的条件。如果一个关系满足某个范式的所有要求，就能避免某些特定类型的问题。我们将讨论四种范式，它们限制性依次增强：

第一范式
第二范式
第三范式
BC范式（Boyce-Codd Normal Form）

如果一个关系满足 BC 范式，那么它必然满足前三种范式。我们先从最简单的开始。

第一范式

一个关系模式 R 满足第一范式，当且仅当每个属性域都只包含原子值。

所谓原子值，是指不可再分的最小数据单元。例如，一个单元格里不能存储一个列表或集合。在传统关系型数据库中，这通常是一个隐含要求。

判断以下哪个表满足第一范式：

表1: (SSN: 123, Name: ‘Alice’, Phone: ‘555-0100’) ✅
表2: (SSN: 456, Name: ‘Bob’, Phone: [‘555-0201’, ‘555-0202’]) ❌

表2的“Phone”字段包含了列表，因此不满足第一范式。在你们的项目中，应该已经避免了这种情况。

第二范式

在定义第二范式前，需要理解两个概念：

完全函数依赖：在函数依赖 X → Y 中，如果移除 X 中的任何一个属性都会导致该依赖不再成立，则称 Y 完全函数依赖于 X。
部分函数依赖：如果移除 X 中的某个属性后，X → Y 仍然成立，则称 Y 部分函数依赖于 X。

一个关系模式 R 满足第二范式，当且仅当每一个非主属性都完全函数依赖于 R 的每一个候选键。

不满足 2NF 的模式可以通过分解来规范化。

让我们看一个例子。假设有一个关系模式 WorksOn(SSN, Pnumber, Hours, Ename, Plocation)，并有以下函数依赖：

{SSN, Pnumber} → Hours
SSN → Ename
Pnumber → Plocation

候选键是 {SSN, Pnumber}。我们来分析非主属性：

Hours 完全函数依赖于候选键（缺少任何一个属性都无法决定 Hours）。
Ename 部分函数依赖于候选键（仅 SSN 就能决定 Ename）。
Plocation 部分函数依赖于候选键（仅 Pnumber 就能决定 Plocation）。

由于存在部分函数依赖，该模式不满足 2NF。为了消除冗余和更新异常，我们可以将其分解为三个模式：

WorkHours(SSN, Pnumber, Hours)
Employee(SSN, Ename)
Project(Pnumber, Plocation)

第三范式

第三范式的要求比第二范式更严格。一个关系模式 R 满足第三范式，当且仅当对于其函数依赖集 F+ 中的每一个非平凡函数依赖 X → A，至少满足以下条件之一：

X 是 R 的超键（即 X 包含候选键）。
A 是 R 的某个候选键的一部分（即 A 是主属性）。

简单来说，3NF 旨在消除非主属性对非超键的传递依赖。在项目第二阶段，你们需要将模式至少规范化到第三范式。

如果违反了 3NF，通常表现为两种问题：

部分依赖：这其实也是 2NF 要解决的问题。
传递依赖：例如，存在 Key → X 和 X → A，其中 X 不是超键，A 也不是主属性。

总结

本节课中我们一起学习了：

阿姆斯特朗公理：包括自反律、增广律和传递律，它们是推导函数依赖的基础。
属性闭包：一种高效算法，用于判断某个函数依赖是否可以从给定集合中推导出来。
范式理论：
- 第一范式要求数据原子性。
- 第二范式要求消除非主属性对候选键的部分函数依赖。
- 第三范式要求消除非主属性对非超键的传递依赖。

理解这些概念有助于我们设计出更合理、更少冗余和更新异常的数据库模式。在下节课中，我们将继续探讨更严格的 BC 范式。

015：第三范式（3NF）与期中考试1复习 📚

在本节课中，我们将要学习第三范式（3NF）的完整定义，并通过实例加深理解。随后，我们将对期中考试1的内容进行回顾，分析常见错误，并解答疑问。

回顾第二范式（2NF）的疑问

上一节我们介绍了第二范式（2NF），其中存在一个容易混淆的概念。本节中，我们来澄清一下。

第二范式（2NF）的定义是：关系R属于第二范式，当且仅当R中的每一个非主属性都完全函数依赖于R的任何一个候选键。

这里“完全函数依赖”的含义是：对于函数依赖 X -> Y，如果从X中移除任意属性A都会导致 X-{A} 不再决定Y，那么Y就完全函数依赖于X。

以下是一个例子，它属于第二范式：

关系模式：(SSN, Pnumber, Hours, Ename, Pname, Plocation)
函数依赖：(SSN, Pnumber) -> Hours；Pnumber -> (Pname, Plocation)
分析：虽然Pname和Plocation仅由Pnumber决定，但Pnumber本身并不是候选键（候选键是(SSN, Pnumber)）。因此，非主属性Pname和Plocation并非部分依赖于候选键，该关系模式满足2NF。

深入学习第三范式（3NF）🔍

在理解了2NF之后，本节我们来看看更高级的第三范式（3NF）。

关系模式R及其函数依赖集F属于第三范式（3NF），当且仅当对于F闭包（F+）中的每一个函数依赖 X -> A，都至少满足以下条件之一：

平凡依赖：A是X的子集（即 A ⊆ X）。
X是超键：X包含R的一个候选键。
A是主属性：A是某个候选键的组成部分。

在项目实践中，你需要将你的数据库模式设计转化为满足3NF的形式。

以下是判断关系是否属于3NF的两个例子。

例子1：判断是否属于3NF

关系模式：Student(SID, Name, Major, Advisor)
函数依赖：SID -> (Name, Major, Advisor)；Major -> Advisor
分析：
- 对于 SID -> Name：SID是主键（超键），满足条件2。
- 对于 Major -> Advisor：Major不是超键，Advisor也不是主属性。因此，该依赖不满足3NF的三个条件中的任何一个。所以，这个关系模式不属于3NF。

例子2：分解以达到3NF

关系模式：Emp_Dept(SSN, Ename, Bdate, Address, Dnumber, Dname, Dmgr_SSN)
函数依赖：SSN -> (Ename, Bdate, Address, Dnumber)；Dnumber -> (Dname, Dmgr_SSN)
分析：存在传递依赖 SSN -> Dnumber -> (Dname, Dmgr_SSN)，导致其不属于3NF。
解决方案：将其分解为两个关系模式：
- Employee(SSN, Ename, Bdate, Address, Dnumber)
- Department(Dnumber, Dname, Dmgr_SSN)
分解后，每个关系模式都满足3NF，设计更高效。

期中考试1内容复习与答疑 📝

现在，我们来回顾一下期中考试1覆盖的核心知识点和常见问题。

函数依赖公理（Armstrong‘s Axioms）复习

以下是考试中涉及的关键公理：

自反律（Reflexivity）：如果Y是X的子集，则 X -> Y。这是最平凡的依赖。
增广律（Augmentation）：如果 X -> Y，则 XZ -> YZ。它类似于乘法运算，但注意不可逆（即不能从 XZ -> YZ 推出 X -> Y）。
传递律（Transitivity）：如果 X -> Y 且 Y -> Z，则 X -> Z。这是非常直观的。

例如，给定模式 R(A, B, C, D, E, F) 和已知函数依赖：

A -> B
BC -> D
D -> EF

我们可以推导出新的依赖：

由1 (A->B) 和2 (BC->D)，利用增广律在1中加入C得到 AC -> BC，再与2通过传递律得到 AC -> D。
由2 (BC->D) 和3 (D->EF)，通过传递律直接得到 BC -> EF。

考试成绩总体分析

本次考试总体成绩良好。平均分87分，中位数92分。大部分同学得分在90分以上。如果你的总分低于70分，建议联系我进行沟通。

各题目详解与常见错误

以下是各部分的详细解析：

第一部分：基础概念选择题
这部分大家完成得非常好。需要留意的点包括：

一个关系可以有多个候选键，但只能有一个主键。
外模式允许为单个用户或用户组定制数据访问视图。

第二部分：简答题

ACID属性：只需描述清楚原子性、一致性、隔离性、持久性的含义即可得分。
关系代数运算符分类：
- 一元运算符：选择（σ）和投影（π）。
- 要求关系并兼容的运算符：并（∪）、交（∩）、差（-）。注意连接（⋈）和笛卡尔积（×）并不要求并兼容。

第三部分：ER图设计
常见错误集中在以下方面：

“每次授课都必须记录”：这要求将“学期”或“授课”建模为实体，而非仅仅是“课程”的一个多值属性。
基数约束混淆：注意区分“至少一个”（粗线）和“至多一个”（箭头）。例如，“每位教授恰好教一门课”应使用从“教授”到“教”的箭头（至多一个）和粗线（至少一个）组合表示。
弱实体的表示：弱实体需用双线矩形表示，其与标识实体间的联系用双线菱形表示。在将ER图转为关系模式时，弱实体的主键由其部分键和标识实体的主键共同构成，外键约束应设置为ON DELETE CASCADE。

第四部分：SQL表定义与约束
关键点：

当将“管理”联系（1:1）并入“部门”表时，经理SSN字段必须声明为NOT NULL，以体现“每个部门必须有一个经理”的约束。
外键引用应明确，例如 FOREIGN KEY (Mgr_SSN) REFERENCES Employee(SSN) ON DELETE NO ACTION。

第五部分：关系代数查询
这是失分较多的部分，常见错误如下：

查询水手姓名：π_name(σ_{bid=104}(Reserves) ⋈ Sailors)。大部分同学正确。
查询同时预订了红船和绿船的水手SID：不能直接写 σ_{color=‘red’ AND color=‘green’}(Boats)，因为不存在同时有两种颜色的船。正确方法是分别选出预订红船和绿船的SID，再取交集：π_sid(σ_{color=‘red’}(Boats) ⋈ Reserves) ∩ π_sid(σ_{color=‘green’}(Boats) ⋈ Reserves)。
查询至少预订了两艘船的水手姓名：常见错误是在对Reserves表做自连接时，没有对其中一个实例进行重命名，导致比较条件 bid ≠ bid 无意义。正确写法应类似：π_name(Sailors ⋈ (ρ_{R1}(Reserves) ⋈_{R1.sid = R2.sid AND R1.bid ≠ R2.bid} ρ_{R2}(Reserves)))。
查询预订了所有船的水手姓名：这是除法运算的典型应用。不能直接写 Reserves ÷ Boats，因为模式不匹配。需要先对Reserves和Boats做适当的投影：π_{sid, bid}(Reserves) ÷ π_{bid}(Boats)。
按所预订船只的颜色分组，求水手的平均评分：这里需要使用分组聚合运算符（ℱ）。例如：ℱ_{color; AVG(rating)}((Reserves ⋈ Boats) ⋈ Sailors)。或者，也可以分别查询每种颜色的平均评分，再将结果合并。

本节课中我们一起学习了第三范式（3NF）的严格定义和判断方法，并通过分解实例掌握了规范化过程。随后，我们系统复习了期中考试1的核心内容，特别是函数依赖公理、ER图设计、SQL约束以及关系代数查询中的易错点。请妥善保管好期中考试的备忘单，期末考仍可使用。对于试卷批改有任何疑问，欢迎随时提出。

016：BCNF范式与分解 📚

在本节课中，我们将要学习数据库设计中的最后一个重要范式——BCNF范式，并探讨如何通过“分解”关系模式来达到范式要求。我们还将学习两种关键的分解属性：无损连接和依赖保持。

回顾已学范式

上一节我们介绍了第一、第二和第三范式。本节中，我们来看看这些范式的核心概念和修正方法。

第一范式 (1NF)

第一范式要求所有属性都是原子的，即属性值不可再分。

测试方法：检查是否存在非原子属性（如列表）。
修正方法：为每个非原子属性创建新的关系。

第二范式 (2NF)

第二范式在1NF基础上，消除了非主属性对候选键的“部分函数依赖”。

核心概念：完全函数依赖 与 部分函数依赖。
修正方法：通过分解，为每个部分依赖创建新的关系，使其变为完全依赖。

第三范式 (3NF)

第三范式在2NF基础上，进一步消除了非主属性之间的传递依赖。

测试方法：检查是否存在非键属性被另一个非键属性函数确定的情况。
修正方法：通过分解，创建一个包含该非键属性的新关系。

这些范式的要求是逐级严格的。我们今天要学习的BCNF范式是其中限制最严格的一种。

BCNF范式 (Boyce-Codd Normal Form) 🔑

BCNF范式比第三范式更为严格。一个关系模式R及其函数依赖集F满足BCNF，当且仅当对于F闭包中的每一个函数依赖 X → A，都满足以下条件之一：

A ∈ X （这是一个平凡依赖）。
X 是 R 的一个超键（即X包含R的某个候选键）。

与第三范式的定义相比，BCNF不允许第三范式中“A是候选键的一部分”这种例外情况。因此，BCNF是更严格的。

直观理解：在BCNF中，每一个非平凡的函数依赖，其决定因素（左边）都必须是一个超键。这意味着每个数据块都可以看作是由一个键唯一标识的实体或关系。

BCNF示例分析

考虑一个关系实例，其中存在函数依赖 X → A，且X不是超键。这会导致数据冗余。例如，如果两行数据具有相同的X值，那么根据函数依赖，它们的A值也必须相同，这就造成了冗余存储。

为了满足BCNF，我们需要通过分解来消除这种由非超键决定因素引起的冗余。

关系分解

当我们有一个庞大的、不符合范式要求的关系时，可以通过“分解”将其拆分成多个较小的关系，以达到目标范式。

然而，并非所有的分解都是好的。我们需要确保分解满足两个重要性质：

无损连接：能够从分解后的关系通过自然连接完全恢复出原始关系，不丢失也不增加信息。
依赖保持：分解后，所有原有的函数依赖约束仍然能够被有效地检查。

下面我们分别探讨这两种性质。

无损连接分解

上一节我们介绍了分解的概念，本节中我们来看看如何确保分解是无损的。

定义：将关系R分解为属性集分别为X和Y的两个关系模式，如果对于满足函数依赖集F的每一个合法关系实例r，都有 r = π_X(r) ⋈ π_Y(r)，则该分解是关于F的无损连接分解。

简单来说：将原表按列拆分成两个子表后，再将这两个子表进行自然连接，结果必须和原表一模一样。

无损连接的快速判定定理

有一个更简便的方法来检验分解R为R1和R2是否为无损连接分解：

计算 R1 ∩ R2（两个属性集的交集）。
检查交集 R1 ∩ R2 是否是 R1 或 R2 的超键（即在F闭包中，R1 ∩ R2 → R1 或 R1 ∩ R2 → R2 成立）。

如果上述条件满足，则该分解是无损连接的。

无损连接的性质

以下是关于无损连接分解的两个重要观察：

如果函数依赖 X → Y 在R上成立，且 X ∩ Y = ∅，那么将R分解为 (R - Y) 和 (X ∪ Y) 是无损连接分解。
无损连接性具有传递性。如果将R无损分解为R1和R2，再将R1无损分解为R11和R12，那么从R到{R11, R12, R2}的分解也是无损的。

依赖保持分解

确保能够恢复数据后，我们还需要确保分解后的模式依然能方便地维护数据的完整性约束（即函数依赖）。

定义：将关系R分解为一组关系模式{R1, R2, ..., Rn}，如果函数依赖集F的闭包 F+ 等价于所有在子模式Ri上成立的函数依赖的投影的并集，则该分解是依赖保持的。

简单来说：原有的所有函数依赖，要么直接保留在某个子表中，要么可以从几个子表中的依赖逻辑推导出来。这样，我们就不必为了检查某个依赖而频繁地连接多个表。

函数依赖的投影

如何计算函数依赖集F在属性集Z上的投影（记为 π_Z(F)）？

取F闭包 F+ 中的所有函数依赖。
只保留那些左右两边的属性都包含在Z中的依赖。
这些保留下来的依赖的集合就是π_Z(F)。

依赖保持的示例

考虑一个合同关系，具有函数依赖：{C → JP, JP → C, SD → P}，其中C是候选键。

依赖 SD → P 违反了BCNF（因为SD不是超键）。
如果我们将其分解为 (CSDJ) 和 (SDP)，可以解决BCNF违例。
但是，原依赖 JP → C 没有被保留在任何子关系中。要验证该依赖，必须对两个子表进行连接操作，这很不高效。因此，这个分解不是依赖保持的。

一个更好的、依赖保持的分解可能是 (CJP) 和 (CSD)，这样JP → C和C → JP保留在第一个子表，SD → P？等等，这里需要重新设计以满足BCNF和依赖保持，这通常需要权衡。

总结

本节课中我们一起学习了数据库设计中的关键进阶内容：

BCNF范式：比3NF更严格的范式，要求所有非平凡函数依赖的决定因素都是超键。
关系分解：通过拆分大表来满足范式要求的主要手段。
无损连接分解：确保分解不会导致信息丢失或扭曲，可以通过子模式交集是否为键来快速检验。
依赖保持分解：确保分解后，原有的所有数据完整性约束（函数依赖）仍然能够被有效地检查和维护。

在实际数据库设计中，我们通常力求达到BCNF，但有时为了保持依赖性或出于性能考虑，可能会止步于3NF。理解无损连接和依赖保持这两个概念，对于评估和设计一个良好的数据库模式至关重要。

017：分解为BCNF和3NF

在本节课中，我们将学习如何将关系模式分解为更高级别的范式，特别是BCNF和3NF。我们将重点理解两种分解的关键属性：无损连接和依赖保持。通过具体的算法和示例，我们将掌握如何检查一个分解是否满足这些属性，并学习如何执行分解。

课程安排与回顾

在开始新内容之前，我们先快速回顾一下课程安排和上节课的内容。

上节课我们介绍了无损连接分解。我们提到，有时一种分解方法可能导致无损连接的结果，但可能会丢失最初给定的一些函数依赖，即完整性约束。本节课，我们将深入探讨依赖保持分解。

依赖保持分解

本节中，我们来看看什么是依赖保持分解，以及如何判断一个分解是否保持了依赖。

首先，我们引入一个关键概念：函数依赖集在属性集上的投影。给定一个关系模式R及其函数依赖集F，将R分解为具有属性集X和Y的两个关系。F在X上的投影，记作 F_X，是指所有满足以下条件的函数依赖U → V：U和V中的属性都包含在属性集X中。

基于投影概念，依赖保持分解的定义如下：将关系模式R及其函数依赖集F分解为模式（X）和（Y），如果 (F_X ∪ F_Y)⁺ = F⁺，则该分解是依赖保持的。换句话说，如果我们取F在X和Y上投影的依赖集的并集，并计算其闭包，能得到F的所有依赖，那么这个分解就保持了依赖。

让我们通过一个例子来理解。

假设原始关系R具有属性{A, B, C}，给定的函数依赖F为：{A → B, B → C, C → A}。我们将R分解为两个关系：R1(AB) 和 R2(BC)。

以下是检查步骤：

计算F在AB上的投影 F_AB：包含A → B。
计算F在BC上的投影 F_BC：包含B → C。
取并集：{A → B, B → C}。
计算该并集的闭包：通过传递律，我们可以推导出A → C和C → A。因此，闭包为{A → B, B → C, A → C, C → A}。
比较闭包：该闭包与原始F的闭包{A → B, B → C, C → A}（通过传递律同样可推出A → C）相同。

因此，这个分解是依赖保持的。

规范化分解算法

到目前为止，我们已经学习了如何检查分解的两种属性。然而，很多时候我们需要从零开始对一个模式进行分解。为此，存在一些标准化的算法，可以确保分解达到特定的范式。

需要记住的关键点是：

将关系转换为BCNF的算法可以保证得到无损连接分解，但不一定能保证依赖保持。
总是存在一个达到3NF的依赖保持且无损连接的分解。

接下来，我们分别讨论分解到BCNF和3NF的算法。

分解为BCNF

首先，我们来看看如何将关系分解为BCNF，这是最严格的范式之一。

分解为BCNF的算法基于我们之前的观察。算法步骤如下：

假设关系R不在BCNF中。找到一个违反BCNF的函数依赖 X → A，其中A是单个属性，且X不是超键。
将R分解为两个关系：
- R1 = R - {A} （即从R中移除属性A）
- R2 = X ∪ {A}
如果R1或R2仍然不在BCNF中，则对它们重复上述步骤。

让我们看一个熟悉的例子。考虑合同关系模式 Contract (S, J, P, C, D, Q, V)，其中{S, J}是候选键。给定的函数依赖包括 SJ → P, S → P, J → S, JP → C。

首先，S → P 违反了BCNF（因为S不是超键）。根据算法：

R1 = Contract - {P} = (S, J, C, D, Q, V)
R2 = X ∪ {A} = {S} ∪ {P} = (S, P)

检查R2，键是S，它满足BCNF。检查R1，它包含函数依赖 J → S，而R1的键是{J, C, D, Q, V}，J不是超键，因此 J → S 违反了BCNF。我们需要进一步分解R1：

基于 J → S 分解R1：
- R11 = R1 - {S} = (J, C, D, Q, V)
- R12 = {J} ∪ {S} = (J, S)

现在，R11和R12都在BCNF中。然而，我们忽略了一个函数依赖 JP → C。在最终分解 (S,P), (J,S), (J,C,D,Q,V) 中，要检查 JP → C 是否成立，需要将 (J,S) 和 (J,C,D,Q,V) 连接起来，这很不方便。

一个解决方案是添加一个额外的关系 (J, P, C) 来专门检查这个依赖。虽然这引入了数据冗余（CJP信息被重复存储），但它使得依赖的强制检查变得廉价（无需连接操作），这是一种权衡。

关于BCNF分解的几点说明：

选择违反BCNF的函数依赖的顺序可能影响最终的分解结果，没有唯一正确答案。
可能不存在一个能达到BCNF的依赖保持分解。例如，模式 (S, B, D)，键为 SB，依赖为 {SB → D, D → B}。为了满足BCNF，必须基于 D → B 分解为 (S, D) 和 (D, B)，但这将丢失检查 SB → D 的能力，因此不是依赖保持的。

分解为3NF

由于BCNF可能无法保持依赖，我们接下来看看如何分解为3NF，这总能得到一个依赖保持且无损连接的分解。

分解为3NF的算法可以利用BCNF分解算法来获得无损连接分解，但为了确保依赖保持，我们需要引入最小覆盖的概念。

最小覆盖

最小覆盖是函数依赖集F的一个等价但更精简的集合G，满足以下条件：

G中每个依赖的右边都是单个属性。
G⁺ = F⁺ （即闭包相同）。
如果从G中删除任何依赖或删除任何依赖左边的属性，其闭包将改变（即无法再推导出所有原依赖）。

寻找最小覆盖的步骤：

右部化为单属性：利用分解律，将F中所有右边有多个属性的依赖拆分为多个依赖。
最小化左部：检查每个依赖，尝试移除其左边的冗余属性。如果移除后闭包不变，则该属性冗余。
删除冗余依赖：检查G中的依赖，如果某个依赖可以从其他依赖通过阿姆斯特朗公理推导出来，则删除它。

示例：给定 F = {A → B, ABCD → E, EF → G, EF → H, ACDF → EG}。

右部化为单属性：将 ACDF → EG 拆分为 ACDF → E 和 ACDF → G。
最小化左部：
- 对于 ABCD → E，由于已有 A → B，可简化为 ACD → E。
- 对于 ACDF → E，检查发现 F 是冗余的（因为 ACD → E 已存在），可简化为 ACD → E。
- 对于 ACDF → G，检查发现它可以通过 ACD → E（增广律得 ACDF → EF）和 EF → G（传递律）推导出来，因此是冗余的，可删除。
删除冗余依赖后，得到最小覆盖 G = {A → B, ACD → E, EF → G, EF → H}。

3NF合成算法

使用最小覆盖G，我们可以通过以下算法得到3NF的依赖保持且无损连接的分解：

对于G中的每一个函数依赖 X → A，创建一个关系模式 XA。
如果步骤1中创建的任何一个关系模式包含R的某个候选键，那么分解完成。否则，添加一个只包含R的任意一个候选键的关系模式。
（可选）合并具有相同左部的关系模式。

示例：再次考虑合同模式 Contract 及其函数依赖。其最小覆盖可能包含 S → P, J → S, JP → C 等。

为每个依赖创建模式：(S, P), (J, S), (J, P, C)。
检查是否包含候选键。原始键是 (S, J)，它没有单独出现在任何模式中。因此，我们需要添加一个包含该键的模式 (S, J, D, Q, V)（这里把其他不属于任何依赖右部的属性也放了进来，通常放于键所在的模式）。
最终分解可能是：(S, P), (J, S), (J, P, C), (S, J, D, Q, V)。这个分解既是3NF，也保持了所有依赖。

课程总结

本节课中，我们一起学习了数据库设计中的关键步骤：关系模式的规范化分解。

我们首先明确了无损连接和依赖保持这两个分解过程需要追求的重要属性。
我们学习了如何检查一个给定的分解是否满足依赖保持，其核心是计算函数依赖集在子模式上的投影及其闭包。
接着，我们探讨了两种主要的规范化分解算法：
- 分解为BCNF的算法能保证无损连接，但可能无法保持所有函数依赖，有时需要通过添加冗余表来检查依赖。
- 分解为3NF的算法总能产生一个既无损连接又依赖保持的分解。该算法依赖于先求出函数依赖集的最小覆盖，然后通过“合成”每个依赖来创建子模式，并确保包含原关系的候选键。
我们通过多个实例逐步演练了寻找最小覆盖和执行3NF合成算法的过程。

理解这些分解技术和它们的权衡，对于设计高效、无冗余且能维护数据完整性的数据库模式至关重要。

018：SQL - 第1部分

概述

在本节课中，我们将学习结构化查询语言的基础知识。我们将从最简单的查询开始，逐步了解如何使用 SELECT、FROM 和 WHERE 子句从数据库中检索数据。课程将使用我们熟悉的水手、船只和预订数据库模式进行演示。

基本SQL查询结构 🧱

上一节我们概述了课程内容，本节中我们来看看SQL查询的基本构成。

一个基础的SQL查询包含三个关键字：SELECT、FROM 和 WHERE。

SELECT 后面跟一个可选的 DISTINCT 关键字，然后是目标属性列表。
FROM 后面跟关系（表）列表。
WHERE 后面跟查询条件。

这里有一个容易混淆的点：在SQL中，SELECT 子句指定我们想要输出的列，这类似于关系代数中的投影操作。而真正的选择操作（即按条件筛选行）发生在 WHERE 子句中。

DISTINCT 关键字用于消除结果中的重复行。在关系代数中，我们通常假设结果集没有重复，但在实际的数据库系统（如MySQL）中，如果不使用 DISTINCT，查询结果可能会包含重复的元组。

简单查询实践 🔍

理解了基本结构后，我们通过几个例子来实践。

查询Q1：查找所有水手的姓名和年龄

以下是实现步骤：

确定涉及的表：只需要 Sailors 表。
确定要选择的属性：sname 和 age。
确定筛选条件：无。

因此，SQL语句如下：

SELECT sname, age
FROM Sailors;

执行后，会返回 Sailors 表中所有水手的姓名和年龄。

如果想消除重复的姓名（尽管在这个例子中不太可能），可以加上 DISTINCT：

SELECT DISTINCT sname
FROM Sailors;

查询Q2：查找评分高于7的所有水手

以下是实现步骤：

涉及的表：Sailors。
要选择的属性：所有属性（使用 *）。
筛选条件：rating > 7。

SQL语句如下：

SELECT *
FROM Sailors
WHERE rating > 7;

这条语句会返回 Sailors 表中所有评分高于7的水手的全部信息。

查询执行过程与多表连接 ⚙️

我们执行了简单查询，现在来看看数据库背后是如何处理这些查询的，并开始学习多表连接。

当数据库处理 SELECT ... FROM ... WHERE ... 查询时，逻辑上会按以下顺序执行：

计算 FROM 子句中所有表的笛卡尔积，生成一个巨大的中间表。
根据 WHERE 子句中的条件，对中间表进行筛选，只保留满足条件的行。
根据 SELECT 子句指定的属性列表，对结果进行投影，只保留需要的列。
如果指定了 DISTINCT，则消除最终结果中的重复行。

需要注意的是，这只是逻辑上的理解模型。实际上，数据库的查询优化器会尝试找出最高效的执行计划，而不会真的去计算完整的笛卡尔积。

查询Q3：查找预订了103号船的水手姓名

现在我们来处理一个需要连接两个表的查询。
以下是实现步骤：

涉及的表：Sailors 和 Reserves（因为需要水手姓名和预订信息）。
要选择的属性：Sailors.sname。
筛选条件：
- 预订的船ID (Reserves.bid) 等于103。
- 并且，Sailors 表中的水手ID (sid) 必须与 Reserves 表中的水手ID (sid) 相匹配，这样才能正确关联。

由于两个表都有 sid 列，为了避免歧义，我们使用范围变量（即表别名）来区分。SQL语句如下：

SELECT S.sname
FROM Sailors S, Reserves R
WHERE R.bid = 103
  AND S.sid = R.sid;

这个查询先对 Sailors (别名S) 和 Reserves (别名R) 做笛卡尔积，然后筛选出同时满足 bid=103 和 S.sid=R.sid 的行，最后投影出水手的姓名。

更复杂的多表连接 🔗

我们学会了连接两个表，现在尝试连接三个表。

查询Q4：查找预订了红色船只的水手姓名

以下是实现步骤：

涉及的表：Sailors (S), Reserves (R), Boats (B)。需要 Boats 表来判断颜色。
要选择的属性：S.sname。
筛选条件：
- 船只颜色为红色：B.color = ‘red’。
- 连接条件：S.sid = R.sid 且 R.bid = B.bid。

SQL语句如下：

SELECT S.sname
FROM Sailors S, Reserves R, Boats B
WHERE B.color = ‘red’
  AND S.sid = R.sid
  AND R.bid = B.bid;

查询Q5：查找名为‘Lubber’的水手所预订船只的颜色

这个查询与上一个类似，但初始筛选条件是基于水手姓名。
以下是实现步骤：

涉及的表：同样需要 Sailors (S), Reserves (R), Boats (B)。
要选择的属性：B.color。
筛选条件：
- 水手名为‘Lubber’：S.sname = ‘Lubber’。
- 连接条件：S.sid = R.sid 且 R.bid = B.bid。

SQL语句如下：

SELECT B.color
FROM Sailors S, Reserves R, Boats B
WHERE S.sname = ‘Lubber’
  AND S.sid = R.sid
  AND R.bid = B.bid;

字符串匹配与表达式 📝

我们已经掌握了基础的多表连接，本节来看看SQL中两个有用的特性：字符串匹配和算术表达式。

SQL允许在 WHERE 子句中进行字符串模式匹配，使用 LIKE 操作符和两个通配符：

_ （下划线）：匹配任意单个字符。
% （百分号）：匹配零个或多个任意字符。

同时，可以在 SELECT 子句中使用算术表达式，计算结果会作为新列返回（但不会存储到数据库中）。

查询Q6：查找姓名以‘B’开头和结尾，且至少包含3个字符的水手的年龄

以下是实现步骤：

涉及的表：Sailors。
要选择的属性：age。
筛选条件：姓名匹配模式 ‘B_%B’。
- 第一个 B：以B开头。
- _：确保至少还有一个字符（与开头的B一起，保证最少2个字符）。
- %：匹配中间任意数量的字符（包括零个）。
- 最后一个 B：以B结尾。
- 组合 ‘B_%B’ 确保了姓名至少为3个字符（如 Bob 符合 B_b）。

SQL语句如下：

SELECT age
FROM Sailors
WHERE sname LIKE ‘B_%B’;

注意，LIKE 匹配默认可能区分大小写，这取决于数据库的配置。

查询Q7：计算在同一天驾驶过两艘不同船只的水手的评分增量（评分+1）

这是一个更复杂的查询，它需要同一张表（Reserves）的两个副本。
以下是实现步骤：

涉及的表：Sailors (S)，以及 Reserves 表的两个别名 R1 和 R2。
要选择的属性：水手姓名 S.sname 以及评分加1的结果 S.rating+1 AS inc_rating。
筛选条件：
- R1 和 R2 是同一天：R1.day = R2.day。
- R1 和 R2 是不同的船：R1.bid != R2.bid。
- 连接水手信息：S.sid = R1.sid 且 S.sid = R2.sid（确保是同一个水手的两条不同预订记录）。

SQL语句如下：

SELECT S.sname, S.rating+1 AS inc_rating
FROM Sailors S, Reserves R1, Reserves R2
WHERE R1.day = R2.day
  AND R1.bid != R2.bid
  AND S.sid = R1.sid
  AND S.sid = R2.sid;

这个查询找出了在同一天有两条不同船只预订记录的水手，并返回了他们的姓名以及评分加1后的值。

总结

本节课中我们一起学习了SQL查询语言的第一部分。我们从最简单的单表查询开始，逐步深入到多表连接，理解了如何使用 SELECT、FROM 和 WHERE 子句来检索和筛选数据。我们还探讨了查询背后的逻辑执行过程，并实践了字符串匹配（LIKE）和在 SELECT 中使用算术表达式等实用特性。这些是构建更复杂SQL查询的基石。

019：SQL - 第2部分 🗃️

在本节课中，我们将继续深入学习SQL，特别是集合操作和嵌套查询。我们将学习如何使用UNION、IN、ANY、ALL和EXISTS等操作符，并探索在MySQL中实现INTERSECT和EXCEPT功能的替代方法。课程内容将围绕一个“水手-预订-船只”的示例数据库展开。

课程概述与安排 📅

在开始今天的SQL内容之前，有几个课程安排需要通知大家。

首先，关于期中考试，我计划将考试分为两部分。一部分在教室进行，另一部分可能安排在机房，让大家实际操作MySQL来回答问题。这样做的目的是为了更好地测试大家的实际编码能力，而不是在纸上写代码。目前这只是初步想法，欢迎大家反馈。

其次，请大家确保已经按照上节课的要求，在自己的MySQL环境中创建了练习所需的表。你可以连接到学校的服务器或本地数据库。

最后，原计划用三节课讲完SQL，但现在看来我们需要多花一些时间。下周我们仍会开始新章节，但关于SQL的应用部分，我们会以讲解为主，减少动手练习，因此总体进度影响不大。

现在，让我们开始今天的学习。

回顾与本节重点 🔍

上一节我们学习了用于查询信息的基本SQL命令。本节我们将进一步探讨数据操作。

如果你看过教材，会发现它提到了集合操作UNION、INTERSECT和EXCEPT。然而，在我们使用的MySQL版本中，INTERSECT和EXCEPT并未被支持。不过理解这些概念仍然很重要，稍后我会展示如何用其他方法实现相同的功能。

本节我们将要学习以下内容：

支持的集合操作：UNION
成员测试操作：IN
两个有趣的比较操作：ANY（与集合中某些值比较）和 ALL（与集合中所有值比较）
存在性测试：EXISTS

基础查询与`OR`条件 ⚙️

让我们看第一个查询示例。我们希望找出预订了红色或绿色船只的水手姓名。

我们使用的数据库模式包含三个表：Sailors（水手）、Reserves（预订）和Boats（船只）。

要完成这个查询，需要考虑涉及哪些表。我们需要水手的姓名（来自Sailors），需要知道谁预订了船（来自Reserves），还需要知道船的颜色（来自Boats）。因此，这三个表都需要。

首先，我们写出SELECT子句。这里要记住，SQL中的SELECT对应于关系代数中的投影（Projection），而WHERE子句则对应于选择（Selection）。不要混淆这两个“选择”的概念。

我们选择水手的姓名：

SELECT S.sname
FROM Sailors S

为了消除可能出现的重复结果，我们可以使用DISTINCT关键字：

SELECT DISTINCT S.sname
FROM Sailors S

接下来，在WHERE子句中连接表并添加条件。我们需要通过SID连接Sailors和Reserves表，通过BID连接Reserves和Boats表。然后，添加条件来筛选颜色为红色或绿色的船只。

以下是完整的查询：

SELECT DISTINCT S.sname
FROM Sailors S, Reserves R, Boats B
WHERE S.sid = R.sid
  AND R.bid = B.bid
  AND (B.color = 'red' OR B.color = 'green');

请注意，最后一个条件(B.color = 'red' OR B.color = 'green')需要用括号括起来，以确保逻辑正确。

使用`UNION`实现集合并集操作 🔄

上一节我们使用了OR条件。回想在关系代数中，我们还有另一种思路：先分别找出预订红色船的水手集合和预订绿色船的水手集合，然后取它们的并集。

在SQL中，我们可以使用UNION操作符来实现。UNION在MySQL中是受支持的。

以下是使用UNION的写法：

SELECT DISTINCT S.sname
FROM Sailors S, Reserves R, Boats B
WHERE S.sid = R.sid
  AND R.bid = B.bid
  AND B.color = 'red'
UNION
SELECT DISTINCT S.sname
FROM Sailors S, Reserves R, Boats B
WHERE S.sid = R.sid
  AND R.bid = B.bid
  AND B.color = 'green';

这个查询将两个子查询的结果合并，自动去除重复项。

需要注意的是，这里直接对水手姓名（sname）取并集，理论上可能存在不同水手同名的情况。更严谨的做法是先对水手ID（sid）进行操作，最后再关联获取姓名。这涉及到嵌套查询的概念，我们稍后会学习。

实现集合交集：`INTERSECT`的替代方案 🤔

现在，考虑一个更复杂的查询：找出既预订了红色船又预订了绿色船的水手姓名。这对应的是集合的交集。

在关系代数中，我们会使用INTERSECT。但正如开头所说，MySQL不支持INTERSECT。因此，我们需要寻找替代方案。

本节我们来看看如何使用嵌套查询和IN操作符来实现交集功能。

核心思路是：将一个查询的结果作为另一个查询（外层查询）的判断条件。我们可以先得到一个预订了红色船的水手ID集合，然后在外层查询中，找出那些ID也出现在预订了绿色船的水手ID集合中的水手。

以下是实现步骤：

创建第一个子查询（临时结果集T1），获取预订红色船的水手ID。
创建第二个子查询，获取预订绿色船的水手ID。
在外层查询中，从T1中选择那些水手ID，要求该ID必须IN（存在于）第二个子查询的结果集中。

对应的SQL代码如下：

SELECT S.sname
FROM Sailors S
WHERE S.sid IN (
    SELECT R.sid
    FROM Reserves R, Boats B
    WHERE R.bid = B.bid AND B.color = 'red'
    AND S.sid IN (
        SELECT R2.sid
        FROM Reserves R2, Boats B2
        WHERE R2.bid = B2.bid AND B2.color = 'green'
    )
);

在这个例子中，内层子查询(SELECT R.sid ...)生成了一个水手ID集合。外层查询的WHERE S.sid IN (...)则检查当前水手的ID是否在这个集合中。通过嵌套，我们实现了交集的效果。

使用表别名实现多条件连接 🧩

除了嵌套查询，还有一种实现“既…又…”查询的常见技巧：为同一张表创建多个别名（副本）。

思路是：由于一条预订记录只能对应一艘船，要同时满足“红色”和“绿色”两个条件，我们需要两份Reserves和Boats表的“副本”。一份用于关联红色船的预订，另一份用于关联绿色船的预订。然后，我们查找同一个水手（S.sid）在这两份副本中都有对应的预订记录。

以下是使用表别名的实现方法：

SELECT DISTINCT S.sname
FROM Sailors S, Reserves R1, Boats B1, Reserves R2, Boats B2
WHERE S.sid = R1.sid
  AND R1.bid = B1.bid
  AND B1.color = 'red'
  AND S.sid = R2.sid
  AND R2.bid = B2.bid
  AND B2.color = 'green';

这里，R1和B1处理红色船的条件，R2和B2处理绿色船的条件。通过WHERE子句将所有这些条件连接起来，最终结果就是同时满足两个条件的水手。

这种方法通常看起来更简洁直观，你可以根据情况选择喜欢的方式。

实现集合差集：`EXCEPT`的替代方案 ➖

接下来，我们查询预订了红色船但未预订绿色船的水手ID。这对应集合的差集（红色船水手集减去绿色船水手集）。

我们可以沿用嵌套查询和IN的思路，结合NOT关键字来实现。

首先，获取预订红色船的水手ID集合。然后，在这个集合中，排除那些也出现在预订绿色船的水手ID集合中的ID。

SQL语句如下：

SELECT R.sid
FROM Reserves R, Boats B
WHERE R.bid = B.bid AND B.color = 'red'
  AND R.sid NOT IN (
      SELECT R2.sid
      FROM Reserves R2, Boats B2
      WHERE R2.bid = B2.bid AND B.color = 'green'
  );

注意，这个查询只涉及Reserves和Boats表，因为结果只需要水手ID（sid），而这些信息在Reserves表中已存在。

组合查询：使用`UNION` 📤

现在来看一个可以使用UNION的简单例子：找出评级为10或预订了104号船的水手ID。

这很直接，我们可以分别查询满足两个条件的水手ID，然后取并集。

查询语句如下：

SELECT sid
FROM Sailors
WHERE rating = 10
UNION
SELECT sid
FROM Reserves
WHERE bid = 104;

UNION操作符会自动合并两个SELECT语句的结果并去除重复。要记住，使用UNION时，两个SELECT语句选择的列数和类型必须兼容。

嵌套查询简介 🎯

在今天的课程中，我们已经多次看到一种查询结构：将一个查询（子查询）的结果作为另一个查询（外层查询）的条件嵌入其中。这被称为嵌套查询（Nested Query），它是SQL一项非常强大的功能。

目前，我们主要将子查询用在WHERE子句中（例如，与IN或NOT IN配合使用）。实际上，子查询还可以用在FROM子句（作为一个临时表）和HAVING子句中（在分组后过滤，我们后续会学到）。但通常不能直接用在SELECT子句后面。

嵌套查询允许我们分步构建复杂查询，使逻辑更清晰，有时也能通过先过滤数据来提高效率。

嵌套查询效率示例 ⚡

让我们回顾一个简单查询，并用嵌套查询的思路重写它，体会其对效率的潜在影响。

假设要查询预订了1号或3号船的水手姓名。之前的简单写法是：

SELECT S.sname
FROM Sailors S, Reserves R
WHERE S.sid = R.sid
  AND (R.bid = 1 OR R.bid = 3);

在关系代数中，一个可能效率不高的方式是先对Sailors和Reserves做笛卡尔积，再进行选择。为了提升效率，我们可以在连接大表Sailors之前，先对Reserves表进行过滤，只保留预订了1号或3号船的记录，这样中间结果集会更小。

用嵌套查询实现这个思路：

SELECT S.sname
FROM Sailors S
WHERE S.sid IN (
    SELECT R.sid
    FROM Reserves R
    WHERE R.bid = 1 OR R.bid = 3
);

在这个查询中，内层子查询(SELECT R.sid FROM Reserves R WHERE R.bid = 1 OR R.bid = 3)首先快速筛选出目标水手ID集合。然后外层查询只需检查水手的ID是否在这个集合中。这种方法通常更高效。

课程总结 📝

本节课我们一起深入学习了SQL的更多功能。

我们首先回顾了基础的多表连接和OR条件查询。然后，重点探讨了集合操作：学习了如何使用UNION实现并集，以及如何使用IN、NOT IN结合嵌套查询来实现MySQL不直接支持的INTERSECT（交集）和EXCEPT（差集）操作。我们还介绍了通过为表创建多个别名来解决复杂连接条件的技巧。

此外，我们初步了解了强大的嵌套查询概念，看到了它如何让查询逻辑更清晰，并有可能通过减少中间数据量来提升查询性能。

这些知识是构建复杂SQL查询的基石。下节课我们将继续学习SQL的其他高级特性，例如更灵活的连接类型（JOIN）和分组聚合（GROUP BY与HAVING）。

020：SQL编程进阶

在本节课中，我们将继续深入学习SQL编程，重点探讨嵌套查询、相关子查询、集合比较操作以及聚合函数的使用。我们将通过具体的查询实例来理解这些概念，并学习如何编写高效、准确的SQL语句。

嵌套查询与关系代数

上一节我们介绍了SQL的基本查询结构。本节中，我们来看看如何通过嵌套查询来实现更复杂的查询逻辑，并将其与关系代数的操作联系起来。

考虑查询2：找出预订了红色船只的船员姓名。

在关系代数中，一种直接的方法是连接 Sailors、Reserves 和 Boats 三个表，然后进行选择和投影。其过程可以表示为：

公式： π_{sname}(σ_{color=‘red’}(Sailors ⨝ Reserves ⨝ Boats))

然而，这种方法会产生大量的中间结果。更高效的方法是分步操作：

先从 Boats 表中选出红色船只，并只投影出 bid。
将上一步的结果与 Reserves 表在 bid 上连接，并投影出 sid。
最后将上一步的结果与 Sailors 表在 sid 上连接，并投影出 sname。

在SQL中，我们可以用嵌套查询来实现这个分步逻辑：

SELECT S.sname
FROM Sailors S
WHERE S.sid IN (
    SELECT R.sid
    FROM Reserves R
    WHERE R.bid IN (
        SELECT B.bid
        FROM Boats B
        WHERE B.color = ‘red’
    )
);

嵌套查询的逻辑评估通常从最内层开始理解，但数据库引擎的实际执行可能从外层开始，并为外层的每一行计算内层查询。

集合比较操作

SQL提供了 ANY 和 ALL 关键字，用于将某个值与子查询返回的集合进行比较。

以下是关键点：

IN 等价于 = ANY。
NOT IN 等价于 <> ALL。

考虑查询22：找出评级高于任意一个名为“Horatio”的船员的船员。

SELECT S.sname, S.sid
FROM Sailors S
WHERE S.rating > ANY (
    SELECT S2.rating
    FROM Sailors S2
    WHERE S2.sname = ‘Horatio’
);

考虑查询23：找出评级高于所有名为“Horatio”的船员的船员。只需将 ANY 改为 ALL：

SELECT S.sname, S.sid
FROM Sailors S
WHERE S.rating > ALL (
    SELECT S2.rating
    FROM Sailors S2
    WHERE S2.sname = ‘Horatio’
);

从逻辑上理解，> ALL 意味着大于集合中的最大值，而 > ANY 意味着大于集合中的最小值。

使用否定实现除法操作

关系代数中的除法操作在SQL中没有直接对应的运算符。但我们可以通过“双重否定”的逻辑来实现。

考虑查询：找出预订了所有船只的船员姓名。

思路是：对于每个船员，检查是否不存在任何一艘船没有被该船员预订。

SELECT S.sname
FROM Sailors S
WHERE NOT EXISTS (
    SELECT B.bid
    FROM Boats B
    WHERE B.bid NOT IN (
        SELECT R.bid
        FROM Reserves R
        WHERE R.sid = S.sid
    )
);

这个查询解读如下：

最内层查询：找出当前船员 S.sid 预订过的所有船只ID。
中间层查询：找出所有不在该船员预订列表中的船只ID（即该船员未预订的船）。
最外层查询：选择那些“不存在未预订船只”的船员，即预订了所有船只的船员。

聚合函数

SQL提供了多种聚合函数，用于对一组值进行计算并返回单个值。

以下是主要的聚合函数：

COUNT：计数。
SUM：求和。
AVG：求平均值。
MAX：求最大值。
MIN：求最小值。

对于 COUNT、SUM、AVG，可以使用 DISTINCT 来去重。MAX 和 MIN 使用 DISTINCT 在逻辑上不改变结果。

考虑查询25：找出所有船员的平均年龄。

SELECT AVG(age) AS avg_age
FROM Sailors;

考虑查询26：找出评级为10的船员的平均年龄。

SELECT AVG(age) AS avg_age
FROM Sailors
WHERE rating = 10;

考虑查询27：找出最年长船员的姓名和年龄。

一个错误的尝试是：

SELECT sname, MAX(age) FROM Sailors; -- 错误！

这会返回所有姓名中的第一个和最大的年龄值，导致数据不匹配。

正确的方法是使用子查询，找到最大年龄，然后匹配对应的船员：

SELECT S.sname, S.age
FROM Sailors S
WHERE S.age = (SELECT MAX(age) FROM Sailors);

考虑查询28：统计船员总数。

SELECT COUNT(*) AS sailor_count FROM Sailors;
-- 或使用主键
SELECT COUNT(sid) AS sailor_count FROM Sailors;

考虑查询29：统计不同的船员姓名数量（去重）。

SELECT COUNT(DISTINCT sname) AS distinct_name_count
FROM Sailors;

本节课中我们一起学习了SQL编程的进阶主题。我们探讨了如何利用嵌套查询和相关子查询构建复杂查询，理解了 ANY 和 ALL 集合比较操作符的用法，学习了通过否定逻辑模拟关系代数的除法操作，并掌握了 COUNT、AVG、MAX 等聚合函数的使用方法。掌握这些技巧对于编写高效、精确的数据检索语句至关重要。

021：SQL - 第4部分

在本节课中，我们将要学习SQL查询中更高级的概念，包括嵌套查询的深入理解、分组聚合操作（GROUP BY和HAVING子句）以及它们之间的区别。我们将通过具体的查询实例来掌握这些核心技巧。

课程安排与作业更新

在开始新内容之前，我们先了解一下接下来的课程安排。本周我们将完成SQL部分的学习，并在周三开始介绍NoSQL数据库，具体会以MongoDB为例进行实践。请大家在课后可以自行搜索并安装MongoDB。

关于作业，作业三的截止日期是本周五。原定于下周五截止的作业四，由于NoSQL课程开始较晚，已顺延至两周后提交，该作业将完全围绕MongoDB展开。

下周，大家需要根据助教本周发出的邮件和日程表，预约时间进行项目演示。演示的详细要求已在项目描述中说明，如有疑问可以随时向我提问。

课程结束前，我们将进行一次简单的小测验。

回顾：嵌套查询与关系除法

上一节我们介绍了嵌套查询，现在我们来深入理解一个容易混淆的查询——实现关系除法的概念。

这个查询的目标是：找出预订了所有船只的船员姓名。其SQL命令如下：

SELECT S.sname
FROM Sailors S
WHERE NOT EXISTS (
    SELECT B.bid
    FROM Boats B
    WHERE NOT EXISTS (
        SELECT R.bid
        FROM Reserves R
        WHERE R.bid = B.bid AND R.sid = S.sid
    )
);

这个三层嵌套查询的逻辑可以理解为：选择那些不存在任何一艘船（B）没有被该船员（S）预订过的船员。

为了更清晰地理解其执行过程，我们可以将其想象为三层循环：

外层循环：遍历每个船员（Sailor S）。
中层循环：对于当前船员，遍历每一艘船（Boat B）。
内层循环：检查当前船员是否预订了当前这艘船。

内层NOT EXISTS子句检查“该船员没有预订这艘船”的情况是否为假。中层NOT EXISTS子句检查“存在一艘船该船员没有预订”的情况是否为假。最终，外层查询只选择那些“不存在任何一艘船他没有预订”的船员，即预订了所有船的船员。

新的查询模式：与子查询结果比较

本节我们来看看如何比较查询结果。考虑这个查询：找出年龄大于等级为10的船员中最年长者的船员姓名。

解决思路分为两步：

首先，找到等级为10的船员中的最大年龄。这是一个聚合查询。
```
SELECT MAX(age) AS max_age
FROM Sailors
WHERE rating = 10;
```
然后，在外层查询中，将此结果作为阈值进行比较。
```
SELECT S.sname
FROM Sailors S
WHERE S.age > (SELECT MAX(age) FROM Sailors WHERE rating = 10);
```
由于内层查询返回的是单个值（标量），因此可以直接在WHERE子句中使用比较运算符（>）。

另一种方法是使用ALL关键字，无需先求最大值：

SELECT S.sname
FROM Sailors S
WHERE S.age > ALL (SELECT age FROM Sailors WHERE rating = 10);

ALL表示外层查询的年龄需要大于子查询返回集合中的每一个值，这等价于大于其中的最大值。

在性能上，第一种方法（先求MAX）通常更优，因为外层查询的每一行只需要与一个标量值比较。而使用ALL时，每一行都需要与子查询结果集中的每一行进行比较。

引入分组聚合：GROUP BY 与 HAVING

到目前为止，我们学习的聚合函数（如MAX, AVG）都是针对整个结果集。现在，我们引入GROUP BY子句，它允许我们根据一个或多个列对结果集进行分组，然后在每个组内进行聚合计算。

GROUP BY的基本语法结构是：

SELECT ... FROM ... [WHERE ...] GROUP BY ... [HAVING ...]

WHERE：对每一行元组进行过滤。
GROUP BY：根据指定列对过滤后的行进行分组。
HAVING：对每一个分组进行过滤（分组资格）。
SELECT：选择要输出的列，对于使用了GROUP BY的查询，SELECT列表中的列通常只能是分组列或聚合函数表达式。

例如，查询每个等级中最年轻船员的年龄：

SELECT rating, MIN(age) AS min_age
FROM Sailors
GROUP BY rating;

这里，rating是分组列，MIN(age)是组内聚合计算。AS min_age用于为聚合结果列赋予一个易读的名称。

理解查询执行顺序与 HAVING 的用法

为了更好地编写分组查询，我们需要理解其概念上的执行顺序：

FROM：计算FROM子句中表的笛卡尔积（本例只有一张表，可忽略）。
WHERE：根据WHERE子句的条件，过滤掉不满足条件的行。
SELECT（初步）：根据SELECT列表，丢弃不需要的列（但此时聚合尚未发生）。
GROUP BY：将剩余的行按照GROUP BY指定的列进行分组。
HAVING：将HAVING子句的条件应用于每个分组，淘汰不满足条件的分组。
SELECT（最终）：为每个保留下来的分组生成一个输出元组，计算SELECT列表中的聚合函数和分组列。

让我们通过一个复杂点的例子来运用WHERE和HAVING：找出每个至少拥有两名船员的等级中，年龄至少18岁的船员的最小年龄。

SELECT rating, MIN(age)
FROM Sailors
WHERE age >= 18
GROUP BY rating
HAVING COUNT(*) >= 2;

WHERE age >= 18：首先过滤出行，只保留年龄≥18的船员。
GROUP BY rating：然后按等级分组。
HAVING COUNT(*) >= 2：最后，只保留那些分组内行数（即船员数）≥2的组。

分组查询实践：多表连接与常见错误

现在我们将分组应用于多表查询。查询每艘红色船只的预订次数。

SELECT B.bid, COUNT(*) AS reservation_count
FROM Boats B, Reserves R
WHERE B.bid = R.bid AND B.color = 'red'
GROUP BY B.bid;

FROM ... WHERE：连接Boats和Reserves表，并筛选出红色船只的预订记录。
GROUP BY B.bid：按船只ID分组。注意，SELECT列表中的B.bid必须是分组列，这样才能正确显示每艘船。
COUNT(*)：统计每个分组（即每艘船）中的记录数，即预订次数。

一个常见的错误是试图在HAVING子句中过滤行级条件：

-- 错误示例
SELECT B.bid, COUNT(*)
FROM Boats B, Reserves R
WHERE B.bid = R.bid
GROUP BY B.bid
HAVING B.color = 'red'; -- 错误！color不是分组列，且HAVING用于过滤组，而非行。

HAVING B.color = 'red'是不允许的，因为在分组后，每个组可能包含多行（尽管此例中按bid分组后color相同，但SQL语法上不允许引用非分组且非聚合的列）。过滤颜色必须在WHERE子句中进行。

综合练习：区分 WHERE 与 HAVING 的条件

考虑查询：找出每个至少拥有两名船员的等级中，船员的平均年龄。

SELECT rating, AVG(age)
FROM Sailors
GROUP BY rating
HAVING COUNT(sid) >= 2;

这里，COUNT(sid) >= 2是分组级别的条件，因此使用HAVING。

再看一个更复杂的：找出每个至少拥有两名船员的等级中，年龄超过18岁的船员的平均年龄。

SELECT rating, AVG(age)
FROM Sailors
WHERE age > 18
GROUP BY rating
HAVING 2 <= (SELECT COUNT(*) FROM Sailors S2 WHERE S2.rating = Sailors.rating);

WHERE age > 18：先过滤出年龄>18的船员。
GROUP BY rating：按等级分组。
HAVING ...：这里不能直接用COUNT(sid) >= 2，因为经过WHERE过滤后，COUNT(sid)只计算了年龄>18的船员。但题目要求“该等级至少有两名船员”（不限年龄）。因此，我们需要一个关联子查询来统计原表中该等级的总人数。

本节课中我们一起学习了SQL查询的进阶知识，包括复杂嵌套查询的逻辑剖析、使用GROUP BY进行数据分组聚合、以及利用HAVING子句对分组结果进行筛选。关键点在于清晰区分WHERE（行过滤）和HAVING（组过滤）的使用场景，并理解查询语句的执行顺序。掌握这些概念后，你就能编写出功能强大且高效的数据检索语句了。

022：NoSQL导论 🚀

在本节课中，我们将要学习NoSQL数据库的基本概念。我们将探讨NoSQL为何出现，它与传统SQL数据库有何不同，以及它的主要类型和应用场景。

课程概述

上一节我们介绍了关系型数据库的规范化与设计。本节中，我们来看看另一种数据库范式——NoSQL。随着互联网和分布式系统的发展，传统的关系型数据库在处理海量、非结构化数据时面临挑战，NoSQL应运而生。

NoSQL的起源与定义

NoSQL并非“不要SQL”，而是“不仅仅是SQL”。它是一种非关系型数据库管理系统，专为分布式数据存储设计。在谷歌、Facebook、Twitter等公司，每天产生TB级的数据，传统的关系模型在处理这种规模和数据灵活性上存在局限。

为什么需要NoSQL？

与传统SQL基于“存储什么数据”来设计数据库不同，NoSQL的起点是“我要回答什么问题”。这使得它在设计上更加灵活，能够适应快速变化的业务需求，例如社交网络功能的每日更新。

此外，NoSQL数据建模通常需要对数据结构和算法有更深的理解，因为它允许开发者更底层地设计数据存储和检索方式。

一个有趣的现象是，在NoSQL中，我们常常会做两件在SQL中极力避免的事：数据复制和非规范化。我们将在后续课程中详细探讨其原因。

NoSQL与关系型数据库对比

以下是两者的核心区别：

数据模型：关系型数据库数据高度结构化，存储在表中；NoSQL数据可以是半结构化或非结构化的。
查询语言：关系型数据库使用声明式的SQL；NoSQL没有统一的查询语言。
模式：关系型数据库有预定义的严格模式；NoSQL通常无模式或模式灵活。
一致性模型：关系型数据库强调ACID属性；NoSQL通常遵循最终一致性，并受CAP定理约束。
设计目标：关系型数据库关注数据完整性和复杂的关联查询；NoSQL优先考虑高性能、高可用性和可扩展性。

CAP定理

CAP定理是分布式系统中的一个基本原理，它指出以下三者不可兼得：

一致性：所有节点在同一时间看到相同的数据。
可用性：系统始终响应，每个请求都收到成功或失败的回应。
分区容错性：即使网络出现分区，系统仍能继续运行。

理论上，任何分布式数据存储只能同时满足其中两项。NoSQL数据库会根据应用需求，在C、A、P中做出权衡和选择。

NoSQL的优缺点

优点：

高可扩展性：易于在分布式集群中扩展。
低成本：通常利用廉价的硬件集群。
模式灵活：可以动态调整数据结构。
适合非关系型数据：如层次化、图形化数据。

缺点：

无统一标准：种类繁多，选择和学习成本高。
查询能力有限：相比成熟的SQL，查询功能相对简单。
最终一致性：可能不适用于需要强一致性的场景（如银行交易）。

NoSQL的主要类型

NoSQL数据库主要分为四类，每种适合不同的应用场景。

1. 键值存储 🗝️

这是最简单的NoSQL形式。数据以键值对形式存储，通过唯一的键来检索值。

核心概念：

数据模型：{ key: value }
值可以是字符串、数字、列表、JSON对象甚至二进制大对象。
适合场景：用户会话、购物车、配置信息。

示例数据库：Amazon DynamoDB, Redis, Riak。

2. 列族存储 📊

数据按列族组织，同一列的数据连续存储。每行可以拥有不同的列。

核心概念：

优化了对某一列进行聚合查询（如求和、求平均）的性能。
适合场景：大数据分析、内容管理系统。

示例数据库：Google Bigtable, Apache Cassandra, HBase。

3. 图形数据库 🕸️

数据以图的形式存储，包含节点和边。

核心概念：

节点代表实体（如用户、帖子）。
边代表实体间的关系（如朋友关系、点赞）。
适合高效处理复杂的关联关系，例如社交网络、推荐系统。
与关系模型的映射：表→节点集合，行→节点，列→属性，连接→边。

示例数据库：Neo4j, InfiniteGraph。

4. 文档数据库 📄

数据存储在类似JSON的文档中，文档是键值对的集合，且值可以嵌套。

核心概念：

数据模型：{ “key1”: “value1”, “key2”: { “nestedKey”: “nestedValue” } }
文档格式灵活，无固定模式。
与关系模型的映射：表→集合，行→文档，列→文档中的字段。
通常不支持跨文档的连接操作。
适合场景：内容管理、博客平台、用户配置文件。

示例数据库：MongoDB, CouchDB。

总结

本节课中我们一起学习了NoSQL数据库的基础知识。我们了解了NoSQL出现是为了解决海量数据、高并发和灵活模式的需求。我们对比了NoSQL与关系型数据库的核心差异，介绍了CAP定理，并详细探讨了四种主要的NoSQL类型：键值存储、列族存储、图形数据库和文档数据库。每种类型都有其适用的场景，在实际应用中，系统也常常混合使用多种数据库技术。从下一讲开始，我们将深入探索文档数据库MongoDB的具体使用。

023：MongoDB - 第1部分

在本节课中，我们将要学习NoSQL数据库家族中的一员——MongoDB。我们将了解MongoDB是什么，它与传统关系型数据库（如SQL）有何不同，并初步学习其基本的数据结构和操作命令。

概述：什么是MongoDB？

上一节我们介绍了NoSQL数据库的概念。本节中，我们来看看一个具体的、目前非常流行的NoSQL数据库：MongoDB。

MongoDB是一个可扩展、高性能、开源的无模式、面向文档的数据库。它的名字来源于“humongous”（巨大的），寓意其设计初衷是为了处理海量数据。与SQL数据库不同，MongoDB在创建时无需预先定义严格的数据结构（即模式），数据可以灵活地以类似JSON的格式存储。

MongoDB的核心概念

为了理解MongoDB，我们需要先掌握其数据组织方式中的几个核心概念。

数据层级结构

MongoDB的数据组织遵循一个清晰的层级结构，从大到小依次是：实例（Instance）、数据库（Database）、集合（Collection）和文档（Document）。

实例：一个运行中的MongoDB服务进程。
数据库：一个高级别的数据容器，类似于SQL中的“数据库”。一个实例可以包含多个数据库。
- 代码示例：use myDatabase （这条命令会切换到名为myDatabase的数据库，如果该数据库不存在则会创建它）
集合：数据库中的一组文档，类似于SQL中的“表”。一个数据库可以包含多个集合。
- 代码示例：db.myCollection （这指向名为myCollection的集合）
文档：集合中的一条记录，类似于SQL中的“行”。文档是MongoDB中数据的基本单元，以BSON（Binary JSON，JSON的二进制编码格式）格式存储。一个集合包含多个文档。
- 代码示例：{“name”: “Aurora”, “gender”: “f”, “weight”: 450}

文档与无模式

文档是MongoDB的核心。每个文档都是一个由键值对组成的数据结构。最关键的特性是无模式：同一个集合内的不同文档可以拥有完全不同的字段结构。

例如，集合中的两个文档可能是这样的：

// 文档1
{
  “_id”: ObjectId(“…”),
  “name”: “Aurora”,
  “gender”: “f”,
  “weight”: 450
}
// 文档2
{
  “_id”: ObjectId(“…”),
  “name”: “Rogue”,
  “gender”: “m”,
  “vampires”: 99,
  “loves”: [“apple”, “watermelon”]
}

可以看到，文档2比文档1多出了vampires和loves字段，而文档1没有这些字段。这在MongoDB中是允许的。

MongoDB的基本操作（CRUD）

了解了数据结构后，我们来看看如何对数据进行增删改查。MongoDB提供了一套丰富的操作语义，通常被称为CRUD操作，即创建（Create）、读取（Read）、更新（Update）和删除（Delete）。

创建（Insert）

在MongoDB中插入文档非常简单，无需预先创建集合。

以下是插入操作的示例，并与SQL进行对比：

SQL:

INSERT INTO users (name, age) VALUES (‘John’, 25);

MongoDB:
```
db.users.insert({name: “John”, age: 25});
```
执行此命令时，如果users集合不存在，MongoDB会自动创建它，然后插入文档。每个文档会自动生成一个唯一的_id字段作为主键。

读取（Find）

查询是数据库最常用的操作。MongoDB使用find()方法进行查询。

以下是查询操作的示例：

SQL (查询年龄大于18的用户姓名和地址，并限制返回5条):
```
SELECT name, address FROM users WHERE age > 18 LIMIT 5;
```
MongoDB:
```
db.users.find(
    {age: {$gt: 18}}, // 查询条件 (WHERE)
    {name: 1, address: 1} // 投影，指定返回的字段 (SELECT)
).limit(5); // 限制返回数量 (LIMIT)
```
- {age: {$gt: 18}} 是查询条件，$gt是“大于”的比较操作符。
- {name: 1, address: 1} 是投影，1表示包含该字段，0表示排除。默认返回_id字段。
- .limit(5) 是链式调用，用于限制结果数量。

更新（Update）

更新操作用于修改已存在的文档。

以下是更新操作的示例：

SQL (将所有年龄大于18的用户状态设为‘A’):
```
UPDATE users SET status = ‘A’ WHERE age > 18;
```

MongoDB:

db.users.update(
    {age: {$gt: 18}}, // 更新条件 (WHERE)
    {$set: {status: “A”}}, // 更新操作 (SET)
    {multi: true} // 选项：更新所有匹配文档，而非仅第一条
);

$set 是一个更新操作符，用于设置字段的值。
{multi: true} 确保更新所有匹配的文档。如果省略，默认只更新第一条匹配的文档。

删除（Remove）

删除操作用于移除集合中的文档。

以下是删除操作的示例：

SQL:

DELETE FROM users WHERE status = ‘D’;

MongoDB:

db.users.remove({status: “D”}); // 删除所有status为‘D’的文档
// db.users.remove({}); // 警告：这会删除集合内的所有文档！

实践：基础查询示例

让我们通过几个具体的查询例子来加深理解。假设我们有一个unicorns（独角兽）集合，里面存储着形态各异的独角兽文档。

以下是几个查询场景及其对应的MongoDB命令：

查询体重大于700磅的雄性独角兽:
```
db.unicorns.find({
    gender: “m”,
    weight: {$gt: 700}
});
```
这是一个“与”(AND)条件查询，文档必须同时满足gender为“m”且weight大于700。
查询没有vampires字段的独角兽:
```
db.unicorns.find({
    vampires: {$exists: false}
});
```
使用$exists操作符可以检查某个字段是否存在。false表示查找不包含该字段的文档。
查询喜欢苹果或橙子的独角兽:
```
db.unicorns.find({
    loves: {$in: [“apple”, “orange”]}
});
```
$in操作符用于匹配字段值在给定数组内的文档。注意，loves字段的值本身可能是一个数组（例如 [“apple”, “watermelon”]），MongoDB也能正确处理这种查询。

总结

本节课中我们一起学习了MongoDB的基础知识。我们首先了解了MongoDB作为面向文档的NoSQL数据库的核心特性：无模式和可扩展性。然后，我们学习了其数据层级结构：数据库 > 集合 > 文档，并理解了文档以灵活的键值对形式存储。最后，我们通过对比SQL，实践了MongoDB的CRUD基本操作（插入、查询、更新、删除）以及几个常用的查询操作符，如$gt、$exists和$in。

掌握这些基础是进一步探索MongoDB更高级功能（如索引、聚合、复制等）的关键。在下一讲中，我们将继续深入MongoDB的查询世界。

024：MongoDB - 第2部分 🚀

概述

在本节课中，我们将继续学习MongoDB，完成NoSQL部分的介绍。我们将重点学习更新文档、查询结果的筛选与排序、以及聚合操作等核心功能。

课程安排与作业提醒

本周将进行第一次项目演示。演示时间表已发布，请各位同学查看。演示时，请确保已向数据库填充数据，并能通过一个简单的界面（如网页、GUI或手机应用）与数据库进行交互。

关于作业4，我们将使用一个Yelp数据集进行练习。如果发布时间较晚，截止日期会相应顺延。

回顾与衔接

上一节我们介绍了NoSQL的概念，并学习了MongoDB的基础知识，包括数据库、集合、文档和字段。我们还实践了CRUD操作中的创建、读取和删除。

本节中，我们将重点学习CRUD中的更新操作，并探索更多高级查询功能。

数据准备

为了进行后续操作，我们需要重新插入“独角兽”数据。首先，切换到unicorns集合。

use unicorns

然后，执行插入数据的脚本。现在，我们的集合中已包含所有独角兽的数据。

复杂查询：使用 `$or` 运算符

在查询4中，我们希望找到雌性独角兽，并且它们要么喜欢苹果，要么体重小于500磅。

在MongoDB中，我们使用 find() 函数进行查询。多个条件默认是“与”关系。要实现“或”逻辑，需要使用 $or 运算符。

以下是查询的构建方法：

首先指定性别为雌性：{gender: ‘f’}。
然后使用 $or 运算符列出“或”关系的条件。

db.unicorns.find({
    gender: ‘f’,
    $or: [
        {loves: ‘apple’},
        {weight: {$lt: 500}}
    ]
})

这个查询的含义是：寻找性别为雌性，并且（喜欢苹果或体重小于500磅）的独角兽。

投影：选择返回的字段

默认情况下，find() 会返回文档的所有字段。为了只查看特定字段，我们可以使用投影，即在 find() 的第二个参数中指定要包含或排除的字段。

1 表示包含该字段。
0 表示排除该字段。

例如，如果我们只想查看独角兽的name和weight，并排除默认的 _id 字段：

db.unicorns.find({}, {name: 1, weight: 1, _id: 0})

注意：在同一个投影对象中，不能混合使用包含和排除（_id 字段除外）。

更新文档操作

更新是CRUD中我们尚未学习的部分。MongoDB的更新操作需要特别注意，因为直接替换可能导致数据丢失。

错误示范：直接替换

如果我们想将名为“Roodles”的独角兽体重更新为590，错误的做法是：

db.unicorns.update({name: ‘Roodles’}, {weight: 590})

这条命令会找到匹配 {name: ‘Roodles’} 的文档，然后用新文档 {weight: 590} 完全替换它，导致其他字段全部丢失。

正确方法：使用 `$set` 运算符

正确的更新方法是使用 $set 运算符，它只修改指定的字段，而不影响其他字段。

db.unicorns.update(
    {name: ‘Roodles’},
    {$set: {weight: 590}}
)

其他更新运算符

$inc (递增)：对数字字段进行增减。例如，将名为“Pilot”的独角兽杀死的吸血鬼数量减少2：
```
db.unicorns.update(
    {name: ‘Pilot’},
    {$inc: {vampires: -2}}
)
```
$push：向数组字段添加元素。例如，为名为“Aurora”的独角兽的食物列表中添加“sugar”：
```
db.unicorns.update(
    {name: ‘Aurora’},
    {$push: {loves: ‘sugar’}}
)
```
如果原字段不是数组，$push 会将其转换为数组。

高级更新选项

`upsert`

upsert 是 “update + insert” 的组合。如果找不到匹配的文档，则插入一个新文档。需要将 upsert 选项设为 true。

例如，更新一个不存在的独角兽“Walla”的吸血鬼数量，如果不存在则创建它：

db.unicorns.update(
    {name: ‘Walla’},
    {$inc: {vampires: 1}},
    {upsert: true}
)

`multi`

默认情况下，update() 只更新匹配的第一个文档。要更新所有匹配的文档，需要将 multi 选项设为 true。

例如，为所有独角兽添加“已接种疫苗”的字段：

db.unicorns.update(
    {},
    {$set: {vaccinated: true}},
    {multi: true}
)

查询结果排序与限制

排序 (`sort()`)

我们可以使用 sort() 方法对查询结果进行排序。1 表示升序，-1 表示降序。

例如，按体重降序排列所有独角兽，并只显示姓名和体重：

db.unicorns.find({}, {name:1, weight:1, _id:0}).sort({weight: -1})

也可以按多个字段排序，例如先按姓名升序，再按体重降序：

.sort({name: 1, weight: -1})

限制数量 (`limit()`) 与跳过 (`skip()`)

limit(n)：只返回前 n 个结果。
skip(n)：跳过前 n 个结果。

例如，获取体重最重的5只独角兽：

db.unicorns.find({}, {name:1, weight:1, _id:0}).sort({weight: -1}).limit(5)

获取体重第二和第三重的独角兽（跳过最重的1个，再取2个）：

.sort({weight: -1}).skip(1).limit(2)

skip() 和 limit() 的执行顺序不影响结果。

计数操作 (`count()`)

count() 方法用于统计文档数量。

统计集合中所有独角兽的数量：

db.unicorns.count()

统计满足条件的文档数量，例如杀死吸血鬼超过50只的独角兽：

db.unicorns.count({vampires: {$gt: 50}})

这等价于先执行 find() 再计数：

db.unicorns.find({vampires: {$gt: 50}}).count()

总结

本节课我们一起完成了MongoDB核心功能的学习。我们深入探讨了如何正确使用 update() 进行文档更新，包括 $set、$inc、$push 等运算符，以及 upsert 和 multi 选项。此外，我们还学习了如何使用投影筛选返回字段，如何对查询结果进行排序、限制和跳过，以及如何进行计数操作。

掌握这些操作后，你已具备使用MongoDB进行基本数据管理和查询的能力。请结合演示作业和即将发布的作业进行实践，以巩固所学知识。下节课我们将进入第6章的学习。

025：数据库应用开发 - 第1部分 🚀

在本节课中，我们将要学习如何将数据库（SQL）与应用程序代码（如Java、Python）连接起来。我们将探讨两种主要方法：嵌入式SQL和使用API（如JDBC）。课程内容将涵盖核心概念，如游标和动态SQL，并解释它们如何帮助解决SQL与编程语言之间的“阻抗不匹配”问题。

SQL在应用程序代码中

到目前为止，我们主要在终端中执行SQL。但在开发实际应用时，我们需要将SQL语句嵌入到宿主语言（如Java、Python、C++）的程序中。

SQL语句可以引用宿主语言中的变量。这些变量可用于向SQL查询传递参数，或接收SQL返回的状态和结果。程序还必须包含连接到正确数据库的语句。

将SQL与应用程序结合主要有两种方法：

嵌入式SQL：直接将SQL代码写入宿主语言程序。
应用程序接口（API）：通过特定的API（如JDBC）来执行SQL。

阻抗不匹配与游标

SQL和编程语言在处理数据时存在根本差异，这被称为“阻抗不匹配”。一个主要问题是，SQL查询返回的结果通常是多集（允许重复行的表），而许多编程语言没有直接表示“表”或“行集合”的内置数据类型。

游标是解决这个问题的关键工具。它允许应用程序一次处理查询结果中的一行。

游标的作用：游标可以被声明在一个关系（表）或一个生成关系的查询上。它像一个指针，可以定位到结果集中的特定行，并读取该行的内容。
如何使用：程序首先打开游标，然后重复执行 FETCH 操作来逐行获取数据，直到处理完所有行。

以下是游标在嵌入式SQL（C语言示例）中的工作流程概念：

EXEC SQL DECLARE cursor_name CURSOR FOR SELECT ... FROM ... WHERE ...;
EXEC SQL OPEN cursor_name;
while (condition) {
    EXEC SQL FETCH cursor_name INTO :host_variable1, :host_variable2;
    // 处理获取到的数据
}
EXEC SQL CLOSE cursor_name;

注意：游标主要用于 SELECT 查询，因为只有SELECT会返回多行数据给程序。对于 INSERT、UPDATE、DELETE 等不返回数据行的操作，通常不需要使用游标。

嵌入式SQL方法

嵌入式SQL是指将SQL语句直接编写在宿主语言（如C、Java）的源代码中。

其工作原理是：一个预处理器会将这些嵌入式SQL语句转换为特定的API调用，然后由常规编译器编译整个程序。

以下是一个嵌入式SQL的C语言程序示例片段，它展示了声明变量、嵌入SQL查询并使用游标获取结果的过程：

// 声明宿主变量
EXEC SQL BEGIN DECLARE SECTION;
char SQLState[6];
int cMinRating;
char cSName[31];
int cAge;
EXEC SQL END DECLARE SECTION;

// 为变量赋值（例如，从用户输入获取）
cMinRating = 8;

// 声明游标，嵌入式SQL查询引用了宿主变量 :cMinRating
EXEC SQL DECLARE sinfo CURSOR FOR
    SELECT S.sname, S.age
    FROM Sailors S
    WHERE S.rating > :cMinRating;

EXEC SQL OPEN sinfo; // 打开游标

// 循环获取每一行数据
while (SQLState != “02000”) { // “02000” 表示“无更多数据”
    EXEC SQL FETCH sinfo INTO :cSName, :cAge; // 获取数据到宿主变量
    printf(“%s is %d years old.\n”, cSName, cAge); // 处理数据
}

EXEC SQL CLOSE sinfo; // 关闭游标

在这个例子中，SQL查询 WHERE S.rating > :cMinRating 中的 :cMinRating 引用了C程序变量。程序通过游标逐行获取查询结果，并将数据存入 cSName 和 cAge 变量中进行处理。

动态SQL

在嵌入式SQL中，查询通常是静态的，即在编译时就已确定。然而，许多应用需要根据运行时条件（如用户输入）来构建查询。这就是动态SQL的用途。

动态SQL允许在程序运行时构造和执行SQL字符串。

其基本步骤是：

在宿主语言中构建一个SQL查询字符串。
准备（PREPARE） 该SQL语句。
在需要时执行（EXECUTE） 它。

例如（概念性代码）：

char sqlString[100] = “SELECT * FROM Products WHERE category = ?”;
EXEC SQL PREPARE dynamicQuery FROM :sqlString;
// ... 之后，当获取到用户输入的类别值 userCategory 后 ...
EXEC SQL EXECUTE dynamicQuery USING :userCategory;

这种方式提供了灵活性，但可能会引入运行时开销。

嵌入式SQL的局限性与API方法

嵌入式SQL有一个主要缺点：数据库依赖性。虽然源代码（嵌入的SQL）看起来通用，但编译后的可执行文件通常只能与一种特定的数据库管理系统（DBMS）协同工作。如果你想更换数据库（例如从MySQL换成PostgreSQL），可能需要修改并重新编译程序。

为了解决这个问题，我们引入了第二种方法：使用应用程序接口（API）。

API在应用程序和数据库之间增加了一个抽象层。应用程序使用一套标准的接口函数调用，而由特定的驱动程序来负责将这些调用翻译成底层数据库能理解的命令。

这种方法的主要优点是数据库独立性。应用程序只需用一种方式编写，通过更换驱动程序，就可以连接到不同的数据库，而无需修改应用程序代码。常见的数据库API包括ODBC（开放数据库连接）和JDBC（Java数据库连接）。

JDBC架构简介

我们将以JDBC为例，了解API方法的工作原理。JDBC架构包含四个主要部分：

应用程序：负责建立/终止数据库连接、设置事务边界、提交SQL语句、接收并处理结果。
驱动程序管理器：负责加载和管理合适的JDBC驱动程序。应用程序通过统一的接口与驱动程序管理器交互，由它来将调用分发给正确的驱动程序。
驱动程序：这是核心组件。它负责与特定数据库建立实际连接、传输请求（SQL语句）、并将数据库返回的结果和错误代码转换为JDBC标准格式。驱动程序有不同类型（下文详述）。
数据源：即实际的数据库。它处理来自驱动程序的命令并返回结果。

一个应用程序可以同时打开多个连接到不同数据源的连接，这为混合使用SQL和NoSQL数据库等技术提供了可能。

JDBC驱动程序的类型

驱动程序根据其实现方式和部署位置分为四种类型：

桥接型驱动：将JDBC调用转换为另一种非原生API（如ODBC）的调用。优点是可以利用现有设施，缺点是增加了中间层，可能影响性能。
- 应用 -> JDBC -> ODBC桥 -> 数据库
本地API驱动（非Java）：将JDBC调用直接转换为特定数据库客户端API（通常用C/C++编写）的调用。性能通常较好，但需要在每台客户端机器上安装该数据库的本地客户端库。
- 应用 -> JDBC -> 数据库本地API -> 数据库
网络协议驱动：驱动程序（客户端部分）通过网络与一个中间件服务器通信，由该服务器将请求转换为数据库特定的调用。客户端部署非常轻量，与数据库类型无关。
- 应用 -> JDBC -> 网络 -> 中间件服务器 -> 数据库
纯Java驱动：用Java实现的驱动程序，通过Java套接字直接与数据库通信。它通常将JDBC调用转换为数据库的网络协议。这是最常见和推荐的类型，兼具性能和可移植性。
- 应用 -> JDBC -> 纯Java驱动 -> 数据库（通过网络协议）

本节课中我们一起学习了数据库应用开发的基础。我们首先认识了将SQL集成到应用程序中的必要性，并探讨了由此产生的“阻抗不匹配”问题及其解决方案——游标。接着，我们详细分析了两种集成方法：嵌入式SQL和API方法。我们通过示例了解了嵌入式SQL和动态SQL的用法，也指出了嵌入式SQL的数据库依赖局限性。最后，我们引入了更优的API方法，并以JDBC为例，深入了解了其架构和四种驱动程序类型的工作原理，这为我们构建灵活、可移植的数据库应用奠定了理论基础。

026：数据库应用开发 - 第2部分

在本节课中，我们将继续学习数据库应用开发，重点介绍连接前端与后端的不同技术方法，包括JDBC、SQLJ以及存储过程。我们将通过对比和示例来理解这些技术的核心概念与应用场景。

📅 课程进度与作业安排

上一节我们介绍了数据库应用开发的基本概念。本节开始前，我们先了解一下课程当前的进度和即将到来的任务。

目前是第10周，所有小组的项目演示已经完成。我们今天将结束第6章的学习。

关于期中考试（第二次），计划安排如下：

第12周周一：复习课。
第12周周二晚上：期中考试第一部分（编程部分）。
第12周周三：无课。
第12周周五：期中考试第二部分（课堂内进行）。

原定于下周截止的作业4，将延期至第12周周一提交。这是一份关于NoSQL和MongoDB的作业。

以下是关于作业4的详细信息：

数据源：作业将使用Yelp提供的数据集，该数据集包含商家、用户签到、评论等多种信息。本次作业仅使用其中的“商家”数据。
数据规模：数据集较大（约300MB），你需要将其导入MongoDB数据库进行操作。
问题类型：作业中的问题多为开放式。例如，有些问题要求你探索数据集并报告发现；有些则模拟客户需求，让你编写查询来寻找符合条件的商家。
开放性讨论：作业包含对NoSQL与SQL处理问题方式的对比讨论。
加分项：本次作业首次设置了加分项。你可以基于该数据集生成一份高质量的分析报告（至少两页，双倍行距），深入探讨你发现的任何有趣现象。最高可获得相当于学期总评3分的额外加分。

🔌 前端与后端集成方法回顾

在上一节，我们讨论了集成前端（宿主语言）与后端（SQL）的两种主要方法。本节中，我们将更深入地探讨其中一种方法及其变体。

这两种方法是：

嵌入式SQL：将SQL代码直接嵌入到宿主语言（如C、Java）程序中。
API（应用程序编程接口）：通过调用特定的API来执行数据库操作，例如我们介绍的JDBC。

我们还介绍了游标的概念，它主要用于处理SELECT查询返回的结果集，帮助解决宿主语言与SQL之间数据类型不匹配的问题。另外，我们简要提到了动态SQL，它允许在运行时构建和执行SQL语句。

🚗 JDBC 深入与驱动类型

现在，让我们更详细地看看JDBC。JDBC的架构包含四个组件，并且有四种类型的驱动程序。

以下是四种JDBC驱动程序类型：

类型1：桥接驱动：通过一个通用接口（如ODBC）连接到另一个接口。性能通常不是最优。
类型2：本地API驱动：直接将调用转换为数据库特定的本地API（非Java编写）。缺点是在每个客户端都需要安装该本地API。
类型3：网络协议驱动：通过中间件（网络服务器）与数据库通信。客户端只需安装很小的驱动，大部分工作由中间件完成，易于部署。
类型4：纯Java驱动：直接将调用转换为数据库特定的本地API，但该API是用Java编写的。通常能提供最佳性能。

理解这些驱动类型的优缺点很重要，例如从类型2发展到类型3主要是为了解决客户端安装依赖的问题。

💻 JDBC 代码示例

我们不会深入JDBC的每个语法细节，但会通过一个示例来理解其工作方式。以下代码示例演示了如何使用JDBC获取数据库的元数据（如表和列的信息）。

// 假设数据库连接 ‘conn’ 已经建立
DatabaseMetaData dbmd = conn.getMetaData(); // 获取元数据
ResultSet rs = dbmd.getTables(null, null, "%", null); // 获取所有表信息

while (rs.next()) { // 遍历结果集
    String tableName = rs.getString("TABLE_NAME"); // 获取表名
    System.out.println("Table: " + tableName);

    // 获取该表的列信息
    ResultSet columns = dbmd.getColumns(null, null, tableName, "%");
    while (columns.next()) {
        System.out.println("  Column: " + columns.getString("COLUMN_NAME"));
    }
    columns.close();
}
rs.close();

这段代码展示了JDBC的风格：你看不到直接的SQL关键字，所有操作都是通过Java对象和方法调用完成的。ResultSet对象就像一个迭代器，允许你逐行遍历查询结果。

⚖️ SQLJ：介于嵌入式SQL与JDBC之间

接下来，我们介绍第三种方法：SQLJ。它旨在用静态SQL查询来补充JDBC。

SQLJ由多家数据库供应商和Sun公司共同开发。它的代码看起来更像嵌入式SQL，因为你能在程序中看到SQL语句片段。SQLJ的一个主要优点是，它允许在编译时进行语法和类型检查，而不是在运行时才发现错误，这提高了代码的可靠性。

以下是SQLJ的一个简单示例，用于从“books”表中获取信息：

// 在宿主程序中声明变量
String title;
float price;
String author;

// SQLJ 迭代器声明
#sql iterator BookIter(String, float, String);

// 创建迭代器对象并执行SQL
BookIter books = null;
#sql books = { SELECT title, price, author FROM books WHERE author = :author };

// 遍历结果
while (books.next()) {
    #sql { FETCH :books INTO :title, :price, :author };
    System.out.println(title + ", " + price);
}
books.close();

在SQLJ中，以#sql开头的行表示SQL操作。:author这样的变量前加冒号，表示它是来自宿主程序的变量。

SQLJ迭代器有两种类型：

命名迭代器：通过列名来引用数据，如BookIter(String title, float price)。
位置迭代器：通过列的位置顺序来引用数据，如BookIter(String, float)。在获取数据时使用FETCH ... INTO语法。

这两种方式在性能上没有差别，选择哪一种取决于开发者的偏好。

📦 存储过程

最后，我们探讨本章的最后一个核心概念：存储过程。

试想一下，如果在你的项目中，需要反复执行同一段复杂的SQL逻辑，你会怎么做？是每次都在应用程序中重写这段代码，还是将其封装起来以便复用？存储过程就是为了解决这个问题而生的。

存储过程是存储在数据库服务器端的一段程序，它可以被客户端调用执行。它的优点包括：

减少网络传输：逻辑在服务器端执行，只需将最终结果返回客户端，避免了传输大量中间数据。
充分利用数据库性能：可以利用数据库的索引、优化器等特性提升处理速度。
代码封装与复用：将业务逻辑封装在数据库层，可以被多个不同的客户端应用程序共享。
避免使用游标：可以直接在服务器端完成复杂的数据筛选和处理。

一个简单的存储过程定义如下：

CREATE PROCEDURE GetCustomerOrderCount (
    IN customer_id INT,
    OUT order_count INT
)
BEGIN
    SELECT COUNT(*) INTO order_count
    FROM orders
    WHERE cust_id = customer_id;
END;

这里，IN表示输入参数，OUT表示输出参数。

存储过程不仅可以包含SQL语句，在某些数据库系统中，你甚至可以调用用Java等语言编写的函数。存储过程可以从嵌入式SQL、JDBC或SQLJ中调用，调用方式各有不同，但概念一致。

🎯 本章总结

本节课中，我们一起学习了数据库应用开发的几种关键技术。

让我们通过几个核心问题来回顾一下：

什么技术允许在宿主语言中执行参数化静态查询？答案是嵌入式SQL。
与静态查询相反，什么技术允许执行完全动态的查询？答案是动态SQL。
什么机制允许一次检索一行数据，并解决阻抗失配问题？答案是游标。
像JDBC这样的技术，在应用程序和数据库之间引入了一层抽象。
SQLJ 是一种静态的、可在编译时检查查询的技术。
什么技术允许直接在数据库服务器端执行应用逻辑？答案是存储过程。

第6章的内容相对概念化，在考试中，我们更可能考察对不同技术的理解、比较以及识别代码片段属于哪种技术，而非详细的语法。

🖼️ 项目展示预览

在结束之前，我想展示一些在项目演示中收集到的小组作品截图。这有助于大家了解其他团队的进展。如果你的团队截图未出现，仅仅可能是因为未提交给助教，并不代表项目质量。

（此处可描述展示的截图内容，例如：团队A已经开发出包含用户注册、商品展示的前端界面，并使用PHPMyAdmin作为后端管理；团队B能够将数据库中的数据以表格形式清晰展示在网页上；等等。）

看到大多数团队已经建立了基本的数据库连接并实现了核心功能，这非常好。请确保继续完善你们的项目，因为在大约一个月后，将进行第二次演示，届时我们希望看到接近完成度的版本。

本次课程到此结束。

027：互联网应用 - 第1部分

在本节课中，我们将学习互联网应用的基础知识，特别是与数据库交互相关的概念。我们将从互联网的基本通信原理开始，然后探讨几种在网络上表示数据的格式。

互联网概念与通信协议

上一节我们介绍了课程概述，本节中我们来看看互联网的基本概念和通信协议。

到目前为止，我们讨论的所有内容都假设发生在本地机器上。在本章中，我们将重点讨论数据库应用中的互联网概念。

互联网的核心概念之一是统一资源标识符。URI用于标识互联网上的资源，资源可以是网页、图像、视频或电子邮件地址等任何需要通信的内容。

URI通常包含三个部分：

协议：用于访问资源的协议，例如 http、ftp 或 mailto。
主机名：托管资源的计算机名称，例如 www.example.com。
路径名：资源在主机上的具体位置，例如 /path/to/resource.html。

URL是URI的一个子集，但在日常使用中，这两个术语经常互换使用。

通信协议是定义通信双方消息结构的一组标准，以确保彼此理解。常见的协议包括TCP/IP和HTTP。

HTTP是一种无状态协议，这意味着服务器不会记住之前的请求。每次请求和响应都是独立的。

以下是HTTP通信的基本流程：

客户端（如Web浏览器）向服务器发送一个HTTP请求消息。
服务器接收请求后，向客户端返回一个HTTP响应消息。
客户端（浏览器）接收响应并渲染显示内容。

一个HTTP请求消息示例：

GET /index.html HTTP/1.1
User-Agent: Mozilla/4.0
Accept: text/html, image/gif

GET 是HTTP方法，表示请求获取资源。
/index.html 是请求资源的URI。
HTTP/1.1 是协议版本。

一个HTTP响应消息示例：

HTTP/1.1 200 OK
Date: Sun, 18 Oct 2015 10:27:04 GMT
Server: Apache/2.2.14
Content-Length: 1024
Content-Type: text/html

<html>...</html>

200 OK 是状态行，表示请求成功。
中间部分是响应头，包含服务器信息、日期、内容类型等元数据。
<html>...</html> 是响应体，即返回的实际内容（如HTML代码）。

由于HTTP是无状态的，为了实现购物车、用户登录状态记忆等功能，需要额外的机制。最常见的方法是使用 Cookie，它可以将状态信息编码在每次请求和响应中。

网络数据格式

了解了基本的网络通信后，本节我们来看看在网络上表示数据的几种常见格式。

主要有三种数据格式用于网络数据交换：

HTML：超文本标记语言，用于网页呈现。
XML：可扩展标记语言，用于描述和交换结构化数据。
JSON：JavaScript对象表示法，一种轻量级的数据交换格式。

HTML：超文本标记语言

HTML是一种标记语言，它通过向普通文本添加具有特殊含义的“标签”来工作。这些标签告诉Web浏览器如何渲染和显示内容。

HTML的基本格式是使用标签包围内容：

<开始标签> 内容 </结束标签>

例如，一个简单的HTML文档结构：

<html>
  <head>
    <title>页面标题</title>
  </head>
  <body>
    <h1>这是一级标题</h1>
    <p>这是一个段落。</p>
    <ul>
      <li>列表项一</li>
      <li>列表项二</li>
    </ul>
  </body>
</html>

<html> 标签定义了整个文档。
<head> 包含元信息，如标题。
<body> 包含页面的可见内容。
<h1>、<p>、<ul>、<li> 等标签定义了内容的格式（标题、段落、无序列表、列表项）。

HTML的局限性在于它主要关注内容如何呈现，而不是描述数据本身的结构和含义，因此不适合用于复杂的数据交换。

XML：可扩展标记语言

XML是为了弥补HTML的不足而开发的。它也是一种标记语言，但重点在于描述数据的结构和含义，而不是其表现形式。XML在文档视图和数据库模式视图之间架起了桥梁。

XML使用用户自定义的标签来创建层次化的元素，元素可以拥有属性和数据。

以下是一个XML数据示例：

<book genre="science" format="hardcover">
  <author>
    <firstname>Richard</firstname>
    <lastname>Feynman</lastname>
  </author>
  <title>The Character of Physical Law</title>
</book>

<book> 是一个元素，genre 和 format 是其属性。
<author> 和 <title> 是嵌套在 <book> 内的子元素。
这种结构清晰地描述了数据的层次和关系。

JSON：JavaScript对象表示法

JSON使用人类可读的文本来传输数据，它由键值对组成，格式简洁。近年来，JSON在许多应用中已经很大程度上取代了XML。

使用相同数据的JSON表示如下：

{
  "book": {
    "genre": "science",
    "format": "hardcover",
    "author": {
      "firstname": "Richard",
      "lastname": "Feynman"
    },
    "title": "The Character of Physical Law"
  }
}

与XML相比，JSON的优势包括：

更简洁：不需要闭合标签，占用字符更少。
解析更快：对计算机程序而言，解析JSON通常比解析XML效率更高。
压缩率更高：在数据压缩时表现更好。
与JavaScript天然集成：在Web开发中处理起来非常方便。

标记语言与标记down语言

我们讨论了HTML和XML这两种标记语言。现在，让我们简单了解一种特殊的“标记down”语言——Markdown。

Markdown不是标记语言的对立面，它本身就是一种轻量级的标记语言。它的目标是让人们使用易读易写的纯文本格式编写文档，然后可以转换成有效的HTML。

Markdown常用于格式化README文件（如在GitHub上）、在线论坛消息，或者创建富文本内容。

以下是Markdown与HTML的简单对比：

目的	Markdown 语法	HTML 语法
一级标题	`# 标题` 或 `标题` 后跟 `======`	`<h1>标题</h1>`
二级标题	`## 标题` 或 `标题` 后跟 `------`	`<h2>标题</h2>`
加粗文本	`加粗文本`	`<b>加粗文本</b>`
无序列表	`- 项目一`	`<ul><li>项目一</li></ul>`
段落	用空行分隔	`<p>段落</p>`

Markdown的语法更直观、更简洁，特别适合快速书写和记笔记。

文档类型定义

最后，我们简要介绍文档类型定义。DTD用于定义XML文档的合法构建模块，它规定了XML文档中可以有哪些元素、属性以及它们的排列规则。

DTD使用类似正则表达式的语法来定义元素出现的规则：

*：表示前面的元素可以出现零次或多次。
+：表示前面的元素必须出现一次或多次。
?：表示前面的元素可以出现零次或一次。
,：表示元素必须按指定顺序出现。
|：表示在列出的元素中选择一个出现。

例如，一个简单的DTD定义：

<!ELEMENT booklist (book*)>
<!ELEMENT book (author, title, published?)>

第一行定义 booklist 元素可以包含零个或多个 book 子元素。
第二行定义每个 book 元素必须包含 author 和 title 子元素，并且可以包含一个可选的 published 子元素。

DTD帮助确保XML文档的结构符合预定义的模式，是验证XML文档有效性的工具之一。

本节课中我们一起学习了互联网应用的基础部分。我们了解了URI和HTTP协议如何实现网络通信，探讨了HTML、XML和JSON这三种关键的网络数据格式及其特点，简单认识了Markdown这种轻量级标记语言，并介绍了用于定义XML文档结构的DTD。这些知识是构建和理解现代Web数据库应用的重要基石。

028：互联网应用 - 第二部分

在本节课中，我们将继续学习第7章关于互联网应用的内容。我们将回顾DTD（文档类型定义），了解JSON模式，并学习如何从网页上抓取数据。最后，我们将探讨数据密集型系统的架构演变，从单层到三层架构。

回顾：文档类型定义（DTD）

上一节我们介绍了XML和DTD的基本概念。本节中，我们来回顾一下DTD中如何使用正则表达式来定义元素结构。

DTD使用类似正则表达式的符号来定义XML文档中元素的结构和出现次数。以下是一些关键符号的含义：

*：表示零次或多次。
+：表示一次或多次。
?：表示零次或一次（即可选）。
,：表示序列（即“与”关系，所有元素必须按顺序出现）。
|：表示选择（即“或”关系）。

在DTD中，我们可以定义元素的内容类型。以下是两种主要类型：

元素内容：指一个元素内部包含其他子元素，从而形成数据的层次结构。例如：
```
<booklist>
    <book>
        <author>...</author>
        <title>...</title>
    </book>
</booklist>
```
混合内容：指一个元素内部可以包含文本和/或其他元素的混合。在DTD中，这通常用 (#PCDATA) 表示，代表可解析的字符数据。

此外，DTD还可以定义属性和其他约束：

#REQUIRED：表示该属性是必需的。
用括号 () 括起来并用 | 分隔的列表，表示枚举选择。例如，(平装 | 精装) "平装" 表示 format 属性可以是“平装”或“精装”，且默认值为“平装”。

现在，请判断以下XML片段是否符合前面描述的DTD规则：

<booklist>
    <book genre="科技">
        <author>
            <firstname>张</firstname>
        </author>
        <title>数据库导论</title>
    </book>
</booklist>

解析：此XML片段存在两处不符合DTD规则的地方：

author 元素下缺少必需的 lastname 子元素。
book 元素的 genre 属性是必需的，但示例中提供了该属性，所以这一点是正确的。

另外，如果 booklist 中不包含任何 book 元素（即为空），根据DTD中 book* 的定义（零个或多个），这也是有效的。

JSON模式

我们之前提到，JSON格式因其高效性（如解析速度快、压缩率高）在许多方面已取代XML。然而，与XML需要DTD进行验证类似，JSON数据也需要一种方式来确保其结构符合预期。

JSON模式是一种目前仍处于草案阶段的标准，它提供了一种一致的方法来验证JSON数据项。其核心思想与XML DTD相似，都是通过定义规则来描述数据的预期结构。

以下是一个简单的JSON模式示例，它定义了一个包含书籍列表的对象：

{
  "type": "object",
  "properties": {
    "booklist": {
      "type": "array",
      "items": {
        "type": "object",
        "properties": {
          "author": {"type": "string"},
          "title": {"type": "string"}
        },
        "required": ["author", "title"]
      }
    }
  }
}

这个模式表示：根是一个对象，它有一个 booklist 属性；booklist 是一个数组，数组中的每个项目都是一个对象；每个对象必须包含 author 和 title 这两个字符串类型的属性。

由于JSON模式尚不成熟，在实际应用中（例如网站表单验证），可能需要借助额外的脚本或函数库来完成验证。

小结：我们讨论了三种主要的网络数据格式。HTML专注于内容在网页上的呈现方式；XML在HTML的基础上，增加了描述数据结构和语义的能力，作为关系型数据库和网页之间的桥梁；而现代的JSON格式，兼具XML的层次化数据表达能力，并在存储和传输效率上更具优势。

从网页抓取数据

了解了数据格式后，我们来看看如何从互联网上获取这些数据。网络抓取是指编写程序自动浏览指定网页并收集页面上可见数据的过程。它通常用于在网站不提供官方API接口时获取数据。

网络抓取可以获取HTML源码中包含的任何内容，如文本、图片链接等，但无法直接访问网站的后端数据库。

有许多工具可用于网络抓取。本节将演示使用Python语言及其库进行抓取的基本方法。即使你不熟悉Python，其语法也相对简单易懂。

我们将以从亚马逊网站抓取产品名称作为示例。基本思路是：分析网页结构，找到目标数据所在的HTML标签和CSS类，然后用程序模拟访问并提取信息。

以下是使用Python进行抓取的核心步骤和代码片段：

导入必要的库：urllib2 用于打开网页，BeautifulSoup 用于解析HTML。
```
import urllib2
from bs4 import BeautifulSoup
```

读取网页内容：将目标网页的URL地址存储下来，然后打开并读取整个页面的HTML代码。

url = "https://www.amazon.com/s?k=thanksgiving+decorations"
response = urllib2.urlopen(url)
html = response.read()

解析HTML并定位数据：使用BeautifulSoup解析HTML。通过检查网页元素，我们发现产品名称位于 <h2> 标签中，并且具有特定的class属性。

soup = BeautifulSoup(html, 'html.parser')
# 找到第一个符合条件的产品名
product = soup.find('h2', {'class': 'a-size-medium a-spacing-none a-color-base a-text-normal'})
product_name = product.get_text()
print(product_name)
# 找到所有符合条件的产品名
all_products = soup.find_all('h2', {'class': 'a-size-medium a-spacing-none a-color-base a-text-normal'})
for prod in all_products:
    print(prod.get_text())

通过类似的方法，你可以定位价格等其他信息，并将它们组合起来。要抓取多页数据，需要分析翻页时URL的变化规律（例如 &page=2），然后通过循环构造不同的URL重复上述抓取过程。

数据密集型系统架构

现在，让我们从具体的数据格式和抓取技术，转向支撑互联网应用的系统架构。数据密集型系统的架构决定了其数据管理、应用逻辑和表示层这三个核心组件是集中在一台机器上，还是分布在多台机器中。

单层架构

这是最早期的架构形式。所有组件（数据库、业务逻辑、用户界面）都集中运行在一台主机（如大型机）上。用户通过简单的“哑终端”进行访问。

优点：易于维护和管理，所有东西都在一起。
缺点：缺乏图形界面，中心系统计算压力大，难以扩展和互联。

两层架构（客户端-服务器架构）

这种架构将系统分为两部分：

瘦客户端：客户端仅负责图形用户界面，所有业务逻辑和数据管理都放在服务器端。
胖客户端：客户端不仅负责界面，还包含了业务逻辑，服务器端仅负责数据管理。
- 缺点：业务逻辑更新困难（需更新所有客户端），存在安全隐患（服务器必须完全信任客户端），客户端软件笨重。

在胖客户端架构中，数据库的存储过程可以发挥优势。通过将关键业务逻辑以存储过程的形式放在数据库服务器端执行，可以减少客户端实现的多样性，提高安全性和一致性。

三层架构

现代Web应用普遍采用三层架构，它将系统清晰地分为：

表示层：直接与用户交互，负责渲染界面（如HTML、CSS、JavaScript），并适配不同设备（电脑、手机、平板）。
中间层（应用服务器）：包含核心业务逻辑，控制业务流程（如购物车状态管理），处理用户请求，并作为表示层与数据库层的桥梁。它动态生成HTML页面，并控制对数据库的访问（通过JDBC等接口）。
数据管理层：即数据库管理系统（DBMS），负责数据的存储、查询和完整性管理。

各层之间通过特定协议通信（如HTTP用于浏览器与中间层，JDBC用于中间层与数据库）。

实例分析：

航空订票系统：
- 数据层：存储航班、座位、客户信息。
- 中间层：处理订票、取消、航班创建等逻辑。
- 表示层：显示查询表单、航班列表，并进行输入验证。
课程注册系统：
- 数据层：存储学生、课程、教师信息。
- 中间层：处理选课、退课、开课等逻辑。
- 表示层：显示课程表、注册表单，并进行输入验证。

三层架构的优势在于职责分离、易于扩展、维护方便，并且安全性更高（业务逻辑集中在受控的中间层）。

总结

本节课中我们一起学习了互联网应用的几个关键部分。我们回顾了如何使用DTD定义和验证XML文档的结构，了解了作为XML现代替代品的JSON格式及其模式验证。接着，我们探索了从网页抓取数据的实用技术，通过Python和BeautifulSoup库演示了基本流程。最后，我们系统地回顾了数据密集型系统架构的演进，从早期的单层主机架构，发展到客户端-服务器两层架构，直至现代普遍采用的表示层、中间层、数据层分离的三层架构，并理解了各层的职责与优势。这些知识将帮助你理解当今网络应用背后的数据流动与系统组织原理。

029：互联网应用 - 第3部分 🖥️

在本节课中，我们将学习互联网应用架构中的表示层技术。我们将了解HTML表单如何收集用户数据，JavaScript如何为网页添加简单的交互功能，以及CSS如何控制网页的样式和布局。课程最后，我们还会简要了解现代前端框架Bootstrap。

期中考试安排 📢

在开始今天的课程内容之前，有几个关于期中考试的重要通知。

期中考试分为两个部分。第一部分是编程考试，由于机房容量限制，我们将分为两组进行。

以下是考试时间安排：

第一组：下午5点至6点。
第二组：晚上8点至9点。

考试地点在218号Linux机房。考试预计只需15分钟左右，不会占用整个小时。对于没有CC账户的同学，我和助教可以登录我们的账户供你们使用。在Linux机房考试的好处是无需再次配置VPN。

考试第二部分将在周五进行。请记住，周一是作业和复习课，周二晚上是正常上课时间，我会展示报名表格。周三没有课。

关于报名，我会在课后通过Angel系统发送一封包含Google表单链接的邮件。请尽快选择你的考试时间并告知我，以便我尽早安排。

关于作业与项目的说明 📝

接下来，我想谈谈作业和项目相关的一些事情。

我上传了一个关于网络数据抓取的示例代码，虽然你们现在可能因为项目而对此不感兴趣，但可以将其作为一个简单的实验来看。这个例子和我们之前抓取感恩节装饰品数据的例子完全相同。

关于作业4，很多同学已经发现，问题4中的许多查询要求实际上没有返回任何结果。

这是因为最初我使用了一个更大的数据集导入到我的数据库，但提供给你们的版本数据有所截断，丢失了很多匹兹堡地区的数据。不过没关系，只要你的查询语句正确，就可以得分。有些问题可能没有答案，因为我选取了很多匹兹堡的例子。例如，有一个关于匹兹堡“Shady Side”社区的问题，如果你在查询时发现城市为匹兹堡且社区名为“Shady Side”的记录，就可以作答。

表示层技术概述

上一讲我们介绍了互联网应用的三层架构。最底层的数据库管理层是我们直到第5章SQL查询为止一直在学习的内容。今天，我们将简要浏览表示层和中间层，但主要聚焦于表示层。

表示层的技术发展相对稳定，而中间层在过去十年间变化巨大。教科书出版于2002年，其中提到的中间层技术（如CGI）现在已很少使用。因此，我们将更多关注表示层，内容会非常简单。

表示层的主要功能是作为用户的主要界面，负责所有的网页设计，并能适配不同的显示设备（如PC、平板、手机或手表）。发生在表示层的功能通常不复杂，可能包括一些字段验证，例如检查网页表单中填写的信息是否正确。除此之外，大部分工作并不单独由表示层处理。

表示层使用的技术主要有以下几种：

HTML表单：用于将数据从客户端传递到中间层的常见方式。
JavaScript：一种基于Java的脚本语言，可以在客户端进行轻量级计算。
CSS：负责网页的外观，如格式、颜色和布局。

我们还有一项技术XSL，但经过查阅，它现在已不常用。教科书也提到了它，但当前主流是CSS。

接下来，我们将逐一讨论这些技术。

HTML 表单

HTML表单是将数据从客户端传递到中间层的常用方式。表单只负责从客户端到中间层的数据传递，中间层如何访问数据库则由中间层自己处理。

很多同学已经做过表单，但也有一些同学还没有。我将展示如何创建一个非常简单的表单。

在HTML中创建表单的基本结构如下，你总是以<form>标签开始，并设置一些属性，然后以</form>标签结束。在表单内部，你可以放置任何你希望用户输入的字段。

<form action="处理文件.jsp" method="post" name="表单名">
    <!-- 表单字段放在这里 -->
</form>

action属性指定处理该表单的JSP文件。
method属性可以是get（更像登录表单）或post（用户提交信息，如发送邮件）。
你也可以为表单命名，以便识别。

在动手创建简单表单之前，我们先看看有哪些可用的输入选项。

创建表单时，我们有多种输入类型：

input：最简单的文本框，也可以改变类型为单选按钮等。
select：下拉列表。
textarea：更大的文本框，用于评论等，可以指定大小。

让我们尝试一下，以便你能看到效果。

假设你从未写过HTML。正如我们上节课或上上节课讨论的，HTML总是以<html>标签开始。

<html>

并以</html>标签结束。在HTML中，我们有不同的部分，第一部分是head部分，然后是body部分。head包含所有信息，例如页面标题，或者你可以链接不同的主机或外部文件。body负责网页内容。

由于我们希望表单在网页上呈现，它实际上会出现在body部分。正如之前所说，表单以<form>标签开始。

假设我们想给这个网页一个标题。你可以使用<h1>标签，它表示一级标题。

<html>
<head>
    <title>页面标题</title>
</head>
<body>
    <h1>注册表单</h1>
    <form action="imaginary.jsp" method="post" name="signupForm">
        <!-- 表单内容 -->
    </form>
</body>
</html>

现在，因为我们想创建一个表单，正如之前看到的，我们应该设置不同的属性。例如，对于这个表单的action，我们假设稍后会有一个JSP文件来处理它。今天实际上不会发生什么，但我们假设有一个虚构的JavaScript会处理将信息传递到中间层。今天我们不处理那部分。

对于method，我们使用post，因为我们要注册用户。如果我们想给这个表单命名，可以随便起一个名字。

首先，假设我们希望用户输入用户名。如果我们直接放文本，它只是HTML中的纯文本。在这里，我们想要一个常见的矩形细长输入框。

用户名: <input type="text" name="username">

在HTML中，表单有趣的一点是，只有input标签没有结束标签。出于某种原因，他们这样设计。

也许你想要密码字段。如果你不希望所有内容都在同一行，可以插入换行。在HTML中，<br>标签用于换行。

<br>
密码: <input type="password" name="password">

现在你可以想象，除了标题，你会看到一个用于输入用户名的文本框和一个用于输入密码的文本框。当你输入密码时，不会显示密码，而是显示点号。

如果我们想提交表单，可以添加另一个input，但将类型改为submit。这将给你一个按钮。value属性是显示在按钮上的标签。

<br>
<input type="submit" value="提交">

现在我们可以打开它看看效果。这是一个非常简单的表单，我们有两个字段。你可以尝试输入内容，但因为我们没有链接到任何实际的操作，所以提交任何内容都不会发生任何事情。

你们想看看其他类型的输入字段吗？比如单选按钮或下拉菜单？我们可以试试。

JavaScript 表单验证

现在，我们想为这个表单添加一点功能。例如，即使用户没有输入任何内容就提交，表单也会通过。也许你想说，至少必须填写用户名。

这实际上可以通过使用JavaScript来实现。

在深入了解之前，我们先快速谈谈JavaScript是什么。它的目标是为表示层添加更多功能。正如我所说，完整的表示层除了显示和渲染外，做的事情不多。

JavaScript可以帮助你做得更多一些。JavaScript的一些示例应用包括：

检测浏览器：不同的浏览器可能兼容不同的功能，你可能需要先检测。
表单验证：我们稍后会尝试，例如，如果用户遗漏了某些字段，你应该弹出一个对话框提示。
浏览器控制：例如，你可以打开新窗口，或实现返回上一页的功能。

通常，JavaScript可以直接嵌入到HTML中，使用<script>标签，或者你也可以编写一个完全独立的脚本文件，然后链接到它。我们将尝试直接嵌入。

如果你将其嵌入，可以指定语言，但如果不指定，默认是JavaScript。你也可以通过src属性链接外部文件。

让我们看看如何直接嵌入。通常的做法（或个人偏好）是将其放在body部分的末尾。

正如之前所说，你以<script>标签开始，然后以</script>标签结束，然后在里面放入你希望拥有的任何功能。

假设今天我们希望有一个功能来测试用户名是否已填写。如果未填写，我们希望提醒用户。

在这里，正如你所知，JavaScript是一种基于Java的语言，所以你在里面看到的代码与Java非常相似。

我们将声明一个函数，用于检查用户名是否为空。这个函数不需要参数，因为它直接链接到同一HTML文档中的表单。

你可以说我想检查变量x，希望它直接是这里的用户名。你可以通过document.forms来访问它，指定是哪个表单（这里是signupForm），然后指定要检查的字段（username）。所有这些都通过之前设置的name来引用。

我们感兴趣的是username的value，不关心类型或其他东西。value将是用户输入的内容。

获取用户名的值后，我们可以进行检查。如果用户名为空，我们弹出一个对话框提示“请输入用户名”。因为这是一个错误情况，我们返回false。如果这种情况没有发生，我们就返回true。

<script>
function validateForm() {
    var x = document.forms["signupForm"]["username"].value;
    if (x == "") {
        alert("请输入用户名");
        return false;
    }
    return true;
}
</script>

这非常直观，但现在别忘了，你实际上需要将它链接回表单，否则什么也不会发生，它只是一个独立的脚本，表单不会知道它在为表单本身做某事。

你可以做的是，除了这些属性，你可以在表单标签中添加onsubmit事件。当提交表单时，返回我们刚刚声明的函数，让它来帮你检查。

<form action="imaginary.jsp" method="post" name="signupForm" onsubmit="return validateForm()">

现在表单变得稍微复杂了一点，至少我们有一些事情在进行，而不仅仅是输入。我们可以保存并刷新页面。现在，如果我们不在用户名中输入任何内容就提交，就会弹出提示。这是最简单的有效性检查。

CSS 样式表

现在，我们实现了一点功能，让我们看看如果想改变一下样式可以做什么。

让我们谈谈样式表。一般来说，样式表的想法是我们希望将显示与内容分离。

这样更好吗？比如用我的工作做验证？我认为在安全性方面，有些检查如果你能在浏览器级别完成，会快很多，而不是访问数据库。很多检查实际上不需要你去数据库，例如，用户名是否被占用，你就不能在浏览器级别完成，必须去数据库查看。所以我认为这取决于你想要实现什么功能，但如果我们只在JavaScript级别做，很多功能可以更快完成。

样式表的想法是我们希望将显示与内容分离，并使显示适应不同的呈现格式。你可以想象，有时你只想以一种方式设计网站，并指定要显示的内容，但你希望有其他来源来决定它应该看起来如何。

你可能会注意到，这种情况很少发生，但有时你打开Facebook，它看起来有点乱，你只看到内容，所有颜色都是默认的。这很少发生，但有时会发生在我身上，也许那时他们的CSS暂时不可用，没有链接好，所以你可以看到所有内容，但显示方式不美观。这就是CSS缺失的时候，但内容都在那里。

在样式表方面，有两个方面。第一个方面是文档转换，它将帮助你决定文档的哪些部分以什么顺序显示。样式表最常用的第二个方面是文档渲染，它将帮助你决定文档的每个部分如何显示。例如，之前我们的表单中有纯文本和标题，我们也有文本字段，实际上你可以说所有的标题我都想以这种方式显示，所有的文本字段我都想以另一种方式显示。

我们为什么想使用样式表？有不同的原因：

我们希望为不同的显示重复使用相同的文档，正如我们之前所说，CSS或其他样式表可以帮助你在不同设备上以不同方式呈现。
根据用户偏好定制显示，有时你可以触发一些设置，让用户希望同一网页以不同方式显示。
必须在不同上下文中重复使用相同的文档。

有两种主要的样式表语言。第一种是我们今天要讨论的级联样式表，也就是CSS。它是为HTML做的。另一种是可扩展样式表语言，我们称之为XSL，用于我们上次讨论的XML文档。但请记住，我之前说过它现在正慢慢消失。有一段时间，一切都用HTML完成，然后人们说现在我想更面向模式而不是面向文档，所以他们转向XML来实现数据层次结构。但现在人们为了压缩或效率原因又回到了JSON。所以现在你看那些源代码，就像我们上次为Amazon做的那样，你看不到任何关于XML的东西，它看起来还是HTML。所以现在，人们不再使用XML来处理表单，而是又回去了。但当教科书在13年前编写时，他们还在谈论XML现在取代了HTML。现在如果你不回头看，我也会谈到Bootstrap，现在很多人都在使用Bootstrap，它实际上是HTML和CSS的组合。

CSS到底是什么？CSS代表级联样式表，它定义了如何显示HTML元素。CSS文件包含样式定义，你可以设置你想使用的字体、字体大小和颜色等。许多不同的HTML文档可以引用同一个CSS文件，所以在同一个网站内，如果你们都引用同一个CSS文件，那么每个人都有相同的样式。如果你想改变整个网站的风格，但希望内容保持不变，那么只需更改那一个CSS文件，每个人都会改变。

CSS被认为是实现内容与呈现分离的第一步。与在Word中手动设置样式不同，如果你在Word中不应用样式，比如标题，你可能会手动高亮这一行，将其设置为14磅，设置为其他字体，这意味着这是一级标题。然后一切都是在行内完成的，这样做不连贯，如果你突然想改变标题样式，你必须回去再次更改每一处。如果你想象在Microsoft Word中操作，使用顶部的样式选择标题、标题一、标题二，这是类似的想法。

CSS文件是什么样子的？CSS文件通过以下行包含到HTML文件中。我们可以使用<link>标签链接我们编写的外部CSS文件。

在CSS文件中，有这样的格式。你总是以一个选择器开始，然后在花括号内说属性的值是什么，就像键值对。例如，对于body部分，我希望所有body部分的背景颜色是黄色。这完全等同于在HTML中直接设置，说它是body，背景颜色是黄色。

让我们看看。我们可以做什么？我们想试试。例如，我们可以创建一个新文件，简单地将其保存为sample.css。

例如，今天，我们希望body部分的背景颜色全是黑色。如果你不熟悉这种表示颜色的方式，它是十六进制数。从0到f，0表示0，一直到9，然后是A、B...所以它不是二进制，是十六进制数。这是表示颜色的方式。如果全是0，表示黑色。也许我们可以说，让字体颜色全是白色。如果你放F，那是最大值。这对这门课来说不是那么重要，只是让你知道，这不是网页设计课，所以没关系。也许我们不喜欢默认字体，我们可以说我们想将字体系列改为Calibri。这是我们幻灯片一直使用的字体，因为我喜欢它，我们也可以用于网页。除此之外，也许我想说对于标题，我也想改变它。也许我想把标题变成橙色。你可以看到我们也可以放一些颜色的常规名称。除了放在左边，我可以将其对齐到中心。这只是向你展示详细的语法，我来展示我们如何使用一个统一的样式。

现在我们设计好了，然后我们需要将其链接回去。我们可以这样做，正如你所记得的，我们将东西放在head部分。我们可以用这种方式链接，使用<link>标签。

<link rel="stylesheet" type="text/css" href="sample.css">

基本上，我们刚刚做的是指定我们希望文本是白色，背景是黑色，标题是橙色。我们改变了字体。

在从CSS继续之前，我只想做多一点事情。

JavaScript 动画示例

我认为JavaScript已经存在很长时间了。有一段时间人们也使用Java Applet，它更像是与JavaScript一起使用，但在网页上显示更多动画或图形功能，我认为这是一项非常古老的技术，现在正在消失，但JavaScript仍然存在。我只是想向你展示我10岁左右第一次接触JavaScript的经历。那时我们在小学开始上计算机课，当时我们使用一个叫FrontPage的软件。这暴露年龄了，这就像一个网页编辑器，你可以使用它。它很糟糕，但我们作为孩子仍然玩得很开心，我们试图使用它。当时我总是看课堂上的电脑工具包。我在网上寻找一些可以融入你的网页的酷东西，我发现有一些小动画。我不知道为什么我会想我可以做，但就像我小时候，我会直接把东西粘贴到我的网页源代码中，像那样。所以我发现有一些下雨的动画，或小云飞的动画，或下雪。所以我找到了这个下雪的JavaScript，我真的很开心，我只是想展示给你们看。主要想法是向你展示这些拖放脚本也可以实现超级简单的动画。当你打开它时，可能太详细了，你不想理解，但它们会谈论很多关于雪的速度，它如何飞，或者你可以与雪进行一些交互，所以你的光标移动，雪会改变方向所有这些小事。你不必理解这个，但网上有很多不同的功能，你可以直接下载。例如，我有这个雪暴JavaScript，我想在我自己的网页上试试。我们可以直接把它合并到我们的网页中。这就是我把背景改成黑色的原因。现在，我们有雪了。所以你看，即使是简单的JavaScript，没有任何图像，因为它们都只是小点，也可以做到这一点。你可以改变雪如何飞等等。

所以这是JavaScript讨论的结束。你可以发现不同种类的小功能，当然，为你的项目添加雪并不是真的有用，但你可以添加更多的有效性检查。当我小时候，我觉得这很酷。好的，这就是JavaScript，也是我今天要讲的表示层的全部内容。因为这不是网页设计课，所以我不会在表示层上放太多重点。

Bootstrap 框架简介

在我们继续之前，实际上关于表示层还有一件事。Bootstrap。我想你们很多人都听说过。谁听说过Bootstrap？相当多。好的，我认为现在使用Bootstrap作为前端设计是一种趋势。

当我第一次接触Bootstrap时，我觉得它看起来真的很花哨。这是我的第一印象，它看起来非常整洁，非常流畅，更像现代的设计方式。但后来它到了被过度使用的程度，无论你去哪个小网页，都是Bootstrap，不管他们有多少内容，都是Bootstrap。每个人看起来都像Bootstrap，但我仍然认为它是目前最好看的网页设计模板。

Bootstrap最初是由Twitter的人开发的。它最初名为Twitter Blueprint，由Twitter的Mark和Jacob开发，供内部使用。一开始，他们并没有真的想让其他人使用它，除了Twitter内部人员，他们可以在内部使用它。也许一开始，每当他们展示工作进展时，展示的演示文稿太乱了，所以他们决定，在公司内部，我们将有一种统一的方式来显示结果，这就是为什么他们开发了Bootstrap。但后来，他们发现这个东西可以非常强大，因为他们设计的方式非常易于使用，并且非常容易在此基础上开发其他类型的样式。所以他们将其开源，它是一个用于创建网站和Web应用程序的开源工具集合。

正如我之前所说，现在人们从XML转向，回到JSON和HTML来显示数据，所以Bootstrap你可以认为它是基于HTML和CSS的设计。它自带许多组件，包括排版、表单、按钮、导航和其他界面组件。你可能不知道课程网站使用的是Bootstrap。好的，这是一个例子。我为你选择了Bootstrap作为课程网站。Bootstrap，你可以看到这只是另一个Bootstrap例子。所以我认为当你回去时，你可以尝试看看其他Bootstrap例子，我们也有一个团队，项目销售Bootstrap模板。所以你可以去看看一般的主题是什么样子，它们有不同的东西。最常见的是很多人会使用Bootstrap来编写他们的GitHub仓库Wiki，所以他们会为仓库编写自己的文档，或者另一种就是这种看起来非常宽，每个部分都是一整行颜色。

这就是Bootstrap。我原本想演示如何在Bootstrap中做事，但会变得太复杂，因为他们自己开发了这么多组件。所以我认为如果你以后想改变，如果你没有使用Bootstrap或者你想改变，你可以去看看。链接到Bootstrap CSS非常容易。然后你查看文档，看看如何使用不同的组件。我快速展示一下课程网站。所以这是导航栏。然后我们也有这里，都是Bootstrap完成的，即使有，我不知道你们有没有人注意到我的设计有一个缺陷，网站上无法向下滚动，但我想你们可以滚动右侧。所以，这是一个基本的Bootstrap例子，但它看起来不那么花哨，因为如果你去很多当前的网站，我想很多会议网站或很多初创公司网站，它们看起来超级Bootstrap风格。所以你可以试试。

这就是Bootstrap，这就是我们表示层的结束，我们在那里花了很多时间。

课程总结与后续安排

实际上，我们没有时间讲中间层了，或者添加太多乐趣。对于中间层，我想我会在期中考试后讲，所以它不会在期中考试中。今天我们就到这里，直到第13讲。之后的所有内容，我们下次再讲。期中考试二，我不会考这些东西。

在你们离开之前，我还有一个关于DTD的小测验。如果你们记得我们上次讨论的XML文档类型定义，它非常简单，非常相似的例子，所以我可以给你一个DTD，然后你只需检查给出的例子是否遵循它。就是这样。DTD代表文档类型定义。好的，我想我应该宣布的一切都宣布了。现在，在开始之前，我再快速过一遍。

本节课中，我们一起学习了互联网应用表示层的核心技术。我们了解了如何使用HTML表单收集用户输入，如何用JavaScript进行简单的客户端验证以提升用户体验，以及如何用CSS来控制网页的视觉样式，实现内容与表现的分离。最后，我们还简要介绍了现代流行的前端框架Bootstrap。这些工具共同构成了构建现代、交互式网页应用的基础。

030：存储与索引

在本节课中，我们将要学习数据库管理系统如何存储和访问数据。我们将从数据的基本抽象概念开始，逐步深入到文件组织、访问方法以及索引技术。最后，我们会介绍如何对不同的存储设计进行性能评估。

数据的基本抽象

数据库管理系统中的数据是一个记录的集合。你可以将记录理解为关系数据模型中的元组。

每个文件实际上由一个或多个页组成。页是读写操作的最小单位。为了高效地访问记录，我们主要依靠两个概念：文件组织和访问方法。这两个概念将在本章后续部分分别讨论。

存储层次与设备

数据库中的数据量通常非常庞大，因此数据必须存储在外部存储设备上，并在需要时被提取到主内存中进行处理。

我们主要使用两种外部存储设备：

磁盘：可以以固定的成本随机检索任意页。访问不同页的成本大致相同，但连续读取多个页比随机读取更便宜。
磁带：必须按顺序读取页。虽然设备本身更便宜，但随机访问成本高昂，因此通常用于数据归档。

存储设备从快到慢、从贵到便宜的层次结构通常为：缓存 -> 主内存 -> 闪存 -> 磁盘 -> 光盘 -> 磁带。

存储引擎

存储引擎是负责文件组织的整体框架。它可以分为三个管理器：

文件管理器：处理文件和访问方法层，并向缓冲区管理器发出调用。它为查询提供记录的概念。
缓冲区管理器：负责将页从外部存储暂存到主内存缓冲区中。
磁盘空间管理器：以页为单位管理磁盘空间。

本节我们将重点讨论文件管理器。

文件组织

文件组织是指在外部存储上安排文件记录的方法。它基于记录ID来物理定位记录。一个存储引擎可能支持多种文件组织方式。

文件管理器需要支持以下基本操作：

扫描：逐步遍历文件中的所有记录。
按RID检索：根据其记录ID检索特定记录。
按条件检索：根据搜索条件检索一组记录。这可以是等值搜索（查找属性值完全匹配的记录）或范围搜索（查找属性值在某个范围内的记录）。
更新：插入和删除记录。

有三种主要的文件组织方式，各有其优缺点。

堆文件组织

堆文件组织是最简单的方式。记录可以放在文件中有空间的任何位置，没有特定的顺序，也没有索引。

优点：设计简单轻量。
缺点：除非扫描整个文件，否则检索特定记录效率很低。在实际应用中较少使用。

适用场景：当典型访问模式是扫描所有记录，且不关心记录顺序时。

有序文件组织

在有序文件组织中，记录根据搜索键的值按顺序存储。搜索键不一定是主键，可以是任何你选择的属性。

优点：当需要按顺序或仅检索某个范围内的记录时，效率很高。
缺点：插入和删除记录的成本很高，因为需要移动大量记录以维持顺序。一种优化方法是使用指针链，这样逻辑上有序，但物理上可以不连续。

索引

索引是一种数据结构，用于组织记录，以加快基于搜索键的查询速度。任何关系属性的子集都可以作为该关系上索引的搜索键。

索引可以与文件结合使用，显著加快搜索速度，并且更新操作通常比有序文件更高效。然而，索引本身会带来额外的存储开销。

索引包含一系列数据项。数据项是包含搜索键值 K 以及足够信息来定位对应数据记录的结构。

数据项有三种设计替代方案：

替代方案一：数据项就是包含键值 K 的实际数据记录本身。
替代方案二：数据项是键值 K 和对应数据记录的记录ID (K, RID) 对。
替代方案三：数据项是键值 K 和具有该键值的所有数据记录的记录ID列表 (K, List of RIDs)。

替代方案一的索引结构本身就是一个文件组织。它可能导致数据冗余。如果数据记录很大，索引也会很大。
替代方案二和三的数据项通常比实际数据记录小得多，因此索引结构更紧凑。替代方案三在多个记录共享相同搜索键值时尤其高效。

索引技术（如B+树、哈希）的选择与数据项的设计方案密切相关。

总结

本节课中，我们一起学习了数据库存储与索引的基础知识。我们首先了解了数据在数据库中的基本抽象形式（记录和页），以及不同的存储设备（磁盘和磁带）。接着，我们探讨了存储引擎的组成部分，并重点介绍了三种文件组织方式：堆文件、有序文件和索引。我们详细比较了它们的优缺点，并解释了索引中数据项的三种不同设计方案。理解这些概念是评估和选择合适数据存储方案的基础。在接下来的课程中，我们将深入探讨具体的索引数据结构和性能量化分析。

031：基于哈希的索引 📊

在本节课中，我们将继续学习第8章的内容，重点探讨索引的分类以及基于哈希的索引技术。我们将了解索引如何根据不同的属性进行分类，并深入理解哈希索引的工作原理及其优缺点。

课程安排更新 📅

在开始新内容之前，先对课程安排进行一些调整。原定的第16章内容已被移除，期末考试将涵盖第1、2、8和12章的内容。第二次作业的截止日期已调整至12月2日。此外，新增了一项加分作业，要求使用任意编程语言实现本节课讨论的数据结构，最多可获得2分。项目演示将在假期后的一周进行。

回顾：数据项的三种形式

上一节课我们介绍了指向数据记录的“数据项”的三种替代形式。理解这些形式是学习索引的基础。

以下是三种数据项形式的总结：

实际数据记录：数据项本身就是包含键值K的完整数据记录。
键值-记录ID对：数据项是键值K和对应的记录ID（RID）的组合，形式为 (K, RID)。
键值-记录ID列表对：数据项是键值K和一组记录ID列表的组合，形式为 (K, List of RIDs)。这种形式允许一个数据项指向多个数据记录，更为紧凑。

索引分类 🏷️

索引可以根据不同的标准进行分类。本节中，我们主要关注两种重要的分类方式。

主索引 vs. 辅助索引

主索引：索引建立在关系的主键上。由于主键具有唯一性，因此主索引默认也是唯一索引。
辅助索引：索引建立在其他属性上。该属性可能包含重复值，因此辅助索引不一定是唯一的。

聚集索引 vs. 非聚集索引

这个分类关注数据记录的物理存储顺序与索引中数据项顺序的关系。

聚集索引：数据记录的物理存储顺序与索引中数据项的排序顺序相同或接近。
非聚集索引：数据记录的物理存储顺序与索引中数据项的排序顺序无关。

重要说明：数据项的“第一种形式”（即数据项就是数据记录本身）必然意味着聚集索引。然而，一个数据库文件最多只能在一个搜索键上建立聚集索引，因为数据记录本身只能按一种顺序物理存储。

检索数据的成本因索引类型的不同而有很大差异，聚集索引通常对范围查询更高效。

基于哈希的索引 🔑

在了解了索引的基本分类后，我们来看看一种具体的索引技术：基于哈希的索引。

哈希索引原理

哈希索引的核心是使用一个哈希函数。该函数以搜索键值作为输入，计算出一个哈希值，这个值直接决定了数据项应该存储在哪个桶中。
bucket_number = hash_function(search_key_value)

理想情况下，哈希函数能将数据均匀地分布到各个桶中，从而实现快速的等值查询（时间复杂度接近O(1)）。然而，哈希索引通常不适合范围查询，因为哈希函数破坏了键值的顺序关系。

桶溢出处理

每个桶通常有一个初始的固定容量（主页）。当向一个已满的桶插入新数据时，系统会使用溢出页来存储额外的数据，而不是重新组织整个结构。溢出页链接到主桶之后。

哈希索引示例

假设我们有一个简单的Employee关系，包含name， age， salary字段。我们可以设计两个哈希索引：

基于age的哈希索引（使用数据项形式1）。
基于salary的哈希索引（使用数据项形式3）。

哈希函数可以这样设计（示例）：

对于age：将其转换为二进制，取最低两位，根据00， 01， 10， 11分配到4个不同的桶。
对于salary：采用另一种哈希计算方式。

通过编程实现这个结构，可以清晰地看到数据是如何通过哈希函数映射到各个桶中的，以及不同数据项形式（如形式1和形式3）在存储上的区别。

基于树的索引 🌲（简要介绍）

作为对比，我们简要介绍另一种主流索引技术。基于树的索引（如B+树）将数据项组织成层次化的树形结构，并保持数据项按键值排序。

与哈希索引擅长等值查询不同，基于树的索引特别适合范围查询。因为键值是有序存储的，所以可以高效地找到某个范围内的所有记录。我们将在下一节课中详细讨论这种索引结构。

课堂测验 ✏️

请准备一张纸，回答以下问题：

在基于哈希的索引中，哈希函数的作用是什么？它决定了数据项将被分配到何处。

总结

本节课我们一起学习了以下内容：

回顾了数据项的三种形式。
学习了索引的两种主要分类：主索引/辅助索引，以及聚集索引/非聚集索引。
深入探讨了基于哈希的索引的工作原理，包括哈希函数、桶和溢出页的概念，并分析了其适用于等值查询但不适用于范围查询的特点。
简要对比了基于树的索引，为下节课的内容做了铺垫。

下节课我们将继续深入探讨基于树的索引结构。

032：基于树的索引与ISAM 🗂️

在本节课中，我们将继续学习文件组织，并重点介绍一种新的数据结构——基于树的索引。我们将从回顾哈希索引开始，然后深入探讨一种名为ISAM（索引顺序存取法）的树状索引结构，了解其工作原理、构建过程以及优缺点。

回顾：哈希索引

上一节我们介绍了哈希索引。哈希索引使用一个哈希函数，将输入值（如键）映射到不同的存储桶中。

公式：bucket_number = hash_function(key)

每个存储桶都有其容量。如果数据条目超过容量，就会发生溢出，通常通过创建溢出页来处理。数据条目本身可以存储实际数据记录（替代方案1），也可以存储指向数据记录的指针（替代方案2或3）。

引入基于树的索引

与哈希索引不同，基于树的索引结构天然支持数据的有序性。这对于范围查询（例如，查找某个区间内的所有数据）非常高效，而哈希索引在这方面则效率较低，因为它无法直接体现数值之间的顺序关系。

一种基本的树状索引思想是使用一个索引文件来指向数据文件。数据文件被分成多个页，索引中的键用于指示应该访问哪个数据页。例如，一个键K1左侧的指针指向所有属性值小于K1的页，而K1和K2之间的指针则指向属性值在K1和K2之间的页。

ISAM 索引详解 🌲

ISAM是一种静态的树状索引结构。“索引顺序”意味着数据条目在底层（叶节点）是按顺序存储的。

ISAM的结构

ISAM树由多层节点构成：

叶节点：存储实际的数据条目，这些条目已按键值排序。
非叶节点（索引节点）：存储键值和指针，用于导航到下一层节点。
根节点：树的顶层节点。

每个节点对应一个磁盘页，有固定的容量（例如，每页可存放2个键值-指针对）。

ISAM的构建过程

以下是构建一个ISAM索引的步骤：

创建叶节点：将所有数据条目按键值排序，然后根据页容量将它们配对分组。每一组形成一个叶节点页。
创建上层索引：
- 为所有叶节点创建一个上层索引页。
- 该索引页中的键值通常取自其指向的子节点中的最小键值（或能区分子节点范围的值）。
- 指针用于指向对应的叶节点页。规则是：指针Pi指向的子树中，所有键值都小于键值Ki。
创建根节点：如果上层索引页多于一个，则继续创建更上层的索引，直到最终形成一个根节点。

关键概念：

主页：在初始构建过程中创建的页（包括叶节点和索引节点）。
溢出页：后续插入新数据时，如果目标叶节点已满，则不会重构整个树，而是链接一个新的页来存放多余数据，这个新页就是溢出页。

插入与删除操作

插入：找到正确的叶节点，如果该节点已满，则在其后链接一个溢出页来存放新数据。索引结构本身（主页）不会改变。
删除：找到并删除数据条目。删除后，主页中可能留下空位，但ISAM不会自动重新填充这些空位或合并半空的页。

ISAM的性能分析

访问一个数据条目的成本（I/O次数）通常等于树的高度。

公式：访问成本 ≈ 树的高度

树的高度可以通过叶节点数量 N 和每个索引节点的扇出 F（即最多可指向的子节点数）来估算。

公式：高度 ≈ log_F(N)

ISAM的主要优点是初始构建后，点查询效率很高（因为树矮且平衡）。然而，其主要缺点是随着数据不断插入，溢出链会变得很长，导致查询性能下降，因为可能需要遍历很长的溢出链才能找到数据，从而失去了树结构原本的高效优势。

总结

本节课我们一起学习了基于树的索引结构，并深入探讨了ISAM。

我们首先回顾了哈希索引的局限性，特别是在范围查询方面。
接着，我们介绍了树状索引如何通过有序性来高效支持范围查询。
然后，我们详细讲解了ISAM索引的静态结构、构建过程以及插入删除操作。
最后，我们分析了ISAM的性能，并指出了其因使用溢出页而可能导致的性能退化问题。

ISAM的静态特性是其弱点。在下节课中，我们将学习一种动态的、能够保持树平衡的索引结构——B+树，它被设计用来克服ISAM的缺点。

033：成本分析模型与索引选择 📊

在本节课中，我们将继续学习数据库索引的成本分析模型，并探讨如何根据不同的查询需求选择合适的索引类型。我们将从回顾聚集索引的成本开始，然后分析非聚集索引的成本差异，最后讨论在实际应用中如何选择索引属性。

回顾聚集索引成本分析

上一节我们介绍了堆文件和聚集索引文件。现在，我们来回顾一下聚集索引的成本分析模型。

首先，我们定义几个关键变量：

B：数据页的数量。
D：一次I/O操作的成本。
F： B+树中每个节点的扇出（子节点数量）。
R：每页中存储的记录数量。

经验研究表明，数据页的平均占用率约为67%。这意味着，如果我们有 B 个逻辑数据页，物理上实际需要大约 1.5B 个数据页来存储数据。

以下是聚集索引下各种操作的成本分析：

扫描
扫描所有数据页的成本是访问每个物理页的成本之和。
Cost = 1.5B * D

等值查询（返回单条记录）
这需要从B+树的根节点遍历到叶子节点，找到目标数据项。
Cost = (log_F(1.5B)) * D

等值查询（返回多条记录）或范围查询
首先找到第一条匹配记录的叶子节点，然后顺序读取后续匹配的记录。由于数据是聚集存储的，后续记录在物理上相邻。
Cost = (log_F(1.5B)) * D + (#匹配记录数 / R) * D

插入
首先搜索到正确的插入位置（叶子页），然后写入该页。
Cost = (log_F(1.5B)) * D + 1 * D

删除
首先搜索到要删除的记录所在页，然后重写该页。
Cost = (log_F(1.5B)) * D + 1 * D

分析非聚集索引成本

上一节我们分析了聚集索引，本节中我们来看看非聚集索引。非聚集索引的树形结构与聚集索引类似，但其叶子节点中的数据项指针指向的数据记录在物理上是无序分布的。

假设索引数据项的大小是实际数据记录的1/10，且页占用率为67%，那么叶子页的数量约为 0.15B。

以下是各种操作的成本分析：

扫描
需要读取所有叶子页，并且由于数据记录非聚集，可能还需要为每个数据记录进行额外的I/O。
Cost ≈ (0.15B + B) * D （简化模型，实际更复杂）

等值查询（返回单条记录）
遍历树找到数据项后，还需要一次I/O去读取实际的数据记录。
Cost = (log_F(0.15B)) * D + 1 * D

等值查询（返回多条记录）或范围查询
每条匹配的记录都可能位于不同的数据页，因此需要为每条记录支付一次额外的I/O。
Cost = (log_F(0.15B)) * D + (#匹配记录数) * D

插入
搜索到正确位置后，写入索引页和数据页。
Cost = (log_F(0.15B)) * D + 2 * D

删除
搜索到记录后，删除索引项和数据记录。
Cost = (log_F(0.15B)) * D + 2 * D

与有序文件（二分查找成本为 log_2(B) * D）相比，非聚集索引（尤其是当扇出F较大时）的搜索成本 log_F(0.15B) * D 通常更低，是一种改进。

索引选择策略 🎯

了解了不同结构的成本后，我们面临一个实际问题：应该基于哪些属性来创建索引？索引选择的目标是优化最频繁或最重要的查询性能。

以下是选择索引时需要考虑的几个关键点：

1. 考察查询的WHERE子句
WHERE子句中用于比较和筛选的属性是索引的首选候选者。因为它们直接参与了数据定位。

2. 匹配查询类型

精确匹配查询：如果应用中有大量 = 操作，哈希索引 是高效的选择。
范围查询：如果查询经常使用 BETWEEN, >, < 等操作，树形索引（如B+树）更合适，因为它能保持键值的顺序。聚集索引对范围查询尤其有利。

3. 考虑多属性索引（复合索引）
有时查询条件涉及多个属性，这时可以创建基于多个属性的复合索引。属性的顺序至关重要，它决定了索引的排序方式，进而影响查询效率。

4. 利用“仅索引查询”
如果查询只需要返回索引键中包含的属性，数据库可以直接从索引中获取数据，而无需访问实际的数据记录页，这称为“仅索引查询”，能极大提升效率。
例如，如果索引建立在 age 上，查询 SELECT COUNT(*) FROM employees WHERE age=20 就是一个仅索引查询。

5. 评估条件的选择性
如果某个条件过滤掉的数据很少（例如 age > 10 几乎匹配所有员工），那么基于该属性的索引帮助不大。应该选择那些能将数据集显著缩小的高选择性属性。

复合索引顺序示例
假设有数据（姓名，年龄，薪水）。复合索引可以有两种顺序：

(age, salary)：先按年龄排序，年龄相同的再按薪水排序。
(salary, age)：先按薪水排序，薪水相同的再按年龄排序。
对于查询 WHERE age=30 AND salary=5000，索引 (age, salary) 更有效，因为它能首先利用age进行快速定位。而对于查询 WHERE salary=5000，索引 (salary, age) 则更好。

总结

本节课中我们一起学习了数据库索引的详细成本分析模型和选择策略。
我们回顾并比较了聚集索引与非聚集索引在扫描、查询、插入、删除等操作上的成本差异，理解了非聚集索引因数据记录分散存储而带来的额外I/O开销。
接着，我们探讨了如何根据实际查询需求（如精确匹配、范围查询、复合条件）来选择合适的索引类型和属性，并引入了“仅索引查询”这一优化概念。
掌握这些知识，有助于我们在设计数据库时做出更明智的索引决策，从而提升系统整体性能。

034：索引选择与查询评估 🗂️

在本节课中，我们将要学习数据库管理系统中的索引选择策略以及查询评估的基本概念。我们将探讨如何根据不同的查询模式选择合适的索引，并初步了解查询优化器如何工作以提高查询性能。

概述

上一节我们介绍了不同类型的索引结构。本节中，我们来看看如何为具体的应用场景选择最合适的索引，并开始接触查询执行计划与优化。

索引选择策略

索引的选择主要取决于应用程序中最常使用的查询类型。

以下是选择索引时需要考虑的几个核心场景：

基于单个属性的等值查询：如果查询经常基于单个属性（如 age = 30），那么在该属性上建立单列索引是最佳选择。
基于单个属性的范围查询：同样，为该属性建立索引（无论是B+树索引）能有效加速范围搜索。
基于多个属性的等值查询（复合索引）：如果查询经常同时基于多个属性进行等值匹配（如 age = 30 AND salary = 40），那么建立一个包含所有这些属性的复合索引通常比多个单列索引更高效。
基于多个属性的混合查询（等值+范围）：当查询包含等值条件和范围条件时（如 age = 30 AND salary BETWEEN 30 AND 50），复合索引中字段的顺序变得至关重要。应将等值查询的字段放在复合索引的前面。这样，数据库可以先快速定位到所有满足等值条件的记录，再在这些记录中高效地进行范围扫描。

核心概念：复合索引字段顺序
在复合索引 (A, B) 中，数据首先按 A 排序，在 A 相同的情况下再按 B 排序。因此，对于查询 A = a AND B > b，索引 (A, B) 是高效的；而对于查询 A > a AND B = b，索引 (B, A) 可能更优。

仅索引扫描

仅索引扫描是一种高效的查询优化技术。如果查询所需的所有数据都包含在索引的键值中，数据库可以只扫描索引而无需访问实际的数据表（堆文件），从而极大提升速度。

以下是实现仅索引扫描的示例：

查询：SELECT COUNT(*) FROM employee GROUP BY deptno
- 所需索引：在 deptno 上建立索引。查询只需要访问 deptno 的值，这些信息在索引中已完全包含。
查询：SELECT deptno, AVG(salary) FROM employee GROUP BY deptno
- 所需索引：复合索引 (deptno, salary)。deptno 用于分组，salary 用于计算平均值，所有数据均可从索引中获取。
查询：SELECT AVG(salary) FROM employee WHERE age = 25
- 所需索引：复合索引 (age, salary) 或 (salary, age)。虽然 (age, salary) 对于 WHERE 条件更高效，但两者都能实现仅索引扫描，因为 age 和 salary 都存在于索引中。

本章总结

在第8章中，我们一起学习了以下核心内容：

存在多种可选的物理存储组织方式（如堆文件、排序文件、哈希文件、索引），每种方式适用于不同的场景。
哈希索引在等值查询上性能最佳，但不支持高效的范围查询。
索引是数据条目的集合，并提供了根据键值快速查找条目的方法。数据条目可以是实际数据记录、键值-记录ID对或键值-记录ID列表对。
索引可分为聚集索引（表中数据行的物理存储顺序与索引顺序一致）和非聚集索引。一个表只能有一个聚集索引。
选择索引时，目标是加速最重要的查询，同时需权衡索引维护的开销。应选择能惠及尽可能多查询的索引。
仅索引扫描策略能显著提升查询性能。
对于复合索引，字段的顺序非常重要，需要根据查询模式（等值、范围）仔细设计。

数据库管理系统：第12章：查询评估概述 ⚙️

从第8章我们深入了解了如何利用索引加速数据访问。现在，我们进入第12章，探讨数据库系统如何评估和执行查询以获得最优性能。

概述

在本章中，我们将要学习关系数据库系统中查询评估的全过程。重点是理解查询执行计划、关系操作符的实现方式以及查询优化的基本思想。

查询执行计划

查询执行计划是关系代数表达式的一种扩展树形表示。在这棵树中：

每个节点代表一个关系代数操作符（如选择、投影、连接）。
每个边代表数据流。
操作符是查询评估的基本构建块，通常通过“拉取”接口实现：父节点从子节点请求数据。

核心概念：查询执行计划树
一个SQL查询首先被转换为关系代数表达式，进而表示为树形执行计划。
例如，对于查询：

SELECT S.name
FROM Sailors S, Reserves R
WHERE S.sid = R.sid AND R.bid = 103

其关系代数表达式为：π_{S.name}(σ_{R.bid=103}(Sailors ⋈ Reserves))
对应的初始查询执行计划树如下：

      π_{S.name}
         |
      σ_{R.bid=103}
         |
       ⋈ (Join)
       / \
  Sailors Reserves

查询优化

相同的查询可以有多种不同的执行计划，其性能差异巨大。查询优化器的任务就是找到代价最小的执行计划。

以下是优化查询计划的两个主要方向：

重排操作顺序：尽早执行选择操作以减少中间结果集的大小。例如，将上述计划优化为：
```
      π_{S.name}
         |
        ⋈ (Join)
       / \
  Sailors  σ_{R.bid=103}
              |
           Reserves
```
这样，连接操作发生在过滤后的、更小的 Reserves 表上。

为操作符选择高效算法：每个关系操作符（尤其是连接）有多种实现算法。
- 嵌套循环连接：最简单但效率低，适用于小表。
- 索引嵌套循环连接：对外层表的每个元组，利用内层表上的索引查找匹配元组。
- 排序归并连接：先将两个表按连接键排序，然后像合并两个有序列表一样进行连接，效率很高。
- 哈希连接：使用哈希表进行连接，特别适用于等值连接。

在查询执行计划中，我们会为每个操作符节点标注所选用的算法。

代价估算

为了比较不同执行计划的优劣，优化器需要估算每个计划的代价。代价主要包括两部分：

操作符本身的执行代价：例如，排序、哈希或索引扫描的成本。
中间结果的大小：每个操作符产生的数据量大小，这直接影响其父操作符的处理成本以及磁盘I/O。

本章总结

在第12章中，我们一起学习了查询评估的基础：

查询通过查询执行计划树进行评估，树中的节点是关系操作符。
查询优化是寻找高效执行计划的过程，涉及重排操作顺序和选择底层算法。
主要的连接算法包括嵌套循环连接、索引嵌套循环连接、排序归并连接和哈希连接，各有其适用场景。
优化器通过代价估算模型来比较和选择计划，估算因素包括操作符代价和中间结果大小。

本节课我们完成了对索引选择和查询评估核心概念的梳理。理解这些原理是进行数据库性能调优和高效SQL编程的基础。

035：期末考试复习指南

在本节课中，我们将对数据库管理系统的核心知识点进行系统性的复习，涵盖从基础概念到高级索引技术的全部内容。我们将按照期末考试可能涉及的问题集结构，逐一梳理每个章节的重点。

问题集一：简答题（第1、6、7章及SQL基础）

上一节我们介绍了课程的整体结构，本节中我们来看看第一部分简答题的复习要点。这部分主要考察对基本术语、概念和不同技术之间比较的理解。

以下是第1章（引言与数据库概念）的核心考点：

数据模型：理解层次、网状和关系模型等经典数据模型。
三层模式结构：区分内模式、概念模式和外模式。
数据独立性：理解逻辑数据独立性和物理数据独立性的含义。

以下是第6章（中级SQL）的核心考点：

完整性约束：掌握CHECK、NOT NULL、UNIQUE等约束。
触发器与存储过程：
- 触发器：由特定数据库事件（如INSERT, UPDATE, DELETE）自动执行的代码块。
- 存储过程：可视为数据库中的函数，用于封装复杂的业务逻辑。
授权：理解GRANT和REVOKE语句在权限管理中的应用。

以下是第7章（高级SQL与应用开发）的核心考点：

JDBC/ODBC：理解数据库连接API的作用。
有状态 vs 无状态：
- 无状态协议：如HTTP，每次请求独立。
- 有状态应用：服务器端维护会话信息（如使用Cookie、Session）。
数据表示格式：区分XML（可扩展标记语言，用于结构化文档）和JSON（轻量级数据交换格式）。
触发器的类型：理解BEFORE、AFTER、INSTEAD OF等不同类型触发器的应用场景。

问题集二：ER模型与关系模型（第2、3、5章）

上一节我们回顾了基础概念，本节中我们来看看数据库设计核心——ER模型和关系模型的转换。

以下是ER模型（第2、3章）的绘图与概念要点：

基本元素：实体集用矩形表示，属性用椭圆表示。主键属性需加下划线。
关系约束：
- 键约束（映射基数）：通过箭头表示。
  - 一对一：两端都画箭头。
  - 一对多：箭头指向“一”的那一端。
  - 多对多：两端都不画箭头。
- 参与约束：用粗线表示全部参与（每个实体都必须参与关系）。
弱实体集：
- 用双线矩形表示。
- 通过标识性关系（双线菱形）依赖于属主实体集。
- 其主键由属主实体集的主键加上自身的部分键（用虚线标出）共同构成。
高级概念：了解特化/泛化（三角形）和聚合（虚线框）的表示法。

以下是关系模型与SQL实现（第3、5章）的要点：

从ER图到关系模式：根据ER图创建对应的SQL表。
- 通常每个实体集和“多对多”关系都需要单独的表。
- 注意定义外键（FOREIGN KEY ... REFERENCES ...）。
参照完整性操作：理解对主表进行DELETE或UPDATE时，由于外键约束可能引发的拒绝、级联等操作。
SQL查询（第5章）：
- 可能要求用不同方式（如使用集合运算符、嵌套查询、相关子查询等）编写同一查询。
- 或根据给定的表和查询语句，推断查询结果。

问题集三：关系代数与查询求值（第4、12章）

上一节我们探讨了数据库设计，本节中我们来看看数据库查询的数学基础——关系代数及其执行计划。

以下是关系代数（第4章）的核心运算符：

基本运算：
- 选择 σ：对应SQL中的WHERE子句，用于筛选行。公式：σ_{predicate}(R)
- 投影 π：对应SQL中的SELECT子句，用于选择列。公式：π_{A1,A2,...}(R)
- 并 ∪、差 -、笛卡尔积 ×
连接运算：包括等值连接、自然连接等。
除运算 ÷：用于处理“查询所有...”这类问题，例如“查找预订了所有船只的水手”。
聚合运算 γ：
- 单参数：如SUM、AVG。
- 带分组：使用γ运算符，对应SQL的GROUP BY。例如，按船只颜色分组计算水手平均评分：γ_{color, AVG(rating)}(...)

以下是查询求值（第12章）的要点：

查询求值计划：将关系代数表达式转化为具体的执行步骤树（关系代数树），并为每个节点选择具体的算法（如排序、哈希连接）。
查询优化：通过改变代数树的结构（如尽早执行选择σ和投影π操作）来减少中间结果大小，提升查询效率。

问题集四：模式求精与范式（第4章部分）

上一节我们讨论了查询的表示与执行，本节中我们来看看如何优化数据库结构本身——模式求精。

以下是函数依赖与范式的核心考点：

函数依赖：给定属性集间的推导关系。
闭包计算：计算给定函数依赖集下属性集的闭包。
候选键：能唯一标识元组的最小属性集。
范式判断：
- BCNF：每个函数依赖的决定因子都是超键。
- 3NF：每个非主属性都非传递依赖于任何候选键。
分解：
- 无损连接分解：分解后的关系通过自然连接能无损地恢复原关系。
- 依赖保持分解：分解后的函数依赖集能逻辑蕴含原所有依赖。

问题集五：存储与索引（第8章）

上一节我们学习了如何设计好的模式，本节中我们来看看如何高效地存储和访问数据——存储结构与索引技术。

以下是文件组织与索引基础：

记录组织：堆文件、排序文件等。
索引条目替代方案：
- 方案1：索引条目直接包含数据记录。
- 方案2：索引条目包含<键值，记录ID>对。
- 方案3：索引条目包含<键值，记录ID列表>对。
索引分类：
- 聚簇索引：数据记录的物理顺序与键值顺序基本一致。
- 非聚簇索引：数据记录物理顺序与键值顺序无关。
索引类型选择：
- 等值查询：哈希索引通常更高效。
- 范围查询：B+树等树型索引更优。

以下是树型索引结构详解：

ISAM索引：静态树结构，创建后不变。插入可能导致叶子页溢出，形成长溢出链，降低查询效率。
B+树索引：动态平衡树，通过分裂、合并节点保持结构平衡，保证高效的搜索性能。
树结构搜索成本：与树高h成正比。对于有N个叶子页、扇出为f的B+树，树高近似为 h ≈ log_f(N)。

以下是索引设计应用题：

给定一个SQL查询（如：SELECT dept, AVG(sal) FROM Emp GROUP BY dept HAVING AVG(sal) > 50），设计合适的索引。
考虑创建覆盖索引（索引包含查询所需的所有列），以避免回表访问。
对于带GROUP BY和HAVING的查询，索引应包含分组列和聚合计算涉及的列。

在本节课中，我们一起系统复习了数据库管理系统的核心内容，涵盖了从数据模型、ER设计、SQL查询、关系代数、模式范式到存储索引的完整知识体系。请重点理解各章节的核心概念与联系，并熟练运用关系代数和SQL解决查询问题。祝大家期末考试顺利！

posted @ 2026-03-29 09:24 布客飞龙II 阅读(17) 评论(0) 收藏举报

刷新页面返回顶部

龙哥盟

人最大的痛苦就是说一些自己都不相信的话。

PSU-COMPSC431-数据库管理系统笔记-全-

PSU COMPSC431 数据库管理系统笔记（全）

001：数据库管理系统导论 - 第1部分

课程概述与规则

什么是数据库与数据库管理系统

为何需要数据库管理系统

数据模型简介

关系数据模型初探

课后任务与总结

002：数据库管理系统导论 - 第2部分

概述

关系数据模型回顾

数据库的抽象层次

查询与事务

查询语言

事务

数据库管理系统的结构

总结

003：实体关系模型（ER模型）- 第2部分

概述

回顾与课程安排

参与约束

练习：添加约束

弱实体

类层次结构（ISA）

练习：电影行业ER图设计

聚合

概念设计决策总结

总结

004：实体关系模型（ER模型）- 第3部分与关系模型

从ER模型到关系模型

处理“管理”关系与预算属性

保单与家属的弱实体设计

三元关系与聚合

ER模型设计要点回顾

电影行业数据库设计练习

关系模型介绍

基本概念

关系的性质

005：关系模型（续）与SQL编程导论 - 第1部分

概述

ER模型快速回顾

域约束

SQL数据定义语言导论

创建表

插入数据

更新数据

删除数据

修改表结构

删除表

完整性约束

总结

006：SQL编程导论 - 第2部分 🗄️

概述

键约束回顾

连接到MySQL并创建表

插入数据与基本查询

理解与实践外键约束

约束如何生效：错误示例

引用完整性操作选项

总结

007：SQL编程导论 - 第3部分 🗄️

课程公告与回顾

修改表与添加约束

查看表结构

更改存储引擎

添加外键约束

外键约束行为实践

基础查询：选择特定字段

从ER图到关系模式

映射实体集

映射关系集

处理参与约束与弱实体

课堂练习

总结与提醒

008：关系代数 - 第1部分

回顾与课程安排

视图：概念简介

方法一：使用 `OR` 条件

方法二：使用 `UNION` 操作