【专题】关系模型的基本理论

1. 关系模型基本概念

1.1 基本术语

关系（Relation）：用于描述数据的一张二维表。

表中每一行称为元组；
表中每一列称为属性。

域（Domain）：指属性的取值范围。

候选键（Candidate Key）：也称为候选码。能唯一的标识关系中每一个元组的最小属性集。

主键（Primary Key，PK）：也称为主码。可从候选键中指定其中一个作为关系的主键。一个关系只能有一个主键。为主键的列不允许取NULL值。

全码（ALL-key）：关系中所有属性的组合是该关系的一个候选码，则该候选码称为全码。

外键（Foreign Key，FK）：关系R中的某个属性k是另一个关系S中的主键，则称该属性k是关系R的外键。

通过外键可以建立两个关系间的联系。

1.2 关系的特征

列是同质的，即每一列中的分量是同一类型的数据，来自同一个域。
不同的列可出自同一个域，称其中的每一列为一个属性，不同的属性要给予不同的属性名。
各列的顺序在理论上是无序的，即列的次序可以任意互换，但使用时按习惯考虑列的顺序。
任意两个元组的侯选码不能相同。
行的顺序无所谓，即行的次序可以任意交换。

分量必须取原子值，即每一个分量都必须是不可分的数据项。

2. 数据库完整性

数据库完整性是指关系中数据的正确性和相容性。

2.1 三类完整性规则

实体完整性：

实体完整性给出了主键的取值的最低约束条件。
主键能够唯一标识关系中的一个元组。
主键的各个属性都不能为空值。

参照完整性规则：

参照完整性给出了在关系之间建立正确的联系的约束条件。
被参照关系的列必须设置为主键。
外键或者取空值，或者等于被参照关系中主键的某个值。
现实世界中的各个实体之间往往存在着某种联系，这种联系在关系模型中也是用关系来描述的。
有的联系是从相互有联系的关系中单独分离出一个新的关系，而有的联系则是仍然隐含在相互有联系的关系中。
总之这样就自然地在关系和关系之间存在着相互参照。
参照完整性主要是对这种参照关系是否正确进行约束。
设F是关系R的一个或一组属性(但F不是R的主键)，K是关系S的主键。如果F与K相对应，则称F是关系R的外键，并称关系R为参照关系，关系S为被参照关系。关系R和关系S可以是同一个关系。
外键或者取空值(要求外键的每个属性均为空值)，或者等于被参照关系中的主键的某个值。

参照完整性规则就是定义外键与主键之间的引用规则。

用户定义完整性规则：

根据应用环境的特殊要求，关系数据库应用系统中的关系往往还应该满足一些特殊的约束条件。
用户定义的完整性就是用于反映某一个具体关系数据库应用系统中所涉及的数据必须要满足的语义要求，即给出某些属性的取值范围等约束条件。
属性的取值应当满足用户定义的约束条件。

2.2 MySQL提供的约束

主键约束：

设置主键约束Primary Key
要求：值唯一，不能为空值。

示例：创建学生表，同时将学号列设置为主键。
CREATE TABLE  学生(
 学号  CHAR(4)                                                           ,
 姓名  VARCHAR(8),
 性别  CHAR(2),
 家庭住址 VARCHAR(20)
); 
示例：删除学生表上的主键。
ALTER  TABLE  学生
    DROP  PRIMARY  KEY;
示例：在已有的学生表上，将学号列设置为主键。
ALTER  TABLE  学生
   ADD   PRIMARY  KEY(学号); 
示例：创建选课表，同时设置学号和课号为主键。
CREATE TABLE  选课(
 学号  CHAR(4)  not null primary key,
 课号  CHAR(3)  not null primary key,
 成绩  decimal(5,2)
);

自增约束：

字段名数字类型 AUTO_INCREMENT
一个表只能有一个字段使用AUTO_INCREMENT约束，且该字段必须为主键的一部分。
默认情况下，自增字段的初始值为1，每新增一条记录，字段值自动加1。
自增约束字段只能是数字类型,如TINYINT、SMALLINT、INT、BIGINT等。

示例：建立s表，为s_id字段定义自动增长约束。

CREATE TABLE s(
  s_id      int                                        ,
  s_name    char(50),
  s_city    char(50)
) ;

