數(shù)據(jù)完整性實(shí)現(xiàn)技術(shù)（DDL）

1、4.2 數(shù)據(jù)完整性實(shí)現(xiàn)技術(shù)（DDL）信息科學(xué)與工程學(xué)院楊金民2020.10數(shù)據(jù)完整性的四大保障措施：主鍵約束；外鍵約束；域約束；業(yè)務(wù)規(guī)則約束；數(shù)據(jù)完整性問(wèn)題DDL介紹表模式（Schema）的定義中，包含了主鍵，外鍵，域約束的定義；業(yè)務(wù)規(guī)則約束用觸發(fā)器（Trigger）來(lái)定義;SQL 標(biāo)識(shí)符（Identifiers）數(shù)據(jù)庫(kù)中的對(duì)象，有如下類(lèi)別：表，列，視圖，存儲(chǔ)過(guò)程，函數(shù)，觸發(fā)器，用戶，角色，索引，用戶自定義數(shù)據(jù)類(lèi)型。例如，在數(shù)據(jù)庫(kù)中創(chuàng)建一個(gè)表student，準(zhǔn)確的說(shuō)法是創(chuàng)建表類(lèi)型的一個(gè)實(shí)例對(duì)象。該實(shí)例對(duì)象的名稱(chēng)字段的取值為student；這樣一來(lái)，表的模式(Schema), 在DBMS內(nèi)部也是

2、使用表來(lái)存儲(chǔ)，設(shè)這個(gè)表為sys_table表。用戶每創(chuàng)建一個(gè)表，其實(shí)就是往sys_table中添加一行數(shù)據(jù)；對(duì)于某個(gè)類(lèi)別，在數(shù)據(jù)庫(kù)中，其實(shí)例對(duì)象都是用名稱(chēng)來(lái)標(biāo)識(shí)。名稱(chēng)命名規(guī)則：以字母開(kāi)頭，只含字母，數(shù)字，下劃線，長(zhǎng)度不超過(guò)128；SQL基本數(shù)據(jù)類(lèi)型Data Type Description BOOLEAN 布爾類(lèi)型 TRUE/FALSECHAR 固定長(zhǎng)度的字符串，如 CHAR(10)VARCHAR 變長(zhǎng)字符串，如 VARCHAR(50)NUMERIC or DECIMAL 數(shù)值型 NUMERIC(7,2)INTEGER IntegerSMALLINT 小整型 FLOAT or REAL 浮點(diǎn)數(shù)

3、. DATE 日類(lèi)型 TIME 時(shí)間類(lèi)型 TIMESTAMP 時(shí)間戳CHARACTER LARGE OBJECT SQL3 文本大對(duì)象BINARY LARGE OBJECT SQL3 二進(jìn)制大對(duì)象表模式定義語(yǔ)句例子CREATE TABLE Emp (eno CHAR(5),ename VARCHAR(30) NOT NULL,birthday DATE,title CHAR(2) CHECK (title IN (NULL,EE,SA,PR,ME),salary DECIMAL(9,2),superno CHAR(5),dno CHAR(5)PRIMARY KEY (eno)FOREIGN K

4、EY (dno) REFERENCES Dept(dno)ON DELETE SET NULL ON UPDATE CASCADE FOREIGN KEY (superno) REFERENCESEmp(eno);引用完整性約束所指的一個(gè)方面StudentNamestudentNosexbirthdateMike2008043101male1990/12/14Tom2008043214female1992/02/21Mary2008043332female1988/07/09EnrollstudentNocourseNosemestergrade20080433324308032010/019

5、020080431014303172009/015620080431014303172008/025620080433324300072004/027720080433324306012000/018720080431014303172002/0256在Student表中的某一行記錄：1）其主關(guān)鍵字studentNo被修改；2）被刪除；3）被插入；在Enroll表中對(duì)應(yīng)的記錄怎么辦？引用完整性約束StudentNamestudentNosexbirthdateMike2008043101male1990/12/14Tom2008043214female1992/02/21Mary2008043

6、332female1988/07/09EnrollstudentNocourseNosemestergrade20080433324308032010/019020080431014303172009/015620080431014303172008/025620080433324300072004/027720080433324306012000/018720080431014303172002/0256在Student表中的某一行記錄：1）修改的其主關(guān)鍵字studentNo；2）刪除某一行記錄；3）插入一行新記錄；1) CASCADE2) SET NULL3) NO ACTION引用完整性

