简单数据库设计实例.docx

简单数据库设计实例.docx

《简单数据库设计实例.docx》由会员分享，可在线阅读，更多相关《简单数据库设计实例.docx（12页珍藏版）》请在冰点文库上搜索。

简单数据库设计实例

数据库设计实例之阿布丰王创作

数据库设计是数据库应用系统设计的一个组成部分，其核心是针对于特定的应用环境，设计合理的数据模型，创建数据库及其应用系统，使之能够有效地存储和处理数据，以满足用户的应用需求。

从实用角度出发，数据库设计可分为如下几个步调：

第一步：

创建概念数据模型

◆确定实体和关系

◆确定属性

◆规范化数据

第二步：

生成物理数据模型

第三步：

验证设计

为便于学习者理解和掌握，下面结合具体的实例来讲解和展示数据库设计的详细过程。

假定我们要开发一个小型的ERP系统，以管理公司内部资源，其应用业务场景描述如下：

v512工作室由IT业界专业人士组成，在提供高端IT培训业务的同时，还自主制作并免费发布大量公益性学习资源，工作室以公司形式运营，目前共拥有18名员工，这些员工分属于4个部分，且员工之间存在上下级管理关系。

计划将来根据业务的发展设立更多的部分，聘用更多的员工。

为包管质量，工作室对其成员的各项专业技能进行了级别评定。

8.5.1确定实体和关系

1.确定高级此外活动

要确定本ERP系统数据库设计中的实体和实体间关系，首先应明确要基于该数据库执行的高级别活动，这里所谓的高级别活动是指从用户的视角出发，确定本数据库设计中系统所涉及到的业务活动。

比方，存储和维护员工的个人信息等。

在前述的应用业务场景中，v512工作室需要考虑的高级别活动包含：

-聘用新员工

-解雇现有员工

-维护员工的个人信息

-增设新部分

-裁撤现有部分

-维护部分信息

-维护工作室业务相关的技能信息

-维护各员工的业务技能掌握情况

2.确定实体

接下来要确定的是，针对上述的高级别活动需要记录和维护有关哪些事物的信息，这些事物将被转换为实体。

其中，员工相关信息可抽象为“Employee”实体、部分相关信息可抽象为“Department”实体、技能相关信息抽象为“Skill”实体，为规范和方便起见，这些实体均采取英文命名，并尽量在名称中体现其含义。

3.确定关系

进一步对上述高级活动进行分析，以确定实体间存在何种关系。

具体包含：

-Employee-Department实体之间存在隶属关系

员工必须且只能隶属于某一个特定的部分，一个部分可以包含0～多名员工，此为一对多关系。

这种从两个方向上对同一个关系的细化描述被称为关系的角色，每个关系都对应两种角色。

-Employee-Department实体之间存在管理关系

每一名员工可以管理0～1各部分，每个部分必须由一名员工负责管理（其管理者不必须隶属于本部分），此为一对一关系。

-Employee-Skill实体之间存在掌握关系

每一名员工均应掌握1～多项业务技能，每项技能可能被0～多名员工掌握，此为多对多关系。

-Employee-Employee实体之间存在管理关系

每位员工由0～1位上级员工负责管理，有的员工可能没有上司（比方公司经理），但有的话只能有一位直接上级。

上级员工可以管理0～多位为下级员工。

经分析而得的上述实体间关系如图8-14所示。

图8-14数据库设计实例CDM之1

4.将多对多关系更改为实体

前文中已讲过，在多对多关系中，可能会出现有属性与关系相关联、而不是单纯的与实体相关联的情况，将这样的属性添加到任何一个实体中都是分歧理的，此时应将该关系转换为、或者说定义为实体。

我们这里的Employee与Skill实体之间就存在这种情况——员工所掌握的技能项目及其评定等级信息就与两个实体之间的多对多关系相关联，因此将此多对多关系定义为“人力资源”（HR）实体，转换后的实体-关系如图8-15所示。

图8-15数据库设计实例CDM之2

在实际的数据库应用设计中，更简单而可行的处理原则是，将一切多对多关系均转换为实体，而不必逐个对其进行细化分析，这样可以很好地解决违背数据库规范化第二范式的问题。

5.分解活动

接下来，需要对前述的高级别业务活动做进一步分析，看是否可以将其中的部分或全部项目分解为相对低级别、或者说更细致的业务活动，比方，其中“维护工作室业务相关的技能信息”这一高级此外活动可继续分解为：

-添加新的技能项目

