数据库原理与MySQL应用-4 关系模型基本原理

第4章关系模型基本原理4.1关系模型基本概念数据库原理与设计2（1）关系（Relation）：是用于描述数据的一张二维表，组成表的行称为元组，组成表的列称为属性。4.1.1基本术语（2）域（Domain）：指属性的取值范围。（3）超键（SuperKey）：能唯一的标识关系中每一个元组的属性或属性集。（4）候选键（CandidateKey）：也称为候选码。如果一个属性集能唯一的标识一个元组，且又不含有多余的属性，则这个属性集称为关系的候选键。（5）主键（PriamaryKey，PK）：也称为主码。一个唯一识别关系中元组的最小属性集合。可以从关系的候选键中，指定其中一个作为关系的主键。一个关系最多只能指定一个主键。要求作为主键的列不允许取NULL值。（6）全码（ALL-key）：关系中所有属性的组合是该关系的一个候选码，则该候选码称为全码。（7）外键（ForeignKey，FK）：关系R中的某个属性k是另一个关系S中的主键，则称该属性k是关系R的外键。通过外键可以建立两个关系间的联系。（1）关系中元组的位置具有顺序无关性，即元组的顺序可以任意交换。4.1.2关系的特征（2）同一属性的数据具有同质性，即每一列中的分量是同一类型的数据，来自同一个域。（3）同一关系的属性名具有不可重复性，即同一关系中不同属性的数据可出自同一个域，但不同的属性要给予不同的属性名。（4）各列的顺序在理论上是无序的，即列的次序可以任意互换，但使用时按习惯考虑列的顺序。（5）关系中任意两个元组不能完全相同，即任意两个元组的侯选键不能相同。（6）关系中每个分量必须取原子值，即每一个分量都必须是不可分的数据项。s_idf_idf_namef_price101a1apple5.2b1blackberry10.2102bs1orange11.2105bs2melon8.2不是原子值4.2数据库完整性数据库原理与设计7

数据库完整性是指数据库中数据在逻辑上的一致性、正确性、有效性和相容性。什么是数据库完整性？为了维护数据库的完整性，DBMS必须能够：（1）提供定义完整性约束条件的机制（2）提供完整性检查的方法（3）违约处理学号姓名性别家庭住址1001张三男北京市1002李四女西安市1001张三男北京市……………………学生关系中重复行会带来的危害？如何避免在关系中输入重复行,确保数据的唯一性？数据冗余数据的不一致设置实体完整性4.2.1三类完整性规则学号姓名性别家庭住址1001张三男北京市1002李四女西安市……………………学生如何确保选课关系中的学号应取学生关系中学号的值？设置参照完整性选课学号课号成绩1001C01951002C0190………………如何约束成绩取值在0-100之间？设置用户定义完整性选课学号课号成绩1001C01951002C0190………………水果订单明细(orderitems)1.实体完整性实现：设置主键约束（Primarykey）。要求：主键值唯一，且各个属性都不能为空值。水果表（fruits）2.参照完整性实现：设置外键约束（Foreignkey）。要求：1)外键或者取空值，或者等于被参照关系中的

主键的某个值。

2)被参照关系的列必须设置为主键供应商表（suppliers）3.用户定义完整性实现：属性的取值应当满足用户定义的约束条件。

设置检查约束（Check）。水果表（fruits）4.2.2MySQL约束控制1.非空（NOTNULL)约束字段名数据类型NOTNULL|NULL【说明】（1）字段默认状态为允许取空值，也可以通过NULL关键字显式的指明。（2）使用了非空约束的字段，如果用户在添加数据时没有给定值，数据库管理系统将会报错。【例4-2】建立非空约束示例。CREATETABLEfruits(f_idCHAR(10)NOTNULL,s_idINTNOTNULL,f_nameCHAR(255)NULL,f_priceDECIMAL(8,2));

