简单数据库设计实例样本.docx

简单数据库设计实例样本.docx

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

简单数据库设计实例样本

数据库设计实例

数据库设计是数据库应用系统设计一种构成某些，其核心是针对于特定应用环境，设计合理数据模型，创立数据库及其应用系统，使之可以有效地存储和解决数据，以满足顾客应用需求。

从实用角度出发，数据库设计可分为如下几种环节：

第一步：

创立概念数据模型

◆拟定实体和关系

◆拟定属性

◆规范化数据

第二步：

生成物理数据模型

第三步：

验证设计

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

假定咱们要开发一种小型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）；