Back Home

数据库

数据库系统的三级模式结构是由美国国家标准协会(ANSI)提出的数据库管理系统框架，它将数据库系统抽象为三个层次：外模式、概念模式和内模式。这种结构提供了数据的逻辑独立性和物理独立性。

1. 三级模式结构

image-20250612121314388

(1) 外模式(External Schema) - 用户级

  • 定义: 也称为子模式或用户视图，是数据库用户(包括应用程序员和最终用户)能够看见和使用的局部数据的逻辑结构和特征描述。
  • 特点:
    • 一个数据库可以有多个外模式
    • 不同用户可以看到不同的数据视图
    • 是概念模式的子集
    • 通过外模式-概念模式映像与概念模式关联

(2) 概念模式(Conceptual Schema) - 概念级

  • 定义: 也称为逻辑模式或全局模式，是对数据库中全部数据的整体逻辑结构和特征的描述。
  • 特点:
    • 一个数据库只有一个概念模式
    • 是所有用户视图的综合
    • 是数据库管理员(DBA)看到的数据库
    • 通过概念模式-内模式映像与内模式关联

(3) 内模式(Internal Schema) - 物理级

  • 定义: 也称为存储模式，是对数据物理结构和存储方式的描述。
  • 特点:
    • 一个数据库只有一个内模式
    • 描述数据在存储介质上的存储方式
    • 包括索引、存储结构、存取路径等细节

2. 两级映像与数据独立性

(1) 外模式/概念模式映像

  • 定义了外模式与概念模式之间的对应关系
  • 保证了逻辑数据独立性：当概念模式改变时，只需修改此映像而无需修改外模式或应用程序

(2) 概念模式/内模式映像

  • 定义了概念模式与内模式之间的对应关系
  • 保证了物理数据独立性：当内模式改变时(如存储结构改变)，只需修改此映像而无需修改概念模式或外模式

3. 三级模式结构的优点

  1. 数据共享性高：不同用户可以共享同一数据库
  2. 数据独立性好：
    • 物理独立性：存储结构改变不影响应用程序
    • 逻辑独立性：逻辑结构改变不影响某些应用程序
  3. 数据安全性好：用户只能看到外模式定义的数据
  4. 简化用户接口：用户无需了解底层实现细节
  5. 有利于数据标准化：便于数据库的设计和维护

4. 实际应用中的对应关系

在关系数据库系统中：

  • 外模式通常对应视图(View)
  • 概念模式对应基本表(Base Table)
  • 内模式对应存储文件、索引等物理存储结构

这种三级模式结构是现代数据库系统的理论基础，为数据库的设计、实现和维护提供了清晰的框架。

数据库的概念模型

一、概念模型的定义

概念模型（Conceptual Model）是数据库设计的高层次抽象，它描述了现实世界中数据的需求、结构和关系，而不涉及具体的数据库实现细节。概念模型是数据库设计人员在需求分析阶段创建的，作为沟通用户需求与数据库实现的桥梁。

二、概念模型的主要特点

  1. 面向业务：反映用户的业务需求和数据视图
  2. 独立于实现：不依赖于特定的DBMS或物理存储结构
  3. 语义丰富：清晰地表达数据含义和关系
  4. 易于理解：使用图形化表示方法，便于与非技术人员交流

三、概念模型的核心要素

1. 实体（Entity）

  • 表示现实世界中可区分的对象或事物
  • 示例：学生、课程、图书、读者等
  • 在ER图中用矩形表示

2. 属性（Attribute）

  • 描述实体的特征或性质
  • 分类：
    • 简单属性 vs 复合属性
    • 单值属性 vs 多值属性
    • 派生属性
    • 键属性（唯一标识实体）
  • 在ER图中用椭圆表示

3. 关系（Relationship）

  • 表示实体之间的联系
  • 有向关系（1:1、1:N、M:N）
  • 在ER图中用菱形表示

4. 约束（Constraint）

  • 基数约束（Cardinality）
  • 参与约束（Participation）
  • 其他业务规则

四、概念模型的表示方法

1. 实体-联系模型（ER模型）

  • 最常用的概念模型表示方法
  • 主要组件：实体、属性、关系
  • 扩展ER模型包括：弱实体、特殊化/泛化等

