一、MySQL 数据库设计

时间：2023-04-04 10:03:53

一、MySQL 数据库设计

1.1 为什么需要设计数据库

良好的数据库设计

  • 降低应用程序的开发难度
  • 具备高效的查询效率
  • 具备良好的扩展性

糟糕的数据库设计

  • 出现数据操作异常、修改复杂、数据冗余等问题
  • 影响程序的性能，甚至会导致程序崩溃

数据库设计是对数据库中实体以及这些实体之间的关系进行规划和结构化的过程，当数据库比较复杂时，需要设计数据库

1.1.1 软件项目开发周期中数据库设计

  • 需求分析阶段：分析客户的业务和数据处理需求
  • 概要设计阶段：设计数据库的E-R模型图，确认需求信息的正确和完整
  • 详细设计阶段：应用三大范式审核数据库结构
  • 代码实现阶段：物理实现数据库，编码实现应用
  • 软件测试阶段：……
  • 上线部署：……

1.1.2 设计数据库的步骤

收集信息

  • 通过对业务人员的访谈等方法，充分了解用户需求，理解数据库需要存储的业务信息（数据）及需要提供的操作

标识实体 （Entity）

  • 标识数据库要管理的关键对象或实体，实体一般是名词

标识每个实体的属性（Attribute）

标识实体之间的关系（Relationship）

数据库设计中，不能重复出现含义相同的实体

1.2 为什么使用 E-R 图

1.2.1 E-R 图概念

E-R图也称实体-联系图(Entity Relationship Diagram)，提供了表示实体类型、属性和联系的方法，用来描述现实世界的概念模型

符号 含义
□ 实体，一般是名词
○ 属性，一般是名词
♢ 关系，一般是动词

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-NIeZXPap-1680502424751)(./assets/image-20230331234821123.png)]

  • E-R图表示映射关系时，也可以通过添加箭头区分一对多和多对一
  • 箭头指向映射关系为一的实体
  • 在业务人员与开发人员的沟通中，E-R图能够极大的提高彼此之间的沟通效率

1.3 数据库设计模型转换

1.3.1 关系型数据库设计分类

  • 概念数据模型设计

  • 逻辑数据模型设计

  • 物理数据模型设计

1.3.2 各模型之间的元素对应关系

概念数据模型 逻辑数据模型 物理数据模型
实体（Entity） 实体（Entity） 表（Table）
属性（Attribute） 属性（Attribute） 列（Column）
标识符（Identifier） 标识符（Primary Identifier/ Foreign Identifier） 键（Primary key/ Foreign key）
关系（Relationship） 关系（Relationship） 参照完整性约束（Reference）

1.3.3 数据模型转换方案

基本转换原理

  • 将E-R图中每一个实体转换成一个表，实体的属性转换为表的列，实体的标识符转换为表的主键

  • 将实体关系转化为表之间的引用完整性约束

    • 根据关系的不同类型，通过外键引用主键的方式有所不同

1.4 实体关系约束转换

1.4.1 一对多关系的两个实体

  • 一般会各自转换为一张表，且后者对应的表引用前者对应的表

  • 一个表中的主键对应另一个表中可重复的字段

    • 主键的值是不能重复的，关联的字段是可以重复的

    • 存在一个值对应一个值（一对一）或者一个值对应多个值（一对多）

1.4.2 一对一关系的两个实体

  • 一般是一个主键对应一个不可重复的字段

1.4.3 多对多关系的两个实体

  • 除了将多对多关系中的两个实体各自转换为表外，一般还会创建第3个表，称为连接表
    • 将多对多关系划分为两个一对多关系，将这两个表主键都插入到第3个表中

1.5 PowerDesigner 软件

一、MySQL 数据库设计
  • Sybase 公司开发的一款强大的数据库设计软件
  • 开发人员经常使用的数据库建模工具
  • 能够使用设计好的数据库模型直接生成SQL建表语句
  • 极大的提升数据库的开发效率

推荐使用的版本：PowerDesigner 16.5

PowerDesigner 涉及版权无法分享，加封面联系方式获取软件

1.5.1 绘制数据库模型图

  • 创建物理数据模型
  • [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-YH7havwn-1680502424753)(./assets/%E6%90%9C%E7%8B%97%E6%88%AA%E5%9B%BE20230331235814.png)]
  • 绘制模型图
  • 创建表实体
  • 添加属性
  • 添加实体之间的映射关系
  • 两个实体间连线，箭头指向主表
  • [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-AVCWFFwo-1680502424753)(./assets/%E6%90%9C%E7%8B%97%E6%88%AA%E5%9B%BE20230401000646.png)]

1.5.2 生成 SQL 代码

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-0F1h7Ru2-1680502424754)(./assets/%E6%90%9C%E7%8B%97%E6%88%AA%E5%9B%BE20230401000958.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-tnIaKJIU-1680502424754)(./assets/%E6%90%9C%E7%8B%97%E6%88%AA%E5%9B%BE20230401001019.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-awtzJ4zx-1680502424754)(./assets/%E6%90%9C%E7%8B%97%E6%88%AA%E5%9B%BE20230401001132.png)]

生成的 SQL文件可直接拿到 Navicate 运行

PowerDesigner中开发数据库模型图时，需要指定该模型图是为数据库管理系统开发的，因为PowerDesigner支持用数据库模型图生成数据库创建脚本的功能

1.6 数据库设计范式

为了建立冗余较小、结构合理的数据库，设计数据库时必须遵循一定的规则

1.6.1 三大数据库设计范式

  1. 第一范式

    • 第一范式的目标是确保每列保持原子性
    • 如果每列都是不可再分的最小数据单元（也称为最小的原子单元），则满足第一范式（1NF）

  2. 第二范式

    • 第二范式的目标是确保表中的每列都和主键相关
    • 要求每个表只描述一件事情

  3. 第三范式

    • 第三范式的目标是确保每列都和主键列直接相关，而不是间接相关
    • 如果一个关系满足第二范式，并且除了主键以外的其他列都不传递依赖于主键列，则满足第三范式（3NF）

1.7 规范化和性能的关系

为满足某种商业目标，数据库性能比规范化数据库更重要

  • 通过在给定的表中添加额外的字段，以大量减少需要从中搜索信息所需的时间

  • 通过在给定的表中插入计算列（如成绩总分），以方便查询

  • 在数据规范化同时，要综合考虑数据库的性能

存放商品信息的表

商品名称 商品型号 单价 数量 金额
电视机 29英寸 2500 40 100000

金额 = 单价 * 数量，是冗余的列

为了提高查询统计速度，以空间换取时间

不要轻易违反数据库设计的规范化原则，如果处理不好，可能会适得其反，使应用程序运行速度更慢

1.8 用三大范式设计表结构

1.8.1 实现思路

  1. 向各表中插入数据，查看表中的每个属性列是否存在重复、插入异常、更新异常和删除异常

  2. 对照三大范式的定义，解决表中的异常问题

  3. 第一范式的目标是确保每列都是不可再分的最小数据单元，查看每列是否都满足第一范式

  4. 第二范式的每列与主键相关，不相关的放入别的表中，即要求一个表只描述一件事情

  5. 第三范式要求表中各列必须和主键直接相关，不能间接相关，查看各表，满足第三范式，对于不满足三大范式的表要进行拆分

标签：

相关文章