数据库原理与应用 课件 第5章 数据库设计

数据库原理与应用技术第5章数据库设计本章知识导图本章学习目标了解：基本流程与需求分析掌握数据库设计基本流程，熟悉跟班作业法、访谈等需求收集方法及核心任务。理解：ER图扩展与逻辑衔接理解ER图特殊属性、弱实体及继承联系；掌握逻辑结构到物理结构的衔接逻辑。掌握：ER图转换与优化原则熟练掌握ER图绘制与转换规则，精通关系模型优化及物理结构中索引设计原则。能够：独立开展设计与建模针对简单业务逻辑独立完成需求分析，设计ER图并转化为满足3NF的关系模式。养成：规范习惯与全周期意识养成规范设计习惯，兼顾业务适配、性能与数据安全，具备数据库全生命周期迭代调整的职业意识。目录5.1数据库概述设计任务、目标与原则、基本流程5.2需求分析任务、方法、结果5.3概念结构设计目标、ER图扩展、设计方法与步骤5.4逻辑结构设计ER图转换、关系模式优化、用户子模式设计5.5物理结构设计内容和方法、索引设计5.6数据库实施与维护实施、运行和维护本章小结5.1数据库概述设计任务·目标与原则·基本流程5.1.1数据库设计概述数据库设计的重要性数据库设计是连接用户业务需求与技术实现的关键环节。

