數據庫系統(tǒng)概論之數據庫完整性

中國地質大學信息工程學院數據庫系統(tǒng)概論AnIntroductiontoDatabaseSystem第五章數據庫完整性10/1/1數據庫系統(tǒng)概論之數據庫完整性第1頁什么是數據庫完整性數據正確性和相容性預防不合語義數據進入數據庫。關注是否真實地反應現實世界例:學生年紀必須是整數，取值范圍為14--29；學生性別只能是男或女；學生學號一定是唯一；學生所在系必須是學校開設系；提要5.1實體完整性5.2參考完整性5.3用戶定義完整性5.4完整性約束命名子句5.5觸發(fā)器10/1/2數據庫系統(tǒng)概論之數據庫完整性第2頁數據庫完整性與安全性

區(qū)分和聯絡兩個不一樣概念,但有一定聯絡;前者是為了預防數據庫中存在不符合語義數據,預防錯誤信息輸入和輸出,既所謂垃圾進垃圾出(GarbageinGarbageout)所造成無效操作和錯誤結果;后者是保護數據庫預防惡意破壞和非法存取;安全性辦法防范對象是非法用戶,完整性辦法防范對象是不合語義數據.完整性控制機制1.完整性約束條件定義機制2.完整性檢驗機制3.違約處理10/1/3數據庫系統(tǒng)概論之數據庫完整性第3頁完整性約束條件定義完整性約束條件：數據模型組成部分約束數據庫中數據語義DBMS應提供定義數據庫完整性約束條件，并把它們作為模式一部分存入數據庫中10/1/4數據庫系統(tǒng)概論之數據庫完整性第4頁完整性檢驗機制檢驗用戶發(fā)出操作請求是否違反了完整性約束條件

10/1/5數據庫系統(tǒng)概論之數據庫完整性第5頁違約處理假如發(fā)覺用戶操作請求使數據違反了完整性約束條件，則采取一定動作來確保數據完整性。10/1/6數據庫系統(tǒng)概論之數據庫完整性第6頁完整性約束條件完整性約束條件作用對象列：對屬性取值類型、范圍、精度等約束條件元組：對元組中各個屬性列間聯絡約束關系：對若干元組間、關系集合上以及關系之間聯絡約束10/1/7數據庫系統(tǒng)概論之數據庫完整性第7頁完整性約束條件（續(xù)）靜態(tài)對靜態(tài)對象約束是反應數據庫狀態(tài)合理性約束動態(tài)對動態(tài)對象約束是反應數據庫狀態(tài)變遷約束10/1/8數據庫系統(tǒng)概論之數據庫完整性第8頁完整性約束條件分類六類完整性約束條件靜態(tài)列級約束靜態(tài)元組約束靜態(tài)關系約束動態(tài)列級約束動態(tài)元組約束動態(tài)關系約束10/1/9數據庫系統(tǒng)概論之數據庫完整性第9頁完整性約束條件（續(xù)）1.靜態(tài)列級約束靜態(tài)列級約束：正確取值域說明最常見、最簡單、最輕易實現一類完整性約束10/1/10數據庫系統(tǒng)概論之數據庫完整性第10頁完整性約束條件（續(xù)）五類靜態(tài)列級約束1)數據類型約束：數據類型、長度、單位、精度等例：學生姓名數據類型為字符型，長度為82)對數據格式約束 例：學號：前兩位表示入年份，后四位為次序編號日期：YY.MM.DD。10/1/11數據庫系統(tǒng)概論之數據庫完整性第11頁補充：完整性約束條件（續(xù)）3)取值范圍或取值集合約束 例：要求成績取值范圍為0-100年紀取值范圍為14-29性別取值集合為[男,女]4)對空值約束 空值：未定義或未知值空值：與零值和空格不一樣有列允許空值，有則不允許，如成績可為空值5)其它約束 例：關于列排序說明，組合列等10/1/12數據庫系統(tǒng)概論之數據庫完整性第12頁補充：完整性約束條件（續(xù)）2.靜態(tài)元組約束要求元組各個列之間約束關系例：訂貨關系中發(fā)貨量<=訂貨量教師關系中教授工資>=700元

