Microsoft PowerPoint - 第十章完整性-new

Size: px

Start display at page:

Download "Microsoft PowerPoint - 第十章完整性-new"

素上官
7 years ago
Views:

1 第十章数据库完整性什么是数据库的完整性完整性控制 Oracle 的完整性完整性什么是数据库的完整性数据库的完整性是指数据的正确性和相容性, 防止不合语义的数据进入数据库例 : 学生的年龄必须是整数, 取值范围为 ; 学生的性别只能是男或女 ; 学生的学号一定是唯一的 ; 学生所在的系必须是学校开设的系 ; 数据库是否具备完整性关系到数据库系统能否真实地反映现实世界, 因此维护数据库的完整性是非常重要的完整性控制机制定义机制 : 根据实际应用需求, 数据库中的数据应满足一些规定的条件是数据模型的一个重要组成部分, 它约束了数据库中数据的语义 DBMS 应提供手段让用户根据现实世界的语义定义数据库的, 并把它们作为模式的一部分存入数据库中完整性检查机制 DBMS 检查用户发出的操作请求是否违背了违约反应如果发现用户的操作请求使数据违背了, 则采取一定的动作来保证数据的完整性提纲什么是数据库的完整性完整性控制 Oracle 的完整性整个完整性控制都是围绕进行的, 从这个角度说, 是完整性控制机制的核心作用的对象对象列对属性的取值类型范围精度等的约束条件元组对元组中各个属性列间的联系的约束关系对若干元组间关系集合上以及关系之间的联系的约束

2 作用的对象分类对象的状态静态对静态对象的约束是反映数据库状态合理性的约束这是最重要的一类完整性约束动态对动态对象的约束是反映数据库状态变迁的约束值的约束指对数据取值类型范围精度等的规定, 例如对属性规定某个值集, 规定属性取值类型格式 ( 职工号第一位为字母, 后面是四位数字 ) 结构约束指对数据之间联系的限制, 以关系库为例 : 同一关系的不同属性之间可能满足一定的约束条件 (A1θA2); 不同关系之间的属性取值也可能要满足一定的约束条件 ( 外键取值 ) 分类静态约束指数据库每一确定状态的数据应满足的约束条件, 例如外键约束等动态约束指 DB 从一种状态转变为另外一种状态时, 新值和旧值之间应满足的约束条件, 例如工资调整时, 要求新工资不低于旧工资 ) 对象状态动态列级约束动态元组约束动态关系约束动态静态列级约束静态元组约束静态关系约束静态列元组关系对象粒度 1 静态列级约束静态列级约束是对一个列的取值域的说明, 这是最常见最简单同时也最容易实现的一类完整性约束五类静态列级约束 1) 对数据类型的约束, 包括数据的类型长度单位精度等例 : 规定学生姓名的数据类型应为字符型, 长度为 8 2) 对数据格式的约束例 : 规定学号的格式为前两位表示入学年份, 后四位为顺序编号出生日期的格式为 YY.MM.DD

3 3) 对取值范围或取值集合的约束例 : 规定成绩的取值范围为 0-100, 年龄的取值范围为 14-29, 性别的取值集合为 [ 男, 女 ] 4) 对空值的约束空值表示未定义或未知的值, 它与零值和空格不同有的列允许空值, 有的则不允许例如规定成绩可以为空值 5) 其他约束例 : 关于列的排序说明, 组合列等 2 静态元组约束静态元组约束就是规定组成一个元组的各个列之间的约束关系例如 : 订货关系中包含发货量订货量等列, 发货量不得超过订货量教师关系中包含职称工资等列, 教授的工资不得低于指定的值静态元组约束只局限在单个元组上 3 静态关系约束在一个关系的各个元组之间或者若干关系之间常常存在各种联系或约束常见静态关系约束 1) 实体完整性约束 2) 参照完整性约束 3) 函数依赖约束见 11 章 4) 统计约束统计约束定义某个字段值与一个关系多个元组的统计值之间的约束关系例 : 规定部门经理的工资不得高于本部门职工平均工资的 5 倍, 不得低于本部门职工平均工资的 2 倍本部门职工的平均工资值是一个统计计算值 4 动态列级约束动态列级约束是修改列定义或列值时应满足的约束条件常见动态列级约束 1) 修改列定义时的约束例 : 规定将原来允许空值的列改为不允许空值时, 如果该列目前已存在空值, 则拒绝这种修改 2) 修改列值时的约束修改列值有时需要参照其旧值, 并且新旧值之间需要满足某种约束条件例 : 职工工资调整不得低于其原来工资, 学生年龄只能增长 5 动态元组约束动态元组约束是指修改某个元组的值时需要参照其旧值, 并且新旧值之间需要满足某种约束条件例 : 职工工资调整不得低于其原来工资 + 工龄 *1.5

