关系型数据库设计与分析

1、关系型数据库设计笔记1、 实体关系模型(Entity-Relationship，简称ER)，是目前应用最广泛的概念设计模型。它将现实世界的信息结构统一用属性、实体以及它们之间的联系来描述。l 实体 (Entity)。客观存在并可相互区别的事物称为实体。实体可以是具体的人、事、物，也可以是抽象的概念或联系。l 属性 (Attribute)。属性为实体的某一方面特征的抽象表示。如教师实体可由教师编号、姓名、年龄、性别、职称等属性来刻画。 l 域 (Domain)。属性的取值范围称为属性的域。 如：教师实体中，属性性别的域为男和女。l 主码 (Primary Key)。码也称关键字，它是能够唯一标识

2、一个实体的属性集。如：教师实体的主码为教师编号。 l 联系 (Relationship)。现实世界的事物总是存在着这样或那样的联系，这种联系必然要在信息世界中得到反映。事物之间的联系可分为两类：一类是实体内部的联系，如组成实体的各属性之间的关系；另一类是实体之间的联系，即不同实体之间的联系。2、 两个实体集之间的联系l 1:1 联系：如果对于A中的一个实体，B中至多有一个实体与其发生联系，反之，B中的每一实体至多对应A中一个实体，则称A与B是1:1联系。 l 1:n 联系：如果对于A中的每一实体，实体B中有一个以上实体与之发生联系，反之，B中的每一实体至多只能对应于A中的一个实体，则称A与B是

3、1:n联系。 l m:n 联系：如果A中至少有一实体对应于B中一个以上实体，反之，B中也至少有一个实体对应于A中一个以上实体，则称A与B为m:n联系。 图（a）11联系 （b）1n联系 （c）mn联系 1 实体型A 实体型A 实体型A 联系名 联系名 联系名 实体型B 实体型B 实体型B 1 1 n n m 6.2 两个实体型之间的三类联系 3、 实体关系模型的表示方法 ER图是直观表示概念模型的工具，ER图的基本思想就是分别用矩形框、椭圆形框和菱形框表示实体、属性和联系，使用无向边将属性与其相应的实体连接起来，并将联系分别和有关实体相连接，注明联系类型4、 设计局部ER图 例6.1 在简单的

4、教务管理系统中，有如下语义约束：l 一个学生可选修多门课程，一门课程可被多个学生选修。因此学生和课程之间是多对多的联系； l 一个教师可讲授多门课程，一门课程可以由多个教师讲授。因此教师和课程之间也是多对多的联系；l 一个系可有多个教师，一个教师只能属于一个系。因此系和教师是之间一对多的联系，同样系和学生之间也是一对多的联系。5、 综合成初步ER图例6.2 以例6.1中教务管理系统的两个局部ER图为例，来说明如何消除各局部ER图之间的冲突，进行局部ER模型的合并，从而生成初步ER图。首先，这两个局部ER图中存在着命名冲突，学生选课局部ER图中的实体“系”与教师任课局部ER图中的实体“单位”，都

5、是指“系”，即所谓的异名同义，合并后统一改为“系”，这样属性“名称”和“单位名”即可统一为“系名”其次，还存在着结构冲突，实体“系”和实体“课程” 在两个不同应用中的属性组成不同，合并后这两个实体的属性组成为原来局部ER图中的同名实体属性的并集。解决上述冲突后，合并两个局部ER图，生成如图6-17所示的初步的全局ER图6、 优化成基本ER图一个好的ER模式，除了能够准确、全面的反映用户需求之外，还应该达到下列要求：l 实体类型的个数应尽量少；l 实体类型所含属性个数应尽可能少；l 实体类型间的联系应无冗余；7、 优化初步ER图的方法：实体类型的合并,指相关实体类型的合并。因为实体类型最终要转换