DESCfruits;2．主键（PRIMARYKEY）约束作为主键的字段必须满足两个条件：（1）值唯一；（2）不能为空值。主键约束分为列级和表级两种定义方式。（1）列级针对表中的一列，（2）表级针对同一表中的一列或多列。【例4-3】建立主键约束示例。CREATETABLEfruits(f_idCHAR(10)NOTNULLPRIMARYKEY,s_idINTNOTNULL,f_nameCHAR(255)NULL,f_priceDECIMAL(8,2));（1）列级主键约束（2）表级PRIMARYKEY约束CREATETABLEfruits(f_idCHAR(10)NOTNULL,s_idINTNOTNULL,f_nameCHAR(255)NOTNULL,f_priceDECIMAL(8,2),

PRIMARYKEY(s_id,f_name));CREATETABLEfruits(f_idCHAR(10)NOTNULL,s_idINTNOTNULLPRIMARYKEY,f_nameCHAR(255)NOTNULLPRIMARYKEY,f_priceDECIMAL(8,2));这个创建语句是否正确？【例4-4】修改主键约束示例。ALTERTABLEfruits

DROPPRIMARYKEY;（1）删除主键约束（2）为已有表添加主键约束ALTERTABLEfruits

ADDPRIMARYKEY(f_id);3．唯一（UNIQUE）约束要求作为候选键的字段满足2个条件：（1）值唯一；（2）可有一个且仅有一个空值。唯一约束既可以在列级定义，也可以在表级定义。【例4-5】唯一约束示例。CREATETABLEsuppliers(s_idintNOTNULLPRIMARYKEY,s_namechar(50)NOTNULL,s_citychar(50)NULL,s_zipchar(10)NULL,s_callCHAR(50)NOTNULLUNIQUE);（1）创建suppliers表，为s_call字段定义唯一约束。

一个表中可以创建多个唯一约束，为了便于管理，可以采用表级方式，通过CONSTRAINT关键字为每个唯一约束指定名称。CREATETABLEsuppliers(s_idintNOTNULLPRIMARYKEY,s_namechar(50)NOTNULL,s_citychar(50)NULL,s_zipchar(10)NULL,s_callCHAR(50),

CONSTRAINTcall_UQUNIQUE(s_call));ALTERTABLEsuppliersDROPINDEXcall_UQ;（2）删除唯一约束call_UQ。（3）为已有表suppliers根据s_call字段创建唯一约束，约束名为call_UQ。ALTERTABLEsuppliersADDCONSTRAINTcall_UQUNIQUE(s_call);【例4-6】检查约束示例。CREATETABLEemployee(enoDECIMAL(2)PRIMARYKEY,enameVARCHAR(8),ageDECIMAL(3)CONSTRAINTage_CKCHECK(age>20ANDage<60),deptnoDECIMAL(2),addressVARCHAR(30));（1）建立employee表，限制age字段的值必须大于20且小于60。4．检查（CHECK）约束INSERTINTOemployee(eno,ename,age)VALUES(11,'MARY',10);（2）删除检查约束age_CK。ALTERTABLEemployee

DROPCONSTRAINTage_CK;（3）为已有表employee添加CHECK约束，限制每条记录age字段的值必须大于20小于60，而且address字段值以‘北京市’开头。ALTERTABLEemployee

ADDCONSTRAINTage_adrr_CKCHECK(age>20ANDage<60ANDaddressLIKE'北京市%');5．外键（FOREIGNKEY）约束以供应商表和水果表为例：供应商表（供应商编号，名称，所在城市，邮编，电话）

水果表（水果编号，名称，单价，供应商编号）主（父）表从（子）表主键外键对主表主键进行INSERT、DELETE、UPDATE操作，会对从表有什么影响呢？（1）插入（INSERT）主表中主键值的插入，不会影响从表中的外键值。（2）修改（UPDATE）

如果从表中的外键值与主表中的主键值一样，则对主表中主键值的修改将影响到从表中的外键值。（3）删除（DELETE）

主表中主键值的删除，可能会对从表中的外键值产生影响，除非主表中的主键值没有在从表中的外键值中出现。对从表外键进行INSERT、DELETE、UPDATE操作，又会对主表有什么影响呢？（1）插入（INSERT）

插入从表的外键值时，要求插入的外键值应“参照”主表中的主键值。（2）修改（UPDATE）

