UNIT two 完整性与视图

UNIT two完整性与视图 武汉大学计算机学院

思考几个问题 • 应用环境中存在很多规则，而这些规则反映为属性的取值域、属性之间的取值约束，这是数据库必须遵从的，否则，数据库中的数据会出现不一致或错误。你如何保证数据之间的约束关系的？你考虑过完整性约束的检测对系统性能的影响吗？你知道DBMS能提供什么帮助吗？ • 视图是一个虚表，应用环境中你会用到很多视图，它确实给你带来很多好处，但你真的觉得操作起来视图能与基本表一样吗？

本讲主要目标 学完本讲后，你应该能够了解： • DBMS的完整性控制的基本原理； • 完整性机制的实施会极大地影响系统性能； • 实现完整性约束的方法有非过程性的方法和过程性的方法，利用CREATE TABLE语句的列约束和表约束实现有限的非过程性地约束；利用触发器过程性地实现动态的约束； • 实体完整性和参照完整性的内容和使用； • 视图是虚表，一旦视图定义，可以像使用基本表一样操作视图，但并非所有的视图都是可更新的。

一．完整性控制的实现原理 二．完整性约束条件分类三. 主键和实体完整性四. 外键和参照完整性五. 非过程性约束的实现六. 过程性约束与触发器七. 视图的定义、查询与更新八. 视图的作用内容提纲

完整性控制的 实现原理

完整性控制的实现原理 • 完整性控制定义数据的完整性是为了防止数据库中存在不符合语义的数据，防止错误信息的输入和输出，即所谓垃圾进垃圾出所造成的无效操作和错误结果。完整性约束条件 ---- 加在数据库数据之上的语义约束条件。完整性控制---- DBMS中检查数据是否满足完整性条件的机制。

完整性规则 定义完整性约束登记 DD 完整性规则检查用户发出更新操作请求操作数据 DB DBMS的完整性子系统完整性控制的实现原理 • 完整性控制子系统（过程性约束）

完整性控制的实现原理 • 完整性控制的功能 • 定义功能 -- 提供定义完整性约束条件的机制 • 检查功能 -- 在一定的时机，检查用户操作请求是否违背完整性约束条件 • 保证数据完整性功能 -- 如果发现用户操作请求使数据违背了完整性约束，则采取一定的动作来保证数据的完整性。

完整性控制的实现原理 • 完整性约束的检测时机 • 立即执行约束 -- 在一条语句执行完后立即检查 • 延迟执行约束 -- 完整性检查延迟到整个事务执行结束后再进行，检查正确方可提交。当用户操作违背约束时，使用不同的检查时机是否影响处理方法？ • 对于立即执行约束，系统将拒绝操作 • 对于延迟执行约束，系统将拒绝整个事务

完整性约束 条件分类

完整性约束条件分类 • 完整性约束条件的对象粒度 • 关系约束 -- 是若干元组间、关系集合上以及关系之间的联系的约束。 • 元组约束 -- 是元组中各个字段间的联系的约束。 • 列约束 -- 列的类型、取值范围、精度、排序等约束。

完整性约束条件分类 • 完整性约束条件的对象状态 • 静态约束 -- 指数据库每一确定状态时的数据对象所应满足的约束条件，它是反映数据库状态合理性的约束。 • 动态约束 -- 指数据库从一种状态转变为另一种状态时，新、旧值之间所应满足的约束条件，它是反映数据库状态变迁的约束。

对象状态 动态列级约束动态元组约束动态关系约束动态静态列级约束静态元组约束静态关系约束静态对象粒度列元组关系完整性约束条件分类 • 完整性约束条件分类

完整性约束条件分类 • 完整性约束条件分类

主键和 实体完整性

主键和实体完整性 • 超键 ——超键是能唯一区分任意两行数据的列或多个列例，在表students中，通过哪些列可以区别不同的学生？ sid lname fname students telephone (lname,fname) (lname,fname,telephone) 包含超键的多个列都是超键 (sid,fname) 武汉大学计算机学院

主键和实体完整性 • 键（候选键） ——给定一个表T，标题Head(T)=A1…An。表T的一个键，有时也称为候选键，是具有以下两个特征的一组属性的集合K=Ai1…Aik: • 如果u,v是T中两个不同的元组，则u[K]≠v[K]; • 没有K的真子集H具有特征1）。 • 表在某一时刻的内容无法告诉我们表中的键是什么 • 每个表都至少有一个键武汉大学计算机学院

