关系数据库设计步骤

看这篇文章之前，希望大家能够对数据库系统、数据模型有知识储备，如果有疑惑可以看我的另外一篇博客数据库系统。
还需要对关系型数据库基础知识有所了解，有疑惑朋友可以看关系型数据库基础知识

• 什么是数据库设计？
  数据库设计是指对于一个给定的应用环境，设计一个优良的数据库逻辑模式和物理结构，并据此建立数据库及其应用系统，使之能够有效地存储和管理数据，满足各种用户的应用需求，包括信息管理要求和数据操作要求。
  我们数据库不一定都是关系型数据库，本文只是基于关系型数据库设计，希望给大家讲清楚数据库设计的步骤。

数据库设计的步骤

1. 需求分析
   ➢ 了解 和分析用户的需求（数据与处理要求）
   ➢ 是设计数据库的起点
   ➢ 是最困难和最耗时的一步
   ➢ 结果是否准确地反映了用户的实际要求，将直接影响到后面各个
   阶段的设计，并影响到设计结果是否合理和实用。决定了构建数据库的速
   度和质量。
   ➢ 需求分析阶段的分析结果是形成用户需求规格说明书和数据字典。
2. 概念结构设计
   ➢ 对用户需求进行综合、归纳与抽象， 形成独立于具体DBMS的概念模
   型（E-R模型）。
3. 逻辑结构设计
   ➢ 将概念结构转换为某个DBMS所支持 的数据模型，并对其进行优化。
4. 物理结构设计
   ➢ 为逻辑数据结构选取一个最适合应用环境的物理结构，包括存储结构
   和存取方法。
5. 数据库实施
   ➢ 根据逻辑设计和物理设计的结果构建数据库，编写与调试应用程序，
   组织数据 入库并进行试运行。
6. 数据库运行和维护
   ➢ 经过试运行后即可投入正式运行。必须不断对数据库设计进 行评估、
   调整与修改。
   ➢ 在运行过程中要对系统进行性能监测、转储/恢复、数据库重组和重构。
   

概念结构设计

• 实体之间的联系
  ◼ 现实世界中事物内部以及事物之间的联系在信息世界中反映为实
  体（型）内部的联系和实体（型）之间的联系。
  ◼ 实体内部的联系: 是指组成实体的各属性之间的联系
  ◼ 实体之间的联系: 是指不同实体集之间的联系（E-R模型关注的）
  ➢ 通常有一对一，一对多，多对多三种联系类别
• 联系的度/元
  ➢ 把参与联系的实体型的数目称为联系的度。
  ➢ 两个实体型之间的联系度为2，称为二元联系；
  ➢ 一个实体型内不同实体之间的联系度为1，称为一元联系；
  ➢ 三个实体型之间的联系度为3，称为三元联系；
  ➢ N个实体型之间的联系度为N，称为N元联系。
  

E-R模型

• E-R图的表示（Chen方法）
  ➢ E-R图提供了表示实体型、属性和联系的方法
• E-R图的表示
  ➢ 实体型：用矩形表示
  ➢ 属性：用椭圆形表示
  ◼ 多值属性：用双线椭圆表示
  ◼ 派生/导出属性：用虚线椭圆表示
  ➢ 码/关键字：加下划线
  ➢ 连接实体型和属性：直线
  ➢ 联系：用菱形表示
  ➢ 连接实体与联系：直线
  ➢联系也可以有属性，连接联系和属性：直线
  
• 联系的类别（联系的基数）：常用的表示
  ◼ 在连接实体型与联系的无向边旁标上联系的类别（1:1，1:n，n:m）
  ◼ 一方实体型的直线旁标上1，多方实体型的直线旁标上n或m
  
  
• 弱实体型
  ➢ 如果一个实体型的存在依赖于其它实体型的存在，则这个实体型叫做弱实体型， 否
  则叫做强实体型。
  ➢ 一般地讲，如果不能从一个实体型的属性中找出可以作为码的属性，即该实体型的
  主键的一部分或全部从其依赖的强实体型中获得，则该实体型为弱实体型。
  ➢ 弱实体型与它所依赖的强实体型之间是部分与整体的关系。
  整体实体如果被破坏，部分实体则不能存在。
  ➢ 在E-R图中用双矩形表示弱实体型，
  用双菱形表示弱实体型与它依赖的
  强实体型之间的识别联系。
  
  示例1——基本的实体-联系表达