7、約束所指的另一個(gè)方面StudentNamestudentNosexbirthdateMike2008043101male1990/12/14Tom2008043214female1992/02/21Mary2008043332female1988/07/09EnrollstudentNocourseNosemestergrade20080433324308032010/019020080431014303172009/015620080431014303172008/025620080433324300072004/027720080433324306012000/01872008043101

8、4303172002/0256在Enroll表中:添加一行記錄；某行記錄的外建字段studentNo的值被修改enroll表中行記錄的外建字段studentNo的值在Student中要存在；表模式創(chuàng)建例子CREATE TABLE Enroll (sno CHAR(12) NOT NULL,cno CHAR(8) NOT NULL,semester CHAR(7), tno CHAR(7),classNo CHAR（2）；grade SMALLINTPRIMARY KEY (sno, cno, semester)FOREIGN KEY (sno) REFERENCES student(sno)

9、ON DELETE CASCADE ON UPDATE CASCADE,FOREIGN KEY (cno) REFERENCES course(cno) ON DELETE NO ACTION ON UPDATE CASCADE FOREIGN KEY (tno) REFERENCES teacher(to) ON DELETE SET NULL ON UPDATE CASCADE );課堂練習(xí)CourseNamecourseNocredithourstextbooksyllabusdtuy_tnodnodatabaseA240608464aintroduction200421324opera

10、ting system430803464ball200100724java430317348csectionnull24寫(xiě)出Course表的模式定義語(yǔ)句 用戶對(duì)某個(gè)表執(zhí)行行更新元組約束 1）域約束：當(dāng)前數(shù)據(jù)行，每個(gè)字段取值受其域約束：data type，取值范圍，NOT NULL，UNIQUE等；2）業(yè)務(wù)規(guī)則約束當(dāng)前行數(shù)據(jù)，不同字段的取值彼此之間存在關(guān)聯(lián)約束；當(dāng)前行數(shù)據(jù)，它受該表中已有行的約束；當(dāng)前行數(shù)據(jù)，它受其它表中已有行的約束；業(yè)務(wù)規(guī)則約束的表達(dá)1）當(dāng)前行數(shù)據(jù)，每個(gè)字段取值受其域約束：data type，domain，NOT NULL 等；2）當(dāng)前行數(shù)據(jù)，不同字段的取值彼此之間存在關(guān)聯(lián)約束

11、；例如：CREATE TABLE student (studentNo CHAR(10) NOT NULL,honors CHAR(1),grade DECIMAL(4,1)CHECK ( (honors = Y AND grade 85) OR honors = N);3）當(dāng)前行數(shù)據(jù)，它受該表中已有行的約束；4）當(dāng)前行數(shù)據(jù)，它受其它表中已有行的約束；使用觸發(fā)器實(shí)現(xiàn)比較后兩類(lèi)業(yè)務(wù)規(guī)則約束（相對(duì)復(fù)雜）觸發(fā)器表：TableNameTableTypeOperationTypeFieldNameTriggerTypeTriggerNameTriggerCodeenrolltableINSERTnull

12、BEFOREenroll_insert.teachertableUPDATEsalaryAFTERsalary_update.staffviewINSERTnullINSTEAD OFtransfer_insertstaffviewDELETEnullINSTEAD OFtransfer_delete理解業(yè)務(wù)規(guī)則約束的觸發(fā)器實(shí)現(xiàn)機(jī)制Thread：while （1） waitForClientRequest（Request); try 解析請(qǐng)求：操作類(lèi)型，表，字段；對(duì)更新操作，查觸發(fā)器表： if (有BEFORE觸發(fā)器) 分別調(diào)用它們； if (有INSTEAD OF觸發(fā)器) 分別調(diào)用它們 el

