Database Principle

Computer Science

发布日期: 2024-11-25

更新日期: 2025-01-14

文章字数: 23.5k

阅读次数:

本文基于《Database System Concepts, 7th Ed.》的课本内容，对 SYSU-CSE DCS281 计算机专业课《数据库系统原理》进行较为详尽的知识点总结，涵盖少量实例分析。

第一章数据库系统概述

1.1 数据库应用实例

重点：

了解数据库在各行业中的应用，如企业信息管理、制造业、银行与金融、大学系统、航空公司、电信、网络服务等。

难点：

理解不同应用场景下数据库系统的具体需求和实现方式。

实例分析：

银行与金融：管理客户信息、账户、贷款及交易。
航空公司：处理预订和航班时刻表。
电信：记录通话、短信和数据使用情况，生成月度账单。

1.2 数据与数据库系统

重点：

区分数据与信息，理解数据库系统的定义及其目的。

难点：

理解数据抽象及其在数据库系统中的作用。

实例分析：

数据：符号化的信息，如学生成绩、图片的像素点。
信息：数据的语义解释，如“学生A的数学成绩为90分”。

1.3 数据库系统组件

重点：

数据库系统的组成部分：硬件、软件、数据、用户。

难点：

理解各组件在数据库系统中的角色及其相互关系。

实例分析：

硬件：大容量内存、外存设备（如磁盘、磁带）。
软件：操作系统、DBMS、编程接口。
数据：目标数据与描述数据。
用户：最终用户、应用程序员、系统分析员、数据库管理员（DBA）。

1.4 数据库设计

重点：

理解数据库设计的逻辑设计与物理设计。
理解模式（Schema）与实例（Instance）的区别。

难点：

如何进行有效的数据库模式设计，确保数据的一致性和完整性。

实例分析：

逻辑设计：定义关系模式，如 instructor(ID, name, dept_name, salary)。
物理设计：决定数据在存储介质上的实际存储方式。

1.5 数据定义语言（DDL）与数据操作语言（DML）

重点：

DDL用于定义数据库模式，如创建表。
DML用于访问和操作数据库中的数据，如查询、插入、更新、删除。

难点：

理解DDL与DML的区别及其在实际操作中的应用。

实例分析：

DDL 示例：

CREATE TABLE instructor (
    ID CHAR(5),
    name VARCHAR(20),
    dept_name VARCHAR(20),
    salary NUMERIC(8,2)
);

DML 示例：

SELECT name
FROM instructor
WHERE dept_name = 'Comp. Sci.';

1.6 SQL查询语言

重点：

SQL作为非过程化查询语言的基本语法和使用方法。

难点：

编写复杂的SQL查询，包括多表联接、子查询等。

实例分析：

查询示例：

SELECT name
FROM instructor
WHERE dept_name = 'Physics' AND salary > 90000;

1.7 数据库系统架构

重点：

理解集中式数据库、客户端-服务器架构、并行数据库和分布式数据库的区别。

难点：

选择适合特定应用场景的数据库架构。

实例分析：

三层架构：客户端、应用服务器、数据库服务器。
分布式数据库：支持地理分布的数据存储和访问。

1.8 数据库系统的发展历史

重点：

数据库系统的发展历程，从磁带时代到关系模型，再到现代的NoSQL和大数据系统。

难点：

理解不同历史阶段数据库技术的演进及其驱动因素。

实例分析：

关系模型的提出：E.F. Codd在1970年提出关系模型，奠定了现代关系数据库的基础。
NoSQL系统：如Google BigTable、Amazon Dynamo，适用于大规模分布式数据存储。

第二章关系模型与关系代数

2.1 关系模型基础

重点：

关系模型的三要素：关系、属性、元组。
理解关系数据库的模式与实例。

难点：

理解关系的数学定义及其在数据库中的应用。

实例分析：

关系定义：

关系R定义在D1, D2, …, Dn上的笛卡尔积 D1×D2×…×Dn 的一个命名子集。

示例关系：

student (student_no, sex, name)
-------------------------------
1 | Male | Jones
2 | Male | Smith
3 | Female | Kate

2.2 关系的性质

重点：

关系的基本性质：列同质性、行唯一性、无序性、原子性。

难点：

理解并应用第一范式（1NF）概念。

实例分析：

违反1NF的例子：
A | B | A
------------
1 | "abc" | "c"
满足1NF的例子：
A | B | C
------------
1 | "abc" | "c"

2.3 关键字概念

重点：

主键、外键、候选键、超键的定义与区别。

难点：

理解外键的引用机制，不依赖于属性名称相同。

实例分析：

外键示例：
student (dept_no, student_no, name, sex)
department (dept_no, dept_name)
dept_no在student中作为外键，引用department中的主键dept_no。

2.4 关系代数操作

重点：

关系代数的基本操作：选择（σ）、投影（π）、并（∪）、差（−）、笛卡尔积（×）、重命名（ρ）。

难点：

组合多个关系代数操作以构建复杂查询。

实例分析：

选择操作：
σ dept_name="Physics" (instructor)

投影操作：

π name (σ dept_name="Physics" (instructor))

联合操作：

π course_id (σ semester="Fall" AND year=2017 (section)) 
∪ 
π course_id (σ semester="Spring" AND year=2018 (section))

差集操作：

π course_id (σ semester="Fall" AND year=2017 (section)) 
− 
π course_id (σ semester="Spring" AND year=2018 (section))

笛卡尔积与选择的结合：

σ instructor.id = teaches.id (instructor × teaches)

重命名操作：
ρ boy (σ sex="Male" (student))

2.5 关系操作实例

重点：

理解并能具体化关系代数操作的结果。

难点：

解析复杂关系代数表达式的执行步骤与结果。

实例分析：

删除操作：
R ← R − E
示例：从student中删除满足特定条件的元组。
插入操作：
R ← R ∪ { r }
示例：向student表中插入一个新元组。
全部更新：
r ← π F1, F2, …, Fn (r)
示例：对Emp表中所有职工的工资上调10%。
部分更新：
r ← π F1, F2, …, Fn (σ p(r)) ∪ (r − σ p(r))
示例：仅更新Emp表中姓名为“小张”的职工工资。

2.6 关系代数运算示例解析

重点：

熟练掌握关系代数运算的具体操作及其结果。

难点：

理解运算顺序及其对结果的影响。

实例分析：

例1：
σ 1=1 (student)
结果：返回student表中的所有元组，因为条件始终为真。
例2：
σ 1=2 (student)
结果：返回空集，因为条件始终为假。

2.7 重点与难点总结

重点：

关系模型的基本概念：关系、属性、元组、主键与外键。
关系代数的基本操作：选择、投影、并、差、笛卡尔积、重命名。
数据库设计：逻辑设计与物理设计，模式与实例的区别。
SQL语言：DDL与DML的使用，编写复杂查询。
数据库系统架构与组件：存储管理器、查询处理器、事务管理。

难点：

关系代数的复杂操作：多步操作的组合理解与应用。
数据库设计中的范式：确保数据库设计符合高范式以避免数据冗余。
事务管理与ACID特性：理解事务的原子性、一致性、隔离性与持久性。
SQL的高级查询：子查询、联接操作、优化查询性能。

2.8 关键术语解释

关系（Relation）：一个二维表，由行（元组）和列（属性）组成。
属性（Attribute）：关系中的列，具有唯一的名称和定义良好的域。
元组（Tuple）：关系中的行，代表数据项的一个实例。
主键（Primary Key）：唯一标识关系中每个元组的属性或属性组合。
外键（Foreign Key）：一个关系中的属性或属性组合，用于引用另一个关系的主键。
关系代数（Relational Algebra）：一种过程化查询语言，用于对关系进行操作和查询。
范式（Normal Form）：数据库设计中的规范，用以减少数据冗余和确保数据一致性。
事务（Transaction）：一组操作，要么全部执行成功，要么全部失败，确保数据库的一致性。

2.9 图表与图示描述

由于文本性质，以下为关键图表和图示的文字描述：

数据库系统架构图：
- 分为三个层次：
  - 物理层：描述数据在存储介质上的存储方式。
  - 逻辑层：描述数据库中的数据结构和关系。
  - 视图层：不同用户视角的数据展示，隐藏底层细节。
关系模型示意图：
- 展示一个关系的二维表结构，包括属性名、元组行，以及主键与外键的标识。
关系代数操作流程图：
- 例如，选择操作σ的流程：输入关系 → 应用选择条件 → 输出满足条件的子集关系。
事务管理流程图：
- 展示事务的开始、执行、提交或回滚的过程，确保ACID特性。

2.10 实例分析详解

实例1：关系代数的选择操作

问题描述：

σ dept_name="Physics" AND salary > 90000 (instructor)

步骤解析：

输入关系：instructor表。
选择条件：dept_name等于”Physics”且salary大于90000。
执行操作：遍历instructor表中的每个元组，筛选符合条件的元组。
输出结果：所有属于”Physics”系且工资超过90000的教员信息。

SQL等效语句：

SELECT *
FROM instructor
WHERE dept_name = 'Physics' AND salary > 90000;

实例2：关系代数的联合操作

问题描述：
查找所有在2017年秋季和2018年春季教授的课程。

关系代数表达式：

π course_id (σ semester="Fall" AND year=2017 (section))
∪
π course_id (σ semester="Spring" AND year=2018 (section))

步骤解析：

第一部分：选择semester为”Fall”且year为2017的section表中的course_id。
第二部分：选择semester为”Spring”且year为2018的section表中的course_id。
执行联合操作：将两部分结果合并，去除重复的course_id。
输出结果：所有在指定学期教授过的课程编号。

SQL等效语句：

SELECT course_id
FROM section
WHERE semester = 'Fall' AND year = 2017
UNION
SELECT course_id
FROM section
WHERE semester = 'Spring' AND year = 2018;

实例3：SQL插入操作

问题描述：
向student表中插入一个新学生，学号为4，姓名“小周”，性别“女”。

关系代数表达式：

S ← S ∪ { (4, "小周", "女") }

SQL语句：

INSERT INTO student (student_no, name, sex) VALUES (4, '小周', '女');

执行步骤：

指定目标关系：student表。
定义新元组：学号为4，姓名“小周”，性别“女”。
执行插入操作：将新元组添加到student表中。

结果：
student表新增一条记录：

student_no | name | sex
---------- | ---- | ---
4          | 小周 | 女

实例4：关系代数的笛卡尔积与选择

问题描述：
将instructor与teaches表进行笛卡尔积，并筛选出匹配的教员与课程。

关系代数表达式：

σ instructor.id = teaches.id (instructor × teaches)

步骤解析：

笛卡尔积：生成instructor与teaches表的所有可能行组合。
选择操作：仅保留instructor.id等于teaches.id的元组。
输出结果：教员与其教授课程的对应关系。

SQL等效语句：

SELECT *
FROM instructor
JOIN teaches ON instructor.id = teaches.id;

结果示例：

instructor.ID | instructor.name | teaches.course_id
------------- | --------------- | -----------------
1             | 张三            | CS101
2             | 李四            | CS102

第三章 SQL查询语言概述

3.1 重点

SQL语言的组成：
- 数据定义语言（DDL）：包括创建、修改和删除数据库对象（如表、视图、索引等）。
- 数据操纵语言（DML）：用于查询、插入、更新和删除数据。
- 数据控制语言（DCL）：用于授权和管理用户权限。
SQL数据类型与模式：
- 常用数据类型包括char(n)、varchar(n)、int、smallint、numeric(p,d)、real、double precision、float(n)等。
- 模式定义了数据库中各关系的结构，包括属性名、数据类型、完整性约束等。
SQL语句结构：
- SELECT语句：包含SELECT、FROM、WHERE、GROUP BY、HAVING、ORDER BY等子句。
- JOIN操作：包括自然连接、内连接和外连接，理解不同类型的连接及其应用。
完整性约束：
- 实体完整性：主键不允许为NULL，确保每个元组的唯一性。
- 参照完整性：外键必须引用另一个关系的主键或唯一键。
- 用户定义完整性：通过CHECK约束等自定义规则确保数据的合理性。

3.2 难点

子查询与嵌套查询：理解相关子查询与非相关子查询的区别及其在SELECT、WHERE、FROM子句中的应用。
复杂连接操作：掌握多表连接、不同类型的外连接及其在实际问题中的应用，如避免错误的自然连接导致的数据失配。
聚集函数与分组：正确使用GROUP BY和HAVING子句，理解聚集函数的作用及其在查询优化中的重要性。
视图的创建与管理：理解视图的本质、创建复杂视图的条件以及视图的更新限制。