2. UML类图

  • 面向对象的概念模型表示
  • 类、属性、关联、继承等概念

3. 其他表示方法

  • 语义网络
  • 对象角色模型(ORM)

五、概念模型的设计步骤

  1. 需求分析：收集用户需求和数据要求
  2. 标识实体：确定系统中的主要对象
  3. 定义属性：为每个实体确定属性
  4. 建立关系：确定实体间的联系
  5. 确定键：为每个实体选择主键
  6. 验证模型：检查完整性、冗余和一致性
  7. 优化模型：消除冗余和不一致性

六、概念模型与逻辑模型的区别

特征 概念模型 逻辑模型
抽象级别 高层次业务抽象 面向具体DBMS的实现
目标 捕获业务需求 准备数据库实现
表示方法 ER图等 关系模式等
关注点 业务实体和关系 表、字段、键等
独立性 完全独立于DBMS 部分依赖DBMS类型

七、概念模型的实际应用

  1. 数据库设计：作为数据库开发的第一步
  2. 系统分析：帮助理解业务需求
  3. 文档编制：作为系统文档的重要组成部分
  4. 沟通工具：在开发人员与用户之间建立共同语言

概念模型是数据库设计过程中至关重要的阶段，它为后续的逻辑设计和物理设计奠定了基础，确保数据库系统能够准确反映业务需求。

数据完整性约束

数据完整性约束是数据库系统中用于保证数据正确性、一致性和可靠性的一组规则。这些约束定义了数据库中数据必须满足的条件，防止无效或不符合业务规则的数据进入数据库。

一、完整性约束的主要类型

1. 实体完整性（Entity Integrity）

  • 定义：确保表中每行数据的唯一性
  • 实现方式：
    • 主键约束（PRIMARY KEY）
    • 唯一约束（UNIQUE）
  • 特点：
    • 主键值不能为NULL
    • 主键值必须唯一
    • 一个表只能有一个主键，但可以有多个唯一约束

2. 参照完整性（Referential Integrity）

  • 定义：保证表之间关系的有效性
  • 实现方式：
    • 外键约束（FOREIGN KEY）
  • 特点：
    • 外键值必须是被参照表的主键值或NULL
    • 定义级联操作（CASCADE, SET NULL, SET DEFAULT等）

3. 域完整性（Domain Integrity）

  • 定义：保证字段值符合定义的数据类型和业务规则
  • 实现方式：
    • 数据类型（INT, VARCHAR等）
    • NOT NULL约束
    • CHECK约束
    • DEFAULT值
    • 枚举类型（ENUM）
  • 示例： 
    CREATE TABLE Employees (
  Age INT CHECK (Age >= 18 AND Age <= 65),
  Gender CHAR(1) CHECK (Gender IN ('M','F')),
  JoinDate DATE NOT NULL
);

4. 用户定义完整性（User-defined Integrity）

  • 定义：根据特定业务需求定制的约束规则
  • 实现方式：
    • 触发器（TRIGGER）
    • 存储过程
    • 应用程序逻辑
  • 示例：
    • 订单日期不能早于客户注册日期
    • 离职日期必须大于入职日期

二、完整性约束的实现机制

1. 声明式约束（Declarative Constraints）

  • 在表定义时直接声明的约束
  • 包括：PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK, NOT NULL等
  • 示例： 
    CREATE TABLE Orders (
  OrderID INT PRIMARY KEY,
  CustomerID INT NOT NULL,
  OrderDate DATE DEFAULT CURRENT_DATE,
  FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);

2. 过程式约束（Procedural Constraints）

  • 通过程序逻辑实现的约束
  • 包括：触发器、存储过程、应用程序代码
  • 触发器示例： 
    CREATE TRIGGER check_salary
BEFORE INSERT ON Employees
FOR EACH ROW
BEGIN
  IF NEW.Salary < 0 THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Salary cannot be negative';
  END IF;
END;

三、完整性约束的作用时机

  1. 立即执行约束（Immediate Constraints）
    • 在每条SQL语句执行时立即检查
    • 大多数约束属于此类
  2. 延迟执行约束（Deferred Constraints）
    • 在整个事务提交时才检查
    • 特别适用于存在循环引用的情况
    • 需要显式声明为DEFERRABLE