靜態(tài)元組約束只局限在元組上10/1/13數據庫系統(tǒng)概論之數據庫完整性第13頁補充：完整性約束條件（續(xù)）3.靜態(tài)關系約束關系各個元組之間或若干關系之間存在各種聯絡或約束常見靜態(tài)關系約束：1)實體完整性約束2)參考完整性約束3)函數依賴約束4)統(tǒng)計約束10/1/14數據庫系統(tǒng)概論之數據庫完整性第14頁統(tǒng)計約束定義某個字段值一個關系多個元組統(tǒng)計值之間約束關系例：職員平均工資2倍<=部門經理工資<=職員平均工資5倍

10/1/15數據庫系統(tǒng)概論之數據庫完整性第15頁完整性約束條件（續(xù)）4.動態(tài)列級約束動態(tài)列級約束是修改列定義或列值時應滿足約束條件10/1/16數據庫系統(tǒng)概論之數據庫完整性第16頁完整性約束條件（續(xù)） 1)修改列定義時約束 例：將原來允許空值列改為不允許空值時:該列當前已存在空值，則拒絕這種修改2)修改列值時約束修改列值時新舊值之間要滿足約束條件例：職員工資調整>=原來工資年紀只能增加10/1/17數據庫系統(tǒng)概論之數據庫完整性第17頁完整性約束條件（續(xù)）5.動態(tài)元組約束修改元組值:各個字段之間要滿足約束條件例:職員工資調整不得低于其原來工資+工齡*1.510/1/18數據庫系統(tǒng)概論之數據庫完整性第18頁完整性約束條件（續(xù)）6.動態(tài)關系約束關系改變前后狀態(tài)：限制條件例：事務一致性、原子性等約束條件10/1/19數據庫系統(tǒng)概論之數據庫完整性第19頁5.1實體完整性5.1.1實體完整性定義5.1.2實體完整性檢驗和違約處理10/1/20數據庫系統(tǒng)概論之數據庫完整性第20頁5.1.1實體完整性定義實體完整性（EntityIntegrity）若屬性A是基本關系R主屬性，則屬性A不能取空值例SAP(SUPERVISOR，SPECIALITY，POSTGRADUATE)POSTGRADUATE屬性為主碼（假設碩士不會重名），則其不能取空值10/1/21數據庫系統(tǒng)概論之數據庫完整性第21頁5.1.1實體完整性定義關系模型必須恪守實體完整性規(guī)則原因實體完整性規(guī)則是針對基本關系而言。一個基本表通常對應現實世界一個實體集或多對多聯絡。(2)現實世界中實體和實體間聯絡都是可區(qū)分，即它們含有某種唯一性標識。(3)關系模型中以主碼作為唯一性標識。(4)主碼中屬性即主屬性不能取空值?？罩稻褪恰安恢馈被颉盁o意義”值。主屬性取空值，就說明存在某個不可標識實體，即存在不可區(qū)分實體，這與第（2）點相矛盾，所以這個規(guī)則稱為實體完整性。10/1/22數據庫系統(tǒng)概論之數據庫完整性第22頁5.1.1實體完整性定義關系模型實體完整性在Createtable中用PrimaryKey定義。對單屬性組成碼有兩種說明方法：定義為列級約束條件表級約束條件對多個屬性組成碼只有一個說明方法，即定義為表級約束條件10/1/23數據庫系統(tǒng)概論之數據庫完整性第23頁[例題1]將Student表中Sno屬性定義為碼。Createtablestudent(Snochar(9)primarykey,/*在列級定義主碼*/Snamechar(20)notnull,Ssexchar(2),Sagesmallint,Sdeptchar(20));5.1.1實體完整性定義10/1/24數據庫系統(tǒng)概論之數據庫完整性第24頁5.1.1實體完整性定義或者Createtablestudent(snochar(9),Snamechar(20)notnull,Ssexchar(2),Sagesmallint,Sdeptchar(20),primarykey(sno),/*在表級定義主碼*/);10/1/25數據庫系統(tǒng)概論之數據庫完整性第25頁5.1.1實體完整性定義[例題2]將SC表中Sno,Cno屬性組定義為碼CreatetableSC(Snochar(9)notnull,Cnochar(4)notnull,gradesmallint,Primarykey(Sno,Cno),/*只能在表級定義主碼*/)；10/1/26數據庫系統(tǒng)概論之數據庫完整性第26頁5.1.2實體完整性檢驗和違約處理用primarykey短語定義了關系主碼后，每當應用程序對基本表插入一條統(tǒng)計或者對主碼列進行更新操作時，RDBMS將自動檢驗：檢驗主碼值是否唯一，假如不唯一則拒絕插入或修改。檢驗主碼地各個屬性是否為空，只要一個為空就拒絕插入或修改10/1/27數據庫系統(tǒng)概論之數據庫完整性第27頁5.1.2實體完整性檢驗和違約處理檢驗主碼值是否唯一方法（2種）：全表掃描keyif2if3if4if5ikey1f21f31f41f51key2f22f32f42f52key3f23f33f43f53..10/1/28數據庫系統(tǒng)概論之數據庫完整性第28頁5.1.2實體完整性檢驗和違約處理全表掃描缺點：耗時10/1/29數據庫系統(tǒng)概論之數據庫完整性第29頁5.1.2實體完整性檢驗和違約處理第二種方法：建索引（如B+樹索引）25511230667815202537304168697176798493515465新插入統(tǒng)計主碼值10/1/30數據庫系統(tǒng)概論之數據庫完整性第30頁5.1.2實體完整性檢驗和違約處理RDBMS關鍵普通都在主碼上自動建立一個索引經過索引查找基本表中是否存在新主碼值——提升效率10/1/31數據庫系統(tǒng)概論之數據庫完整性第31頁5.2參考完整性5.2.1參考完整性定義5.2.2參考完整性檢驗和違約處10/1/32數據庫系統(tǒng)概論之數據庫完整性第32頁5.2.1參考完整性定義關系模型參考完整性在Createtable中用foreignkey短語定義哪些列為外碼。10/1/33數據庫系統(tǒng)概論之數據庫完整性第33頁5.2.1參考完整性定義[例題3]定義SC中參考完整性。Createtablesc(snochar(9)notnull,cnochar(4)notnull,gradesmallint,Primarykey(sno,cno),/*在表級定義實體完整性*/foreignkey(sno)referencesstudent(sno),/*在表級定義參考完整性*/foreignkey(Cno)referencesstudent(Cno),/*在表級定義參考完整性*/)；10/1/34數據庫系統(tǒng)概論之數據庫完整性第34頁5.2.2參考完整性檢驗和違約處理參考完整性將兩個表中對應元組聯絡起來后。當進行增刪操作時有可能破壞參考完整性，必須進行檢驗。10/1/35數據庫系統(tǒng)概論之數據庫完整性第35頁5.2.2參考完整性檢驗和違約處理對SC和Students有4種可能破壞參考完整性情況：SC表中增加一個元組，該元組Sno屬性值在Students中找不到一個元組，其Sno屬性值與之相等。修改SC表中一個元組，修改后該元組Sno屬性在Students中找不到一個元組，其Sno屬性值與之相等。從Students表中刪除一個元組，造成SC表中一些元組Sno屬性值在Students中找不到一個元組，其Sno屬性值與之相等。修改Students表中一個元組Sno屬性，造成SC表中一些元組Sno屬性值在Students中找不到一個元組，其Sno屬性值與之相等10/1/36數據庫系統(tǒng)概論之數據庫完整性第36頁表5.1可能破壞參考完整性情況及違約處理被參考表（Students）參考表（比如SC）違約處理可能破壞參考完整性插入元組拒絕可能破壞參考完整性修改外碼值拒絕刪除元組可能破壞參考完整性拒絕/級聯刪除/設置為空修改主碼值可能破壞參考完整性拒絕/級聯修改除/設置為空10/1/37數據庫系統(tǒng)概論之數據庫完整性第37頁處理策略1、拒絕（Noaction）執(zhí)行不允許該操作執(zhí)行。該策略普通為默認策略。2、級聯（Cascade）操作當刪除或修改被參考表(Students)一個元組造成了與參考表（SC）不一致，則刪除或修改參考表中全部造成不一致元組。比如，刪除Students表中元組，Sno值為15121則要從SC表中級聯刪除SC.Sno=‘15121’全部元組。3、設置空值（Set-Null） 比如：學生（學號，姓名，性別，專業(yè)號，年紀）專業(yè)（專業(yè)號，專業(yè)名）專業(yè)號能夠取空值但在學生-選課數據庫中，Students關系為被參考關系，其主碼為Sno，SC為參考關系，Sno為外碼。若SCSno為空值，則表明。。。。10/1/38數據庫系統(tǒng)概論之數據庫完整性第38頁處理策略若違反了參考完整性，系統(tǒng)選取默認策略，即拒絕執(zhí)行。假如想讓系統(tǒng)采取其它策略則必須在創(chuàng)建表時候顯式地加以說明。10/1/39數據庫系統(tǒng)概論之數據庫完整性第39頁[例題4]顯式說明參考完整性違約處理CreatetableSC(snochar(9)notnull,cnochar(4)notnull,gradesmallint,Primarykey(sno,cno),/*在表級定義實體完整性*/foreignkey(sno)referencesstudent(sno),/*在表級定義參考完整性*/

ondeletecascade/*當刪除student表中元組時，級聯刪除SC表中對應元組*/onupdatecascade,/*當更新student表中Sno時，級聯更新SC表中對應元組*/foreignkey(sno)referencescourses(sno),,/*在表級定義參考完整性*/

ondeletenoAction/*當刪除courses表中元組時造成了與SC表不一致拒絕刪除*/onupdatecascade

)；10/1/40數據庫系統(tǒng)概論之數據庫完整性第40頁5.3用戶定義完整性5.3.1屬性上約束條件定義5.3.2屬性上約束條件檢驗和違約處理5.3.3元組上約束條件定義5.3.4元組上約束條件檢驗和違約處理10/1/41數據庫系統(tǒng)概論之數據庫完整性第41頁5.3.1屬性上約束條件定義屬性值限制，包含：列值非空（Notnull短語）列值唯一（unique短語）檢驗列值是否滿足一個布爾表示式（Check短語）10/1/42數據庫系統(tǒng)概論之數據庫完整性第42頁5.3.1屬性上約束條件定義1、不允許取空值[例題5]在定義SC表時，說明Sno,Cno,Grade屬性不允許取空值。CreatetableSC(Snochar(9)notnull,Cnochar(40)notnull,Gradesmallintnotnull,Primarykey(Sno,Cno),)10/1/43數據庫系統(tǒng)概論之數據庫完整性第43頁5.3.1屬性上約束條件定義2、列值唯一[例題6]建立部門表Dept,要求部門名稱Dname列取值唯一，部門編號Deptno列為主碼。Createtabledept(Deptnonumeric(2)，Dnamechar(9)nuique,Locationchar(10),Primarykey(Deptno));10/1/44數據庫系統(tǒng)概論之數據庫完整性第44頁5.3.1屬性上約束條件定義3、用Check短語指定列值應該滿足條件[例題7]Student表Ssex只允許取“男”或“女”。CreatetableStudents(Snochar(9)primarykey,Snomechar(8)notnull,Ssexchar(2)check(SsexIN(‘男’，’女’))，Sagesmallint,SdeptChar(20))10/1/45數據庫系統(tǒng)概論之數據庫完整性第45頁5.3.1屬性上約束條件定義[例題8]SC表Grade值應該在0和100之間。CreatetableSC(Snochar(9)notnull,Cnochar(4)notnull,GradesmallintCheck(Grade>=0andGrade<=100),primarykey(Sno,Cno),Foreignkey(Sno)referencesStudent(Sno),Foreignkey(Cno)referencesCourse(Cno));10/1/46數據庫系統(tǒng)概論之數據庫完整性第46頁5.3用戶定義完整性5.3.1屬性上約束條件定義5.3.2屬性上約束條件檢驗和違約處理5.3.3元組上約束條件定義5.3.4元組上約束條件檢驗和違約處理10/1/47數據庫系統(tǒng)概論之數據庫完整性第47頁5.3.2屬性上約束條件檢驗和違約處理當往表中插入元組或修改屬性值時，RDBMS就檢驗屬性上約束條件是否被滿足，不然拒絕執(zhí)行。10/1/48數據庫系統(tǒng)概論之數據庫完整性第48頁5.3用戶定義完整性5.3.1屬性上約束條件定義5.3.2屬性上約束條件檢驗和違約處5.3.3元組上約束條件定義5.3.4元組上約束條件檢驗和違約處10/1/49數據庫系統(tǒng)概論之數據庫完整性第49頁5.3.3元組上約束條件定義與屬性上約束條件定義類似，在Create語句中能夠用Check短語定義元組上約束條件，即元組級限制。 [例題9]當學生性別是男時，其名字不能以Ms.打頭。 CreatetableStudent (Snochar(9),Snamechar(8)notnull,Ssexchar(2),Sagesmallint,Sdeptchar(20),Primarykey(Sno),check(Sex=‘女’orSnamenotlike‘Ms.%’));10/1/50數據庫系統(tǒng)概論之數據庫完整性第50頁5.3用戶定義完整性5.3.1屬性上約束條件定義5.3.2屬性上約束條件檢驗和違約處5.3.3元組上約束條件定義5.3.4元組上約束條件檢驗和違約處10/1/51數據庫系統(tǒng)概論之數據庫完整性第51頁5.3.4元組上約束條件檢驗和違約處理當往表中插入元組或修改屬性值時，RDBMS就檢驗元組上約束條件是否被滿足，不然拒絕執(zhí)行。10/1/52數據庫系統(tǒng)概論之數據庫完整性第52頁5.4完整性約束命名子句1、完整性約束命令子句語法：Constraint<完整性約束條件>[primarykey短語|foreignkey短語|check短語]10/1/53數據庫系統(tǒng)概論之數據庫完整性第53頁5.4完整性約束命名子句[例題10]建立學生記錄表Students，要求學號在90000~99999之間，姓名不能取空值，年紀小于30，性別只能是“男”或“女”。CreatetableStudents(Snonumeric(6)constraintC1check(Snobetween90000and99999),Snamechar(20)constraintC2notnull,Sagenumeric(3)constraintC3check(Sage<30),Ssexchar(2)constraintC4check(SsexIn(‘男’，‘女’))，constraintStudentKeyprimarykey(Sno));10/1/54數據庫系統(tǒng)概論之數據庫完整性第54頁5.4完整性約束命名子句[例題11]建立教師表teacher,要求每個老師應發(fā)工資不低于3000元。（應發(fā)工資實際上就是實發(fā)工資列Sal與扣除項Deduct之和）Createtableteacher(Enonumeric(4)primarykey,Enamechar(10),Jobchar(8),Salnumeric(7,2),Deductnumeric(2),Contraintempkeyforeignkey(Deptno)referencesdept(deptno),ContrantC1check(Sal+Deduct>=3000));10/1/55數據庫系統(tǒng)概論之數據庫完整性第55頁5.4完整性約束命名子句2.修改表中完整性限制使用Altertable語句[例題12]去掉[例題10]Students表中對性別限制。AltertablestudentDropconstraintC410/1/56數據庫系統(tǒng)概論之數據庫完整性第56頁5.4完整性約束命名子句[例題13]修改表Students表中約束條件，要求學號改為800000~999999之間，年紀小于30改為45。先刪除原來約束條件，再增加新約束條件。AltertablestudentsDropconstraintC1;AltertablestudentsAddconstraintC1check(Snobetween800000and999999),AltertablestudentsDropconstraintC3;AltertablestudentsAddconstraintC3check(Sage<45);10/1/57數據庫系統(tǒng)概論之數據庫完整性第57頁5.5觸發(fā)器5.5.1定義觸發(fā)器5.5.2激活觸發(fā)器5.5.3刪除觸發(fā)器10/1/58數據庫系統(tǒng)概論之數據庫完整性第58頁5.5.1定義觸發(fā)器1、觸發(fā)器（Trigger）:定義在關系表上一類由事件驅動特殊過程。2、格式Createtrigger<觸發(fā)器名>{Before|After}<觸發(fā)事件>on<表名>Foreach{Row|Statement}[When<觸發(fā)條件>]<觸發(fā)動作體>10/1/59數據庫系統(tǒng)概論之數據庫完整性第59頁

5.5.1定義觸發(fā)器舉例[例題18]定義一個Before行級觸發(fā)器，為教師表Teacher定義完整性規(guī)則“教授工資不得低于4000元，假如低于4000元，自動改為4000元”。CreatetriggerInser_or_SalBeforeinsertorupdateonteacherForeachrowAsbeginIf(new.pJob=‘教授’)and(new.Sal<4000)Thennew.sal:=4000;EndIf;End；10/1/60數據庫系統(tǒng)概論之數據庫完整性第60頁

5.5.1定義觸發(fā)器[例題19]定義一個After行級觸發(fā)器，當教師表Teacher工資發(fā)生改變后就自動在工資改變表Sal_log中增加一條對應紀錄。CreatetableSal_log(Enonumric(4)ref