4 6 动态关系约束动态关系约束是加在关系变化前后状态上的限制条件例 : 事务一致性原子性等约束条件状态小结静态动态粒度列级列定义类型格式值域空值改变列定义或列值元组级元组值应满足的条件元组新旧值之间应满足的约束条件关系级实体完整性约束参照完整性约束函数依赖约束统计约束关系新旧状态间应满足的约束条件提纲完整性控制什么是数据库的完整性完整性控制 Oracle 的完整性关系系统三类完整性的实现参照完整性的实现的主要功能定义功能允许用户定义各类检查功能检查用户提供的请求是否违背了违约反应如果发现用户的操作违背了, 则采取一定的动作来保证数据的完整性 ( 一般是取消该事务已产生的影响 -- 恢复 ) 检查功能立即执行的约束 (Immediate constraints) 检查是否违背完整性约束的时机通常是在一条语句或事务执行完后立即检查延迟执行的约束 (Deferred constraints) 在某些情况下, 完整性检查需要延迟到整个事务执行结束后再进行

5 例 1: begin transaction update Students set Sno= where Sno= ; update SC set Sno= where Sno= ; commit 如是立即执行约束, 事务不能成功执行如是延迟执行约束, 事务可以成功执行例 2: 银行数据库中借贷总金额应平衡的约束就应该是延迟执行的约束从账号 A 转一笔钱到账号 B 为一个事务, 从账号 A 转出去钱后账就不平了, 必须等转入账号 B 后账才能重新平衡, 这时才能进行完整性检查违约反应拒绝该操作其他处理方法完整性规则的形式化表述一条完整性规则可以用一个五元组表示 : (D,O,A,C,P) D(Data) 约束作用的数据对象 ; O(Operation) 触发完整性检查的数据库操作, 即当用户发出什么操作请求时需要检查该完整性规则, 是立即检查还是延迟检查 ; A(Assertion) 数据对象必须满足的断言或语义约束, 这是规则的主体 ; C(Condition) 选择 A 作用的数据对象值的谓词 ; P(Procedure) 违反完整性规则时触发的过程例 1: 在学号不能为空的约束中 D 约束作用的对象为 Sno 属性 O 插入或修改 Student 元组时 A Sno 不能为空 C 无 (A 可作用于所有记录的 Sno 属性 ) P 拒绝执行该操作例 2: 在教授工资不得低于 6000 元的约束中 D 约束作用的对象为工资 Sal 属性 O 插入或修改职工元组时 A Sal 不能小于 6000 C 职称 = 教授 (A 仅作用于职称 = 教授的记录 ) P 拒绝执行该操作

6 关系系统三类完整性的实现参照完整性的实现目前许多关系数据库系统都提供了定义和检查实体完整性参照完整性和用户定义的完整性的功能对于违反实体完整性规则和用户定义的完整性规则的操作一般都是采用拒绝执行的方式进行处理而对于违反参照完整性的操作, 并不都是简单地拒绝执行, 有时还需要采取另一种方法, 即接受这个操作, 同时执行一些附加的操作, 以保证数据库的状态仍然是正确的例 : 职工 - 部门数据库包含职工表 EMP 和部门表 DEPT DEPT 关系的主码为部门号 Deptno EMP 关系的主码为职工号 Empno, 外码为部门号 Deptno 该 Deptno 与 DEPT 关系中 Deptno 相对应称 DEPT 为被参照关系或目标关系,EMP 为参照关系 (1) 外码是否可以接受空值的问题外码是否能够取空值是依赖于应用环境的语义的在实现参照完整性时, 系统除了应该提供定义外码的机制, 还应提供定义外码列是否允许空值的机制例 1: 在职工 - 部门数据库中,EMP 关系包含有外码 Deptno, 某一元组的这一列若为空值, 表示这个职工尚未分配到任何具体的部门工作这和应用环境的语义是相符的, 因此 EMP 的 Deptno 列应允许空值例 2: 在学生 - 选课数据库中,Student 关系为被参照关系, 其主码为 Sno SC 为参照关系, 外码为 Sno 若 SC 的 Sno 为空值, 则表明尚不存在的某个学生, 或者某个不知学号的学生, 选修了某门课程, 其成绩记录在 Grade 列中这与学校的应用环境是不相符的, 因此 SC 的 Sno 列不能取空值 (2) 删除被参照关系的元组时的问题出现违约操作的情形需要删除被参照关系的某个元组, 而参照关系有若干元组的外码值与被删除的被参照关系的主码值相对应违约反应 : 可有三种策略级联删除 (CASCADES) 受限删除 (RESTRICTED) 置空值删除 (NULLIFIES) 三种处理方法, 哪一种是正确的, 要依应用环境的语义来定级联删除将参照关系中所有外码值与被参照关系中要删除元组主码值相对应的元组一起删除受限删除只有当参照关系中没有任何元组的外码值与要删除的被参照关系的元组的主码值相对应时, 系统才执行删除操作, 否则拒绝此删除操作置空值删除删除被参照关系的元组, 并将参照关系中所有与被参照关系中被删除元组主码值相等的外码值置为空值