看下面的示例：

• 派生属性怎样设计？
  派生属性年龄可以不用包含在关系模型中，可以通过当前时间减出生年月。派生属性可以不用包含关系模型中。
• 复合属性怎样设计
  **属性是不可在分的数据项，它不可以包含其他属性，**复合属性家庭住址需要直接连到实体客户上。
• 多值属性怎样设计
  上图中电话号码是多值属性（移动电话、家庭电话、单位电话等）两种设计方法：

1. 将多值属性处理方法类同于复合属性直接全并到实体上。改方法有几点不足：1）浪费存储空间，并不是所有客户都需要存贮三个电话号码。2）缺少弹性，如果需要存储更多类型的电话号码则需要修改模型。
2. 讲多值属性建模为弱实体型表示。例如可以将多值属性电话号码建模为联系方式弱实体型，它有两个属性电话用途和电话号码。联系电话是部分码，要联系客户编号构成主码。
   多值属性例子二：
   联系和实体一样，最终是需要存储起来的，如果管理员与商品之间存在多对多的管理联系，那么就要用一个新的关系来存储这个联系，用于记录哪位管理员在什么时间对哪种商品进行了何种操作。

这里需要注意联系和实体一样是要存储起来的，所以要创建联系是要谨慎！联系不能有增删改原因请看下图：

如果管理员删除P0001，那么商品中就没有P0001，那么管理联系表中就不会有P0001不满足参照完整性，所以管理不能成为联系，而应该是中间没有，直接将管理员拥有管理权限就行，不需要将管理设置为联系。就是说不能将实体的功能当做联系

在下面这个ER图中，因为需要实现不同的功能，所以点赞数可以是联系也可以是功能：
➢ 作为联系：如果我们要存储哪个用户点赞了哪条动态，则要把点赞当作联系来处理。
➢ 作为功能：若并不要存储哪个用户点赞了哪条动态，只想记录某条动态的点赞数，则把点赞当作功能来处理，在动态实体上加一属性点赞数。

例子

❖ 某工厂开发信息系统，经过可行性分析，详细调查确定了该系统由
物资管理、销售管理、劳动人事管理等子系统组成。
❖ 下面分别设计各子系统的分E-R图。

设计各个分ER图、

• 物资管理子系统E-R图的设计
  
• 人事管理子系统E-R图的设计
  
• 销售管理子系统E-R图的设计
  ❖ 该子系统的主要功能是：
  ⚫ 处理顾客和销售员送来的订单
  ⚫ 工厂是根据订货安排生产的
  ⚫ 交出货物同时开出发票
  ⚫ 收到顾客付款后，根据发票存根和信贷情况进行应收款处理通过需求分析，知道销售子系统功能围绕“订单”和“应收账款”的处理来实现。

❖先设计该E-R图的草图：
数据结构中订单、顾客、顾客应收账目、 产品是许多子功能、数据流共享的数据， 因此设计为实体。
（如右图所示）
❖再对E-R图进行详细设计和调整：参照需求分析和数据字典中的详尽描述， 遵循前面给出的两个准则。

（1）每张订单由订单号、若干头信息和订单细节组成。订单细节又有订货 的零件号、数量等来描述。按照准则（2），订单细节就应该上升为实体。 一张订单可以订若干产品，订单与订单细节两个实体之间是1∶n的联系。
（2）原订单和产品的联系实际上是订单细节和产品的联系，增加了一个联系—参照2。订单处理时从中获得当前单价、产品重量等信息。
（3）工厂对大宗订货给予优惠。每种产品都规定了不同订货数量的折扣，应增加一个实体—“折扣规则” 存放这些信息。

◼ 对每个实体定义的属性如下：
➢ 顾客：{顾客号，顾客名，地址，电话，信贷状况，账目余额}
➢ 订单：{订单号，顾客号，订货项数，订货日 期，交货日期，工种号，生产地点}
➢ 订单细则：{订单号，细则号，零件号，订货 数，金额}
➢ 应收账款：{顾客号，订单号，发票号，应收 金额，支付日期，支付金额，当前余额，货款 限额}
➢ 产品：{产品号，产品名，单价，重量}
➢ 折扣规则：{产品号，订货量，折扣}

ER图集成

