第07章专题-数据库设计

专题：数据库设计1数据库设计21用户需求分析2概念结构设计（ER图）3逻辑结构设计（关系模式）4物理结构设计了解组织机构了解各部门业务情况确定数据库的信息组成1.用户需求分析3两大类数据模型2.概念结构设计（E-R图）4DBMS支持的数据模型概念模型认识抽象信息世界机器世界现实世界中客观对象的抽象过程现实世界现实世界概念模型数据库设计人员完成逻辑模型物理模型由DBMS完成概念模型逻辑模型数据库设计人员完成基本概念

(1)实体（Entity）

客观存在并可相互区别的事物称为实体。可以是具体的人、事、物或抽象的概念。(2)属性（Attribute）

实体所具有的某一特性称为属性。一个实体可以由若干个属性来刻画。

(3)码（Key）

唯一标识实体的属性集称为码。

2.概念结构设计（E-R图）5实体名称属性(4)域（Domain）

属性的取值范围称为该属性的域。(5)实体型（EntityType）

用实体名及其属性名集合来抽象和刻画同类实体称为实体型(6)实体集（EntitySet）同型实体的集合称为实体集2.概念结构设计（E-R图）6(7)联系（Relationship）

现实世界中事物内部以及事物之间的联系在信息世界中反映为实体内部的联系和实体之间的联系实体型间联系两个实体型一对一联系（1:1）三个实体型一对多联系（1:n）多个实体型多对多联系（m:n）

2.概念结构设计（E-R图）7联系两个实体型间的联系8实体型A联系名实体型B111:1联系实体型A联系名实体型Bmnm:n联系实体型A联系名实体型B1n1:n联系概念模型的表示方法很多实体－联系方法(E-R方法)用E-R图来描述现实世界的概念模型E-R方法也称为E-R模型2.概念结构设计（E-R图）9概念模型的设计步骤2.概念结构设计（E-R图）10逻辑结构设计需求分析数据流图数据字典

数据抽象、局部视图的设计分E-R图

视图集成用户满意总E-R图NoYes实例：教学管理数据库模型的设计2.概念结构设计（E-R图）11练习1：

假如要为某基层单位建立一个“基层单位”数据库。通过调查得出，用户要求数据库中存储下列基本信息。部门：部门号，名称，领导人编号；职工：职工号，姓名，性别，工资，职称，照片，简历；工程：工程号，工程名，参加人数，预算，负责人；办公室：地点，编号，电话。这些信息的关联的语义为：每个部门有多个职工，每个职工只能在一个部门工作；每个部门只有一个领导人，领导人不能兼职；每个部门可以同时承担若干工程项目，数据库中应记录每个职工参加项目的日期；一个部门可有多个办公室；每个办公室只有一部电话；数据库中还应存放每个职工在所参加的工程项目中承担的具体职务。要求：设计基层单位数据库的概念模型。2.概念结构设计（E-R图）12练习1参考答案：13领导部门职工n工作111办公办公室n1n工程承担1负责参加nmn1具体职务日期实体仓库：仓库号、面积、电话号码零件：零件号、名称、规格、单价、描述供应商：供应商号、姓名、地址、电话号码、帐号项目：项目号、预算、开工日期职工：职工号、姓名、年龄、职称实体之间的联系如下：

(1)一个仓库可以存放多种零件，一种零件可以存放在多个仓库中。仓库和零件具有多对多的联系。用库存量来表示某种零件在某个仓库中的数量。(2)一个仓库有多个职工当仓库保管员，一个职工只能在一个仓库工作，仓库和职工之间是一对多的联系。职工实体型中具有一对多的联系(3)职工之间具有领导-被领导关系。即仓库主任领导若干保管员。(4)供应商、项目和零件三者之间具有多对多的联系练习二：用E-R图表示某个工厂物资管理的概念模型14练习二参考答案15逻辑结构设计（关系模式）16实体实体集合及实体联系相关属性集合加工、转换数文件据记录库相关数据项集合数据世界DBMS的数据模型信息世界E-R模型关系模式对关系的描述，用关系名(属性1，属性2，…属性n)来表示。eg.

