数据库实验9数据控制完整性Word文档下载推荐.docx
《数据库实验9数据控制完整性Word文档下载推荐.docx》由会员分享，可在线阅读，更多相关《数据库实验9数据控制完整性Word文档下载推荐.docx（28页珍藏版）》请在冰豆网上搜索。
首先向该表插入数据,练习当与现有的数据环境不等时,无法建立实体完整性以及参照完整性。
createtableScholarship（
M_IDvarchar（30,Stu_idchar（10,R_Moneyint
insertScholarship
values（'
whatmeaning'
10000
values（null,'
12346'
实验9.2参照完整性
学习建立外键,以及利用FOREIGNKEY…REFERENCES子句以及各种约束保证参照完整性。
二、实验内容
（一、参照完整性
（1为练习参照完整性,在数据库tempdbforintegrity中建立表Course,包括cno（char（4,cname（varchar（50,cpoints（int三列,令cno为其主键;
并且在表
Course中插入数据。
为下面的实验步骤做预先准备。
CREATETABLECOURSE（
CNOCHAR（4,CNAMEVARCHAR（50,CPOINTSINT,
PRIMARYKEY（CNO
insertcourse
1'
数据库'
3
2'
数学'
3'
物理'
4
（2在数据库tempdbforintegrity中,建立表SC,包括sno,cno及grade三列。
令sno和cno分别为参照Stu_Union表以及Course表的外键,设定级联删除,并令（sno,cno为其主键。
在不违反参照完整性的前提下,插入数据。
CREATETABLESC（
CNOCHAR（4,SNOCHAR（5,GRADEINT
PRIMARYKEY（CNO,SNO,
FOREIGNKEY（CNOREFERENCESCOURSE（CNO,
FOREIGNKEY（SNOREFERENCESSTU_UNION（SNO,
INSERTSC
VALUES（'
35'
76
insertsc（cno,sno,grade
88
99
（3练习违反参照完整性的插入数据。
（4在Stu_Union中删除数据,练习级联删除。
DELETE
FROMSTU_UNION
WHERESNO='
解决方案:
FOREIGNKEY（CNOREFERENCESCOURSE（CNO
ONDELETECASCADE,
FOREIGNKEY（SNOREFERENCESSTU_UNION（SNO
删除成功
（5在Course中删除数据,练习级联删除。
FROMcourse
WHERECNO='
（6用altertable语句将SC表中的ondeletecascade改为ondeleterestrict。
重新插入SC的数据。
重复本实验中的（4、（5,观察结果,分析原因。
ALTERTABLESC
DROPCONSTRAINTFK_SC_Sno;
ADDCONSTRAINTFK_SC_CnoFOREIGNKEY（CnoREFERENCESCourse（CnoONDELETERESTRICT
（7用altertable语句将SC表中的ondeletecascade改为ondeletesetNULL。
alterTABLESC
DROPCONSTRAINTFK_SC_CNO
ALTER
ADDCONSTRAINTFK_SC_CNOFOREIGNKEY（CNO
REFERENCESCOURSE（CNOONDELETESETNULL
（二、多重级联删除
（1为了练习多重级联删除,建立Stu_Card表,包括card_id（char（14,stu_id,remained_money（decimal（10,2三列,令stu_id为参照Stu_Union表的外键,令card_id为其主键,并插入数据。
CREATETABLEStu_Card（
card_idchar（14,snochar（5,remained_moneydecimal（10,2primarykey（card_id,
foreignkey（snoreferencesstu_union（sno
--insertstu_card
--values（'
9558844022312'
20101'
11111.11--go
insertstu_card
9558844022313'
20102'
15000
go
9558844022314'
20103'
11111
9558844022315'
20104'
11112
select*
fromstu_card
（2为了练习多重级联删除,建立ICBC_Card表,包括bank_id（char（20,stu_card_id,restored_money（decimal（10,2三列,令stu_card_id为参照Stu_Card表的外键,令bank_id为其主键,并插入数据。
createtableICBC_Card（
bank_idchar（20,
stu_card_idchar（14,
restored_moneydecimal（10,2,
Primarykey（bank_id,
Foreignkey（stu_card_idreferencesstu_card（card_id
insertICBC_card
11111.11
1111
4'
fromICBC_card
（3通过删除Stu_Union表中的一条记录,观察Stu_Union、Stu_Card、ICBC_Card三个表的多重级联删除。
alterTABLEstu_card
DROPCONSTRAINTFK_stu_card_SNO
GO
ALTERTABLEstu_card
ADDCONSTRAINTFK_stu_card_SNOFOREIGNKEY（SNOREFERENCESSTU_UNION（SNOONDELETECASCADE
ALTERTABLEicbc_card
ADDCONSTRAINTFK_icbc_card_stu_card_idFOREIGNKEY（stu_card_idREFERENCESSTU_card（card_idONDELETECASCADE
（三、表的互参照问题及其解决方法
（1建立教师授课和教师听课关系的两张表,规定一个教师可以授多门课,但是每门课程只能指定一个教师去授课,同时,规定一门课程可以由多位老师听课,但是每位老师只能听一门课程。
所以要为两张表建立相互之间的参照关系。
createtableteacher_course
（
tnochar（4,
tnamechar（10,
cnochar（4,
primarykey（tno
createtablelisten_course
primarykey（cno,tname
altertablelisten_course
addconstraintFK_listen_courseforeignkey（tnoreferencesteacher_course（tno
altertableteacher_course
addconstraintFK_teacher_courseforeignkey（cnoreferenceslisten_course（cno（2创建一个班里的学生互助表,规定:
包括学生编号,学生姓名,学生的帮助对象,每个学生有且仅有一个帮助对象,帮助对象也必须是班里的学生。
createtablestudent
snochar（10,
cnamechar（20,
helperchar（20,
primarykey（sno
altertablestudent
addconstraintfk_studentforeignkey（helperreferencesstudent（helper
（3学校学生会的每个部门都有一个部长,每个部长领导多个部员,每个部只有一个部员有监察评测部长的权力。
请给出体现这两种关系（即领导和评测的两张互参照的表的定义。
createtableleader（
sidchar（9,
snamevarchar（20,
myleaderchar（9
primarykey（sid
createtablemonitor
mymonitorchar（9,
altertableleader
addconstraintFK_leaderforeignkey（myleaderreferences
Monitor（sid
altertableMonitor
addconstraintFK_Monitorforeignkey（mymonitorreferences
leader（sid
9.3用户自定义完整性
学习用户自定义约束,并实践用户自定义完整性,利用SQL查询分析器用短语NOTNULL、UNIQUE、CHECK保证用户定义完整性。
（1创建worker表,包括Number（char（5,Name（char（8,Sex（char（1,Sage（int,Department（char（20五个字段。
请自定义2个约束U1以及U2,其中U1规定Name字段唯一,U2规定sage（级别字段的上限是28。
CREATETABLEWORKERS（
NUMBERCHAR（5,
NAMECHAR（8CONSTRAINTU1UNIQUE,
SEXCHAR（1,
SAGEINTCONSTRAINTU2CHECK（SAGE<
=28,
DEPARTMENTCHAR（20
（2在worker表中插入一条合法记录。
Insertworkers
Values（‘1’,’nameuse’,’M’,21,’IM’
（3练习插入违反U2约束的例子,U2规定元组的sage属性的值必须<
=28。
INSERTWORKERS
NAMEUSE1'
29,'
IM'
SELECT*
FROMWORKERS
（4去除U2约束。
altertableworkers
dropu2
（5重新插入（3中想要插入的数据,观察结果,分析原因。
（6创建规则rule_sex,规定插入或更新的值只能是M或F,并绑定到worker的sex字段。
CREATERULERULE_SEXAS@VALUEIN（'
F'
EXECSP_BINDRULERULE_SEX,'
WORKERS.[SEX]'
;
（7练习违反规则rule_sex的插入操作。
VALUES（‘1’,’NAMEUSER2’,’W’,18,’IM’
（8加入约束U3,令sage的值>
=0。
ALTERTABLEWorkersADDCONSTRAINTU3CHECK（Sage>
=0
（9加入规则R2,确保插入的记录的sage值在1到100之间,并绑定到sage属性上。
CreateruleR2AS@SAGEBEWTEEN1AND100
SP_BINDRULER2,’WORKERS.[SAGE]’
9.4触发器
通过实验加深对数据完整性的理解,学会创建和使用触发器。
（1为worker表建立触发器T1,当插入或是更新表中数据时,保证所操作的记录的sage值大于0。
CREATETRIGGERT1ONWORKERS
FORUPDATE,INSERT
AS
IF（SELECTSageFROMINSERTED<
1
BEGIN
PRINT'
年龄必须是大于0的整数!
操作失败!
'
ROLLBACKTRANSACTION
END
（2为worker表建立触发器T2,禁止删除编号为00001的CEO。
CREATETRIGGERT2ONWORKERS
FORDELETE
IF（SELECTNUMBERFROMDELETED=’00001’
PRINT‘禁止删除编号为00001的CEO’
（3worker表中的人员的编号是唯一且不可改变的,创建触发器T3实现更新中编号的不可改变性。
CREATETRIGGERT3ONWORKERS
FORUPDATE
IFUPDATE（NUMBER
PRINT‘不得更新该数据’
END
（4练习违反T1触发器的约束的插入操作。
00001'
name2'
0,'
（5练习违反T1触发器的约束的更新操作。
--INSERTWORKERS
--VALUES（'
12,'
--GO
UPDATEWORKERS
SETSAGE=0
WHERESAGE=12
（6练习违反T2触发器的约束的插入操作。
NAME1'
27,'
DELETEWORKERS
WHERENUMBER='
（7练习违反T3触发器的约束的更新操作。
00002'
name3'
SETNUMBER='
0000'
SELECT*FROMWORKERS（8）建立一个在worker表上的触发器T4，要求插入记录的sage值必须比表中已记录的最大sage值大。
CREATETRIGGERT4ONWORKERSFORINSERTASIF（SELECTSAGEFROMINSERTED>
（SELECTMAX（SAGEFROMWORKERSBEGINPRINT'
必须输入的值比表中已记录的最大sage值大。
ROLLBACKTRANSACTIONEND（9）建立一个在worker表上的触发器T5，要求当更新一个记录的时候，表中的记录的sage值要比原记录的sage值大，因为一般工资级别只能升不能降。
CREATETRIGGERT5ONWORKERS
FORUPDATEASIF（SELECTSAGEFROMINSERTED>
（SELECTSAGEBEGINPRINT'
必须输入的值比原记录的sage值大。
ROLLBACKTRANSACTIONENDFROMDELETED
升级会员 