7 例 : 要删除 Student 关系中 Sno= 的元组, 而 SC 关系中有 4 个元组的 Sno 都等于级联删除 : 将 SC 关系中所有 4 个 Sno= 的元组一起删除如果参照关系同时又是另一个关系的被参照关系, 则这种删除操作会继续级联下去受限删除 : 系统将拒绝执行此删除操作置空值删除 : 将 SC 关系中所有 Sno= 的元组的 Sno 值置为空值 ( 不符合应用语义 ) (3) 修改被参照关系中主码的问题 1) 不允许修改主码策略 2) 允许修改主码策略违约操作要修改被参照关系中某些元组的主码值, 而参照关系中有些元组的外码值正好等于被参照关系要修改的主码值要修改参照关系中某些元组的主码值, 而被参照关系中没有任何元组的外码值等于被参照关系修改后的主码值 (3) 修改被参照关系中主码的问题 1) 不允许修改主码策略 2) 允许修改主码策略违约反应 ( 一 ) 修改的关系是被参照关系 : 与删除类似级联修改受限修改置空值修改违约反应级联修改修改被参照关系中主码值同时, 用相同的方法修改参照关系中相应的外码值受限修改拒绝此修改操作只当参照关系中没有任何元组的外码值等于被参照关系中某个元组的主码值时, 这个元组的主码值才能被修改置空值修改修改被参照关系中主码值, 同时将参照关系中相应的外码值置为空值例 : 学生休学一年后复学, 这时需要将 Student 关系中 Sno= 的元组中 Sno 值改为而 SC 关系中有 4 个元组的 Sno= 级联修改 : 将 SC 关系中 4 个 Sno= 元组中的 Sno 值也改为如果参照关系同时又是另一个关系的被参照关系, 则这种修改操作会继续级联下去受限修改 : 只有 SC 中没有任何元组的 Sno= 时, 才能修改 Student 表中 Sno= 的元组的 Sno 值改为置空值修改 : 将 Student 表中 Sno= 的元组的 Sno 值改为而将 S 表中所有 Sno= 的元组的 Sno 值置为空值在学生选课数据库中只有第一种方法是正确的 (3) 修改被参照关系中主码的问题 1) 不允许修改主码策略 2) 允许修改主码策略违约反应 ( 二 ) 修改的关系是参照关系 : 与插入类似受限修改递归修改

8 参照完整性的实现提纲结论 RDBMS 在实现参照完整性时, 除了需要向用户提供定义主码外码的机制外, 还需要向用户提供按照自己的应用要求选择处理依赖关系中对应的元组的方法什么是数据库的完整性完整性控制 Oracle 的完整性 Oracle 的完整性 ORACLE 中的实体完整性 Oracle 中的实体完整性 Oracle 中的参照完整性 Oracle 中用户定义的完整性 ORACLE 在 CREATE TABLE 语句中提供了 PRIMARY KEY 子句, 供用户在建表时指定关系的主码列在列级使用 PRIMARY KEY 子句在表级使用 PRIMARY KEY 子句 ORACLE 中的实体完整性例 1: 在学生选课数据库中, 要定义 Student 表的 Sno 属性为主码 CREATE TABLE Student (Sno NUMBER(8), Sname VARCHAR(20), Sage NUMBER(20), CONSTRAINT PK_SNO PRIMARY KEY (Sno)); 或 : CREATE TABLE Student (Sno NUMBER(8) PRIMARY KEY, Sname VARCHAR(20), Sage NUMBER(20)); ORACLE 中的实体完整性例 2: 要在 SC 表中定义 (Sno, Cno) 为主码 CREATE TABLE SC (Sno NUMBER(8), Cno NUMBER(2), Grade NUMBER(2), CONSTRAINT PK_SC PRIMARY KEY (Sno, Cno));

