未记录事实核验 · 状态：维护中

记录仅说明核验时间与维护状态，不保证适用于所有环境；使用前请留意正文中的版本、适用条件与风险提示。

warning 适用边界与维护说明

本文适合已有基础SQL知识的读者学习关系模型、完整性约束、关系代数及规范化，并参考电商表结构与索引设计，不是完整可执行练习或可部署项目。各节SQL应作为独立示例阅读：同名表存在不同定义，查询所需的students.age、students.major、courses.is_required及graduated_students、categories、brands表结构未完整提供。代码主要采用MySQL风格，CHECK的执行受版本和表达式限制；保留的CURDATE()及子查询CHECK不能直接用于该实现，经理工资表达式也未实现注释所述业务规则，不能作为有效约束照搬。条件索引已明确属于其他数据库方言，JOIN写法本身不代表性能提升。本次仅作全文结构与内容一致性审阅，未进行数据库执行测试、外部事实核验或链接可达性检查。

关系型数据库理论基础 - 关系模型与数据组织原理

tip 学习目标

本文将深入介绍关系型数据库的理论基础，包括关系模型的数学原理、数据完整性约束机制以及关系运算理论。

核心内容：

关系模型的数学理论基础
数据完整性约束的技术实现
关系代数基本运算及SQL示例
范式理论与数据库设计规范

学习价值：理解关系型数据库的理论基础，为高质量的数据库设计和优化奠定坚实基础。

abstract 本节概览

学习目标：掌握关系模型的理论基础和技术实现
技能价值：具备数据库设计和优化的理论指导能力
预计用时：25分钟
学习成果：能够基于关系理论进行数据库设计
应用前景：为数据库架构设计提供理论支撑

关系模型理论基础

关系模型的数学定义

关系（Relation）：在数学上，关系是笛卡尔积的有限子集。在数据库中，关系对应于表（Table）。

元组（Tuple）：关系中的一行数据，对应表中的记录。

属性（Attribute）：关系中的列名，定义数据的特征。

域（Domain）：属性的取值范围，如整数域、字符串域等。

度（Degree）：关系中属性的个数。

基数（Cardinality）：关系中元组的个数。

关系模型的基本概念

关系模式（Relation Schema）

-- 关系模式定义示例
Students(StudentID, Name, Age, Major, EnrollYear)
-- 其中：StudentID为主键，Age属于整数域，Major属于字符串域

关系实例（Relation Instance）

-- 关系实例（表中的实际数据）
+----------+--------+-----+----------+----------+
| StudentID| Name   | Age | Major    | EnrollYear|
+----------+--------+-----+----------+----------+
| 001      | 张三   | 20  | 计算机   | 2023     |
| 002      | 李四   | 21  | 数学     | 2022     |
| 003      | 王五   | 19  | 物理     | 2024     |
+----------+--------+-----+----------+----------+

超键（Super Key）：能够唯一标识关系中每个元组的属性集合。

候选键（Candidate Key）：最小的超键，不能再去掉任何属性。

主键（Primary Key）：从候选键中选择的一个作为主要标识。

外键（Foreign Key）：用于引用另一关系或同一关系中候选键的属性或属性集合；SQL中通常引用主键或满足相应条件的唯一键。

数据完整性约束机制

实体完整性约束

定义：主键约束，确保关系中每个元组都有唯一标识。

技术实现：

