资源描述
第第5 5章完整性约束与章完整性约束与模式分解模式分解讲课内容：讲课内容：完整性约束的目的就是防止对数据库中数据的意外破坏意外破坏！在前面讲过的E-R模型中，就已经提到过完整性约束：即码和联系的形态。而模式分解则是得到最优的数据库逻辑设计的关键!域约束域约束触发器触发器关系关系DBDB设计设计参照完整性参照完整性函数依赖函数依赖模式分解模式分解断言断言码与范式码与范式规范化规范化本章总结本章总结7/3/202415.15.1域约束域约束数据库的完整性是指数据的正确性和相容性，例如：学生的学号必须唯一、性别只能是男或女等等。数据库的完整性约束强加在数据库数据之上的语义约束称为数据库的完整性约束；它们作为模式的一部分存入数据库中DBMS中检查数据是否满足完整性约束的机制称为完整性检查一般来说，完整性约束可以是与数据库有关的任意谓词(条件)。7/3/202425.15.1域约束域约束完整性约束的类型检测任意谓词的代价太高，通常只局限于那些只需极小开销就可检测的完整性约束：建立在列(域、字段)上的约束建立在行(记录)上的约束建立在整个表或库(多个表)上的约束最基本的约束前面的SQL DDL讲到了在定义关系模式时要指明属性的域，这就是数据库中的域约束；每当有新数据插入到数据库时或修改数据库中的数据时，系统都要进行域约束检查；域约束是最基本的完整性约束。7/3/202435.15.1域约束域约束用户自定义的域类型create domain name_type char(10)constraint name_type_test check (value in(张三,李四,王五)check约束主要用来检查域的属性值是否符合规定的条件，例如：Check(value not null)：检查域的属性值是否为空值；Check(value=10)：检查域的属性值是否大于等于10。7/3/202445.15.1域约束域约束域约束的检查时机SQL的属性域是否合法在编译阶段就强制进行检查而不是执行时（事务提交之前），例如：update person set age=qwe所以在DBMS查询处理的语法分析阶段就需要访问“数据字典”。强类型语言SQL的优缺点编译器能够细致地检查属性类型的合法性；限制了类型转换的灵活性；由于数据库系统主要用于支持非计算机专家的使用，所以强类型检查往往利大于弊。7/3/202455.25.2参照完整性参照完整性问题的提出所谓的参照完整性就是说一个关系中给定属性集的取值都来自于另一关系中某个属性集的取值，例如关系teacher和department：Teacher_schema=(teacher_number,teacher_name,teacher_age,department_name)Department_schema=(department_name,department_location,department_leader)teacher中属性department_name的取值必须来自于department中属性department_name的值集。7/3/202465.25.2参照完整性参照完整性有趣的现象对于上面两个关系模式及具体的关系teacher和关系department，那么以下两种情况是完全不同的：关系teacher中department_name的属性值有一个为“信息技术系”，但它在关系department中是不存在的；关系department中department_name的属性值有一个为“医学系”，但它从来都没有在关系teacher中出现过。上面两种情况谁更合理呢？7/3/202475.25.2参照完整性参照完整性参照完整性的形式化定义假设关系r1(R1)和r2(R2)的主码分别为属性集K1和K2。称R2的子集为参照关系r1中K1的外码(广义与狭义之分)，是要求对r2中的任意元组t2，均存在r1中的元组t1，使得：t2=t1K1 这种要求就称为参照完整性约束，或子集依赖。子集依赖也可以表示为：(r2)K1(r1)一般来说，这里的是等于K1的(狭义外码)！谁是参照关系，谁是被参照的关系？7/3/202485.25.2参照完整性参照完整性E-R模型中的参照完整性联系集：根据E-R图中的联系集得到的每一个关系都有参照完整性约束；弱实体集：弱实体集的关系模式必须包含它所依赖的强实体集的主码，因此每个弱实体集的关系模式都包含导致参照完整性约束的外码。7/3/202495.25.2参照完整性参照完整性数据库修改，条件：(r2)K(r1)插入：检查被参照关系向r2中插入元组t2，则系统必须保证r1中存在元组t1，使得t1K=t2，即t2K(r1)；否则失败！删除：检查参照关系如果从r1中删除元组t1，则系统必须计算r2中参照t1的元组集合，即=t1K(r2)。如果该集合非空，则：报错并撤消删除命令；或者是r2中参照t1的元组也必须被删除掉，但这可能导致级联删除。7/3/2024105.25.2参照完整性参照完整性数据库修改，条件：(r2)K(r1)更新，有两种情况需要考虑(U=D+I)：如果关系r2中元组t2被更新，并且该更新修改了外码上的值，则进行类似插入情况的测试。具体做法是：令t2表示元组t2的新值，则系统必须保证：t2K(r1)；否则更新失败！如果关系r1中元组t1被更新，并且该更新修改了主码K上的值，则进行类似删除情况的测试。具体做法是：系统必须用旧的t1值(更新前的值)计算=t1K(r2)。如果该集合非空，则更新操作失败或者进行级联更新，即同时更新关系r2中满足上述条件的记录！7/3/2024115.25.2参照完整性参照完整性SQL中的参照完整性在SQL中利用DDL来具体地指明关系的主码和外码。为简便起见，规定SQL中主码的所有属性都隐含定义为not null：create table语句的primary key子句中包含一组构成主码的属性；create table语句的foreign key子句包含一组构成外码的属性及被该外码所参照的关系和对应的主码属性；狭义外码的属性名必须和主码的属性名一致。7/3/2024125.25.2参照完整性参照完整性举例，定义主码的一种形式：create table department (department_name varchar(12)not null,department_location varchar(12),department_leader varchar(10),primary key(department_name),)主码必须用primary key子句明确指定。7/3/2024135.25.2参照完整性参照完整性举例，定义狭义外码的一种形式：create table student (student_number char(7)primary key not null,student_name varchar(10),department_name varchar(12),foreign key(department_name)references department(department_name)它必须是另一关系的主码7/3/2024145.25.2参照完整性参照完整性举例，定义主码与广义外码（参照关系）create table bfsu_student (stu_name varchar(20)not null primary key,age int)create table dcs_student (DCS_name varchar(20)not null references bfsu_student(stu_name),age int)7/3/2024155.25.2参照完整性参照完整性主码、外码与参照完整性约束参照完整性不必完全是主码和狭义外码(属性名相同)的关系，也可以是满足参照完整性定义的主码和广义外码(属性名不同)的关系；但无论什么形式的外码都必须参照某个关系的主码！为什么在商用RDBMS中必须明确指定外码呢？不能因为与某个关系的主码的属性名相同，就认为它们之间一定是主码和外码的参照关系。因为用户在定义属性名的时候有很大的随意性和偶然性。例如：create table person(name，age,)create table animal(name,age,)7/3/2024165.25.2参照完整性参照完整性级联更新在SQL中当参照完整性约束被破坏时：拒绝执行破坏参照完整性的操作；或者是在SQL-92的foreign key或references子句中指明进行级联删除或级联更新。例如：create table dcs_student (DCS_name varchar(20)not null references bfsu_student(stu_name)on delete cascade,age int)7/3/2024175.25.2参照完整性参照完整性特殊的参照完整性自参照的关系，举例：插入数据的问题？create table employee (emp_id int not null primary key,emp_name varchar(30)not null,manager_id int not null references employee(emp_id)完整性的检查时机：事务提交之前！?7/3/2024185.25.2参照完整性参照完整性特殊的参照完整性在ANSI SQL中建议的解决办法是让系统推迟约束检查，直到整个事务提交；否则就根本无法插入任何一行数据。但遗憾的是不是所有的DBMS产品目前完全实现这一点，例如：insert into employee values(1,ceo,1)必须是第一条INSERT语句！insert into employee values(2,cto,1)解决办法二：在employee表中插入全部或部分数据之后，再建立自参照的关系，当然已经插入的数据一定要满足这种自参照的约束。7/3/2024195.25.2参照完整性参照完整性特殊的参照完整性两个关系相互参照create table test1(p1 char(10)not null primary key,f1 char(10)not null)create table test2(p2 char(10)not null primary key,f2 char(10)not null)能否在建表的时候创建相互参照的约束呢？alter table test1 add foreign key(f1)references test2(p2)alter table test2 add foreign key(f2)references test1(p1)7/3/2024205.25.2参照完整性参照完整性特殊的参照完整性两个关系相互参照时如何插入数据？不像自参照的关系那样，SQL Server对相互参照的关系没有实现约束检查推迟机制，即使把要插入的数据放到一个事务中：begin tran test insert into test1 values(key1,for1)insert into test2 values(for1,key1)commit tran test只能在插入数据之后再建立相互参照的约束，但已插入的数据一定要满足相互参照的约束；两个关系相互参照有什么好处？防止对数据的非法或误操作：增、删、改。7/3/2024215.35.3断言断言断言的概念所谓断言就是一个谓词，它表达了我们希望数据库总能满足的一个条件。域约束和参照完整性约束是断言的特殊形式。SQL-92中断言的定义形式create assertion check 断言的利弊一方面能够保证数据库的一致性；另一方面，检测和维护断言的开销很大，系统效率降低。一般不主张使用断言！7/3/2024225.35.3断言断言举例在学校的选课系统中规定，选修某门课程的学生总数不能大于这门课程的容量，这个限制条件可以用断言描述如下：create assertion selecting_constraint check(not exists (select*from course as c where course_capacity=65 then(insert into retiree values (R.teacher_number,R.teacher_name,new R.teacher_age)delete from teacher where teacher.teacher_number=R.teacher_number)7/3/2024255.45.4触发器触发器触发器是一种特殊的存储过程：存储过程是存储在服务器上的预先编译好的一组SQL语句，可供客户端的程序调用；存储过程与触发器和断言：断言执行的动作是不允许这样做；在触发器中用户可以自己定义要执行的动作。存储过程，包括触发器都可以看成是数据库语言的程序化(存取数据与存取逻辑)；主动数据库就是触发器的集合，未来的智能数据库更是以触发器为基础的！对一个数据库应用系统来说，当前的DBMS有效支持的触发器数目是多少？7/3/2024265.45.4触发器触发器与触发器相关的数据库选项与参数递归选项：ON/OFFrecursive triggers：该参数可以限制直接递归，但不能限制间接递归；嵌套参数：nested triggers：SQL Server 2000规定一个触发器最多只能嵌套32层。7/3/2024275.45.4触发器触发器完整性约束小结定义在一个关系上的约束：域约束DEFAULTNOT NULLPRIMARY KEY(主码)|UNIQUE(后选码)FOREIGN KEYCHECK定义在整个数据库上的约束：断言触发器create table my_table create table my_table(name varchar(20)not(name varchar(20)not null default null default FENGFENG,)7/3/2024285.45.4触发器触发器完整性约束小结约束与系统的性能约束的一致性检查顺序(事务提交之前)域约束DEFAULTNOT NULLCHECK对有FOREIGN KEY约束的参照表的检查对有FOREIGN KEY约束的被参照表的检查PRIMARY KEY|UNIQUE断言触发器7/3/2024295.55.5函数依赖函数依赖函数依赖是一种特殊的约束，它是码的概念的推广。简单地说，函数依赖是合法关系集上的约束。码的定义：令R是关系模式，R的子集K是R的超码是指对任意的合法关系r(R)及r中的任意两个元组t1和t2，总能满足以下条件：若t1t2，则t1Kt2K；函数依赖：读作函数确定，或函数依赖于。令R且R，R存在函数依赖是指对任意合法关系r(R)及r中任意两个元组t1和t2，总满足以下条件：若t1=t2，则t1=t2。7/3/2024305.55.5函数依赖函数依赖举例1如图所示，关系r上函数依赖 AC是满足的，但CA是不满足的；函数依赖ABD也是满足的。注意，这里用AB作为A,B的简写；在所有关系中都满足的函数依赖，例如：AA和ABA在所有包含属性A及B的关系中都是满足的；一般来说，如果，则形如的函数依赖是平凡的；证明：令=，7/3/2024315.55.5函数依赖函数依赖举例2假如有如下关系模式：Teacher_schema=(teacher_number,teacher_name,teacher_age,department_name)由于teacher_number是主码，因此在这个关系模式上有函数依赖集：teacher_numberteacher_nameteacher_numberteacher_ageteacher_numberdepartment_name但是，不希望有函数依赖：teacher_nameteacher_age如果事先并不知道teacher_number是主码，但是有这三个函数依赖存在，是否可以确定teacher_number是候选码？7/3/2024325.55.5函数依赖函数依赖举例3对关系course(Course_schema)来说：course_namecourse_capacity？course_locationcourse_capacity？对关系模式Course_schema来说：course_locationcourse_capacity？结论：函数依结论：函数依结论：函数依结论：函数依赖赖赖赖在关系在关系在关系在关系r(R)r(R)r(R)r(R)上是满足上是满足上是满足上是满足的，但在关系的，但在关系的，但在关系的，但在关系模式模式模式模式R R R R上却不上却不上却不上却不一定成立！一定成立！一定成立！一定成立！7/3/2024335.55.5函数依赖函数依赖小结完全函数依赖：与的任意真子集的关系部分函数依赖：与完全函数依赖的情况相反传递函数依赖：如果而且，那么 SQL不提供定义函数依赖的简便方法，但可以书写查询语句来检测函数依赖是否满足，或者是创建断言来体现函数依赖。7/3/2024345.55.5函数依赖函数依赖函数依赖集的闭包一般来说，除了给定的函数依赖集外，还需要考虑在模式上成立的其他所有函数依赖。逻辑蕴涵：如果给定函数依赖集F，可以证明其他某些函数依赖也成立，就称这些函数依赖被F逻辑蕴涵；闭包：令F为一个函数依赖集，F的闭包是指被F所逻辑蕴涵的所有函数依赖的集合，记为F+。计算函数依赖集的闭包的目的是什么？如何根据F计算F+呢？7/3/2024355.55.5函数依赖函数依赖Armstrong公理自反律：若，则增补律：若，则传递律：若及，则上述推理规则是保真的，也是完备的。为计算方便，又增加了以下定理：合并律：若及，则分解律：若，则及伪传递律：若及，则7/3/2024365.65.6码与范式码与范式基本概念主属性与非主属性：包含在主码中的属性叫做主属性，而不包含在主码中的属性叫做非主属性或非码属性。1NF：对一个关系模式来说，如果它的每一个属性都是不可再分割的（原子的），这样的关系模式就属于第一范式。2NF：若关系模式R属于1NF，且每一个非主属性都完全函数依赖于主码，则R属于第二范式。7/3/2024375.65.6码与范式码与范式基本概念3NF：对关系模式R2NF来说，如果R中不存在这样的主码X、属性集Y及非主属性Z(ZY)，使得XY、YZ成立，则R属于第三范式。即每一个非主属性既不部分依赖于主码，也不传递依赖于主码。规范化：关系模式要满足一定的要求，这种要求的不同程度称为不同的范式，即：5NF4NFBCNF3NF2NF1NF通过适当的方法可以将一个低一级范式的关系模式转化为若干个高一级范式的关系模式的集合，这个过程就叫规范化。7/3/2024385.75.7关系数据库设计关系数据库设计设计目标关系数据库的设计目标就是要生成一组关系模式，它既不必存储不必要的冗余信息，又可以方便地获取信息(不丢失信息)。设计方法关系数据库的设计方法之一就是通过规范化来设计满足适当范式要求的关系模式。设计中易犯的错误信息冗余：冗余与系统性能的关系信息丢失：绝对不允许的7/3/2024395.75.7关系数据库设计关系数据库设计有冗余的关系模式Course_teacher_schema=(course_name,course_location,course_capacity,teacher_number,teacher_name,teacher_age,department_name)该模式中的信息冗余具体是指什么？信息冗余的后果是什么？该模式有没有信息丢失的现象？插入异常删除异常如何将该模式规范化？7/3/2024405.85.8模式分解模式分解关系course_teacher(Course_teacher_schema)7/3/2024415.85.8模式分解模式分解将Course_teacher_schema分解Course_department_schema=(course_name,course_location,course_capacity,department_name)Teacher_department_schema=(teacher_number,teacher_name,teacher_age,department_name)上述分解有什么特点？以department_name为公共属性；这样的分解会产生什么样的后果呢?7/3/2024425.85.8模式分解模式分解分解之后的关系course_departmentteacher_department 7/3/2024435.85.8模式分解模式分解重新构造关系course_teacher找出课程容量超过150人的课程和相关教师的信息：利用course_department和teacher_departmentcourse_name,teacher_number,department_name (course_capacity=150(course_departmentteacher_department)查询的结果又会出现什么样的问题呢？7/3/2024445.85.8模式分解模式分解自然连接结果中多了一些莫名其妙的元组！因此，查询结果中就有了错误的答案！假阳性7/3/2024455.85.8模式分解模式分解原因以department_name为公共属性的模式分解造成了信息丢失：因为一个系开设了多门不同的课程，而且一个系有多位教师，这样分解之后就分不清哪个教师主讲哪门课程。尽管上述自然连接的结果包含了原来所有的元组，同时还产生了一些无用的信息，但要描述的真正信息却丢失了-谁讲的哪门课？由于有信息丢失，这样的模式分解称为有损分解，或有损连接分解；相反就是无损连接分解。7/3/2024465.85.8模式分解模式分解无损连接分解的形式化定义令R为关系模式，关系模式集R1,R2,Rn为R的一个分解，即：R=R1R2.Rn。令r是模式R上的关系，即r(R)，而ri=Ri(r)，于是总有：r r1 r2 rn令C表示数据库上的约束集。如果对模式R上满足C的所有合法关系r，均有：r=R1(r)R2(r)Rn(r)那么，R1,R2,Rn就是关系模式R的一个无损连接分解。7/3/2024475.85.8模式分解模式分解模式分解要达到的目标无损连接分解：没有信息丢失保持函数依赖：应用Armstrong公里分解后的模式最好是属于BCNF或3NF：数据冗余度最小；没有各种更新异常无损连接分解的条件令R为一关系模式，F为R上的函数依赖集。R1和R2为R的无损连接分解的条件是：F+中至少有如下函数依赖中的一个：R1R2R1R1R2R27/3/2024485.85.8模式分解模式分解举例Course_teacher_schema=(course_name,course_location,course_capacity,teacher_number,teacher_name,teacher_age,department_name)该模式上的函数依赖集有：course_namecourse_locationcourse_capacityteacher_numberteacher_name teacher_agedepartment_name如何根据函数依赖集进行模式分解呢？7/3/2024495.85.8模式分解模式分解分解模式Course_teacher_schema第一步，对于函数依赖：course_namecourse_locationcourse_capacity 根据增补律可以得到函数依赖：course_namecourse_namecourse_name course_locationcourse_capacity 可以将上述模式分解为两个模式：它们的公共属性为course_name；其中的一个包含course_name、course_location和course_capacity属性。7/3/2024505.85.8模式分解模式分解分解模式Course_teacher_schema第一步的分解结果：Course_schema=(course_name,course_location,course_capacity)Teacher_info_schema=(course_name,teacher_number,teacher_name,teacher_age,department_name)模式Course_schema已经满足3NF的要求，而模式Teacher_info_schema还不满足2NF的要求(而且还有信息冗余)，需要对其进行进一步的分解。7/3/2024515.85.8模式分解模式分解分解模式Course_teacher_schema第二步，对于第二个函数依赖：teacher_numberteacher_name teacher_age department_name 根据增补律可以得到如下的函数依赖：teacher_numberteacher_numberteacher_number teacher_name teacher_age department_name 将Teacher_info_schema分解为两个模式，它们的公共属性为teacher_number；其中一个包含teacher_number、teacher_name、teacher_age和department_name。7/3/2024525.85.8模式分解模式分解分解模式Course_teacher_schema第二步的分解结果：Teacher_schema=(teacher_number,teacher_name,teacher_age,department_name)Teaching_schema=(teacher_number,course_name)第三步，最终的分解结果：上面两个模式，加上：Course_schema=(course_name,course_location,course_capacity)7/3/202453小结：完整性约束与规范化小结：完整性约束与规范化完整性约束检查的时机与顺序？为什么有时要推迟约束的检查时机？规范化数据库设计的基本原则是什么？规范化的基本手段是什么？模式的规范化程度越高，就越好吗？数据冗余越少越好吗？7/3/202454
展开阅读全文