主键和实体完整性 • 主键 ——主键是被数据库设计者选择出来作为表T的行的唯一性标识符的候选键 • 键是客观的概念，主键是主观的概念例下表中的键和主键是什么？如果学校不允许重名，则键为sid,(lname,fname). 可以任选一个作主键如果学校允许重名呢？武汉大学计算机学院

主键和实体完整性 • 实体完整性规则 ——表T中的任意行在主键列的取值都不允许为空下面的理解中，哪个是正确的？规则4.1 实体完整性规则若属性A是基本关系R的主属性，则属性A不能取空值。规则4.1 实体完整性规则若属性A是基本关系R的主键属性，则属性A不能取空值。规则4.1 实体完整性规则任何主键属性都不能为空。规则4.1 实体完整性规则主键不能为空。？ √ √ ╳ 武汉大学计算机学院

主键和实体完整性 • 什么操作可能破坏实体完整性规则？假定关系R的主键为K，对R的操作有：检索和更新两大类。 • 检索操作不可能破坏实体完整性规则 • 更新操作： • 插入元组 • 删除元组 • 修改元组（可能，当插入元组的主键属性为空时）（不可能）（可能，当修改元组的主键属性为空时）（修改元组 = 删除元组 + 插入元组）武汉大学计算机学院

enrollment enrollment sid cno major first sid cno major time 1 101 No Y 1 101 No 1 1 108 Yes Y 1 108 Yes 1 2 105 No Y 2 105 No 1 3 101 Yes Y 3 101 Yes 1 3 108 No Y 3 108 No 1 5 102 No Y 5 102 No 1 5 105 No Y 5 105 No 1 主键和实体完整性 • 如何选择有效的主键？ enrollment sid 为主键? cno 为主键? (sid,cno)为主键 ? (sid,cno,major)为主键？  (sid,cno，first)为主键 ? (sid,cno，time)为主键 ?  主键的选择可能决定系统的功能   武汉大学计算机学院