-- 主键约束实现
CREATE TABLE students (
    student_id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 复合主键示例
CREATE TABLE enrollments (
    student_id INT,
    course_id INT,
    enrollment_date DATE,
    PRIMARY KEY (student_id, course_id, enrollment_date)
);

约束规则：

主键属性不能为空（NOT NULL）
主键值在关系中必须唯一
一个关系只能有一个主键

参照完整性约束

定义：外键约束，确保关系间引用的一致性。

技术实现：

-- 外键约束实现
CREATE TABLE courses (
    course_id INT PRIMARY KEY,
    course_name VARCHAR(100) NOT NULL,
    credits INT CHECK (credits > 0)
);

CREATE TABLE student_courses (
    enrollment_id INT PRIMARY KEY AUTO_INCREMENT,
    student_id INT,
    course_id INT,
    grade DECIMAL(3,1),
    
    -- 定义外键约束
    FOREIGN KEY (student_id) REFERENCES students(student_id)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
    FOREIGN KEY (course_id) REFERENCES courses(course_id)
        ON DELETE RESTRICT
        ON UPDATE CASCADE
);

约束操作：

CASCADE：级联删除/更新
RESTRICT：限制删除/更新
SET NULL：设置为空值
SET DEFAULT：设置为默认值；是否支持取决于数据库实现，前述MySQL常用的InnoDB外键不支持该操作

域完整性约束

定义：确保属性值符合预定义的数据类型和约束条件。

MySQL执行边界：MySQL从8.0.16起才实际执行CHECK约束；更早版本可能接受语法但不执行。以下含CURDATE()的CHECK不符合MySQL的表达式限制，不能直接执行，且不能仅将当前日期替换为固定日期来实现持续有效的业务规则。

技术实现：

-- 域完整性约束示例
CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL,
    price DECIMAL(10,2) CHECK (price > 0),
    category ENUM('electronics', 'books', 'clothing', 'food'),
    stock_quantity INT DEFAULT 0 CHECK (stock_quantity >= 0),
    manufacturing_date DATE CHECK (manufacturing_date <= CURDATE()),
    
    -- 复杂约束条件
    CONSTRAINT price_range CHECK (
        (category = 'electronics' AND price >= 10.00) OR
        (category != 'electronics' AND price >= 1.00)
    )
);

用户自定义完整性约束

业务规则约束：

-- 业务规则约束示例
CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    hire_date DATE NOT NULL,
    salary DECIMAL(10,2) CHECK (salary > 0),
    department VARCHAR(30),
    manager_id INT,
    
    -- 自引用外键约束
    FOREIGN KEY (manager_id) REFERENCES employees(employee_id),
    
    -- 业务规则：员工不能管理自己
    CONSTRAINT no_self_management CHECK (employee_id != manager_id),
    
    -- 业务规则：经理的工资必须高于普通员工
    CONSTRAINT manager_salary_rule CHECK (
        manager_id IS NULL OR 
        salary > (SELECT AVG(salary) FROM employees WHERE manager_id = employee_id)
    )
);

关系代数及SQL示例

基本关系运算

选择（Selection, σ）：

-- 选择运算：σ(age > 20)(Students)
SELECT * FROM students WHERE age > 20;

投影（Projection, π）：

-- 投影运算：π(name, major)(Students)
SELECT DISTINCT name, major FROM students;

并（Union, ∪）：

-- 并运算：CS_Students ∪ Math_Students
SELECT student_id, name FROM students WHERE major = 'Computer Science'
UNION
SELECT student_id, name FROM students WHERE major = 'Mathematics';

差（Difference, −）：

-- 差运算：All_Students − Graduated_Students
SELECT s.student_id, s.name FROM students s
WHERE NOT EXISTS (
    SELECT 1 FROM graduated_students g
    WHERE g.student_id = s.student_id
);

笛卡尔积（Cartesian Product, ×）：

-- 笛卡尔积：Students × Courses
SELECT s.*, c.*
FROM students s
CROSS JOIN courses c;

扩展关系运算

连接（Join, ⋈）：

-- 内连接（θ-连接）
SELECT s.name, sc.grade, c.course_name
FROM students s
INNER JOIN student_courses sc ON s.student_id = sc.student_id
INNER JOIN courses c ON sc.course_id = c.course_id
WHERE sc.grade >= 90;

-- 左外连接
SELECT s.name, COALESCE(sc.grade, 'Not Enrolled') as grade
FROM students s
LEFT JOIN student_courses sc ON s.student_id = sc.student_id;

-- 自连接示例
SELECT e1.name as employee, e2.name as manager
FROM employees e1
LEFT JOIN employees e2 ON e1.manager_id = e2.employee_id;

除（Division, ÷）：