库存（仓库号，器件号，数量）仓库（仓库号，城市，面积）逻辑结构设计（关系模式）17转换过程示意图逻辑结构设计（关系模式）18E-R图关系模型1、实体相应转换为一个关系，实体名称作为关系名称，该关系包括对应实体的全部属性，并确定出该关系的关键字。转换方法和原则2、联系

a.联系具有属性：联系名作为关系名，发生联系的实体的主关键字作为联系的主关键字。

b.联系不具有属性：不带有属性的联系可以去掉。转换举例一厂长号*姓名年龄厂长厂号*厂名地点工厂管理11厂长（厂长号，姓名，年龄）工厂（厂号，厂名，地点）管理（厂号，厂长号）转换举例二仓库号*地点面积仓库货号*品名价格产品存放1n仓库（仓库号，地点，面积）产品（货号，品名，价格）数量存放（仓库号，货号，数量）转换举例三学号*姓名年龄学生课程号*课程名学时数课程学习mn学生（学号，姓名，年龄）课程（课程号，课程名，学时数）成绩学习（学号，课程号，成绩）24nm学生课程选课学分课程号*课程名年龄姓名性别学号*练习：解释下列E—R模型图中各图形和线条代表的含义，并将其转化为关系模式。数据模型的优化1、关系模型基础及关系运算3、对数据依赖进行极小化处理4、确定关系模式分别属于第几范式5、确定模式是否进行合并或分解2、确定数据依赖关系模型关系数据模型的组成要素：关系数据结构：实体及联系统一用二维表表示（关系）。

关系操作

关系完整性约束查询操作学号姓名年龄1姚明202张靓颖213成龙20关系模型实体完整性

参照完整性

用户定义完整性选择

投影

连接

除并

交

差积

增加

删除

修改专门的关系运算传统的集合运算更新操作1．属性和域属性------用来描述事物特征。思考：用来描述学生特征的词汇有哪些？关系数据结构域----具有相同数据类型的值的集合。例如：姓名的取值范围是合法姓名的集合。学号的域是若干位数字组成的字符串的集合。性别的域是｛男｝，｛女｝。……注意：①关系数据模型中，所有的属性都应是不可再分的原子数据的集合。②关系数据模型中，允许某些属性值为空，即NULL。如：姓名、性别、年龄、籍贯、系别、专业学号姓名年龄性别系别专业01姚明26男体育篮球22王娜22女数学数教………………元组属性基本概念及定义2．关系和元组设有一名为R的关系，它有属性A1，A2，。。。，An，其对应的域分别为D1，D2，。。。，Dn，则关系模式表示为：R（A1，A2，。。。，An）称为关系R的模式。N为R的属性的个数，称为R的目或度。基本概念及定义在关系模型中，实体及实体间的联系用关系来表示。

如：学生、课程、学生修读课程的考试三个实体间的关系在关系模型中表达如下：

学生（学号、姓名、性别、年龄、班级号）课程（课程号、课程名、周学时、学分）

考试（学号、课程号、成绩）基本概念及定义例如：关于学生的关系模式：Student（学号，姓名，性别，出生年月，系别，专业）而其中某一个学生<06021，王娜，女，1986，数学系，信息与计算科学>就是关系Student的一个关系。所有学生的值构成了关系的元组集合。结论：关系模式是型，是对关系的描述。关系是值，是关系模式的具体体现；关系模式是稳定的。关系是变化的，关系是某一时刻关系模式的内容。

注意：

1]在关系R中，元组的次序无关，但不能允许有相同的二个元组；属性的次序无关。

2]一个关系可视为一个二维表。组成表的所有数据项都应是不可再分的最小数据项。

3]关系对应表

，

属性对应列

，

元组对应行.3．码/键候选码/候选键――如果关系的某一个属性或属性组的值唯一地标识一个元组。而其任何真子集无此性质，则这个属性或属性组称为该关系的候选码。基本概念及定义主码/主键------一个关系R可有多个候选码，若选中一个作为Key，则它就是主码，每个元组的主键值应是唯一的。主属性------主码的属性，称为主属性。非主属性-----不包含在候选码中的属性，称为非主属性。

教师编号姓名年龄学历1aaa40本科2bbb26硕士3ccc28博士侯选码：教师编号，姓名主码：教师编号主属性：教师编号非主属性：年龄，学历R例1：例2：①学生R（学号，姓名，性别，专业号）

