关系数据库设计

关系数据库设计第一页，共五十七页，2022年，8月28日人们在总结信息资源开发、管理和服务的各种手段时，认为最有效的是数据库技术（DBT）。DBT的应用已越来越广泛，从小型的单项事务处理系统到大型的IS大都采用先进的DBT来保持系统数据的整体性、完整性和共享性。目前，一个国家的DB建设规模（指DB的个数、种类）、DB信息量的大小和使用频度已成为衡量这个国家信息化程度的重要标志之一。第二页，共五十七页，2022年，8月28日

DB设计是研制DB及其应用系统的技术，是DB在应用领域中主要的研究课题。

DB设计是指：对于一个给定的应用环境，构造最优的DB模式，建立DB及其应用系统，使之能有效地存储数据，满足各种用户的应用需求（信息和处理要求）。

DB设计通常是在一个通用的DBMS支持下进行的，即利用现成的DBMS为基础构建。在DB领域内，常把使用DB的各类系统统称为“数据库应用系统（DBAS）”。第三页，共五十七页，2022年，8月28日按规范设计的方法，可将DB设计分为六个阶段：5.1.1需求分析阶段5.1.2概念模型设计阶段5.1.3逻辑模型设计阶段5.1.4数据库物理设计阶段5.1.5数据库实施阶段5.1.6数据库运行和维护阶段5.1数据库设计的基本步骤第四页，共五十七页，2022年，8月28日不满意不满意概念设计阶段逻辑设计阶段物理设计阶段数据库实施阶段数据库运行、维护阶段应用要求、DBMS详细特征应用需求（数据、处理）转换规则、DBMS功能、优化方法需求收集和分析设计概念结构设计逻辑结构数据模型优化设计物理结构评价设计，性能预测物理实现试验性运行使用、维护数据库需求分析阶段图5.1数据库设计步骤第五页，共五十七页，2022年，8月28日说明：（1）这个设计步骤是从DBAS设计和开发的全过程来考察DB设计的问题，因此，它既是DB也是DBAS的设计过程。（2）在设计过程中努力将DB设计和IS其它成分（模块结构设计、代码设计、处理流程设计等）的设计紧密结合，把数据和业务处理的需求收集、分析、抽象、设计、实现在各个阶段同时进行、相互参照、相互补充，以完善两方面的设计。第六页，共五十七页，2022年，8月28日

进行数据库设计首先必须准确了解与分析用户需求，包括数据与处理需求。需求分析是整个设计过程的基础，是最困难、最耗时的一步。作为“地基”的需求分析是否做得充分与准确，决定了在其上构建“数据库大厦”的速度与质量。需求分析做得不好，可能会导致整个数据库重新设计。5.1.1需求分析阶段第七页，共五十七页，2022年，8月28日该阶段收集到的基础数据和一组数据流图（DataFlowDiagram，简写为DFD），是下一步设计概念结构的基础。从DB设计的角度考虑，需求分析的目标是：对现实世界要处理的对象（组织、部门、企业等）进行详细调查，在了解原系统的概况、确定新系统功能的过程中，收集支持系统目标的基础数据及其处理。调查的重点是“数据”和“处理”，具体做法：1.了解组织结构情况。调查该组织由哪些部门组成，各部门的职责是什么，为分析信息流程作准备。第八页，共五十七页，2022年，8月28日2.了解各部门的业务活动情况。调查各部门输入和使用什么数据、如何加工处理这些数据、输出什么信息、输出到什么部门、输出结果的格式是什么等。3.确定新系统的边界。确定哪些功能由计算机完成或将来准备让计算机完成，哪些活动由人工完成。由计算机完成的功能就是新系统应该实现的功能。第九页，共五十七页，2022年，8月28日

在概念设计阶段，设计人员仅从用户角度看待数据及其处理要求和约束，产生一个反映用户观点的概念模型，也称为“组织模式”。概念结构独立于数据库逻辑结构，独立于支持DB的DBMS，其特点：（1）能充分反映现实世界，包括实体及实体间的联系，能满足用户对数据处理的要求，是现实世界的一个真实模型；（2）易于理解，从而可以和不熟悉计算机技术的用户交换意见；（3）易于更动，当现实世界改变时容易修改和扩充；（4）易于向关系模型转换。描述概念模型的有力工具是E-R模型。5.1.2概念模型设计阶段第十页，共五十七页，2022年，8月28日

