数据库完整性

转载自：知乎  https://zhuanlan.zhihu.com/p/345372629

数据完整性是指保护数据的正确性、有效性和相容性，防止错误（不符合语义）的数据进入数据库造成无效操作。

为维护数据库的完整性，DBMS必须：

（1）提供定义完整性约束条件的机制（2）提供完整性检查的方法（3）违约处理

完整性检查是围绕完整性约束条件进行的，因此完整性约束条件是完整性控制机制的核心。完整性约束根据约束条件所涉及对象状态的不同，可以分为静态约束和动态约束。由于完整性约束总是要针对一些对象，比如说针对关系，元组，或者是列。所以完整性约束条件又可以分为表级约束，元组级约束和属性级约束（列级约束）

综上所述，完整性约束条件可分为6类。

（1）静态列级约束：包括对数据类型的约束；对数据格式的约束；对取值范围或取值集合的约束；对空值的约束；其他约束
（2）静态元组级约束：静态元组级约束是对元组的属性组值的限定，即规 定属性之间的值或结构的相互制约关系。例如：规定老师的工资不低于4000
（3）静态关系约束：常见的静态关系约束有实体完整性约束、参照完格性约束、函数依赖约束和统计约束。
（4）动态列级约束：动态列级约束是指修改列定义或修改列值时必须满足的约束条件。

（5）动态元组级约束：动态元组级约束是指当修改元组的值时，元组中字段组或字段间需要满足某种约束。
（6）动态关系约束：动态关系约束是加在关系变化前、后状态上的限制条件。

SQL Server的数据完整性

SQL Server 具有较健全的数据完整性控制机制，它使用约束、默认、规则和触发器 4 种 方法定义和实施数据完整性功能。SQL Server中数据完整性包括实体完整性，参照完整性和域完整性。

（1）实体完整性

实体完整性为表级完整性，又称为行的完整性，要求表中有一个主键，其值不能为空且能唯一地标识对应的记录。通过索引、UNIQUE 约束、PRIMARY KEY 约束或 IDENTITY 属性可实现数据的实体完整性。

（2）参照完整性

参照完整性是表级完整性，它维护从表中的外码与被参照表中主码的相容关系。外码只能取被参照表中主码的有效值或者为空；如果在被参照表中某一元组被外码参照，那么这个元组既不能被删除，也不能更改其主码。

（3）域完整性

域完整性为列级和元组级完整性，又称为列完整性，指给定列输入的有效性。实现域完整性的方法有：限制类型（通过数据类型）、格式（通过 CHECK 约束和规则）或可能的取值范围（通过 CHECK 约束、DEFALUT 定义、NOT NULL 定义和规则）等。

CHECK 约束通过显示输入到列中的值来实现域完整性；DEFAULT 定义后，如果列中没有输入值则填充默认值来实现域完整性；通过定义列为 NOT NULL 限制输入的值不能为空也能实现域完整性。

（4）用户自定义完整性

用户自定义完整性约束就是针对某一具体关系数据库的约束条件，它反应某一具体应用所涉及的数据必须满足的语义要求。

SQL Server中数据完整性的具体方法：声明数据完整性，通过系统本身自动强制来实现，包括各种约束，默认和规则；过程数据完整性，通过脚本语言（T-SQL）定义，在执行这些语言时强制实现数据完整性，包括触发器和存储过程。

数据完整性的实现

实体完整性的实现

（1）一个表只能有一个primary key约束，对应的列不能取空值
（2）由于 PRIMARY KEY 约束能够确保数据的唯一性，所以经常用来定义标识列。
（3）当为表定义了primary key约束时，SQL Server为主键创建唯一索引，在查询使用主键时，索引可用来快速访问
（4）如果要确保一个表中的非主键列不输入重复值，应在该列上定义唯一约束 （UNIQUE 约束）。
（5）创建primary key约束时，系统会自动创建索引，默认为聚簇索引；当创建unique约束时，系统自动创建unique索引，默认为非聚簇索引

1.利用图形向导方式创建和删除primary key和unique约束