修改从表的外键值时，要求修改的外键值需“参照”主表中的主键值。（3）删除（DELETE）

从表中元组的删除不需要参照主表中的主键值。定义外键约束表级语法格式：FROEIGNKEY(字段[,字段…])

REFERENCES主表(字段[,字段…])[ONDELETECASCADE|SETNULL|NOACTION][ONUPDATECASCADE|SETNULL|NOACTION]（1）CASCADE：主表记录的删除或者修改操作，会自动删除或修改子表中与之对应的记录。（2）SETNULL：主表记录的删除或者修改操作，会将子表中与之对应记录的外健值自动设置为NULL。（3）NOACTION：主表记录的删除或者修改操作，如果子表中存在与之对应的记录，则删除或修改操作将被禁止执行。【例4-7】外键约束示例。CREATETABLEsuppliers(

s_idintNOTNULLPRIMARYKEY,s_namechar(50)NOTNULL,s_citychar(50)NULL,s_zipchar(10)NULL,s_callCHAR(50)NOTNULL);（1）建立fruits和suppliers表，实现两表间的外键约束，并指定为级联更新。CREATETABLEfruits(f_idchar(10)NOTNULLPRIMARYKEY,s_idINTNOTNULL,f_namechar(255)NOTNULL,f_pricedecimal(8,2)NOTNULL,

CONSTRAINTsid_FKFOREIGNKEY(s_id)REFERENCESsuppliers(s_id)ONUPDATECASCADE);CREATETABLEfruits(f_idchar(10)NOTNULLPRIMARYKEY,s_idINTREFERENCESsuppliers(s_id)ONUPDATECASCADE,f_namechar(255)NOTNULL,f_pricedecimal(8,2)NOTNULL);表级列级（2）删除fruits表上的sid_FK约束。ALTERTABLEfruitsDROPCONSTRAINTsid_FK;（3）为fruits表设置与suppliers表的外键约束，并指定为级联删除和级联更新。ALTERTABLEfruitsADDCONSTRAINTsid_FKFOREIGNKEY(s_id)REFERENCESsuppliers(s_id)ONDELETECASCADEONUPDATECASCADE;6.自增（AUTO_INCREMENT）约束

默认情况下，在MySQL中AUTO_INCREMENT的初始值是1，每新增一条记录，字段值自动加1。

一个表只能有一个字段使用AUTO_INCREMENT约束，且该字段必须为主键的一部分。

AUTO_INCREMENT约束的字段可以是任何整数类型，如TINYINT、SMALLINT、INT、BIGINT等。【例4-8】自增约束示例。CREATETABLEsuppliers(s_idintNOTNULLAUTO_INCREMENTPRIMARYKEY,s_namechar(50)NOTNULL,s_citychar(50)NULL);建立suppliers表，为s_id字段定义自动增长约束。INSERTINTOsuppliers(s_name,s_city)VALUES('FastFruitInc.','Tianjin'),('LTSupplies','Chongqing'),('ACME','Shanghai');【例4-9】默认值约束示例。CREATETABLEorders(o_numINTNOTNULLAUTO_INCREMENTPRIMARYKEY,o_dateDATETIMEDEFAULT(CURDATE()),c_idINT);

创建orders表，为o_date字段定义默认值约束，默认值为当前系统日期。INSERTINTOorders(o_num,c_id)VALUES(30001,10001),(30002,10003);7.默认值（DEFAULT）约束总结：数据库完整性实体完整性参照完整性用户定义完整性主键约束（primarykey）唯一约束（unique）外键约束（foreignkey）检查约束（check）4.3关系代数数据库原理与应用38

关系代数中的操作可以分为以下两类。（1）传统的集合运算，包括并、交、差。（2）专门的关系运算，包括对关系进行垂直分割（投影）、水平分割（选择）、关系的联合（连接、自然连接）等。

一个或两个关系经过关系运算后的结果仍然是一个关系。4.3.1关系代数的基本操作1．并（Union）关系R和S具有相同的属性个数n，且相应的属性取自同一个域。R∪S={t|t∈R∨t∈S}【说明】（1）t为元组变量；（2）逻辑运算符包括逻辑与∧、逻辑或∨、逻辑非﹁