逻辑模型设计阶段的任务是将概念模型设计阶段得到的基本E-R图，转换为与选用的DBMS产品所支持的数据模型相符合的逻辑结构。此设计过程分三步进行，如下图所示：5.1.3逻辑模型设计阶段概念结构（基本E-R图）一般数据模型（关系模型）优化的DM特定的DBMS支持下的数据模型转换规则DBMS的特点和限制优化方法第十一页，共五十七页，2022年，8月28日分析：（1）首先将概念结构向一般的关系模型转换；（2）然后向特定的DBMS支持下的数据模型转换；（3）最后进行模型的优化。第十二页，共五十七页，2022年，8月28日对于一个给定的逻辑模型选取一个最适合应用环境的物理结构的过程，称为DB物理设计。

DB的物理结构：主要指数据库在物理设备上的存储结构和存取方法，它完全依赖于给定的计算机系统。物理设计的内容包括：（1）确定数据的存储结构。从DBMS所提供的存储结构中选取合适的加以实现。目前使用的基本上均为关系型DBMS，所有数据均以“DBF”为存储结构—即一个关系（若干记录）存储为一个文件。5.1.4数据库物理设计阶段第十三页，共五十七页，2022年，8月28日确定存储结构应考虑三个因素：存取时间、存储空间利用率、维护代价。有时几个因素之间会产生矛盾，设计者应根据实际情况对这些因素进行权衡。（2）存取路径的选择和调整。DB必须支持多个用户的多种应用，因而必须提供对DB的多个存取入口，即对同一数据存储要提供多条存取路径。（例，有时必须同时提供主存取路径及辅存取路径，前者用于主键检索，后者用于辅助键-非主键检索。）（3）确定数据存放位置。一般应将数据的易变部分和稳定部分分开、经常存取和不经常存取部分分开存放。经常存取或存取时间要求高的记录应“聚簇（cluster）”在一起，存于高速存储设备上（如硬盘），存取频率小或存取时间要求低的存于低速存储设备上（如软盘、光盘等）。第十四页，共五十七页，2022年，8月28日

根据逻辑设计和物理设计的结果，在计算机系统上建立起实际数据库结构、装入数据、测试和试运行的过程称为数据库的实施阶段。（相应与管理信息系统设计的编码、调试阶段。）实施阶段主要有三项工作。（1）建立实际数据库结构。对描述逻辑设计和物理设计结果的程序即“源模式”，经DBMS编译成目标模式并执行后，便建立了实际的数据库结构。（2）装入试验数据对应用程序进行调试。试验数据可以是实际数据，也可由手工生成或用随机数发生器生成。应使测试数据尽可能覆盖现实世界的各种情况。（3）装入实际数据，进入试运行状态。测量系统的性能指标，是否符合设计目标。如果不符，则返回到前面，修改数据库的物理模型设计甚至逻辑模型设计。5.1.5数据库实施阶段第十五页，共五十七页，2022年，8月28日

数据库系统正式运行，标志着数据库设计与应用开发工作的结束和维护阶段的开始。运行维护阶段的主要任务有三项：（1）维护数据库的安全性与完整性：检查系统安全性是否受到侵犯，及时调整授权和密码，实施系统转储与备份，发生故障后及时恢复。（2）监测并改善数据库运行性能：对数据库的存储空间状况及响应时间进行分析评价，结合用户反应确定改进措施。（3）根据用户要求对数据库现有功能进行扩充。5.1.6数据库运行和维护阶段第十六页，共五十七页，2022年，8月28日

目前使用的RDB设计方法均称为“规范设计法”，主要有：新奥尔良法、的五阶段法、基于E-R模型的数据库设计方法、基于3NF的设计方法、用户视图法等。本节主要介绍：5.2.1基于E-R模型的数据库设计方法5.2.2用户视图法5.2关系数据库设计方法第十七页，共五十七页，2022年，8月28日

