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

1、數(shù)據(jù)控制-完整性控制 實 驗 報 告班級 07計算機 姓名 馬兆亮 日期 2010-05-07 同組人 （無） 指導教師 金華 評分 數(shù)據(jù)控制-完整性控制部分實驗一 、實驗目的熟悉通過SQL對數(shù)據(jù)進行完整性控制二 、實驗設備操作系統(tǒng)為Microsoft Windows 2000數(shù)據(jù)庫管理系統(tǒng)為Microsoft SQL Server 2000個人版或標準版微型計算機：奔騰，每人一臺三、實驗步驟建立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,庫克,女,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ù)庫,1003,5,005);INSERT INTO Course VALUES(002,操作系統(tǒng),1001,5,null);INSERT INTO Course VALUES(003

4、,網(wǎng)絡編程,1002,4,001);INSERT INTO Course VALUES(004,微機原理,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個表的主碼建立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.設SC表的成績屬性不允許取空值 ALTER TABLE Sc ALTER COLUMN cgrade smallint NOT NULL;驗證：當插入語句為：INSERT INTO Sc VALUES(1078,305,null);4.建立部門表（DEPT），屬性包括部門編號、部門名稱、地址，部門名稱要求取值唯一，部門編號為主碼CREATE

10、TABLE Dept( sdept char(10) PRIMARY KEY, dname char(20) UNIQUE, dadd char(50),);5.為STUDENT表添加約束：性別的值只允許取“男”或“女”，學號范圍為10000-19999，姓名不能取空值ALTER TABLE Student ADD CONSTRAINT S1 CHECK (ssex in(男,女);驗證：當插入記錄：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驗證：當輸入INSERT INTO Sc VALUES(1086,002,703);顯示如下信息10.對COURSE表建立插入-更新觸發(fā)器，學時小于等于32學時時學分不能超過2分，學時小于等于64學時時學分不能超過4分，任何課程的學分最高不能超過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有錯誤,調(diào)試不成功。四實驗中遇到的問題及解決方法。1，在建立參照完整性約束時，執(zhí)行如下語句ALTER TABLE Sc ADD CONSTRAINT FK_sno FOREI