其质量直接决定了数据处理的效率、安全性与系统的整体可用性，是保障后续应用开发顺利进行的基石。数据库设计的核心任务信息管理需求确定在数据库中需要存储和管理哪些具体的数据对象，定义数据的结构与属性。数据操作需求明确对数据对象需要进行的操作类型，如查询、增加、删除、修改等，以满足业务逻辑需求。5.1.2数据库设计的目标与原则设计目标贴合业务需求准确映射用户的业务逻辑，支持各类数据操作，确保系统实用性。保障数据质量确保数据的完整性与一致性，避免冗余与冲突，维护数据可信度。优化系统性能减少响应时间，降低资源占用，确保高并发场景下的系统稳定性。设计原则一致性原则数据定义、编码规则、命名规范保持统一，降低维护成本。完整性原则通过约束确保数据符合业务规则，防止无效或错误数据进入系统。最小冗余原则减少数据冗余，实现“一次存储、多次引用”，提高存储效率。可扩展性原则预留扩展空间，便于未来业务逻辑变化时，系统能够灵活适配。5.1.3数据库设计的基本流程01.需求分析收集并分析用户需求，形成需求分析文档，确定系统边界。02.概念结构设计构建独立于DBMS的概念数据模型（如ER图），抽象信息结构。03.逻辑结构设计将概念模型转化为具体DBMS支持的数据模型（如关系模型）并优化。04.物理结构设计设计数据库的物理存储结构与存取策略，如索引、分区等。05.数据库实施创建数据库对象，加载数据，开发应用程序并进行测试试运行。06.运行和维护持续进行日常维护、性能监控、故障排除与系统优化。5.2需求分析任务、方法、结果5.2.1需求分析的任务数据需求分析确定要存储的数据，包括实体识别、属性定义和数据量估算。功能需求分析确定支持的数据操作，包括基础操作、业务流程关联和查询复杂度分析。性能与约束分析确定数据操作的质量要求，包括业务与技术约束及性能指标（响应时间、并发量）。5.2.2需求分析的方法标准调查步骤1.调查组织机构情况了解组织架构、部门职能及人员分工2.调查业务活动情况深入各部门，掌握具体业务流程与操作细节3.明确系统要求协助用户梳理功能需求、性能指标及特殊约束4.确定系统边界界定新系统与外部环境及其他系统的接口常用收集方法跟班作业深入业务一线，亲身体验流程，获取真实感受访谈法一对一或小组访谈，挖掘用户深层需求与痛点检查文档梳理现有的报表、单据和规章制度，分析数据流向调查问卷针对大量用户设计问卷，收集共性需求与量化数据5.2.3需求分析的结果数据字典对数据库中各类数据对象的描述集合，包括数据项、数据结构、数据流、数据存储和处理过程。需求说明书对需求分析成果的结构化汇总，涵盖需求背景、范围、详细内容、验收标准等。需求确认需求分析文档需经过用户和专家评审确认，作为后续设计的依据。5.3概念结构设计目标、ER图扩展、设计方法与步骤5.3.1概念结构设计的目标精准反映需求完整覆盖所有数据实体、属性及业务关联，确保无遗漏或偏差。具备高可读性采用直观的表达方式（如ER图），便于用户理解和参与评审。预留扩展空间具备一定的抽象性与通用性，能够灵活适配未来业务需求的变化。5.3.2ER图的扩展描述（属性）多值属性用双椭圆表示，实体可对应多个取值。例如：学生的“联系电话”派生属性用虚线椭圆表示，由其他属性推导而来。例如：由出生日期计算的“年龄”复合属性用嵌套椭圆表示，可拆分为多个子属性。例如：“地址”可拆分为省、市、区、街道图示：扩展属性的ER图表示方法5.3.2ER图的扩展描述（继承联系）核心概念：一般与特殊用于描述实体间“一般与特殊”的层级关系，子类继承父类属性并拥有特有属性。实体分类定义父类：一般实体，包含子类的共同属性。子类：特殊实体，继承父类并拥有特有属性。图形表示方法使用三角形连接，顶点指向父类，底边连接子类，清晰展示层级继承关系。典型示例：高校人员管理父类“人员”->子类“教师”、“行政人员”5.3.2ER图的扩展描述（基数）核心定义(Definition)描述实体间联系的数量约束，记为l..h。其中l为最小出现次数，h为最大出现次数。主要作用(Purpose)细化1:1、1:N、M:N等联系类型，明确业务规则中实体关联的具体数量限制。应用示例(Example)学生选课：学生(2..6)门课；课程(0..40)个学生。第一步：识别局部实体根据数据字典拆分业务模块，识别核心实体。要求：实体名称唯一，避免歧义第二步：定义属性与主键为实体添加描述其特征的属性。确定能唯一标识实体实例的主键。第三步：确定实体间联系分析业务逻辑，明确实体间的相互作用。判断联系类型（1:1,1:N,M:N），并按需定义联系属性。5.3.3概念结构设计–局部绘制局部ER图为每个业务模块独立绘制，确保模块内逻辑完整使用标准符号，并正确标注主键、外键等特殊属性整合全局ER图将多个局部ER图合并，消除冗余，形成完整的系统模型。核心任务：解决冲突实体冲突：统一命名与含义，消除同名异义或异名同义属性冲突：统一数据类型、长度及取值范围联系冲突：统一联系的类型（如1:1,1:N,M:N）5.3.3概念结构设计–从局部到整体优化目的旨在提升模型整体质量，通过消除冗余，避免因结构过于复杂导致的维护困难，同时确保语义上的一致性与准确性。识别冗余要素冗余联系：可由其他元素推导得出。冗余属性：可由其他属性推导或重复存储。消除策略核心方法是删除识别出的冗余元素。在分析过程中，可借助计算最小函数依赖集等形式化方法辅助判断，确保优化彻底。5.3.3概念结构设计–消除冗余符号规范严格遵循“实体-矩形、属性-椭圆形、联系-菱形”的符号规则，线段连接准确。命名规范名称简洁、表意，使用业务术语，格式统一（如实体用名词，联系用动词）。主键唯一每个实体必须有且仅有一个主键，确保能唯一标识实例。联系类型准确联系类型（1:1,1:N,M:N）需严格匹配业务逻辑，避免逻辑错误。属性确定属性必须有明确归属，不可无主或共享；联系的属性不可错归为实体属性。5.3.3概念结构设计–核心规则5.3.3概念结构设计–案例设计一个工厂产品、零件和材料的基本ER模型。实体产品：编号，性能参数，价格零件：零件号，规格材料：材料名，价格，库存量联系每种零件只能由一种材料制造，一种材料可以用于制作多种零件一种产品需要多种零件组装，一种零件可以用于组装多种零件局部E-R图设计技术部门：关心的是产品的编号、性能、由哪些零件组成，每个零件的零件号、消耗的材料名和数量。供销部门：关心的是产品的编号、价格和库存量。任务设计局部ER图

全局ER图（初步）