-- 除运算示例：找出选修了所有必修课程的学生
SELECT s.student_id, s.name
FROM students s
WHERE NOT EXISTS (
    SELECT c.course_id
    FROM courses c
    WHERE c.is_required = TRUE
    AND NOT EXISTS (
        SELECT 1
        FROM student_courses sc
        WHERE sc.student_id = s.student_id
          AND sc.course_id = c.course_id
    )
);

范式理论与数据库设计

第一范式（1NF）

定义：关系中每个属性都是原子的，不可再分。

违反1NF的示例：

-- 违反1NF：interests字段包含多个值
+----------+--------+------------------------+
| StudentID| Name   | Interests              |
+----------+--------+------------------------+
| 001      | 张三   | 编程,音乐,体育         |
+----------+--------+------------------------+

符合1NF的设计：

-- 符合1NF的设计
CREATE TABLE students (
    student_id INT PRIMARY KEY,
    name VARCHAR(50) NOT NULL
);

CREATE TABLE student_interests (
    student_id INT,
    interest VARCHAR(50),
    PRIMARY KEY (student_id, interest),
    FOREIGN KEY (student_id) REFERENCES students(student_id)
);

第二范式（2NF）

定义：满足1NF，且每个非主属性都完全函数依赖于每个候选键，不存在对候选键真子集的部分函数依赖；非主属性是指不属于任何候选键的属性。

违反2NF的示例：

-- 违反2NF：course_name仅依赖于course_id，不依赖于完整主键
CREATE TABLE enrollments_bad (
    student_id INT,
    course_id INT,
    course_name VARCHAR(100),  -- 部分依赖
    grade DECIMAL(3,1),
    PRIMARY KEY (student_id, course_id)
);

符合2NF的设计：

-- 拆分为两个表，消除部分依赖
CREATE TABLE courses (
    course_id INT PRIMARY KEY,
    course_name VARCHAR(100) NOT NULL
);

CREATE TABLE enrollments (
    student_id INT,
    course_id INT,
    grade DECIMAL(3,1),
    PRIMARY KEY (student_id, course_id),
    FOREIGN KEY (course_id) REFERENCES courses(course_id)
);

第三范式（3NF）

定义：对于每个非平凡函数依赖X → A，X是超键，或A是主属性（属于至少一个候选键）；3NF也满足2NF。

违反3NF的示例：

-- 违反3NF：department_location传递依赖于student_id
CREATE TABLE students_bad (
    student_id INT PRIMARY KEY,
    name VARCHAR(50),
    department VARCHAR(50),
    department_location VARCHAR(100)  -- 传递依赖
);

符合3NF的设计：

-- 消除传递依赖
CREATE TABLE departments (
    department_id INT PRIMARY KEY,
    department_name VARCHAR(50),
    location VARCHAR(100)
);

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    name VARCHAR(50),
    department_id INT,
    FOREIGN KEY (department_id) REFERENCES departments(department_id)
);

巴斯-科德范式（BCNF）

定义：对于每个非平凡函数依赖X → Y，决定因素X都必须是超键。BCNF比3NF更严格。

BCNF设计示例：

-- BCNF设计：处理多个候选键的情况
CREATE TABLE course_instructors (
    instructor_id INT,
    course_code VARCHAR(10),
    semester VARCHAR(20),
    room_number VARCHAR(10),
    
    -- 约束条件：
    -- (instructor_id, semester) → room_number
    -- (course_code, semester) → instructor_id
    -- (room_number, semester) → instructor_id
    
    PRIMARY KEY (course_code, semester),
    UNIQUE KEY (instructor_id, semester),
    UNIQUE KEY (room_number, semester)
);

企业级应用实践

电商系统数据库设计

-- 用户表设计
CREATE TABLE users (
    user_id BIGINT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    phone VARCHAR(20),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    status ENUM('active', 'inactive', 'suspended') DEFAULT 'active',
    
    INDEX idx_email (email),
    INDEX idx_phone (phone),
    INDEX idx_created_at (created_at)
);