E-R模型主要支持概念设计，是对现实世界的一种抽象。所谓抽象：是对实际的人、物、事和概念的人为处理；它抽取人们关心的共同特性，忽略非本质的细节，并把这些特性用各种概念精确地加以描述。这些概念组成了某种模型。1．E-R模型的设计步骤

（1）设计局部E-R模式；（2）将各个局部E-R模式，综合成全局E-R模式；（3）全局E-R模式的优化。5.2.1基于E-R模型的数据库设计方法第十八页，共五十七页，2022年，8月28日

设计局部E-R模式，其实质是将大系统进行分解，使其成为逻辑功能相对独立的一些局部问题。先分别对每个局部模式进行设计，建立各局部的E-R模式，然后以各局部的E-R模式为基础进行集成。设计E-R模式可以采用三种不同的次序进行设计：

①自顶向下：首先从抽象级别高、普遍性强的类开始，然后逐步细分。例如：物资管理中，物资是最高级别的抽象，它可分为五金类、燃料类、钢材类、劳保类等。

（1）设计局部E-R模式第十九页，共五十七页，2022年，8月28日②由底向上：首先从具体对象开始，逐步抽象形成类。以物资为例，先查看仓库有哪些物资，再分析每种具体的物资属于哪个门类。③由内向外：首先从最中心的对象开始,逐步扩展到与它相关的其他对象。以物资管理为例,先从最中心的管理对象“物资”开始，逐步扩展到存放物资的仓库，仓库的管理者，物资的使用者，物资的采购入库，库存情况等。第二十页，共五十七页，2022年，8月28日应注意区分实体与属性：在实体与属性间并不存在形式上可以截然划分的界限，而是根据需要选定并进行区分的，也即实体与属性是相对的，常常是现实世界（人为主观）对它们已有了大体的自然划分：实体是E-R模式中的基本单位，是若干属性有意义的聚合。例1，物资。可设一个属性为物资类别，则五金类、燃料类、钢材类、劳保类均为属性值，而不按实体设计；如果每种类别的物资需进行不同的描述，则应将其作为实体。例2，简历。在学生信息表中可设简历为一个属性，而不按实体设计；如果每个学生的简历进行详细的描述，则应将其作为实体。第二十一页，共五十七页，2022年，8月28日实体、属性划分准则：①作为“属性”，不能再具有需要描述的性质（即属性不可再细分）；②“属性”不能与其它实体具有联系，即E-R图中的联系是实体之间的联系而不是属性之间的联系。总原则：能够作为属性的，尽量作为属性对待，目的在于简化E-R图的处置。第二十二页，共五十七页，2022年，8月28日例，销售管理子系统局部E-R图设计。开发小组的成员经过调查研究、信息流程分析和数据收集，明确该子系统主要功能：（1）接收定单：处理客户和销售员送来的定单；（2）处理定单：工厂根据定单安排生产；（3）交货同时开发票；（4）支付过帐：收到客户付款后，根据发票存根和客户信贷情况进行应收款处理。第二十三页，共五十七页，2022年，8月28日对应的局部E-R图为：注：一个客户对应每一份定单有一笔应收帐，所以，当一个客户有多份定单就有多笔应收帐。客户定单应收帐产品订货排产支付11NMNN第二十四页，共五十七页，2022年，8月28日实体及其属性定义：（1）客户（客户号，客户名，地址，电话，信誉状况，帐号，帐户余额，…）（2）应收帐款（客户号，定单号，发票号，应收金额，支付日期，支付金额，…）（3）产品（产品号，产品名，规格，单价，…

）（4）定单（定单号，若干定单头信息，定单细则，…

）。

第二十五页，共五十七页，2022年，8月28日按照“实体、属性划分原则”，“定单细则”不应该作为定单的属性处理，而上升为实体。一张定单可以定购若干种产品，所以定单与定单细则两个实体之间为1:N的联系。定单可拆分为两个实体来描述：①定单（定单号，客户号，定货项数，定货日期，交货日期，…）②定单细则（定单号，细则号，产品号，定货数量，单价，…

）第二十六页，共五十七页，2022年，8月28日则局部E-R图应修改为：注：一份定单中可能定购了多种产品，但一个定单细则中只包含一种产品的信息。排产支付1N客户定单应收帐产品订货1NN1定单细则组成1N第二十七页，共五十七页，2022年，8月28日