关系的并操作对应于关系“插入”记录的操作，俗称为“+”操作。【例4-10】设有关系R和S如下，计算R∪S。2．差（Difference）关系R和S具有相同的属性个数n，且相应的属性取自同一个域。R-S={t|t∈R∧tS}

关系的差操作对应于关系的“删除”记录的操作，俗称为“－”操作。例如：设有关系R和S如下，计算R-S。3．笛卡儿集（CartesianProduct）

设关系R和S的属性个数（即列数）分别为r和s，R和S的笛卡儿积是一个（r+s）列的元组集合，每个元组的前r列来自R的一个元组，后s列来自S的一个元组，若R有k1个元组，S有k2个元组，则关系R和关系S的笛卡尔积有k1×k2个元组。

R×S=

关系的笛卡儿积操作对应于两个关系记录横向合并的操作，俗称“×”操作。设有关系R和S如下，计算R×S。4．投影（Projection）

关系R上的投影是从R中选择出若干属性列组成新的关系。∏A(R)={t[A]|t∈R}其中，A为R中的属性列

投影操作是对一个关系进行垂直分割，消去某些列，并重新安排列的顺序。在MySQL中用SELECT短语实现。例如：设有关系R，投影R关系的f_price列和f_id列。

∏f_price,f_id(R)或

∏3,1(R)5．选择（Selection）

关系R上的选择操作是从R中选择符合条件的元组。ðF(R)={t|t∈R∧F(t)=true}

选择操作是对一个关系进行水平分割，消去某些行。在MySQL中用WHERE短语实现。F表示选择条件，是一个逻辑表达式。（1）运算对象。可以是常数，或属性名或列的序号。如ð3≥10(R)（2）运算符。比较运算符（<，≤，>，≥，=，≠，也称为θ符）、逻辑运算符（逻辑与∧，逻辑或∨，逻辑非﹁）。设有关系R，计算ðf_price≥10(R)。ðf_price≥10(R)4.3.2关系代数的4个组合操作1．交（Intersection）关系R和S具有相同的属性个数n，且相应的属性取自同一个域。R∩S={t|t∈R∧t∈S}关系的交可以用差来表示，即R∩S=R-{R-S}。

关系的交操作对应于寻找两关系共有记录的操作，是一种关系“查询”操作。设有关系R和S如下，计算R∩S。2．连接（Join）从两个关系的笛卡儿积中选取属性值满足某一θ操作的元组。R∞S=ðAθB(R×S)AθB例：计算:R3=CC#CNAMETEACHERk1C语言王华k3编译原理李红SCS#C#GRADE1k1832k389R3C.C#CNAMETEACHERES#SC.C#GRADEk1C语言王华1k183k3编译原理李红2k389等值连接例：计算:R4=CC#CNAMETEACHERk1C语言王华k3编译原理李红SCS#C#GRADE1k1832k389如果θ是等号“＝”，该连接操作称为“等值连接”。R∞S=ðA=B(R×S)A=B1=5R4C.C#CNAMETEACHERES#SC.C#GRADEk1C语言王华1k183k3编译原理李红2k389自然连接

例：计算:R5=R5C#CNAMETEACHERES#GRADEk1C语言王华183k3编译原理李红289CC#CNAMETEACHERk1C语言王华k3编译原理李红SCS#C#GRADE1k1832k389

自然连接是一种特殊的等值连接，它要求两个关系中进行比较的分量必须是相同的属性组，并且要在结果中去掉重复的属性。

（1）分量

每个元组的各属性值分别为一个分量。3．除（Division）CC#CNAMET#k1C语言王华k3编译原理李红第一行元组的分量有三个，分别是：k1、C语言、王华。

（2）象集

