本文共 7624 字，大约阅读时间需要 25 分钟。

数据库设计

第一节：为什么需要规范的数据库设计

大多数开发人员开发复杂度不高项目时，都是根据业务需求，直接创建数据库，创建表和插入测试数据，然后再查下数据，为什么需要强调先设计再创建数据库，创建表呢？原因非常简单，正如修造建筑物一样，如果盖一间茅屋或一间简易平房，您会花钱请人设计房屋图纸吗？毫无疑问，没人请，但是如果是房地产开发商开发一个楼盘，修建多栋楼的小区，他会请人设计施工图纸吗?答案是肯定的，不但开发商会考虑设计施工图纸，甚至很多专业的购房者也会在看房时要求开发商出示设计图纸。

同样道理，在实际的项目开发中,如果系统的数据存储量较大,设计的表比较多。表和表之间的关系比较复杂,我们就需要首先考虑规范的数据库设计,然后再进行具体的创建库。创建表的工作,不管是创建动态网站,还是创建桌面窗口应用程序,数据库设计的重要性都不言而喻,如果设计不当,会存在数据操作异常,修改复杂，数据冗余等问题,程序性能会受到影响,通过进行规范化的数据库设计,可以消除不必要的数据冗余,获得合理的数据库设计,提高项目的应用性能。

1.1 什么是数据库设计

​ 数据库设计就是将数据库中中数据实体以及这些数据实体之间关系进行规划和结构化的过程。

​ 如图所示为一个数据库的结构,该数据库包含学生及其成绩信息,图中还显示了Student(学生)、Gade(年级)、Course(课程)以及Result(成绩)这四个数据实体之间的关系。

1.2 数据库设计重要性

数据库中创建的数据实体的种类,以及在数据实体之间建立的复杂关系是决定数据库系统效率的重要因素。

槽糕的数据库设计表现在两个方面

• 效率低下
• 更新和检索数据时会出现许多问题

良好的数据库设计表现在以下几方面

• 效率高
• 便于进一步扩展
• 使得应用程序的开发变得更容易

第二节 设计数据库步骤

开发一个项目需要经过需求分析,概要设计,（详细设计）,代码编写,运行测试和上线维护几个阶段,下面重点讨论在各个阶段中数据库的设计过程。

​需求分析阶段：根据项目功能需求，分析客户的业务和数据实体。

​概要设计阶段：绘制数据库的ER模型图,用于在项目团队内部、设计人员和客户之间进行沟通,确认需求信息的正确性和完整性。

详细设计阶段：将ER图转换为多张表,进行逻辑设计,确认各表的主外键并应用数据库设计的三大范式进行审核,经项目组开会讨论确定后,还需根据项目的技术实现团队开发能力以及项目的经费来源,选择具体的数据库(如MySQL成 Oracle等)进行物理实现。包括创建库和创建表,存储过程等,创建完毕后,开始进入代码编写阶段,开发前后端应用程序。

​现在,我们共同讨论在需求分析阶段,后台数据库的设计步骤。

​需求分析阶段的重点是调查,收集并分析客户业务的数据需求,处理需求,安全性与完整性需求常用的需求调研方法有在客户的公司跟班实习,组织召开调查会,邀请专人介绍,设计调查表.并请用户填写和查阅与业务相关数据记录等。

​ 常用的需求分析方法有调查客户的公司组织情况,各部门的业务需求情况,协助客户分析系统的各种业务需求和确定新系统的边界。

​ 无论数据库的大小和复杂程度如何,在进行数据库的系统分析时,可以参考下列基本步骤

• (1) 收集信息（根据需求说明书）
• (2) 标识实体
• (3) 标识每个实体需要存储的详细信息
• (4) 标识实体之间的关系

2.1 收集信息

创建数据库之前，必须充分理解数据库需要完成的任务和功能。简单地说，就是需要了解数据库需要存储哪些信息(数据),实现哪些功能,以酒店管理系统为例.我们需要了解酒店管理系统的具体功能,以及在后台数据库中保存的数据。