（1）primery key约束

找到需要设置主键的表，右键，选择设计

在需要设置主键的列，找到对应的那个行，右键，设置主键即可，若要选择多列，按Ctrl键多选即可

删除同理。

（2）unique约束

同样找到所需要选择的列，右键，选择索引/键

按添加后，选择是唯一的即可

删除同理，在索引/键窗口中，找到对应的索引删除即可。

2.利用“T-SQL 命令”创建及删除 PRIMARY KEY 约束或 UNIQUE 约束

可以在创建表或修改表时定义

通过例题进行讲解

例题1：设置学生表的主键

student（sno，sname，sex，birth，classno）其中，sno为主，（设姓名唯一）

方法一：

create table student
(
sno nvarchar(15) primary key,
sname nvarchar(10) unique,
sex nchar(1),
birth date,
classno nvarchar(10)
)

方法二：

create table student
(
sno nvarchar(15),
sname nvarchar(10),
sex nchar(1),
birth date,
classno nvarchar(10),
constraint PK_student primary key(sno),
constraint UQ_student unique(sname)
)

方法三：

alter table student
add constraint PK_student primary key(sno)
go
alter table student
add constraint UQ_student unique(sname)
go

删除主键约束和unique约束：

alter table student
drop constraint PK_student,UQ_student

参照完整性的实现

对于两个关联的表进行数据的插入和删除时，通过参照完整性保证数据的一致性。利用 FOREIGN KEY 定义表的外键，PRIMARY KEY 或 UNIQUE 约束定义主表中的主键或唯一键（不允许空），可以实现主表和从表之间的参照完整性。定义表间参照关系：先定义主表的主键或唯一键，在定义从表的外键约束。

外键可以理解为两张二维表含以上的公共字段，这个字段的字段名在两张表中可以不同，但是数据类型必须相同。

创建外键也有两种方法：使用图形界面；使用SQL语句，在此只讲解第二种方法，通过例题讲解

例题2：在SCC数据库中创建外键约束，关联学生表student和班级表class，实现参照完整性约束

方法一：

create table student
(
sno nvarchar(15) primary key,
sname nvarchar(10) unique,
sex nchar(1),
birth date,
classno nvarchar(10) foreign key references class(classno)
)

方法二：

create table student
(
sno nvarchar(15),
sname nvarchar(10),
sex nchar(1),
birth date,
classno nvarchar(10),
constraint PK_student primary key(sno),
constraint UQ_student unique(sname),
constraint FK_student_class foreign key(classno) references class(classno)
)

方法三：

alter table student
add constraint FK_student_class foreign key(classno) references class(classno)

删除外键约束：

alter table
drop constraint FK_student_class

域完整性的实现

SQL Server通过数据类型，check约束，规则，defalut和not null实现域完整性。下面将介绍check约束和规则还有defalut。

1.check约束的定义与删除（对于 timestamp 类型字段和 identity 属性字段不能定义 CHECK 约束）

通过图形向导方式创建与删除check约束

选择需要设计check约束的表，在约束处，右键新建约束

在表达式处输入表达式即可。

利用SQL创建check约束，语法格式如下：

CHECK [ NOT FOR REPLICATION ] ( logical_expression )

关键字 CHECK 表示定义 CHECK 约束，如果指定 NOT FOR REPLICATION 选项，则当复制代理执行插入、更新或删除操作时，将不会强制执行此约束。其后的 logical_expression 逻辑表达式，称为 CHECK 约束表达式，返回值为 TRUE 或 FALSE，该表达式只能为标量表达式。

例题3：还是student表，其中的sex只能是男或女

方法一：

create table student
(
sno nvarchar(15) primary key,
sname nvarchar(10) unique,
sex nchar(1) check (in ('男','女')),
birth date,
classno nvarchar(10) foreign key references class(classno)
)

方法二：

create table student
(
sno nvarchar(15),
sname nvarchar(10),
sex nchar(1),
birth date,
classno nvarchar(10),
constraint PK_student primary key(sno),
constraint UQ_student unique(sname),
constraint FK_student_class foreign key(classno) references class(classno),
constraint CK_sex check( in ('男','女'))
)