x在关系R中的象集为Zx，它表示关系R中X分量等于x的元组集合在属性集Z上的投影。CC#CNAMET#k1C语言王华k3编译原理李红k1数据库王华例如，取T#为“王华”的元组在CNAME列上的象集。CNAMEC语言数据库CNAMET#='王华'象集的实质就是一次选择运算和一次投影运算。关系的除法运算分为如下4个步骤：（1）将被除关系的属性分为象集属性和结果属性：与除关系相同的属性属于象集属性，不相同的属性属于结果属性。（2）在除关系中，对与被除关系相同的属性（象集属性）进行投影，得到除目标数据集。（3）将被除关系分组，原则是，结果属性值一样的元组分为一组。（4）逐一考察每个组，如果它的象集属性值中包含除目标数据集，则对应的结果属性值则属于该除法运行结果集。3．除（Division）

计算R÷S的操作步骤为：（1）将关系R和关系S的属性进行划分，即R(X,Y)、S(Y,Z);（2）若X的某个值x的像集Yx包含S关系中Y的所有元组，则将x放入结果集中。ABCa1b1c2a2b3c5a3b4c4a1b2c3a2b2c3a1b2c1BCDb1c2d1b2c1d1b2c3d2ASR÷SR【例4-11】设关系R、S分别如下，求R÷S的结果。a1例如：检索选修全部课程的学生学号？C#CNAMETEACHERk1C语言王华k2数据库原理李红k3编译原理李红S#C#GRADE1k1832k1882k2853k3922k389CSC∏S#,C#(SC)÷∏C#(C)说明：除操作适合于包含“对于所有的/全部的”语句的查询操作。

【例4-13】有如下3个关系，用关系代数表达式实现下列每个查询语句及对应的SQL查询语句。S#SNAMEAGESEX1钱衡23男2刘丽22女3张友22男CC#CNAMET#c1C语言王华c2数据库原理李红c3编译原理李红SCS#C#SCORE1c1832c2853c3922c389SCSC（1）检索学习课程号为C2课程的学生学号与成绩。∏S#，SCORE（ðC#=’C2’（SC））4.3.3关系代数操作实例SELECTs#,scoreFROMSCWHEREc#=’C2’;S#SNAMEAGESEX1钱衡23男2刘丽22女3张友22男CC#CNAMET#c1C语言王华c2数据库原理李红c3编译原理李红SCS#C#SCORE1c1832c2853c3922c389SCSC（2）检索学习课程号为C2课程的学生学号与姓名。∏S#，SNAME（ðC#=’C2’（S∞SC））SELECTs.s#,snameFROMS,SCWHEREs.s#=sc.s#ANDc#=’C2’;S#SNAMEAGESEX1钱衡23男2刘丽22女3张友22男CC#CNAMET#c1C语言王华c2数据库原理李红c3编译原理李红SCS#C#SCORE1c1832c2853c3922c389SCSC（3）检索选修课程名为数据库原理的的学生的学号与姓名。∏S#，SNAME（ðCNAME=’数据库原理’（S∞SC∞C））SELECTs.s#,snameFROMS,SC,CWHEREs.s#=sc.s#ANDsc.c#=c.c#ANDcname=’数据库原理’;S#SNAMEAGESEX1钱衡23男2刘丽22女3张友22男CC#CNAMET#c1C语言王华c2数据库原理李红c3编译原理李红SCS#C#SCORE1c1832c2853c3922c389SCSC（4）检索选修课程号为C2或C3的学生学号。∏S#（ðC#=’C2’∨C#=’C3’（SC））;SELECTs#FROMscWHEREc#=’C2’ORc#=’C3’;S#SNAMEAGESEX1钱衡23男2刘丽22女3张友22男CC#CNAMET#c1C语言王华c2数据库原理李红c3编译原理李红SCS#C#SCORE1c1832c2853c3922c389SCSC（5）检索至少选修课程号为C2和C3的学生学号。∏1（ð1=4∧2=’C2’∧5=’C3’（SC×SC））SELECTs1.s#FROMSCS1,SCS2WHEREs1.s#=s2.s#ANDs1.c#='C2'ANDs2.c#='C3';S#C#SCORES#C#SCORE1c1831c1831c1832c2851c1833c3921c1832c3892c2852c389S#SNAMEAGESEX1钱衡23男2刘丽22女3张友22男CC#CNAMET#c1C语言王华c2数据库原理李红c3编译原理李红SCS#C#SCORE1c1832c2853c3922c389SCSC（6）检索没有选修C2课程的学生姓名。∏SNAME（S）－∏SNAME（ðC#=’C2’（S∞SC））SELECTsnameFROMSWHEREs#NOTIN(SELECTs#FROMSCWHEREc#='C2');S#SNAMEAGESEX1钱衡23男2刘丽22女3张友22男CC#CNAMET#c1C语言王华c2数据库原理李红c3编译原理李红SCS#C#SCORE1c1832c2853c3922c389SCSC（7）检索选修了全部课程的学生姓名。∏SNAME（S∞（∏S#，C#（SC）÷∏C#（C）））SELECTsnameFROMSWHERENOTEXISTS(SELECTc#FROMCWHERENOTEXISTS(SELECTc#FROMSCWHEREs.s#=sc.s#ANDsc.c#=c.c#));（1）首先确定用到的关系，如果多个关系则进行自然连接，然后执行选择和投影操作。（2）当查询涉及否定含义或全部值时，则用差或除法操作。（3）关系代数的操作表达式是不唯一的。（4）多个关系连接时，应遵守：①尽可能早地执行选择操作；②尽可能早地执行投影操作；③把选择和投影后的表达式进行连接。【总结】4.4元组关系演算数据库原理与设计68元组关系演算是以元组为变量，其一般形式为：{t|P(t)

}