四、违反完整性约束的处理

当数据操作违反完整性约束时，数据库系统通常会：

  1. 拒绝执行该操作（默认行为）
  2. 根据定义的级联规则处理（对参照完整性）
    • CASCADE：级联更新/删除
    • SET NULL：设置为NULL
    • SET DEFAULT：设置为默认值
    • RESTRICT/NO ACTION：拒绝操作（默认）

五、完整性约束的重要性

  1. 保证数据质量：防止无效或不一致数据进入数据库
  2. 维护数据一致性：确保数据间关系正确
  3. 减少应用层验证：将验证逻辑下移到数据库层
  4. 文档化业务规则：约束本身记录了重要的业务规则
  5. 提高系统可靠性：避免因数据错误导致的系统问题

六、实际应用示例

图书馆管理系统中的完整性约束

CREATE TABLE Readers (
    CardID CHAR(10) PRIMARY KEY,
    Name VARCHAR(50) NOT NULL,
    Email VARCHAR(100) UNIQUE
);

CREATE TABLE Books (
    ISBN CHAR(13) PRIMARY KEY,
    Title VARCHAR(200) NOT NULL,
    Type ENUM('Paper','Electronic') NOT NULL
);

CREATE TABLE PaperBooks (
    ISBN CHAR(13) PRIMARY KEY,
    Location VARCHAR(50) NOT NULL,
    Status ENUM('OnShelf','Borrowed') DEFAULT 'OnShelf',
    FOREIGN KEY (ISBN) REFERENCES Books(ISBN)
    ON DELETE CASCADE
);

CREATE TABLE BorrowRecords (
    CardID CHAR(10),
    ISBN CHAR(13),
    BorrowDate TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    ReturnDate TIMESTAMP NULL,
    PRIMARY KEY (CardID, ISBN, BorrowDate),
    FOREIGN KEY (CardID) REFERENCES Readers(CardID),
    FOREIGN KEY (ISBN) REFERENCES PaperBooks(ISBN),
    CHECK (ReturnDate IS NULL OR ReturnDate >= BorrowDate)
);

数据完整性约束是数据库设计的核心内容，合理使用各种约束可以显著提高数据质量和系统可靠性，同时减少应用程序中的验证代码。

关系数据库规范化

关系数据库规范化是数据库设计中的核心理论，它通过一系列规范化形式（Normal Forms）来消除数据冗余、减少异常，并确保数据依赖关系的合理性。

一、规范化的目的

  1. 消除数据冗余：避免相同数据在多个地方重复存储
  2. 减少更新异常：
    • 插入异常（无法插入某些数据）
    • 删除异常（删除数据时丢失不该丢失的信息）
    • 修改异常（需要修改多处相同数据）
  3. 建立合理的数据依赖关系
  4. 提高数据库结构的灵活性

二、规范化形式（Normal Forms）

规范化过程分为多个级别，从低到高依次为：

1. 第一范式（1NF）

  • 定义：所有属性都是原子性的（不可再分）
  • 要求：
    • 每个列都是不可分割的基本数据项
    • 没有重复的列（即没有多值属性）
    • 有主键标识每一行
  • 示例：
    • 不符合1NF的表（多值属性）： 
      StudentID | Name       | Courses
----------------------------------
1001      | 张三       | 数学,物理,化学
    • 符合1NF的表： 
      StudentID | Course
-------------------
1001      | 数学
1001      | 物理
1001      | 化学

2. 第二范式（2NF）

  • 前提：已经满足1NF
  • 定义：消除部分函数依赖（非主属性完全依赖于主键）
  • 要求：
    • 表必须有主键
    • 所有非主属性必须完全依赖于整个主键（而不是主键的一部分）
  • 示例：
    • 不符合2NF的表（部分依赖）： 
      OrderID | ProductID | ProductName | Quantity
--------------------------------------------
001     | P100      | 笔记本电脑  | 2

      （ProductName只依赖于ProductID，而不是完整的复合主键(OrderID,ProductID)）

    • 解决方案：拆分为两个表 
      订单明细表(OrderID, ProductID, Quantity)
