逻辑设计与实施维护

5.5 逻辑结构设计

💡 逻辑设计的核心任务：将E-R图转换为关系模式，并进行优化

5.5.1 关系模型核心概念

💡 这些概念是MySQL操作的基础，必须理解透彻！

关系模型核心术语表

术语	定义	对应理解
关系（Relation）	一张二维表	表（Table）
元组（Tuple）	表中的一行	记录（Record）
属性（Attribute）	表中的一列	字段（Field）
域（Domain）	属性的取值范围	数据类型
分量	元组中的一个属性值	字段值
关系模式	关系的结构描述	表结构
候选码	能唯一标识元组的属性组	可作为主键的列
主码（主键）	被选中的候选码	PRIMARY KEY
外码（外键）	引用其他表主码的属性	FOREIGN KEY
主属性	候选码中的属性	主键列
非主属性	不在任何候选码中的属性	非主键列

关系模式的表示：

关系名（属性1，属性2，...，属性n）

例如：Student（学号，姓名，性别，年龄，班级）
      主键用下划线标注

5.5.2 键的概念

键类型	定义	特点
超码（Super Key）	能唯一标识元组的属性组	可能包含冗余属性
候选码（Candidate Key）	最小的超码	不含冗余属性，可能有多个
主码（Primary Key）	被选中的候选码	只能有一个，不能为NULL
外码（Foreign Key）	引用其他表主码的属性	用于建立表间联系
主属性	候选码中的属性	参与构成候选码
非主属性	不在任何候选码中的属性	普通属性

主表与从表的关系

MySQL创建外键示例：

-- 创建主表
CREATE TABLE class (
    class_id VARCHAR(10) PRIMARY KEY,
    class_name VARCHAR(50)
);

-- 创建从表，设置外键
CREATE TABLE student (
    stu_id VARCHAR(10) PRIMARY KEY,
    name VARCHAR(20),
    class_id VARCHAR(10),
    FOREIGN KEY (class_id) REFERENCES class(class_id)
);

5.5.3 关系的性质

💡 关系是一种规范化的表格，有以下限制：

性质	说明
列是同质的	每一列中的分量是同一类型的数据
不同列可出自同一域	但不同列要有不同的属性名
列的顺序无关紧要	列的次序可以任意交换
行的顺序无关紧要	行的次序可以任意交换
分量必须是原子值	每个分量都是不可再分的数据项（第一范式）
没有重复的元组	任意两个元组不能完全相同

5.5.4 关系的完整性约束

💡 完整性约束保证数据的正确性和一致性

完整性	定义	规则	SQL实现
实体完整性	主键约束	主键不能为空（NOT NULL），且必须唯一	PRIMARY KEY
参照完整性	外键约束	外键值必须是被参照表主键的值，或者为NULL	FOREIGN KEY REFERENCES
用户定义完整性	业务规则约束	根据具体业务需求定义的约束	CHECK、NOT NULL、UNIQUE、DEFAULT

MySQL约束示例：

-- 实体完整性
CREATE TABLE student (
    sno CHAR(10) PRIMARY KEY,              -- 主键约束
    sname VARCHAR(20) NOT NULL             -- 非空约束
);

-- 参照完整性
CREATE TABLE sc (
    sno CHAR(10),
    cno CHAR(10),
    grade DECIMAL(5,2),
    FOREIGN KEY (sno) REFERENCES student(sno),  -- 外键约束
    FOREIGN KEY (cno) REFERENCES course(cno)
);

-- 用户定义完整性（综合示例）
CREATE TABLE student (
    sno CHAR(10) PRIMARY KEY,              -- 实体完整性：主键约束
    sname VARCHAR(20) NOT NULL,            -- 用户定义：非空约束
    age INT DEFAULT 18,                    -- 用户定义：默认值约束
    email VARCHAR(50) UNIQUE,              -- 用户定义：唯一约束
    gender CHAR(1) CHECK (gender IN ('M','F')),  -- 用户定义：检查约束(8.0+)
    class_id VARCHAR(10),
    FOREIGN KEY (class_id) REFERENCES class(class_id)  -- 参照完整性
);

