MySQL学习笔记 05 - 数据库设计

一、数据库设计概述

数据库设计是指根据用户的需求，规划数据库中的数据对象以及数据对象之间的关系，设计数据库的结构和建立数据库的过程。数据库设计非常重要，糟糕的数据库设计会使系统效率低下、不利于更新和检索数据；良好的数据库设计可以提升应用系统的运行和开发效率、便于扩展。

1、软件开发的步骤

软件开发的步骤大体上分为：

1. 需求分析：分析客户的业务和数据处理的需求。
2. 概要设计：在项目团队内部、设计人员和客户之间进行 ，确认需求信息的正确性和完整性。此阶段，数据库设计方面要设计出各种实体、属性，并以E-R图的形式表示出来。
3. 详细设计：对项目的具体实现进行规划，程序中的类以及类中的方法名都能在这个阶段进行设计。此阶段，数据库设计方面要将E-R图转换为表，并对表添加各种约束。
4. 代码编写：基于详细设计进行代码实现。
5. 运行测试：对项目进行各种测试，包括单元测试、集成测试和系统测试等。
6. 部署发行：对项目进行部署，并且开始商业应用。

2、数据库设计的步骤

数据库设计是在代码编写前完成的，需求分析阶段重点是调查、收集并分析客户业务数据需求、处理需求、安全性与完整性需求。业务数据需求是创建数据库表的依据；数据处理需求是创建存储过程（函数）等数据库对象以及对表进行CRUD的依据；完整性需求是各种约束的依据。

• 数据库的设计过程大致可分为4个步骤：

1. 需求分析：在本阶段主要是得到准确并且完整的用户需求信息，包括数据及其处理。在该阶段中首先要进行调查，调查的方法有：在客户的公司跟班实习、组织召开调查会、邀请专人介绍、调查问卷、查阅客户业务相关的数据。根据调查的信息进行分析，整理出客户与研发人员都能明白的文档（系统需求说明书）。该阶段是整个设计过程的基础，是最困难、最耗费时间的一步。
2. 概念模型：通过对用户需求进行综合、归纳与抽象，形成一个独立于具体DBMS的数据模型。通过系统需求说明书中的描述，标识出系统需要的各种对象（实体）、各种对象的具体属性和各种对象之间的联系。一般本阶段要形成整个系统的E-R图（实体关系图）。是整个数据库设计的关键。
3. 物理模型：为概念模型选取一个物理数据库，如MySQL、Oracle、SQLServer 。
4. 运行验证：在上述设计的基础上，收集数据并具体建立一个数据库，运行一些典型的应用任务来验证数据库设计的正确性=和合理性。一个大型数据库的设计过程往往需要经过多次循环反复。当设计中的某一步聚发生问题时，可能就需要返回到前面去进行修改。因此，在做上述数据库设计时就应考虑到今后修改设计的可扩展性和方便性。

• 概念设计（设计概念模型）实际上就是把需求分析中的数据进行转化，形成程序员视图的过程。其中的关键点有：标识对象、确定对象的属性、标识对象之间的关系、形成E-R图。

3、E-R图

制作E-R图的工具有很多，比如Visio、Power Designer等，多数开发人员更喜欢使用Power Designer，这里，我们采用Power Designer 15.1（以下称PD）完成。使用Power Designer工具设计数据库一般分三步

3.1、创建概念模型（Conceptual Data Model）

概念模型是对业务需求分析后的图表表示方法，描述实体与属性及其关系，概念模型研究的是实体设计能否满足业务需求。在概念模型中不同的表中不允许出现相同的列名称，如果员工表中需要设计deptno的外键来引用部门表的主键属性，则是通过在员工表与部门表之间添加关系来表示表与表之间的关系。

1. 打开Power Designer。
2. 点击“File”菜单，选择“New Model”，在弹出的对话框中选择“Models Types”>“Conceptual Data Model”，在“Model Name”中输入概念模型的名称“company_info”，最后点击“OK”按钮。
3. 在工具箱中找到“实体”图标和“关系”图标。 鼠标左键选中“实体”图标，然后在工作区空白区域点击左键，创建一个实体。
4. 鼠标左键双击“Entity_1”实体，弹出实体属性对话框，在实体属性对话框中的“General”选项卡中的Name中输入“部门表”，Code中输入“dept”，其中Name是实体的标签，Code是实体的名称。
5. 鼠标左键点击“Attributes”选项卡，在该选项卡中输入表的属性信息。Name是属性的标签，Code是属性的名称，Data Type是属性的类型，Length是属性的长度，P（Primary Key：主键）表示属性是否是主键，M（Mandatory：强制）表示属性不允许包含NULL值，D（Display：显示）表示该属性是否在工作区中显示。同样的方法创建员工表。
6. 最后来创建概念模型中的关系。鼠标左键选择工具箱中的“关系”，然后鼠标左键从“部门表”点击拖动到员工表后松开，在部门表和员工表之间建立了1对多的关系。
7. 鼠标左键双击表与表之间的关系，可以查询表与表之间的关系。

3.2、将概念模型转换为物理模型（Physical Data Model）

相对于概念模型研究的是实体设计能否满足业务需求，而物理模型则考虑具体使用什么数据库来存储数据。概念模型可以转换为物理模型，转换后实体将被转换为表，属性被转换为列。

1. 在概念模型中点击“Tools”菜单，选择“Generate Physical Data Model”来生成物理模型。
2. 鼠标左键双击概念模型中的“Diagram_1”和物理模型中的“Diagram_1”可以在概念模型与物理模型之间进行切换。在物理模型中鼠标左键双击表与表之间的关系连线，可以查询物理模型下的表之间的关系。

3.3、将物理模型转换为SQL脚本

1. 根据物理模型可以生成对应的数据库脚本。当切换到物理模型时，Power Designer的菜单栏会多出一个名称为“DataBase”的菜单，点击“DataBase”菜单，选择“Generate DataBase”命令后弹出如下对话框。
2. 在Directory中选择数据库脚本的存储目录，在File name中输入数据库脚本文件的名称，然后点击“确定”按钮，将物理模型导出为数据库脚本。

二、数据库设计范式

1、第一范式(1NF)

第一范式要求单个表中每个列必须是原子列（每一列都是不可再分的最小数据单元）、列不存在重复属性、每个实体的属性也不存在多个数据项。

2、第二范式(2NF)

第二范式是在满足第一范式的基础上，要求数据表里的所有数据都要和该数据表的主键有完全依赖关系。如果有哪些数据只和主键的一部份有关的话，它就不符合第二范式。也就是说表必须有主键，而且如果一个数据表的主键只有单一一个字段的话，它就一定符合第二范式。

3、第三范式(3NF)

第三范式是在满足第二范式的基础上，每一个非主键列都直接依赖主键列，不依赖其它非主键列，即数据库中不能存在传递函数依赖关系。

4、三大范式总结

数据库设计三大范式总结如下：

范式	要求
第一范式	1：列具有原子性 2：列不允许出现复合属性 3：值只能是一个数据项
第二范式	每一个非主键列都与主键列相关，每个表只描述一件事情
第三范式	每一个非主键列都与主键直接相关

## 5、三大范式的优缺点

• 优点：

1. 范式化的数据库更新起来更加快；
2. 范式化之后，只有很少的重复数据，只需要修改更少的数据；
3. 范式化的表更小，可以在内存中执行；
4. 很少的冗余数据，在查询的时候需要更少的distinct或者group by语句。

• 缺点：

1. 范式化的设计会产生更多的表；
2. 在查询的时候经常需要很多的表连接查询，导致查询性能下降 。