产品表(ProductID, ProductName)

3. 第三范式（3NF）

  • 前提：已经满足2NF
  • 定义：消除传递函数依赖
  • 要求：
    • 非主属性不能依赖于其他非主属性
    • 即：非主属性必须直接依赖于主键
  • 示例：
    • 不符合3NF的表（传递依赖）： 
      StudentID | Name | DeptID | DeptName | DeptLocation
-------------------------------------------------
1001      | 张三 | D01    | 计算机   | 东校区

      （DeptName和DeptLocation依赖于DeptID，而DeptID又依赖于StudentID）

    • 解决方案：拆分为两个表 
      学生表(StudentID, Name, DeptID)
部门表(DeptID, DeptName, DeptLocation)

4. 巴斯-科德范式（BCNF）

  • 前提：已经满足3NF
  • 定义：所有决定因素都必须是候选键
  • 与3NF的区别：
    • 3NF允许主属性对候选键的传递依赖
    • BCNF消除了这种可能性
  • 示例：
    • 不符合BCNF的表： 
      学生选课表(StudentID, Course, Teacher)
假设：每个教师只教一门课，每门课可由多个教师教授

      （Teacher → Course，但Teacher不是候选键）

    • 解决方案：拆分为两个表 
      教师授课表(Teacher, Course)
学生选课表(StudentID, Teacher)

5. 第四范式（4NF）

  • 前提：已经满足BCNF
  • 定义：消除多值依赖
  • 多值依赖：当一个属性集确定后，另一个属性集有多个独立的值与之对应
  • 示例：
    • 不符合4NF的表： 
      StudentID | Course | Hobby
---------------------------
1001      | 数学   | 篮球
1001      | 数学   | 音乐
1001      | 物理   | 篮球
1001      | 物理   | 音乐

      （Course和Hobby是独立的多值属性）

    • 解决方案：拆分为两个表 
      学生选课表(StudentID, Course)
学生爱好表(StudentID, Hobby)

6. 第五范式（5NF）

  • 前提：已经满足4NF
  • 定义：消除连接依赖
  • 应用场景：处理复杂的多表关联情况
  • 实际应用较少，多数情况下4NF已经足够

三、规范化的优缺点

优点：

  1. 减少数据冗余
  2. 避免数据异常（插入/删除/更新异常）
  3. 提高数据一致性
  4. 使数据库设计更灵活

缺点：

  1. 过度规范化可能导致查询性能下降（需要多表连接）
  2. 增加查询复杂度
  3. 可能需要更多的表连接操作

四、反规范化（Denormalization）

在某些情况下，为了提高查询性能，会有意违反规范化原则：

  • 适当增加冗余数据
  • 合并表减少连接操作
  • 使用预计算字段

适用场景：

  • 读密集型应用
  • 数据仓库
  • 报表系统

五、规范化实践建议

  1. 通常设计到3NF或BCNF即可满足大多数业务需求
  2. 权衡规范化与性能：根据应用特点决定规范化程度
  3. 考虑业务变化：设计应具有一定灵活性以适应需求变化
  4. 文档化设计决策：记录为什么选择特定的规范化级别

六、规范化示例

初始表（未规范化）：

订单表(OrderID, OrderDate, CustomerID, CustomerName, 
      CustomerAddress, ProductID, ProductName, 
      Category, Quantity, UnitPrice, Discount)

规范化过程：

  1. 1NF：确保所有属性原子性
    • 已经满足（假设没有多值属性）
  2. 2NF：
    • 拆分为： 
      订单主表(OrderID, OrderDate, CustomerID)
订单明细表(OrderID, ProductID, Quantity, UnitPrice, Discount)
客户表(CustomerID, CustomerName, CustomerAddress)
产品表(ProductID, ProductName, Category)
  3. 3NF：
    • 检查是否有传递依赖
    • 当前设计已满足3NF
  4. BCNF：
    • 检查所有决定因素是否为候选键
    • 当前设计已满足BCNF

最终规范化设计：

订单主表(OrderID, OrderDate, CustomerID)
订单明细表(OrderID, ProductID, Quantity, UnitPrice, Discount)
客户表(CustomerID, CustomerName, CustomerAddress)
产品表(ProductID, ProductName, Category)