（2）将各个局部E-R模式，综合成全局E-R模式①视图集成方式有两种：

·

多个局部E-R图一次性集成；

·

逐步集成：用累加的方式一次集成两个局部E-R图。②每次集成的步骤：

·

合并，解决各局部E-R图之间的冲突问题，生成初步E-R图；

·

修改和重构，消除不必要的冗余，生成基本E-R图。第二十八页，共五十七页，2022年，8月28日合并过程主要解决各E-R图之间的冲突，其冲突主要有三类：属性冲突、命名冲突和结构冲突。

①属性冲突：即属性值的类型、取值范围或取值集合不同。例如：有的E-R图中将日期作为日期型，有的定义为字符型。属性冲突问题可通过统一规范的工程化管理来解决。②命名冲突：不同意义的对象在不同的局部应用中具有相同的名字（同名异义），或同一意义的对象在不同的局部应用中具有不同的名字（异名同义）。③结构冲突：同一对象在不同应用中具有不同的抽象。在某一局部应用中被当作实体，而在另一局部应用中则当作属性；或同一实体在不同局部E-R图中所包含的属性个数不同。造成此问题的原因是各局部应用所关心的侧重点不同，解决的办法是取各分E-R图中实体属性的并集。第二十九页，共五十七页，2022年，8月28日（3）全局E-R模式的优化

三原则：（1）进行相关实体类型的合并，以减少实体类型的个数；（2）尽可能消除实体中的冗余属性；（3）尽可能消除冗余的联系类型。

第三十页，共五十七页，2022年，8月28日

E-R图向关系模型转换，要解决的问题是如何将实体和实体间的联系转换为关系模式，以及如何确定这些关系模式的属性和码。关系模型的逻辑结构是一组关系模式的集合。E-R图则由实体、实体的属性和实体之间的联系三个要素组成。所以将E-R图转换为关系模型实际上就是将实体、实体的属性和实体之间的联系转换为关系模式，这种转换一般遵循如下原则：

(1)一个实体型转换为一个关系模式。实体的属性就是关系的属性，实体的码就是关系的码。

2．E-R图向关系模型的转换第三十一页，共五十七页，2022年，8月28日

（2）一个1:1联系可以转换为一个独立的关系模式，也可以与任意一端对应的关系模式合并。如果转换为一个独立的关系模式，则与该联系相连的各实体的码以及联系本身的属性均转换为关系的属性，每个实体的码均是该关系的候选码。如果与某一端实体对应的关系模式合并，则需要在该关系模式的属性中加入另一个关系模式的码和联系本身的属性。

（3）一个1:N联系可以转换为一个独立的关系模式，也可以与N端对应的关系模式合并。如果转换为一个独立的关系模式，则与该联系相连的各实体的码以及联系本身的属性均转换为关系的属性，而关系的码为N端实体的码。如果与N端实体对应的关系模式合并，则需要在该关系模式的属性中加入1端关系模式的码和联系本身的属性。第三十二页，共五十七页，2022年，8月28日（4）一个M:N联系转换为一个关系模式，与该联系相连的各实体的码以及联系本身的属性均转换为关系的属性，而关系的码为各实体码的组合。（5）具有相同码的关系模式可合并。形成了一般的数据模型后，下一步就是向特定的RDBMS的模型转换。设计人员必须熟悉所用RDBMS的功能与限制。这一步是依赖于机器的，不能给出一个普遍的规则，但对于关系模型来说，这种转换通常都比较简单，不会有太多的困难。第三十三页，共五十七页，2022年，8月28日例，根据调查、分析构建了如下E-R图，要求将之转换为一组对应的关系模式。其对应的一组关系为：（1）实体转换结果：领导M供应商供应物资项目NP参加部门属于职工MN1N11天数供应量第三十四页，共五十七页，2022年，8月28日①供应商（供应商编号，供应商名称，电话，帐号…）②物资（物资编号，物资名称，型号规格，计量单位，价格…）③项目（项目编号，预算，开工日期，预计完成时间…）④职工（职工编号，姓名，出生日期，职称…）⑤部门（部门编号，部门名称…）第三十五页，共五十七页，2022年，8月28日（2）联系转换结果：①供应（供应商编号，项目编号，物资编号，供应量）②参加（职工编号，项目编号，参加天数）③领导，该联系并入“部门”实体，则“部门”的关系模式修改为：部门（部门编号，部门名称，领导者职工编号…）④属于，该联系并入“职工”实体，则“职工”的关系模式修改为：职工（职工编号，姓名，部门编号，出生日期，职称…）第三十六页，共五十七页，2022年，8月28日