-删除不再使用的技能项目

-维护/更新现有技能项目的详细详细

最后一项高级活动“维护各员工的业务技能掌握情况”也可以进行类似的分解。

需要说明的是高级业务活动的确定以及这里的细化分解并没有一成不变的尺度，这就好比实现某一预期功能的编程工作是没有尺度答案的（有的只是参考实现），其目的都是为后续的确定业务规则和实体属性等步调提供便当，如果业务逻辑不是很复杂的话也可以一步完成，具体由数据库设计者自行掌控即可。

6.确定业务规则

接下来要做的是，对前述业务活动进行再次分析，确定其应遵守的具体规则。

比方，一名员工必须且只能隶属于某一个部分就是一个业务规则。

业务规则通常可以暗示为一对一、一对多和多对多关系，或者相应的约束条件，这些规则将来会体现到数据库的结构之中。

本实例中相关的业务规则如下：

-员工必须隶属于某一个部分

-员工编号一经确定不得更改

-员工的姓名、性别、职务、所属部分等其它个人信息可以更改

-员工所掌握的技能项目及其评定等级可以更改

-每个部分允许设置0～多部电话

…

和前述业务活动的确定及分解情况类似，业务规则的确定以满足实际业务需要为准，注意不要搞得过于繁琐而失去其实用价值，具体详略程度需设计者根据经验自行掌控，也可以待后续环节流露出问题后再追溯回来，进行迭代处理。

8.5.2确定属性

首先，根据前述分析的业务活动的需要确定所有要记录和维护的数据条目，然后再将这些数据条目做为属性关联到相应的实体或关系中，比方：

-员工实体

员工编号、员工姓名、性别、职务、上司工号、工资、出生日期、所属部分

-部分实体

部分编号、部分名称、部分经理、部分地址、电话号码

-技能实体

技能编号、技能名称

-人力资源实体

员工编号、技能编号、掌握程度

设置属性后的实体-关系如图8-16所示。

图8-16数据库设计实例CDM之3

其中，带有下划线的属性为所在实体的标识符。

为实体或关系设置属性时，应尽量采取规范的命名规则——属性的名称应体现其含义、且应遵循统一的命名格式，以便于将来的使用和维护，比方，假定部分编号使用缩略名称dept_name，那么部分名称等相应的其它属性就不该该使用完整名称，如department_name，而应是其名称坚持一致，如dept_name。

在本阶段，设计者只需根据自己的经验和判断进行设计即可，而不必强求数据（属性）与实体间的关联关系一定是正确或合理的，后续的环节还将对现有的数据模型进行规范化处理，以发现可能存在的问题并进行修正。

8.5.3规范化数据

前文中已经讲过，规范化数据的目的在于防止出现数据冗余和操纵异常，进而节省存储空间并更好地确保数据的一致性，实际上就是对所设计的数据模型进行一系列的规范化测试（判断其是否满足指定的范式要求）、发现问题并进行整改的过程。

在进行数据的规范化测试之前，应先简单地罗列出各实体中的数据，并为每个实体确定一个唯一的标识符、以唯一地标识实体集中的每一个实体，标识符可以是一个属性、也可以由多个属性组合而成。

实际上这一工作我们已经完成了，拜见图8-16，其中，使用下划线标识表记标帜的即为标识符属性，HR实体（多对多关系转换而成的实体）的标识符由其所依赖的两个实体的标识符（emp_id和skill_id）组合而成。

1.第一范式测试

根据第一范式的要求，检查各实体的属性是否存在多值的情况，这里的“多值”指的是同一个属性在同一个实体上可以有几个分歧的取值，如果有则应将这样的属性从其所属的实体中删除掉，并用这些删除掉的属性创建新的实体和关系。

在我们的设计实例中，假定一个部分可以有0～多个电话号码，则Department实体中的phone属性就存在多值的情况，解决法子是，将phone属性从Department实体中删除，并创建一个单独的Phone实体（严格来说是实体集）。

此时，Department实体和Phone实体间存在一对多的关联关系——每个部分可以有0～多个电话号码，每个电话号码必须也只能隶属于某一个特定的部分。

修正后的实体-关系如图8-17所示。

图8-17数据库设计实例CDM之4

2.第二范式测试

第二范式要求各实体中所有非标识符属性都必须完全依赖于实体的标识符，如果存在非标识符属性部分依赖（而不是完全依赖）于该实体的标识符的情况，则应从当前实体中删除这些属性、并将之加入到适合的实体中，其相关原理分析及具体做法前文中已有详细讲解。

