实验五

实验五实验目的:熟悉SQLSERVER2005的操作,实践表的基本操作实验环境:WINDOWS7,MSSQLSERVER2005实验内容:5.1实体完整性1）在数据库School中建立表Stu_Union，进行主键约束，在没有违反实体完整性的前提下插入并更新一条记录createtableStu_Union(Snochar(9)primarykey,Snamechar(20)notnull,Ssexchar(2),Sagesmallint,Sdeptchar(20));2）演示违反实体完整性的插入操作insertintoStu_Union(Sno,Ssex,Sage,Sdept)values('12345','男','20','CS');3）演示违反实体完整性的更新操作insertintoStu_Union(Sno,Sname,Ssex,Sage,Sdept)values('12345','陈某','男','20','CS');insertintoStu_Union(Sno,Sname,Ssex,Sage,Sdept)values('12346','林某','男','20','IS');updateStu_UnionsetSno='12345'whereSno='12346'4）演示事务的处理，包括事务的建立，处理以及出错时的事务回滚,演示事务处理和批处理的区别。setxact_abortonbegintraninsertintoStu_Union(Sno,Sname,Ssex,Sage,Sdept)values('12347','王某','男','20','IS');insertintoStu_Union(Sno,Ssex,Sage,Sdept)values('12345','男','20','CS');committran5）通过建立Scholarship表，插入一些数据。演示当与现有的数据环境不等时，无法建立实体完整性以及参照完整性。createtableScholarship(Snochar(9)referencesStu_Union(Sno),Moneynumint);insertintoScholarshipvalues('100','100');5.2参照完整性1）为演示参照完整性，建立表Course，令cno为其主键，并在Stu_Union中插入数据。为下面的实验步骤做预先准备。createtableCourse(Cnochar(10)primarykey,Cnamechar(40));2）建立表sc，另sno和cno分别为参照Stu_Union表以及Course表的外键，设定为级连删除，并令(sno,cno)为其主键。在不违反参照完整性的前提下，插入数据。createtableSC(Snochar(9),Cnochar(10),primarykey(Sno,Cno),foreignkey(Sno)referencesStu_Union(Sno)ondeletecascade,foreignkey(Cno)referencesCourse(Cno)ondeletecascade);3）演示违反参照完整性的插入数据insertintoSCvalues('12345','123')4）在Stu_Union中删除数据，演示级连删除。insertintoCoursevalues('123','数据库')insertintoSCvalues('12345','123')deletefromStu_UnionwhereSno='12345'5）Course中删除数据，演示级连删除。insertintoSCvalues('12345','123')deletefromSCwhereSno='123'6）为了演示多重级连删除，建立Stu_Card表，令stu_id为参照Stu_Union表的外键，令card_id为其主键，并插入数据。createtableStu_Card(stu_idchar(9)referencesStu_Union(Sno)ondeletecascade,card_idchar(9)primarykey);insertintoStu_Cardvalues('12345','123')7）为了演示多重级连删除，建立ICBC_Card表，令stu_card_id为参照Stu_Card表的外键，令bank_id为其主键，并插入数据。createtableICBC_Card(stu_card_idchar(9)referencesStu_Card(card_id)ondeletecascade,bank_idchar(9)primarykey);insertintoICBC_Cardvalues('123','111')8）通过删除stu_union表中的一条记录，演示三个表的多重级连删除。deletefromStu_UnionwhereSno='12345'9）演示事务中进行多重级连删除失败的处理。修改ICBC_Card表的外键属性，使其变为OndeleteNoaction,演示事务中通过删除stu_union表中的一条记录，多重级连删除失败，整个事务回滚到事务的初始状态。setxact_abortonbegintrandeletefromStu_UnionwhereSno='12345'committran5.3索引的建立和作用1.实验目的学会在SQLSERVER中建立索引通过本实验体会覆盖索引的作用，在以后的实践中，能适时地使用覆盖索引来提高数据库的性能。通过实验体会聚簇索引的优缺点，学会根据具体情况创建聚簇索引2.实验内容1）STUDENTS(sid,sname,email,grade)在sname上建立聚簇索引，grade上建立非聚簇索引，并分析所遇到的问题createclusteredindexStusnameonStudents(sname)createindexStusgradeonStudents(grade)删除主键之后便可以创建新的聚集索引2）数据库SCHOOL的选课表CHOICES有如下结构：CHOICES(no，sid，tid，cid，score)假设选课表集中用于查询分析，经常执行统计某课程修读的学生人数查询访问要求：A．首先执行没有索引的实验（设数据库CHOICES表在cid列上没有索引）SelectCONVERT(varchar(100),GETDATE(),121)selectcount(*)fromchoiceswherecid='10046'SelectCONVERT(varchar(100),GETDATE(),121)A．然后做有索引的实验SelectCONVERT(varchar(100),GETDATE(),121)selectcount(*)fromchoiceswherecid='10046'SelectCONVERT(varchar(100),GETDATE(),121)B．对比试验结果，并进行分析3）以数据库SCHOOL中CHOICES表为例，设建表时考虑到以后经常有一个用sid查询此学生所有选课信息的查询，考虑到一般学生不止选一门课，且要询问这些记录的所有信息，故在sid上建立索引，使相同sid的记录存在一起，取数据页面时能一起取出来，减少数据页面的存取次数要求：A．首先执行没有任何索引的情况SelectCONVERT(varchar(100),GETDATE(),121)selectcount(*)fromchoiceswheresid='825773822'SelectCONVERT(varchar(100),GETDATE(),121)B．在sid上建有非聚簇索引的情况createindexsidIndexonCHOICES(sid)SelectCONVERT(varchar(100),GETDATE(),121)selectcount(*)fromchoiceswheresid='825773822'SelectCONVERT(varchar(100),GETDATE(),121)C．在sid上建有聚簇索引的情况dropindexCHOICES.sidIndexcreateclusteredindexsidIndexonCHOICES(sid)SelectCONVERT(varchar(100),GETDATE(),121)selectcount(*)fromchoiceswheresid='825773822'SelectCONVERT(varchar(100),GETDATE(),121D．对比实验结果，并进行分析5.4触发器的建立和使用实验目的：通过实验使学生加深对数据完整性的理解，学会创建和使用触发器实验内容：1）创建Teacher（Tno，Tname，Tsex，Tage，Tdept）表，并自定义2个约束U1和U2，其中U1规定Tname字段唯一，U2规定Tage上限是28.为Teacher表建立触发器T1，当插入或更新表中的数据时，保证所操作的记录的Tage值大于0，并插入几条数据.createtableTeacher(Tnochar(9),Tnamechar(20)unique,Tsexchar(2),Tageintcheck(Tage20),Tdeptchar(20));createtriggerT1onTeacherforinsert,updateasif(selectTagefrominserted)1beginprint'Tage要大于'rollbacktransactionendinsertintoTeachervalues('12345','陈某','男',1,'CS')insertintoTeachervalues('12345','蔡某','男',0,'CS')2）为Teacher表建立触发器T2，禁止删除编号s01的校长。createtriggerT2onTeacherfordeleteasif(selectTnofromdeleted)='s01'beginprint'Thisisboss.Denytodelete'rollbacktransactionendinsertintoTeachervalues('s01','林某','男',18,'CS')deletefromTeacherwhereTno='s01'3）Teacher表中的人员的编号是唯一且不可变更的，创建触发器实现更新中编号的不可改变性。createtriggerT3onTeacherforupdateasifupdate(Tno)beginprint'Denytoupdate'rollbacktransactionendupdateTeachersetTno='123'whereTno='12345'4）设置一个触发器，该触发器仅允许“dbo”用户可以删除Teacher表内的数据，否则出错。createtriggerT4onTeacherfordeleteasif(USER!='dbo')beginprint'Denytodelete'rollbacktransactionend5）设置一个针对Teacher表的触发器，当有人更新Teachder表时，触发器将自动将该操作者的名称和操作时间记录在一张表内，以便追查。createtableinfor(namechar(20),timeDatetime);createtriggerT5onTeacherforupdateasbegininsertintoinforvalues(USER,getDate())endupdateTeachersetTage=19whereTage=19