关系型数据库设计

目录

1.数据库设计的重要性及定义

1.1 数据库设计的重要性

1.1.1 失败的数据库设计造成的后果 

1.1.2 优秀的数据库设计带来的好处 

1.2 数据库设计的定义 

2.数据库需求分析 

2.1 需求分析的步骤

2.1.1 收集信息

2.1.2 标识实体

2.1.3 标识每个实体的详细信息 

2.1.4 标识实体之间的联系 

3.概念模型:E-R图 

 3.1 E-R图中的标识

3.2 E-R图中的映射关系 

3.2.1 映射关系 

4.物理模型:数据库模型图 

4.1 绘制数据库模型图

4.1.1 使用PowerDesigner创建物理模型 

4.1.2 绘制模型图 

4.2 生成SQL代码 

4.2.1 生成单张表的数据库脚本 

4.2.2 批量生成数据库中所有数据表的脚本 

5.数据库规范设计 

5.1 常见数据库设计问题        

5.1.1 常见的更新异常

5.2 三大范式 

5.3 规范化和性能的关系 

---

数据库开发是一门非常强调实践的技术，学习中不要只死记硬背理论知识，而要将理论与实践相结合，在实践中提高自己解决问题的能力。

1.数据库设计的重要性及定义

使用SQL Server时，开发的数据库就好似便捷式简易住房，它由多块板材拼接组合而成，不需要做细致的设计。后续在企业中做项目，小型的信息系统就好似搭建平房，需要有简单的图纸规划房屋的结构，需要考虑房屋的承重等问题，在实际的软件开发过程中，对于淘宝、京东这类大型软件系统来说，设计就显得更加重要。这就像盖摩天大楼，不但要有设计，而且要对设计进行反复的计算和审核，否则，一旦在建筑过程中发现问题，将造成无法承受的损失。 

1.1 数据库设计的重要性

在实际的项目开发过程中，如果系统存储的数据量较大，表的数量较多，数据间的关系就会比较复杂。此时要考虑规范的数据库设计，并通过数据库的三大范式检验设计的合理性，然后进行建库、建表等工作。

1.1.1 失败的数据库设计造成的后果 

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

1.1.2 优秀的数据库设计带来的好处 

• 降低应用程序的开发难度
• 具备高效的查询效率
• 具备良好的扩展性(应对因业务扩展而给程序带来的变化)。

通过规范化的数据库设计，可以消除不必要的数据冗余，使数据库设计更加合理，从而达到提高程序性能的目标。 

1.2 数据库设计的定义 

数据库设计就是对数据库中的实体，以及这些实体之间的关系进行规划和结构化的过程。数据库设计最终的结果就是数据库模型。 

2.数据库需求分析 

软件项目的开发要经历需求分析、概要设计、详细设计、代码实现、软件测试、安装部署等阶段。
数据库作为软件项目最重要的组成部分，数据库设计开发在整个软件项目周期中都有体现。 

2.1 需求分析的步骤

2.1.1 收集信息

在收集信息阶段，需要通过业务人员的访谈等方法了解数据库需要存储哪些业务信息(数据)，这些信息需要支撑哪些业务功能。以医院管理系统为例，在收集信息阶段需要明确医院的哪些信息需要由数据库来保存。

2.1.2 标识实体

• 在收集信息阶段，获得的是关于业务需求的描述。
• 实体在数据库中就是表，在Java中就是实体。
• 在数据库设计中不能重复出现含义相同的实体。 

2.1.3 标识每个实体的详细信息 

从收集的信息中标识实体后，下一步的工作是围绕这些实体开始补充每个实体所需要存储的详细信息，也就是实体的属性。 

2.1.4 标识实体之间的联系 

在系统中，各实体不是孤立存在的。在使用系统完成业务功能的过程中，各实体间的数据会相互关联。
在数据库需求分析中还要理清各实体间的联系。 

3.概念模型:E-R图 