②专业S（专业号，专业名）

其中：关系R的“专业号”属性与关系S的主码“专业号”相对应，所以，“专业号”属性是学生关系R的外码。

基本概念及定义外码------若关系R中的属性或属性组F并非该关系的码，但它们与另外一个关系S的主码相对应，则称其为该关系的外码。并称R为参照关系，S为被参照关系。关系的完整性关系模型的完整性规则是对关系的某种约束条件，有四类完整性约束：域完整性、实体完整性、参照完整性和用户定义的完整性。2、实体完整性规则：若属性A是基本关系R的主属性，则属性A取值不能为空，即不能为NULL。

如：stu（学号，姓名，性别，系别）学号不能取空值。注意：所有主属性都不能取空值，而不仅是主码整体不能取空值。如：选修（学号，课程号，成绩）中，学号和课程号都不能取空值。1、域完整性约束规则：最简单、最基本的约束。属性值应是域中的值，属性的值能否为空，由问题的语义决定的。例：考试分数（成绩）的取值范围是0~100。

3、参照完整性规则：若属性（或属性组）F是基本关系R的外码，它与基本关系S的主码Ks相对应，则对于R中每个元组在F上的值必须为：

或者取NULL

或者等于S中某个元组的主码值。例2：①R（学号，姓名，性别，专业号）②S（专业号，专业名）4、用户定义的完整性：某一具体的数据库的属性及其关系所应满足的条件。例如：规定举重队员25>年龄>15，100kg>体重>55Kg。小结：

实体完整性和参照完整性是所有关系数据库都支持的，称为关系的两个不变性。关系的完整性关系操作关系代数关系演算结构化查询语言：SQL元组关系演算域关系演算关系操作关系操作的能力常用关系代数表示

6）选择(Selection)

7）投影(Projection)

8）连接

(Join)

4）除(Division)5）迪卡尔积1）并(Union)2）交(Intersection)3）差(SetDifference)

关系操作方式的特点是集合操作(一次一集合)关系代数——运算符集合运算并，交，差，迪卡尔积集合运算——并Union(∪)R和S的并，R∪S，是在R或S或两者中的元素的集合一个元素在并集中只出现一次，即消除重复元组R和S必须兼容，即必须同属性、同域R∪SRS集合运算——并Union(∪)自己练42ABC367257723443RABC345723SABC367257723443345R∪S集合运算——交Intersect(∩)R和S的交，R∩S，是在R和S中都存在的元素的集合一个元素在交集中只出现一次，即消除重复元组R和S必须兼容，即必须同属性、同域R∩SRS集合运算——交Intersect(∩)45ABC367257723443RABC345723SABC723R∩S集合运算——差Minus(-)R和S的差，R-S，是在R中而不在S中的元素的集合R和S必须兼容，即必须同属性、同域R-SRSS-RRS集合运算——差Minus(-)S-RABCa1b3c248ABC367257723443RABC345723SABC367257443R－SABC367S－R集合运算——笛卡儿积(×)关系R、S的笛卡儿积是两个关系的元组对的集合所组成的新关系R×S：属性是R和S的组合（有重复）元组是R和S所有元组的可能组合是R、S的无条件连接，使任意两个关系的信息能组合在一起关系R和关系S的目分别为k1、k2，关系R有m个元组，关系S有n个元组，则其迪卡尔积的目为k1+k2个，有m×n个元组集合运算——笛卡儿积(×)51AB

12RCD

10102010EaabbSAB

11112222CD

1019201010102010EaabbaabbRx

S关系代数——专门的关系运算选择、投影、连接、除法关系运算——选择(σ)-[限制]表达式：σF(R),

表示从R中选择满足条件(使逻辑表达式F为真)的元组行的运算，从关系R中选择符合条件的元组构成新的关系逻辑表达式的运算符号有：关系运算——选择(σ)-[限制]年龄大于17岁：身高不低于170cm：性别不为女并且体重小于50kg：学号SNO在20号以下成绩GRADE不低于80分：年龄>

17身高≥170cm性别≠‘女’∧体重＜50kgSNO＜20andGRAGE≥80所在系别为数学系或计算机系的男生：系=‘计算机’∨系=‘数学’