9 ORACLE 中的实体完整性在用 PRIMARY KEY 语句定义了关系的主码后, 每当用户程序对主码列进行更新操作时, 系统自动进行完整性检查违约操作使主属性值为空值的操作使主码值在表中不唯一的操作违约反应系统拒绝此操作, 从而保证了实体完整性 ORACLE 中的参照完整性 ORACLE 的 CREATE TABLE 语句允许用户定义参照完整性用 FOREIGN KEY 子句定义哪些列为外码列用 REFERENCES 子句指明这些外码相应于哪个表的主码用 ON DELETE CASCADE 短语指明在删除被参照关系的元组时, 同时删除参照关系中外码值等于被删除的被参照关系的元组中主码值的元组 ORACLE 中的参照完整性 ORACLE 中的参照完整性例 1: 建立表 EMP 表 CREATE TABLE EMP (Empno NUMBER(4), Ename VARCHAR(10), Job VERCHAR2(9), Mgr NUMBER(4), Sal NUMBER(7,2), Deptno NUMBER(2), CONSTRAINT FK_DEPTNO FOREIGN KEY (Deptno) REFERENCES DEPT(Deptno) ON DELETE CASCADE); 或 : CREATE TABLE EMP (Empno NUMBER(4), Ename VARCHAR(10), Job VERCHAR2(9), Mgr NUMBER(4), Sal NUMBER(7,2), Deptno NUMBER(2) CONSTRAINT FK_DEPTNO FOREIGN KEY REFERENCES DEPT(Deptno) ON DELETE CASCADE); ORACLE 中的参照完整性 ORACLE 中的参照完整性这时 EMP 表中外码为 Deptno, 它相应于 DEPT 表中的主码 Deptno 当要修改 DEPT 表中的 DEPTNO 值时, 先要检查 EMP 表中有无元组的 Deptno 值与之对应若没有, 系统接受这个修改操作否则, 系统拒绝此操作当要删除 DEPT 表中某个元组时, 系统要检查 EMP 表, 若找到相应元组即将其随之删除当要插入 EMP 表中某个元组时, 系统要检查 DEPT 表, 先要检查 DEPT 表中有无元组的 Deptno 值与之对应若没有, 系统拒绝此插入操作否则, 系统接受此操作