13、se 執(zhí)行請(qǐng)求操作； if (有AFTER觸發(fā)器) 分別調(diào)用它們； catch exception(e) . 使用觸發(fā)器實(shí)現(xiàn)每學(xué)期選課不超過(guò)25學(xué)分CREATE TRIGGER trigger_insertEnrollBEFORE INSERT ON enrollREFERENCING NEW ROW AS newFOR EACH ROWBEGINcredit integerSELECT SUM(credit) INTO credit FROM course WHERE courseId IN (SELECT courseId FROM enroll WHERE studentId = new

14、.studentId AND semester =new.semester) OR courseId = new.courseId;WHEN ( credit 25 ) raise_application_error(20000, 這個(gè)學(xué)期已經(jīng)選修學(xué)分 + credit);END;實(shí)現(xiàn)每個(gè)房間的預(yù)訂不重疊沖突CREATE TRIGGER trigger_insertBookingBEFORE INSERT ON bookingREFERENCING NEW ROW AS newFOR EACH ROWBEGINWHEN( EXIST SELECT hotelNo, roomNo FROM bo

15、oking WHERE hotelNo = newrow.hotelNo AND roomNo =new.roomNo AND dateTo = new.dateFrom AND dateFrom old.salary*1.1) INSERT INTO auditEmpVALUES (new.eno, date(now(), new.salary, old.salary);事件條件動(dòng)作隨堂測(cè)試基于觸發(fā)器放入業(yè)務(wù)規(guī)則定義1）對(duì)老師表，rank的取值有：教授，副教授，助理教授。要求：職稱(chēng)級(jí)別低的工資不能高于職稱(chēng)級(jí)別高的老師的工資；2) 當(dāng)員工表中的某個(gè)員工被刪除時(shí)，該員工在workOn表中相應(yīng)的記

16、錄也要求一同刪除；刪除或者修改中的一個(gè)對(duì)象DROP TABLE emp;ALTER TABLE emp .;DROP VIEW staff;ALTER VIEW staff .;DROP PROCEDURE add_enroll;ALTER PROCEDURE add_enroll;DROP TRIGGER staff_insert;ALTER TRIGGER staff_insert;關(guān)系數(shù)據(jù)庫(kù)模型的知識(shí)圖譜審計(jì)蹤跡記錄權(quán)限管理對(duì)象權(quán)限用戶授權(quán)收權(quán)觸發(fā)器業(yè)務(wù)規(guī)則約束完整性約束主鍵約束域約束外鍵約束SQL表達(dá)應(yīng)用程序屏蔽編程可視化數(shù)據(jù)存儲(chǔ)過(guò)程個(gè)性化數(shù)據(jù)屏蔽SQL視圖屏蔽聯(lián)接業(yè)務(wù)數(shù)據(jù)數(shù)據(jù)庫(kù)中的對(duì)

17、象應(yīng)用程序(視圖和存儲(chǔ)過(guò)程)存儲(chǔ)過(guò)程視圖表觸發(fā)器(完整性約束）用戶(權(quán)限管理）索引數(shù)據(jù)庫(kù)管理系統(tǒng)（DBMS)SQL DDL創(chuàng)建 CREATE；刪除 DROP；修改ALTER；數(shù)據(jù)庫(kù)設(shè)計(jì)人員SQL DMLSELECT；DELETE；UPDATE；INSERT；Statistics；CALL；數(shù)據(jù)庫(kù)使用人員什么時(shí)候定義成視圖？什么時(shí)候定義成存儲(chǔ)過(guò)程？階段考試題希爾頓是一跨國(guó)旅館經(jīng)營(yíng)公司，其數(shù)據(jù)庫(kù)系統(tǒng)中有如下4個(gè)表：Hotel (hotelNo, name, city)Room (roomNo, hotelNo, type, price)Booking (hotelNo, guestId, dateFrom, dateTo, roomNo)