SQL Server 数据库技术课件2

2.1关系数据模型

2.2E-R模型到关系模型的转换

2.3关系模型的三类完整性约束

2.4案例1：活期储蓄管理系统数据库设计1/12/20232.1关系数据模型2.1.1关系模型的结构关系（relationship）

关系是满足一定条件的二维表。表中的一行称为关系的一个元组，用来存储事物的一个实例；表中的一列称为关系的一个属性，用来描述实体的某一特征。表是由一组相关实体组成的集合。所以，表和实体集这两个词常常可以交替使用。

表2-1给出了实体集、关系、表等一系列术语之间的对照关系

概念模型中关系理论中关系数据库中某些软件中实体集(Entityset)关系(relation)表(Table)表（或数据库文件）实体(Entity)元组(Tuple)行(Row)记录(Recode)属性(Attribute)属性(Attribute)列(Col)字段(Field)主码(Primarykey)主码(Primarykey)关键字(Primarykey)关键字(Primarykey)1/12/2023

关系是一个行与列交叉的二维表，每一列（属性）的所有数据都是同一种数据类型的，每一列都有惟一的列名，列在表中的顺序无关紧要；表中的任意两行（元组）不能相同，行在表中的顺序也无关紧要。

表2-2以图书借阅系统为例给出了一个表的实例编号书名作者出版社出版日期定价F12.245计算机文化基础刘凌志青山2002.521.80F33.33数据结构实用教程王云晓蓝天2002.522.00G22.12VisualBasic实用教程梁晓峰碧水2003.1028.00G11.22Java程序设计实用教程张大海碧水2004.720.80F23.55数据结构（C语言版）马志刚青山2004.724.50G11.11C语言程序设计张大海蓝天2004.826.30G12.08C语言程序设计梁晓峰青山2004.1022.00G12.09C语言程序设计梁晓峰青山2004.1022.001/12/2023

关系的特点：关系的每一行定义实体集的一个实体，每一列定义实体的一个属性。每一行必须有一个主码，主码是一个属性组（可以是一个属性），它能惟一的标识一个实体。每一列表示一个属性，且列名不能重复。列的每个值必须与对应属性的类型相同。列有取值范围，称为域。列是不可分割的最小数据项。行、列的顺序对用户无关紧要。1/12/2023关系中的术语候选码（CandidateKey）

若关系中的某一属性组（或单个属性）的值能惟一的标识一个元组，则称该属性组（或属性）为候选码。选择一个候选码作为关系的主码。主属性（PrimeAttribute）和非主属性关系中包含在任何一个候选码中的属性称为主属性。不包含在任何一个候选码中的属性称为非主属性。1/12/20232.1.2关系代数

关系代数是一种抽象的查询语言。从数学的角度来看，关系是一个集合，关系的元组是集合的元素。因此，关系代数包括集合运算：交、并、差、乘，还有关系专有的运算：选择、投影、连接、除。并(union)

设R和S是两个关系，它们的并运算是将两个关系中的所有元组构成一个新关系，即由属于R或属于S的元素组成，该关系用R∪S，也可以记做R+S。

1/12/2023

【例2-1】有在职职工和离退修职工两个表，若需要所有职工的表，可以利用并运算实现。表2-3在职职工表Ｒ

工号姓名性别状态106001张春来男1103026马宏明男1105008王红女1表2-4离退修职工表S

工号姓名性别状态106008李建新男0104012王红女01/12/2023工号姓名性别状态106001张春来男1103026马宏明男1105008王红女1106008李建新男0104012王红女0表2-4新关系Ｒ∪S1/12/2023差（difference）

设R和S是两个关系，R和S的差运算是由属于R而不属于S的所有元组构成新关系，也就是从关系R中减去关系S中的元组，该关系用R-S。差运算也要求两个关系对应属性的性质（属性名、属性的域等）必须一致。

【例2-2】某商店有本店商品表，接到不合格商品表后，需要将本店中的不合格商品去掉。可以利用差运算实现。品牌名称厂家106001奶粉天南103026奶粉地北205008白糖南山204045白糖北山302034食盐西山表2-6本店商品表Ｒ

1/12/2023表2-7

不合格商品表Ｓ

品牌名称厂家103026奶粉地北4023037

火腿

西山

204045白糖北山表2-8新关系Ｒ－Ｓ

