简单数据库设计实例Word文件下载.docx
《简单数据库设计实例Word文件下载.docx》由会员分享，可在线阅读，更多相关《简单数据库设计实例Word文件下载.docx（12页珍藏版）》请在冰豆网上搜索。
要确定本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）,
salaryNUMBER（8,2）,
birthDATE,
hiredateDATE,
dept_idVARCHAR2（20）
--创建部门电话信息表
CREATETABLEphone（
phone_codeVARCHAR2（20）PRIMARYKEY,
--创建技能项目信息表
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
ALTERTABLEemployeeADDCONSTRAINTShr_mgr_id_fk
升级会员 