实际上，由于单个属性做为标识符的实体中不成能出现非标识符属性对标识符部分依赖的情况，我们只需检查那些由多个属性联合组成标识符的实体。

本设计实例中，只有HR实体中存在两个属性（emp_id和skill_id）组合构成标识符的情况，而其skill_level属性的确完全依赖于这二者，故本设计已符合第二范式的要求。

3.第三范式测试

第二范式要求实体中的属性间不得存在传递依赖，即所有的非标识符属性必须完全依赖于标识符、且不得依赖于实体中的其它（非标识符）属性，如果存在传递性依赖的情况，则应从当前实体中将相关属性删除，有需要的话，也可以将这些属性添加到适合的其它实体中。

经逐个分析，前述各实体的属性之间均不存在传递性依赖，故本设计已符合第三范式的要求。

8.5.4生成物理数据模型

在确定概念数据模型并完成了数据库的规范化之后，数据库设计就基本完成了，接下来要做的是生成与前述概念数据模型相对应的物理数据模型。

这个过程也称作解析关系，因为其主要工作就是将模型中的实体转换为表、并将实体间关系转换为表间的外键关系。

具体原则如下：

1.将实体转换为表

实体中的标识符转换为表的主键，实体中的属性转换为表中的字段，属性的数据类型转换/具体化为特定数据库管理系统所支持的数据类型。

2.解析关系

概念数据模型中实体间关系最终将被转换为物理数据模型中表的外键，具体可分为如下三种情况：

◆解析一对多关系

在解析一对多关系时，“一”方表中的主键字段（由其对应实体中的标识符转换得来）将成为“多”方表中的外键字段。

例如，图8-18所示的CDM中，Department实体和Phone实体间存在一对多关系——一个部分可以拥有0～多部电话，但一部电话必须也只能隶属于一个部分。

图8-18一对多关系CDM

在转换后的PDM中，Department表中的主键字段（dept_id）成为Phone表中的外键字段，具体如图8-19所示。

图8-19一对多关系PDM

◆解析一对一关系

在解析一对一关系时，可以在其中任意一方（表中）设置外键。

如果该一对一关系在一个方向是强制性的，而在另一方向是可选的，则应在可选的一方设置外键。

例如，图8-20所示的CDM中，Department实体和Employee实体间存在一对一关系——一个部分必须也只能由一名员工（部分经理）负责管理，此为强制性关系；一名员工至多可以管理一个部分，此为非强制性关系。

图8-20一对一关系CDM

在转换后的PDM中，Employee表中的主键字段（emp_id）成为Department表中的外键字段，具体如图8-21所示。

图8-21一对多关系PDM

◆解析多对多关系

对于多对多关系，应将之转换为实体，以便于在数据库中实现，否则很容易出现违背数据库规范化第二范式和第三范式的情况。

例如，图8-22所示的CDM中，Employee实体和Skill实体间存在多对多关系——每位员工均掌握一门以上的业务技能项目，此为强制性关系；每门业务技能项目可能被0～多名员工掌握（有的技能项目被为公司业务所需，但尚未招聘到掌握该技能项目的员工），此为非强制性关系。

图8-22多对多关系CDM

上述的多对多关系可转换为实体，具体如图8-23所示。

图8-23多对多关系转换为实体CDM

这实际上是将一个多对多关系转换成了两个一对多关系。

转换而得的HR实体集中的每个实体都必须在Employee、Skill实体集中各存在一个对应的实体（两个强制关系），因为员工对技能项目的掌握关系只有在与一个特定的员工和特定的技能项目实体相关联时才有意义。

换句话说，新建的HR实体既依赖于Employee实体、同时又依赖于Skill实体，后两者中的标识符组合起来则可以唯一地标识HR实体集中的一个实体，HR实体集方框两侧小圆圈和鸟爪状标识表记标帜中间的三角形就是用来标明这种依赖性的。

将多对多关系转换为实体后更适合其相关属性信息在数据库中的存储，这样可以很好地防止数据冗余。

比方，如果需要记录员工对技能项目的掌握程度，则可以在新实体HR中添加相应的属性（skill_level），确定属性后的CDM如图8-24所示。

图8-24多对多关系转换为实体CDM之二

说明，被依赖的Employee实体和Skill实体中的标识符将自动成为HR实体的联合标识符，以唯一标识每一个HR实体，由于在当前的CDM中已使用三角形标识表记标帜标明了这种依赖关系，因此不需要HR实体集方框中再显式添加emp_id和skill_id属性。

