数据库实验报告04

本科实验报告课程名称：数据库系统概论实验项目：数据完整性实验地点：软件楼208专业班级：软件1401学号：2014005490学生姓名：崔正宇指导教师：安建成2016年6月20日实验4数据完整性一、实验目的和要求（1）了解SQLSerer数据库系统中数据完整性控制的基本方法（2）熟练掌握常用CREATE或ALTER在创建或修改表时设置约束（3）了解触发器的机制和使用（4）验证数据库系统数据完整性控制二．实验平台使用SQLServer数据库管理系统提供的SSMS和查询编辑器。三、实验内容和原理结合ST数据库中的各个表，设置相关的约束，要求包括主键约束、外键约束、唯一约束、检查约束、非空约束等，掌握各约束的定义方法。设置一个触发器，实现学生选课总学分的完整性控制，了解触发器的工作机制。设计一些示例数据，验证完整性检查机制。要求包括如下方面的内容：3.1使用SQL语句设置约束使用CREATE或ALTER语句完成如下的操作，包括：1．设置各表的主键约束2．设置相关表的外键3.设置相关属性的非空约束、默认值约束、唯一约束4.设置相关属性的CHECK约束3.2使用触发器创建一个触发器，实现如下的完整性约束：当向SC表中插入一行数据时，自动将学分累加到总学分中。记录修改学分的操作。3.4检查约束和触发器分别向相关表插入若干条记录，检查你设置的完整性约束是否有效：1．插入若干条包含正确数据的记录，检查插入情况2．分别针对设置的各个约束，插入违反约束的数据，检查操作能否进行3．向SC表插入若干行数据，检查触发器能否实现其数据一致性功能。四、实验内容实验结果与分析创建学生选课数据库TEST，包括三个基本表，其中Student表保存学生基本信息，Course表保存课程信息，SC表保存学生选课信息，其结构如下表：1．创建基本表及约束（1）创建Student表CREATETABLEStudent(SnoCHAR(8)PRIMARYKEY,SnameCHAR(8)NOTNULL,SsexCHAR(2)CHECK(Ssexin('男','女')),SageSMALLINT,SdeptCHAR(20),SclassCHAR(4)NOTNULL,StotalsmallintDEFAULT0);（2）创建Course表CREATETABLECourse(CnoCHAR(4)CONSTRAINTFK_CoursePRIMARYKEY,CnameCHAR(40),CpnoCHAR(4),CcreditSMALLINT);（3）创建SC表CREATETABLESC(SnoCHAR(8)FOREIGNKEY(Sno)REFERENCESStudent(Sno),CnoCHAR(4),GradeSMALLINTCONSTRAINTSC_CHECKCHECK(Grade0ANDGrade100),PRIMARYKEY(Sno,Cno),CONSTRAINTFK_SCFOREIGNKEY(Cno)REFERENCESCourse(Cno));插入数据（1）插入学生信息到Student表INSERTINTOstudentVALUES('20100001','李勇','男',20,'CS','1001',0)INSERTINTOStudentVALUES('20100002','刘晨','女',19,'CS','1001',0)INSERTINTOStudentVALUES('20100021','王敏','女',18,'MA','1002',0)INSERTINTOStudentVALUES('20100031','张立','男',19,'IS','1003',0)INSERTINTOStudentVALUES('20100003','刘洋','女',NULL,NULL,'1001',0)INSERTINTOStudentVALUES('20100010','赵斌','男',19,'IS','1005',0)INSERTINTOStudentVALUES('20100022','张明明','男',19,'CS','1002',0)（2）插入到课程信息到Course表INSERTINTOCourse(Cno,Cname,Cpno,Ccredit)VALUES('1','数据库系统原理','5',4)INSERTINTOCourse(Cno,Cname,Cpno,Ccredit)VALUES('2','高等数学',null,2)INSERTINTOCourse(Cno,Cname,Cpno,Ccredit)VALUES('3','管理信息系统','1',4)INSERTINTOCourse(Cno,Cname,Cpno,Ccredit)VALUES('4','操作系统原理','6',3)INSERTINTOCourse(Cno,Cname,Cpno,Ccredit)VALUES('5','数据结构','7',4)INSERTINTOCourse(Cno,Cname,Cpno,Ccredit)VALUES('6','数据处理',null,2)INSERTINTOCourse(Cno,Cname,Cpno,Ccredit)VALUES('7','C语言',null,4)（3）插入到SC表INSERTINTOSCVALUES('20100001','1',92)INSERTINTOSCVALUES('20100001','2',85)INSERTINTOSCVALUES('20100001','3',88)INSERTINTOSCVALUES('20100002','1',90)INSERTINTOSCVALUES('20100002','2',80)INSERTINTOSCVALUES('20100003','1',null)INSERTINTOSCVALUES('201000010','3',null)截图如下：修改约束对数据库中已经存在的表，可对其增加约束或修改已存在的约束：（1）添加约束ALTERTABLECourseADDUNIQUE(Cname)ALTERTABLECourseADDFOREIGNKEY(Cpno)REFERENCESCourse(Cno)（2）修改约束ALTERTABLESCDROPCONSTRAINTSC_CHECKALTERTABLESCADDCONSTRAINTSC_CHECKCHECK(Grade=0ANDGrade=100)二、检查完整性约束通过修改数据库中的数据检查完整性约束条件的作用。1．检查主键约束（1）执行下面的语句修改Student表，观察语句能否正确运行，解释为什么？INSERTINTOStudentVALUES('20100101','李斌','男',20,'CS','1001',0)INSERTINTOStudentVALUES('20100001','李斌','男',20,'CS','1001',0)消息2627，级别14，状态1，第1行违反了PRIMARYKEY约束'PK__Student__DDDF64467F60ED59'。不能在对象'dbo.Student'中插入重复键。语句已终止。（因为sno为主键不能有重复，所以无法运行）UPDATEStudentSETSno='20100021'WHERESname='张立'消息2627，级别14，状态1，第1行违反了PRIMARYKEY约束'PK__Student__DDDF64467F60ED59'。不能在对象'dbo.Student'中插入重复键。语句已终止。（张立要修改的学跟王敏的学号重复了，由于学号是学生表的主键，不可以重复，所以无法运行）（2）执行下面的语句修改SC表，观察语句能否正确运行，解释为什么？INSERTINTOSCVALUES('20100001','1',78)INSERTINTOSCVALUES('20100001',null,78)2．检查唯一约束执行下面的语句修改Course表，观察语句能否正确运行，解释为什么？INSERTINTOCourseVALUES('8','JAVA',7,3)INSERTINTOCourseVALUES('9','数据结构',7,3)3．检查默认值、允许空值列运行如下的语句：INSERTINTOStudent(Sno,Sname,Ssex,Sclass)VALUES('20100102','张盛','男','1008')观察插入数据行的数值SELECT*FROMStudentWHereSno='20100102'4．检查非空约束下面的语句包含空值，检查运行结果，解释为什么？INSERTINTOStudent(Sno,Sname,Ssex,Sclass)VALUES('20100103','张盛','男',NULL)INSERTINTOStudent(Sno,Sname,Ssex)VALUES('20100104','张盛','男')5．检查CHECK约束执行下面的语句，解释其运行结果。INSERTINTOSCVALUES('20100001','4',95)INSERTINTOSCVALUES('20100001','4',102)INSERTINTOStudent(Sno,Sname,Ssex,Sclass)VALUES('20100103','张盛','男','1008')INSERTINTOStudent(Sno,Sname,Ssex,Sclass)VALUES('20100104','张盛','','1008')6．检查外键约束（1）执行下面的语句检查外键约束的作用INSERTINTOSCVALUES('20100301','1',95)INSERTINTOSCVALUES('20100001','10',95)UPDATESCSETCno='10'WhereCno='1'UPDATECourseSETCno='10'WhereCno='3'（2）执行下面的语句检查对被引用表的约束Student表：DELETEStudentWHERESno='20100021'DELETEStudentWHERESno='20100001'UPDATECourseSETCno='10'WHERECname='数据库系统原理'三、触发器的定义及使用1．定义触发器（1）定义一个触发器，实现有关学分的完整性约束：当向SC表插入一行选课记录时，自动将该课程的学分累加到该学生的总学分中。CREATETRIGGERtr_INSERTONSCFORINSERTAS--声明变量DECLARE@snochar(8)DECLARE@criditintDECLARE@cnochar(4)--提取插入的数据SELECT@sno=Sno,@cno=CnoFROMinserted--提取学生的总学分SELECT@cridit=CcreditFROMSCjoinCourseON(SC.Cno=Course.Cno)WHERESC.Cno=@cno--更新总学分UPDATEStudentSETStotal=Stotal+@criditWHERESno=@snoGO（2）定义一个触发器，实现对SC表的操作登记：当用户向SC表插入或修改时，记录该操作到数据库中。创建日志登记表:CREATETABLELOG_TABLE(usernamechar(10),--操作人员datedatetime,--修改时间Snochar(8),--学生学号Cnochar(4)--课程号)创建日志登记触发器:CREATETRIGGERtr_UPDATEONSCFORINSERT,UPDATEASDECLARE@snochar(8)DECLARE@cnochar(4)DECLARE@newsmallintSELECT@sno=Sno,@cno=CnoFROMinsertedINSERTINTOLOG_TABLEVALUES(CURRENT_USER,getdate()