6、成关系模式，涉及多个实体类型的信息要通过连接操作获得，所以，减少实体类型个数，可减少连接的开销，提高处理速度。一般把一对一联系的两个实体类型合并。消除冗余,在这里指冗余的数据和实体之间冗余的联系。冗余的数据是指可由基本的数据导出的数据，冗余的联系是由其他的联系导出的联系。在前面消除冲突合并后得到的初步ER图中，可能存在冗余的数据或冗余的联系。冗余的存在容易破坏数据库的完整性，给数据库的维护增加困难，应该消除。例6.3 对例6.2中生成的初步ER图进行分析优化。l 在图6-17所示的初步ER图中，“课程”实体中的属性“教师号”可由“讲授”这个教师与课程之间的联系导出，而学生的平均成绩可由“选修”

7、联系中的属性“成绩”中计算出来，所以“课程”实体中的 “教师号” 与“学生”实体中的“平均成绩”均属于冗余数据。 l 另外，“系”和“课程”之间的联系“开课”，可以由“系”和“教师”之间的“属于”联系与“教师”和“课程”之间的“讲授”联系推导出来，所以“开课”属于冗余联系。典型实例例6.2 New Century唱片公司决定将制作唱片的有关音乐人的信息存入数据库中。 每个New Century中的音乐人都有No、姓名，地址、电话号码等信息。 每样乐器都有乐器名（如吉他、电子合成器、长笛等），音乐的基调（如C、B-flat、E-flat）等信息。 每张唱片都有标题、出版日期、格式（如CD和MC）

8、、唱片标识码等信息。 每首歌曲都有标题和作者等信息。   每个音乐人可以演奏多种乐器，且一种乐器可以由多个音乐人演奏。 每张唱片有一组歌曲，但一首歌曲只能出现在一张唱片中。 每首歌曲由一名或多名音乐人来完成，一名音乐人可以完成多首歌曲。 每个唱片只有一名制片人，一个音乐人可以制作多个唱片。 例6.3设计一个科研档案管理系统的ER图。教师：教师编号、姓名、性别、年龄、出生日期、工作时间、职称、政治面貌、文化程度；研究生：研究生学号、姓名、指导教师编号、指导教师姓名、专业代码、班级； 项目：项目编号、项目名称、项目来源、项目级别、开始时间、结束时间；论文：论文编号、论文题目、论文级别、发

9、表刊物、发表时间、主办单位专业：专业代码、专业名称、学科代码、学科名称实体间关系：l 每位研究生都有一位教师作为导师，一个教师可以指导多名研究生（教师和研究生之间存在一对多的关系）。l 每个项目都有多名教师和研究生参加，并有一位教师作为项目负责人（项目和研究生之间、项目和教师之间都是多对多的关系）。l 每篇论文由一名以上教师或研究生完成，按作者顺序排列（教师和论文之间、研究生和论文之间都是多对多的关系）。l 每位研究生只属于某一专业（研究生和专业之间是一对多的关系）。例6.4下面用ER图来表示某个工厂物资管理的概念模型。物资管理涉及的实体有：=仓库。属性有仓库号、面积、电话号码。=零件。属性有

10、零件号、名称、规格、单价、描述。=供应商。属性有供应商号、姓名、地址、电话号码、账号。=项目。属性有项目号、预算、开工日期。=职工。属性有职工号、姓名、年龄、职称。这些实体之间的联系如下： （1）一个仓库可以存放多种零件，一种零件可以存放在多个仓库中，因此仓库和零件具有多对多的联系。用库存量表示某种零件在某个仓库中的数量。 （2）一个仓库有多个职工当仓库保管员，一个职工只能在一个仓库工作，因此仓库和职工之间是一对多的联系。 （3）职工之间具有领导-被领导关系。即仓库主任领导若干保管员，因此职工实体集中具有一对多的联系。（4）供应商、项目和零件三者之间具有多对多的联系。即一个供应商可以供给若干项

11、目多种零件，每个项目可以使用不同供应商供应的零件，每种零件可由不同供应商供给。下面给出此工厂的物资管理E-R图。为了更清晰地表示实体及其实体之间的联系，人们常常把实体及其属性用一幅图表示，如图（a）所示；实体及其实体之间的联系如图（b）所示，完整的实体联系图如图（c）所示。根据E-R建立数据库模式的步骤1、E-R图转换为表并进行必要的合并，本步骤可以按照机械方法完成一个良好的E-R图，完成本步转换和合并得到的结果，已经是比较理想的数据库模式(尽管还有人工进一步优化的余地)2、优化本步无具体可行的机械方法，主要依靠设计人员的经验和能力相关概念：数据模型数据（data）是描述事物的符号记录。模型（

