數(shù)據(jù)控制-完整性控制報(bào)告

1、數(shù)據(jù)控制-完整性控制 實(shí) 驗(yàn) 報(bào) 告班級(jí) 07計(jì)算機(jī) 姓名 馬兆亮 日期 2010-05-07 同組人 （無） 指導(dǎo)教師 金華 評(píng)分 數(shù)據(jù)控制-完整性控制部分實(shí)驗(yàn)一 、實(shí)驗(yàn)?zāi)康氖煜ねㄟ^SQL對(duì)數(shù)據(jù)進(jìn)行完整性控制二 、實(shí)驗(yàn)設(shè)備操作系統(tǒng)為Microsoft Windows 2000數(shù)據(jù)庫(kù)管理系統(tǒng)為Microsoft SQL Server 2000個(gè)人版或標(biāo)準(zhǔn)版微型計(jì)算機(jī)：奔騰，每人一臺(tái)三、實(shí)驗(yàn)步驟建立STUDENT、COURSE、SC表，向各表插入若干記錄。CREATE TABLE Student /*創(chuàng)建student表*/(sno char(10), sname char(20) NOT NU

2、LL, ssex char(5), sage SMALLINT, dno char(5);CREATE TABLE Course/*創(chuàng)建course表*/(cno char(10),cname char(20) NOT NULL,tno char(10),ccredit int,cpno char(5);CREATE TABLE Sc/*創(chuàng)建Sc表*/(sno char(10),cno char(10) ,cgrade char(5); /*向Student表中添加記錄*/INSERT INTO Student VALUES(1001,庫(kù)克,女,20,01);INSERT INTO Stude

3、nt VALUES(1002,張小芳,女,21,03);INSERT INTO Student VALUES(1003,李建,男,23,02);INSERT INTO Student VALUES(1004,王雨,男,21,03);INSERT INTO Student VALUES(1005,任娜,女,22,02);/*向Course表中添加記錄*/INSERT INTO Course VALUES(001,數(shù)據(jù)庫(kù),1003,5,005);INSERT INTO Course VALUES(002,操作系統(tǒng),1001,5,null);INSERT INTO Course VALUES(003

4、,網(wǎng)絡(luò)編程,1002,4,001);INSERT INTO Course VALUES(004,微機(jī)原理,1003,6,002);INSERT INTO Course VALUES(005,C 語言,1001,4,null);/*向Sc表中添加記錄*/INSERT INTO Sc VALUES(1001,001,74);INSERT INTO Sc VALUES(1001,002,68);INSERT INTO Sc VALUES(1001,003,79);INSERT INTO Sc VALUES(1001,004,83);INSERT INTO Sc VALUES(1001,005,92)

5、;INSERT INTO Sc VALUES(1002,001,73);INSERT INTO Sc VALUES(1002,002,88);INSERT INTO Sc VALUES(1002,003,73);INSERT INTO Sc VALUES(1002,004,87);INSERT INTO Sc VALUES(1002,005,85);INSERT INTO Sc VALUES(1003,001,80);INSERT INTO Sc VALUES(1003,002,71);INSERT INTO Sc VALUES(1003,003,80);INSERT INTO Sc VALU

6、ES(1003,004,null);INSERT INTO Sc VALUES(1003,005,69);INSERT INTO Sc VALUES(1004,001,86);INSERT INTO Sc VALUES(1004,002,96);INSERT INTO Sc VALUES(1004,003,79);INSERT INTO Sc VALUES(1004,004,83);INSERT INTO Sc VALUES(1004,005,null);INSERT INTO Sc VALUES(1005,001,84);INSERT INTO Sc VALUES(1005,002,69);

7、INSERT INTO Sc VALUES(1005,003,94);INSERT INTO Sc VALUES(1005,004,85);INSERT INTO Sc VALUES(1005,005,77);1.建立3個(gè)表的主碼建立Student的主碼，兩條語句分別執(zhí)行：ALTER TABLE Student ALTER COLUMN sno char(10) NOT NULL;ALTER TABLE Student ADD CONSTRAINT PK_Student PRIMARY KEY(sno);建立Course的主碼，兩條語句分別執(zhí)行：ALTER TABLE Course ALTER

8、 COLUMN cno char(10) NOT NULL;ALTER TABLE Course ADD CONSTRAINT PK_Course PRIMARY KEY(cno);建立Sc表的主碼，三條語句分別執(zhí)行：ALTER TABLE Sc ALTER COLUMN sno char(10) NOT NULL;ALTER TABLE Sc ALTER COLUMN cno char(10) NOT NULL;ALTER TABLE Sc ADD CONSTRAINT PK_Sc PRIMARY KEY(sno,cno);2.為SC表建立參照完整性約束ALTER TABLE Sc ADD

9、CONSTRAINT FK_sno FOREIGN KEY REFERENCES Student(sno);ALTER TABLE Sc ADD CONSTRAINT FK_cno FOREIGN KEY REFERENCES Course(cno);3.設(shè)SC表的成績(jī)屬性不允許取空值 ALTER TABLE Sc ALTER COLUMN cgrade smallint NOT NULL;驗(yàn)證：當(dāng)插入語句為：INSERT INTO Sc VALUES(1078,305,null);4.建立部門表（DEPT），屬性包括部門編號(hào)、部門名稱、地址，部門名稱要求取值唯一，部門編號(hào)為主碼CREATE

10、TABLE Dept( sdept char(10) PRIMARY KEY, dname char(20) UNIQUE, dadd char(50),);5.為STUDENT表添加約束：性別的值只允許取“男”或“女”，學(xué)號(hào)范圍為10000-19999，姓名不能取空值A(chǔ)LTER TABLE Student ADD CONSTRAINT S1 CHECK (ssex in(男,女);驗(yàn)證：當(dāng)插入記錄：INSERT INTO Student VALUES(1024,石一飛,男,23,03);顯示如下信息：ALTER TABLE Student WITH NOCHECK ADD CONSTRAIN

11、T S3 CHECK (sno=10000and sno =0 and cgrade 100) 0BEGINPRINT insert_update error: you insert the grade 100ROLLBACK TRANSACTIONEND驗(yàn)證：當(dāng)輸入INSERT INTO Sc VALUES(1086,002,703);顯示如下信息10.對(duì)COURSE表建立插入-更新觸發(fā)器，學(xué)時(shí)小于等于32學(xué)時(shí)時(shí)學(xué)分不能超過2分，學(xué)時(shí)小于等于64學(xué)時(shí)時(shí)學(xué)分不能超過4分，任何課程的學(xué)分最高不能超過5分CREATE Trigger Insert_Or_Update_SalON CourseFOR

12、 INSERT , UPDATE AS declare thours smallintdeclareccredit intselect (thours=ins.thours and ccredit=ins.ccredit) from inserted ins IF(thours2) if(thours=64 and ccredit5) begin raiserror (警告：非法操作！,16,1) rollback transactionend有錯(cuò)誤,調(diào)試不成功。四實(shí)驗(yàn)中遇到的問題及解決方法。1，在建立參照完整性約束時(shí)，執(zhí)行如下語句ALTER TABLE Sc ADD CONSTRAINT FK_sno FOREI