主键和实体完整性 • 创建表时定义主键,DBMS自动支持实体完整性定义表语句的基本语法 CREATE TABLE 〈表名〉（ (<列名><数据类型>[NOT NULL│NULL] [UNIQUE] [, <列名><数据类型>[NOT NULL│NULL] [UNIQUE]]… [, PRIMARY KEY （<列名>[，<列名>] …）] [, FOREIGN KEY （<列名>[，<列名>] …）REFERENCES <被参照表名>] ）；武汉大学计算机学院

主键和实体完整性 • 标准化 • SQL-86 • “数据库语言SQL” • SQL-89 • “具有完整性增强的数据库语言SQL”，增加了对完整性约束的支持 • SQL-92 • “数据库语言SQL”，是SQL-89的超集，增加了许多新特性，如新的数据类型，更丰富的数据操作，更强的完整性、安全性支持等。 • SQL-99 • 正在讨论中的新的标准，将增加对面向对象模型的支持武汉大学计算机学院

外键和 参照完整性

KS KR F 被参照的关系参照关系外键 R S 外键和参照完整性 • 外键设F是基本关系R的一个或一组属性，但不是R的键。如果F与基本关系S的键KS相对应，则称F是R的外键（Foreign Key），并称R为参照关系（Referencing Relation），S为被参照关系（Referenced Relation）。关系R和S不一定是不同的关系。武汉大学计算机学院

外键和参照完整性 • 参照完整性规则若属性（或属性组）F是基本关系R的外键，它与基本关系S的键KS相对应（关系R和S不一定是不同的关系），则对于R中每个元组在F上的值必须为：（1）或者取空值（F的每个属性值均为空值）；（2）或者等于S中某个元组的主键值。武汉大学计算机学院

外键和参照完整性 • 实例例1两个关系间学生（学号，姓名，性别，专业号，年龄）专业（专业号，专业名）例2两个以上的关系间学生（学号，姓名，性别，专业号，年龄）课程（课程号，课程名，学分）选修（学号，课程号，成绩）例3同一关系学生2（学号，姓名，性别，专业号，年龄，班长）武汉大学计算机学院

KS KR F 被参照的关系参照关系外键 R S 外键和参照完整性 • 什么操作可能破坏参照完整性武汉大学计算机学院

外键和参照完整性 • 什么操作可能破坏参照完整性 • 对被参照表的删除和更新操作 • 对参照表的插入和更新操作武汉大学计算机学院

外键和参照完整性 • 创建表时定义外键,DBMS自动支持参照完整性定义表语句的基本语法 CREATE TABLE 〈表名〉（ (<列名><数据类型>[NOT NULL│NULL] [UNIQUE] [, <列名><数据类型>[NOT NULL│NULL] [UNIQUE]]… [, PRIMARY KEY （<列名>[，<列名>] …）] [, FOREIGN KEY （<列名>[，<列名>] …）REFERENCES <被参照表名>] ）；武汉大学计算机学院

外键和参照完整性 • 实现参照完整性要考虑的几个问题： • 外键能否接受空值的问题 • 在被参照关系中删除元组的问题 -- 级联删除、受限删除、置空值删除 • 在参照关系中插入元组的问题 -- 受限插入、递归插入 • 修改关系中主键的问题 -- 不允许修改主键、允许修改主键

外键和参照完整性 • 参照完整性被破坏时可选择的策略 DBMS在实现参照完整性时，除了要提供定义主键、外键的机制外，还需要提供不同的策略供用户选择。根据应用环境的要求，选择策略(Full SQL-99)： • NO ACTION • CASCADE • SET NULL • SET DEFAULT • RESTRICT

外键和参照完整性 • 参照完整性约束被破坏时，系统支持的触发动作： • 在子表中用INSERT或UPDATE操作插入与父表中键值不匹配的外键值时 --- RESTRICT: 拒绝对子表进行的操作 • 在父表中用DELETE或UPDATE操作删除父表与子表有匹配行的键值时 --- CASCADE：删除父表中的行，且删除子表中匹配的行 --- SET DEFAULT：删除父表中的行，且自动设置子表中的外键值为缺省值 --- SET NULL：删除父表中的行，且自动设置子表中的外键值为NULL --- NO ACTION：拒绝对父表进行的操作。是默认的动作武汉大学计算机学院

外键和参照完整性 • 定义表语句的语法扩充 CREATE TABLE 〈表名〉（ (<列名><数据类型>[NOT NULL│NULL] [UNIQUE] [DEFAULT 缺省值] [, <列名><数据类型>[NOT NULL│NULL] [UNIQUE]] [DEFAULT 缺省值] … [, PRIMARY KEY （<列名>[，<列名>] …）] FOREIGN KEY （<列名>[，<列名>] …）REFERENCES <被参照表名> [ON DELETE [CASCADE | SET DEFAULT | SET NULL | NO ACTION] ] [ON UPDATE [CASCADE | SET DEFAULT | SET NULL |NO ACTION] ] ）；不同的实现对该版本的支持有区别武汉大学计算机学院

非过程性约束 的实现

非过程性约束的实现 • Create Table语句中的完整性约束基本Create Table语句的语法： Create Table [schema.] tablename ( {columnname datatype [DEFAULT {default_constant | null}] [col_constr{col_constr …}] | table_constr} {,{columnname datatype [DEFAULT {default_constant| null}] [col_constr{col_constr …}] | table_constr} …})；

非过程性约束的实现 • Create Table语句中的列约束约束单个列的 col_constr 形式如下： {NOT NULL | [CONSTRAINT constrainname] UNIQUE | PRIMARY KEY（< columnname >[，< columnname >] …） | CHECK ( search_cond ) | FOREIGN KEY（< columnname >[，< columnname >] …） REFERENCES <tablename> [ON DELETE [CASCADE | SET DEFAULT | SET NULL | NO ACTION] ] [ON UPDATE [CASCADE | SET DEFAULT | SET NULL |NO ACTION] ] }

非过程性约束的实现 • Create Table语句中的列约束约束单个列的 col_constr 实例如下： --创建表:学生（学号，姓名，性别，专业，年龄）其中,性别只能取’F’和’M’这两个值 CREATE TABLE S （ S# CHAR（8） NOT NULL UNIQUE， SN CHAR（8） UNIQUE， SE CHAR（1） CONSTRAINT S_SE CHECK(SE IN (‘F’, ‘M’))， SA SMALLINT， SD CHAR（4）， PRIMARY KEY（S#））；

非过程性约束的实现 • Create Table语句中的表约束约束表的CHECK ( search_cond )实例如下： --创建表:学生（学号，姓名，性别，专业，年龄）其中,如果性别为男,姓名不能以Ms.开头 CREATE TABLE S （ S# CHAR（8） NOT NULL UNIQUE， SN CHAR（8） UNIQUE， SE CHAR（1） CONSTRAINT SSE CHECK(SE IN (‘F’, ‘M’))， SA SMALLINT， SD CHAR（4）， PRIMARY KEY（S#), CHECK (SE = ‘F’ OR SN NOT LIKE ‘Ms.%’) ）；

非过程性约束的实现 • Alter Table语句 Alter Table 语句允许DBA改变原先在Create Table 语句中定义的表的结构，加入或改变列、加入或删除各种约束等。约束的名字的用处：如果表约束被命了名，表所有者可以用这个名字DROP该约束。

非过程性约束的实现 • Alter Table语句 ORACLE Alter Table ALTER TABLE tblname [ADD ({colname datatype [DEFAULT {default_const|NULL}] [col_constr {col_constr...}] | table_constr} -- choice of colname-def. or table_constr {, ...})] -- zero or more added colname-defs. or table_constrs. [DROP {COLUMN columnname | (columnname {, columnname…})}] [MODIFY (columnname data-type [DEFAULT {default_const|NULL}] [[NOT] NULL] {, . . .})] -- zero or more added colname-defs. [DROP CONSTRAINT constr_name] [DROP PRIMARY KEY] [disk storage and other clauses (not covered, or deferred)] [any clause above can be repeated, in any order] [ENABLE and DISABLE clauses for constraints];

非过程性约束的实现 • 用CREATE TABLE及ALTER TABLE实现非过程性约束 • NOT NULL • UNIQUE • CHECK • PRIMARY KEY • FOREIGN KEY

过程性约束与 触发器

过程性约束与触发器 • 触发器触发器（Trigger）就是一类靠事件驱动的特殊过程，一旦由某个用户定义，任何触发该触发器的事件发生时，均由服务器自动激活相应的触发器。触发器包含三个要素： • 触发事件（条件） • 触发时机 • 触发动作

过程性约束与触发器 • 触发器 • 触发器的定义 • 指明什么条件下触发器被执行 • 指明触发器执行的动作是什么 • 触发器的作用 • 示警 • 满足特定条件时自动执行某项任务 • 触发器事件 • Insert、delete、update

{ } create trigger trigger-name insert delete update { } [of column-name] old row as identifier new row as identifier { } referencing each row { } for each statement when(search-condition) { } begin atomic triggered-SQL-statement end 过程性约束与触发器 begin after • 定义触发器的语句(Full SQL-99的Create Trigger语法) on table-name

过程性约束与触发器 • 用触发器实现过程性约束 • 在定义触发器动作时,各DBMS产品用自己特定的过程性语言,不可能给出一个Create Trigger的”基本SQL”版本 • 不同产品的Create Trigger形式中,非动作部分很相似; • 大部分DBMS,如SYSBASE、ORACLE和INFORMAX都提供包含常驻内存的变量、循环控制和if-then-else逻辑的过程语言的扩展.如SYSBASE、ORACLE和INFORMAX的T-SQL 、PL/SQL和SPL;DB2 UDB的过程扩展性少一些

过程性约束与触发器 • ORACLE的Create Trigger语法 CREATE [OR REPLACE] TRIGGERtrigger_name {BEFORE | AFTER | INSTEAD OF} {INSERT | DELETE | UPDATE [OF columnname{,columnname…}]} ON tablename [REFERENCING corr_name_def {, corr_name_def…}] [FOR EACH ROW | FOR EACH STATEMENT] [WHEN (search_condition)] | BEGIN ATOMIC statement; {statement; …} END ; 定义行的相关名字的corr_name_def 如下: {OLD old_row_corr_name | NEW new_row_corr_name}

过程性约束与触发器 • 在ORACLE中,用触发器实现完整性约束例,使用触发器来检查,在学生表中,新的学生行中,性别必须是’F’或’M’ create triggersse after insert on s referencing new as x for each row when x .se not in (‘F’, ‘M’) begin raise_application_error(-20003, ‘invalid discount attempted on insert’); end ;

UNIT two 完整性与视图