3.3 实例分析

例1：查找所有导师的姓名

SELECT name
FROM instructor;

解答：
该查询语句选择instructor关系中的name属性，返回所有导师的姓名。

例2：查找计算机科学系的导师姓名和课程编号

SELECT name, course_id
FROM instructor, teaches
WHERE instructor.ID = teaches.ID
  AND instructor.dept_name = 'Comp. Sci.';

解答：
该查询通过连接instructor和teaches关系，筛选计算机科学系的导师，并返回他们的姓名及所教授的课程编号。

例3：删除数学成绩不及格的学生选修信息

DELETE FROM takes
WHERE grade < '60';

解答：
该语句从takes关系中删除所有成绩低于60的记录，假设60为及格线。

例4：增加所有导师的薪水5%

UPDATE instructor
SET salary = salary * 1.05;

解答：
该语句将instructor关系中所有导师的薪水增加5%。

例5：使用子查询查找平均薪水以上的导师

SELECT name
FROM instructor
WHERE salary > (SELECT AVG(salary) FROM instructor);

解答：
该查询返回薪水高于所有导师平均薪水的导师姓名。

例6：创建视图显示不包含薪水的导师信息

CREATE VIEW faculty AS
SELECT ID, name, dept_name
FROM instructor;

解答：
该视图faculty仅包含导师的ID、name和dept_name，隐藏了salary属性，提高了数据的安全性。

例7：使用JOIN操作查找学生及其班主任

假设有两个关系：

Stu（学生）
Class（班级）

SELECT Stu.name, Class.ban_zhuren
FROM Stu
JOIN Class ON Stu.ban_hao = Class.ban_hao;

解答：
该查询通过JOIN操作连接Stu和Class关系，返回每个学生的姓名及其对应的班主任。

第四章访问SQL与触发器

4.1 重点

访问SQL的两种方法：
- 动态SQL：在运行时生成和执行SQL语句，适合需要根据条件动态改变查询的场景。
- 嵌入式SQL：将SQL语句嵌入到一般编程语言中，编译时转换为函数调用，提高执行效率。
JDBC（Java数据库连接）：
- 核心操作流程：加载驱动、建立连接、创建语句、执行查询、处理结果、关闭连接。
- 特点：跨平台、支持对象-关系映射、网络独立性、数据库独立性等。
完整性约束与事务管理：
- 事务的ACID特性：原子性、一致性、隔离性、持久性。
- 事务控制语句：BEGIN TRANSACTION、COMMIT、ROLLBACK等。
触发器：
- 定义与作用：在特定事件（如INSERT、UPDATE、DELETE）发生时自动执行的存储过程。
- 类型：AFTER触发器、INSTEAD OF触发器。
- 应用实例：维护数据一致性、自动更新相关表、日志记录等。

4.2 难点

触发器的设计与实现：如何正确设计触发器的触发条件和执行动作，避免递归触发和性能问题。
事务的并发控制与隔离级别：理解不同隔离级别（如读未提交、读已提交、可重复读、串行化）的特点及其对数据库性能与一致性的影响。
JDBC的高级特性：如批处理、预编译语句、元数据获取等，需要深入理解其API的使用方法和最佳实践。
权限管理与安全性：正确配置用户权限，理解角色的创建与分配，以及视图在数据安全中的应用。

4.3 实例分析

例1：使用JDBC连接数据库并查询导师信息

public static void JDBCexample(String dbid, String userid, String passwd) {
    try (Connection conn = DriverManager.getConnection(
            "jdbc:oracle:thin:@db.yale.edu:2000:univdb", userid, passwd);
         Statement stmt = conn.createStatement()) {
        // 执行查询
        ResultSet rs = stmt.executeQuery("SELECT * FROM instructor");
        while (rs.next()) {
            System.out.println(rs.getString("name") + " " + rs.getFloat("salary"));
        }
    } catch (SQLException sqle) {
        System.out.println("SQLException : " + sqle);
    }
}

解答：
该Java方法使用JDBC连接到Oracle数据库，查询instructor关系中的所有记录，并输出导师姓名和薪水。使用了try-with-resources语法确保资源自动关闭。

例2：创建一个AFTER触发器，更新学生总学分

CREATE TRIGGER credits_earned
AFTER UPDATE OF takes.grade
ON takes
REFERENCING NEW ROW AS nrow OLD ROW AS orow
FOR EACH ROW
WHEN (nrow.grade <> 'F' AND nrow.grade IS NOT NULL AND (orow.grade = 'F' OR orow.grade IS NULL))
BEGIN ATOMIC
    UPDATE student
    SET tot_cred = tot_cred +
        (SELECT credits FROM course WHERE course.course_id = nrow.course_id)
    WHERE student.id = nrow.id;
END;

解答：
该触发器在takes关系的grade属性更新后执行。当新成绩不为F且不为空，且原成绩为F或为空时，触发器将相应课程的学分加到学生的总学分tot_cred中，确保学生的学分信息实时更新。

例3：使用JDBC执行INSERT操作并处理潜在的SQL注入风险

String sql = "INSERT INTO instructor (ID, name, dept_name, salary) VALUES (?, ?, ?, ?)";
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setString(1, "88877");
pstmt.setString(2, "Perry");
pstmt.setString(3, "Finance");
pstmt.setInt(4, 125000);
pstmt.executeUpdate();

解答：
该Java代码使用预编译语句PreparedStatement插入新导师记录，避免了通过字符串拼接可能引发的SQL注入风险。确保了数据的安全性和完整性。

4.4 第三章与第四章中提及的重要图示描述

视图的依赖关系图：
- 说明：图中展示了视图之间的依赖关系，如视图physics_fall_2017_watson依赖于视图physics_fall_2017，而physics_fall_2017又依赖于基础关系course和section。
- 关键点：展示了视图层次结构，强调视图定义的递归展开过程，确保视图定义不是递归的以避免无限循环。
触发器执行过程图：
- 说明：图示展示了当执行INSERT、UPDATE或DELETE操作时，系统如何创建inserted和deleted临时表，并在触发器中使用这些表进行相应的数据操作。
- 关键点：强调触发器在特定事件发生时自动执行，并利用临时表维护数据的前后状态。
JDBC操作流程图：
- 说明：图中展示了JDBC的典型操作流程，包括加载驱动、建立连接、创建语句、执行查询、处理结果集以及关闭连接的步骤。
- 关键点：帮助理解JDBC在Java应用中与数据库交互的整体流程，提高编程实践的准确性。
联合查询与子查询示意图：
- 说明：图示展示了如何通过JOIN操作和嵌套SELECT子查询在多个关系间进行数据整合。
- 关键点：帮助理解复杂查询的结构和执行顺序，增强对多表联结与子查询逻辑的掌握。

第五章高级SQL

5.1 重点

SQL与编程语言的集成：
- 理解如何通过不同的编程语言（如Java、Python、PHP等）访问和操作数据库。
JDBC的使用：
- 掌握Java应用程序中使用JDBC连接数据库、执行SQL语句以及处理结果集的流程。
函数与存储过程：
- 理解数据库中的函数和存储过程的定义与调用，掌握其在业务逻辑封装中的应用。
触发器的设计与应用：
- 学会设计DML触发器（AFTER和INSTEAD OF），并理解其在自动执行副作用操作中的作用。

5.2 难点

事务管理与连接池：
- 理解事务的概念，掌握如何在应用程序中正确管理事务，避免数据不一致。
安全性问题：
- 理解SQL注入攻击的原理及防范措施，如使用预编译语句（Prepared Statements）。
元数据的使用：
- 熟悉如何通过JDBC获取数据库及结果集的元数据，以动态处理不同的数据库结构。

5.3 实例分析

例1：JDBC 连接与查询

任务： 使用JDBC连接到SQL Server数据库，执行查询并处理结果集。

示例代码：

import java.sql.*;

public class JDBCExample {
    public static void main(String[] args) {
        String dbURL = "jdbc:sqlserver://localhost:1433;databaseName=univdb";
        String user = "userid";
        String password = "passwd";

        try (Connection conn = DriverManager.getConnection(dbURL, user, password);
             Statement stmt = conn.createStatement()) {

            String sql = "SELECT dept_name, AVG(salary) as avg_salary FROM instructor GROUP BY dept_name";
            ResultSet rs = stmt.executeQuery(sql);

            while (rs.next()) {
                String deptName = rs.getString("dept_name");
                float avgSalary = rs.getFloat("avg_salary");
                System.out.println(deptName + " " + avgSalary);
            }

            rs.close();
        } catch (SQLException e) {
            System.out.println("SQLException: " + e.getMessage());
        }
    }
}

说明：

使用DriverManager.getConnection建立连接。
创建Statement对象执行SQL查询。
使用ResultSet处理查询结果。

例2：使用预编译语句防止SQL注入

任务： 安全地向instructor表插入数据，防止SQL注入攻击。

示例代码：

import java.sql.*;

public class SecureInsertExample {
    public static void main(String[] args) {
        String dbURL = "jdbc:sqlserver://localhost:1433;databaseName=univdb";
        String user = "userid";
        String password = "passwd";

        String insertSQL = "INSERT INTO instructor (ID, name, dept_name, salary) VALUES (?, ?, ?, ?)";

        try (Connection conn = DriverManager.getConnection(dbURL, user, password);
             PreparedStatement pstmt = conn.prepareStatement(insertSQL)) {

            // 设置参数
            pstmt.setString(1, "88877");
            pstmt.setString(2, "Perry");
            pstmt.setString(3, "Finance");
            pstmt.setInt(4, 125000);

            // 执行插入
            pstmt.executeUpdate();

            // 插入另一条记录
            pstmt.setString(1, "88878");
            pstmt.setString(2, "John");
            pstmt.setString(3, "Marketing");
            pstmt.setInt(4, 110000);
            pstmt.executeUpdate();

            System.out.println("数据插入成功。");
        } catch (SQLException e) {
            System.out.println("SQLException: " + e.getMessage());
        }
    }
}

说明：

使用PreparedStatement预编译SQL语句，防止恶意用户通过输入构造SQL注入攻击。
通过set方法动态设置参数值。

例3：触发器的创建与使用

任务： 为student表创建一个AFTER INSERT触发器，更新student_sum表中的学生总数。

示例SQL代码：

-- 创建student表
CREATE TABLE student (
    stu_id INT IDENTITY(1,1) PRIMARY KEY,
    stu_name VARCHAR(10),
    stu_gender CHAR(2),
    stu_age INT
);

-- 创建student_sum表
CREATE TABLE student_sum (
    stuCount INT DEFAULT(0)
);

-- 创建AFTER INSERT触发器
CREATE TRIGGER trig_insert
ON student
AFTER INSERT
AS
BEGIN
    DECLARE @stuNumber INT;
    SELECT @stuNumber = COUNT(*) FROM student;

    IF NOT EXISTS (SELECT * FROM student_sum)
        INSERT INTO student_sum VALUES (0);

    UPDATE student_sum SET stuCount = @stuNumber;
END;

-- 创建删除触发器
CREATE TRIGGER trig_delete
ON student
AFTER DELETE
AS
BEGIN
    SELECT stu_id AS 已删除的学生编号, stu_name, stu_gender, stu_age
    FROM deleted;
END;

-- 创建更新触发器
CREATE TRIGGER trig_update
ON student
AFTER UPDATE
AS
BEGIN
    DECLARE @stuCount INT;
    SELECT @stuCount = COUNT(*) FROM student;
    UPDATE student_sum SET stuCount = @stuCount;

    SELECT stu_id AS 更新前学生编号, stu_name AS 更新前学生姓名 FROM deleted;
    SELECT stu_id AS 更新后学生编号, stu_name AS 更新后学生姓名 FROM inserted;
END;

说明：

触发器trig_insert在向student表插入新记录后执行，更新student_sum表中的学生总数。
触发器trig_delete在删除student表中的记录后执行，显示已删除的学生信息。
触发器trig_update在更新student表中的记录后执行，更新student_sum表并显示更新前后的学生信息。

注意事项：

确保在SQL Server中执行上述SQL语句时，具有相应的权限。
触发器中的deleted和inserted表是SQL Server中特殊的临时表，用于存储被删除和插入的元组。

4.4 第三章与第四章中提及的重要图示描述