用户视图法就是将需要设计的数据库应用系统,从不同的用户角度分析数据需求,这些单独的需求称为用户视图。然后再将所有的用户视图合成一个复杂的数据库系统,其目的是化繁为简,分步设计。用户视图法需要经过以下步骤：１．分析与该系统有关的用户种类；

2．分析各类用户分别用到的视图（将用户视图表示为一些表的集合）：（１）判断用户视图所包含的实体，并为每个实体建立一个表。

5.2.2用户视图法第三十七页，共五十七页，2022年，8月28日

（２）判断每个表的关键字，关键字可能是一个或多个属性组合。（３）判断每种实体的性质，根据用户需求来寻找所需实体的其他属性。（４）判断实体之间的关系，即１:１、１:Ｎ、M:N。

3．表的规范化将第二步所列出的表规范化为三范式（3NF）。第三十八页，共五十七页，2022年，8月28日

４．数据库图示化表示方法，用数据结构图表示表集合中的约束与联系。５．汇总各用户视图的设计结果，将所有的用户视图综合成一个复杂的数据库系统（注意合并过程中的冲突解决）。第三十九页，共五十七页，2022年，8月28日5.3.1将用户视图表示为一些表的集合5.3.2判断实体之间的关系5.3.3列出所有表的关键字5.3.4数据库图示化表示方法5.3.5汇总各用户视图的设计结果(选择仓库管理进行设计)5.3数据库设计举例第四十页，共五十七页，2022年，8月28日１．仓库保管员用户视图仓库保管员需要随时掌握仓库的入库情况、出库情况、库存情况。仓库保管员的需求就是物资基本情况表（wzbmb）、物资入库表（wzrkb）、物资出库表（wzlkb）、物资库存表(wzkcb)、用料单位表（dwbmb）等的集合。（1）判断用户视图所包含的实体，并为每个实体建立一个表。

5.3.1将用户视图表示为一些表的集合第四十一页，共五十七页，2022年，8月28日

（2）判断每个表的关键字。其他属性在后面的步骤中再填入。主关键字是一个唯一的标识符，通过它来区分不同的对象。物资基本情况表（wzbmb）的主关键字是物资编码，物资入库表（wzrkb）的主关键字是日期与入库单编号，物资出库表（wzlkb）的主关键字是日期与出库单编号，物资库存表(wzkcb)的主关键字是物资编码和日期，单位编码表（dwbmb）的主关键字是单位编码，现将主关键字加入对应的表中。