规范化是数据库设计的基础理论，合理应用规范化原则可以创建出结构良好、易于维护的数据库。但在实际应用中，需要根据具体业务需求和性能要求找到规范化程度的最佳平衡点。

事务的特性

事务是数据库管理系统中的一个核心概念，用于确保数据的完整性和一致性。事务的特性通常由ACID属性来描述，ACID代表四个关键特性：原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）和持久性（Durability）。

  1. 原子性（Atomicity）：
    • 事务是不可分割的工作单位。事务中的所有操作要么全部完成，要么全部不做。如果事务中的某个操作失败，那么事务所有操作都将被撤销（回滚），以确保数据的一致性。
  2. 一致性（Consistency）：
    • 事务在执行前后，数据库必须处于一致的状态。事务的执行不能破坏数据库的完整性约束，如主键约束、外键约束、唯一性约束等。一致性确保了数据库从一个一致的状态转换到另一个一致的状态。
  3. 隔离性（Isolation）：
    • 事务的执行是相互独立的，一个事务的执行不应受到其他事务的干扰。数据库系统需要提供一定的隔离机制，以防止事务并发执行时可能发生的数据不一致问题，如脏读、不可重复读和幻读。
  4. 持久性（Durability）：
    • 一旦事务被提交，它对数据库的修改就是永久性的，即使系统发生故障也不会丢失。持久性保证了事务提交后，其结果将被永久保存在数据库中。

这些特性共同确保了数据库管理系统在面对并发操作和系统故障时，仍能保持数据的完整性和一致性。

根据提供的文件内容，以下是提取的知识点以及详细讲解：

数据库编程概述

  • 关系模型与关系代数：关系模型是数据库的基础模型，关系代数是操作关系模型的数学工具。
  • 关系数据库系统与SQL语言：SQL语言是关系数据库的标准语言，用于数据定义、数据操纵、数据库安全性和完整性。
  • 关系数据理论与数据库设计：包括关系规范化理论、数据库设计的各个阶段（需求分析、概念设计、逻辑设计、物理设计）以及数据库的实施与维护。

2. SQL语言的三种使用方式

  • 交互式SQL (ISQL)：独立运行，适用于临时用户操作访问数据，如即席查询。
  • 嵌入式SQL (ESQL)：将SQL语句嵌入到高级程序设计语言中，用于数据库应用开发。
  • 过程化SQL (PL/SQL)：可在数据库服务器中独立运行，常用于编写存储过程、存储函数、触发器等。

3. 嵌入式SQL (ESQL)

3.1 嵌入式SQL的处理过程

  • 预编译方法：将嵌入式SQL语句转换为函数调用，再进行编译处理。
  • 主语言与嵌入式SQL的通信：通过SQL通信区（SQLCA）、主变量、游标等实现数据交换。

3.2 嵌入式SQL语句与主语言之间的通信

  • SQL通信区（SQLCA）：用于存放SQL语句执行状态代码。
  • 主变量：用于输入或输出数据，通过前缀“:”区分主变量和SQL变量。
  • 指示变量：用于指示主变量的值是否为空值或是否被截断。
  • 游标（Cursor）：用于协调SQL语句与主语言之间的数据处理方式，逐行处理查询结果。

3.3 不用游标的SQL语句

  • 单条结果元组的数据交换：使用SELECT ... INTO ...或FETCH ... INTO ...。
  • 非CURRENT形式的增删改语句：如INSERT、UPDATE、DELETE等。

3.4 使用游标的SQL语句

  • 游标操作：
    • 声明游标：DECLARE cursor_name CURSOR FOR subquery;
    • 打开游标：OPEN cursor_name;
    • 推进游标指针并取当前记录：FETCH cursor_name INTO host-variable;
    • 关闭游标：CLOSE cursor_name;
  • CURRENT形式的UPDATE和DELETE语句：用于修改或删除当前游标指向的记录。

3.5 动态SQL

  • 动态SQL语句的可变性：SQL语句正文、变量个数、语句类型、引用对象等可以在运行时动态变化。
  • 动态SQL的使用：
    • SQL语句主变量：程序主变量包含SQL语句内容。
    • 动态参数：使用参数符号（?）表示运行时设定的值。
    • 执行准备好的语句：EXEC SQL EXECUTE <语句名> [INTO <主变量表>] [USING <主变量或常量>];

