数据控制-完整性控制 实 验 报 告班级 07计算机 姓名 马兆亮 日期 2010-05-07 同组人 （无） 指导教师 金华 评分 数据控制-完整性控制部分实验一 、实验目的熟悉通过SQL对数据进行完整性控制二 、实验设备操作系统为Microsoft Windows 2000数据库管理系统为Microsoft SQL Server 2000个人版或标准版微型计算机：奔腾，每人一台三、实验步骤建立STUDENT、COURSE、SC表，向各表插入若干记录。CREATE TABLE Student /*创建student表*/(sno char(10), sname char(20) NOT NULL, ssex char(5), sage SMALLINT, dno char(5);CREATE TABLE Course/*创建course表*/(cno char(10),cname char(20) NOT NULL,tno char(10),ccredit int,cpno char(5);CREATE TABLE Sc/*创建Sc表*/(sno char(10),cno char(10) ,cgrade char(5); /*向Student表中添加记录*/INSERT INTO Student VALUES(1001,库克,女,20,01);INSERT INTO Student 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,数据库,1003,5,005);INSERT INTO Course VALUES(002,操作系统,1001,5,null);INSERT INTO Course VALUES(003,网络编程,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);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 VALUES(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);INSERT INTO Sc VALUES(1005,003,94);INSERT INTO Sc VALUES(1005,004,85);INSERT INTO Sc VALUES(1005,005,77);1.建立3个表的主码建立Student的主码，两条语句分别执行：ALTER TABLE Student ALTER COLUMN sno char(10) NOT NULL;ALTER TABLE Student ADD CONSTRAINT PK_Student PRIMARY KEY(sno);建立Course的主码，两条语句分别执行：ALTER TABLE Course ALTER COLUMN cno char(10) NOT NULL;ALTER TABLE Course ADD CONSTRAINT PK_Course PRIMARY KEY(cno);建立Sc表的主码，三条语句分别执行：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 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 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 CONSTRAINT 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表建立插入-更新触发器，学时小于等于32学时时学分不能超过2分，学时小于等于64学时时学分不能超过4分，任何课程的学分最高不能超过5分CREATE Trigger Insert_Or_Update_SalON CourseFOR 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有错误,调试不成功。四实验中遇到的问题及解决方法。1，在建立参照完整性约束时，执行如下语句ALTER TABLE Sc ADD CONSTRAINT FK_sno FOREIGN KEY REFERENCES Student(sno);ALTER TAB