12、Model)是现实世界的抽象。数据模型（Data Model）是数据特征的抽象，是数据库管理的教学形式框架。数据模型所描述的内容包括三个部分：数据结构、数据操作、数据约束。1）数据结构:数据模型中的数据结构主要描述数据的类型、内容、性质以及数据间的联系等。数据结构是数据模型的基础，数据操作和约束都建立在数据结构上。不同的数据结构具有不同的操作和约束。2）数据操作:数据模型中数据操作主要描述在相应的数据结构上的操作类型和操作方式。3）数据约束：数据模型中的数据约束主要描述数据结构内数据间的语法、词义联系、他们之间的制约和依存关系，以及数据动态变化的规则，以保证数据的正确、有效和相容。数据模型按不

13、同的应用层次分成三种类型：分别是概念数据模型、逻辑数据模型、物理数据模型。1、概念数据模型（Conceptual Data Model）：简称概念模型，是面向数据库用户的实现世界的模型，主要用来描述世界的概念化结构，它使数据库的设计人员在设计的初始阶段，摆脱计算机系统及DBMS的具体技术问题，集中精力分析数据以及数据之间的联系等，与具体的数据管理系统（Database Management System，简称DBMS）无关。概念数据模型必须换成逻辑数据模型，才能在DBMS中实现。2、逻辑数据模型（Logical Data Model）:简称数据模型，这是用户从数据库所看到的模型，是具体的DBM

14、S所支持的数据模型，如网状数据模型(Network Data Model)、层次数据模型(Hierarchical Data Model)等等。此模型既要面向用户，又要面向系统，主要用于数据库管理系统（DBMS）的实现。3、物理数据模型（Physical Data Model）:简称物理模型，是面向计算机物理表示的模型，描述了数据在储存介质上的组织结构，它不但与具体的DBMS有关，而且还与操作系统和硬件有关。每一种逻辑数据模型在实现时都有起对应的物理数据模型。DBMS为了保证其独立性与可移植性，大部分物理数据模型的实现工作又系统自动完成，而设计者只设计索引、聚集等特殊结构。在概念数据模型中最常

15、用的是E-R模型、扩充的E-R模型、面向对象模型及谓词模型。在逻辑数据类型中最常用的是层次模型、网状模型、关系模型。 三级模式结构：外模式、概念模式和内模式 一、概念模式（Schema） 定义：也称逻辑模式，是数据库中全体数据的逻辑结构和特征的描述，是所有用户的公共数据视图。 理解： 一个数据库只有一个概念模式； 是数据库数据在逻辑级上的视图； 数据库模式以某一种数据模型为基础； 定义模式时不仅要定义数据的逻辑结构（如数据记录由哪些数据项构成，数据项的名字、类型、取值范围等），而且要定义与数据有关的安全性、完整性要求，定义这些数据之间的联系。 二、外模式（External Schema） 定义

16、：也称子模式（Subschema）或用户模式，是数据库用户（包括应用程序员和最终用户）能够看见和使用的局部数据的逻辑结构和特征的描述，是数据库用户的数据视图，是与某一应用有关的数据的逻辑表示。 理解： 一个数据库可以有多个外模式； 外模式就是用户视图； 外模式是保证数据安全性的一个有力措施。 三、内模式（Internal Schema） 定义：也称存储模式（Storage Schema），它是数据物理结构和存储方式的描述，是数据在数据库内部的表示方式（例如，记录的存储方式是顺序存储、按照B树结构存储还是按hash方法存储；索引按照什么方式组织；数据是否压缩存储，是否加密；数据的存储记录结构有何