wzbmb(wzbm，wzrkb（rq,rkh，

wzlkb（rq，lkh，dwbmb(dwbm，

wzkcb(wzbm，rq，

第四十二页，共五十七页，2022年，8月28日

（3）判断每种实体的性质。根据用户需求来确定所需实体的其他属性。

wzbmb(wzbm，wzmc，xhgg，jldw，price)。

wzrkb（rkh，rq，wzbm，cgl，sssl，price，ysr）。

wzlkb（lkh，rq，dwbm，wzbm，qls，sfs，price）。

wzkcb(wzbm，rq，price，wzkcl)。

dwbmb(dwbm，dwmc)。第四十三页，共五十七页，2022年，8月28日

2．仓库主管用户视图因为每个单位有多个仓库，每个仓库有多种物资，仓库主管需要随时了解每个仓库的库存量、资金总额，每个仓库存放哪些物资。既要保证生产，又不积压物资，以保证流动资金的周转。仓库主管的需求包括：仓库情况、库存情况、各单位用料情况等。（1）判断用户视图所包含的实体，并为每个实体建立一个表。有仓库情况表（wzckbmb）、库存情况表（wzkcb）、各单位用料情况表（wzhyb）。

第四十四页，共五十七页，2022年，8月28日

（2）判断每个表的关键字，其他属性在后面的步骤中再填入。仓库情况表（wzckbmb）的主关键字是仓库编码(wzckbm)，库存情况表（wzkcb）的主关键字是物资编码和日期，各单位用料情况表(wzhyb)的主关键字是单位编码和物资编码，现将主关键字加入对应的表中。

wzckbmb(wzckbm，

wzkcb(wzbm，rq，

wzhyb(dwbm，wzbm，第四十五页，共五十七页，2022年，8月28日

（3）判断每种实体的性质。仓库情况表（wzckbmb）除主关键字仓库编码(wzckbm)外，还包括仓库名称（wzckmc）。库存情况表（wzkcb）除主关键字物资编码与日期（wzbm，rq）外，还应包括价格（price），库存量（wzkcl）。各单位用料情况表(wzhyb)除主关键字单位编码（dwbm）和物资编码（wzbm）外，还应包括汇总期（rq），总金额（zje）。

wzckbmb(wzckbm，wzckmc)wzkcb(wzbm，rq，price，wzkcl)wzhyb(dwbm，wzbm，rq，zje)第四十六页，共五十七页，2022年，8月28日

3．物资用户的用户视图每个使用物资的用户需要随时了解哪个仓库存放哪些物资，库存量是多少，能否满足生产需要，即需要掌握仓库库存情况、本单位用料情况等。（1）判断用户视图所包含的实体，并为每个实体建立一个表。有库存情况表（wzkcb）（该表在前面已设计）、本单位用料情况表（dwylhyb）。第四十七页，共五十七页，2022年，8月28日（2）判断每个表的关键字，其他属性在后面的步骤中再填入。本单位用料情况表(dwylhyb)的主关键字是单位编码和物资编码，现将主关键字加入对应的表中。

dwylhyb（dwbm，wzbm，

（3）判断每种实体的性质。本单位用料情况表(dwylhyb)除主关键字单位编码（dwbm）和物资编码（wzbm）外，还应包括汇总期（rq），总金额（zje）：dwylhyb（dwbm，wzbm，rq，zje）。第四十八页，共五十七页，2022年，8月28日

（1）１：Ｎ的联系，应将“1”表的主关键字加入到“N”表中作为外部关键字。在上述用户视图中，仓库情况表wzckbmb(wzckbm，wzckmc)与库存情况表wzkcb(wzbm，rq，price，wzkcl)就是１：Ｎ的联系。因为每个仓库可以存放多种物资，所以应将仓库情况表wzckbmb表中的wzckbm加入到库存情况表wzkcb表中作外部关键字。

wzkcb（wzbm，rq，price，wzkcl，wzckbm）5.3.2判断实体之间的关系第四十九页，共五十七页，2022年，8月28日

（2）M：N的联系，应建立一个新表，新表的关键字是原始表中多个关键字的组合。在上述用户视图中，用料单位与物资是M：N的联系，即每个用料单位可以领用仓库中的任何一种物资，反之每一种物资可被任一单位领用。物资出库表（wzlkb）就是用料单位与物资基本情况表（wzbmb）之间加入的表，如果出库单是一单多料制即一张领料单填写多种物资，物资出库表（wzlkb）的关键字就必须包括出库日期（领料日期）和物资编码。第五十页，共五十七页，2022年，8月28日

（1）主关键字：所有表的关键字均在前面列出。但是要根据实际需求，进行调整，如果各个仓库允许存放相同物资的话，那么库存情况表wzkcb中的（wzckbm）也必须包含在主关键字组合中。同样，wzckbm也应包含在物资入库表wzrkb和物资出库表wzlkb的主关键字组合中。（2）可选关键字：可选关键字可以作为主关键字的属性或者属性的组合，但它不是主关键字。可选关键字并不常见，在上述表中不存在。（3）第二关键字：第二关键字是指与检索数据紧密相关的属性。如果系统中有第二关键字则可在这一步明确。（4）外部关键字：第2章已指出，它是指一个表的属性或属性的集合，必须与另一个表某行的主关键字相匹配。5.3.3列出所有表的关键字第五十一页，共五十七页，2022年，8月28日5.3.4