全局ER图（优化）。5.3.3概念结构设计–案例技术部门的局部ER图产品编号组成性能参数零件数零件号规格耗用量材料名消耗零件材料mn1m供销部门的局部ER图编号价格用量材料名价格库存量使用产品材料mn5.3.3概念结构设计–案例全局ER图（初步）产品编号组成性能参数零件数零件号规格耗用量价格用量材料名价格库存量使用消耗零件材料mn1mmn5.3.3概念结构设计–案例全局ER图（优化）产品编号组成性能参数零件数零件号规格耗用量价格材料名价格库存量消耗零件材料mn1m5.4逻辑结构设计ER图转换|关系模式优化|用户子模式设计5.4.1E-R图向关系模式的转换实体的转换基本规则一个实体对应一个关系模式。实体的属性即为关系模式的属性，实体的码即为关系模式的码。具有相同码的关系模式可以合并。联系的转换1:1联系将一方的码加入另一方，或独立建立关系模式。1:N联系将一方（1方）的码加入另一方（N方）或独立建立关系模式。M:N联系必须独立建立关系模式，包含双方码和联系属性。多元联系必须独立建立关系模式，包含双方码和联系属性。特殊情况的转换多值属性为多值属性单独建立一个关系模式。派生属性可以由其他属性得到，省略。复合属性将其拆分为多个简单属性。继承联系采用单表继承或多表继承的方式。案例：1:1联系的转换方法一：将“教师”的码加入“办公室”关系教师(教师号,姓名,职称)办公室(办公室编号,楼栋,面积,教师号)方法二：将“办公室”的码加入“教师”关系教师(教师号,姓名,职称,办公室编号)办公室(办公室编号,楼栋,面积)方法三：独立建立一个关系模式教师(教师号,姓名,职称)办公室(办公室编号,楼栋,面积)办公(办公室编号,教师号)案例：1:N联系的转换转换方法：引入外键将“部门”的码加入“员工”关系模式中，形成如下结构：部门(部门编号,部门名称,部门电话)员工(员工编号,姓名,职位,入职日期,部门编号)核心原理说明在1:N联系中，总是将“1”方的码加入“N”方的关系模式中。案例：M:N联系的转换转换方法：建立独立关系模式学生实体：学生(学号,姓名,性别,所在院系)课程实体：课程(课程号,课程名,学分,

先修课程号)选课联系：选课(学号,课程号,成绩)关键说明：选课关系的码（主键）是“学号”和“课程号”的组合。它不仅记录了学生的选课情况，还存储了成绩这一联系属性。案例：多元联系的转换转换方法：建立独立关系模式供应商(供应商编号,供应商名,地址)工程(工程编号,工程名称)设备(设备号,设备名)供应(供应商编号,工程编号,设备号,数量)关键说明：供应关系的码（主键）是“供应商号”、“工程编号”和“设备号”的组合。案例：特殊情况的转换多值属性：学生联系电话处理方式：单独建立关系模式学生(学号,姓名,专业)学生电话(学号,电话号码)复合属性：学生地址处理方式：拆分为简单属性并入原实体学生(学号,姓名,专业,省,市,区,街道)或者：将地址视作一个属性（类型为字符串）继承：职工