∧性别=‘男’关系运算——选择(σ)-[限制]ABCabccbddafσB=‘b’

(R)ABCabccbdσ年龄>=18AND性别=‘女’(student)例2：例1：从学生关系中选择年龄大于等于18岁的女生。关系运算——选择(σ)例:σssex=‘男’

ANDsdep=‘IS’(Student)

2、σ

Sage

>=20ANDSdept=‘CS’(student)

1、σSage=19（student）思考：运算结果中，有可能会出现重复元组吗？例3：ABC367257723443R

A<5(R)

ABC367257443

A<5

C=7(R)

ABC367257例4：关系运算——选择(σ)关系运算——投影(π)表达式：πA1,A2,…,An(R),表示从R中选择属性集A1,A2,…,An组成新的关系列的运算，从关系R中选择若干属性列组成新的关系πC,A

(R)ABCabccbdcdadafCAcadcacfd例1：R关系运算——投影(π)例:πssex,sage(student)例2：查询学生的姓名和所在系

πsname，sdept（student)该怎样表示呢？注意：投影运算的结果中,要去除可能的重复元组思考：运算结果中，有可能会出现重复元组吗？关系运算——投影(π)πC,A

(R)ABCabccbdadcdafCAcadccafd例3：RCAcadcfd关系运算——条件连接(θ连接)从R×S的结果集中，选取在指定的属性集上满足θ条件的元组，组成新的关系，也成为θ连接θ是一个关于属性集的逻辑表达式RS

iθj=σ（连接条件）（R×S

）关系运算——条件连接(θ)注意：两个表的运算属性在结果表中都要出现例1：关系运算——等值连接(θ为‘=‘)RSR.B=S.B注意：两个表的运算属性在结果表中都要出现表达式：RS

i=j即从R与S的迪卡尔积中选取属性值相同的那些元组。关系运算——自然连接()要求两个关系中进行比较的分量必须是相同的属性组，并且在结果中把重复的属性列去掉。表达式:RS关系运算——自然连接()RS注意：两个表的公共属性在结果表中只出现一个自然连接是一种特殊的等值连接例1：AB

12412CD

aababrB13123DaaabbE

s

11112

aaaab

ABCDE例2：关系运算——自然连接()rs关系运算——除(÷)用一个（m+n）元的关系R除以一个n元关系S，结果产生一个m元的新关系表达式：R÷SR的属性必须包含S的全部属性；R中的某些属性不出现在S中；结果新关系的属性是由R中那些不出现在S中的属性组成；新关系的元组由在S中出现的且在R

中对应值相同的那些元组组成。关系运算——除(÷)例

RSABCDCDabcdcdabefefbcefedcdedefabde计算：R÷SR÷SABabed小结：传统集合运算中，并、交、差运算（不包括积）的关系R、S必须有相同的目，并且相应的属性取自同一个域；关系R和关系S的目分别为k1、k2，元组分别为m、n个，则其迪卡尔积运算的新关系的目为k1+k2，元组为mn个；一般连接操作从行的角度运算；自然连接同时从行和列的角度运算；消除重复列两个关系中进行比较的分量必须是相同的属性组；

除运算同时考虑行和列。

关系代数——实例[问1]查询信息系（IS系）全体学生

σSdept

='IS'(Student)

或σ5='IS'(Student)结果：SnoSnameSsexSageSdept95002刘晨女19IS95004张立男19IS关系代数——实例[问2]查询年龄小于20岁的学生

σSage<20(Student)

或σ4<20(Student)

结果：

SnoSnameSsexSageSdept95002刘晨女19IS95003王敏女18MA95004张立男19IS

关系代数——实例关系代数——实例[问3]查询选修了2号课程的学生的学号

课程号、学号←SC表第①步：2号课程的选课情况σcno=‘2’(SC)关系代数——实例1第②步

选修2号课程的学号πsno(σcno=‘2’(SC))课堂练习1ABCadcababbcfdfR在关系R上以下操作的结果？：

πA，B（

σ

B=b(R))1、ABacbb有两个关系S和C。试用关系代数表达式表示下列查询语句：

S#SNAMEAGESEX1李强23男2刘丽22女3张友22男S

