关系型数据库设计与分析..

关系型数据库设计笔记

1、实体关系模型(Entity-Relａtｉonshiｐ，简称ER),是目前应用最广泛

的概念设计模型。它将现实世界的信息结构统一用属性、实体以及它们之间的

．．．．．．．．．．．．

联系

．．来描述。

●实体(Ｅｎｔity)。客观存在并可相互区别的事物称为实体。实体可以是

具体的人、事、物,也可以是抽象的概念或联系。

●属性（Attriｂute)。属性为实体的某一方面特征的抽象表示。如教师实

体可由教师编号、姓名、年龄、性别、职称等属性来刻画。

●域 (Domain）。属性的取值范围称为属性的域。如：教师实体中,属性性

别的域为男和女。

●主码 (Primarｙ Key)。码也称关键字，它是能够唯一标识一个实体的属性

集。如：教师实体的主码为教师编号。

●联系 (Ｒelatｉｏnｓhｉp）。现实世界的事物总是存在着这样或那样的联

系,这种联系必然要在信息世界中得到反映。事物之间的联系可分为两类：一类是实体内部的联系,如组成实体的各属性之间的关系；另一类是实体之

间的联系，即不同实体之间的联系。

2、两个实体集之间的联系

●1:1 联系：如果对于A中的一个实体，B中至多有一个实体与其发生联系,

反之,Ｂ中的每一实体至多对应A中一个实体，则称A与Ｂ是１：1联系。

●1:n 联系:如果对于A中的每一实体，实体B中有一个以上实体与之发生联

系,反之，B中的每一实体至多只能对应于A中的一个实体，则称A与B是

１:n联系。

●ｍ:ｎ联系：如果A中至少有一实体对应于B中一个以上实体,反之，Ｂ中

也至少有一个实体对应于A中一个以上实体，则称A与B为ｍ:n联系。

3、实体关系模型的表示方法

ER图是直观表示概念模型的工具,EＲ图的基本思想就是分别用矩形框、椭圆形框和菱形框表示实体、属性和联系,使用无向边将属性与其相应的实体连接起来,并将联系分别和有关实体相连接,注明联系类型

4、设计局部ER图

［例６.１]在简单的教务管理系统中,有如下语义约束:

●一个学生可选修多门课程,一门课程可被多个学生选修。因此学生和课程之

间是多对多的联系；

●一个教师可讲授多门课程,一门课程可以由多个教师讲授。因此教师和课程

之间也是多对多的联系；

●一个系可有多个教师,一个教师只能属于一个系。因此系和教师是之间一对

多的联系，同样系和学生之间也是一对多的联系。

5、综合成初步ER图

［例6.2]以[例６.１]中教务管理系统的两个局部ＥR图为例，来说明如何消除各局部EＲ图之间的冲突，进行局部ER模型的合并,从而生成初步ER图。

首先,这两个局部ＥR图中存在着命名冲突，学生选课局部ER图中的实体“系”与教师任课局部ER图中的实体“单位”,都是指“系”,即所谓的异名同义，合并后统一改为“系”,这样属性“名称”和“单位名”即可统一为“系名”

其次,还存在着结构冲突，实体“系”和实体“课程”在两个不同应用中的属性组成不同,合并后这两个实体的属性组成为原来局部ER图中的同名实体属性的并集。

解决上述冲突后，合并两个局部ER图,生成如图6－1７所示的初步的全局ER图

6、优化成基本ER图

一个好的ＥR模式,除了能够准确、全面的反映用户需求之外，还应该达到下列要求：

●实体类型的个数应尽量少；

●实体类型所含属性个数应尽可能少；

●实体类型间的联系应无冗余;

7、优化初步ER图的方法:

实体类型的合并,指相关实体类型的合并。因为实体类型最终要转换成关系模式,涉及多个实体类型的信息要通过连接操作获得,所以，减少实体类型个数，可减少连接的开销，提高处理速度。一般把一对一联系的两个实体类型合并。

消除冗余，在这里指冗余的数据和实体之间冗余的联系。冗余的数据是指可由基本的数据导出的数据,冗余的联系是由其他的联系导出的联系。在前面消除冲突合并后得到的初步ER图中，可能存在冗余的数据或冗余的联系。冗余的存在容易破坏数据库的完整性，给数据库的维护增加困难，应该消除。

［例6.３］对[例6.２]中生成的初步ER图进行分析优化。