视图的依赖关系图：
- 说明：图中展示了视图之间的依赖关系，如视图physics_fall_2017_watson依赖于视图physics_fall_2017，而physics_fall_2017又依赖于基础关系course和section。
- 关键点：展示了视图层次结构，强调视图定义的递归展开过程，确保视图定义不是递归的以避免无限循环。
触发器执行过程图：
- 说明：图示展示了当执行INSERT、UPDATE或DELETE操作时，系统如何创建inserted和deleted临时表，并在触发器中使用这些表进行相应的数据操作。
- 关键点：强调触发器在特定事件发生时自动执行，并利用临时表维护数据的前后状态。
JDBC操作流程图：
- 说明：图中展示了JDBC的典型操作流程，包括加载驱动、建立连接、创建语句、执行查询、处理结果集以及关闭连接的步骤。
- 关键点：帮助理解JDBC在Java应用中与数据库交互的整体流程，提高编程实践的准确性。
联合查询与子查询示意图：
- 说明：图示展示了如何通过JOIN操作和嵌套SELECT子查询在多个关系间进行数据整合。
- 关键点：帮助理解复杂查询的结构和执行顺序，增强对多表联结与子查询逻辑的掌握。

第六章基于E-R模型的数据库设计

6.1 重点

实体-关系模型（E-R模型）：理解实体、属性、关系及其在数据库设计中的应用。
属性类型：掌握简单属性、复合属性、多值属性和派生属性的区别及其表示方法。
关系的基数约束：熟悉一对一、一对多、多对多等关系的特点及其在E-R图中的表示。
主键：了解实体集和关系集的主键选择及其在唯一标识实体中的作用。
弱实体集与标识关系：掌握弱实体集的定义及其与标识实体集之间的关系。

6.2 难点

E-R图到关系模式的转换：如何准确地将E-R图中的各个元素映射到关系数据库的模式中。
处理多值属性：理解如何通过分离关系来消除多值属性带来的冗余。
聚合与泛化/特化：掌握在E-R模型中表示复杂关系和层次结构的方法。
设计规范化：了解如何通过规范化过程消除冗余和避免更新异常。
依赖约束的保持与无损分解：理解在关系模式分解过程中，如何保持功能依赖以及确保分解的无损性。

6.3 主要内容总结

1. 数据库设计过程概述

数据库设计过程通常包括以下几个阶段：

需求分析：全面描述未来数据库用户的需求。
概念设计：选择合适的数据模型（通常是E-R模型）并将需求转化为数据库的概念模式。
逻辑设计：将概念模式转换为特定的数据模型（如关系模型）的逻辑模式。
物理设计：决定数据库的物理存储结构和优化性能。

2. 实体-关系模型（E-R模型）

E-R模型用于概念化地描述数据库的结构，主要包括以下三个基本概念：

实体集（Entity Set）：表示具有相同属性的一类对象，如“学生”、“教师”。
属性（Attribute）：描述实体集特征的字段，如“姓名”、“学号”。属性可以分为简单属性、复合属性、多值属性和派生属性。
关系集（Relationship Set）：表示实体集之间的关联，如“学生选课”关系。

2.1 实体集与属性

实体：可以独立存在并具有唯一标识的对象。
主键（Primary Key）：用于唯一标识实体集中的每一个实体，如“学生”的学号。

2.2 属性类型

简单属性：不可再分的属性，如“年龄”。
复合属性：可以分解为更小的子属性，如“姓名”可以分为“名字”和“姓氏”。
多值属性：一个实体可以有多个此类属性的值，如“电话号码”。
派生属性：可以由其他属性计算得到，如“年龄”可以由“出生日期”计算得出。

2.3 关系集与基数约束

一对一（1:1）关系：一个实体集中的一个实体只与另一个实体集中的一个实体相关联。
一对多（1:N）关系：一个实体集中的一个实体可以与另一个实体集中的多个实体相关联，但反之不成立。
多对多（M:N）关系：两个实体集中的多个实体可以相互关联。

2.4 弱实体集与标识关系

弱实体集（Weak Entity Set）：其存在依赖于另一个强实体集，通过标识关系进行标识。例如，“选课”实体集可能依赖于“学生”和“课程”实体集。
标识关系（Identifying Relationship）：连接弱实体集与其标识强实体集的关系。

3. 从E-R图到关系模式的转换

将E-R图转换为关系模式时，需遵循以下步骤：

实体集转换：每个强实体集转换为一个关系，属性成为关系的字段，主键作为关系主键。
弱实体集转换：将弱实体集转换为关系，包含其标识实体集的主键作为外键，并结合自身的部分主键。
关系集转换：
- 多对多关系：创建一个新的关系，包含两个参与实体集的主键作为外键。
- 一对多或多对一关系：在“多”侧的关系中添加“一”侧的外键。
- 一对一关系：可以选择任一侧添加对方的外键。

3.1 处理多值属性

多值属性通过创建新的关系来消除冗余。例如，“教师”的“电话号码”可以通过一个单独的关系 教师电话（教师ID，电话） 来表示。

3.2 复合属性的展开

复合属性被拆解为更小的简单属性。例如，“地址”可以拆分为“街道”、“城市”、“州”、“邮编”。

4. 扩展的E-R特性

4.1 泛化与特化（Generalization/Specialization）

泛化：将多个具体实体集抽象为一个通用的高层实体集。
特化：将一个通用实体集细分为多个具体的低层实体集。

4.2 聚合（Aggregation）

聚合用于表示关系集之间的关系，将一个或多个关系集视为一个整体来参与其他关系集。

5. 数据库设计中的常见问题与解决方案

5.1 冗余与更新异常

冗余：信息重复存储，可能导致数据不一致。
更新异常：如插入、删除、修改操作可能引发数据不一致。

5.2 规范化

通过规范化过程（如达到第三范式、BCNF等），消除冗余，避免更新异常。

6. 替代符号表示

不同的E-R图符号如Chen符号、Crow’s Feet符号以及UML类图，有助于不同场景下的数据库设计和表示。

6.4 实例分析

实例1：大学信息维护

需求描述

一所大学需要维护以下信息：

各个系，包括名称、系主任和地址。
各个班级，包括班级编号、名字和年级。
各个教师，包括教师编号、姓名和年龄。
一个系有多个班级，但班级只属于一个系。
一个系聘请某些教师，一个教师被一个系聘请。
教师最多是一个班的班主任；任何班级都必须有一个班主任。

设计过程

实体集定义
- 系（Department）：属性包括 dept_name（主键）、head、address。
- 班级（Class）：属性包括 class_id（主键）、name、grade。
- 教师（Teacher）：属性包括 teacher_id（主键）、name、age。
关系集定义
- 隶属（Affiliation）：一对多关系，连接 Department 和 Class。一个系可有多个班级，但班级只能属于一个系。
- 聘请（Employment）：一对多关系，连接 Department 和 Teacher。一个系聘请多个教师，一个教师被一个系聘请。
- 班主任（Advisor）：一对一关系，连接 Teacher 和 Class。一个教师最多担任一个班主任，一个班级必须有一个班主任。
关系模式转换
- Department(dept_name [PK], head, address)
- Class(class_id [PK], name, grade, dept_name [FK])
- Teacher(teacher_id [PK], name, age, dept_name [FK])
- Advisor(teacher_id [PK, FK], class_id [FK])

SQL语句示例
创建上述关系模式的SQL Server语句如下：

CREATE TABLE Department (
    dept_name VARCHAR(50) PRIMARY KEY,
    head VARCHAR(50),
    address VARCHAR(100)
);

CREATE TABLE Teacher (
    teacher_id INT PRIMARY KEY,
    name VARCHAR(50),
    age INT,
    dept_name VARCHAR(50),
    FOREIGN KEY (dept_name) REFERENCES Department(dept_name)
);

CREATE TABLE Class (
    class_id INT PRIMARY KEY,
    name VARCHAR(50),
    grade INT,
    dept_name VARCHAR(50),
    FOREIGN KEY (dept_name) REFERENCES Department(dept_name)
);

CREATE TABLE Advisor (
    teacher_id INT PRIMARY KEY,
    class_id INT,
    FOREIGN KEY (teacher_id) REFERENCES Teacher(teacher_id),
    FOREIGN KEY (class_id) REFERENCES Class(class_id)
);

重点难点解析

班主任关系的处理：采用一对一关系，需要在 Advisor 表中将 teacher_id 和 class_id 设置为外键，并分别引用 Teacher 和 Class 表。
避免冗余：Class 和 Teacher 表中的 dept_name 作为外键避免了信息重复存储。

实例2：处理多值属性

需求描述

记录教师的多个电话号码和多个子女姓名。

设计过程

由于多值属性会导致数据冗余，需通过创建新的关系来处理。

实体集定义
- 教师（Teacher）：属性包括 teacher_id（主键）、name、age。
多值属性的处理
- 教师电话（Teacher_Phone）：teacher_id（外键）、phone_number。
- 教师子女（Teacher_Child）：teacher_id（外键）、child_name。
关系模式转换
- Teacher(teacher_id [PK], name, age)
- Teacher_Phone(teacher_id [FK], phone_number)
- Teacher_Child(teacher_id [FK], child_name)

SQL语句示例

CREATE TABLE Teacher (
    teacher_id INT PRIMARY KEY,
    name VARCHAR(50),
    age INT
);

CREATE TABLE Teacher_Phone (
    teacher_id INT,
    phone_number VARCHAR(20),
    FOREIGN KEY (teacher_id) REFERENCES Teacher(teacher_id)
);

CREATE TABLE Teacher_Child (
    teacher_id INT,
    child_name VARCHAR(50),
    FOREIGN KEY (teacher_id) REFERENCES Teacher(teacher_id)
);

重点难点解析

多值属性的分离：通过创建 Teacher_Phone 和 Teacher_Child 表，将教师的多个电话号码和子女姓名分别存储，避免冗余。
保持关系的完整性：使用外键引用确保每个电话号码和子女姓名都关联到一个有效的教师。

6.5 图表与图示

1. E-R图的图形表示

实体集与属性

实体集用矩形表示，内部列出其属性，主键属性下划线表示。
复合属性用带有分支的椭圆表示，分支连接到子属性。
多值属性用双椭圆表示。

关系集与基数约束

关系集用菱形表示，连接参与的实体集。
一对一关系在E-R图中通过双向箭头表示。
一对多关系通过一端的单向箭头和多端的多向标记（如叉形脚）表示。
多对多关系通过双向箭头连线表示。

弱实体集与标识关系

弱实体集用双矩形表示，属性用双椭圆表示。
标识关系集用双菱形表示，连接弱实体集与强实体集。

2. 从E-R图到关系模式的转换示意

原始E-R图示例：

[Department]---<Affiliation>---[Class]
[Department]---<Employment>---[Teacher]
[Teacher]---<Advisor>---[Class]

转换后的关系模式：

1. Department(dept_name [PK], head, address)
2. Class(class_id [PK], name, grade, dept_name [FK])
3. Teacher(teacher_id [PK], name, age, dept_name [FK])
4. Advisor(teacher_id [PK, FK], class_id [FK])

6.6 总结

数据库设计是一个系统性工程，需要从需求分析开始，经过概念设计、逻辑设计到物理设计的多个阶段。在E-R模型的帮助下，可以清晰地描述实体、属性和它们之间的关系。通过将E-R图转换为关系模式，并应用规范化理论，可以有效地消除冗余、避免更新异常，提升数据库的整体质量。

6.7 建议

深入理解E-R模型：通过实践绘制E-R图，掌握不同关系的表示方法。
多练习E-R图到关系模式的转换：理解各种关系的映射规则，特别是处理多值属性和弱实体集时的细节。
掌握规范化理论：学习不同范式的定义及其目的，应用规范化过程优化数据库设计。
使用专业工具辅助设计：利用数据库设计工具（如ERwin、PowerDesigner）绘制E-R图，提高设计效率和准确性。

第七章标准化

7.1 关系数据库设计中的问题

重点难点：

重点： 理解关系数据库设计中的数据冗余和异常问题。
难点： 识别不同类型的异常（更新异常、插入异常、删除异常）及其产生原因。

内容摘要：

数据冗余： 通过关系的自然连接（如instructor和department的自然连接in_dep），可能导致信息的重复存储。
异常问题：
- 更新异常： 部门经理变更时需要更新多个元组，容易出错。
- 插入异常： 新部门无员工时，无法插入该部门的信息。
- 删除异常： 删除部门所有员工后，失去部门经理的信息。