唯一（UNIQUE）约束：

唯一约束实现实体完整性。
要求作为唯一约束的字段满足2个条件：
1. 值唯一；
2. 可有一个且仅有一个空值。

示例：建立employee表，在employee表中定义一个phone字段，并为phone字段定义指定名称的唯一约束。
CREATE  TABLE  employee
( empno   DECIMAL(2)  PRIMARY  KEY,
 name    VARCHAR(8),
 age     DECIMAL(3),
 phone   VARCHAR(12),
 deptno  DECIMAL(2),
 CONSTRAINT emp_phone UNIQUE(phone)
);
示例：删除唯一约束emp_phone。
ALTER TABLE employee
 DROP  INDEX  emp_phone;
示例：为已有表employee根据phone字段创建唯一约束，约束名为emp_phone。
ALTER TABLE employee
 ADD  CONSTRAINT  emp_phone   UNIQUE(phone);

检查（CHECK）约束：

检查约束实现用户自定义完整性。
检查输入的每一个数据，只有符合条件的数据才允许插入表中。
语法：[CONSTRAINT 约束名] CHECK(检查条件)

示例：建立选课表，限制成绩列的值必须在0~100之间。
CREATE TABLE  选课(
 学号  CHAR(4) not null,
 课号  CHAR(3) not null,
 成绩  decimal(5,2)  CONSTRAINT  cj_ck  CHECK(成绩 BETWEEN 0 and 100)); 
示例：删除选课表中的检查约束。
ALTER  TABLE  选课
  DROP  CONSTRAINT  cj_ck;
示例：为选课表成绩列添加检查约束。
ALTER TABLE 选课
  ADD  CONSTRAINT  cj_ck  CHECK(成绩 BETWEEN 0 and 100)); 

外键约束：

语法：[CONSTRAINT 外键约束名] [FOREIGN KEY(外键列)] REFERENCES 主表名(主键列)

示例：创建选课表，其学号取值参照学生表中的学号值。

# Table1
CREATE TABLE  学生(
 学号  CHAR(4)                                               ,
 姓名  VARCHAR(8),
 性别  CHAR(2),
 家庭住址 VARCHAR(20)
); 
# Table2
CREATE TABLE  选课(
 学号  CHAR(4) not null,
 课号  CHAR(3) not null,
 成绩  decimal(5,2),
 constraint  xh_fk foreign key(学号)   references  学生(学号)
);

示例：删除选课表中的外键约束xh_fk。

ALTER  TABLE  选课
  DROP  CONSTRAINT  xh_fk;

示例：为选课表学号列添加外键约束，约束名为xh_fk。

ALTER TABLE 选课
  ADD  CONSTRAINT  xh_fk  
    FOREIGN KEY(学号)  REFERENCES 学生(学号);

对主(父)表进行INSERT、DELETE、UPDATE操作，对从(子)表的影响：

插入(INSERT)

主表中主键值的插入不会影响到从表中的外键值。
修改(UPDATE)

如果从表中的外键值与主表中的主键值一样，主表中的主键值的修改要影响从表中的外键值。

删除(DELETE)

主表中主键值的删除可能会对从表中的外键值产生影响，除非主表中的主键值没有在从表的外键值中出现。

对从(子)表外键进行INSERT、DELETE、UPDATE操作，又会对主(父)表的影响：

插入(INSERT)

插入从表的外键值时，要求插入的外键值应“参照”主表中的主键值
修改(UPDATE)

修改从表的外键值时，要求修改的外键值需“参照”主表中的主键值
删除(DELETE)

从表中记录的删除不需要参照主表中的主键值