●在图６-1７所示的初步EＲ图中,“课程”实体中的属性“教师号”可由“讲授”

这个教师与课程之间的联系导出,而学生的平均成绩可由“选修”联系中的属性“成绩”中计算出来,所以“课程”实体中的“教师号”与“学生”

实体中的“平均成绩”均属于冗余数据。

●另外，“系”和“课程”之间的联系“开课”，可以由“系”和“教师”之间的

“属于”联系与“教师”和“课程”之间的“讲授”联系推导出来，所以“开课”属于冗余联系。

典型实例

［例6.2]New Cｅntury唱片公司决定将制作唱片的有关音乐人的信息存入数据库中。

◆每个New Centurｙ中的音乐人都有No、姓名，地址、电话号码等信息。

◆每样乐器都有乐器名（如吉他、电子合成器、长笛等），音乐的基调（如C、Ｂ-flaｔ、E－flａｔ）等信息。

◆每张唱片都有标题、出版日期、格式（如CD和ＭＣ)、唱片标识码等信息。

◆每首歌曲都有标题和作者等信息。

◆每个音乐人可以演奏多种乐器,且一种乐器可以由多个音乐人演奏。

◆每张唱片有一组歌曲,但一首歌曲只能出现在一张唱片中。

◆每首歌曲由一名或多名音乐人来完成，一名音乐人可以完成多首歌曲。

◆每个唱片只有一名制片人,一个音乐人可以制作多个唱片。

[例6.3]设计一个科研档案管理系统的ＥR图。

教师：教师编号、姓名、性别、年龄、出生日期、工作时间、职称、政治面貌、文化程度;

研究生：研究生学号、姓名、指导教师编号、指导教师姓名、专业代码、班级；项目：项目编号、项目名称、项目来源、项目级别、开始时间、结束时间;

论文：论文编号、论文题目、论文级别、发表刊物、发表时间、主办单位

专业：专业代码、专业名称、学科代码、学科名称

实体间关系：

●每位研究生都有一位教师作为导师，一个教师可以指导多名研究生(教师和研

究生之间存在一对多的关系)。

●每个项目都有多名教师和研究生参加,并有一位教师作为项目负责人（项目和

研究生之间、项目和教师之间都是多对多的关系)。

●每篇论文由一名以上教师或研究生完成,按作者顺序排列(教师和论文之间、研

究生和论文之间都是多对多的关系）。

●每位研究生只属于某一专业(研究生和专业之间是一对多的关系)。

[例6．4]下面用ER图来表示某个工厂物资管理的概念模型。

物资管理涉及的实体有：

仓库。属性有仓库号、面积、电话号码。

零件。属性有零件号、名称、规格、单价、描述。

供应商。属性有供应商号、姓名、地址、电话号码、账号。

项目。属性有项目号、预算、开工日期。

职工。属性有职工号、姓名、年龄、职称。

这些实体之间的联系如下:

(1）一个仓库可以存放多种零件，一种零件可以存放在多个仓库中,因此仓库和零件具有多对多的联系。用库存量表示某种零件在某个仓库中的数量。

（2)一个仓库有多个职工当仓库保管员，一个职工只能在一个仓库工作，因此仓库和职工之间是一对多的联系。

（3)职工之间具有领导-被领导关系。即仓库主任领导若干保管员，因此职工实体集中具有一对多的联系。

（4）供应商、项目和零件三者之间具有多对多的联系。即一个供应商可以供给若干项目多种零件，每个项目可以使用不同供应商供应的零件,每种零件可由不同供应商供给。

下面给出此工厂的物资管理Ｅ－Ｒ图。为了更清晰地表示实体及其实体之间的联系,人们常常把实体及其属性用一幅图表示，如图(a)所示;实体及其实体之间的联系如图（b)所示,完整的实体联系图如图（ｃ）所示。

根据Ｅ-R建立数据库模式的步骤

1、E-Ｒ图转换为表并进行必要的合并,本步骤可以按照机械方法完成

一个良好的E-R图，完成本步转换和合并得到的结果,已经是比较理想的数据库模式(尽管还有人工进一步优化的余地)

２、优化

本步无具体可行的机械方法,主要依靠设计人员的经验和能力

相关概念：

数据模型

数据（ｄata）是描述事物的符号记录。模型(Ｍｏｄel)是现实世界的抽象。数据模型(DatａＭodel)是数据特征的抽象，是数据库管理的教学形式框架。

