项目10 数据库设计（课件）-《数据库MYSQL》同步教学（河北科技出版社）

数据库原理与应用技术MySQL 项目10 数据库设计 1 《数据库原理与应用技术-MySQL》 数据库设计 任务1 需求分析 任务2 概念结构设计 任务3 逻辑结构设计 任务4 物理结构设计 任务5 数据库的实施和维护 任务6 关系模式的规范化 1 《数据库原理与应用技术-MySQL》 任务1 需求分析 子任务1 需求分析的内容 子任务2 需求分析的工具 1 《数据库原理与应用技术-MySQL》 子任务1 需求分析的内容 需求分析是数据库设计中非常重要的一个步骤，它是其他各步的基础。 数据库应用系统的需求分析包括数据需求分析、功能需求分析（数据处理需求分析、业务规则需求分析）、性能需求分析（数据操作响应时间或数据访问响应时间、系统吞吐量、允许并发访问的最大用户数、每秒事务处理系统代价值）和其他需求分析（存储需求分析、安全性需求分析、备份和恢复需求分析）。 1 《数据库原理与应用技术-MySQL》 （1）数据需求分析 数据需求分析是从对数据组织与存储的设计角度，辨识应用领域所管理的各类数据项和数据结构，与数据处理需求分析结果一起，组成数据字典，形成数据规范说明书。 （2）功能需求分析 功能需求分析主要针对DBAS应具有的功能进行分析，是DBAS需求分析的核心环节，总体上可分为数据处理需求分析与业务规则需求分析。 数据处理需求分析从数据访问和处理的角度，明确对各数据项所需要进行的数据访问操作。在系统规划与分析阶段，DBAS开发者已经明确了各类用户视图，因此数据处理需求分析阶段可以从这些视图出发。 1 《数据库原理与应用技术-MySQL》 （3）性能需求分析 性能需求分析描述了系统应当做到什么程度，分析DBAS应具有的性能指标。 （4）性能需求分析 其他需求分析包括存储需求和安全性需求等。存储需求分析是指估计DBAS需要的数据存储量，如数据库所存储的数据总量。安全性需求分析主要用于数据库安全设计，避免被非法使用和攻击。 1 《数据库原理与应用技术-MySQL》 子任务2 需求分析的工具 数据库需求分析阶段的两个主要工具是数据流图和数据字典。 1 《数据库原理与应用技术-MySQL》 数据流图 数据流图（Data Flow Diagram，DFD）表达了数据与处理的关系。 数据流图的基本元素如图所示。 1 《数据库原理与应用技术-MySQL》 数据流图 例10.1 学生选课系统的数据流图如图所示。 1 《数据库原理与应用技术-MySQL》 数据字典 数据字典是各类数据描述的集合，它是进行详细的数据收集和数据分析后所获得的主要成果。数据字典在数据库设计中占有很重要的地位。数据字典通常包括以下5个部分： 1 《数据库原理与应用技术-MySQL》 数据字典 （1）数据项 数据项是不可再分的数据单位，它的描述为： 数据项=｛数据项名，数据项含义说明，别名，数据类型，长度，取值范围，取值含义，与其他数据项的逻辑关系｝ 其中，“取值范围”和“与其他数据项的逻辑关系”两项定义了数据的完整性约束条件，是设计数据完整性检验功能的依据。 1 《数据库原理与应用技术-MySQL》 数据字典 （2）数据结构 数据结构反映了数据之间的组合关系，一个数据结构可以由若干个数据项组成，也可以由若干个数据结构组成，或由若干数据项和数据结构混合组成。数据结构的描述为： 数据结构=｛数据结构名，含义说明，组成，｛数据项或数据结构｝｝ 1 《数据库原理与应用技术-MySQL》 数据字典 （3）数据流 数据流是数据结构在系统内传输的路径。数据流的描述为： 数据流=｛数据流名，说明，数据流来源，数据流去向，组成：｛数据结构｝，平均流量，高峰期流量｝ 其中，“数据流来源”说明该数据流来自哪个过程；“数据流去向”说明该数据流将到哪个过程；“平均流量”是指在单位时间（每天、每周、每月等）内传输的次数；“高峰期流量”则是指在高峰时期的数据流量。 1 《数据库原理与应用技术-MySQL》 数据字典 （4）数据存储 数据存储是数据及其结构停留或保存的地方，也是数据流的来源和去向之一。数据存储可以是手工文档、手工凭单或计算机文档。数据存储的描述为： 数据存储=｛数据存储名，说明，编号，输入的数据流，输出的数据流，组成：{数据结构｝，数据量，存取频度，存取方式｝ 其中，“数据量”说明每次存取多少数据；“存取频度”指每小时或每天或每周存取几次、每次存取多少数据等信息；“存取方式”指是批处理还是联机处理，是检索还是更新，是顺序检索还是随机检索等；“输入的数据流”要指出其数据的来源；“输出的数据流”要指出其数据去向。 。 1 《数据库原理与应用技术-MySQL》 数据字典 （5）处理过程 处理过程的具体处理逻辑一般用判定表或判定树来描述。数据字典中只需要描述处理过程的说明性信息，通常包括以下内容： 处理过程=｛处理过程名，说明，输入：｛数据流｝，输出：｛数据流｝，处理：｛简要说明｝｝ 其中，“简要说明”中主要说明该处理过程的功能及处理频度要求，如单位时间里处理多少事务、多少数据量、响应时间要求等。 。 1 《数据库原理与应用技术-MySQL》 任务2 概念结构设计 1 《数据库原理与应用技术-MySQL》 1. 概念结构设计概述 将需求分析得到的用户需求抽象为信息模型的过程就是概念结构设计。 对用户要求描述的现实世界（可能是一个工厂、一个商场或一个学校等），通过对其包含的事物及联系进行分类、聚集和概括，建立抽象的概念数据模型。这个概念模型应反映现实世界各部门的信息结构、信息流动情况、信息间的相互制约关系以及各部门对信息存储、查询和加工的要求等。所建立的模型应避开数据库在计算机上的具体实现细节，用一种抽象的形式表示出来。 1 《数据库原理与应用技术-MySQL》 1.概念结构设计概述 以E-R模型为例：第一步先明确现实世界各部门所含的各种实体及其属性、实体间的联系以及对信息的制约条件等，从而给出各部门内所用信息的局部描述（在数据库中称为用户的局部视图）；第二步将前面得到的多个用户的局部视图集成为一个全局视图，即用户要描述的现实世界的概念模型。可以把采用E-R方法的概念结构设计分为以下三步：设计局部E-R图、设计全局E-R图和优化全局E-R图。 1 《数据库原理与应用技术-MySQL》 2. 概念结构的特点 概念模型是现实世界的一个真实模型：概念模型应能真实、充分地反映现实世界，能满足用户对数据的处理要求。 概念模型应当易于理解：概念模型只有被用户理解后，才可以与设计者交换意见，参与数据库的设计。 概念模型应当易于更改：由于现实世界（应用环境和应用要求）会发生变化，这就需要改变概念模型，易于更改的概念模型有利于修改和扩充。 概念模型应易于向数据模型转换：概念模型最终要转换为数据模型。设计概念模型时应当注意，使其有利于向特定的数据模型转换。 1 《数据库原理与应用技术-MySQL》 任务3 逻辑结构设计 子任务1 E-R图向关系模型转换的规则 子任务2 关系模型的优化及设计用户子模式 1 《数据库原理与应用技术-MySQL》 逻辑结构设计的主要任务是将概念结构设计阶段的E-R图转换为具体的DBMS支持的某种数据模型，包括层次模型、网状模型和关系模型。与此同时，可能还需为各种数据处理应用领域产生相应的逻辑子模式。 1 《数据库原理与应用技术-MySQL》 子任务1 E-R图向关系模型转换的规则 E-R图向关系模型的转换规则 一个实体型转换为一个关系模式，实体的属性就是关系的属性，实体的码就是关系的码。 一个1:1联系可以转换为一个独立的关系模式，也可以与任意一端对应的关系模式合并。 一个1:n联系可以转换为一个独立的关系模式，也可以与n端对应的关系模式合并。 一个m:n联系可以转换为一个独立的关系模式。 一个多元联系可以转换为一个独立的关系模式。 具有相同码的关系模式可以合并。 有些1:n的联系，将属性合并到n端后，该属性也作为主码的一部分。 1 《数据库原理与应用技术-MySQL》 例10.2 已知学生与课程之间存m:n联系，E-R图如图所示，将其转换为关系模式。 1 《数据库原理与应用技术-MySQL》 转换后的关系模式为： 学生（学号，姓名，性别，年龄，家庭住址） 课程（课程号，课程名，课时） 选修（学号，课程号，成绩） 1 《数据库原理与应用技术-MySQL》 子任务2 关系模型的优化及设计用户子模式 1. 关系模式的优化 数据库逻辑设计的结果不是唯一的。为了进一步提高数据库应用系统的性能，通常以规范化理论为指导，根据应用适当地修改、调整关系数据库的结构，这就是关系模式的优化。关系模式的优化方法如下： 确定数据依赖。 对数据依赖进行极小化处理，消除冗余联系。 确定关系模式的范式级别，根据应用环境，对某些模式进行合并或分解。 对关系模式进行必要的分解。 1 《数据库原理与应用技术-MySQL》 如果一个关系模式的属性特别多，就应该考虑是否可以对这个关系进行垂直分解。如果有些属性是经常访问的，而有些属性是很少访问的，则应该把它们分解为两个关系模式。 如果一个关系的数据量特别大，就应该考虑是否可以进行水平分解。如，学生关系模式中数据庞大，可以将学生关系水平分割为在校学生和毕业学生两个关系。 1 《数据库原理与应用技术-MySQL》 子任务2 关系模型的优化及设计用户子模式 2. 设计用户子模式 这部分主要是考虑使用方便性和效率问题，主要借助视图手段实现。 建立视图，使用更符合用户习惯的别名。 对不同级别的用户定义不同的视图，以保证系统的安全性。 对复杂的查询操作，可以定义视图，简化用户对系统的使用。 1 《数据库原理与应用技术-MySQL》 任务4 物理结构设计 子任务1 物理结构设计的内容 子任务2 关系模式存取方法选择 子任务3 确定数据库的存储结构 1 《数据库原理与应用技术-MySQL》 子任务1 物理结构设计的内容 数据库的物理结构设计是指对一个给定的逻辑数据模型选取一个最合适应用环境的物理结构（存储结构与存取方法）的过程。 数据库的物理设计通常分为两步： 确定数据库的物理结构。 对物理结构进行评价，评价的重点是时间和空间效率。 1 《数据库原理与应用技术-MySQL》 子任务1 物理结构设计的内容 物理结构设计依赖于具体的数据库管理系统。设计人员必须充分了解所用DBMS的内部特征，特别是系统所提供的存取方法和存取结构。与逻辑设计相同，物理设计也必须遵循数据的特性及用途，必须了解应用环境，特别是应用的处理频率和响应时间要求。 通常关系数据库物理设计的内容主要包括： 为关系模式选择存取方法。 设计关系、索引等数据库文件的物理存储结构。 1 《数据库原理与应用技术-MySQL》 子任务2 关系模式存取方法选择 数据库系统是多用户共享的系统，对同一个关系要建立多条存取路径才能满足多用户的多种应用要求。物理结构设计的任务之一是根据多用户共享数据的需求确定建立哪些存取路径以及选择哪些存取方法，存取方法是快速存取数据库中数据的技术。数据库管理系统一般提供索引方法和聚簇方法。 1 《数据库原理与应用技术-MySQL》 1. 索引存取方法 索引存取方法是指对关系的哪些属性列建立索引、哪些属性列建立组合索引、哪些索引要设计唯一索引。 关系上定义的索引数不要过多，因为系统为维护索引和查找索引都要付出代价。更新频率很高的表也不适合建立索引，因为每一次更新操作都要对索引进行相应的修改，增加了维护成本。 1 《数据库原理与应用技术-MySQL》 2. 聚簇存取方法 聚簇存取方法是指为了提高某个属性（或属性组）的查询速度，把在这个或这些属性上具有相同值的元组集中存放在连续的物理块中，该属性（或属性组）称为聚簇码。 聚簇功能可以大大提高按聚簇码进行查询的效率，聚簇功能不仅适用于单个关系，也适用于经常进行连接操作的多个关系，即把多个连接关系的元组按连接属性值聚集存放。 1 《数据库原理与应用技术-MySQL》 子任务3 确定数据库的存储结构 确定数据库物理结构主要指确定数据的存放位置和存储结构，包括确定关系、索引、聚簇、日志、备份等的存储安排和存储结构，确定系统配置等。 1 《数据库原理与应用技术-MySQL》 子任务3 确定数据库的存储结构 确定数据的存放位置和存储结构要综合考虑存取时间、存储空间利用率和维护代价三方面的因素。这三个方面常常是相互矛盾的，因此需要进行权衡，选择一个折中方案。 确定数据的存放位置：为了提高系统性能，数据应根据应用情况将的易变部分与稳定部分、经常存取部分和存取频率较低部分分开存放。 确定系统配置：关系数据库管理系统产品一般都提供了一些系统配置变量和存储分配参数，供设计人员和数据库管理员对数据库进行物理优化。 评价物理结构：物理结构设计过程中需要综合衡量时间效率、空间效率、维护代价和各种用户要求，在众多的设计方案中进行仔细评价，从而选出一个较好的方案。 1 《数据库原理与应用技术-MySQL》 任务5 数据库的实施和维护 子任务1 数据库实施 子任务2 数据库运行维护 1 《数据库原理与应用技术-MySQL》 子任务1 数据库实施 在数据库实施阶段中，系统设计人员要运用DBMS提供的数据定义语言和宿主语言，根据数据库的逻辑设计和物理设计的结果建立数据库，编制与调试应用程序，组织数据入库并进行系统试运行。 1 《数据库原理与应用技术-MySQL》 数据库实施的步骤 （1）定义数据库结构 在确定了数据库的逻辑结构和物理结构之后，接着就要使用所选定的数据库管理系统提供的各种工具来建立数据库结构。当数据库结构建立好后，就可以开始运行DBMS提供的数据语言及其宿主语言编写数据库的应用程序。 1 《数据库原理与应用技术-MySQL》 数据库实施的步骤 （2）组织数据入库 组织数据入库是数据库实施阶段的主要工作。来自各部门的数据通常不符合系统的格式，需要对数据格式进行统一，同时还要保证数据的完整性和有效性。对于中、大型系统，应设计一个数据输入子系统辅助数据的入库工作。对于数据量不是很大的小型系统，可以用人工方法完成数据的入库，步骤如下： 筛选数据； 转换数据格式； 输入数据； 校验数据。 1 《数据库原理与应用技术-MySQL》 数据库实施的步骤 （3）应用程序的编码与调试 数据库应用程序的设计应与数据库的设计同时进行，也就是编制与调试应用程序的同时，需要实现数据的入库。如果调试应用程序时数据的入库尚未完成，可先使用模拟数据。 1 《数据库原理与应用技术-MySQL》 数据库实施的步骤 （4）数据库试运行 在将一部分数据加载到数据库后，就可以开始对数据库系统进行联合调试了，这个过程又称为数据库试运行。这个阶段要实际运行数据库应用程序，执行对数据库的各种操作，测试应用程序在功能和性能上是否满足设计要求。如果不满足要求，则要对应用程序进行修改、调整，直到满足设计要求为止。 试运行的工作主要有两个：功能测试和性能测试。功能测试是运行数据库应用程序，执行各种操作，测试程序是否满足设计要求，找出不足，改进现有程序直到符合设计要求；性能测试是测量系统的性能指标，分析是否符合设计目标。 1 《数据库原理与应用技术-MySQL》 子任务2 数据库运行维护 数据库试运行结果符合设计目标后即可投入正式运行。在数据库系统运行过程中，必须不断地对其结构性能进行评价、调整和修改。数据库运行维护的基本工作包括： 数据库的转储与恢复 数据库的安全性和完整性控制 数据库的性能监测及分析改进 数据库的重组和重构 1 《数据库原理与应用技术-MySQL》 1. 数据库的转储和恢复 数据库的转储和恢复是系统正式运行后重要的维护工作之一。数据库管理员要针对不同的应用要求制定不同的转储计划，以保证一旦发生故障尽快将数据库恢复到某种一致的状态，并尽可能减少对数据库的破坏。 1 《数据库原理与应用技术-MySQL》 2. 数据库的安全性和完整性控制 在数据库运行过程中，由于应用环境的变化，对安全性的要求也会发生变化。例如，有的数据原来是机密的，现在变成可以公开查询的了，而新加入的数据又可能是机密的，系统中用户的密级也会变化，这些都需要数据库管理员根据实际情况修改原有的安全性机制。同样，数据库完整性的约束条件也会变化，也需要数据库管理员不断修正，以满足用户需求。 1 《数据库原理与应用技术-MySQL》 3. 数据库的性能监测及分析改进 在数据库运行过程中，监测系统运行、对监测数据进行分析，并找出改进系统性能的方法，是数据库管理员的又一重要任务。目前有些DBMS产品提供了监督系统性能的参数工具，数据库管理员可以利用这些工具方便地得到系统运行过程中一系列性能参数的值。数据库管理员应仔细分析这些数据，判断当前系统运行状况是否最佳，应做哪些改进，例如，调整系统的物理参数或对数据库进行重组织和重构造等。 1 《数据库原理与应用技术-MySQL》 4. 数据库的性能监测及分析改进 数据库运行一段时间后，由于记录不断增、删、改，数据库的物理存储情况变坏，降低了数据的存取效率，数据库的性能下降。这时，数据库管理员就要对数据库进行重组织或部分重组织（只对频繁增加、删除数据的表进行重组织），DBMS一般都提供数据重组织的实用程序。在重组织的过程中，按原设计要求重新安排存储位置、回收垃圾、减少指针链等，以提高系统性能。 1 《数据库原理与应用技术-MySQL》 数据库的重组织并不修改原设计的逻辑和物理结构，而数据库的重构造则不同，它要部分修改数据库的模式和内模式。由于数据库应用环境的变化（例如，增加了新的应用或实体，取消了某些应用，有的实体和实体间的联系发生了变化等），原有的数据库设计不能满足新的需求，就需要调整数据库的模式和内模式（例如，在表中增加或删除某些数据项、改变数据项的类型、增加或删除某个表、改变数据库的容量、增加或删除某些索引等）。当然，数据库的重构是有限的，只能做部分修改。如果应用变化太多、太大，重构也无济于事，说明此数据库应用系统的生命周期已经结束，应该设计新的数据库应用系统了。 1 《数据库原理与应用技术-MySQL》 任务6 关系模式的规范化 1 《数据库原理与应用技术-MySQL》 关系模式的规范化 如果一个关系没有进行规范化，可能导致数据冗余大、数据更新引起数据不一致、数据插入异常和数据删除异常的问题。为解决这些问题，我们需要对关系模式进行规范化处理。关系模式的规范化就是指由一个低级范式通过模式分解转换为若干个高级范式的过程。 1 《数据库原理与应用技术-MySQL》 范式 范式（Normal Form，NF）即规范化的关系模式，是衡量关系模式好坏的一组规则的集合。根据规范化的程度不同，范式有不同的级别，级别越高，其包含的限制约束条件越严格，关系产生数据冗余和操作异常的情况就越少。 1 《数据库原理与应用技术-MySQL》 范式的级别 目前关系数据库有六种范式：第一范式（First Normal Form，1NF）、第二范式（Second Normal Form，2NF）、第三范式（Third Normal Form，3NF）、第四范式（Fourth Normal Form，4NF）、第五范式（Fifth Normal Form，5NF）和BC范式（Boyce-Codd Normal Form，BCNF）。 高一级别的范式是在低一级别范式应满足的限制条件基础上再增加一些额外的限制条件，如第二范式是在第一范式的基础上增加一些约束条件，而第三范式又在第二范式的基础上增加一些约束条件，以此类推。因此，满足高一级别范式的关系模式必定满足低一级别的范式要求。一般来说，数据库只需满足第三范式就行了。 1 《数据库原理与应用技术-MySQL》 （1）第一范式 如果关系模式R中的所有属性都是不可分的数据项，则称R属于第一范式，记为R∈1NF。 关系的第一范式是关系要满足的最基本的范式，在关系数据库中，任何关系都必须满足第一范式。 新建一个“学生课程成绩”表，其关系模式为：学生课程成绩（学号，姓名，性别，专业，班级名，班主任，课程号，课程名，课时，成绩）。它显然满足第一范式，但存在数据冗余和各种异常问题，所以仅满足第一范式的关系模式必须要进一步规范化。 1 《数据库原理与应用技术-MySQL》 （2）第二范式 若关系模式R∈1NF，且每个非主属性都完全依赖于R的码，则R∈2NF。 “学生课程成绩”关系满足1NF，接下来我们通过分析该关系模式中非主属性对码的函数依赖情况，判断该关系模式是否满足2NF。 1 《数据库原理与应用技术-MySQL》 “学生课程成绩”关系的码是（学号，课程号），非主属性集是｛姓名，性别，专业，班级名，班主任，课程名，课时，成绩｝，这8个非主属性对码的函数依赖情况如下： （学号，课程号）→成绩，（学号，课程号）→姓名 （学号，课程号）→性别，（学号，课程号）→班主任 （学号，课程号）→课程名，（学号，课程号）→课时 （学号，课程号）→专业，（学号，课程号）→班级名 1 《数据库原理与应用技术-MySQL》 通过分析得知，成绩对码是完全依赖外，其他7个非主属性对码都是部分依赖，因此“学生课程成绩”不满足2NF，即学生课程成绩∉2NF。 下面我们通过模式分解的方法，消除该关系模式中的部分依赖，使之进一步达到2NF。要消除非主属性对码（学号，课程号）的部分依赖，除了“成绩”外的“姓名”“性别”等7个非主属性必须从该关系模式中分离出去。 1 《数据库原理与应用技术-MySQL》 具体的操作是：将函数依赖于“学号”的“姓名”、“性别”、“专业”、“班级名”和“班主任”这5个属性放在一个关系模式“学生_班级”中，而将函数依赖于“课程号”的“课程名”和“课时”这两个属性放在一个关系模式“课程”中，剩下的“学号”、“课程号”和“成绩”放在一个关系模式“成绩”中，最后，为保持关系分解的无损连接和函数依赖性，在“学生”和“课程”两个关系模式中分别添加“学号”和“课程号”属性。分解后的三个关系模式如下： 学生_班级（学号，姓名，性别，专业，班级名，班主任） 课程（课程号，课程名，课时） 成绩（学号，课程号，成绩） 1 《数据库原理与应用技术-MySQL》 这样分解后，三个关系模式都满足2NF，即学生_班级∈2NF；课程∈2NF；成绩∈2NF。由2NF的定义可以得出以下几个结论。 如果一个关系模式R∈1NF，且R中没有非主属性，则R∈2NF。 如果一个关系模式R∈1NF，且R中所有的候选码都是单属性码，则R∈2NF。 如果一个关系模式R只有两个属性，则R∈2NF。 如果一个关系模式R∈2NF，则R∈1NF。 1 《数据库原理与应用技术-MySQL》 3.第三范式 对于关系模式R，每一个非主属性既不部分函数依赖于码，也不传递函数依赖于码，则 R∈3NF。 1 《数据库原理与应用技术-MySQL》 “学生_班级”、“课程”和“成绩”三个关系模式已经满足2NF，即已满足“每一个非主属性不部分函数依赖于码”的条件。我们只需要分析这三个关系模式中是否有非主属性对码的传递依赖，就可以判断它们是否满足3NF。 在“课程”和“成绩”两个关系模式中均没有非主属性对码的传递依赖，因此课程∈3NF，成绩∈3NF。而在“学生_班级”中，我们发现，学号→班级名，班级名→班主任，所以该关系中存在非主属性“班主任”对码“学号”的传递函数依赖，所以学生＿班级∉3NF。 1 《数据库原理与应用技术-MySQL》 下面我们通过模式分解的方法，消除该关系模式中的传递依赖，使之进一步达到3NF。将非主属性“班主任”从该关系模式中分离出去，放在另一个关系模式“班级”中，并添加“班级名”属性以保持分解的无损连接和函数依赖性，“学生＿班级”分解后的两个关系模式如下： 学生（学号，姓名，性别，班级名） 班级（班级名，班主任） 1 《数据库原理与应用技术-MySQL》 这样，分解后的关系模式“学生”和“班级”都满足3NF，即：学生∈3NF；班级∈3NF。由3NF的定义可以得出以下几个结论。 如果一个关系模式R∈1NF，且R中没有非主属性，则R∈3NF。 如果一个关系模式R只有两个属性，则R∈3NF。 如果一个关系模式R∈3NF，则R∈2NF和1NF。 1 《数据库原理与应用技术-MySQL》 $$