7.2 不良数据依赖与关系分解

重点难点：

重点： 理解函数依赖的概念及其在关系数据库设计中的应用。
难点： 辨别“好”与“不好”的函数依赖，以及进行关系分解以消除不良依赖。

内容摘要：

函数依赖（Functional Dependency, FD）： 若属性集α决定属性集β，即α → β，则在任何合法实例中，若两个元组在α上相同，则在β上也相同。
不良函数依赖： 例如branch → manager，当branch不是超级键时，会导致数据重复和异常。
关系分解： 将“坏”关系分解为多个“好”关系以消除不良依赖。需要确保分解是无损连接（lossless-join）并尽可能保持依赖性（dependency preserving）。

实例分析：

关系 worker(name, branch, manager) 存在的函数依赖：
- name → branch
- branch → manager
- name → manager
问题： branch → manager 不是良好的依赖，因为branch不是超级键，导致信息冗余。
分解方案：
- worker1(name, branch)
- branch(branch, manager)
这种分解消除了冗余，避免了更新、插入和删除异常。

7.3 正规化与范式

重点难点：

重点： 理解不同范式（第一范式1NF，第二范式2NF，第三范式3NF，BCNF，第四范式4NF）的定义及其重要性。
难点： 识别关系是否满足特定范式，以及进行规范化分解。

内容摘要：

第一范式（1NF）： 所有属性值必须是原子性的，不能再分。
第二范式（2NF）： 满足1NF且每个非主属性完全依赖于候选键，消除部分依赖。
第三范式（3NF）： 满足2NF且没有传递依赖，消除传递依赖。
BCNF（Boyce-Codd Normal Form）： 满足3NF且每个决定因素都是超级键。
第四范式（4NF）： 满足BCNF且没有多值依赖。

实例分析：

关系 student(course_id, title, dept_name, credits, sec_id, semester, year, building, room_number, capacity, time_slot_id) 存在的函数依赖：
- course_id → title, dept_name, credits
- building, room_number → capacity
- course_id, sec_id, semester, year → building, room_number, time_slot_id
分解至BCNF：
- course(course_id, title, dept_name, credits)
- classroom(building, room_number, capacity)
- section(course_id, sec_id, semester, year, building, room_number, time_slot_id)
各个分解后的关系满足BCNF，消除了函数依赖导致的问题。

7.4 规范化算法与依赖闭包

重点难点：

重点： 掌握属性闭包（Attribute Closure）及其在确定函数依赖和候选键中的应用。
难点： 计算函数依赖的闭包，理解规范化过程中的依赖保留和无损连接。

内容摘要：

属性闭包（α+）： 在给定一组函数依赖F的情况下，属性集α的闭包是由α决定的所有属性的集合。
候选键： 最小的超级键，能够唯一标识关系中的元组。
规范化算法：
- 算法步骤：
  1. 确定所有函数依赖的闭包。
  2. 分解关系，确保每个分解后的关系满足目标范式（如3NF、BCNF）。
  3. 确保分解是无损连接，并尽可能保留函数依赖。

实例分析：

计算属性闭包：
- 关系 R(A, B, C, G, H, I)
- 函数依赖集 F = {A → B, A → C, CG → H, CG → I, B → H}
- 计算 (AG)+：
  1. 初始：AG
  2. A → B 和 A → C，得到 AGBC
  3. CG → H 和 CG → I，得到 AGBCHI
  - 结论： (AG)+ 包含所有属性，因此AG是超级键。

7.5 依赖与范式的深入理解

重点难点：

重点： 了解函数依赖的规范形式（如规范覆盖Canonical Cover），以及在规范化过程中的应用。
难点： 理解并应用规范覆盖的简化规则，进行关系的规范化分解。

内容摘要：

规范覆盖（Canonical Cover）： 函数依赖集的简化形式，消除冗余和多余的属性。
规范覆盖的计算步骤：
1. 分解右侧多个属性的函数依赖为单属性依赖。
2. 消除左侧多余的属性。
3. 合并具有相同左侧的函数依赖。
依赖保留（Dependency Preservation）： 分解后的关系能够保留原来的所有函数依赖，便于在不进行连接操作的情况下进行依赖的验证。

实例分析：

计算规范覆盖：
- 关系 R(A, B, C)
- 函数依赖集 F = {A → BC, B → C, A → B, AB → C}
- 步骤：
  1. 合并相同左侧的依赖：A → BC 和 A → B 简化为 A → BC
  2. 删除AB → C，因为已由A → B和B → C通过传递性推导出。
  - 规范覆盖： Fc = {A → B, B → C}

7.6 无损连接与依赖保留的算法

重点难点：

重点： 掌握无损连接分解的判定条件及依赖是否保留的算法。
难点： 理解并应用算法判断分解是否无损连接和依赖保留。

内容摘要：

无损连接分解（Lossless-Join Decomposition）： 分解后通过自然连接能够恢复原始关系，不丢失信息。
- 判定条件： 当分解的关系R1和R2的交集R1 ∩ R2是R1或R2的超级键时，分解是无损的。
依赖保留判定：
- 通过检查分解后的所有关系的函数依赖集的并集是否能够推导出原关系的所有函数依赖。

实例分析：

无损连接分解：
- 关系 R(A, B, C)
- 分解为 R1(A, B) 和 R2(B, C)
- 交集R1 ∩ R2 = {B}，且B → C，因此分解无损。
依赖保留判定：
- 分解后，R1包含A → B，R2包含B → C，两者的并集等于规范覆盖Fc = {A → B, B → C}，因此依赖保留。

7.7 多值依赖与第四范式（4NF）

重点难点：

重点： 理解多值依赖（Multivalued Dependency）的概念及其在规范化中的作用。
难点： 识别多值依赖并进行符合第四范式的分解。

内容摘要：

多值依赖（Multivalued Dependency, MVD）： 若属性集α决定属性集β，且β与其他非相关属性独立，记作α →→ β。
第四范式（4NF）： 满足BCNF，且不存在任何非平凡的多值依赖，即每个多值依赖的决定因素都是超级键。
多值依赖的分解： 将关系分解为多个关系，每个关系消除一个多值依赖，确保分解后的关系在4NF。

实例分析：

关系 inst_info(ID, child_name, phone_number) 存在的多值依赖：
- ID →→ child_name
- ID →→ phone_number
分解为：
- inst_child(ID, child_name)
- inst_phone(ID, phone_number)
这样分解后的关系均满足4NF，消除了多值依赖引起的冗余。

7.8 规范化注意事项与高级范式

重点难点：

重点： 理解更高级范式（如第五范式PJNF）及其在特定情况下的应用。
难点： 处理复杂的多值依赖和连接依赖，理解规范化与性能优化之间的权衡。

内容摘要：

第五范式（PJNF）： 在多值依赖基础上，进一步消除连接依赖，确保关系满足所有可能的投影-连接条件。
规范化的实际应用：
- 在实际设计中，常常在BCNF和3NF之间权衡，考虑依赖保留和性能。
- 对于复杂依赖，通常采用4NF或更高级范式以消除冗余。

实例分析：

关系 dept_advisor(s_ID, i_ID, dept_name)
- 函数依赖集 F = {i_ID → dept_name, s_ID, dept_name → i_ID}
- 分解后无法同时满足BCNF和依赖保留，需在应用中权衡选择。

7.9 综合实例与规范化总结

重点难点：

重点： 综合运用所学知识进行实际关系的规范化。
难点： 在实际应用中识别和解决复杂的函数依赖和多值依赖问题，确保分解的正确性（无损和依赖保留）。

内容摘要：

规范化的目标：
- 无损连接（Lossless-Join）： 确保分解后可以通过连接恢复原始关系。
- 依赖保留（Dependency Preservation）： 确保所有原始函数依赖可以在分解后的关系中得到验证。
规范化过程：
1. 确定所有函数依赖及多值依赖。
2. 按照范式规则进行分解。
3. 验证分解是否满足无损连接和依赖保留。

实例分析：

关系 cust_banker_branch(customer_id, employee_id, branch_name, type)
- 规范覆盖 Fc = {customer_id, employee_id → type, employee_id → branch_name, customer_id, branch_name → employee_id}
- 分解为：
  - (customer_id, employee_id, type)
  - (employee_id, branch_name)
  - (customer_id, branch_name, employee_id)
- 删除子集关系，最终得到：
  - (customer_id, employee_id, type)
  - (customer_id, branch_name, employee_id)
这样的分解满足3NF，保持了无损连接和依赖保留。

7.10 多值依赖与进一步规范化（7.117 - 7.140）

重点难点：

重点： 深入理解多值依赖在规范化过程中的应用，特别是第四范式（4NF）。
难点： 处理属性间复杂的多值依赖，确保分解后的关系结构合理且无冗余。

内容摘要：

合并与分解策略：
- 对包含多值依赖的关系进行分解，确保每个分解后的关系仅有单一或无多值依赖。
- 使用算法识别非平凡多值依赖并进行适当的分解。
第四范式（4NF）详细定义：
- 若R满足BCNF，并且对于每个R中的多值依赖α →→ β，α是R的超级键，则R在4NF中。

实例分析：

关系 R(A, B, C, G, H, I) 存在的多值依赖：
- A →→ B
- B →→ HI
- CG →→ H
分解步骤：
1. 分解为R1(A, B) 和 R2(A, C, G, H, I)
2. R2仍不满足4NF，继续分解为R3(C, G, H) 和 R4(A, C, G, I)
3. R4再次分解为R5(A, I) 和 R6(A, C, G)
最终分解后的关系均满足4NF，消除了所有非平凡多值依赖。

7.11 方法论与最佳实践

重点难点：

重点： 了解如何在实际数据库设计中应用规范化原则与方法。
难点： 在复杂的实际场景中平衡规范化与性能需求，避免信息丢失和确保数据完整性。

内容摘要：

规范化设计的目标：
- 合理消除冗余，避免数据异常。
- 保持分解关系的可恢复性和依赖保留性。
最佳实践：
- 在设计早期进行规范化，确保关系结构合理。
- 对于性能敏感的应用场景，可以适当进行非规范化处理，如创建物化视图（Materialized Views）以提升查询效率。
- 避免在数据库设计中使用交叉表（Crosstab）的方式，因其不利于数据的灵活查询和维护。

实例分析：

关系inst_child(ID, child_name) 和 inst_phone(ID, phone_number)
- 通过分解消除重复和多值依赖，确保每个关系满足4NF。
- 保持数据的独立性和完整性，避免冗余信息。

7.12 总结与复习要点

函数依赖和多值依赖： 理解其定义和在规范化中的作用。
范式的层次与要求： 按照1NF到4NF逐步规范化，理解每个范式消除的冗余类型。
关系分解原则： 确保分解是无损连接且依赖保留。
算法应用： 熟练运用属性闭包、规范覆盖等算法进行关系规范化。
实际应用： 在实际设计中合理应用规范化原则，平衡规范化与性能需求。

学习建议：

多做练习： 通过大量实例练习，巩固函数依赖和范式的理解。
理解而非记忆： 深入理解规范化的原理，避免死记硬背。
实际应用： 尝试在实际数据库设计项目中应用所学知识，提升实践能力。

7.13 附录：SQL Server环境下的示例SQL语句

示例1：创建和分解worker关系

-- 创建原始worker表
CREATE TABLE worker (
    name VARCHAR(50),
    branch CHAR(1),
    manager VARCHAR(50)
);

-- 插入示例数据
INSERT INTO worker VALUES
('Jones', 'A', 'Frank'),
('Smith', 'B', 'David'),
('Curry', 'C', 'Frank'),
('Larry', 'B', 'David'),
('Julia', 'C', 'Frank');

-- 分解为worker1和branch表
CREATE TABLE worker1 (
    name VARCHAR(50),
    branch CHAR(1)
);

CREATE TABLE branch (
    branch CHAR(1) PRIMARY KEY,
    manager VARCHAR(50)
);

-- 插入分解后的数据
INSERT INTO worker1 (name, branch)
SELECT name, branch FROM worker;

INSERT INTO branch (branch, manager)
SELECT DISTINCT branch, manager FROM worker;

示例2：创建触发器以维护student_sum表

-- 创建学生表
CREATE TABLE student (
    stu_id INT IDENTITY(1,1) PRIMARY KEY,
    stu_name VARCHAR(10),
    stu_gender CHAR(2),
    stu_age INT
);

-- 创建存储学生人数的student_sum表
CREATE TABLE student_sum (
    stuCount INT DEFAULT(0)
);

