mysql-12 数据库完整性与关系数据理论

发表于 | 更新于 | 分类于 ， | 评论数： | 热度： ℃
mysql-12 数据库完整性与关系数据理论

mysql-12 数据库完整性与关系数据理论

概念

1）安全性：保护数据库以防止不合法使用所造成的数据泄露、更改或破坏；

2）完整性：指数据的正确性和相容性，即防止数据库中存在不符合语义（或不正确）的数据；

3）PK：Primary Key

4）FK：Foreign Key

1、实体完整性（primary key）

1）可以在表级或者列级定义primary key

2）若主码由多个属性构成，那么只能在表级定义primary key

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
#在列级定义主码
create table student(
	sno char(9) primary key,
	sname char(20),
	ssex char(2));
	
#在表级定义主码
create table student(
	sno char(9),
	sname char(20),
	ssex char(2),
	primary key(sno));
	
#主码由多个属性构成，那么只能在表级定义primary key
create table sc(
	sno char(9),
	cno char(4),
	grade smallint,
	primary key(sno,cno));

2、参照完整性（foreign key）

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
#定义sc表的参照完整性
create table sc(
	sno char(9),
	cno char(4),
	grade smallint,
	primary key(sno,cno),
	foreign key(sno) references student(sno),
	foreign key(cno) references course(cno));
	
#定义sc表的参照完整性
create table sc(
	sno char(9),
	cno char(4),
	grade smallint,
	primary key(sno,cno),
	foreign key(sno) references student(sno) on delete cascade on update cascade,
	foreign key(cno) references course(cno) on delete no action on update cascade);
1）第一行on delete cascade 表示当删除student表中元组时，级联删除sc表中对应元组
2）第一行on update cascade 表示当更新student表中sno时，级联更新sc表中对应元组
3）第二行on delete no action 表示当删除course表中元组造成与sc表不一致时，拒绝删除
4）第二行on update cascade 表示当更新course表中cno时，级联更新sc表中对应元组

3、用户定义的完整性（约束条件）

1）约束条件包括

  • 列值非空（not null）
  • 列值唯一（unique）
  • 检查列值是否满足一个条件表达式（check 短语）
1
2
3
4
5
create table sc(
	sno char(9) not null,
	cno char(4) unique not null,
	grade smallint check(grade>=0 and grade<=100),
	primary key(sno,cno));

4、完整性约束命名子句（constraint）

1）语法

1
2
3
4
constraint <约束名> <约束条件>

约束条件包括：
not null，unique，primary key，foreign key，check短语等

2）举例

　　建立学生表student，要求学生学号在1~99之间，姓名不能取空值，年龄小于30，性别只能是男或者女。

1
2
3
4
5
6
7
8
9
10
create table student(
	sno numeric(6)
		constraint c1 check(sno between 1 and 99),
	sname char(20)
		constraint c2 not null,
	sage numeric(3)
		constraint c3 check(sage<30),
	ssex char(2)
		constraint c4 check(ssex in('男','女'))，
		constraint studentkey primary key(sno));

　　去掉上例中对性别的限制

1
drop constraint c4;

　　将上例中年龄改为25岁以下

1
2
3
4
alter table student
	drop constraint c3;
alter table student
	add constraint c3 check(sage<25);

注意：修改约束只能删除原有约束再新建约束。

5、断言（assertion）

　　断言可以定义涉及多个表或聚集操作的比较复杂的完整性约束，断言创建以后，任何对断言中所涉及关系的操作都会触发关系数据库的管理系统对断言的检查，任何使断言部位真值的操作都会被拒绝执行。

1）语法

1
create assertion <断言名> <check子句>

2）举例

　　限制数据库课程最多60名学生选修。（涉及到两个表）

1
2
3
4
create assertion a1
	check(60>=
			(select count(*) from sc,course
			where sc.cno=course.cno and couese.sname='数据库'));

　　每当学生选修课程的时候，将在sc表中插入一条元组（sno，cno，null）,断言a1会被出发检查，如果此时数据库课程选课人数已经超过60人，则check子句返回 ‘假’，那么此插入操作会被拒绝。

　　限制每个学期每门课程最多只能60名学生选修。