➢ E-R图的集成一般需要分两步
◼合并。解决各分E-R图之间的冲突，将分E-R图合并，生成
初步E-R图。
◼修改和重构。消除不必要的冗余，生成基本E-R图。
合并E-R图，生成初步E-R图
◼ 各个局部应用所面向的问题不同，各个子系统的E-R图之间必定会存在
许多不一致的地方，称之为冲突。
◼ 子系统E-R图之间的冲突主要有三类：
①属性冲突
②命名冲突
③结构冲突

① 属性冲突
➢属性域冲突，即属性值的类型、取值范围或取值集合不同。
◼ 例如零件号，有的部门把它定义为整数，有的部门把它定义为字符型。
◼ 年龄，某些部门以出生日期形式表示职工的年龄，有的用整数表示职工的年龄。
➢属性取值单位冲突
◼ 例如零件的重量有的以公斤为单位，有的以斤为单位，有的以克为单位。
② 命名冲突
➢同名异义，即不同意义的对象在不同的局部应用中具有相同的名字。
➢异名同义（一义多名），即同一意义的对象在不同的局部应用中具有不
同的名字。
◼ 如对科研项目，财务科称为项目，科研处称为课题，生产管理处称为工程。
➢命名冲突
◼ 可能发生在实体、联系一级上
◼ 也可能发生在属性一级上
◼ 通过讨论、协商等行政手段加以解决
③ 结构冲突
➢同一对象在不同应用中具有不同的抽象。
◼ 例如，职工在某一局部应用中被当作实体，而在另一局部应用中被当作属性。
◼ 解决方法：把属性变换为实体或把实体变换为属性，使同一对象具有相同的抽象。
➢同一实体在不同子系统的E-R图中的属性个数和属性排列次序不完全相同。
◼ 解决方法：取各子系统的E-R图中属性的并集，再适当调整属性的次序。
➢实体间的联系在不同的E-R图中为不同的类型。
◼ 例如，实体E1与E2在一个E-R图中是多对多联系，在另一个E-R图中是一对多联系
◼又如，在某E-R图中E1与E2发生联系，而在另一个E-R图中E1、E2、E3三者有联系
◼ 解决方法：根据应用的语义对实体联系的类型进行综合或调整。

（2）消除不必要的冗余，设计基本E-R图
◼ 冗余的数据是指：可由基本数据导出的数据
◼ 冗余的联系是指：可由其他联系导出的联系
◼ 冗余带来的问题：破坏数据库的完整性，数据库维护困难，应当予以消除
◼ 消除冗余方法：

1. 分析方法
2. 规范化理论的方法(范式)
   
   
   ❖ 用规范化理论来消除冗余
   ① 确定分E-R图实体之间的数据依赖。
   ◼实体之间一对一、一对多、多对多的联系可以用实体码之间的函数依赖来表示。
   ② 求FL的最小覆盖GL ，差集为 D= FL - GL
   ◼逐一考察D中的函数依赖，确定是否是冗余的联系，若是，就把它去掉
   ❖ 应注意的问题：
   ➢ 冗余的联系一定在D中，而D中的联系不一定是冗余的；
   ➢ 当实体之间存在多种联系时，要将实体之间的联系在形式上加以区分。
   

• 概念设计小结
  1．实体与属性的划分原则
  （1）作为属性，不能再具有 需要描述的性质。
  （2）属性不能与其他实体具 有联系。
  2．E-R图的集成
  ➢设计各个子系统的分E-R图
  ➢消除冲突，进行集成
  ➢消除冗余，设计基本E-R图

逻辑结构设计

逻辑结构设计的任务
➢ 把概念结构设计阶段设计好的基本E-R图转换为与选用的DBMS产品所支持的逻辑结构。
➢ 目前主要使用关系模型，关系模型的逻辑结构是一组关系模式的集合。

E-R图向关系模型的转换

➢ 转换内容
◼ 将E-R图转换为关系模型：就是将实体型、实体的属性和实体型之间的联系转化为关系模式。
➢ 转换原则
◼ 实体型的转换
◼ 实体型之间联系的转换

实体型的转换

实体型间联系的转换

• 1-1联系
  实体型之间联系的转换
  （1）实体型间的1:1联系：
  ➢ 可以转换为一个独立的关系模式
  ◼ 关系模式的属性：与该联系相连的各实体的码以及联系本身的属性
  ◼ 关系模式的候选码：每个实体的码均是该关系模式的候选码
  ➢ 也可以与相连的任意一端对应的关系模式合并（推荐使用）
  ◼ 关系模式的属性：
  与某一端关系模式合并，则在该关系模式的属性中加入另一端关系模式的码和联系的属性
  ◼ 合并后关系模式的码：不变。但加入的另一端关系模式的码可以作为该关系模式的候选码。
  