方法三：

alter table student
add constraint CK_sex check ( in ('男','女'))

删除check约束：

alter table student
add constraint CK_sex

2.默认值约束defalut

通过例题讲解

例题4：还是student表，其中的sex默认为男（注意：默认值约束只能用列级约束）

方法一：

create table student
(
sno nvarchar(15) primary key,
sname nvarchar(10) unique,
sex nchar(1) dafalut '男' check (in ('男','女')),
birth date,
classno nvarchar(10) foreign key references class(classno)
)

方法二：

alter table student
alter column sex set defalut '男'

defalut约束的删除：

alter table student
drop constraint DF_sex

3.规则对象的定义，使用与删除（只进行简单介绍）

规则是一组使用 T-SQL 语句组成的条件语句，规则提供了另外一种在数据库中实现域完整性与用户定义完整性的方法。规则对象的使用方法与默认值对象的使用步骤类似：

（1）定义规则对象
（2）将规则对象绑定到列或用户自定义类型

（1）规则对象的定义

CREATE RULE [ schema_name. ] rule_name
AS condition_expression
[;]

参数说明：

（1）schema_name：规则所属架构的名称。
（2）rule_name：新规则的名称。规则名称必须符合标识符规则。
（3）condition_expression：定义规则的条件。规则可以是 WHERE 子句中任何有效的表达式，并且可以包括诸如算术运算符、关系运算符和谓词（如 IN、LIKE、BETWEEN）这样的元素。规则不能引用列或其他数据库对象。可以包括不引用数据库对象的内置函数，但不能使用用户定义函数。
（4）condition_expression 可以包括一个变量。每个局部变量的前面都有一个 at 符号(@)。该表达式引用通过 UPDATE 或 INSERT 语句输入的值。在创建规则时，可以使用任何名称或符号表示值，但第一个字符必须是 at 符号(@)。

（2）将规则对象绑定到用户定义数据类型或列。

sp_bindrule [ @rulename = ] 'rule' , [ @objname = ] 'object_name'[ , [ @futureonly = ] 'futureonly_flag' ]

参数说明：

（1）[ @rulename =] 'rule'：由 CREATE RULE 语句创建的规则名称，需要使用单引号。
（2）[ @objname = ] 'object_name'：要绑定规则的表和列或别名数据类型。不能将规则绑定到 text、ntext、image、varchar(max)、nvarchar(max)、varbinary(max)、xml、CLR 用户定义类型或 timestamp 列。无法将规则绑定到计算列。
（3）[ @futureonly = ] 'futureonly_flag'：只有将规则绑定到别名数据类型时才使用。 future_only_flag 的数据类型为 varchar(15)，默认值为 NULL。当此参数设置为 futureonly 时，可以防止具有别名类型的现有列继承新的规则。如果 futureonly_flag 为 NULL，则会将新规则绑定到目前没有规则或正在使用别名数据类型的现有规则的所有别名数据类型列上。

（3）规则对象的删除

删除规则对象前，首先应使用系统存储过程 sp_unbindrule 解除被绑定对象与规则对象之间的绑定关系，使用格式如下：

sp_unbindrule [@objname =] 'object_name'
[, [@futureonly =] 'futureonly_flag']

在解除列或自定义类型与规则对象之间的绑定关系后，就可以删除规则对象了。 语法格式：

DROP RULE { [ schema_name . ] rule_name } [ ,...n ] [ ; ]

                                 关系代数

转载自：博客园https://www.cnblogs.com/LilRed/p/17238929.html

概述

关系代数是研究数据库的一个重要工具，是一种抽象的查询语言，其运算的对象、结果均为关系。实际上关系的运算既包括关系运算，也包括集合运算，从细节入手还包括比较和逻辑运算。

原始运算

如同任何代数，一些运算是原始的，而可以通过原始运算来定义的另一些运算是导出的。

并（Union）

并操作($\cup$)的对象是两个集合R、S，返回一个新集合，这个新集合内元组的特点为：