-- 创建插入触发器
CREATE TRIGGER trig_insert
ON student
AFTER INSERT
AS
BEGIN
    DECLARE @stuNumber INT;
    SELECT @stuNumber = COUNT(*) FROM student;
    IF NOT EXISTS (SELECT * FROM student_sum)
        INSERT INTO student_sum VALUES(0);
    UPDATE student_sum SET stuCount = @stuNumber;
END;

-- 创建删除触发器
CREATE TRIGGER trig_delete
ON student
AFTER DELETE
AS
BEGIN
    SELECT stu_id AS 已删除的学生编号, stu_name, stu_gender, stu_age
    FROM deleted;
END;

-- 创建更新触发器
CREATE TRIGGER trig_update
ON student
AFTER UPDATE
AS
BEGIN
    DECLARE @stuCount INT;
    SELECT @stuCount = COUNT(*) FROM student;
    UPDATE student_sum SET stuCount = @stuCount;

    SELECT stu_id AS 更新前学生编号, stu_name AS 更新前学生姓名 FROM deleted;
    SELECT stu_id AS 更新后学生编号, stu_name AS 更新后学生姓名 FROM inserted;
END;

注意事项：

确保在SQL Server中执行上述SQL语句时，具有相应的权限。
触发器中的deleted和inserted表是SQL Server中特殊的临时表，用于存储被删除和插入的元组。

通过以上总结，学生可以系统地掌握数据库规范化的理论与实践，理解不同范式的意义及其应用，熟悉在SQL Server环境下实现规范化设计的具体步骤与SQL语句。

第八章多值依赖（MVDS）

8.1 重点

多值依赖（MVD）的定义与理解：
- 理解多值依赖的概念，即在关系模式中，如果属性集合X多值依赖于Y，意味着对于每个X的值，Y的值是相互独立的。
第四范式（4NF）的定义及其与BCNF的关系：
- 熟悉4NF的定义：如果一个关系模式中的每一个非平凡的多值依赖都由超级键决定，则该关系模式属于4NF。
- 理解4NF比BCNF更严格，4NF不仅处理函数依赖，还处理多值依赖。
多值依赖的规约与分解：
- 学会根据MVD进行关系模式的分解，以消除数据冗余。
- 了解4NF分解的方法，确保分解后的关系模式依然保持无损连接。

8.2 难点

多值依赖的识别与应用：
- 在复杂的关系模式中识别哪些属性之间存在多值依赖，区分多值依赖和函数依赖。
4NF与BCNF的区别与联系：
- 理解虽然每一个函数依赖都是多值依赖，但反之则不然，导致4NF包含更多的约束条件。
复杂分解的正确性验证：
- 确保通过多值依赖分解后的关系模式依然满足原有的数据依赖和保持无损连接。

8.3 实例分析

实例1：多值依赖的定义与应用

关系模式：Drinkers(name, addr, phones, beersLiked)

多值依赖：

name →→ phones
name →→ beersLiked

解释：

每个饮酒者的电话号码（phones）与他们喜欢的啤酒（beersLiked）是相互独立的，即每个电话号码可以与每种喜欢的啤酒组合出现，导致数据的重复。

SQL Server 中的示例操作：

假设有以下数据：

INSERT INTO Drinkers (name, addr, phones, beersLiked) VALUES 
('sue', 'a', 'p1', 'b1'),
('sue', 'a', 'p2', 'b2'),
('sue', 'a', 'p2', 'b1'),
('sue', 'a', 'p1', 'b2');

分解为4NF：

分解出电话信息：

CREATE TABLE Drinkers_Phones (
    name VARCHAR(50),
    addr VARCHAR(100),
    phones VARCHAR(20),
    PRIMARY KEY (name, phones)
);

INSERT INTO Drinkers_Phones (name, addr, phones)
SELECT DISTINCT name, addr, phones FROM Drinkers;

分解出喜欢的啤酒信息：

CREATE TABLE Drinkers_BeersLiked (
    name VARCHAR(50),
    beersLiked VARCHAR(50),
    PRIMARY KEY (name, beersLiked)
);

INSERT INTO Drinkers_BeersLiked (name, beersLiked)
SELECT DISTINCT name, beersLiked FROM Drinkers;

这样可以消除原表中的多值依赖，避免数据冗余。

实例2：4NF 的分解

原关系模式：Drinkers(name, addr, phones, beersLiked, manf)

多值依赖：

name →→ (areaCode, phone)
name →→ (beersLiked, manf)

分解步骤：

创建 Drinkers_AreaPhone 表：

CREATE TABLE Drinkers_AreaPhone (
    name VARCHAR(50),
    areaCode VARCHAR(10),
    phone VARCHAR(20),
    PRIMARY KEY (name, areaCode, phone)
);

创建 Drinkers_BeersManf 表：

CREATE TABLE Drinkers_BeersManf (
    name VARCHAR(50),
    beersLiked VARCHAR(50),
    manf VARCHAR(50),
    PRIMARY KEY (name, beersLiked, manf)
);

移除原表中的多值依赖：

CREATE TABLE Drinkers_Info (
    name VARCHAR(50),
    addr VARCHAR(100),
    PRIMARY KEY (name)
);

确保分解无损连接：

通过连接 Drinkers_AreaPhone 和 Drinkers_BeersManf 可以恢复原始数据。

8.4 图表与图示

1. 多值依赖的图示描述

图1：MVD X →→ Y 的图示
- 关系模式 R 包含属性集 X、Y 和其他属性。
- 如果两个元组在 X 上相同，则可以交换 Y 属性的值，生成新的元组仍在 R 中。

2. 4NF 分解的流程图

图2：4NF 分解流程
- 检查关系模式中的每一个多值依赖。
- 如果某个多值依赖不满足4NF，则将关系模式分解为两个子模式，一个包含多值依赖的属性，另一个包含剩余属性。
- 确保分解后的子模式满足无损连接。

第九章应用程序开发

9.1 重点

应用程序架构与Web基础：
- 理解现代应用程序的多层架构（前端、中间层、后端）及其各自的职责。
- 掌握Web技术的基础，如HTML、JavaScript、Servlets、JSP等。
快速应用程序开发（RAD）工具与框架：
- 熟悉常用的RAD工具和Web框架，如Java Server Faces (JSF)、Ruby on Rails、ASP.NET等，提高开发效率。
应用性能优化：
- 了解缓存技术（如连接池、查询缓存、HTML缓存）以提高Web应用的响应速度和处理能力。
应用安全性：
- 深入理解Web应用常见的安全问题，如SQL注入、跨站脚本攻击（XSS）、跨站请求伪造（CSRF）等，并掌握相应的防护措施。
- 掌握加密技术及其在数据库中的应用，确保数据传输和存储的安全。

9.2 难点

会话管理与状态保持：
- 理解无状态的HTTP协议如何通过会话（Session）管理用户状态，掌握Cookies、Session等技术。
触发器的复杂应用：
- 设计复杂的触发器以维护数据一致性或实现自动化操作，同时避免触发器带来的性能问题和意外副作用。
跨域认证与单点登录（SSO）：
- 理解如何实现跨域的安全认证机制，如SAML、OpenID，以及在分布式系统中的认证流程。
加密技术的结合与应用：
- 了解对称加密与非对称加密的结合使用（混合加密），以及在实际数据库中的具体应用场景和配置方法。

9.3 实例分析

例1：创建和使用触发器

任务： 为student表创建一个AFTER UPDATE触发器，更新student_sum表中的学生总数，并记录更新前后的学生信息。

示例SQL代码：

-- 创建student表
CREATE TABLE student (
    stu_id INT IDENTITY(1,1) PRIMARY KEY,
    stu_name VARCHAR(10),
    stu_gender CHAR(2),
    stu_age INT
);

-- 创建student_sum表
CREATE TABLE student_sum (
    stuCount INT DEFAULT(0)
);

-- 创建AFTER UPDATE触发器
CREATE TRIGGER trig_update
ON student
AFTER UPDATE
AS
BEGIN
    DECLARE @stuCount INT;
    SELECT @stuCount = COUNT(*) FROM student;
    UPDATE student_sum SET stuCount = @stuCount;

    -- 显示更新前后的学生信息
    SELECT stu_id AS 更新前学生编号, stu_name AS 更新前学生姓名 FROM deleted;
    SELECT stu_id AS 更新后学生编号, stu_name AS 更新后学生姓名 FROM inserted;
END;

说明：

当student表中的记录被更新时，trig_update触发器自动执行。
触发器更新student_sum表中的stuCount为当前学生总数。
使用deleted表记录更新前的学生信息，使用inserted表记录更新后的学生信息。

例2：防范SQL注入攻击

任务： 通过编写Servlet，确保用户输入通过预编译语句处理，防止SQL注入。

示例Java Servlet代码片段：

// 假设已经建立数据库连接conn
String persontype = request.getParameter("persontype");
String name = request.getParameter("name");

if(persontype.equals("student")) {
    String sql = "SELECT ID, name, dept_name FROM student WHERE name = ?";
    PreparedStatement pstmt = conn.prepareStatement(sql);
    pstmt.setString(1, name);
    ResultSet rs = pstmt.executeQuery();

    out.println("<table BORDER COLS=3>");
    out.println("<tr> <td>ID</td> <td>Name</td> <td>Department</td> </tr>");
    while(rs.next()) {
        String ID = rs.getString("ID");
        String studentName = rs.getString("name");
        String deptName = rs.getString("dept_name");
        out.println("<tr> <td>" + ID + "</td><td>" + studentName + "</td><td>" + deptName + "</td></tr>");
    }
    out.println("</table>");
    rs.close();
    pstmt.close();
} else {
    // 类似处理instructor类型
}

说明：

使用PreparedStatement预编译SQL语句，确保用户输入的name参数被安全处理，防止SQL注入。
动态生成HTML表格显示查询结果。

例3：使用JSP实现动态网页

任务： 编写一个JSP页面，根据用户输入的名字显示问候语。

示例JSP代码：

<html>
<head>
    <title>Hello</title>
</head>
<body>
    <% 
        String name = request.getParameter("name");
        if (name == null) { 
    %>
            Hello World
    <% 
        } else { 
    %>
            Hello, <%= name %>
    <% 
        } 
    %>
</body>
</html>

说明：

根据用户通过表单提交的name参数，动态显示不同的问候语。
如果用户未输入名字，则显示“Hello World”。

9.4 图表与图示

1. Web 应用程序三层架构图示描述

图1：应用程序三层架构
1. 前端（Presentation Layer）： 用户界面部分，负责与用户交互，通过浏览器展示HTML、CSS、JavaScript等。
2. 业务逻辑层（Business Logic Layer）： 处理应用程序的核心功能和业务规则，通常通过Servlets、JSP、或其他中间件实现。
3. 数据访问层（Data Access Layer）： 负责与数据库的交互，通过JDBC、ORM工具（如Hibernate）进行数据的读取与存储。

2. Servlet 处理流程图示描述

图2：Servlet 处理流程
1. 用户在浏览器中提交请求（如表单）。
2. 请求发送到Web服务器。
3. Web服务器将请求转发给对应的Servlet。
4. Servlet处理请求，包括与数据库的交互。
5. Servlet生成HTML响应并返回给Web服务器。
6. Web服务器将响应发送回用户的浏览器显示。

3. 防范XSS攻击的流程图示描述

图3：防范跨站脚本攻击（XSS）流程
1. 用户提交输入数据。
2. 服务器端对输入数据进行过滤，移除或转义HTML标签。
3. 生成安全的HTML页面返回给用户。
4. 浏览器渲染页面，防止恶意脚本执行。

第十章物理存储系统和文件组织

10.1 物理存储介质分类

物理存储介质按数据存储的特性分为以下两类：

易失性存储（Volatile Storage）：
- 断电后数据丢失。
- 例如：缓存（Cache）、主存（Main Memory）。
非易失性存储（Non-Volatile Storage）：
- 断电后数据仍然保存。
- 包括：二级存储（磁盘、闪存）、三级存储（磁带、光盘）和带有电池支持的主存。

影响存储介质选择的因素：

数据访问速度。
单位存储成本。
可靠性。

10.2 存储层次结构

存储设备按照速度、成本和存储容量形成一个层次结构：