5.5.5 关系代数（基本了解）

关系代数是关系模型的理论基础，提供了一组对关系进行操作的运算。

传统集合运算（要求两个关系结构相同）：

运算	符号	说明
并（Union）	R ∪ S	属于R或属于S的元组
差（Difference）	R - S	属于R但不属于S的元组
交（Intersection）	R ∩ S	既属于R又属于S的元组
笛卡尔积（Cartesian Product）	R × S	R和S所有元组的组合

专门的关系运算：

运算	符号	说明	SQL对应
选择（Selection）	σ	从关系中选择满足条件的元组	WHERE
投影（Projection）	π	从关系中选择若干属性列	SELECT 列名
连接（Join）	⋈	将两个关系按条件组合	JOIN
除法（Division）	÷	特殊运算	复杂子查询

5.5.6 E-R图转换为关系模型

💡 这是数据库设计的关键步骤：把概念设计的E-R图转换为可以建表的关系模式

转换的三要素

实体类型的转换规则

flowchart LR
    subgraph ER图
        E[实体<br>学生]
        A1((学号))
        A2((姓名))
        A3((年龄))
        E --- A1
        E --- A2
        E --- A3
    end

    subgraph 关系模式
        T[Student表<br>sno PK, sname, age]
    end

    E ==>|转换| T

规则：一个实体转换为一个关系（表），实体的属性成为表的列，实体的码成为表的主键

-- 实体"学生"转换为表
CREATE TABLE student (
    sno CHAR(10) PRIMARY KEY,   -- 学号（主键）
    sname VARCHAR(20),          -- 姓名
    age INT                     -- 年龄
);

联系类型的转换规则

规则总览表

联系类型	转换规则	生成的表数量
1:1	任选一端加入另一端的主键和联系属性	不增加新表
1:N	在N端加入1端的主键和联系属性	不增加新表
M:N	联系转换为新表，包含两端主键+联系属性	增加1个表

① 1:1 联系的转换

flowchart LR
    subgraph ER图
        E1[班级] ---|1:1 管理| E2[班长]
    end

    subgraph 方案1
        T1[班级表<br>+ 班长学号FK]
        T2[班长表]
    end

    subgraph 方案2
        T3[班级表]
        T4[班长表<br>+ 班级号FK]
    end

-- MySQL实现（方案2：在班长表中加入班级号）
CREATE TABLE class (
    class_id VARCHAR(10) PRIMARY KEY,
    class_name VARCHAR(50)
);

CREATE TABLE monitor (
    stu_id VARCHAR(10) PRIMARY KEY,
    name VARCHAR(20),
    class_id VARCHAR(10) UNIQUE,  -- 1:1关系，所以加UNIQUE
    appoint_date DATE,            -- 联系的属性
    FOREIGN KEY (class_id) REFERENCES class(class_id)
);

② 1:N 联系的转换

flowchart LR
    subgraph ER图
        E1[平台] ---|1:N 雇佣| E2[管理员]
    end

    subgraph 关系模式
        T1[平台表]
        T2[管理员表<br>+ 平台商标FK<br>+ 雇佣期限]
    end

规则：在N端加入1端的主键和联系的属性

-- MySQL实现
CREATE TABLE platform (
    trademark VARCHAR(50) PRIMARY KEY,
    name VARCHAR(100),
    company VARCHAR(100)
);

CREATE TABLE admin (
    account_id VARCHAR(20) PRIMARY KEY,
    password VARCHAR(50),
    username VARCHAR(50),
    trademark VARCHAR(50),          -- 加入1端的主键
    employment_period VARCHAR(50),  -- 联系的属性
    FOREIGN KEY (trademark) REFERENCES platform(trademark)
);

③ M:N 联系的转换

flowchart LR
    subgraph ER图
        E1[顾客] ---|M:N 购买| E2[商品]
    end

    subgraph 关系模式
        T1[顾客表]
        T2[商品表]
        T3[订单表<br>顾客ID FK<br>商品ID FK<br>数量<br>时间]
    end

规则：M:N联系必须转换为一个新的关系表，包含两端实体的主键（作为联合主键或外键）和联系的属性