在数据库需求分析阶段，我们已经充分了解系统需要实现的业务功能，并完成实体标识及实体间关系的标识。此时需要进一步进行概要设计阶段。为了更加形象直观地体现数据库的设计意图，如同建筑行业的施工图纸，数据库设计也有类似的图形化展示工具——E-R图(Entity Relationship Diagram，实体-联系图)，通过一些具有特定含义的图形符号提供图形化展示实体、属性和联系的方法。

 3.1 E-R图中的标识

1.实体
在E-R图中，使用矩形表示实体。
2.属性
在E-R图中，椭圆形表示实体的属性。(字段)
3.联系
联系指在需求分析阶段标识的实体之间的关系，使用菱形表示。 

3.2 E-R图中的映射关系 

在绘制E-R图的过程中，还需要在E-R图上表现实体之间的映射关系，即一个实体关联其他实体的个数。这种映射关系被称为映射基数。对于实体A和B之间的映射关系，其映射基数必为下列基数之一。 

3.2.1 映射关系 

一对一联系

• A中的一个实体最多与B中的一个实体关联，并且B中的一个实体最多与A中的一个实体关联。例如，学校里班级与班主任之间的联系是一对一的联系。学校里一个班级只有一个教师作为班主任，而一个教师也只能做一个班级的班主任。

一对多联系(1/N)/多对一联系(N/1)

• 一对多联系是A中的一个实体可以与B中的任意数量的实体关联；B中的一个实体最多与A中的一个实体关联。例如，学校里班级和学生的联系是一对多联系。学校里一个班级可以有多个学生，而一个学生只能属于一个班级。反过来说，学生和班级的联系是多对一联系。

这个用得最多;

多对多联系

• A中的一个实体可以与B中的任意数量的实体关联，反之亦然。例如，学校里的任课教师和班级的关系。一个任课教师可以给多个班级上课，一个班级可以有多个任课教师上课。 

使用多对多时需要创建中间表来进行连接，中间表的除主键外的键是其他实体的主键； 

在E-R图中表示映射关系时，箭头指向映射关系为1的实体。

4.物理模型:数据库模型图 

完成E-R图的绘制，相当于完成了数据库的概要设计，概要设计阶段的重点是理清系统中存在的实体和实体之间的关系。下一步就要进行数据库的详细设计。数据库的详细设计采用数据库模型图来实现。在这一阶段，工作内容是把E-R图转换为数据库中的多张表，并标识各表的主键和外键。设计良好的数据库模型通过图形化的方式显示数据库存储的信息、信息的数据类型，以及表与表之间的关系。 

4.1 绘制数据库模型图

4.1.1 使用PowerDesigner创建物理模型 

• 启动PowerDesigner，然后执行"File"-"new Model"命令，在打开的"new Model"对话框中选择"Physical Data Model"(物理模型)-”Physical Diagram“(物理图)图标；
• 然后在下方的DBMS选项中选择MySQL即可 ；

4.1.2 绘制模型图 

在右侧的Toolbox工具中绘制数据库模型图主要使用Dhysical Diagram下的3钟工具，从左到右分别是Table(表)、View(视图)和Reference(关系)。 

创建数据表实体 

单击table按钮，把鼠标指针移动到绘制区后，单击即可创建一个table图像。如果要切换回选择状态，右击即可切换。
双击创建的table图形，进入编辑界面，如下所示 :

 

在table编辑界面可以添加表名，其中Code为代码生成时使用的名字，必须为英文；而Name可以使用中文，方便阅读。

添加属性 

在表编辑界面选择"Columns”选项卡，进入属性编辑页面。按照E-R图中实体的设计给patient表添加相应的属性即可。每个属性可设置的数据项如下。

可设置属性 

添加实体中的映射关系 

完成实体创建后，还需要在数据库模型中建立实体间的练习及外键约束。在两个实体间连线，箭头指向主表。