1
2
3
4
5
alter table sc add term date;
create assertion a2
	check(60>=
			all(select count(*) from sc
			group by cno,term));

注意： 上例中，先新增一个属性列term，其格式为date，指的是这一列专门用来区分课程是哪个学期的；然后增加断言a2，以cno，term为准分组，即按照不同学期的不同课程分组。

3）删除断言

　　如果断言很复杂，那么系统在检测和维护断言上的开销较高，这时就该考虑是否删除断言了。

1
drop assertion <断言名>

6、触发器（trigger）

　　触发器又叫“事件-条件-动作”规则，当指定事件发生，对条件检查，若条件成立则执行规则，否则不执行。规则的动作体可以很复杂，可以涉及其他表和其他数据库对象。

1）语法

1
2
3
4
5
create trigger <触发器名>
{before|after} <触发事件> on <表名>
referencing new|old row as <变量>
for each {row|statement}
[when <触发条件>] <触发动作体>

2）说明

  • 只有表的创建者（拥有者）才可以在表上创建触发器，且触发器数量有限（由RDBMS决定）；
  • 触发器名必须唯一；
  • 触发器只能定义在基本表上，不能定义在视图上；
  • before|after表示激活触发器的时机，before表示在触发事件的操作执行之前激活触发器；
  • row|statement表示行级触发器和语句级触发器；
  • when <触发条件>在触发器被激活后检查条件是否为真，为真则触发器动作体执行，否则不执行；
  • when <触发条件>可以省略，即省略了检查条件的步骤，相当于没有“where”子句一样；
  • 有时候由于动作提设计有误等原因，触发动作体执行失败，那么此时，激活触发器的事件就会终止执行；

3）触发器执行顺序

　　一个数据表上可能定义了多个触发器，如多个before触发器、多个after触发器等，同一个表上的多个触发器激活时的执行顺序如下

  • 执行该表上的before触发器
  • 激活触发器的sql语句
  • 执行该表上的after语句

4）举例

　　将每次对表student的插入操作所增加的学生个数记录到表snum中。

1
2
3
4
5
6
7
create trigger scount
after insert on student
referencing
	new table as delta
	for each statement
		insert into snum(numbers)
		select count(*) from delta;

5）删除触发器

　　每一次访问一个表都可能触发一个触发器，因此很容易影响系统性能，需要慎重使用。

1
drop trigger <触发器名> on <表名>

7、范式

1）相关概念

  • 关系数据库中的关系需要满足一定要求，满足不同程度要求的为不同范式。
  • X->Y：X函数确定Y，或者说Y函数依赖于X；
  • 若候选码多于一个，则选定其中一个作为主码；
  • 包含在任何一个候选码中的属性称为主属性；
  • 不包含在任何候选码的属性称为非主属性；
  • 整个属性组是码时，称为全码；

2）找候选码的三个准则

  • 未出现在函数依赖中的属性
  • 未出现在函数依赖右边的属性
  • 只在右边出现的属性一定不是候选码的一部分

注意：由候选码可以决定所有属性

3）举例

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
1、R({W,X,Y,Z},{X->Z,WX->Y}
	KEY=(W,X)
解析：
1）排除Z和Y（准则3）
2）仅由X无法决定所有属性，需要再加一个W，因此KEY=(W,X)


2、R({A,B,C,D,E,I},{A->D,AB->E,E->C,BI->E,CD->I})
	KEY=(A,B)
解析：
1）函数依赖右边无A、B，（准则2）
2）仅由A、B觉可以推出其他属性了，所以KEY=(A,B)


3、R({J,S,P},{(S,J)->P,(J,P)->S})
	KEY=(J,S)或者KEY=(J,P)，此时主属性为J,S,P
解析：
1）确定J为候选码的一部分（准则2）
2）验证发现(J,S)或者(J,P)均可决定所有属性，因此KEY=(J,S)或者KEY=(J,P)，此时主属性为J,S,P。

4）各个范式关系（这里<不是小于号，是包含于的意思）

1
5NF<4NF<BCNF<3NF<2NF<1NF
欢迎打赏，谢谢
------ 本文结束------
0%