Skip to main content

任务三 逻辑结构设计

逻辑结构设计是什么​

简单说：把人人能看懂的E-R图，变成计算机能处理的具体表结构，同时保证数据的高质量和无冗余。

逻辑结构设计是数据库设计的第三阶段，是将独立于技术的概念模型（E-R图）转换为特定数据库管理系统（DBMS）所支持的数据模型的过程。简单说，就是把E-R图“翻译”成具体的表结构。

逻辑结构设计是从“业务蓝图”到“技术图纸”。

二、核心目标​

  1. 转换模型：将E-R图转换为关系模型（表、字段、键）
  2. 规范化设计：消除数据冗余和异常，通常要满足第三范式（3NF）
  3. 优化结构：权衡规范化和性能，设计出合理的关系模式
  4. 保持语义：确保转换后的表结构能完全表达原E-R图的业务含义

三、逻辑结构设计的主要任务​

任务1：E-R图向关系模式的转换（核心工作）​

转换规则：

E-R图元素转换规则示例
实体→ 一张表学生实体 → 学生表
实体的属性→ 表的字段学号、姓名 → student_id, name
实体的主键→ 表的主键学号 → student_id (PRIMARY KEY)
1:1联系合并到任意一方的表中，或单独建表院长-学院：院长表加college_id外键
1:n联系在“n”方的表中添加外键部门-员工：员工表加dept_id外键
m:n联系→ 单独一张关联表学生-课程 → 选课表(student_id, course_id, 成绩)
联系的属性→ 对应表的字段选课联系的“成绩” → 选课表的grade字段

任务2：数据模型的规范化​

为什么要规范化？ 消除以下问题：

  • 数据冗余：相同数据存储多次，浪费空间
  • 更新异常：修改一处，需修改多处，易不一致
  • 插入异常：想插入数据，但因缺少部分信息而无法插入
  • 删除异常：删除一条信息，意外丢失其他信息

常用范式：

  • 第一范式（1NF）：字段不可再分（原子性）

    -- 错误：一个字段存多个电话号码
    CREATE TABLE student (phone VARCHAR(100)); -- 存 "13800138000,13900139000"

    -- 正确：拆分为多行或单独表
    CREATE TABLE student_phone (student_id INT, phone VARCHAR(20));

  • 第二范式（2NF）：消除部分依赖（非主属性必须完全依赖于主键）

    -- 问题表：订单明细(订单号, 产品号, 产品名称, 数量, 单价)
    -- 问题：产品名称只依赖于产品号（部分依赖），不依赖于完整主键(订单号,产品号)

    -- 解决：拆分为两张表
    -- 订单明细表(订单号, 产品号, 数量, 单价)  -- 完全依赖于完整主键
    -- 产品表(产品号, 产品名称)              -- 产品信息单独管理

  • 第三范式（3NF）：消除传递依赖（非主属性不能依赖于其他非主属性）

    -- 问题表：学生(学号, 姓名, 院系号, 院系名称, 院系地址)
    -- 问题：院系名称、地址依赖于院系号（非主属性），而院系号依赖于学号（传递依赖）

    -- 解决：拆分为两张表
    -- 学生表(学号, 姓名, 院系号)
    -- 院系表(院系号, 院系名称, 院系地址)

任务3：关系模式优化与调整​

在规范化基础上，根据实际需求进行反规范化或优化：

  • 适度反规范化：为提高查询性能，故意保留一些冗余 
    -- 订单表冗余商品名称，避免每次查询都要联表
    CREATE TABLE orders (
        order_id INT PRIMARY KEY,
        product_id INT,
        product_name VARCHAR(100),  -- 冗余字段，违反3NF但提高性能
        quantity INT,
        FOREIGN KEY (product_id) REFERENCES products(product_id)
    );
  • 水平/垂直分割：将大表拆分
  • 设计派生字段：如订单总金额、库存警戒标志等

任务4：完整性约束定义​

  • 主键约束：唯一标识每行记录
  • 外键约束：保证参照完整性
  • 唯一约束：确保某字段值唯一
  • 检查约束：字段值必须满足条件（如年龄>0）
  • 非空约束：字段不能为空
  • 默认值：字段的默认值

三、完整案例：图书馆管理系统​

概念模型回顾（E-R图）：​

  • 实体：图书、读者、读者类别
  • 联系：读者 属于 读者类别（n:1），读者 借阅 图书（1:n）

逻辑结构设计过程：​

步骤1：实体转表（基础转换）

-- 读者类别表
CREATE TABLE reader_category (
    category_id INT PRIMARY KEY,          -- 来自实体主键
    category_name VARCHAR(50) NOT NULL,   -- 来自实体属性
    max_books INT NOT NULL,               -- 最大借书量
    loan_days INT NOT NULL                -- 借期天数
);