表示满足公式P的所有元组t的集合。S#SNAMEAGESEX1钱衡23男2刘丽22女3张友22男S例：写出元组表达式表示的关系R1={t|S(t)

}。（1）R(t)

表示t是R关系中的一个元组。S#SNAMEAGESEX1钱衡23男2刘丽22女3张友22男R11．原子公式三种形式（2）t[i]θc或cθt[i]

表示元组t的第i个分量与常量c满足条件θ。S#SNAMEAGESEX1钱衡23男2刘丽22女3张友22男S例：写出元组表达式表示的关系R2={t|S(t)

∧t[4]=‘女’}S#SNAMEAGESEX2刘丽22女R2（3）t[i]θs[j]

表示元组t的第i个分量与元组s的第j个分量之间满足条件θ。（1）如果P1和P2是公式，则﹁P1、P1∨P2、P1∧P2也为公式2．公式的递归定义（2）如果P是公式，那么(t)(P)和(t)(P)也是公式。（3）公式中各种运算符的优先级从高到低依次为：θ、和、﹁、∧和∨。在公式外还可以加括号，以改变上述优先顺序。例：写出下列元组演算表达式表示的关系。R3={t|(u)(S(t)∧R(u)∧t[3]<u[2])}ABC123456789RABC123346569SABC123346R3t[3]u[2]例：写出下列元组演算表达式表示的关系。R4={t|(u)(R(t)∧S(u)∧t[3]>u[1])}ABC123456789RABC123346569SABC456789R4u[1]t[3]【例】设有关系R和S，写出下列元组演算表达式表示的关系。ABC1a17f89e90c5ABC1a13a54c42b0SR(1)R1={t|R(t)∧S(t)}（2）R2={t|R(t)∧t[2]=a}ABC1a1R1ABC1a13a5R2ABC1a17f89e90c5ABC1a13a54c42b0SR（3）R3={t|(u)(R(t)∧S(u)∧t[1]<u[3]∧t[2]≠b)}ABC1a13a54c4R3t[1]u[3]t[2]ABC1a17f89e90c5ABC1a13a54c42b0SR（4）R4={t|(u}(R(u)∧t[1]=u[3]∧t[2]=u[1])}R4u[1]u[3]CA11534402（1）并R∪S={t|R(t)∨S(t)}3．关系代数中5种基本运算用元组关系演算表达式的表达（3）投影

∏i1,i2,…,ik(R)={t|(u)(R(u)∧t[1]=u[i1]∧t[2]=u[i2]∧…∧t[k]=u[ik])