C#CNAMETEACHERk1C语言王华k5数据库原理程军k8编译原理程军C(1)检索“程军”老师所授课程的课程号和课程名。（2）检索年龄大于21的男学生学号和姓名。课堂练习2：（1）πC#,CNAME(σ

TEACHER=‘程军’（C））（2）πS#，CNAME(σAGE﹥21and

SEX=‘男’（S））

课堂练习2：

C#CNAMEk5数据库原理k8编译原理

S#SNAME1李强3张友3、设有如图所示的关系R、W计算：（1）R1=

πY,T(R)(2)R2=

σ

P>5andT=e(R)R3=R∞WPQTY292977babaggceedecdffefdRTYBccdddemnnW课堂练习3：YTdfecedR1PQTY97ageeffR2PQTYB22779bbggaccccdddddemnmnnR3课堂练习3：关系数据理论数据依赖范式数据依赖:是现实世界属性间相互联系的抽象，是数据的内在性质,是语义的体现。数据依赖是造成“不良”关系模式的根本原因。一、什么是数据依赖关系数据理论可以唯一确定！函数关系y=f（x）

；

函数依赖极为普遍存在于现实生活中。一个学生关系student（sno，sname，sdept），当学号值确定之后，姓名和该生所在系能唯一确定吗？我们称sno函数决定sname和sdept，或称sname和sdept函数依赖于sno，记为：sno→sname，sno→sdept。例：一个学生关系。

U=（Sno，Sdept，MN，Cname，G）

由现实世界已知事实知：

①一个学号只对应一个学生，一个系有若干学生，但一个学生只属于一个系；

②一个系只有一名系主任；③一个学生可选修多门课程，每门课程有若干名学生选修，

每个学生学习每一门课程都有一个成绩；

我们可以得到属性组U上的一组函数依赖关系，大家写一下：

CnameSnoSdeptMNGF={Sno→Sdept，Sdept→MN，（Sno，Cname）→G}如果只考虑函数依赖这一种数据依赖，我们可得到以上的函数依赖关系，但这种关系模式存在几个问题：一个好的关系模式不会发生插入异常、删除异常和更新异常，数据冗余尽量要少。[1]数据冗余。[2]更新异常。[3]插入异常。[4]删除异常。每一个系主任的姓名重复出现，重复次数与该系所有学生的课程成绩出现次数相同。这将浪费大量的存储空间。如某系更换系主任后，系统必须修改与该系学生有关的每一个元组。如果一个系刚成立，尚无学生，或者有了学生尚未安排课程，就无法把这个系及其系主任的信息存入数据库。如果某个系的学生全部毕业了，在删除该系学生信息同时，也把该系及其系主任的信息也丢掉了。二、关系规范化理论：

1、定义：规范化理论是数据库逻辑设计的工具，是衡量关系模式三大缺点的严重程度的度量。1971年E.F.CODD提出了规范化理论，通过分解关系模式来消除其中不合适的数据依赖。按规范化理论，将一个低一级的范式要求的关系模式分解为几个高一级范式的关系模式的过程叫规范化.

将上例学生关系分解为：

S（Sno，Sdept）；SG（Sno，Cname，G）

；

Dept（Sdept，MN）；如，

F={Sno→Sdept，Sdept→MN，（Sno，Cname）→G}Sno→Sdept（Sno，Cname）→GSdept→MN

2、

函数依赖:设R(U)是一个关系模式，U是R的属性集合，X、Y是U上的子集。若对R(U)任意一个可能的关系r，如果r中不可能存在两个元组，它们在X上的属性值相等,而在Y上的属性值不等，则称“X函数确定Y”或称“Y函数依赖于X”，记为X→Y注意：

1]函数依赖是语义范畴的定义，只能根据语义确定一个函数依赖。如：姓名→年龄，只能在没有同名人的条件下成立。

2]函数依赖是指R的一切关系都必须满足的约束条件。

3、对函数依赖：X→Y①X→Y中，X为决定因素。②如X→Y且Y→X，则记：X←→Y。③若Y不函数依赖于X，则记作X→Y。

1)如果X→Y,并且Y不是X的子集,则称X→Y是

非平凡的函数依赖.2)如果Y是X的子集,则称X→Y是平凡的函数依赖.[必然成立]3)如果X→Y是关系模式R(U)的一个函数依赖,当对X