-- 读者表
CREATE TABLE reader (
    reader_id INT PRIMARY KEY,            -- 读者卡号
    name VARCHAR(100) NOT NULL,
    phone VARCHAR(20),
    register_date DATE DEFAULT CURRENT_DATE,
    category_id INT NOT NULL,             -- 来自1:n联系的外键
    FOREIGN KEY (category_id) REFERENCES reader_category(category_id)
);

-- 图书表
CREATE TABLE book (
    book_id INT PRIMARY KEY,              -- 图书编号
    title VARCHAR(200) NOT NULL,
    author VARCHAR(100),
    publisher VARCHAR(100),
    total_copies INT DEFAULT 1,           -- 总库存
    available_copies INT                  -- 可用库存（可派生，但为性能保留）
);

步骤2：m:n联系转表（假设允许续借，需要独立管理借阅记录）

-- 借阅记录表（处理借阅联系及属性）
CREATE TABLE loan_record (
    loan_id INT PRIMARY KEY AUTO_INCREMENT,
    reader_id INT NOT NULL,
    book_id INT NOT NULL,
    borrow_date DATE NOT NULL DEFAULT CURRENT_DATE,  -- 借书日期
    due_date DATE NOT NULL,                          -- 应还日期
    return_date DATE,                                -- 实际归还日期
    renew_count INT DEFAULT 0,                       -- 续借次数
    FOREIGN KEY (reader_id) REFERENCES reader(reader_id),
    FOREIGN KEY (book_id) REFERENCES book(book_id),
    -- 约束：一本书同一时间只能被一个读者借阅（简化模型）
    UNIQUE KEY unique_borrowing (book_id, return_date)
);

步骤3：规范化检查与优化

  • 检查是否满足3NF
  • 发现：book.available_copies 可从 total_copies - 借出数量 计算，属于派生属性
  • 决策：保留冗余，因为频繁查询且计算成本高

步骤4：定义完整性约束

-- 添加更多约束
ALTER TABLE loan_record
ADD CONSTRAINT chk_dates CHECK (due_date > borrow_date),
ADD CONSTRAINT chk_return_date CHECK (return_date IS NULL OR return_date >= borrow_date);

ALTER TABLE reader_category
ADD CONSTRAINT chk_max_books CHECK (max_books > 0);

-- 添加索引优化查询
CREATE INDEX idx_reader_category ON reader(category_id);
CREATE INDEX idx_loan_reader ON loan_record(reader_id, return_date);
CREATE INDEX idx_loan_due_date ON loan_record(due_date) WHERE return_date IS NULL;

步骤5：形成最终关系模式

读者类别(category_id, category_name, max_books, loan_days)
读者(reader_id, name, phone, register_date, category_id) → 外键: category_id
图书(book_id, title, author, publisher, total_copies, available_copies)
借阅记录(loan_id, reader_id, book_id, borrow_date, due_date, return_date, renew_count)
    → 外键: reader_id, book_id

四、逻辑结构设计的输出成果​

  1. 数据库模式图：展示所有表及其关系
  2. 数据字典：详细描述每个表、字段 
    ## 表：loan_record
    | 字段名 | 类型 | 空值 | 默认值 | 说明 | 约束 |
    |--------|------|------|--------|------|------|
    | loan_id | INT | NO | AUTO_INCREMENT | 借阅ID | 主键 |
    | reader_id | INT | NO | - | 读者ID | 外键→reader |
    | borrow_date | DATE | NO | CURRENT_DATE | 借书日期 | ≥注册日期 |
  3. SQL DDL脚本：可直接执行的建表语句
  4. 完整性约束文档：所有业务规则的正式定义

五、逻辑 vs 概念 vs 物理设计​

方面概念结构设计逻辑结构设计物理结构设计
关注点业务视角数据视角技术视角
核心问题“业务有什么？”“数据怎么组织？”“数据怎么存储？”
产出物E-R图关系模式（表结构）文件结构、索引
技术依赖完全独立依赖数据模型类型依赖具体DBMS
主要活动识别实体关系规范化、表设计存储分配、优化
例子画“读者借书”关系设计reader、book、loan_record表为loan_record建索引

类比（续接房屋建造）：

  • 概念设计：房间布局草图（3卧2厅）
  • ✅ 逻辑设计：详细的施工图纸（标出每面墙尺寸、门窗位置、管道走向）
  • 物理设计：材料清单和施工方法（用红砖还是混凝土）

六、总结​

逻辑结构设计的本质：在“业务理解”和“技术实现”之间架起桥梁，通过科学的规范化理论和实际的经验判断，设计出既正确反映业务、又高效可靠的数据结构。

这是数据库设计中最需要技术严谨性的一步，既要保证数据理论的正确性，又要为后续的物理实现和性能优化打下坚实基础。一个好的逻辑设计，能让数据库在未来多年的演化中保持稳定和灵活。