基于图8-24所示的CDM而生成的PDM如图8-25所示。

图8-25多对多关系转换为实体PDM

多对多关系转换为实体后，需要再对所得到的新实体和两个一对多关系进行规范化检查，最可取的做法是在创建概念数据模型阶段就将多对多关系转换为实体，以防止到生成物理数据模型阶段进行关系解析时的迭代规范化处理。

比方，在本设计实例中，由于在前述步调（创建概念数据模型->确定实体和关系）中已经将多对多关系转换为实体，因此这里已不存在待解析的多对多关系。

物理数据模型与特定的数据库管理系统相关，比方表中字段的数据类型会随数据库种类和版本号而存在差别，基于Oracle11g数据库，图8-17所示的概念数据模型所转换成的物理数据模型如图8-26所示，本数据库设计满足数据库规范化第三范式的要求。

图8-26数据库设计实例PDM

8.5.5验证设计结果

在实施上述设计结果之前，需要对齐进行最后的验证、以确保该设计能够满足应用开发的需要。

具体来说，就是再次检查在设计过程之初确定的各项业务活动，以确保基于当前的数据库设计可以访问和操纵业务活动所需要的全部数据。

比方，是否可以满足应用开发中聘用新员工、解雇现有员工、维护员工的个人信息等业务活动对数据的访问和操纵要求，在本设计中，答案是肯定的。

例如：

如果经验没有问题，那么就可以实施本设计结果了。

这里所谓的“实施”，具体来说就是根据先前的物理数据模型设计结果，运用目标数据库管理系统（DBMS）所支持的数据语言、客户端或服务器端工具、以及应用开发宿主语言（如Java），在目标数据库管理系统中创建各种数据库对象，编制与调试应用程序，组织数据入库，并进行调试运行。

基于Oracle11g数据库，实施本数据库设计的最终结果（图8-26所示的PDM）的对应SQL脚本文件如下：

--丢弃已创建数据表（供调试之用）

DROPTABLEhr;

DROPTABLEskill;

DROPTABLEphone;

DROPTABLEdepartmentCASCADECONSTRAINTS;

DROPTABLEemployee;

--创建部分信息表

CREATETABLEdepartment（

dept_idVARCHAR2（20）PRIMARYKEY,

mgr_idVARCHAR2（20）,

dept_nameVARCHAR2（30）,

addressVARCHAR2（50）

）;

--创建员工信息表

CREATETABLEemployee（

emp_idVARCHAR2（20）PRIMARYKEY,

emp_nameVARCHAR2（20）,

sexchar（4）,

jobVARCHAR2（20）,

mgr_idVARCHAR2（20）,

salaryNUMBER（8,2）,

birthDATE,

hiredateDATE,

dept_idVARCHAR2（20）

）;

--创建部分电话信息表

CREATETABLEphone（

phone_codeVARCHAR2（20）PRIMARYKEY,

dept_idVARCHAR2（20）

）;

--创建技能项目信息表

CREATETABLEskill（

skill_idVARCHAR2（20）PRIMARYKEY,

skill_nameVARCHAR2（30）

）;

--创建员工技能等级评定信息表

CREATETABLEhr（

emp_idVARCHAR2（20）,

skill_idVARCHAR2（20）,

skill_levelNUMBER

（2）,

CONSTRAINThr_pkPRIMARYKEY（emp_id,skill_id）

）;

--添加外键约束

ALTERTABLEhrADDCONSTRAINTShr_emp_id_fk

FOREIGNKEY（emp_id）REFERENCESemployee（emp_id）;

ALTERTABLEhrADDCONSTRAINTShr_skill_id_fk

FOREIGNKEY（skill_id）REFERENCESskill（skill_id）;

ALTERTABLEphoneADDCONSTRAINTSphone_dept_id_fk

FOREIGNKEY（dept_id）REFERENCESdepartment（dept_id）;

ALTERTABLEdepartmentADDCONSTRAINTSdepartment_mgr_id_fk

FOREIGNKEY（mgr_id）REFERENCESemployee（emp_id）;

ALTERTABLEemployeeADDCONSTRAINTShr_dept_id_fk

FOREIGNKEY（dept_id）REFERENCESdepartment（dept_id）;

ALTERTABLEemployeeADDCONSTRAINTShr_mgr_id_fk

FOREIGNKEY（mgr_id）REFERENCESemployee（emp_id）;