的每一个真子集X1,都有X1→Y,则Y对X的函数依赖是完全的,称Y对X完全函数依赖,记作XFY.4)如果对X某个真子集X1,有X1→Y,则称Y对X是部分函数依赖,记作XpY.

3、函数依赖的种类(S#,CN)G(S#,CN)SDPF

这里G完全函数依赖于主码（S#,CN）；p

这里SDP部分函数依赖于主码（S#,CN）

例1:为学校设计一个关系模式，其中U为关系名，S#：描述学号,CN:描述课程号,G:描述学习成绩,SDP:描述系名,MN:描述系主任.

关系模式：U(S#,CN,G,SDP,MN)5)设X,Y,Z为关系模式R的互不相同的属性集合,如果X→Y(Y

X),而Y→X,但Y→Z,则称Z传递函数依赖于X,记作XtZ.若X→Y,Y→X,则X←→Y,X直接Z称直接函数依赖.

例2:U(S#,CN,G,SDP,MN)S#SDP,SDPS#,SDPMN

则有S#tMN练习1、关系模式R（A，B，C，D），R上成立的函数依赖集F={ABD,BD，ABC}，则R的主码是？判断R是否存在完全函数依赖关系？2、关系模式S（P，Q，T，Y，X），S上成立的函数依赖集F={PQYT，QYT，TX}，则S的主码是？判断S存在的函数依赖的类型？练习3、建立一个关于系、学生、班级、学会等诸信息的数据库。描述学生的属性有：学号、年龄、系名、班号、宿舍区；描述班级的属性有：班号、专业名、系名、人数、入校年份；描述系的属性有：系号、系名、系办公室地点、系人数；描述学会的属性有：学会名、成立年份、学会地点、学会人数；有关语义如下：一个系由若干专业，每个专业每年只招一个班，每个班由若干学生。一个系的学生住在同一个宿舍区。每个学生可参加若干学会，每个学会由若干学生。学生参加某学会有一个入会年份。

请给出关系模式，写出每个关系模式的函数依赖关系并判断其函数依赖类型。4、范式

1NF—2NF—3NF—BCNF—4NF4、范式、规范化的定义:衡量一个关系模式“好坏”程度的标准规范化：通过分解把属于低级范式的关系模式转换为几个属于高级范式的关系模式的集合的过程。

低高学号家庭住址邮编城市区街道学号邮编城市区街道不满足1NF满足1NF1]1NF定义：在关系模式R中的所有属性都是不可再分的最小数据项,则关系R属于第一范式,记作R∈1NF.

例如:4、范式的定义注意：不满足1NF的数据库不是关系数据库。1NF，FirstNF元组中每一个分量都必须是不可分割的数据项。学号姓名年龄系别系主任课程成绩课程号成绩S1赵亦17计算机刘伟C190S1赵亦17计算机刘伟C282S2钱尔18信息王平C384S3刘思佳17信息王平C268不符合规范约束条件学号姓名年龄系别系主任课程号成绩S1赵亦17计算机刘伟C190S1赵亦17计算机刘伟C282S2钱尔18信息王平C384S3刘思佳17信息王平C268处理后符合规范约束条件第一范式要求数据表不能存在重复的记录，即存在一个关键字。1NF的第二个要求是每个字段都不可再分，即已经分到最小，关系数据库的定义就决定了数据库满足这一条。主关键字需要满足下面几个条件：主关键字在表中是惟一的。主关键字段不能存在空值。每条记录都必须有一个主关键字。4、范式的定义

2]2NF定义:若关系模式R∈1NF,且每个非主属性都完全函数依赖于候选码（或者说R中不存在非主属性对候选码的部分函数依赖）,则关系模式R属于第二范式,记作R∈2NF.

例3:关系模式U(S#,CN,G,SDN,MN)其中U为关系名,S#:描述学号,CN:描述课程名,G:描述学习成绩,SDN:描述系名,MN:描述系主任

S#CNGSDNMNfpp非主属性对主码的函数依赖关系:

这里（S#,CN）是主码，G完全函数依赖于主码；SDN、MN部分函数依赖于主码4、范式的定义学号姓名年龄系别系主任课程号成绩S1赵亦17计算机刘伟C190S