酒店为客人休息准备充足数量的客房,后台数据库需要存放每间客房的信息,如房间号,房间类型,价格等。

旅客在酒店入住要办理入住手续,后台数据库需要存放客人的相关信息,如客人姓名房间号等。

2.2 标识实体

在收集需求信息后,必须标识数据库要管理的关键对象或实体,我们曾经学习过对象的概念,实体可以是有形的事物如人或产品:也可以是无形的事物,如商业交易,公司部门或贷款周期,在系统中标识这些实体以后,与它们相关的实体就会条理清楚,以酒店管理系统为例,我们需要标识出系统中的主要实体。

客房：包括单人间、标准间、三人间、豪华间、总统套房。 客人：入住酒店旅客的个人信息 数据库中的每个不同的实体都拥有一个与其相对应的表,也就是说,在我们的酒管管理系统数据库库中,会对应至少两张表,分别是客房表和客人表。 注意：实体一般是名词，一个实体只描述一件事情,不能重复出现含义相同的实体。

2.3 标识每个实体的详细信息（数据项）

将数据库中的主要实体标识为表的候选实体以后,下一步就是标识每个实体存储的详细信息,也称为该实体的属性,这些属性将组成表中的列,简单地说:就是需要细分出每个实体包含的属性信息,接下来以酒店管理系统为例,逐步分解每个实体的属性信息.如图

注意：

分解时,含义相同的成员信息不能重复出现,例如联系方式和电话等。 每个实体对应一张表,实体中的每个属性对应表中的每一列。,从上图的关系可以看出客人应该也含姓名和身份证号等

2.4 标识实体之间的关系

关系型数据库有一项非常强大的功能,它能够关联数据库中各个关系的相关信息,不同类型的信息可以单独存储,但是如果需要,数据库引擎还可以根据需要将数据组合起来,在设计过程中,要标识实体之间的关系,需要分析数据库表,确定这些表在逻辑上是如何相关的,然后添加关系列建立起表之间的联接,以酒店管理系统为例,客房与客人有主从关系,我们需要在客人实体中表明它入住的客房房间号。

第三节 绘制E-R图

在需求分析阶段解决了客户的业务和数据处理需求后,就进入了概要设计阶段,我们需要和项目团队的其他成员以及我们的客户沟通,讨论数据库的设计是否满足客户的业务和数据处理需求,和机械行业需要机械制图,建筑行业需要施工图一样,我们的数据库设计也需要图形化的表达方式一E-R（Entity-Relationship）图,也可称为实体一关系图.它包括一些具有特定含义的图形符号,下面将介绍相关理论和具体的图形符号。

3.1 实体-关系模型

1.实体

所调实体就是指现实世界中具有区分其他事物的特征或属性并与其他实体有联系的实体,例如,酒店管理系统中的客房(如1008房间,1018房间等),客人(如张三,李四、王五等)等。

实体一般是名词,它对应我们表中的一行数据,例如张三用户这个实体,将对应于客人表中,张三客人所在的一行数据,包括他的姓名、身份证号码等信息。严格地说,实体是指表中一行一行的特定数据,但我们在开发时,也常常把整个表也称为一个实体。

2.属性

属性可以理解为实体的特征.例如,客人这一实体的属性有入住日期.结账日期和付的押金等,属性对应表中的列。

3.联系

联系是两个或多个实体之间的关联关系,如图所示为客人实体和客房实体之间的联系。

实体使用方块表示,实体一般是名词,属性使用椭圆表示,一般也是名词.联系使用菱形表示,一般是动词。

4.映射基数

映射基数表示通过联系与该实体关联的其他实体的个数,对于实体集X和Y之间的二元关系,映射基数必须为下列基数之

一对一:X中的一个实体最多与Y中的一个实体关联,并且Y中的一个实体最多与X中的一个实体关联。假定规定每辆汽车同时只能占用一个车位,同时每个车位也只停放一辆汽车,那么,汽车实体和车位实体之间就是一对一的关系,