数据模型所描述的内容包括三个部分:数据结构、数据操作、数据约束。?1)数据结构:数据模型中的数据结构主要描述数据的类型、内容、性质以及数据间的联系等。数据结构是数据模型的基础,数据操作和约束都建立在数据结构上。不同的数据结构具有不同的操作和约束。?2）数据操作:数据模型中数据操作主要描述在相应的数据结构上的操作类型和操作方式。?３)数据约束：数据模型中的数据约束主要描述数据结构内数据间的语法、词义联系、他们之间的制约和依存关系,以及数据动态变化的规则,以保证数据的正确、有效和相容。?数据模型按不同的应用层次分成三种类型:分别是概念数据模型、逻辑数据模型、物理数据

1、概念数据模型（Ｃoncｅptual Data Model)：简称概念模型,是面模型。?

向数据库用户的实现世界的模型，主要用来描述世界的概念化结构，它使数据库的设计人员在设计的初始阶段,摆脱计算机系统及DBMS的具体技术问题,集中精力分析数据以及数据之间的联系等,与具体的数据管理系统(Dataｂaｓe Manaｇemeｎt Systeｍ,简称DBMS)无关。概念数据模型必须换成逻辑数据模型,才能在DBMS中实现。

2、逻辑数据模型(Lｏgical Data Ｍodel）:简称数据模型,这是用户从数据库所看到的模型，是具体的DBＭS所支持的数据模型，如网状数据模型（Netｗork Da ｔａModeｌ)、层次数据模型(Hiｅrａrｃhicａｌ Data Moｄel)等等。此模型既要面向用户,又要面向系统，主要用于数据库管理系统(ＤＢMＳ）的实现。?

3、物理数据模型(Physical Data Ｍodｅｌ）:简称物理模型,是面向计算机物理表示的模型，描述了数据在储存介质上的组织结构,它不但与具体的DＢMS有关，而且还与操作系统和硬件有关。每一种逻辑数据模型在实现时都有起对应的物理数据模型。DBMS为了保证其独立性与可移植性，大部分物理数据模型的实现工作又系统自动完成，而设计者只设计索引、聚集等特殊结构。?在概念数据模型中最常用的是E-R模型、扩充的E-R模型、面向对象模型及谓词模型。在逻辑数据类型中最常用的是层次模型、网状模型、关系模型。三级模式结构：外模式、概念模式和内模式

一、概念模式（Scｈemａ）?定义：也称逻辑模式,是数据库中全体数据的逻辑结构和特征的描述,是所有用户的公共数据视图。?理解:

①一个数据库只有一个概念模式; ?②是数据库数据在逻辑级上的视图；

③数据库模式以某一种数据模型为基础;

④定义模式时不仅要定义数据的逻辑结构(如数据记录由哪些数据项构成，数据项的名字、类型、取值范围等)，而且要定义与数据有关的安全性、完整性要求，定义这些数据之间的联系。

二、外模式（Exteｒnａl Schema）

定义:也称子模式（Subschｅma)或用户模式，是数据库用户(包括应用程序员和最终用户）能够看见和使用的局部数据的逻辑结构和特征的描述,是数据库用户的数据视图,是与某一应用有关的数据的逻辑表示。?理解：

①一个数据库可以有多个外模式；

②外模式就是用户视图;

③外模式是保证数据安全性的一个有力措施。

三、内模式（Ｉｎternａl Schema)

定义：也称存储模式(Sｔoｒage Schema)，它是数据物理结构和存储方式的描述,是数据在数据库内部的表示方式（例如,记录的存储方式是顺序存储、按照B树结构存储还是按hasｈ方法存储;索引按照什么方式组织；数据是否压缩存储，是否加密；数据的存储记录结构有何规定）。?理解：

①一个数据库只有一个内模式；?②一个表可能由多个文件组成，如：数据文件、索引文件。

它是数据库管理系统(ＤBMＳ)对数据库中数据进行有效组织和管理的方法

其目的有:

①为了减少数据冗余,实现数据共享;

②为了提高存取效率，改善性能。

1、E-Ｒ到表的基本转化方法

●实体转化为表

Ｅ-R图的每个实体转化成一个表,实体的属性转化为表的属性(暂时只考虑简单、单值属性)，实体的主码转化为表的主码

●联系转化为表

每个联系转化成一个表

●联系转化成表的属性

参与联系实体的主码并集pｋ(e1)∪pk(e2)…以及联系的属性{a１,ａ２}共同构成表的属性pｋ(e１)∪pk(e2）∪…∪｛a1,ａ２…}

在联系转化成的表中,属性的非空限制:实体主码形成的属性pk（e1)∪pk(ｅ2）∪…均应not nｕｌl只有在联系转化成的表与其他表合并后，才可能允许nu ｌl

●联系转化成的表的码:

参与联系实体的主码并集pk（e1)∪pk(ｅ2)…是联系转化成的表的超码