-- MySQL实现
CREATE TABLE customer (
    account_id VARCHAR(20) PRIMARY KEY,
    password VARCHAR(50),
    nickname VARCHAR(50),
    address VARCHAR(200),
    phone VARCHAR(20),
    email VARCHAR(100)
);

CREATE TABLE product (
    product_id VARCHAR(20) PRIMARY KEY,
    name VARCHAR(100),
    stock INT,
    price DECIMAL(10,2),
    type VARCHAR(50)
);

-- M:N联系转换为新表
CREATE TABLE orders (
    order_id VARCHAR(20) PRIMARY KEY,     -- 订单号为主键
    product_id VARCHAR(20),               -- N端主键
    account_id VARCHAR(20),               -- M端主键
    quantity INT,                         -- 联系属性
    order_time DATETIME,                  -- 联系属性
    FOREIGN KEY (product_id) REFERENCES product(product_id),
    FOREIGN KEY (account_id) REFERENCES customer(account_id)
);

转换规则速查表

flowchart TB
    subgraph 转换决策
        A{联系类型?}
        A -->|1:1| B[任选一端加外键]
        A -->|1:N| C[N端加外键]
        A -->|M:N| D[新建联系表]
    end

    style D fill:#FFB6C1

联系类型	转换规则	外键位置	新增表
1:1	合并到任一端	任选一端	❌
1:N	合并到N端	N端	❌
M:N	新建联系表	联系表中	✅

5.5.7 完整转换实例：电商平台

E-R图：

（一） ER图逻辑分析

根据ER图，系统中包含 4个实体 和 3个联系。

实体（Entities）

平台 (Platform)：属性包括商场（标识符）、名称、所属公司。
管理员 (Administrator)：属性包括账号ID（标识符）、账号密码、用户名。
顾客 (Customer)：属性包括账号ID（标识符）、账号密码、昵称、地址、电话、邮箱、备注。
商品 (Product)：属性包括商品编号（标识符）、名称、库存量、图片、描述、单价、类型。

联系（Relationships）

聘用 (Employ)：平台 - 管理员（1:N）。
- 关系属性：聘期。
下单 (Place Order)：顾客 - 商品（M:N）。
- 关系属性：订单编号、数量、下单时间。
上传发布 (Upload/Publish)：管理员 - 商品（M:N）。
- 关系属性：发布时间。

（二）关系模式转换（Conceptual Schema）

根据规则，我们将ER图转换为6个关系模式（4个实体表 + 2个M:N联系产生的表）。

实体类型转换

规则：

实体类型的转换（1）每个实体类型转换成一个关系模式（2）实体的属性即为关系模式的属性（3）实体标识符即为关系模式的键

结果：

平台 (商场/商标, 名称, 所属公司)
顾客 (账号ID, 账号密码, 昵称, 地址, 电话, 邮箱, 备注)
商品 (商品编号, 名称, 库存量, 图片, 描述, 单价, 类型)
管理员 (账号ID, 账号密码, 用户名) [待根据1:N规则修改]

联系类型转换（规则二）

规则：

（1）1 ：1 两个实体类型转换成的两个关系模式中任意一个关系模式的属性中加入另一个关系模式的键和联系类型的属性。（2）1 ：N 在N端实体类型转换成的关系模式中加入1端实体类型的键和联系类型的属性。（3）M : N 将联系类型也转换成关系模式，其属性为两端实体类型的键加上联系类型的属性，而键为两端实体键的组合。

处理“聘用”联系（1:N）：
- 规则：在N端（管理员）加入1端（平台）的键和联系属性。
- 更新后的管理员模式：(账号ID, 账号密码, 用户名, 商场/商标, 聘期)
处理“下单”联系（M:N）：
- 规则：新建关系模式，属性为两端键+联系属性。
- 下单模式：(订单编号, 顾客账号ID, 商品编号, 数量, 下单时间)
- 注：虽然标准M:N主键通常是联合主键，但在订单场景中，“订单编号”本身具有唯一性，通常作为独立主键。
处理“上传发布”联系（M:N）：
- 规则：新建关系模式，属性为两端键+联系属性。
- 上传发布模式：(管理员账号ID, 商品编号, 发布时间)