一对多:X中的一个实体可以与Y中的任意数量的实体关联,Y中的一个实体最多与X中的一个实体关联,一个客房可以入住多个客人,所以说,客房实体和客人实体之间就是典型的一对多关系,一对多关系也可以表示为1:N

多对一:X中的一个实体最多与Y中的一个实体关联,Y中的一个实体可以与X中的任意数量的实体关联,客房实体和客人实体之间是典型的一对多关系,反过来说,客人实体和客房实体之间就是多对一的关系

多对多:X中的一个实体可以与Y中的任意数量的实体关联,反之亦然,例如,图书馆的每本图书可以借给多个读者,每个读者可以借阅多本书,那么,图书实体和读者实体之间就是典型的多对多的关系,多对多关系也可以表示为M:N.

5.实体关系图

ER图以图形的方式将数据库的整个逻辑结构表示出来,ER图的组成包括以下几部分

矩形表示实体集 椭圆形表示属性 菱形表示联系集 直线用来连接属性和实体集,也用来连接实体集和联系集(直线可以带有箭头，表示一方，没有箭头表示N方)

客人和客房E-R图

课程和学生E-R图

系和教师E-R图

​ 绘制E-R图后，我们还需要与客户反复进行沟通，让客户提出修改意见，以确认系统中的数据处理需求是否正确和完整。

3.2 关系数据库模式

​ 一个关系描述为属性名的集合称为关系模式，关系数据库模式是对关系数据库结构的数学描述形式。

酒店管理系统实体”客人“和”客房“,分别可以使用关系模式表示为:

​ 客人记录（客人编号，客人姓名、身份证号、房间号、入住日期、结账日期、押金、总金额）

​ 客房（房间号、房间描述、房间类型、房间状态、床位数、入住人数）

课程和学生关系模式表示如下：

​ 课程（课程号，课程名，学时，类别）

​ 学生（学号，姓名，性别，专业，出生日期，照片） ​ 学生课程表（学号，课程号，成绩）

系和教师关系模式表示如下：

​ 系（系号，系名，系主任，电话）

​ 教师（教师号，姓名，专业，职称，性别，年龄）

第四节 绘制数据库模型图

​ 概要设计阶段解决了客户的需求捕获，并绘制E-R图，在后续的详细设计阶段,我们需要把E-R图转换为数据库中多个表,并标识每个表的主外键。设计良好的数据库模型图可以通过图形化的方式显示数据库存储的信息，以及表之间的关系，以确保数据库设计准确、完整有效，下面我们以酒店实体为素材使用PowerDesinger工具演示如何创建数据库模型图。

​

第五节 数据规范化

​ 在概要设计阶段，我们设计出来了E-R图，但是怎么审核这些设计图呢?,怎么评审出最优的设计方案呢？接下来就是规范化E-R图。

​ 接下来我们来酒店管理系统的客房信息表数据

​ 从用户的角度而言，将所有信息放在一个表中很方便,因为这样查询数据库可能会比较容易，但是上述表具有下列问题。

• 信息重复

  “客房类型”、“客房状态”、“床位数"列中有许多重复的信息.例如"标准间”、“入住”信息。会造成空间浪费，容易出错，不小心输入”标准间“和“标间”,在数据库中表示两种不同的客房类型。

• 更新异常

  冗余信息不仅浪费空间，而且会增加更新的难度，如果需要将“客房类型”修改为"标间"而不是“标准间”，则需要修改所有包含该值的行,如果由于某种原因，没有更新所有行，这种情况称为更新异常.

• 插入异常

  我们发现2001、2002和2003房间的价格分别是188元、168元、和158元、尽管这三间客房都是标准间，但是他们的价格出现不同，这种问题称为插入异常。

• 删除异常

  某些情况下，当删除一行数据时，可以会丢失有用的信息，例如，删除客房号为‘’1001“的行，就会丢失客房类型为"单人间"的信息。再查询时，发现没有了单人间,会误以为只有”标间“和”总统套房“了，这种情况称为删除异常。

5.1 规范设计

​ 如何重新规范设计表才能避免上述诸多异常？