• 一对多关系是一张表中的主键对应一张表中的可重复字段，主键的值是不能重复的，而关联的字段是可以重复的，这就会存在一个值对应一个值(一对一)或者一个值对应多个值(一对多)两种可能
• 要表示多对多关系，除了将多对多关系中的两个实体各自转换为表，一般还会创建第3张表，即链接表，它将多对多关系划分为两个一对多关系。将这两张表的主键都插入第3张表中。

在双击连接线后中的joins中即可查看连接的关系是否正确；

4.2 生成SQL代码 

PowerDesigner的强大之处还在于创建数据库模型图后可以直接生成数据库脚本。 

4.2.1 生成单张表的数据库脚本 

 

打开这个页面即可看到创建脚本，选中复杂粘贴即可使用。

4.2.2 批量生成数据库中所有数据表的脚本 

 

 

设计数据库脚本文件的保持位置及数据库脚本文件的文件名，单击"确定"按钮生成数据库脚本； 

5.数据库规范设计 

在数据库的需求分析阶段，由于设计者的工作经验、水平、视角等因素，不同的人设设计的数据库各不相同。开发者缺乏在设计阶段对数据库评价的手段，往往在系统开发到一半甚至上线后才发现数据库设计得不合理，导致系统性能低下甚至不稳定。 

5.1 常见数据库设计问题        

数据库设计问题大部分是数据冗余引起的。什么是数据冗余呢?以医院信息管理系统为例，考虑一种极端的情况，整个医院管理系统由一张表组成。一张设计糟糕的、存在大量冗余数据的数据表如下所示: 

从以上表中可以看到大量的冗余数据，如姓名，检查名称。当然这样的数据对于查询来说很方便，只需要异常简单的查询就能够把所有需要的数据查询处理。数据库的基本操作还包括增、删、改操作，这3项操作可以归为数据更新操作，大量的冗余数据的存在容易导致数据库的更新异常。常见的更新异常如下: 

5.1.1 常见的更新异常

• 插入异常(Insertion Anomaly)。在表中增加一条检查数据。例如，医生给一名患者开了"凝血五项"的检查，但是医生在录入数据时只录入了"凝血"这样一个不规范的检查名称。出现这种情况就会导致数据库中记录的内容越来越不一致。不利于系统的维护。
• 删除异常(Deletion Anomayly)。这种异常的表现是，假如在以上表中，删除第2条记录，其检查名称是"尿常规"。因为表中只有这一条数据是"尿常规"，所以在执行删除后，数据库就再也找不到"尿常规"这种检查。也就是说，在数据库层面医院不能做这项检查。(会删除有效数据，比如，删除夏颖的数据，即会把所有的数据删除)
• 修改异常(Modification Anomayly)。这种异常指当修改某个值时，同样的操作需要重复多次。就是你如果修改一个已经在表中重复的值时，它就会全部修改。 

5.2 三大范式 

总共有5大范式，但是全部完成有可能影响开发效率，一般实际开发中完成1，2范式即可; 

在设计数据库时，有一些专门的规则，它们被称为数据库的设计范式。遵守这些规则，将会创建设计良好的数据库。下面将逐一讲解数据库设计中的三大范式理论。 

三大范式葛优非常通俗直白的名称，分别是第一范式(1NF)、第二范式(2NF)、第三范式(3NF)，从名称上无法分辨每个范式的含义。 

5.3 规范化和性能的关系 

为了满足三大范式，数据操作性能会受到相应的影响。所以，在实际的数据库设计中，既要考虑三大范式，避免数据的冗余和各种数据操作异常，又要考虑数据库访问性能。有时，为了减少表间的连接，提高数据库的访问性能，允许适当的数据冗余列，这可能是最合适的数据库设计方案。
    
在以上表中，总金额类的存在表明该表的设计不满足第三范式，因为"总金额"等于"单价"乘以"数量"，这说明"金额"是冗余列。这里的数据冗余的作用是提高查询效率，我们都知道，存到数据库中是存到内存中，就会占取空间，但是如果每次查询时都去计算就会降低查询效率，所以这里使用空间换查询效率的方式 ；

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