所以4个不同实体集、2个m:n联系转换为关系模式有六个（实体4个中间量联系2个）

（三）转换得到的完整 SQL 代码

-- 1. 平台表 (Platform)
-- 实体转换：商场作为主键
CREATE TABLE platform (
    trademark VARCHAR(50) NOT NULL COMMENT '商场/商标',
    name VARCHAR(100) COMMENT '名称',
    company VARCHAR(100) COMMENT '所属公司',
    PRIMARY KEY (trademark)
);

-- 2. 管理员表 (Admin)
-- 1:N联系转换：并在表中加入平台的FK和聘期属性
CREATE TABLE admin (
    account_id VARCHAR(20) NOT NULL COMMENT '账号ID',
    password VARCHAR(50) NOT NULL COMMENT '账号密码',
    username VARCHAR(50) COMMENT '用户名',
    trademark VARCHAR(50) COMMENT '所属商场/商标 (外键)',
    employment_period VARCHAR(50) COMMENT '聘期',
    PRIMARY KEY (account_id),
    FOREIGN KEY (trademark) REFERENCES platform(trademark)
);

-- 3. 顾客表 (Customer)
-- 实体转换：包含ER图中所有属性
CREATE TABLE customer (
    account_id VARCHAR(20) NOT NULL COMMENT '账号ID',
    password VARCHAR(50) NOT NULL COMMENT '账号密码',
    nickname VARCHAR(50) COMMENT '昵称',
    address VARCHAR(200) COMMENT '地址',
    phone VARCHAR(20) COMMENT '电话',
    email VARCHAR(100) COMMENT '邮箱',
    remark TEXT COMMENT '备注',
    PRIMARY KEY (account_id)
);

-- 4. 商品表 (Product)
-- 实体转换：包含ER图中所有属性
CREATE TABLE product (
    product_id VARCHAR(20) NOT NULL COMMENT '商品编号',
    name VARCHAR(100) NOT NULL COMMENT '名称',
    stock INT DEFAULT 0 COMMENT '库存量',
    price DECIMAL(10, 2) COMMENT '单价',
    type VARCHAR(50) COMMENT '类型',
    image_url VARCHAR(255) COMMENT '图片',
    description TEXT COMMENT '描述',
    PRIMARY KEY (product_id)
);

-- 5. 订单表 (Orders)
-- M:N联系转换 ("下单")：关联顾客和商品
CREATE TABLE orders (
    order_id VARCHAR(50) NOT NULL COMMENT '订单编号',
    product_id VARCHAR(20) NOT NULL COMMENT '商品编号 (外键)',
    account_id VARCHAR(20) NOT NULL COMMENT '顾客账号ID (外键)',
    quantity INT COMMENT '数量',
    order_time DATETIME COMMENT '下单时间',
    PRIMARY KEY (order_id),
    FOREIGN KEY (product_id) REFERENCES product(product_id),
    FOREIGN KEY (account_id) REFERENCES customer(account_id)
);

-- 6. 商品发布记录表 (Product_Publish)
-- M:N联系转换 ("上传发布")：关联管理员和商品
CREATE TABLE product_publish (
    admin_id VARCHAR(20) NOT NULL COMMENT '管理员账号ID (外键)',
    product_id VARCHAR(20) NOT NULL COMMENT '商品编号 (外键)',
    publish_time DATETIME COMMENT '发布时间',
    -- 使用联合主键，确保同一个管理员对同一个商品的发布记录唯一（或根据业务需求调整）
    PRIMARY KEY (admin_id, product_id),
    FOREIGN KEY (admin_id) REFERENCES admin(account_id),
    FOREIGN KEY (product_id) REFERENCES product(product_id)
);

5.6 物理结构设计

💡 物理设计决定数据在存储介质上的存储结构和存取方法

5.6.1 物理设计的主要任务

任务	内容
确定存储结构	选择存储引擎（如InnoDB、MyISAM）
设计索引	确定哪些列需要建立索引
确定存储位置	数据文件、日志文件的存放位置
确定系统参数	缓冲区大小、并发连接数等