​ 在数据库设计时,有一些专门的规则,称为数据库的设计范式，道守这些规则,您将创建设计良好的数据库,下面逐一讲解数据库设计中著名的三大范式理论。

1.第一范式(1NF, Normal Formate)

第一范式的目标是确保每列的原子性

第一范式的目标是确保每列的原子性,如果每列(或者每个属性值)都是不可再分的最小数据单元(也称最小的原子单元),则满足第一范式(1NF)。

例如 客人信息表(姓名,客人编号,地址,……) 其中地址列还可以细分为国家,省,市,区等,更多的程序甚至把"姓名"列也拆分为姓”和名”等。

2.第二范式(2NF)

第二范式目标是每个表只描述一件事情。

在数学中函数依赖关系分为：完全函数依赖（包括直接依赖、传递依赖）、部分函数依赖

第二范式在第一范式的基础上更进一层,其目标是确保表中的每列都和主健相关（完全依赖），不能出现部分依赖。如果一个关系满足第一范式（1NF）,并且除了主键以外的其他列都和主键相关,则满足第二范式(2NF)。

例如 客房信息表(如房间号,客房描进,客房类型,客房状态,床位数,入住人数,价格等),该表的数据主要用来描述客房,所以将“房间号”设为主键,“客房描述”,“床位数”,“价格”三列都和"房间号"主键列相关,但“客房类型”列和“客房状态”列 和主键列不相关(存在部分依赖),即"客房类型"列不完全依赖于“房间号”主键列,该列应从该表中删除,放入客房类型表中,同样,"客房状态"列也因为不依赖于“房间号”主键列,而应从该表中删除,单独放到客房状态表中。 这样,客房表就只描述一件事情一客房信息在使用第二范式对客房表进行规范化之后,酒店管理系统数据库中有关客房信息表结构。 分解成了以下三个表： 客房表(房间号、客房描述,客房类型编号,客房状态编号,床位数、入住人数,价格,…) 客房类型表(客房类型编号,客房类型名称) 客房状态表(客房状态,客房状态名称)

下图是符合第二范式的E-R图

3.第三范式(3NF)

如果一个关系满足2NF，并且除了主键以外的其他列都不传递依赖于主键列，则满足第三范式（3NF）

第三范式在第二范式的基础上更进一层,第三范式的目标是确保每列都和主键列直接相关,而不是间接相关,如果一个关系满足第二范式(2N),并且除了主键以外的其他列都只能依赖干主键列，列和列之间不存在相互依赖关系,则满足第三范式。

为了理解第三范式,需要根据 Armstrong公理之一定义传递依赖。假设A.B和C是关系R的三个属性,如果A→B且B→C,则从这些函数依赖(FD)中,可以得出A→C.如上所述,依赖A→C是传递依赖。

还是以酒店的客房为例,符合第二范式的客房信息表结构是

客房信息表(房间号、客房描述、客房类型编号、客房状态编号、床位数、入住人数、价格…）

初看该表没有问题,满足2NF,每列都和主键列“房间号“相关,再细看您会发现"床位数"列、“价格”列也和“客房类型编号”列相关,最后经过传递依赖,"床位数"列,"价格"列和"客房类型名称"列相关。为了满足3NF,应该去掉床位数”列和价格”列,将此列放入客房类型表中。这样,就生成符合第三范式的酒店管理系统的数据库结构,如下所示

客人记录(客人姓名、身份证号、房间号、入住日期、结账日期、押金、总金额）

客房(房间号、客房描述、客房类型编号、客房状态编号、入住人数)

客房类型(客房类型编号、客房类型名称、床位数、价格)

客房状态(客房状态编号、客房状态名称)

了解了用于规范化数据库设计的三大范式后,让我们一起来审核上面表中实体

1)是否满足第一范式 第一范式要求每列必须是最小的原子单元,即不能再细分。前面我们提及过,为方便查询,地址需要分为省.市、区等,但我们目前还没有这方面的查询需求,因此本例已经符合第一范式。 2)是否满足第二范式 第二范式要求每列必须和主键相关,不相关的列放入别的表中,即要求一个表只描述一件事情。 应用的技巧是,我们可以直接查看该表描述了哪几件事情,然后一件事情创建一张表 您看看该表描述了哪几件事情? 相信您一定看出来了,该表描述了以下三件事情。