17、规定）。 理解： 一个数据库只有一个内模式； 一个表可能由多个文件组成，如：数据文件、索引文件。 它是数据库管理系统(DBMS)对数据库中数据进行有效组织和管理的方法 其目的有： 为了减少数据冗余，实现数据共享； 为了提高存取效率，改善性能。1、E-R 到表的基本转化方法l 实体转化为表E-R图的每个实体转化成一个表，实体的属性转化为表的属性(暂时只考虑简单、单值属性)，实体的主码转化为表的主码l 联系转化为表每个联系转化成一个表l 联系转化成表的属性参与联系实体的主码并集pk(e1)pk(e2)以及联系的属性a1,a2共同构成表的属性pk(e1)pk(e2)a1,a2在联系转化成的表中,属性

18、的非空限制：实体主码形成的属性pk(e1)pk(e2)均应not null只有在联系转化成的表与其他表合并后，才可能允许nulll 联系转化成的表的码：参与联系实体的主码并集pk(e1)pk(e2)是联系转化成的表的超码多对一联系，上述超码去掉一个“一”端实体的主码后，是联系表的候选码多对多联系，上述超码是联系表的候选码l 实体转化成的表： Dept(dno,dname) Student(sno,sname) Course(cno,cname)l 联系转化成的表： SD(sno,dno,time) /dno非空 SC(sno,cno,score)例题：请将下述E-R转化为关系模式：（注意指明各

19、表的主码）l 实体转化成的表-Teacher(tno,name)-class(classno,classname)-Course(cno,cname)l 联系转化成的表-tc(tno,cno)-tcc(classno,cno,tno)2、表的合并l 主要讨论联系转化的表与相关实体转化的表的合并问题l 按照联系类别分别讨论能否合并、如何合并二元m:1联系二元1:1联系二元m:n联系多元联系i. 二元多对一联系： 联系转化的表可以和“多端” （此例中一个院系有多个学生，学生为多）实体转化成的表进行合并示例： E-R图 转化成的表l Dept(dno,dname) l Student(sno,sna

20、me)l SD(sno,dno,time) /dno非空 表的合并l Student+SDà Student(sno,sname,dno,time)/dno可以为空ii. 二元一对一联系： 联系转化的表可以任一端实体转化成的表进行合并 二元一对一联系不能导致相关实体转化成的表合并示例： E-R图如右所示 转化成的表l Dept(dno,dname)l President(pid,name)l Manage(dno,pid) /dno,pid均可作主码，假设选dno作主码 表的合并l 可以：Dept+Manageà Dept(dno,dname,pid)l 或者：Presid

21、ent+ManageàPresident(pid,name,dno) 不能进行下述合并：Dept+Manage+President à?(不能接受的合并)iii. 二元m:n联系 联系转化的表和实体转化的表不能进行合并l 示例： E-R图 转化成的表l Student(sno,sname)l Course(cno,cname)l SC(sno,cno,score) 无法进行表的合并iv. 多元联系 联系转化的表和实体转化的表不能进行合并 即便是m:n:1,其转化的表和也不能进行合并l 示例： E-R图(省略了属性)： 转化成的表：l Class(classno,classn

22、ame)l Teacher(tno,tname)l Course(courseno,coursename)l TCC(tno,classno,courseno)/P.K.=(classno,tno)或(classno,courseno) 无法进行表的合并例题：教务系统概念模型如下图所示请将E-R图转化为表并进行必要的合并：l 将E-R图转化为表： 实体转化成表d(dno,dname)c(cno,cname,property)s(sno,sname,age,sex)t(tno,tname,age,sex) 联系转化为表sd(sno,dno)td(tno,dno)sc(sno,cno,score)

23、tc(tno,cno,time) l 表的合并 s+sdàs(sno,sname,age,sex,dno) t +td àt(tno, tname,age,sex,dno)l 合并表后的关系模式 d(dno,dname) c(cno,cname,property) s(sno,sname,age,sex,dno) t(tno,tname,age,sex,dno) sc(sno,cno,score) tc(tno,cno)l 关系模式图如图所示l 教务系统数据概念模型与逻辑模型对比概念模型主要用E-R图刻画，用于需求分析逻辑模型主要由关系模式图刻画，用于模式设计l 请将E-R

24、图转化为表并进行必要的合并： 假设每个实体都有属性id和name 假设供应联系有属性quantity,其它联系无属性l E-R图转化为表 实体转化成表project(pid,pname)employee(eid,ename)supplier(sid,sname)component(cid,cname)warehouse(wid,wname) 联系转化为表participate(pid,eid)lead(eid,leid) /leid非空supply(sid,pid,cid,quantity)produce(sid,cid)store(cid,wid)manager(eid,wid)l 表的合并

