Database Designment

数据库设计是指对于一个给定的应用场景，构造（设计）优化的数据库逻辑模式和物理结构，并据此建立数据库，满足各种用户的应用需求。
一般的设计步骤为：

  • 需求分析
  • 概念结构设计
  • 逻辑结构设计
  • 物理结构设计
  • 数据库实施
  • 数据库运行维护

需求分析

通过详细调查现实世界要处理的对象（组织、部门、企业等），充分了解原系统（手工系统或计算机系统）工作概况，明确用户的各种需求。
其任务是由数据库设计人员和用户双方共同收集信息需求和处理需求；通过仔细分析；将这些需求按一定的规范要求以用户和设计人员都能理解接受的文档形式确定下来。

概念结构设计

通过对用户需求进行综合、归纳与抽象，形成一个独立于具体数据库管理系统的概念模型。
最常用的概念模型即E-R模型。
E-R模型将现实世界中的信息用实体、属性和实体之间的联系表示。
一些E-R图的要点如下：

  • 矩形表示实体、椭圆表示属性、零星表示关系
  • 关系是实体间的关系，有三种：1对1、1对n、m对n
  • 属性必须为不可再分的项，不能包含其他属性，不能与其他实体具有关系

逻辑结构设计

将概念结构转换成特定DBMS所支持的数据模型的过程。MySQL是关系型数据库，所以此步便是将E-R图转换为表的过程。
在此阶段，要做的事一般包括以下三点：

  • 将概念模型(E-R图)转换为关系模式（表）的集合
  • 使用范式理论对模式进行优化
  • 设计关系子模式–视图

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

  • 一个实体对应一个表，联系也可以单独对应一个表，实体或联系的属性构成表的字段
  • 1:1联系中，可以将联系单独建表（包含字段为联系两端实体的主键+联系本身的属性）;也可以将联系归入任一一端实体（为这个实体对应的表增加字段另一个实体的主键和原来联系包含的属性）
  • 1:n联系中，可以将联系单独建表（包含字段为联系两端实体的主键+联系本身的属性）;也可以将联系归入任n端实体（为这个实体对应的表增加字段另一个实体的主键和原来联系包含的属性）
  • m:n联系中，只能将联系单独建表（包含字段为联系两端实体的主键+联系本身的属性）
  • 多实体联系中，只能将联系单独建表（包含字段为联系多端实体的主键+联系本身的属性）

范式

为了建立冗余较小、结构合理的数据库，设计数据库时必须遵循一定的规则。在关系型数据库中这种规则就称为范式。范式是符合某一种设计要求的总结。要想设计一个结构合理的关系型数据库，必须满足一定的范式。
目前关系数据库有六种范式：第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、巴斯-科德范式（BCNF）、第四范式(4NF）和第五范式（5NF，又称完美范式）。
数据库的范式设计得越高阶，冗余度就越低，同时高阶的范式一定满足低阶的范式的要求。一般来说，在关系型数据库中，最高也就遵循到BCNF，普遍是3NF，但也不是绝对，有时候为了提高某些查询性能，我们还需要破坏范式规则，也就是反规范化。

  • 1NF：第一范式要求确保数据表中每个字段的值都具有原子性，也就是说表中每个字段不能再被拆分。这个范式一定需要遵守。

  • 2NF：在满足第一范式的基础上，还要满足数据库表中的每一条数据，都是可唯一标识的。而且所有非主键字段，都必须完全依赖主键，不能只依赖主键的一部分。

    • 比赛表（球员编号，比赛编号）–> (姓名，年龄，比赛时间，场地，得分)则不满足第二范式。虽然联合主键（球员编号，比赛编号）可以唯一标识么一条记录，但比赛表存在部分依赖，即（球员编号）–> (姓名，年龄)和(比赛编号–>比赛时间，比赛场地)
    • 不满足第二范式会产生如下问题：
      • 数据冗余：如果一个球员参加了n场比赛，那么球员的姓名和年龄就重复了n次，一个比赛也有可能有m个球员参加，那比赛的时间和地点就重复了m次。
      • 插入异常：如果我们想要添加一场新的比赛，但是这时球员还没有确定，那么就无法插入。
      • 删除异常：如果我们想要删除某个球员编号，会将比赛信息删除掉。
      • 更新异常：如果我们想要调整某个比赛的时间，那么数据库表中所有关于这个比赛的时间都需要进行调整，否则会出现一场比赛时间不同的情况。
    • 将比赛表调整为球员表（球员编号，姓名，年龄）、比赛表（比赛编号，比赛场地，比赛时间）和得分表（比赛编号，球员编号，得分）三个表则满足2NF

  • 3NF：在第二范式的基础上，确保数据表中的每一个非主键字段都和主键字段直接相关，也就是说，要求数据表中的所有非主键字段不能依赖于其他非主键字段字段。（即不能存在非主属性A依赖非主属性B，非主属性B依赖于主键C的情况）。通俗来说，该规则的意思是所有非主键属性之间不能有依赖关系，必须相互独立。

    • 员工信息表（员工编号）–>(姓名，部门编号，部门名称)满足2NF，但是存在(部门编号)–>(部门名称)不满足3NF
    • 将其分为员工表（员工编号，姓名）和部门表（部门编号，部门名称）满足3NF

  • BCNF：前三个范式消除了非主属性对候选键的部分依赖和传递依赖，如果进一步消除主属性对候选键的部份依赖和传递依赖则满足BCNF

    • 仓库表（仓库名，管理员，物品名，数量）有仓库名 → 管理员，管理员 → 仓库名，（仓库名，物品名）→ 数量。其候选键为：（管理员，物品名），（仓库名，物品名）。主属性为：仓库名、管理员、物品名
    • 满足3NF，但由于主属性管理员部分依赖于候选键(仓库名，物品名)不满足BCNF
    • 将其拆分为仓库（仓库名，管理员）、库存（仓库名，物品名，数量）满足BCNF

物理结构设计

为逻辑数据结构选取一个最适合应用环境的物理结构,包括存储结构和存取方法。
此阶段的工作是：

  • 为关系模式选取合适的存取方法（即索引）
  • 设计关系(表)、聚簇、索引、日志、备份等数据的物理存储结构及确定系统配置
    • 可以将比较大的表分别放在两个磁盘上，以加快存取速度，这在多用户环境下特别有效
    • 可以将日志文件与数据库对象（表、索引等）放在不同的磁盘以改进系统的性能
    • 系统都为数据库的系统配置（同时使用数据库的用户数、同时打开的数据库对象数、内存分配参数、缓冲区分配参数（使用的缓冲区长度、个数）、存储分配参数 、物理块的大小、物理块装填因子、时间片大小、数据库的大小、锁的数目等）赋予了合理的缺省值。在进行物理设计时需要根据应用环境确定这些参数值，以使系统性能最优。

数据库实施

根据逻辑设计和物理设计的结果构建数据库,编写与调试应用程序,组织数据入库并进行试运行。
此阶段的工作是：

  • 建立数据库，建表
  • 装入数据
  • 测试运行

数据库运行和维护

经过试运行后即可投入正式运行,在运行过程中必须不断对其进行评估、调整与修改。
此阶段的工作是：

  • 数据库备份和恢复
  • 数据库权限控制
  • 数据库性能监督、分析与改进
  • 数据库重组织和重构造（数据库长期运行的dml操作会使物理存储性能变差，重组织可以提高系统性能）