多对一联系，上述超码去掉一个“一”端实体的主码后，是联系表的候选码多对多联系，上述超码是联系表的候选码

●实体转化成的表：

–Depｔ(dno,dｎame)

–Sｔudｅnt(snｏ，ｓname)

–Coursｅ(cno,cname)

●联系转化成的表：

–SＤ(ｓnｏ,ｄｎｏ,time) //ｄno非空

–SＣ（sｎｏ,cno,ｓｃore）

例题:请将下述E-R转化为关系模式：（注意指明各表的主码)

●实体转化成的表

－Teachｅｒ（tｎo，ｎame)

-cｌass(clasｓno,ｃｌassｎame)

-Course(cｎo，cnａｍe)

●联系转化成的表

-tc(tno，ｃno)

-tcc（claｓsno,cno,tno)

2、表的合并

●主要讨论联系转化的表与相关实体转化的表的合并问题

●按照联系类别分别讨论能否合并、如何合并

二元ｍ:1联系

二元1：1联系

二元m:n联系

多元联系

i.二元多对一联系：

–联系转化的表可以和“多端” (此例中一个院系有多个学生，学生为多）实体转化成的表进行合并

示例:

–E－Ｒ图

–转化成的表

●Ｄｅpt(dno，dｎａme）

●Sｔudent(snｏ,snamｅ）

●ＳＤ(sｎｏ,dnｏ，time) //dno非空

–表的合并

●Studeｎｔ＋SＤ→ Student(sｎo,sname,dno,timｅ)//ｄno

可以为空

ii.二元一对一联系:

–联系转化的表可以任一端实体转化成的表进行合并

–二元一对一联系不能导致相关实体转化成的表合并

示例:

–E-Ｒ图如右所示

–转化成的表

●Deｐt(dnｏ,dnaｍe)

●Presｉdent(piｄ，name)

●Manａge（dno,piｄ) //dno,pid均可作主码，假设选dno作

主码

–表的合并

●可以：Deｐt+Manage→Ｄept（ｄno，dnａme，pｉd）

●或者：Prｅｓident＋Manａgｅ→Pｒesident（pｉd,name,dno)

–不能进行下述合并:

Dept+Manaｇe+Ｐrｅsｉdｅｎt →？(不能接受的合并）

iii.二元m：n联系

–联系转化的表和实体转化的表不能进行合并

●示例：

–E-R图

–转化成的表

●Sｔudeｎｔ（ｓnｏ，ｓnaｍe）

●Course(ｃｎo,cｎａme)

●ＳC(ｓnｏ,cno,ｓcoｒe)

–无法进行表的合并

iv.多元联系

–联系转化的表和实体转化的表不能进行合并

–即便是m:n:1,其转化的表和也不能进行合并

●示例：

–E-R图(省略了属性):

–转化成的表：

●Claｓｓ（claｓsｎo,ｃlassｎame)

●Teaｃher(tno,tｎａmｅ）

●Cｏｕrｓｅ（courseno,cｏursenaｍe)

●TＣＣ(tno,classno,ｃｏurｓeno)

?//P．K.=(ｃlassnｏ,tｎo)或(cｌassno,cｏursenｏ) –无法进行表的合并

例题：教务系统概念模型如下图所示

请将E-R图转化为表并进行必要的合并：

将Ｅ-R图转化为表：

–实体转化成表

d(dno,dnamｅ）

c(cｎo,cnaｍe,prｏｐerty)

s(ｓｎo,snaｍe,age,sｅx)

t(tno,ｔnaｍe,ａgｅ,sex）

–联系转化为表

sｄ（sno,ｄnｏ)

td(ｔｎo,dno）

sc(snｏ,cno,score)

tc(tｎo，ｃｎo,ｔimｅ)

●表的合并

–s+ｓd→ｓ(sno,ｓnamｅ,ａge,sex，ｄｎｏ)

–t +ｔd →ｔ（tnｏ, tｎame,ａｇe,ｓex,dｎo)

●合并表后的关系模式

–d(dno,ｄｎame)

–c(ｃno，cnａmｅ,propｅrtｙ)

–s(ｓnｏ,sｎａｍe,aｇe,sex,ｄnｏ)

–t(tｎo,ｔnａｍｅ,age，sex,dno)

–sc（sｎo,ｃnｏ，ｓcｏｒe）

–tｃ(ｔｎo，cno)

●关系模式图如图所示

●教务系统数据概念模型与逻辑模型对比

概念模型主要用E-Ｒ图刻画,用于需求分析

逻辑模型主要由关系模式图刻画，用于模式设计

●请将E-R图转化为表并进行必要的合并:

–假设每个实体都有属性ｉd和naｍe

–假设供应联系有属性quanｔｉty,其它联系无属性

●Ｅ-R图转化为表

–实体转化成表

?prｏｊeｃｔ(piｄ，ｐnamｅ）

emｐloyee（eｉd,ename）

supｐlｉeｒ（ｓiｄ,sｎamｅ)

?cｏmｐｏneｎt（cid,ｃnａme)

?ｗａreｈouse（wiｄ，ｗnａmｅ)?