主存储（Primary Storage）：
- 特点：速度最快，但易失性。
- 举例：缓存、主存。
二级存储（Secondary Storage）：
- 特点：非易失性，速度适中。
- 举例：闪存、磁盘（HDD、SSD）。
三级存储（Tertiary Storage）：
- 特点：非易失性，访问速度慢，适合归档数据。
- 举例：磁带（顺序访问）、光盘。

实例分析：

磁带存储容量可达1-12TB，适合大规模归档存储，但访问速度慢（顺序访问）。
磁盘存储的扇区（Sector）是访问的最小单位，一般大小为512字节。

10.3 磁盘存储机制

磁盘存储的关键技术细节：

磁盘结构：
- 磁盘由若干圆形磁盘组成，每个磁盘包含多个磁道（Tracks），每个磁道再分为扇区（Sectors）。
- 典型硬盘每个磁盘有50K-100K磁道，每扇区大小通常为512字节。
读写过程：
- 磁臂定位时间（Seek Time）：磁臂移动到目标磁道的时间。
- 旋转延迟（Rotational Latency）：目标扇区旋转至磁头下的时间。
- 数据传输率（Data-Transfer Rate）：数据传输速率，典型范围为25至200MB/s。

实例分析：
假设一块硬盘的平均定位时间为5ms，旋转延迟为5ms，传输速率为100MB/s，一次读取1MB数据：

总延迟 = 定位时间 + 旋转延迟 + 数据传输时间 = 5ms + 5ms + (1MB / 100MB/s) = 15ms。

10.4 磁盘可靠性与性能

平均故障间隔时间（MTTF）：
- 表示磁盘在连续运行中平均无故障的时间，通常为3-5年。
- 例如：MTTF为120万小时，表示1000块新磁盘中，每1200小时可能损坏一块。
磁盘控制器：
- 负责磁盘与计算机的交互，执行磁臂移动、数据读写、校验和计算等任务。
- 提供坏扇区重新映射功能以提高可靠性。

10.5 闪存存储

NAND闪存：
- 特点：广泛用于存储设备（如SSD），便宜但需要按页读取（页大小512B-4KB）。
- 速度：读取一页耗时20-100微秒。
- 限制：每个页只能写一次，重写需先擦除。
固态硬盘（SSD）：
- 内部使用多块闪存，提供块级接口。
- 传输速率：通过NVMe协议可达3GB/s。

10.6 RAID（独立磁盘冗余阵列）

RAID通过并行和冗余提升存储系统的性能和可靠性：

RAID 0（条带化存储，无冗余）：
- 特点：将数据分块存储到多个磁盘中，读写性能高，但无数据保护。
- 用途：高性能、数据丢失风险可接受的场景。
RAID 1（镜像存储）：
- 特点：将每块磁盘的数据完全复制到另一块磁盘。
- 优点：提供高可靠性，适合日志存储。
RAID 5（分布式奇偶校验）：
- 特点：数据和奇偶校验分布在所有磁盘上，提供容错能力。
- 缺点：写性能较低。
RAID 6（双奇偶校验）：
- 特点：比RAID 5增加额外奇偶校验块，可容忍两块磁盘同时故障。

实例分析：
RAID 1的两个磁盘MTTF均为10万小时，修复时间为10小时，则系统数据丢失的平均时间为：

MTTDL ≈ (MTTF² / 修复时间) = (10万² / 10) = 10亿小时（约11万年）。

10.7 文件组织

数据库存储为文件集合，每个文件由记录组成：

固定长度记录：
- 存储方式：记录按固定字节偏移存储。
- 删除方式：可以通过移动记录或维护空闲列表实现。
可变长度记录：
- 存储方式：使用偏移量和长度标记字段，实际数据存储在记录末尾。
- 优点：支持变长字段（如VARCHAR）和重复字段。
大对象存储：
- 例如：BLOB/CLOB类型。
- 存储方式：可以拆分为多个元组存储，或者存储为文件系统中的文件。

10.8 文件组织策略

堆文件（Heap）：
- 无序存储，记录插入到空闲空间。
- 使用空闲空间映射表（Free-Space Map）来快速定位空闲块。
顺序文件（Sequential）：
- 按搜索键值存储，适合顺序访问。
- 处理删除和插入：通过溢出块和指针链维护顺序。
多表聚簇文件（Multitable Clustering）：
- 将多个关系存储在同一文件中，适合频繁的连接查询。

10.9 列存储与压缩

列存储（Column-Oriented Storage）：
- 将每个属性单独存储，适合数据分析和决策支持。
- 优点：减少I/O、提升压缩率、支持矢量化处理。
- 缺点：重建元组成本高，更新和删除效率低。
实例：Parquet文件格式：
- 广泛应用于大数据场景，提供高效的列式存储。

10.10 缓存管理

缓冲区管理器：
- 功能：管理内存中的数据块缓存，减少磁盘访问次数。
- 替换策略：
  - LRU（最近最少使用）：根据历史访问记录替换。
  - MRU（最近最多使用）：适合需要频繁访问的块。
  - Toss-Immediate：块处理完成后立即释放。

实例分析：
嵌套循环连接可能导致LRU策略性能低下，因为循环内的访问模式会不断驱逐之前加载的块。

10.11 数据字典（系统目录）

数据字典存储数据库的元数据，例如：

关系的名称、属性及其类型。
索引信息和物理存储位置。
用户信息和统计数据。

元数据的高效访问对数据库系统的运行至关重要。

第十一章索引

11.1 索引的基本概念

关键概念

搜索键（Search Key）：
- 用于查找记录的一个或多个属性。
索引文件（Index File）：
- 包含形式为 (search-key, pointer) 的索引条目，指向实际数据记录。
索引的两种基本类型：
1. 有序索引（Ordered Indices）：按搜索键值排序存储。
2. 哈希索引（Hash Indices）：通过哈希函数将搜索键均匀分布到“桶”中。

评价指标

支持的访问类型：
- 按指定值查找记录。
- 按范围值查找记录。
时间效率：访问、插入和删除的时间。
空间开销：索引的存储成本。

11.2 有序索引

有序索引是基于搜索键值排序的索引方式。

分类

聚簇索引（Clustering Index）：
- 索引的键值与文件的物理顺序一致。
- 通常是主键，但不一定必须是主键。
- 又称主索引（Primary Index）。
非聚簇索引（Non-Clustering Index）：
- 索引的键值与文件的物理顺序无关。
- 又称次级索引（Secondary Index）。
密集索引（Dense Index）：
- 每个搜索键值都有一个索引记录。
- 例子：对instructor关系的ID属性建立密集索引。
  - 解析：每个ID值在索引中都有一个对应的条目，指向实际数据记录。
稀疏索引（Sparse Index）：
- 仅为部分搜索键值建立索引条目。
- 适用场景：文件按搜索键排序，例如每个块的第一个键值。
- 优缺点：稀疏索引节省空间，但查找速度较慢。

例子解析：
假设文件根据dept_name排序，对其建立稀疏索引。要查找Physics，首先找到小于Physics的最大键值（例如Mathematics），然后从指针指定的位置顺序扫描。

11.3 多级索引

当索引文件太大无法全部加载到内存时：

将索引看作顺序文件，为其构建稀疏索引（称为外层索引）。
如果外层索引仍然过大，可以继续构建更高层的索引，形成多层次结构。

优点：通过分层结构减少磁盘访问次数，提高查找效率。
缺点：插入或删除时需要更新所有层次的索引。

11.4 B+树索引

B+树是一种动态多级索引结构，广泛用于数据库系统中。

特点

自动调整以适应插入和删除，避免性能下降。
平衡性：从根到叶节点的路径长度相同。
节点结构：
- 非叶节点存储键值和指针，形成稀疏索引。
- 叶节点存储所有搜索键值及对应指针，形成密集索引，并通过指针相连。

优点

插入和删除只需局部调整，性能稳定。
查找时间为O(log n)，其中n为树的阶。

例子解析：

插入“Adams”：
- 找到适当的叶节点，如果节点已满，拆分节点，将部分键值移至新节点，并将新节点的信息插入父节点。
- 如果父节点也满，继续向上拆分，可能导致树高度增加。
删除“Singh”：
- 如果删除导致节点不足，将其与相邻节点合并，或从邻居节点借用键值。
- 如果父节点变得不足，继续调整，可能导致树高度减少。

11.5 哈希索引

哈希索引通过哈希函数将搜索键值映射到桶地址。

分类

静态哈希：
- 桶数量固定。
- 缺点：当文件大小动态变化时，可能导致桶溢出或空间浪费。
动态哈希：
- 线性哈希（Linear Hashing）：增量扩展桶，以适应文件增长。
- 可扩展哈希（Extendable Hashing）：通过增加哈希表的深度而不增加桶数量。

例子解析：

对instructor的ID属性建立哈希索引：

将ID值通过哈希函数映射到桶。
如果多个键值映射到同一桶，使用溢出链表存储额外记录。

优缺点对比：

哈希索引适合精确查找。
有序索引适合范围查询。

11.6 复合索引

复合搜索键：包含多个属性的搜索键，例如(dept_name, salary)。

特点

按字典序排序（例如：(Finance, 80000) < (Marketing, 70000)）。
可以同时高效处理多个条件查询。

例子解析：
假设有索引(dept_name, salary)：

查询条件dept_name="Finance" AND salary=80000：
- 索引直接定位到满足条件的记录。
查询条件dept_name="Finance" AND salary<80000：
- 索引可快速定位到Finance，然后顺序扫描满足salary<80000的记录。
查询条件dept_name<"Finance" AND salary=80000：
- 无法高效利用索引，因为dept_name的范围查询优先级更高。

11.7 位图索引

哈希索引通过哈希函数将搜索键值映射到桶地址。

特点

每个属性值对应一个位图，位图每位表示一个记录是否具有该值。
适合属性值数量较少的情况，例如性别、国家、收入等级。

查询操作

使用位运算（与、或、非）快速计算结果。
例子：
- 查询男性且收入等级为L1：Bitmap(Male) AND Bitmap(L1)。
- 查询结果位图可以直接定位记录。

优点：空间开销小，查询效率高。
缺点：不适合动态变化的数据。

11.8 索引的创建与管理

SQL中的索引定义

创建索引：

CREATE INDEX index_name ON table_name (attribute_list);

例子：对branch表的branch_name属性创建索引：

CREATE INDEX b_index ON branch(branch_name);

删除索引：
DROP INDEX index_name;

唯一索引：

CREATE UNIQUE INDEX unique_index_name ON table_name(attribute_list);

用于强制候选键的唯一性。

数据库自动创建索引

数据库通常会自动为主键和外键创建索引：
- 主键索引：加速主键查找。
- 外键索引：加速连接查询。

11.9 索引的优化选择

索引的使用需权衡查询性能与更新成本：

查询优化：
- 索引加速查询，但复杂查询可能需要多个索引。
- 索引调优工具：部分数据库提供索引调优助手，根据查询和更新负载建议优化索引。
更新代价：
- 插入、删除、更新操作需要维护索引，可能增加成本。

第十三章查询优化

13.1 查询优化的目标

查询优化的目标是通过生成低成本的执行计划来提高查询性能。

查询优化的两种方法：
1. 启发式优化（Heuristic Optimization）：
  - 使用规则优化查询结构，不需要具体的统计信息。
  - 优化过程基于通用规则，如选择操作下推、投影操作提前等。
2. 基于成本的优化（Cost-Based Optimization）：
  - 使用统计信息和模型估计查询计划的执行成本。
  - 比较多个等价查询计划，选择成本最低的方案。

13.2 关系代数等价性与规则

等价性定义

两个关系代数表达式在所有合法的数据库实例上生成相同的结果集，则它们是等价的。

常用等价规则

选择操作规则：
- 选择的分解与交换：
  - σθ1∧θ2(E) ≡ σθ1(σθ2(E))
  - σθ1(σθ2(E)) ≡ σθ2(σθ1(E))
- 选择与笛卡尔积、连接的结合：
  - σθ(E1 × E2) ≡ E1 ⨝θ E2
  - σθ1(E1 ⨝θ2 E2) ≡ E1 ⨝θ1∧θ2 E2
投影操作规则：
- 只保留最后一个投影操作：
  πL1(πL2(...πLn(E)...)) ≡ πL1(E)，其中 L1 ⊆ L2 ⊆ ... ⊆ Ln。