教师/行政人员处理方式1：父类和子类均独立为关系模式职工（工号，姓名，性别，联系电话，人员类型）教师（工号，院系，职称）行政人员（工号，部门，级别）处理方式2：仅子类均独立为关系模式教师（工号，姓名，性别，联系电话，人员类型,院系，职称）行政人员（工号，姓名，性别，联系电话，人员类型，部门，级别）处理方式3：父类与子类合并为单一关系模式教师（工号，姓名，性别，联系电话，人员类型,院系，职称,部门，级别）案例：实体集内部实体间联系的转换职工工号姓名年龄民意测验性别职称领导1m职工（工号，姓名，年龄，性别，职称，领导者工号，民意测验）零件代号名称数量价格组装mn零件（代号，名称，价格）组装（代号，组装件代号，数量）案例：弱实体的转换职工（工号，姓名，年龄，性别，职务）亲属（工号，亲属姓名，亲属关系）职工工号姓名年龄性别职称亲属亲属关系亲属姓名有综合案例P(P#，PP，PC)S(S#，SP)M(M#，MC)L(L#，LC)P-S(P#，S#，Q1)S-M(S#，M#，Q2)M-L(M#，L#，Q3)Q1组成mnP#PP产品PSPS#零件SQ3存放mn材料MM#MC仓库LL#LOCPCQ2消耗n1合并？5.4.2关系模式的优化-关系规范化优化的必要性转换后的关系模式可能存在数据冗余和更新异常，需要进一步优化以确保逻辑模型的健壮性。1.确定数据依赖深入分析关系模式中的函数依赖（FD），明确属性间的约束关系，这是优化的基础。2.关系模式分解根据数据依赖将大模式分解为小模式，消除部分依赖和传递依赖，解决数据冗余问题。3.检查范式确保分解后的关系模式满足更高的范式要求，通常目标是达到3NF（第三范式）或BCNF。4.综合权衡在规范化和查询效率之间寻找平衡点，必要时可适当保留冗余以提升系统性能。案例：关系模式优化优化前：存在数据冗余与异常原始模式：订单(订单号,客户号,客户名,商品号,商品名,数量,单价)核心问题：客户名、商品名等属性重复存储，导致更新异常依赖分析：存在传递依赖（如订单号→客户号→客户名）优化后：满足3NF规范分解结果：订单、客户、商品、订单明细（共4个关系）优化效果：彻底消除了数据冗余，解决了插入、删除和更新异常设计原则：遵循“一事一地”原则，每个表只描述一个实体垂直分割定义：按属性相关性拆分，适用于属性过多且访问频率差异大的场景。目的：减少无效数据加载，提升高频字段查询效率。基本信息表ID/姓名/性别扩展信息表兴趣/地址/简介水平分割定义：按行划分独立子集（如时间、地区），适用于超大表。目的：缩小扫描范围，缓解单表性能压力。订单_2023表：仅包含2023年数据订单_2024表：仅包含2024年数据核心区别总结：垂直分割是“按列拆分”，解决列宽问题；水平分割是“按行拆分”，解决行多问题。两者都是为了优化数据库查询性能。5.4.2关系模式的优化-分割5.4.3用户子模式设计什么是用户子模式？用户子模式（外模式）是数据库整体逻辑模式的一个子集，是为不同用户或应用程序设计的局部数据视图。核心设计原则根据不同用户的需求和权限，设计针对性的用户视图，确保视图既满足业务需求又符合权限控制。主要作用与价值保障数据安全限制用户只能访问其权限范围内的数据，防止越权操作。简化用户操作提供符合业务习惯的数据视图，隐藏复杂的底层结构，提升易用性。确保数据独立性底层逻辑模式变化时，只需调整映射关系即可保证应用程序稳定。5.5物理结构设计内容和方法、索引设计5.5.1物理结构设计的内容和方法核心设计内容存储结构设计确定存储位置及文件组织方式（堆文件、顺序文件、散列文件）。存取路径选择设计数据的存取路径，主要通过索引来实现高效查找。索引设计选择合适的列建立索引，并根据查询模式选择最优索引类型。分区设计对大表进行逻辑或物理分区，显著提高查询和维护效率。标准设计方法确定数据分布策略根据数据的使用频率和重要性，将数据分布在不同的存储设备上。选择存储结构根据数据的特点（如大小、更新频率）和操作需求选择文件组织方式。设计访问方法核心在于索引设计，这是提高系统查询效率的关键环节。5.5.2索引设计索引的作用索引是一种数据结构，如同书的目录，能帮助数据库快速定位数据，显著提升查询效率。核心设计原则选择合适的列：优先在主键、外键及WHERE子句常用列上建立。避免过度索引：索引会增加写操作开销，需权衡查询与更新性能。考虑索引类型：B+树适用于范围查询，哈希索引适用于等值查询。定期维护索引：定期重建或整理，防止索引碎片化影响效率。B+树索引结构示意图5.6数据库实施与维护实施、运行和维护5.6.1数据库实施1.创建数据库对象使用DDL语句（如CREATETABLE,CREATEINDEX）创建数据库、表、视图、索引等，构建物理存储结构。2.数据加载将实际的业务数据导入到数据库中，可利用数据库自带的导入工具（如SQLLoader）或编写专门的迁移程序。3.应用程序开发开发与数据库交互的应用程序，封装数据访问逻辑，实现用户所需的各项业务功能和操作界面。4.系统测试与试运行进行全面的测试（功能、性能、压力