數(shù)據(jù)庫(kù)完整性

1、大連海事大學(xué)數(shù)據(jù)庫(kù)原理課程實(shí)驗(yàn)大綱實(shí)驗(yàn)名稱(chēng)： 實(shí)驗(yàn)七 完整性 實(shí)驗(yàn)學(xué)時(shí)： 2 適用專(zhuān)業(yè)： 實(shí)驗(yàn)環(huán)境： 執(zhí) 筆 者： 編寫(xiě)日期： 1 實(shí)驗(yàn)?zāi)康模?） 掌握實(shí)體完整性、參照完整性和用戶(hù)自定義完整性約束的創(chuàng)建方法。（2） 掌握完整性約束的運(yùn)行檢查機(jī)制。（3） 掌握參照完整性的級(jí)聯(lián)刪除和修改方法。（4） 掌握正確設(shè)計(jì)關(guān)系模式完整性約束的方法。2 實(shí)驗(yàn)內(nèi)容2.1 掌握實(shí)體完整性約束的創(chuàng)建和使用方法（1） 創(chuàng)建表時(shí)定義由一個(gè)屬性組成的主鍵（給約束命名）。（2） 創(chuàng)建表時(shí)定義由兩個(gè)或兩個(gè)以上屬性組成的主鍵（給約束命名）。（3） 刪除以上兩個(gè)主鍵約束。（4） 利用ALTER TABLE語(yǔ)句定義上述兩個(gè)主鍵。2

2、.2 掌握參照完整性約束的創(chuàng)建和使用方法（5） 創(chuàng)建表時(shí)定義一個(gè)列級(jí)參照完整性約束（給約束命名）。（6） 創(chuàng)建表時(shí)定義一個(gè)表級(jí)的由兩個(gè)屬性組成的參照完整性約束（給約束命名）。（7） 設(shè)計(jì)數(shù)據(jù)更新語(yǔ)句檢查參照完整性約束是否起作用。（8） 刪除上述完整性約束。（9） 利用ALTER TABLE 建立上述參照完整性約束，并且規(guī)定UPDATE/DELETE時(shí)的動(dòng)作。（10） 設(shè)計(jì)數(shù)據(jù)更新語(yǔ)句檢查參照完整性約束及其相應(yīng)的動(dòng)作是否起作用。2.3 掌握用戶(hù)自定完整性約束的創(chuàng)建和使用方法（11） 定義一個(gè)檢查約束，檢查其值在某個(gè)取值范圍內(nèi)，并設(shè)計(jì)相應(yīng)的更新語(yǔ)句檢查該約束是否起作用？（12） 定義一個(gè)檢查其值符

3、合某個(gè)匹配模式的檢查約束（使用LIKE），并設(shè)計(jì)相應(yīng)的更新語(yǔ)句檢查該約束是否起作用？（13） 定義一個(gè)檢查其值符合某個(gè)正則表達(dá)式的檢查約束（使用SIMILAR TO），并設(shè)計(jì)相應(yīng)的更新語(yǔ)句檢查該約束是否起作用？（14） 定義一個(gè)UNIQUE約束，并設(shè)計(jì)相應(yīng)的更新語(yǔ)句檢查該約束是否起作用？（15） 定義一個(gè)DEFAULT約束，設(shè)計(jì)一個(gè)INSERT語(yǔ)句檢查該約束是否起作用。3 實(shí)驗(yàn)要求（1） 深入復(fù)習(xí)教材第五章數(shù)據(jù)庫(kù)完整性約束內(nèi)容。（2） 根據(jù)書(shū)上的例子，針對(duì)TPCH數(shù)據(jù)庫(kù)模式設(shè)計(jì)各種完整性約束，每種類(lèi)型完整性約束至少要設(shè)計(jì)一個(gè)，描述清楚完整性約束要求，設(shè)計(jì)和運(yùn)行觸發(fā)完整性約束檢查的數(shù)據(jù)更新語(yǔ)句，