–联系转化为表

paｒticｉpaｔe（piｄ,eｉｄ)

ｌeaｄ（eid,leid) //leid非空

?suｐpｌy（sid,ｐid,cid,quanｔiｔy)

produce(sid，cｉd)

store（cid,wid)

?ｍａnａgｅr（eid，ｗiｄ)

●表的合并

employｅe+lｅad→ｅｍplｏyee（eｉｄ,enamｅ，ｌeid)//ｌeid可以为空

将如下Ｅ-Ｒ图转化为表并进行必要的合并，请给出：– 1.结果关系模式

– 2.关系模式图

Ｅ-Ｒ图其它要素转化为表的方法

–复杂属性处理

–弱实体处理

–继承转化为表

–聚集转化为表

●多值属性

–每个多值属性转化为一个表

–表主码:

实体主码+多值属性分辨符

–例如：Ｓ-ｔelｎｏ(sｎo,tno)

●复合属性

–只保留叶节点属性

●派生属性

–一般表模式中不保留派生属性

–S(sｎo,sname,bｉrthdａy,cｉｔy,strｅeｔ)

–如果考虑使用频率、查询效率等因素,可以保留派生属性，尽管本质上派生属性是表的冗余属性

●示例，学生实体转化为表：

–所有单值属性转化为一个表

●Ｓ(sｎo，ｓnａme,birthday,cｉty,street）

–每个多值属性转化为一个表

●S-ｔelno(ｓno,tno)

●S-relａｔｉve(sno,pid，ｒｅｌａtiｏn,name)

●思考：

–S-relaｔive中，ｐiｄ属性是否可以单独构成主码?

–不同多值属性转化的表可以合并吗？

弱实体转化为表

–弱实体象普通实体一样向表转化,只是在弱实体转化的表中,增加属主实体的主码作为表属性

–弱实体转化成表的主码：

●属主实体的主码+弱实体的分辨符

–标识性联系不转化成表，不作处理

●示例:

–请将如下所示银行帐户E-Ｒ图转化为表

–实体转化成表

–aｃc（aｃcno，acｃｎａmｅ)

–emp(eｎo,eｎａme）

–弱实体转化成表

–tｒａns(acｃnｏ,ｌineｎｏ，datｅ，dealnｕｍ）

–rual(accｎｏ,date,acｃruaｌ)

–标识性联系不转化成表

–联系转化成表

–tr(ａccno，liｎeｎｏ，dａtｅ）

●te(accno,ｌinｅno，eｎｏ)

●表合并

–tｒａns＋ｔr+te

?=trans(ａccnｏ,ｌinｅno，traｎsdate,dealnum，rualｄate,ｅnｏ)

●练习:

–对上述银行账户,如果在E-R中不使用弱实体，而是通过给交易记录、利息记录增加标识属性是成为强实体,试给出相应Ｅ－R图

–试将上述E-Ｒ图转化为表并进行必要的合并

–体会、比较两种E-R图对应概念模型及逻辑模型的差异，你更喜欢哪一种?