5.6.2 索引设计原则

建议建索引的情况	不建议建索引的情况
主键列	数据量很小的表
外键列	经常增删改的表
经常用于WHERE条件的列	取值很少的列（如性别）
经常用于ORDER BY的列	很少用于查询的列
经常用于JOIN的列	数据重复率高的列

-- 创建索引示例
CREATE INDEX idx_student_name ON student(sname);
CREATE INDEX idx_sc_sno ON sc(sno);
CREATE UNIQUE INDEX idx_student_email ON student(email);

-- 复合索引（多列索引）
CREATE INDEX idx_student_class_age ON student(class_id, age);

5.6.3 MySQL存储引擎选择

存储引擎	特点	适用场景
InnoDB	支持事务、行级锁、外键	需要事务支持的应用（默认）
MyISAM	不支持事务、表级锁、速度快	读多写少、不需要事务
Memory	数据存储在内存中	临时表、缓存

-- 指定存储引擎
CREATE TABLE orders (
    id INT PRIMARY KEY,
    amount DECIMAL(10,2)
) ENGINE=InnoDB;

-- 查看表的存储引擎
SHOW TABLE STATUS LIKE 'orders';

5.7 数据库实施与维护

5.7.1 实施阶段的任务

任务	说明
创建数据库	执行DDL语句创建数据库和表
数据导入	将原有数据导入新数据库
编写应用程序	开发数据库应用系统
测试	功能测试、性能测试
试运行	在实际环境中试运行

5.7.2 维护阶段的任务

任务	说明
性能监控	监控数据库运行状态
备份恢复	定期备份，故障时恢复
安全管理	用户权限管理、审计
结构调整	根据需求变化调整表结构
性能优化	优化查询、调整索引

-- 数据库备份（命令行）
mysqldump -u root -p database_name > backup.sql

-- 数据库恢复（命令行）
mysql -u root -p database_name < backup.sql

-- 查看数据库状态
SHOW STATUS;

-- 查看进程列表
SHOW PROCESSLIST;

⬅️ 数据库设计与E-R模型 🏠 00-数据库 ➡️ MySQL 数据库管理

05-逻辑设计与实施维护

逻辑设计与实施维护

5.5 逻辑结构设计

5.5.1 关系模型核心概念

5.5.2 键的概念

主表与从表的关系

5.5.3 关系的性质

5.5.4 关系的完整性约束

5.5.5 关系代数（基本了解）

5.5.6 E-R图转换为关系模型

转换的三要素

实体类型的转换规则

联系类型的转换规则

规则总览表

① 1:1 联系的转换

② 1:N 联系的转换

③ M:N 联系的转换

转换规则速查表

5.5.7 完整转换实例：电商平台

（一） ER图逻辑分析

（二）关系模式转换（Conceptual Schema）

（三）转换得到的完整 SQL 代码

5.6 物理结构设计

5.6.1 物理设计的主要任务

5.6.2 索引设计原则

5.6.3 MySQL存储引擎选择

5.7 数据库实施与维护

5.7.1 实施阶段的任务

5.7.2 维护阶段的任务

💬 评论

逻辑设计与实施维护

5.5 逻辑结构设计

5.5.1 关系模型核心概念

5.5.2 键的概念

主表与从表的关系

5.5.3 关系的性质

5.5.4 关系的完整性约束

5.5.5 关系代数（基本了解）

5.5.6 E-R图转换为关系模型

转换的三要素

实体类型的转换规则

联系类型的转换规则

规则总览表

① 1:1 联系的转换

② 1:N 联系的转换

③ M:N 联系的转换

转换规则速查表

5.5.7 完整转换实例：电商平台

（一） ER图逻辑分析

（二） 关系模式转换（Conceptual Schema）

（三）转换得到的完整 SQL 代码

5.6 物理结构设计

5.6.1 物理设计的主要任务

5.6.2 索引设计原则

5.6.3 MySQL存储引擎选择

5.7 数据库实施与维护

5.7.1 实施阶段的任务

5.7.2 维护阶段的任务

💬 评论

（二）关系模式转换（Conceptual Schema）