品牌名称厂家106001奶粉天南205008白糖南山302034食盐西山1/12/2023交（intersection）

设R和S是两个关系，R和S的交运算是由属于R并且也属于S的所有元组构成的一个新关系，即由关系R和关系S中公共的元组构成的新关系。该关系用R∩S。交运算也要求两个关系对应属性的性质（属性名、属性的域等）必须一致。

【例2-3】在【例2-2】中，找出本店内不合格的商品，可以用交运算实现。品牌名称厂家103026奶粉地北204045白糖北山表2-9新关系Ｒ∩Ｓ

1/12/2023

乘（product）

设R和S是两个关系，R和S的乘运算是一个新关系（记为R×S）结果是两个关系中所有元组按一定规则组合而成。关系R有m个属性，关系S有n个属性，则R×S由（m+n）个属性；R有i个元组，S有j个元组，则R×S有（i×j）个元组；每个元组的前m个属性是R的一个元组，后n个属性是S的一个元组。

说明：两个关系（集合）的乘运算，在集合论中也称为广义笛卡尔积。所以关系的乘运算也称为广义笛卡尔积。

【例2-4】现有学生表、必修课程表，每个学生必须学习所有必修课程，要求形成选课表。可以利用关系乘运算实现。

1/12/2023学号姓名0406001王晓立0406002张大卫表2-10学生表Ｒ

表2-11

必修课程表Ｓ

课程号课程名学分C601数据库3C602操作系统2C603数学51/12/2023表2-12新关系Ｒ×Ｓ

学号姓名课程号课程名学分0406001王晓立C601数据库

30406001王晓立C602操作系统20406001王晓立C603

数学5

0406002张大卫C601

数据库

30406002张大卫C602操作系统

20406002张大卫C603

数学51/12/2023投影（projection）

关系R的投影运算是从R中选择某些属性的所有值组成的新关系。换言之，投影运算的结果是一个表的垂直方向的子集。关系R的投影运算记为：πA(R)。其中，A为R的一组属性列。投影的结果将消除重复的元组。

【例2-5】在表2-5所示的关系R∪S中①列出所有职工的名单，可以通过投影运算实现；

姓名张春来马宏明王红李建新π姓名(R∪S)

1/12/2023②列出职工姓名及其任职状态，可以通过投影运算实现。姓名状态张春来1马宏明1王红1李建新0王红0π姓名，状态(R∪S)π姓名(R∪S)可记为π2(R∪S)，π姓名，状态(R∪S)可记为π2,4(R∪S)。注意：π姓名(R∪S)运算的结果将同名的两个“王红”去掉重复，只保留一个。

1/12/2023选择（selection）

关系R的选择运算是从关系R中选择满足指定条件（用F表示）的元组构成的新关系。换言之，选择运算的结果是一个表的水平方向的子集。关系R的选择运算记为：σF（R）。其中，F是包括属性名的逻辑表达式，运算符有算术比较运算符：＜，≤，＞，≥，＝，≠，和逻辑运算符：┐，∨，∧（非、或、与）。

【例2-6】

①在表2-11中求出学分大于等于3的课程信息。表2-13σ学分≥3(R∪S)课程号课程名学分C601数据库3C603数学51/12/2023②在表2-4中求所有在职男职工名单。

σ性别=‘男’∧状态=1(R∪S)工号姓名性别状态106001张春来男1103026马宏明男11/12/2023连接（join）

关系R与关系S的连接运算是从两个关系的广义笛卡尔积中选取属性间满足一定条件的元组形成一个新关系。记作：

其中：A为包含R中的属性的表达式

B为包含S中属性的表达式

θ通常为关系比较符。等值连接（equaljoin）θ为“＝”的连接运算称为等值连接。关系R和S的等值连接是从R和S的广义笛卡尔积R×S中选取A与B等值的那些元组，形成的关系。S

RAθB1/12/2023自然连接（naturaljoin）

关系R和S的自然连接是一种特殊的等值连接，它要求关系R和S中进行比较的分量必须是相同的属性组的一种连接，并且在结果中把重复的属性列去掉（只保留一个）。自然连接记为：一般的连接运算是从行的角度进行的。但自然连接还需要取消重复列，所以自然连接是同时从行和列的角度进行运算。自然连接是构造新关系的有效方法，是关系代数中常用的一种运算，在关系数据库理论中起着重要作用。利用投影、选择和自然连接运算可以任意地分解和构造关系。一般地，自然连接使用在R和S有公共属性的情况中。如果两个关系没有公共属性，那么它们的自然连接就转化为广义笛卡尔积。S