10 ORACLE 中定义用户完整性的两类方法 (1) 用 CREATE TABLE 语句在建表时定义用户完整性约束 (2) 使用 alter table (1) 用 CREATE TABLE 语句在建表时定义用户完整性约束可定义三类完整性约束列值非空 (NOT NULL 短语 ) 列值唯一 (UNIQUE 短语 ) 检查列值是否满足一个布尔表达式 (CHECK 短语 ) (3) 通过触发器来定义用户的完整性规则例 1: 建立部门表 DEPT, 要求部门名称 Dname 列取值唯一, 部门编号 Deptno 列为主码 CREATE TABLE DEPT (Deptno NUMBER, Dname VARCHAR(9) CONSTRAINT U1 UNIQUE, Loc VARCHAR(10), CONSTRAINT PK_DEPT PRIMARY KEY (Deptno)); 其中 CONSTRAINT U1 UNIQUE 表示约束名为 U1, 该约束要求 Dname 列值唯一例 2: 建立学生登记表 Student, 要求学号在至之间, 年龄 <29, 性别只能是男或女, 姓名非空 CREATE TABLE Student (Sno NUMBER(5) CONSTRAINT C1 CHECK (Sno BETWEEN AND 99999), Sname VARCHAR(20) CONSTRAINT C2 NOT NULL, Sage NUMBER(3) CONSTRAINT C3 CHECK (Sage < 29), Ssex VARCHAR(2) CONSTRAINT C4 CHECK (Ssex IN (' 男 ', ' 女 ')); 例 3 : 建立职工表 EMP, 要求每个职工的应发工资不得超过 3000 元应发工资实际上就是实发工资列 Sal 与扣除项 Deduct 之和 CREATE TABLE EMP (Eno NUMBER(4) Ename VARCHAR(10), Job VARCHAR(8), Sal NUMBER(7,2), Deduct NUMBER(7,2) Deptno NUMBER(2), CONSTRAINTS C1 CHECK (Sal + Deduct <=3000));

11 (2) 使用 alter table 例 1: 修改缺省值原来 : create table Students(, sex char(1) default M ); 替换 : alter table Students replace sex default F (2) 使用 alter table 修改缺省值原来 : create table Students(, sex char(1) default M ); 替换 : alter table Students replace sex default F ; 增加约束 alter table Students add constraint chk_sa check (SA<30); 删除已有约束 alter table Students drop constraint chk_sa; (3) 通过触发器 (Trigger) 来定义用户的完整性规则触发器是一种特殊类型的存储过程, 通过事件 ( 插入删除和更新 ) 触发执行是实现复杂完整性约束的有效方法在应用中的其他作用维护复制数据保持导出数据列的当前值 ( 自动更新导出列 ) 业务规则限制( 书价不许一次提价超过 10%) 实现定制记录( 监控 : 不许周六周日插入表, 银行必须记录大于的事务等 ) (3) 通过触发器 (Trigger) 来定义用户的完整性规则三部分事件 : 激活触发器条件 : 测试触发器是否满足运行的条件动作 : 触发器运行后执行的动作数据库触发器是一类靠事件驱动的特殊过程, 一旦由某个用户定义, 任何用户对该数据的增删改操作均由服务器自动激活相应的触发子, 在核心层进行集中的完整性控制定义数据库触发器的语句 CREATE [OR REPLACE] TRIGGER 例 4: 为教师表 Teacher 定义完整性规则教授的工资不得低于 800 元, 如果低于 800 元, 自动改为 800 元 CREATE TRIGGER UPDATE_SAL BEFORE INSERT OR UPDATE OF Sal, Pos ON Teacher FOR EACH ROW WHEN (:new.pos=' 教授 ') BEGIN IF :new.sal<800 THEN :new.sal:=800; END IF; END;

12 约束和触发器触发器用于维护数据库一致性, 但最好考虑用约束来实现同样的目的 ; 触发器根据操作来定义一致性 ; 约束易理解, 能给 DBMS 较多机会优化执行 ; 约束可以阻止任何语句违反一致性 ; 触发器只能由特定语句来激活, 比较灵活 ; 触发器的其他用途 ( 如维护复制数据库等 ) Sybase 的完整性在 Sybase 中解决完整性的方法还有规则索引触发器等规则创建一个值的清单或值的集合的规则 CREATE RULE rul_state in ( CA, CO, WA ) 创建一个值的范围规则 CREATE RULE rul_disc_range between 0 and 100 创建一个值由编辑掩码确定的规则 CREATE RULE rul_part_num like [BF][1-9][1-9]_ Sybase 的完整性规则将规则 rul_state 与 authors.state 列和 publishers.state 列相绑定 : EXEC sp_bindrule rul_state, authors.state EXEC sp_bindrule rul_state, publishers.state 将规则 rul_disc_range 与 discounts.discount 列相绑定 : EXEC sp_bindrule rul_disc_range, discounts.discount 查看规则 rul_state 的正文和其他有关的信息 : EXEC sp_helptext rul_state EXEC sp_help rul_state 删除 :DROP RULE rul_state Sybase 的完整性唯一索引 : 限制唯一索引列的取值 ( 唯一 ) CREATE [UNIQUE][CLUSTERED NONCLUSTERED] INDEX index_name ON table_name(column_name[,column_name ]) 例如 : 为 authors 表的 au_id 列创建一个唯一索引 CREATE UNIQUE INDEX idx_u_auid ON authors(au_id)

幻灯片 1

幻灯片 1 数据库系统原理 Database System Principles 四川大学计算机学院段磊 leiduan@scu.edu.cn 2014.9 第五章数据库完整性完整性的概念指数据的正确性和相容性与安全性的比较安全性防止人为恶意破坏数据库和非法存取完整性防止不合语义的不正确的数据进入数据库 DBMS 维护完整性的机制定义机制允许用户定义实体完整性参照完整性和用户定义的完整性检查机制

Microsoft PowerPoint - 第十章 完整性-new

Microsoft PowerPoint - 第十章完整性-new