数据控制完整性控制报告

数据控制-完整性控制 实 验 报 告班级 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>='10000'and sno <='19999');验证：当插入记录为：INSERT INTO Student VALUES('2064020220','王龙','男',23,'01');显示如下信息： 6.STUDENT表的“所属系”定义为外码，并定义级联删除、级联修改 ALTER TABLE Student ADD CONSTRAINT S5 FOREIGN KEY REFERENCES Dept(Sdept) ON DELETE CASCADE ON UPDATE CASCADE7.删除SC表的成绩属性的约束 ALTER TABLE Sc DROP CONSTRAINT cgrade;8.SC表的成绩属性取值范围设为0-100分ALTER TABLE Sc ADD CONSTRAINT cgrade CHECK (cgrade >=0 and cgrade <=100);验证：当插入数据: INSERT INTO Sc VALUES('1005','005',103);显示如下信息：9.对SC表建立插入-更新触发器，当所插入或更新的成绩值超过100分，则警告，并拒绝接收新值CREATE TRIGGER insert_update ON Sc For INSERT, UPDATEAS IF (SELECT count(*) FROM inserted WHERE cgrade > 100) > 0BEGINPRINT ' insert_update error: you insert the grade > 100'ROLLBACK 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(thours<=32 and ccredit>2) if(thours>=64 and ccredit<4) if (ccredit>5) begin raiserror ('警告：非法操作！',16,1) rollback transactionend有错误,调试不成功。四实验中遇到的问题及解决方法。1，在建立参照完整性约束时，执行如下语句ALTER TABLE Sc ADD CONSTRAINT FK_sno FOREIGN KEY REFERENCES Student(sno);ALTER TAB