R

1/12/2023

【例2-7】设图书借阅信息如表2-15所示，图书信息如表2-16所示，求各读者借阅图书的信息。

读者编号图书编号借期还期2004060002F12.2452004-6-292002060328F23.552004-5-262004-12-222004160426G11.112004-10-212002060328G12.082004-8-262002060328G11.222004-9-012004160426G12.102004-9-142004-11-08表2-15借阅信息表borrowinf1/12/2023编号书名作者出版社出版日期定价F12.245计算机文化基础刘凌志青山2002.521.80F33.33数据结构实用教程王云晓蓝天2002.522.00G22.12VisualBasic实用教程梁晓峰碧水2003.1028.00G11.22Java程序设计实用教程张大海碧水2004.720.80F23.55数据结构（C语言版）马志刚青山2004.724.50G11.11C语言程序设计张大海蓝天2004.826.30G12.08C语言程序设计梁晓峰青山2004.1022.00G12.10C语言程序设计梁晓峰青山2004.1022.00表2-16图书信息表books1/12/2023求各读者借阅图书的信息,可通过等值连接实现：

borrowinf

books

图书编号=编号

结果为:读者编号图书编号借期还期书名作者出版社出版日期定价2004060002F12.2452004-6-29计算机文化基础刘凌志青山2002.521.802002060328F23.552004-5-262004-12-22数据结构(C语言版)马志刚青山2004.728.002004160426G11.112004-10-21C语言程序设计张大海蓝天2004.82002060328G12.082004-8-26C语言程序设计梁晓峰青山2004.102002060328G11.222004-9-01Java程序设计实用教程张大海碧水2004.720.802004160426G12.102004-9-142004-11-08C语言程序设计梁晓峰青山2004.1022.001/12/2023除（divide）

设关系R的属性可以分成互不相交的两组，用X、Y表示（X∩Y为空集，X∪Y为R的全部属性），则关系R可以表示为：R(X,Y)。

设R(X,Y)和S(Y,Z)是两个关系，其中R中的Y和S中的Y可以有不同的属性名，但必须出自相同的域。R与S的除运算是一个新关系P(X)（记为R÷S）,它的属性为属性组X，设S在Y上的投影πy(S)有j个元组，R÷S由所有满足以下条件的元素构成：(1)R中存在若干元组构成的子集R’（也是关系），该关系在Y上的投影πy(R’)包含关系S在Y上的投影πy(S)；(2)πx(R’)中（去掉重复后）只有一个元素x，则x是R÷S中的一个元素（元组）。1/12/2023

【例2-8】已知学生选课表R，要找出同时选修课号为C601、C605两门课程的学生的学号。指定的课程号和对应课程名构成的关系记为S，则该问题可以用R÷S表示。学号课程号0406001C6010406001C6020406001C6050406002C6020406002C6050406002C6030406003C6050406003C601选课表

R课程号课程名C601数据库C605软件工程S学号

04060010406003R÷S1/12/20232.1.3关系代数与数据查询

在关系代数中，关系代数运算经过有限次复合而成的式子称为关系代数表达式，它的运算结果仍然是一个关系。可以用关系代数表达式表示对数据库的查询和更新操作。

【例2-9】以图书借阅信息表（表2-15）、图书信息表（表2-16）为例，完成下列任务：(1)每本图书的编号和书名；(2)每位读者的编号和所借图书的书名；(3)2004年8月以前的图书未还情况读者编号和所借图书书名；(4)编号为：2002060328的读者所借图书信息。1/12/2023每本图书的编号和书名；解:R1=π编号,书名（books）编号书名F12.245计算机文化基础F33.33数据结构实用教程G22.12VisualBasic实用教程G11.22Java程序设计实用教程F23.55数据结构（C语言版）G11.11C语言程序设计G12.08C语言程序设计G12.10C语言程序设计R11/12/2023(2)每位读者的编号和所借图书的书名；解:R2=π读者编号，书名(borrowinfbooks)

图书编号=编号