• 1-n联系
  实体型间的1:n联系：
  ➢ 可以转换为一个独立的关系模式
  ◼ 关系模式的属性：与该联系相连的各实体的码 + 联系本身的属性
  ◼ 关系模式的码：n端的实体的码
  ➢ 与n端对应的关系模式合并
  ◼ 合并后关系模式的属性：在n端关系模式中 + 1端关系的码 + 联系的属性
  ◼ 合并后关系模式的码：不变
  ◼ 可以减少系统模式中的关系个数，一般情况下更倾向于采用这种方法
  
• m-n
  实体型间的m:n联系：
  ➢ 一个m:n联系转换为一个关系模式
  ◼ 关系的属性：与该联系相连的各实体的码以及联系本身的属性
  ◼ 关系的码：各实体码的组成关系的码或关系码的一部分
  
  三个或三个以上实体间的一个多元联系
  ➢ 转换为一个关系模式
  ◼ 关系模式的属性：与该多元联系相连的各实体的码 + 联系本身的属性
  ◼ 关系模式的码：各实体码的组成关系的码或关系码的一部分

• 具有相同码的关系模式可合并
  ➢ 目的：减少系统中的关系个数
  ➢ 合并方法：
  ◼ 将其中一个关系模式的全部属性加入到另一个关系模式中
  ◼ 然后去掉其中的同义属性（可能同名也可能不同名）
  ◼ 适当调整属性的次序
  对于1-n和1-1建议合并
  
  对于1-n和1-1建议合并

数据模型的优化

❖ 数据库逻辑设计的结果不是唯一的。
❖ 得到初步数据据模式后，还应该适当地修改、调 整数据库逻辑结构，以进一步提高数据库应用系统的性能，这就是数据模型的优化。
❖ 关系数据模型的优化通常以规范化理论为指导。(范式)

• 优化数据模型的方法:
  （1）确定数据依赖
  按需求分析阶段所得到的语义，分别写出每个关系模式内部各属性之间的数据依赖以及不同关系模式属性之间数据依赖。
  （2）对于各个关系模式之间的数据依赖进行极小化处理，消除冗余的联系。
  （3）按照数据依赖的理论对关系模式进行分析，考察是否存在部分函数依赖、传递函数依赖、多值依赖等，确定各关系模式分别属于第几范式。
  （4）按照需求分析阶段得到的各种应用对数据处理的要求，分析对于这样的应用环境这些
  模式是否合适，确定是否要对它们进行合并或分解。包括水平分解和垂直分解。
• 几点注意：
  ❖ 对于一个具体应用来说，到底规范化进行到什么程度，需要权衡响应时间和 潜在问题两者的利弊来决定。
  ❖ 当查询经常涉及两个或多个关系模式的属性时，系统必须经常地进行连接运 算，连接运算的代价是相当高的。这种情况下，需要降低规范化程度。
  ❖ 非BCNF的关系模式会存在不同程度的更新异常。如果在实际应用中对此关 系模式只是查询，并不执行更新操作，就不会产生实际影响。

设计用户子模式

❖ 数据库模式——全局模式。
考虑系统全局应用需求，时间效率、空间效率、易维护等。
❖ 用户子模式——视图机制
考虑局部应用的特殊需求和用户人习惯与方便。包括：
（1）使用更符合用户习惯的别名
➢ 合并各分E-R图曾做了消除命名冲突的工作，以使数据库系统中同一关 系和属性具有唯一的名字。这在设计数据库整体结构时是非常必要的。
➢ 在设计用户子模式时可以设计子模式时重新定义某些属性名，使其与用户习惯一致，以方便使用。
（2）针对不同级别的用户定义不同的视图，提高系统的安全性假设有关系模式: 产品（产品号，产品名，规格，单价，生产车间，生产负责人，产品成本，产品合格率，质量等级）
➢ 为一般顾客建立视图：
产品1（产品号，产品名，规格，单价）
➢ 为产品销售部门和管理部门建立视图：
产品2（产品号，产品名，规格，单价，生产车间，生产负责人）
（3）简化用户对系统的使用
➢ 某些局部应用中经常要使用一些很复杂的查询，为了方便用户，可以将这些复 杂查询定义为视图。

数据设计完整例子