4. 过程化SQL (PL/SQL)

4.1 过程化SQL的块结构

  • 块结构：包括声明部分、执行部分和异常处理部分。
  • 块之间可以互相嵌套：每个块完成一个逻辑操作。

4.2 变量和常量的定义

  • 变量定义：变量名 数据类型 [ [NOT NULL] := 初值表达式];
  • 常量定义：常量名 数据类型 CONSTANT := 常量表达式;
  • 赋值语句：变量名称 := 表达式;

4.3 流程控制

  • 条件控制语句：IF-THEN、IF-THEN-ELSE、嵌套的IF语句。
  • 循环控制语句：LOOP、WHILE-LOOP、FOR-LOOP。
  • 错误处理：使用EXCEPTION块处理异常。

5. 存储过程和函数

  • 存储过程：用过程化SQL编写的过程，存储在数据库服务器中，可以被应用程序或其他存储过程调用。
  • 函数：与存储过程类似，但必须定义返回值的类型，可以出现在SQL语句和表达式中。
  • 优点：
    • 运行效率高：编译和优化后存储在数据库中。
    • 降低通信开销：只需提交过程名和参数值。
    • 方便管理：由数据库管理系统统一管理。

5.1 存储过程的用户接口

  • 创建存储过程：CREATE [OR REPLACE] PROCEDURE 过程名([参数1,参数2,...]) AS <过程化SQL块>;
  • 执行存储过程：CALL/PERFORM PROCEDURE 过程名([参数1,参数2,...]);
  • 修改存储过程：重命名或重新编译。
  • 删除存储过程：DROP PROCEDURE 过程名;

5.2 函数的用户接口

  • 创建函数：CREATE [OR REPLACE] FUNCTION 函数名([参数1,参数2,...]) RETURN returntype AS <过程化SQL块>;
  • 执行函数：CALL/SELECT 函数名([参数1,参数2,...]);
  • 修改函数：重命名或重新编译。
  • 删除函数：DROP FUNCTION 函数名;

6. 面向驱动的数据库编程

  • ODBC编程：开放式数据库连接，提供标准化的数据库访问API。
  • JDBC编程：Java数据库连接，提供Java应用程序访问数据库的API。
  • Python DB-API：Python的数据库访问规范，提供统一的数据库访问接口。

6.1 ODBC体系结构

  • ODBC驱动程序管理器：将用户调用的SQL语句转换成特定数据库的访问函数。
  • 数据源名称（DSN）：指定与后台数据库服务器的连接方式。

6.2 JDBC驱动的实现方式

  • JDBC-ODBC桥：通过ODBC访问数据库。
  • 本地驱动：使用专用协议访问数据库。
  • 纯Java驱动：直接访问数据库，性能更高。

6.3 Python程序中的数据库访问流程

  • 创建Connection：建立与数据库的连接。
  • 获取Cursor：用于执行SQL语句和获取结果。
  • 命令执行与数据处理：执行SQL语句并处理结果。
  • 关闭Cursor和Connection：释放资源。

7. 复习思考题

  • 嵌入式SQL应用程序的构成：包括主变量声明、条件处理、数据库连接、主程序逻辑、数据库断开等。
  • 主变量与指示变量的作用：主变量用于数据交换，指示变量用于指示主变量的值是否为空值或是否被截断。
  • 嵌入式SQL与主语言语句之间的信息交换方式：通过SQL通信区、主变量、游标等实现。
  • 游标的使用流程：声明游标、打开游标、推进游标指针并取当前记录、关闭游标。
  • 嵌入式SQL语句访问数据库的执行异常：如SQLERROR、NOT FOUND、SQLWARNING等。
  • 存储过程和函数的创建与使用：根据需求创建存储过程或函数，调用时传递参数并获取结果。

这些知识点涵盖了数据库编程的基础概念、嵌入式SQL的使用、过程化SQL的结构与功能、存储过程和函数的创建与调用，以及面向驱动的数据库编程技术。

关系代数除运算

image-20250607210319563