读者编号书名2004060002计算机文化基础2002060328数据结构(C语言版)2004160426C语言程序设计2002060328C语言程序设计2002060328Java程序设计实用教程2004160426C语言程序设计R21/12/2023(3)2004年8月以前的图书未还情况读者编号和所借图书书名；解:

R3=π读者编号,书名(σ借期≤’2004-08-01’∧还期=NULL(borrowinf)books)

图书编号=编号读者编号书名2004060002计算机文化基础R31/12/2023(4)编号为2002060328的读者所借图书信息。解:R4=σ读者编号=’2002060328’

(borrowinfbooks)

图书编号=编号

读者编号图书编号借期还期书名作者出版社出版日期定价2002060328F23.552004-5-262004-12-22数据结构(C语言版)马志刚青山2004.728.002002060328G12.082004-8-26C语言程序设计梁晓峰青山2004.102002060328G11.222004-9-01Java程序设计实用教程张大海碧水2004.720.80R41/12/20232.2E-R模型到关系模型的转换

E-R模型可以向现有的各种数据库模型转换，对不同的数据库模型有不同的转换规则。这里只讨论E-R模型向关系模型的转换方法。E-R模型向关系模型转换的规则是：实体一个实体类型转换成一个关系模式。实体的属性就是关系的属性，实体的码就是关系的码。

E-R模型向关系模型转换时，除了将实体转换为关系外，还要考虑如何将实体之间的联系正确的转换为关系。一对一联系

（1：1）一般是将联系与任意一端实体所对应的关系模式合并，需要在该关系模式的属性中加入另一个实体的码和联系本身的属性。1/12/2023

【例2-10】实体部门（编号，名称）与实体经理（工号，姓名）之间的任职联系是1:1联系。E-R模型如图2-1所示。将其转换为关系模型。11部门经理任职名称工号编号任职日期姓名11部门（编号，名称）经理（工号，姓名，编号，任职日期）图2-11/12/2023一对多（1:n）联系一般是将该联系与n端实体所对应的关系模式合并。合并时需要在n端实体的关系模式的属性中加入1端实体的码和联系本身的属性。

【例2-11】

将授课系统中的E-R模型转换为关系模型。教师课程参考书讲授1n11/12/2023该E-R模型中有3个实体和1个多元联系。可以用两种方法转换为关系模型：1)

将联系转换为一个单独的关系。则该E-R模型对应的关系模型包含4个关系模式：

课程（课号，课名，学分）教师（工号，姓名，职称）参考书（书号，书名）讲授（工号，课号，书号）

2)由于该例中的联系是一对多联系，所以可以将联系合并到实体对应的关系中。课程（课号，课名，学分）教师（工号，姓名，职称，课号）参考书（书号，书名，课号）1/12/2023多对多(m:n)联系将联系转换成一个关系模式。与该联系相连的各实体的码及联系本身的属性转换为关系的属性，而关系的码为各实体码的组合。

【例2-12】将学生选课系统的E-R模型转换为关系模型。

课程号课程名学分课程学号所在系年龄姓名性别学生成绩选课mn学生（学号，姓名，性别，年龄，所在系）选课（学号，课程号，成绩）

课程（课程号，课程名，学分）1/12/2023【例2-13】将图书借阅系统的E-R模型转换为关系模型。

读者编号姓名读者类型已借数量图书出版日期出版社编号书名定价借期还期借阅mn读者（编号，姓名，读者类型，已借数量）借阅（读者编号，图书编号，借期，还期）图书（编号，书名，出版社，出版日期，定价）1/12/20232.3关系模型的三类完整性约束

2.3.1实体完整性

实体完整性规则:

若属性A是关系R的主属性，则A不能取空值。2.3.2参照完整性规则

外码和参照关系设F是基本关系R的一个或一组属性，但不是关系R的主码（或候选码）。如果F与基本关系S的主码KS相对应，则称F是基本关系R的外码,并称基本关系R为参照关系,称基本关系S为被参照关系。例如：在授课系统的关系模型中：课程（课号，课名，学分）课号关系课程的主码教师（工号，姓名，职称，课号）课号是关系教师的外码参考书（书号，书名，课号）关系教师是参照关系，关系课程是被参照关系。1/12/2023参照完整性规则