-- 商品表设计
CREATE TABLE products (
    product_id BIGINT PRIMARY KEY AUTO_INCREMENT,
    sku VARCHAR(50) UNIQUE NOT NULL,
    product_name VARCHAR(200) NOT NULL,
    description TEXT,
    category_id INT NOT NULL,
    brand_id INT,
    price DECIMAL(10,2) NOT NULL CHECK (price >= 0),
    cost DECIMAL(10,2) CHECK (cost >= 0),
    stock_quantity INT DEFAULT 0 CHECK (stock_quantity >= 0),
    weight DECIMAL(8,3) CHECK (weight > 0),
    status ENUM('active', 'inactive', 'discontinued') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (category_id) REFERENCES categories(category_id),
    FOREIGN KEY (brand_id) REFERENCES brands(brand_id),
    
    INDEX idx_sku (sku),
    INDEX idx_category (category_id),
    INDEX idx_price (price),
    INDEX idx_status_created (status, created_at)
);

-- 订单表设计
CREATE TABLE orders (
    order_id BIGINT PRIMARY KEY AUTO_INCREMENT,
    order_number VARCHAR(32) UNIQUE NOT NULL,
    user_id BIGINT NOT NULL,
    order_status ENUM('pending', 'paid', 'shipped', 'delivered', 'cancelled') DEFAULT 'pending',
    total_amount DECIMAL(12,2) NOT NULL CHECK (total_amount >= 0),
    shipping_fee DECIMAL(8,2) DEFAULT 0.00 CHECK (shipping_fee >= 0),
    tax_amount DECIMAL(10,2) DEFAULT 0.00 CHECK (tax_amount >= 0),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE RESTRICT,
    
    INDEX idx_user_id (user_id),
    INDEX idx_order_status (order_status),
    INDEX idx_created_at (created_at)
);

性能优化策略

索引设计策略：

-- 复合索引设计
CREATE INDEX idx_product_category_price ON products(category_id, price, status);
CREATE INDEX idx_order_user_status_date ON orders(user_id, order_status, created_at);

-- 覆盖索引设计
CREATE INDEX idx_product_list_cover ON products(category_id, status, product_id, product_name, price);

-- 部分索引（条件索引）：以下为PostgreSQL等支持该语法的数据库示例，不能直接用于前述MySQL风格建表。
CREATE INDEX idx_active_products ON products(category_id, price) WHERE status = 'active';

查询优化实践：

-- 写法一：使用逗号连接；不能仅凭这种写法判断查询效率
SELECT p.product_name, p.price, c.category_name
FROM products p, categories c
WHERE p.category_id = c.category_id
AND p.price > 100
AND p.status = 'active';

-- 写法二：使用显式JOIN表达连接关系；性能需结合实际索引、数据分布和执行计划判断
SELECT p.product_name, p.price, c.category_name
FROM products p
INNER JOIN categories c ON p.category_id = c.category_id
WHERE p.status = 'active'
AND p.price > 100;

知识连接

什么是数据库：了解数据库系统的整体架构
范式理论：深入学习数据库规范化理论
表结构设计原则：学习实际的数据库设计方法
Doris/数据模型：掌握概念模型设计

学习检验

理论掌握检验：

[ ] 能够准确定义关系、元组、属性等基本概念
[ ] 理解主键、外键、候选键的区别和作用
[ ] 掌握数据完整性约束的分类和实现方法
[ ] 了解关系代数基本运算的含义和SQL实现

实践能力检验：

[ ] 能够设计符合范式要求的关系模式
[ ] 掌握完整性约束的SQL实现语法
[ ] 能够识别和解决数据冗余和异常问题
[ ] 具备复杂查询的关系代数分析能力

总结要点

关系模型理论：

关系模型建立在严格的数学基础上，提供了数据组织的理论框架
关系模型提供结构化的数据组织方式；数据一致性和完整性还依赖正确的约束、事务机制及业务实现
关系代数为查询语言提供了理论基础

数据完整性：

实体完整性确保每个记录的唯一性
参照完整性维护表间关系的一致性
域完整性保证数据值的有效性
用户自定义约束实现业务规则的强制执行

范式理论应用：

规范化可减少由函数依赖引起的数据冗余及更新异常，具体效果取决于依赖识别和关系分解是否正确
不同范式适用于不同的业务场景
实际设计中需要平衡范式要求与查询性能

#SQL #关系型数据库 #关系模型 #数据完整性 #范式理论

学习路径：