4、并截圖相應(yīng)的實(shí)驗(yàn)結(jié)果，每幅截圖并要有較為詳細(xì)的描述。也可以按照附1所列示例做實(shí)驗(yàn)。（3） 實(shí)驗(yàn)步驟和實(shí)驗(yàn)總結(jié)中要詳細(xì)描述實(shí)驗(yàn)過(guò)程中出現(xiàn)的問(wèn)題、原因和解決方法。（4） 思考題：完整性約束的違約處理有哪幾種方式？4 實(shí)驗(yàn)步驟4.1 掌握實(shí)體完整性約束的創(chuàng)建和使用方法（1） 創(chuàng)建表時(shí)定義由一個(gè)屬性組成的主鍵（給約束命名）。創(chuàng)建零件表part時(shí)定義主鍵。/CREATE TABLE PART(PARTKEY INTEGER,MFGR CHAR(100),TYPE CHAR(25),SIZE INTEGER,NAME CHAR(55),CONTAINER CHAR(10),BRAND CHAR(10),R

5、ETAILPRICE REAL,COMMENT VARCHAR(23),CONSTRAINT PARTY PRIMARY KEY(PARTKEY)如圖：partkey為主鍵（2） 創(chuàng)建表時(shí)定義由兩個(gè)或兩個(gè)以上屬性組成的主鍵（給約束命名）。創(chuàng)建零件供應(yīng)關(guān)系表partsupp時(shí)定義主鍵。CREATE TABLE PARTSUPP(PARTKEY INTEGER,AVAILQTY INTEGER,SUPPKEY INTEGER,COMMENT VARCHAR(199),SUPPLYCOST REAL,CONSTRAINT PARTSUPPY PRIMARY KEY(PARTKEY,SUPPKEY);

6、如圖給點(diǎn)約束名PARTSUPPY.PARTKEY,和SUPPKEY為主鍵（3） 刪除以上兩個(gè)主鍵約束。刪除零件表和零件供應(yīng)關(guān)系表的主鍵。ALTER TABLE PARTSUPPDROP CONSTRAINT PARTSUPPY;利用約束名SUPPKEY刪除約束ALTER TABLE PARTDROP CONSTRAINT PARTY利用約束名PARTY刪除約束（4） 利用ALTER TABLE語(yǔ)句定義上述兩個(gè)主鍵。利用ALTER TABLE增加part和partsupp表的主鍵。ALTER TABLE PARTADD CONSTRAINT PARTY PRIMARY KEY (PARTKEY)

7、;利用ALTER給表PART增加約束PARTYALTER TABLE PARTSUPPADD CONSTRAINT PARTSUPPY PRIMARY KEY(PARTKEY,SUPPKEY)利用ALTER給表增加約束PARTSUPPY主鍵PARTKEY,SUPPKEY4.2 掌握參照完整性約束的創(chuàng)建和使用方法（5） 創(chuàng)建表時(shí)定義一個(gè)列級(jí)參照完整性約束（給約束命名）。定義partsupp表的列級(jí)參照完整性約束。CREATE TABLE PARTSUPP(PARTKEY INTEGER,AVAILQTY INTEGER,SUPPKEY INTEGER,COMMENT VARCHAR(199),S

8、UPPLYCOST REAL NOT NULL,CONSTRAINT PARTSUPPY PRIMARY KEY(PARTKEY,SUPPKEY);（6） 創(chuàng)建表時(shí)定義一個(gè)表級(jí)的由兩個(gè)屬性組成的參照完整性約束（給約束命名）。定義lineitem的參照完整性約束。 CREATE TABLE lineitem (orderkey INTEGER,partkey INTEGER,suppkey INTEGER,linenumber INTEGER,quantity REAL,extendedprice REAL,discount REAL,tax REAL,returnflag CHAR(1),li

9、nestatus CHAR(1),shipDATE DATE,commitDATE DATE,receiptDATE DATE,shipinstruct CHAR(25),shipmode CHAR(10),comment VARCHAR(44)CONSTRAINT LINETY PRIMARY KEY(ORDERKEY,PARTKEY,SUPPKEY),CONSTRAINT FPART FOREIGN KEY(PARTKEY) REFERENCES PART,CONSTRAINT FPARTSUPP FOREIGN KEY(SUPPKEY,PARTKEY) REFERENCES PARTSU

10、PP);創(chuàng)建表時(shí)創(chuàng)建外碼FPART和FPARTSUPP分別為PART和PARTSUPP表的主碼（7） 設(shè)計(jì)數(shù)據(jù)更新語(yǔ)句檢查參照完整性約束是否起作用。設(shè)計(jì)INSERT、DELETE和UPDATE語(yǔ)句檢查上述完整性約束是否有效。INSERT INTO lineitem (orderkey,partkey,suppkey)VALUES(4,2,4)INSERT INTO PART(PARTKEY,SIZE,RETAILPRICE)VALUES(1,34,2)（8） 刪除上述完整性約束。刪除partsupp和lineitem的參照完整性約束。ALTER TABLE LINEITEM DROP CONS

11、TRAINT FPARTALTER TABLE LINEITEMDROP CONSTRAINT FPARTSUPPALTER TABLE PARTSUPPDROP CONSTRAINT FPART（9） 利用ALTER TABLE 建立上述參照完整性約束，并且規(guī)定UPDATE/DELETE時(shí)的動(dòng)作。利用ALTER TABLE 建立上述兩個(gè)完整性約束，并規(guī)定UPDATE和DELETE級(jí)聯(lián)修改和刪除。ALTER TABLE PARTSUPP ADD CONSTRAINT FPART FOREIGN KEY(PARTKEY) REFERENCES PART ON DELETE CASCADE ON

12、UPDATE NO ACTIONALTER TABLE LINEITEMADD CONSTRAINT FPS1 FOREIGN KEY(PARTKEY) REFERENCES PART ON DELETE CASCADE ON UPDATE NO ACTIONALTER TABLE LINEITEM ONADD CONSTRAINT FPS2 FOREIGN KEY(SUPPKEY,PARTKEY) REFERENCES PARTSUPP ON DELETE NO ACTION ON UPDATE NO ACTION（10） 設(shè)計(jì)數(shù)據(jù)更新語(yǔ)句檢查參照完整性約束及其相應(yīng)的動(dòng)作是否起作用。刪除零件

13、供應(yīng)記錄partsupp 或者是 lineitem表中的記錄，檢查約束是否起作用？刪除主鍵時(shí)因?yàn)橹鞔a約束無(wú)法刪除4.3 掌握用戶(hù)自定完整性約束的創(chuàng)建和使用方法（11） 定義一個(gè)檢查約束，檢查其值在某個(gè)取值范圍內(nèi)，并設(shè)計(jì)相應(yīng)的更新語(yǔ)句檢查該約束是否起作用？定義一個(gè)檢查L(zhǎng)ineitem中的折扣只能在0和1之間的CHECK約束，并設(shè)計(jì)INSERT語(yǔ)句檢查約束是否起作用。ALTER TABLE lineitem ADD CONSTRAINT CHE1 CHECK (DISCOUNT between 0 and 1 )（12） 定義一個(gè)檢查其值符合某個(gè)匹配模式的檢查約束（使用LIKE），并設(shè)計(jì)相應(yīng)的更新

14、語(yǔ)句檢查該約束是否起作用？定義一個(gè)CHECK約束，檢查國(guó)家表nation中的國(guó)家名稱(chēng)的最后一個(gè)字必須為“國(guó)”字。設(shè)計(jì)一個(gè)INSERT語(yǔ)句檢查該約束是否有效。alter table nationadd constraint namek check(name like%國(guó));（13） 定義一個(gè)檢查其值符合某個(gè)正則表達(dá)式的檢查約束（使用SIMILAR TO），并設(shè)計(jì)相應(yīng)的更新語(yǔ)句檢查該約束是否起作用？定義一個(gè)CHECK約束，檢查CUSTOMER表中的PHONE必須由12位數(shù)字組成。并設(shè)計(jì)一個(gè)INSERT語(yǔ)句或者是UPDATE語(yǔ)句檢查該約束是否有效。alter table customeradd co

15、nstraint CHE3 check( PHONE SIMILAR TO(_);不為12位語(yǔ)句已終止（14） 定義一個(gè)UNIQUE約束，并設(shè)計(jì)相應(yīng)的更新語(yǔ)句檢查該約束是否起作用？在NATION表的NAME字段上定義一個(gè)UNIQUE約束，不允許國(guó)家名重復(fù)。設(shè)計(jì)一個(gè)INSERT語(yǔ)句檢查約束是否有效。alter table nationadd constraint CHE4 UNIQUE(NAME);（15） 定義一個(gè)DEFAULT約束，設(shè)計(jì)一個(gè)INSERT語(yǔ)句檢查該約束是否起作用。在SUPPLIER表中的NATIONKEY字段上定義一個(gè)DEFAULT約束，默認(rèn)取值為中國(guó)的國(guó)家代碼。并設(shè)計(jì)一個(gè)IN

16、SERT語(yǔ)句檢查該約束是否有效。ALTER TABLE SUPPLIERADD CONSTRAINT CHE5 DEFAULT 1 FOR NATIONKEYINSERT INTO SUPPLIER(SUPPKEY,NAME)VALUES(1,張鋒)默認(rèn)為1，如圖示思考題：沖突處理有四種解決方式：no action,restrict,set null,cascadeNo action指在違約時(shí)直接回滾不作任何處理，restrict在違約時(shí)拒絕執(zhí)行命令，set 在違約時(shí)把違約項(xiàng)設(shè)為空值，cascade當(dāng)當(dāng)刪除或修改參照關(guān)系中某元組的主碼值時(shí)，被參照關(guān)系中，那些外部碼具有該值的元組也將被刪除或修改，以保證參照完整性。5 總結(jié)與體