参照完整性规则:若属性（或属性组）F是基本关系R的外码，它与基本关系S的主码KS相对应，则对于R中的每个元组在F上的值必须满足：或者取空值（F的每个属性均为空值），或者等于S中某个元组的主码值。

例如，在授课系统的关系模型中，关系教师中的外码“课号”只能是下面两类值：(1)空值。表示还未给该教师安排课。(2)非空值，但此值必须为被参照关系课程中某一门课程的“课号”。

注意：在实际应用中，外码不一定与对应的主码同名。在关系数据库中，表与表之间的联系是通过公共属性实现的。这个公共属性往往是一个表的主码，同时是另一个表的外码。1/12/20232.3.3用户定义的完整性用户定义的完整性就是针对某一具体要求来定义的约束条件，它反映某一具体应用所涉及的数据必须满足的语义要求。例如，某个属性必须取惟一值；某些属性之间应满足一定的函数关系；某个属性的取值范围在0—400之间等。关系模型应提供定义和检验这类完整性的机制，以便系统用统一的方法处理它们，而不需要由应用程序来承担这一功能。所以，用户定义的完整性通常是定义属性取值的约束，即对属性的值域的约束。对属性的值域的约束也称为域完整性规则是指对关系中属性取值的正确性限制。包括数据类型、精度、取值范围、是否允许空值等。1/12/20232.3.4完整性规则检查为了维护数据库中数据的完整性，在对关系数据库执行插入、删除和修改操作时，就要检查是否满足以上三类完整性规则。当执行插入操作时

首先检查实体完整性规则，插入行的主码属性上的值，是否已经存在。若不存在，可以执行插入操作；否则不可以执行插入操作。

再检查参照完整性规则，如果是向被参照关系插入，不需要考虑参照完整性规则；如果是向参照关系插入，插入行在外码属性上的值是否已经在相应被参照关系的主码属性值中存在。若存在，可以执行插入操作；否则不可以执行插入操作，或将插入行在外码属性上的值改为空值后再执行插入操作(假定该外码允许取空值)。

最后检查用户定义完整性规则，检查被插入的关系中是否定义了用户定义完整性规则，如果定义了，检查插入行在相应属性上的值是否符合用户定义完整性规则。若符合，可以执行插入操作；否则不可以执行插入操作。1/12/2023当执行删除操作时

一般只需要检查参照完整性规则。如果是删除被参照关系中的行，则应检查被删除行在主码属性上的值是否正在被相应的参照关系的外码引用，若没被引用，可以执行删除操作，若正在被引用，有三种可能的做法：不可以执行删除操作(拒绝删除)，或将参照关系中相应行在外码属性上的值改为空值后再执行删除操作(空值删除)，或将参照关系中相应行一起删除(级联删除)。当执行修改操作时因为修改操作可看成先执行删除操作，再执行插入操作，因此是上述两种情况的综合。1/12/20232.4案例1：活期储蓄管理系统数据库设计

问题的提出

在活期储蓄管理过程中，“储户”通过“存取款”与“储蓄所”发生业务联系。试设计活期储蓄管理系统数据库的概念模型、关系模型并确定约束。

系统需求分析通过对数据库用户(储蓄所业务人员)的调查，对用户的需求进行分析得知：储户是指在某个储蓄所开户的人。该储蓄所称为储户的开户行。一个储蓄所可以有多个储户，每个储户有惟一的账号。每个储户可以在多个允许发生业务的储蓄所进行存取款。储户按信誉分为“一般”和“良好”两种。信誉“一般”的储户不允许透支。信誉“良好”的储户可以透支，但不能超过5万元。储户按状态分为：“正常”和“挂失”两种（分别用0、1表示）。状态为“正常”的储户允许存取款，状态为“挂失”的储户不允许存取款。1/12/2023储户的信息有：账号、姓名、密码、电话、地址、信誉、存款额、开户行编号、开户日期、状态等。储蓄所的信息有：编号、名称、电话、地址、负责人储户进行存取款时应该提供账号、存取标志(1：表示存款，0：表示取款)、存取金额、存取日期等信息，储蓄所首先要对储户的身份进行验证，对合法的储户再根据“信誉”判断是否可以发生此次业务。若发生业务则记录相应信息，修改储户的存款额。概念模型设计根据需求分析得知，此系统涉及“储户”和“储蓄所”两个实体集。两个实体