客房信息。

客房类型信息

客房状态信息

3）是否满足第三范式

第三范式要求表中各列必须和主键直接相关,不能间接相关,浏览各个表，都满足第三范式。

符合第三范式的E-R图

使用PowerDesigner设计的数据库模型图

5.2 规范化和性能的关系

需要提醒的是,对于项目的最终用户来说,客户最关心的是方便、清晰的数据结果。您如果让客户选择,毫无疑问,客户会认为最初的表设计最适合需求,虽然它根本就不满足三大范式,并且存在大量的数据冗余。

所以说在设计数据库时设计人员和客户对数据库的设计有一定的矛盾。通过三大范式分解的三个表,为了满足客户的需求,最终需要通过三个表之间的联接查询,恢复客户需要的数据结果,插入数据同样如此对客户输入的数据,我们需要分开插入在三个不同的表中。 由此可以看出,为了满足三大范式,我们的数据操作性能会受到相应的影响。所以,在实际的数据库设计中,既要考虑三大范式,避免数据的冗余和各种数据操作异常。又要考虑数据访问性能,有时，为了减少表间连接，提高数据库的访问性能，允许适当的数据冗余列,这可能是最合适的数据库设计方案。

比如：

购买商品表（商品名称，商品型号,单价，数量，金额）

该表不满足第三范式，因为"金额" 可以由”单价“和"数量"计算得到，说明“金额“是冗余列，但是增加”金额“列可以提高查询统计的速度，这就是以空间换时间。

第五节：数据设计综合练习

需求说明

假设某建筑公司要设计一个数据库。公司的业务规则概括说明如下：

公司承担多个工程项目，每一项工程有：工程号、工程名称、施工人员等 公司有多名职工，每一名职工有：职工号、姓名、性别、职务（工程师、技术员）等 公司按照工时和小时工资率支付工资，小时工资率由职工的职务决定（例如，技术员的小时工资率与工程师不同） 根据业务要求：绘制E-R图并规范化，绘制数据库模型图

公司制定了一个工资报表和工时报表如下图

工资报表

项目工时表

通过观察发现表中包含大量冗余数据，可能会导致数据异常：

• 更新异常
• 添加异常
• 删除异常

如何解决：规范化

第一范式优化：符合要求,每一列都是原子不能分割。

第二范式优化：项目工时表描述了三件事情，（项目信息、工时信息、员工信息）；工资表也描述三件事情

​ （项目信息、员工信息、工时信息）

​ 优化后的关系模式

​ 工程表（工程号、工程名称）

​ 员工表（职工号、姓名、职务、小时工资率）

​ 项目工时表（工程号、职工号、工时、创建日期）

第三范式优化：员工表（职工号、姓名、职务、小时工资率）不满足第三范式

​ 优化后

​ 工程表（工程号、工程名称）

​ 员工表（职工号、姓名、职务编号））

​ 职务表（职务编号、职务名称、小时工资率）

​ 项目工时表（工程号、职工号、工时、创建日期）

绘制E-R：使用画图工具

绘制数据库模型图：使用PowerDesigner完成

总结

需求分析阶段，设计数据库的步骤

​ 收集信息

​ 标识实体

​ 标识每个实体的属性

​ 标识实体之间的关系

在概要设计阶段和详细设计阶段，设计数据库的步骤

​ 绘制E-R图

​ 将E-R图转化为数据库模型图

应用三大范式规范化表设计

​ 第一范式确保每列的原子性

​ 第二范式要求每个表只描述一件事情

​ 第三范式要求表中各列必须和主键直接相关，不能间接相关

转载地址：https://blog.csdn.net/qq_45337431/article/details/100669555 如侵犯您的版权，请留言回复原文章的地址，我们会给您删除此文章，给您带来不便请您谅解！