连接操作规则：
- 连接的交换性：E1 ⨝ E2 ≡ E2 ⨝ E1
- 连接的结合性：(E1 ⨝ E2) ⨝ E3 ≡ E1 ⨝ (E2 ⨝ E3)
选择与连接的分配性：
- σθ(E1 ⨝ E2) ≡ (σθ(E1)) ⨝ E2，条件是 θ 只涉及 E1 的属性。
投影与连接的分配性：
- πL1∪L2(E1 ⨝ E2) ≡ (πL1(E1)) ⨝ (πL2(E2))，条件是 L1 和 L2 包含连接所需的所有属性。
集合操作规则：
- 并集和交集的交换性与结合性：
  - E1 ∪ E2 ≡ E2 ∪ E1，(E1 ∪ E2) ∪ E3 ≡ E1 ∪ (E2 ∪ E3)
- 选择与集合操作的分配性：
  - σθ(E1 ∪ E2) ≡ σθ(E1) ∪ σθ(E2)

例子解析：
查询：查找Music系的教师姓名及其在2017年授课的课程标题。

SELECT ARTIST.NAME, COURSE.TITLE
FROM ARTIST, TEACHES, COURSE
WHERE ARTIST.dept_name = 'Music'
  AND TEACHES.year = 2017
  AND ARTIST.ID = TEACHES.ID
  AND TEACHES.course_id = COURSE.course_id;

初始表达式：
πname, title(σdept_name='Music'∧year=2017(instructor ⨝ teaches ⨝ course))
使用连接结合性：
(instructor ⨝ teaches) ⨝ course 转换为 instructor ⨝ (teaches ⨝ course)
应用选择下推规则：
σdept_name='Music'(instructor) ⨝ σyear=2017(teaches) ⨝ course
提前投影：减少中间结果的大小。

13.3 基于成本的优化

步骤

生成等价表达式：
- 使用关系代数等价规则生成多个等价表达式。
注释表达式：
- 为每个表达式选择具体的算法（如嵌套循环连接、哈希连接等）。
估算成本：
- 基于统计信息估算每种执行计划的成本。
选择最低成本计划：
- 比较所有计划，选择成本最低的一个。

成本估算

统计信息：
- 记录的数量（nr）。
- 每个块的记录数（fr）。
- 属性的不同值数量（V(A, r)）。
选择操作：
- σA=v(r) 的记录数：nr / V(A, r)。
- σA≥v(r) 的记录数：nr * (Amax - v + 1) / (Amax - Amin + 1)。
连接操作：
- 若 R ⋂ S 为键：|r ⨝ s| = |s|。
- 一般情况：|r ⨝ s| ≈ nr * ns / max(V(A, r), V(A, s))。

例子解析：
学生表（student）和选课表（takes）：

nstudent = 5000, bstudent = 100, ntakes = 10000, btakes = 400。
V(ID, student) = 5000, V(ID, takes) = 2500。
假设 ID 是 takes 的外键，连接结果的大小为 ntakes = 10000。

13.4 动态规划与连接顺序

连接顺序优化

连接顺序会显著影响查询性能。
动态规划通过递归计算子集的最优连接计划，避免重复计算。

算法

对于每个子集 S：
- 尝试所有可能的分割 S1 和 S2（S1 ⨝ S2）。
- 计算每种分割的成本，选择最低成本的方案。
存储每个子集的最优计划，避免重复计算。

复杂性：

一般情况：O(3^n)，其中 n 为关系数量。
左深树优化：O(n * 2^n)，适用于优化复杂度较低的场景。

13.5 启发式优化

启发式规则

选择操作尽早执行：
- 减少待处理的元组数量。
投影操作尽早执行：
- 减少待处理的属性数量。
优先执行选择性强的操作：
- 选择性强的操作通常会显著减少中间结果的大小。
避免笛卡尔积：
- 替换为连接操作。

例子解析：
查询：查找专辑名称为“Andy’s OG Remix”的艺术家姓名。

SELECT ARTIST.NAME
FROM ARTIST, APPEARS, ALBUM
WHERE ARTIST.ID = APPEARS.ARTIST_ID
  AND APPEARS.ALBUM_ID = ALBUM.ID
  AND ALBUM.NAME = "Andy’s OG Remix";

分解谓词：
- ARTIST.ID = APPEARS.ARTIST_ID
- APPEARS.ALBUM_ID = ALBUM.ID
- ALBUM.NAME = "Andy’s OG Remix"
谓词下推：
- 将 ALBUM.NAME = "Andy’s OG Remix" 下推到 ALBUM 中，减少中间结果。
替换笛卡尔积：
- 用连接代替笛卡尔积：ARTIST ⨝ APPEARS ⨝ ALBUM。
提前投影：
- 删除不必要的属性，减少中间结果的大小。

13.6 统计信息与直方图

数据库系统使用统计信息（如记录数、属性的不同值数）来估算查询成本。
直方图：
- 等宽直方图：将值域划分为等宽区间。
- 等深直方图：每个区间包含相同数量的元组。

例子解析：
假设V(age, people) = 5, nr = 5：

查询 age=2：
- 满足条件的记录数为 nr / V(age, people) = 5 / 5 = 1。
查询 age>=2：
- 满足条件的记录数为 nr * (Amax - 2 + 1) / (Amax - Amin + 1)。
- 结果为 5 * (4 - 2 + 1) / (4 - 0 + 1) = 3。

第十四章事务

14.1 事务的基本概念

事务（Transaction）

定义：事务是一个程序执行的单位，通过访问和可能更新数据库中的数据项来完成任务。
性质：事务要么完全执行（所有操作成功完成），要么完全不执行（任何操作都不生效）。

例子：
将$50从账户A转移到账户B的事务：

读取账户A余额。
从账户A扣除50。
写回账户A的新余额。
读取账户B余额。
向账户B添加50。
写回账户B的新余额。

问题：

如果系统在步骤3和步骤6之间崩溃，可能导致数据不一致（$50丢失）。

14.2 事务的ACID属性

事务需要满足以下四个关键特性以保证数据库的一致性：

原子性（Atomicity）：
- 事务的所有操作要么全部执行，要么全部不执行。
- 解决方法：通过日志记录，在事务失败时回滚未完成的操作。
一致性（Consistency）：
- 事务的执行必须保证数据库从一个一致状态转变为另一个一致状态。
- 例子：在转账事务中，A和B账户余额之和在事务执行后保持不变。
隔离性（Isolation）：
- 事务彼此独立运行，每个事务的中间状态对其他事务不可见。
- 问题：如果隔离性未得到保证，可能导致数据不一致。
持久性（Durability）：
- 一旦事务提交，其对数据库的修改必须永久保存，即使系统崩溃。
- 解决方法：使用日志或稳定存储。

14.3 事务的状态

事务的生命周期由以下几种状态组成：

活动状态（Active）：事务正在执行。
部分提交状态（Partially Committed）：事务的最后一条语句已经执行，但尚未完成提交。
失败状态（Failed）：事务由于某种原因无法继续。
中止状态（Aborted）：事务被回滚，数据库恢复到事务开始时的状态。
提交状态（Committed）：事务成功完成，所有修改被永久保存。

例子：

BEGIN TRANSACTION;
UPDATE account SET money = money - 50 WHERE no = 'A';
UPDATE account SET money = money + 50 WHERE no = 'B';
COMMIT;

如果在第一条UPDATE执行后系统崩溃，事务的原子性和一致性会受影响。

14.4 调度与并发执行

调度（Schedule）

调度是指事务中所有操作按照时间顺序执行的过程。
串行调度：
- 一个事务完成后再执行下一个事务。
- 保证一致性，但效率低。
并发调度：
- 多个事务交错执行，提高资源利用率和响应时间。

并发执行的优点

提高处理器和磁盘的利用率。
减少事务的平均响应时间。

并发调度的问题

如果未正确处理并发调度，可能导致以下数据不一致性：

丢失修改：
- 两个事务同时修改同一数据，一个事务的修改被另一个事务覆盖。
- 例子：
  T1: read(A); A := A + 100; write(A);
  T2: read(A); A := A + 200; write(A);
  如果T1和T2同时执行，T2的写入会覆盖T1的结果，导致T1的修改丢失。
不可重复读：
- 一个事务在两次读取同一数据时，数据值发生了变化。
- 例子：
  T1: read(A); print(A); read(A); print(A);
  T2: read(A); A := A + 100; write(A);
  T1读取两次A的值可能不同。
读“脏”数据：
- 一个事务T2读取了另一个事务T1未提交的数据，如果T1回滚，则T2读取的数据无效。
- 例子：
  T1: read(A); A := A + 100; write(A); rollback;
  T2: read(A); print(A);
  T2读取了T1未提交的修改，导致读“脏”数据。

14.5 可串行化（Serializability）

定义

一个调度是可串行化的，如果它的执行效果与某个串行调度相同。

可串行化是并发调度的正确性准则。

冲突可串行化（Conflict Serializability）

如果一个调度可以通过交换非冲突操作转换为串行调度，则该调度是冲突可串行化的。
冲突的定义：
- 两个操作访问同一数据，且至少有一个是写操作：
  - read(Q) vs write(Q)：冲突。
  - write(Q) vs write(Q)：冲突。

检测冲突可串行化

通过优先图（Precedence Graph）检测：

图中每个节点表示一个事务。
如果事务Ti的操作在事务Tj之前访问了数据，则添加一条从Ti到Tj的边。
判断方法：如果优先图无环，则调度是冲突可串行化的。

例子：

调度：
T1: read(A); write(A);
T2: read(A); write(A);
- 优先图：T1 → T2，无环，因此可串行化。

14.6 恢复性调度

恢复性（Recoverable）

如果事务Tj依赖于事务Ti写入的数据，则Tj必须在Ti提交后才能提交。

问题：如果Tj在Ti回滚之前提交，则可能导致不一致。

级联回滚（Cascading Rollback）

一个事务的失败可能导致多个事务回滚。
避免方法：无级联调度（Cascadeless Schedule），要求Tj读取Ti的数据时，Ti必须已提交。

14.7 事务隔离级别

SQL-92定义了四种隔离级别，允许不同程度的并发性：

Serializable（可串行化）：
- 最严格的隔离级别，保证事务调度可串行化。
Repeatable Read（可重复读）：
- 防止不可重复读，但无法避免幻影读。
Read Committed（读已提交）：
- 只能读取已提交的数据，可能导致不可重复读。
Read Uncommitted（读未提交）：
- 允许读取未提交的数据，可能导致读“脏”数据。

例子解析：
事务1：

SELECT * FROM accounts WHERE balance > 1000;

事务2：

UPDATE accounts SET balance = balance - 100 WHERE id = 1;

如果隔离级别是Read Uncommitted，事务1可能读取到事务2未提交的结果。

第十五章并发控制

15.1 引言：并发控制的必要性

目标：在不预先知道整个调度的情况下，确保所有执行调度都是正确的（即可串行化的）。
挑战：
1. 数据一致性：多事务并发执行时，可能导致数据不一致。
2. 事务隔离性：必须避免事务间的互相干扰。
解决方案：
- 使用并发控制协议，如锁机制、时间戳协议、多版本控制等。

15.2 基于锁的并发控制

2.1 锁的基本概念

定义：锁是控制数据项并发访问的一种机制。
两种锁模式：
1. 共享锁（S锁）：允许事务读取数据，但不允许修改。
2. 排它锁（X锁）：允许事务读取和修改数据。
锁兼容矩阵：

请求锁类型已持有锁类型兼容性

S S 是

S X 否

X S 否

X X 否

例子：
事务T2读取两个数据项：

T2: lock-S(A); read(A); unlock(A);
    lock-S(B); read(B); unlock(B);
    display(A + B)

这种方式不能保证可串行化，因为没有控制事务间的交互。

2.2 两阶段锁协议（2PL）

定义：一种确保冲突可串行化的协议，分为两个阶段：
1. 增长阶段：事务可以获取锁，但不能释放锁。
2. 收缩阶段：事务可以释放锁，但不能获取新锁。
关键点：事务的“锁点”（获取最后一个锁的时间）决定了事务的顺序。

例子：

符合两阶段锁协议：

T1: lock-S(B); lock-X(A); read(B); read(A);
    unlock(B); write(A); unlock(A)

不符合两阶段锁协议：

T1: lock-S(B); read(B); unlock(B);
    lock-X(A); read(A); write(A); unlock(A)

扩展：

严格两阶段锁协议：事务直到提交或回滚后才释放其所有的排它锁，避免了级联回滚。
严格强制两阶段锁协议：事务直到提交或回滚后才释放所有的锁，保证了事务的可恢复性和串行化顺序。

2.3 死锁处理