25、employee+leadàemployee(eid,ename,leid)/leid可以为空l 将如下E-R图转化为表并进行必要的合并，请给出： 1.结果关系模式 2.关系模式图E-R图其它要素转化为表的方法 复杂属性处理 弱实体处理 继承转化为表 聚集转化为表l 多值属性 每个多值属性转化为一个表 表主码：实体主码+多值属性分辨符 例如：S-telno(sno,tno)l 复合属性 只保留叶节点属性l 派生属性 一般表模式中不保留派生属性 S(sno,sname,birthday,city,street) 如果考虑使用频率、查询效率等因素，可以保留派生属性，尽管本质上派生属性是表

26、的冗余属性l 示例，学生实体转化为表： 所有单值属性转化为一个表l S(sno,sname,birthday,city,street) 每个多值属性转化为一个表l S-telno(sno,tno)l S-relative(sno,pid,relation,name)l 思考： S-relative中，pid属性是否可以单独构成主码？ 不同多值属性转化的表可以合并吗？弱实体转化为表 弱实体象普通实体一样向表转化，只是在弱实体转化的表中，增加属主实体的主码作为表属性 弱实体转化成表的主码：l 属主实体的主码+弱实体的分辨符 标识性联系不转化成表，不作处理l 示例： 请将如下所示银行帐户E-R图转化

27、为表l 将E-R图转化为表： 实体转化成表 acc(accno,accname) emp(eno,ename) 弱实体转化成表 trans(accno,lineno,date,dealnum) rual(accno,date,accrual) 标识性联系不转化成表 联系转化成表 tr(accno,lineno,date)l te(accno,lineno,eno)l 表合并 trans+tr+te=trans(accno,lineno,transdate,dealnum,rualdate,eno)l 练习： 对上述银行账户，如果在E-R中不使用弱实体，而是通过给交易记录、利息记录增加标识属性是

28、成为强实体，试给出相应E-R图 试将上述E-R图转化为表并进行必要的合并 体会、比较两种E-R图对应概念模型及逻辑模型的差异，你更喜欢哪一种？l 将E-R图转化为表： 实体转化成表l acc(accno,accname)l trans(tid,lineno,date,dealnum)l rual(rid,date,accrual)l emp(eno,ename) 联系转化成表l ta(tid,accno)l ra(rid,accno)l tr(tid,rid)l te(tid,eno)l 表合并trans+ta+tr+te=trans(tid,accno,lineno,date,dealnum

29、,rid,eno)rual+ra=rual(rid,accno,date,accrual)l 弱实体方案转化的逻辑模式 acc(accno,accname) emp(eno,ename) trans(accno,lineno,transdate,dealnum,rualdate,eno) rual(accno,date,accrual)l 强实体方案转化的逻辑模式： acc(accno,accname) emp(eno,ename) trans(tid,accno,lineno,date,dealnum,rid,eno) rual(rid,accno,date,accrual)l 课堂练习：

30、请分别给出两种逻辑模式的模式图 试述你更喜欢哪种方案？继承关系à表 l 继承关系的三种处理方案 父类、子类分别建表p(pid,name)s(pid,sno,dept)t(pid,tno,dept)父类并入子类，只为子类建表s(pid,name,sno,dept)t(pid,name,tno,dept)子类并入父类，只为父类建表p(pid,name,sno,s-dept,tno,t-dept)l 比较： 三种方案各有优缺点，都可以接受 设计人员根据具体情况，综合评定选择确定最终方案 讨论：针对这个示例，你更愿意选择哪个方案？l 学校系统概念模型如下E-R图所示： 请按照继承关系三种处理方案分别转化成表 比较各方案优缺点，你更喜欢哪种方案？l 父类、子类分别建表 实体转化成表l person(pid,name,age)l student(pid,sno)l teacher(pid,tno)l book(bno,bname)l course(cno,cname) 联系转化成表l pb(pid,bno)l tsc(t-pid,