简单来讲，并操作消除了两个集合重复的元组（集合的性质），新关系（元组集合）中每个元组是唯一的。

进行并运算是有前提的，即R、S必须相容：

• R与S必须同元，即属性数目必须相同

• 对任意i，R的第i个属性的域必须和S的第i个属性的域相同

也就是说，两个关系的格式必须一样，如果R有学号、姓名、年龄三种属性，那S有且仅有这三种属性，次序也要相同。

差（Minus）

差操作(-)的返回的新集合特点为：

$$�-�=\{�|�\in �\wedge �\notin �\}$$

即R-S代表在R中但不在S中的元组的集合。

进行差运算是有前提的，即R和S必须同类型：

• 属性集相同（n目）

• 次序相同

• 属性名可以不同

交（Intersect）

交操作($\cap$)返回的新集合的特点为：

$$�\cap �=\{�|�\in �\wedge �\in �\}$$

或者说：

$$�\cap �=�-(�-�)$$

即同时出现在两个关系中的元组的集合。

R和S必须满足同类型。

笛卡尔积

实质上是R与S的无条件连接，使得任意两个关系的信息可以组合在一起。

若R的关系为 ${�}_1\times {�}_1$ (${�}_1$行${�}_1$目)，S的关系为 ${�}_2\times {�}_2$，则新关系 $�\times �$ 为 $({�}_1\times {�}_2)\times ({�}_1+{�}_2)$。

选择运算（σ）

选择运算($�$)的操作对象为一个集合R，返回的结果为：

$${�}_{�(�)}=\{�|�\in �,�(�)=����\}$$

$�\in \{>,<,\ge ,\le ,=,\ne \}$

简单地理解就是从R中选出满足F为真的元组组成新元组。

举个例子，如果我要筛选课程信息表中4学分的课程，那么我可以用下列表达式说明：

$${�}_{�������=4(������)}$$

或者将属性名用列序号代替：

$${�}_{4=4(������)}$$

显然选择运算针对的是行，注意需要去除重复的分组。

投影运算（π）

投影运算($\mathrm{\Pi }$ 音同 $�$)是从关系R中取若干列组成新的关系（集合）：

$${\mathrm{\Pi }}_{�}(�)=\{�[�]|�\in �\},�\subseteq �$$

$�$ 是一个属性（名），如下列的例子：

${\mathrm{\Pi }}_{{�}_1,{�}_2,...,{�}_{�}}(�)$表示从R中选择属性集${�}_1,{�}_2,...,{�}_{�}$ 组成新的关系。

显然这是对列的运算，注意也需要去除重复的行。

连串（Concatenation）与笛卡尔积

连串是对元组而言的，若 $�=({�}_1,...,{�}_{�})，�=({�}_1,...,{�}_{�})$，则定义r与s的连串为：

$$\stackrel{⌢}{��}=({�}_1,...,{�}_{�},{�}_1,...,{�}_{�})$$

简单来说就是属性名的拼接，这种拼接是有顺序的（A和B不能交换）。

这时，我们的笛卡尔积表达式就可以表示为：

$$�\times �=\{\stackrel{⌢}{��}|�\in �\wedge �\in �\}$$

我们也称之为广义笛卡尔积。

使用笛卡尔积进行关系内部选择比较

求数学成绩比王红高的学生：

注意，这里有两个R，其中一个R改名为S，方便描述和运算。

连接（θ）及其类似的运算

（注意：在此不讨论半连接和反连接$⋊$,$⋉$,$▹$)

从两个关系的广义笛卡尔积中选取给定属性间满足一定条件的元组：

$$\underset{���}{�\bowtie �}=\{\stackrel{⌢}{��}|�\in �\wedge �\in �\wedge �[�]��[�]\}$$

注意：

• A、B为R和S上度数相等且可比的属性列

• $�$ 为算术比较符，为 = 时称为等值连接

用选择运算定义：

$$\underset{���}{�\bowtie �}={�}_{�[�]��[�]}(�\times �)$$

举个例子：

