数据库三范式

利用关系型数据库进行建表时普遍需要遵循的三个规范。

三范式内容

第一范式（1NF）：当关系模式 R 的所有属性都不能在分解为更基本的数据单位时，称 R 是满足第一范式。
第二范式（2NF）：如果关系模式 R 满足第一范式，并且 R 的所有非主属性都完全依赖于 R 的每一个候选关键属性，称 R 满足第二范式。
第三范式（3NF）：设 R 是一个满足第一范式条件的关系模式，X 是 R 的任意属性集，如果 X 非传递依赖于 R 的任意一个候选关键字，称 R 满足第三范式。

三范式的具体实现

1NF：每一列属性都是不可再分的属性值，确保每一列的原子性。

  • 两列的属性相近或相似或一样，尽量合并属性一样的列，确保不产生冗余数据。
  • 目标：原子性 字段不可再分。

2NF：每一行的数据只能与其中一列相关。

  • 目标：有主键，非主键字段依赖主键。

3NF：一个数据库表中不包含已在其他表中已包含的非主键字段。

  • 目标：非主键字段不能相互依赖。每列都与主键有直接关系，不存在传递依赖。

image.png

如上表中

  • 每列都是不能再分的属性值，保证每一列的原子性，符合第一范式；
  • 当两列数据属性值都差不多相同时，区分它们就需要通过主键（删除时通过主键来区分），即其他非主键字段都依赖于主键字段，符合第二范式；
  • 所有非主键字段都依赖于主键字段，而不能是其他非主键字段，符合第三范式；（例如，stu_name 依赖于 id，gender 也是依赖于 id，而不能依赖于 stu_name）

软件设计的标准与步骤

软件设计步骤如下，主要是过一遍，了解即可。

  1. 良好的数据库设计
    1. 访问效率高
    2. 减少数据冗余，节省存储空间，便于进一步扩展
    3. 可以使应用程序的开发变得更容易
  2. 数据库设计的基本步骤
    1. 软件开发步骤
      1. 需求分析：你要做的是什么，你设计的系统要实现什么样的目标
      2. 概要设计：系统设计需要依赖的外部环境是什么，比如微信的登录、阿里云存储、服务器、https 证书、第三方依赖等等
      3. 逻辑设计/详细设计：功能步骤划分，比如涉及到多少个角色，每个角色对应什么样的功能，每个功能中的数据流向等等
      4. 代码编写：再上面的需求等都完成后才开始写代码
      5. 软件测试：常见的白盒和黑盒测试。白盒就是里面的代码可以看到，黑盒就是只进行输入和输出的测试。另外现在测试分得更细，会有功能测试、单元测试、压力测试等等
      6. 安装部署：等上面的步骤都完成后才会进行上线部署

数据库设计步骤

数据库设计分为以下 6 个步骤。

  1. 需求分析
  2. 概要设计
  3. 逻辑结构设计
  4. 物理设计阶段
  5. 数据库实施阶段
  6. 数据库运行和维护阶段

需求分析

  1. 需求分析任务
  • 调查分析用户活动
  • 收集和分析需求数据，确定系统边界信息需求，处理需求，安全性和完整性需求
  • 编写系统分析报告
  1. 需求分析方法
  • 自顶向下：从最上层的系统组织机构入手，采用逐层分解的方式分析系统。用数据流图和数据字典描述系统。
  • 自底向上：从单向功能分析组成全部系统。

概要设计

当需求分析清楚后，就就需要设计 E-R 图建模。

  1. 目标：设计数据库的 E-R 模型图。（entity实体，relation关系）
  2. 设计步骤：
    1. 第一步：数据抽象与局部 E-R 模型设计
      1. 数据抽象：抽象实体、实体属性、实体之间的关系
      2. 局部 E-R 模型设计：属性必须是不可分的数据项，联系只能发生在实体之间
    2. 全局 E-R 模型设计
      1. 多元集成法：一次性将多个局部 E-R 图合并为一个全局 E-R 图
      2. 二元集成法：首先集成两个重要的局部 E-R 图，然后用累加的方法逐步将一个新的 E-R 图集成进来

E-R 图示例。
image.png

逻辑结构设计

  1. 目的：将 E-R 图中的实体、属性和联系转换成为关系模式。
  2. 实体间关系转换遵循的原则
    1. 实体转换为关系模式，实体的属性就是关系的属性，实体的键就是关系的键
    2. 联系转换为关系模式，与该联系相连的个实体的键以及联系的属性均转换为该关系的属性
    3. 联系关系三种情况：（1:1 1:n n:n）
  3. 实体间关系的转换规则
    1. 1:1 关系转换为一个独立的关系模式
    2. 1:n 关系可以转换为一个独立的关系模式
    3. n:n 关系转换为一个关系模式

image.png
image.png
image.png

  1. 关系模式规范化：3NF

物理设计阶段

  1. 目标：对于给定的逻辑数据模型，选取一个最适合应用环境的物理结构
  2. 物理结构设计：
    1. 存储结构的设计
    2. 数据存取路径的设计
    3. 数据存放位置的设计
    4. 系统配置的设计

数据库实施阶段

指根据逻辑设计和物理设计的结果，在计算机上建立起实际的数据库结构、装入数据、进行测试和试运行的过程。

数据库运行和维护阶段

  • 维护数据库的安全性与完整性
  • 检测并改善数据库性能
  • 重新组织和构造数据库

案例

创建一个订单相关的数据库表设计。

  1. -- t_product.sql
  4. -- 创建一个新的数据库
  5. create database test default character set utf8mb4 default collate utf8mb4_general_ci;
  7. -- 切换数据库
  8. use test;
  10. /**
  11.  * 表设计：
  12.  * 一张订单：编号  商品id 商品名字 单价 数量 创建日期 操作员 总金额
  13.  * 
  14.  * 订单合计表（总表）： t_order  order_id  user_id  total_money  cdate
  15.  * 订单详情表： t_orderItem  item_id  pid  pname  price  num  order_id
  16.  */
  18. -- 创建订单总表
  19. create table if not exists t_order (
  20.     order_id int primary key auto_increment,    -- 订单编号
  21.     user_id int,    -- 用户编号
  22.     total_money decimal(10, 2),        -- 总金额
  23.     cdate date    -- 创建时间
  24. );
  26. -- 创建订单详情表
  27. create table if not exists t_orderItem (
  28.     item_id int primary key auto_increment,        -- 订单详情编号
  29.     pid int,    -- 商品id  => 这里还应该有一张商品表
  30.     pname varchar(100),        -- 商品名称
  31.     price decimal (10, 2),    -- 商品价格
  32.     num int,    -- 商品数量
  33.     order_id int,    -- 外键
  34.     constraint fk_order_id foreign key(order_id) references t_order(order_id) -- 关联t_order的order_id
  35. );
  37. -- 连表查询
  38. -- 可以同时查询显示两张表中的内容
  39. select o.*, it.* from t_order o join t_orderItem it on o.order_id =it.order_id;
  41. -- 查询部分信息
  42. select it.pid, it.pname, it.price, it.num, o.user_id, o.total_money, o.cdate from t_order o join t_orderitem it on o.order_id = it.order_id;

image.png
image.png
image.png

通过连表查询，将 order_id = 1 的订单中所有商品详情订单都查询出来。
image.png

只查询部分信息。
image.png

注：查询将在后面的章节中说明，这里先简单了解。

to be continue…