死锁：当一组事务彼此等待对方释放锁时，会导致系统无法继续执行。
死锁预防策略：
1. 等待-死（Wait-Die）：较老的事务可以等待较新的事务；较新的事务必须回滚。
2. 伤害-等待（Wound-Wait）：较老的事务会中止较新的事务；较新的事务可以等待较老的事务。
死锁检测：
- 使用等待图，如果图中存在环，则表示发生死锁。
死锁恢复：
- 回滚代价最小的事务以打破死锁。

15.3 基于时间戳的并发控制

3.1 时间戳协议

时间戳：每个事务在开始时分配一个唯一的时间戳（TS）。
规则：
1. 如果TS(Ti) < TS(Tj)，则系统必须确保Ti的操作在Tj之前执行。
2. 每个数据项维护两个时间戳：
  - **W-timestamp(Q)**：最后写入Q的事务的时间戳。
  - **R-timestamp(Q)**：最后读取Q的事务的时间戳。

读操作规则：

如果TS(Ti) < W-timestamp(Q)，表示Ti试图读取已被覆盖的数据，Ti回滚。
如果TS(Ti) >= W-timestamp(Q)，执行读取操作，并更新R-timestamp(Q)。

写操作规则：

如果TS(Ti) < R-timestamp(Q)，表示Ti生成的值已经被其他事务读取，Ti回滚。
如果TS(Ti) < W-timestamp(Q)，表示Ti试图写入过期数据，Ti回滚。
否则，执行写操作，并更新W-timestamp(Q)。

优点：

保证了可串行化。
避免死锁（因为没有事务等待）。

3.2 Thomas写规则

改进：当事务试图写入过期值时，直接忽略写操作，而不是回滚事务。
优势：允许更多的并发调度，支持一些不可冲突串行化的调度。

15.4 多版本并发控制（MVCC）

4.1 多版本协议的核心思想

数据库维护每个数据项的多个版本。
写入：创建新版本。
读取：根据事务的时间戳读取适当的版本。

4.2 多版本时间戳排序（MVTO）

每个数据项的版本包含：
1. 内容：版本的值。
2. W-timestamp：创建该版本的事务时间戳。
3. R-timestamp：读取该版本的最大事务时间戳。
规则：
1. 读取：选择满足条件的最新版本。
2. 写入：
  - 如果TS(Ti) < R-timestamp(Qk)，则回滚事务。
  - 否则，创建新版本。

优点：

读取操作永不等待，提高了并发性。
保证了可串行化。

4.3 快照隔离（Snapshot Isolation, SI）

工作原理：
- 每个事务读取开始时的“快照”数据。
- 写操作遵循第一个提交者获胜（First-Committer-Wins）规则。
优点：
- 读操作不会阻塞，提高了读性能。
- 写操作避免冲突。
不足：
- 可能导致非串行化调度（如丢失更新）。

15.5 其他并发控制协议

5.1 图锁协议

基于数据项的部分顺序关系（有向无环图）。
规则：
- 事务必须按规定的顺序访问数据。
优点：避免死锁，增加并发性。
缺点：可能需要锁定未访问的数据，增加开销。

5.2 多粒度锁

数据项可分为多种粒度（如数据库→表→记录）。
意向锁：
- IS（意向共享）：表示将对数据的子节点加共享锁。
- IX（意向排它）：表示将对数据的子节点加排它锁。
- SIX（共享且意向排它）：当前节点为共享锁，子节点可能加排它锁。
优点：提高并发性，减少锁管理开销。

15.6 总结

本章介绍了数据库并发控制的核心机制和协议，包括基于锁的控制、时间戳协议和多版本并发控制。

重点内容：

两阶段锁协议及其扩展（严格2PL、强制2PL）。
时间戳协议和Thomas写规则。
多版本控制中的快照隔离（SI）。
死锁的检测与预防。

实例分析：通过多个调度示例，详细阐述了并发控制协议在实际场景中的应用及其优缺点。

关键思想：在保证事务ACID特性的基础上，最大化并发性和系统性能。

第十六章恢复系统

16.1 恢复系统的目标

核心问题：在系统发生故障时，如何保证数据库的一致性和事务的ACID特性（原子性、一致性、隔离性、持久性）。
恢复算法的两部分：
1. 正常事务处理期间的准备：
  - 记录足够的信息以便在故障发生后能够恢复。
2. 故障后的恢复操作：
  - 将数据库恢复到一致状态。

16.2 故障分类

事务故障：
- 逻辑错误：由于内部错误（如违反约束条件）导致事务无法完成。
- 系统错误：如死锁，数据库系统需要终止某个事务。
系统崩溃：
- 由于硬件或软件故障导致系统崩溃。
- 假设：非易失性存储的数据不会被系统崩溃破坏（Fail-Stop假设）。
磁盘故障：
- 硬盘损坏或部分数据丢失。
- 使用校验和检测磁盘故障。

16.3 存储结构

易失性存储：
- 不会在系统崩溃后保留数据。
- 例如：主存、缓存。
非易失性存储：
- 系统崩溃后保留数据。
- 例如：磁盘、闪存。
稳定存储：
- 理想化的存储，能够抵抗所有故障。
- 实现方式：保持多个副本（如RAID系统）。

16.4 日志和恢复机制

4.1 日志记录

日志的作用：记录事务的操作，以便在系统崩溃后进行恢复。
日志记录方式：
1. 事务开始：<Ti start>。
2. 写操作：<Ti, X, V1, V2>，记录数据项X的旧值V1和新值V2。
3. 事务提交：<Ti commit>。

例子：
事务T0将$50从账户A转移到账户B：

日志记录：

<T0 start>
<T0, A, 1000, 950>
<T0, B, 2000, 2050>
<T0 commit>

如果系统崩溃：
- 若<T0 commit>存在，则需要重做（Redo）。
- 若<T0 commit>不存在，则需要回滚（Undo）。

4.2 Undo和Redo操作

Undo（回滚）：
- 将事务所做的修改恢复为旧值。
- 写入补偿日志记录：<Ti, X, V1>。
Redo（重做）：
- 将事务提交的修改重新应用于数据库。

恢复规则：

需要Undo：日志中有<Ti start>但没有<Ti commit>。
需要Redo：日志中有<Ti start>且有<Ti commit>。

4.3 恢复算法

Redo阶段：
- 从最近的检查点开始，向前扫描日志，对所有提交的事务重做操作。
Undo阶段：
- 从日志末尾向后扫描，对未完成的事务进行回滚操作。

示例：
假设以下日志记录：

<checkpoint L={T0, T1}>
<T0 start>
<T0, A, 1000, 950>
<T0, B, 2000, 2050>
<T1 start>
<T1, C, 700, 600>
<T0 commit>

恢复步骤：
1. Redo：重做T0的操作，因为T0已提交。
2. Undo：回滚T1的操作，因为T1未提交。

16.5 检查点（Checkpoint）

5.1 检查点的作用

检查点减少了恢复时需要处理的日志记录长度。
步骤：
1. 将内存中的日志记录写入稳定存储。
2. 将修改过的缓冲区块写回磁盘。
3. 写入<checkpoint L>日志记录，其中L是所有活跃事务的列表。

5.2 恢复时的优化

从最近的检查点开始扫描日志。
只需要处理检查点之后开始的事务，之前的日志可被忽略。

例子：
日志：

<checkpoint L={T1, T2}>
<T1 start>
<T1, A, 100, 200>
<T1 commit>
<T2 start>
<T2, B, 300, 400>

T1已提交，无需处理。
T2尚未完成，需要回滚。

16.6 Write-Ahead Logging (WAL)

规则：写入数据块之前，必须先将对应的日志记录写入稳定存储。
目的：保证即使系统崩溃，也能通过日志恢复数据。

16.7 数据库缓冲管理

No-Force策略：事务提交时，不强制将数据块写回磁盘。
Steal策略：允许未提交事务修改的数据块写回磁盘，但必须遵守WAL规则。

步骤：

写入日志记录。
刷新日志到稳定存储。
将数据块写回磁盘。

16.8 模糊检查点（Fuzzy Checkpointing）

避免长时间中断：允许事务在检查点期间继续运行。
步骤：
1. 暂停更新，写入<checkpoint L>日志记录。
2. 记录所有修改过的缓冲区块。
3. 恢复更新，将缓冲区块逐步写回磁盘。

16.9 远程备份系统

9.1 目的

提供高可用性，即使主站点发生故障，事务处理仍可在备份站点继续。

9.2 工作原理

主站点将日志记录传输到备份站点。
备份站点应用日志更新本地数据库。
主站点故障后，备份站点接管事务处理。

9.3 提交策略

One-Safe：主站点提交后即可返回结果，但可能导致更新丢失。
Two-Very-Safe：主备站点同时记录提交，可靠但延迟高。
Two-Safe：在主站点和备份站点之间权衡性能和可靠性。

16.10 总结

本章详细讲解了数据库恢复的核心机制，包括日志记录、Undo/Redo操作、检查点和远程备份系统。通过具体的例子说明了如何在事务失败或系统崩溃后恢复数据库的一致性。重难点如下：

日志记录与WAL规则：日志是恢复的核心，必须首先写入稳定存储。
检查点的优化作用：减少日志扫描的范围，加速恢复。
恢复算法的两个阶段：Redo和Undo。
远程备份系统：提高系统的可用性和容错能力。

通过这些机制，数据库系统能够在故障后快速恢复，同时保证事务的ACID特性。

Lavoisier

http://Lavoisier7.github.io/2024/11/25/Database_Principle/

本博客所有文章除特別声明外，均采用 CC BY 4.0 许可协议。转载请注明来源 Lavoisier !

SQL Server Database

Computer Architecture

进阶版计算机组成原理

2024-11-26 Computer Science

elective CA

Marcel Proust

前半生出入社交名利场，后半生缠绵病榻

2024-11-24 Literary giants

Marcel Proust In Search of Lost Time

Database Principle

第一章 数据库系统概述

1.1 数据库应用实例

1.2 数据与数据库系统

1.3 数据库系统组件

1.4 数据库设计

1.5 数据定义语言（DDL）与数据操作语言（DML）

1.6 SQL查询语言

1.7 数据库系统架构

1.8 数据库系统的发展历史

第二章 关系模型与关系代数

2.1 关系模型基础

2.2 关系的性质

2.3 关键字概念

2.4 关系代数操作

2.5 关系操作实例

2.6 关系代数运算示例解析

2.7 重点与难点总结

重点：

难点：

2.8 关键术语解释

2.9 图表与图示描述

2.10 实例分析详解

实例1：关系代数的选择操作

实例2：关系代数的联合操作

实例3：SQL插入操作

实例4：关系代数的笛卡尔积与选择

第三章 SQL查询语言概述

3.1 重点

3.2 难点

3.3 实例分析

第四章 访问SQL与触发器

4.1 重点

4.2 难点

4.3 实例分析

4.4 第三章与第四章中提及的重要图示描述

第五章 高级SQL

5.1 重点

5.2 难点

5.3 实例分析

4.4 第三章与第四章中提及的重要图示描述

第六章 基于E-R模型的数据库设计

6.1 重点

6.2 难点

6.3 主要内容总结

1. 数据库设计过程概述

2. 实体-关系模型（E-R模型）

2.1 实体集与属性

2.2 属性类型

2.3 关系集与基数约束

2.4 弱实体集与标识关系

3. 从E-R图到关系模式的转换

3.1 处理多值属性

3.2 复合属性的展开

4. 扩展的E-R特性

4.1 泛化与特化（Generalization/Specialization）

4.2 聚合（Aggregation）

5. 数据库设计中的常见问题与解决方案

5.1 冗余与更新异常

5.2 规范化

6. 替代符号表示

6.4 实例分析

实例1：大学信息维护

需求描述

设计过程

重点难点解析

实例2：处理多值属性

需求描述

设计过程

重点难点解析

6.5 图表与图示

1. E-R图的图形表示

实体集与属性

关系集与基数约束

弱实体集与标识关系

2. 从E-R图到关系模式的转换示意

6.6 总结

6.7 建议

第七章 标准化

7.1 关系数据库设计中的问题

第一章数据库系统概述

第二章关系模型与关系代数

第四章访问SQL与触发器

第五章高级SQL

第六章基于E-R模型的数据库设计

第七章标准化

第八章多值依赖（MVDS）

第九章应用程序开发

第十章物理存储系统和文件组织

第十一章索引

第十三章查询优化

第十四章事务