自然连接

从两个关系的广义笛卡儿积中选取在相同属性列B上取值相等的元组，并去掉重复的行，表达式为：

$$�\bowtie �=\{\stackrel{⌢}{��}[�]|�\in �\wedge �\in �\wedge �[�]=�[�]\}$$

与等值连接的区别在于：属性组相同，去重。

举个例子：

r与s的B、D两个属性重复，具体而言有3个子元组(BD)重复，为(1a,1a,2b)，2个1a内部做笛卡尔积，得到4个新元组，1个2b内部做笛卡尔积，得到1个新元组，组成自然连接的结果。

外连接

为避免自然连接时因失配而发生的信息丢失，可以假定往参与连接的一方表中附加一个取值全为空值的行，它和参与连接的另一方表中的任何一个未匹配上的元组都能匹配，称之为外连接。

外连接 = 自然连接 + 未匹配元组（悬挂元组）

外连接分为3种类型。

左外连接

⟕ = 自然连接 + 左侧表中未匹配元组

右外连接

⟖ = 自然连接 + 右侧表中未匹配元组

全外连接

⟗ = 自然连接 + 两侧表中未匹配元组

外连接的例子

左外连接：

左侧老师信息和右侧已经过结合律完成连接的关系进行左外连接，此时发现P03的孙立，分数为600，没有匹配到，这时在自然连接的结果后补上一行元组，由于右侧没有对应的，所以C#和CN均为null。

右外连接：

右侧所有课程的信息中C03化学没有匹配到，因此分配一个空的老师信息，给自然连接的结果添加一个新的（残缺）元组。

全外连接：

除法运算

除法运算其表达式为：

$$�÷�=\{�[{�}_1,...,{�}_{�}]|�\in �\wedge \mathrm{\forall }�\in �((�[{�}_1,...,{�}_{�}]\cup �)\in �)\}$$

用投影来化简：

$$�÷�={\mathrm{\Pi }}_{�}(�)-{\mathrm{\Pi }}_{�}({\mathrm{\Pi }}_{�}(�)\times {\mathrm{\Pi }}_{�}(�)-�)$$

以上图为例，对于“完成”和“DB项目”两个关系，它们有Task这个共同属性，“完成”÷“DB”表示为完成了Database1和Database2

两个项目的学生构成的关系（表）。

                          关系代数的除法运算及易错示例

转载自：https://blog.csdn.net/Imomoco/article/details/102727039

除法运算

大概数据库中关系运算复杂点的也就是除法运算了，这也可能是很多入门新手数据库学习中遇到的第一个障碍。

接着我们来理清一下。

除法//话不多说，直接开莽

我们先创建两个表格，一个学生表，一个S1表。

学生表：

S1:

R÷S1=学生表中年龄为19的学生信息的新表格，但这个新表格中的字段不包括年龄。

答案如下：

懂了没?是不是很清晰！

不懂？没事我们再来举例！

依旧是这个学生表，但我们S1换成一个具有两个字段的S2

S2:

那结果就是在学生表中，/同时/满足S2所有条件的学生信息，但没有S2中的字段的新表。如下图：

学生÷S2

现在让我们来理解一下书中的定义。

书中的定义

这个式子我一开始看的时候也是脑壳一懵，不要慌！我们来慢慢解析。

首先tr为求象集。

于是我们引入象集的概念：

在学生表中，每个分量值都会有它的象集。

如学生表：

001的象集：{（张三，19，计算机）}

计算机的象集：{（001，张三，19），（002，李冰，21），（004，王华，21）}

所以，象集的本质是一次选择运算和一次投影运算。

S2:

所以学生表上学号和姓名的分量的象集为：

（001，张三）象集为（19，计算机）

（002，李四）象集为（20，管理）

（003，李冰）象集为（21，计算机）

（004，王华）象集为（21，计算机）

年龄和系名在S2上的投影为：

（21，计算机）

显然只有（003，李冰）和（004，王华）的象集包含投影（21，计算机）

所以学生÷S2：

再结合到公式上看，应该可以理解公式了。