父表中主键值的修改(UPDATE），对子表中的外键值的影响：

子表中对应的外键值进行自动更新---级联更新。

设置级联更新：

在设置外键时加上短语ON UPDATE CASCADE

示例：对主表学生1001的学号改为1078，同时从表选课1001的学号自动修改为1078.
CREATE TABLE  选课(
 学号  CHAR(4) not null,
 课号  CHAR(3) not null,
 成绩  decimal(5,2),
 constraint xh_fk foreign key(学号) references 学生(学号)                                           
); 

父表中主键值的删除(DELETE），对子表中的外键值的影响：

子表中对应的外键值记录进行自动删除---级联删除。
设置级联删除：

在设置外键时加上短语 ON DELETE CASCADE

示例：删除主表学生学号为1001的记录，同时从表选课自动删除学号为1078的记录.
CREATE TABLE  选课(
 学号  CHAR(4) not null,
 课号  CHAR(3) not null,
 成绩  decimal(5,2),
 constraint xh_fk foreign key(学号) references 学生(学号)                                               
); 

ON UPDATE | DELETE 后其它短语：

ON UPDATE|DELETE SET NULL |NO ACTION
SET NULL：父表记录的delete或update操作，会将子表中与之对应的外键值自动设置为NULL值。
NO ACTION：父表记录的delete或update操作，如果子表存在与之对应的记录，delete或update操作失败。

2.3 触发器

触发器（TRIGGER）是一种特殊的存储过程。触发器实现参照完整性。

为什么要使用触发器？

当在学生表中修改某个学生的学号时，选课表中该学生的学号一起被自动修改。

当来新同学时，在学生表中添加一条记录，同时学生的总人数需要自动改变。

当对表进行INSERT、UPDATE、DELETE操作时就会自动激活相应的触发器并执行。
触发器经常用于加强数据的完整性约束和业务规则等。

创建触发器：

CREATE  [ OR  REPLACE ]  TRIGGER  触发器名
    BEFORE | AFTER  
    INSERT | DELETE | UPDATE  [OF 列1[,列2 ……] ] 
    ON   表名
    FOR  EACH  ROW 
      触发的SQL语句

示例：创建触发器delete_trigger，触发器将记录哪些用户删除了department表中的数据，以及操作的时间。

# 创建merch_log的日志信息表，用于存储用户对表的操作信息。
CREATE  TABLE  merch_log(  
  who        VARCHAR(30),
  oper_date  DATE
);

# 在department表上创建触发器，实现向merch_log表添加操作的用户名、日期。
CREATE   TRIGGER  del_tri
  AFTER  DELETE
  ON  fruits
  FOR  EACH ROW
  INSERT INTO  merch_log(who,oper_date)
    VALUES(USER(),SYSDATE());

# 测试触发器是否正常运行
DELETE FROM department WHERE deptno=10;
SELECT * FROM merch_log;

触发器如何取得激活触发器操作的旧值和新值？

旧值。在字段名前加上OLD.限定词。
新值。在字段名前加上NEW.限定词。

INSERT触发器，只能使用NEW.列名，因为不涉及旧值行。

DELETE触发器，只能使用OLD.列名，因为不涉及新值行。

UPDATE触发器，可以使用OLD.列名引用更新前某一行的旧值，使用NEW.列名引用更新后行的新值。

示例：本例实现级联更新。在修改department表中的deptno之后（AFTER）级联地、自动地修改employee表中原来在该部门的雇员的deptno。
CREATE  TRIGGER  tr_dept_emp
  AFTER  UPDATE  
  ON  department
  FOR  EACH  ROW
  UPDATE  employee  SET  deptno=NEW.deptno   
    WHERE  deptno=OLD.deptno;

删除触发器：

语法：DROP TRIGGER 触发器名;

示例：删除触发器tr_dept_emp。
DROP TRIGGER tr_dept_emp;

3. 关系代数

关系代数中的操作可以分为以下两类：

传统的集合运算，包括并、交、差。

专门的关系运算，包括对关系进行垂直分割（投影）、水平分割（选择）、关系的联合（连接、自然连接）等。

一个或两个关系经过关系运算后的结果仍然是一个关系。

3.1 关系代数的基本操作

并（Union）：

设关系R和S具有相同的关系模式，R和S的并是由属于R或属于S的所有元组构成的集合。

R∪S = {t | t∈R∨t∈S}

t为元组变量。

逻辑运算符包括逻辑与∧、逻辑或∨、逻辑非﹁。

关系的并操作对应于关系“插入”记录的操作，俗称为+操作。

差（Difference）：

设关系R和S具有相同的关系模式，R和S的差是由属于R但不属于S的元组构成的集合。

R - S = { t | t∈R∧t S }

关系的差操作对应于关系的“删除”记录的操作，俗称为－操作。

笛卡儿积（Cartesian Product）：

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

R × S = { (Tr Ts) | Tr∈R ∧ Ts∈S }

关系的笛卡儿积操作对应于两个关系记录横向合并的操作，俗称×操作。

示例：设有关系R和S如下，计算R×S。

投影（Projection）：

关系R上的投影是从R中选择出若干属性列组成新的关系。

∏A(R) = { t[A]|t∈R }

A为R中的属性列，可以是属性的序号或属性名。

投影操作是对一个关系进行垂直分割，消去某些列，并重新安排列的顺序。

在MySQL中用SELECT短语实现。

示例：对S关系，计算∏3,1(S)或∏C,A(S)。

选择（Selection）：

关系R上的选择操作是从R中选择符合条件的元组。

ðF(R) = {t | t∈R∧F(t)=true }

F表示选择条件，是一个逻辑表达式。
1. 运算对象。
  
  可以是常数，或属性名或列的序号。如ð3≥10(R)
2. 运算符。
  
  比较运算符（<，≤，>，≥，=，≠，也称为θ符）、逻辑运算符（逻辑与∧，逻辑或∨，逻辑非﹁）。

选择操作是对一个关系进行水平分割，消去某些行。

在MySQL中用WHERE短语实现。

示例：对R关系，计算ðC>’6’(R)。

3.2 关系代数的4个组合操作

交（Intersection）：

设关系R和S具有相同的关系模式，R和S的交是由属于R又属于S的元组构成的集合。

R∩S = { t | t∈R∧t∈S }

关系的交可以用差来表示，即R∩S = R-{ R-S }。

关系的交操作对应于寻找两关系共有记录的操作，是一种关系“查询”操作。

连接（Join）：

从两个关系的笛卡儿积中选取属性值满足某一θ条件的元组。

等值连接：

如果θ是等号“＝”，该连接操作称为“等值连接”。

自然连接：

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

除（Division）：

分量。

每个元组的各属性值分别为一个分量。

象集。

x在关系R中的象集为Zx，它表示关系R中X分量等于x的元组集合在属性集Z上的投影。

关系的除法运算步骤：

将被除关系的属性分为象集属性和结果属性：与除关系相同的属性属于象集属性，不相同的属性属于结果属性。

在除关系中，对与被除关系相同的属性（象集属性）进行投影，得到除目标数据集。

将被除关系分组，原则是，结果属性值一样的元组分为一组。

逐一考察每个组，如果它的象集属性值中包含除目标数据集，则对应的结果属性值则属于该除法运行结果集。

4. 关系运算（元组关系运算）

元组关系演算是以元组为变量，其一般形式为：

{ t|P(t) }表示满足公式P的所有元组t的集合。

原子公式三种形式：

R(t)，表示t是R关系中的一个元组。

t[i]θc 或 cθt[i]，表示元组t的第i个分量与常量c满足条件θ。

t[i] θ s[j]，表示元组t的第i个分量与元组s的第j个分量之间满足条件θ。

公式的递归定义：

如果P1和P2是公式，则﹁P1、P1∨P2、P1∧P2也为公式
如果P是公式，那么( t)( P)和( t)( P)也是公式。
公式中各种运算符的优先级从高到低依次为：θ → ∃ = ∀ → ﹁ → ∧ = ∨。在公式外还可以加括号，以改变上述优先顺序。

关系代数中5种基本运算用元组关系演算表达式的表达：

设关系R和S都是具有3个属性列的关系：

并，R∪S={t |R(t)∨S(t)}。
差，R－S={t |R(t)∧﹁S(t)}。
投影，∏i1,i2,…,ik(R)={t | ( u)(R(u)∧t[1]=u[i1]∧t[2]=u[i2]∧… ∧t[k]=u[ik]) }。

笛卡尔积，R×S={t |( u)( v)( R(u)∧S(v)∧t[1]=u[1]∧t[2]=u[2]∧t[3]=u[3]∧t[4]=v[1]∧t[5]=v[2]∧t[6]=v[3]) }。
选择，σF(R)={t | R(t) ∧F’} 其中F’是F的等价表示形式。
交，R∩S={t |R(t)∧S(t)}。