实验六数据库完整性.docx
《实验六数据库完整性.docx》由会员分享，可在线阅读，更多相关《实验六数据库完整性.docx（22页珍藏版）》请在冰豆网上搜索。
实验六数据库完整性
云南大学软件学院
实验报告
课程：
数据库原理与实用技术实验任课教师：
张璇，刘宇
专业：
软件工程学号：
姓名：
成绩：
实验6数据库完整性
bindandunbind的格式
SP_bindruleEnter_University_date_rule,'students.enroll_date'
SP_unbindrule'students.enroll_date'
sp_bindefaultTime_default,'students.enroll_date'
sp_unbindefault'students.age'
实验6-1完整性约束
1、创建规则（用图形或者语句方法创建）
（1）创建入学日期规则“Enter_University_date_rule”，假定该学校于1923年4月30日创建。
要求：
入学日期必须大于等于学校创建日期，并且小于等于当前日期
CREATERULEEnter_University_date_rule
AS@enroll_date>='4/30/1923'AND@enroll_date<=GETDATE（）
GO
SP_bindruleEnter_University_date_rule,'students.enroll_date'
（2）创建学生年龄规则“Age_rule”。
要求：
学生年龄必须在15～30岁之间
CREATERULEAge_rule
AS@age>=15AND@age<=30
GO
sp_bindruleAge_rule,'students.age'
（3）创建学生性别规则“Sex_rule”。
要求：
性别只能为“男”或“女”
CREATERULESex_rule
AS@genderIN（'男','女'）
GO
sp_bindruleSex_rule,'students.gender'
（4）创建学生成绩规则“Score_rule”。
要求：
学生成绩只能在0～100之间
CREATERULEScore_rule
AS@grade>=0AND@grade<=100
GO
sp_bindruleScore_rule,'grades.grade'
（5）用图形方法查看学生成绩规则“Score_rule”，截图为：
（6）用语句方法查看学生成绩规则“Score_rule”，语句为：
EXECsp_helptextScore_rule
2、删除规则Enter_University_date_rule
SP_unbindrule'students.enroll_date'
GO
DROPRULEEnter_University_date_rule
3、创建默认（用图形或者语句方法创建）
（1）创建默认时间“Time_default”为当前系统时间
CREATEDEFAULTTime_default
ASGETDATE（）
GO
sp_bindefaultTime_default,'students.enroll_date'
（2）创建默认入学年龄“Age_default”为18岁
CREATEDEFAULTAge_default
AS18
GO
sp_bindefaultAge_default,'students.age'
（3）用图形方法查看默认入学年龄“Age_default”，截图为：
（4）用语句方法查看默认入学年龄“Age_default”，语句为：
EXECsp_helptextAge_default
4、删除默认入学年龄“Age_default”
sp_unbindefault'students.age'
GO
DROPDEFAULTAge_default
5、创建声明式默认：
在创建表的过程中创建声明式默认
（1）创建表“default_example”，表中包含字段pid、name、sex、age。
要求设定sex的默认值为“男”、age的默认值为18。
输入数据：
编号
姓名
性别
年龄
101
苏晴
女
201
马拯山
20
执行结果为：
CREATETABLEdefault_example
（pidintPRIMARYKEY,
namevarchar（20）NOTNULL,
sexchar（10）DEFAULT'男',
ageintDEFAULT18
）
GO
INSERTINTOdefault_example（pid,name,sex）
VALUES（101,'苏晴','女'）
GO
INSERTINTOdefault_example（pid,name,age）
VALUES（201,'马拯山',20）
SELECT*
FROMdefault_example
（2）在表中增加新的默认，将编号默认为100。
插入一条记录，执行结果为：
ALTERTABLEdefault_example
ADDCONSTRAINTpid_defaultDEFAULT100FORpid
GO
INSERTINTOdefault_example（name,sex,age）
VALUES（'张三','男',20）
GO
SELECT*
FROMdefault_example
6、在“学生管理数据库”各个数据表中建立相应的主键、外键、惟一值、以及check约束，要求：
学生的年龄必须是两位数，其中第一位是1或2：
Students表
ALTERTABLEstudents
ADDconstraintpk_stud_idPRIMARYKEY（stud_id）
ALTERTABLEstudents
WITHNOCHECK
ADDconstraintck_genderCHECK（genderIN（'男','女'））
ALTERTABLEstudents
WITHNOCHECK
ADDconstraintck_ageCHECK（age>=10ANDage<30）
ALTERTABLEstudents
WITHNOCHECK
ADDconstraintck_enroll_dateCHECK（enroll_date>='4/30/1923'ANDenroll_date<=GETDATE（））
因为已经创建好主键约束，所以此次主键约束无效，check约束有效
Courses表：
ALTERTABLEcourses
ADDconstraintpk_cidPRIMARYKEY（cid）
ALTERTABLEcourses
WITHNOCHECK
ADDconstraintck_creditCHECK（creditISNULLORcredit>0）
Grades表：
ALTERTABLEgrades
ADDconstraintpk_gradesPRIMARYKEY（stud_id,cid）
ALTERTABLEgrades
ADDconstraintfk_grades1FOREIGNKEY（stud_id）REFERENCESstudents
ALTERTABLEgrades
ADDconstraintfk_grades2FOREIGNKEY（cid）REFERENCEScourses
ALTERTABLEgrades
WITHNOCHECK
ADDconstraintck_creditCHECK（creditISNULLORcredit>0）
ALTERTABLEgrades
WITHNOCHECK
ADDconstraintck_gradeCHECK（gradeISNULLORgradebetween0and100）
Instructs表：
ALTERTABLEinstructs
ADDconstraintpk_iidPRIMARYKEY（iid,class）
ALTERTABLEinstructs
WITHNOCHECK
ADDconstraintck_periodCHECK（periodISNULLORperiod>0）
删除check约束：
Students表：
ALTERTABLEstudents
DROPconstraintck_gender
ALTERTABLEstudents
DROPconstraintck_age
ALTERTABLEstudents
DROPconstraintck_enroll_date
7、完成教科书456页如下习题：
[7.1]a：
CREATETABLEproducts（
pidchar（3）notnull,
pnamenchar（10）notnull,
citynchar（10）notnull,
quantityintnotnullcheck（quantity>0）,
pricedecimal（8,2）notnullcheck（price>0.00）,
primarykey（pid）
）
GO
CREATETABLEagent（
aidnchar（3）notnull,
anamenchar（10）notnull,
citynchar（10）notnull,
percentintnotnullcheck（percentbeween0and10）,
primarykey（aid）
）
实验6-2触发器
1、使用触发器
（1）创建一个触发器trig_update，返回对“学生表”进行更新操作后，被更新的记录条数
CREATETRIGGERtrig_update
ONstudents
AFTERUPDATE
AS
Print'TRIGGEROUTPUT:
'+convert（varchar（5）,@@rowcount）+'rowswereupdated.
（2）执行触发器
UPDATEstudents
SETstud_id=stud_id
WHEREstud_id=20009001
（3）修改触发器trig_update，除返回被更新的记录条数外，再返回学生的所有基本信息
DROPTRIGGERtrig_update;
GO
CREATETRIGGERtrig_update
ONstudents
AFTERUPDATE
AS
BEGIN
Print'TRIGGEROUTPUT:
'+convert（varchar（5）,@@rowcount）+'rowswereupdated.'
SELECT*FROMstudents
END
执行触发器
UPDATEstudents
SETstud_id=stud_id
WHEREstud_id=20009001
2、使用触发器的两个特殊表：
插入表（inserted）和删除表（deleted）。
（1）在“学生表”上创建触发器ins_del_sample，在对学生表进行插入、删除或更新操作后，分别从inserted表和deleted表中查询学生学号、姓名、性别、年龄和所在院系。
（请同学们在做删除操作时，注意备份）
SELECT*INTOstudents_copyFROMstudents
GO
CREATETRIGGERins_del_sample
ONstudents
AFTERUPDATE,INSERT,DELETE
AS
BEGIN
Print'INSERTED:
'
SELECTstud_id,stud_name,gender,age,school
FROMinserted
Print'DELETED:
'
SELECTstud_id,stud_name,gender,age,school
FROMdeleted
END
（2）执行插入、删除和更新操作后返回的表有什么区别？
INSERTINTOstudents
VALUES（20026004,'李四','男',19,'计算机','软件班','2002/08/27'）
DELETEFROMstudents
WHEREstud_id=20026004
UPDATEstudents
SETage=22
WHEREstud_id=20026004
触发了这个触发器trig_update
3、使用系统存储过程查看触发器
（1）显示触发器trig_update的一般信息
EXECSP_HELPtrig_update
（2）显示触发器trig_update的源代码
EXECSP_HELPTEXTtrig_update
（3）显示“学生表”上所有的依赖关系
EXECSP_DEPENDSstudents
（4）显示触发器trig_update所引用的对象
EXECSP_DEPENDStrig_update
4、难题（注意inserted表和deleted表的使用，并请自己修改数据表）
（1）为“成绩表”创建一个触发器，当向表中插入数据时，如果成绩大于等于60分，该学生就能得到相应的学分，否则，该学生不能得到学分。
SELECT*INTOgrades_copyFROMgrades
GO
CREATETRIGGERtrig_grade
ONgrades_copy
AFTERINSERT
AS
BEGIN
UPDATEgrades_copy
SETcredit=0
WHEREgrade<60andstud_idIN（SELECTstud_idFROMinserted）
END
测试插入成绩小于60分的学生：
INSERTINTOgrades_copy
VALUES（20023002,'C801',59,4）
SELECT*FROMgrades_copy
WHEREstud_id=20023002ANDcid='C801'
测试插入成绩大于等于60分的学生：
INSERTINTOgrades_copy
VALUES（20023002,'C802',90,4）
SELECT*FROMgrades_copy
WHEREstud_id=20023002ANDcid='C802'
（2）为“学生表”创建一个触发器，当删除表中的一个学生资料时，将“成绩表”中的相应记录也删除掉
CREATETRIGGERtrig_students
ONstudents_copy
AFTERDELETE
AS
BEGIN
DELETEFROMgrades_copy
WHEREstud_idIN（SELECTstud_idFROMdeleted）
END
测试删除学号为20023002的学生：
DELETEFROMstudents_copy
WHEREstud_id=20023002
GO
SELECT*FROMstudents_copy
GO
SELECT*FROMgrades_copy
（3）为“成绩表”创建一个触发器，如果成绩由原来的小于60分更改为大于等于60分时，该学生就得到相应的学分，如果成绩由原来的大于等于60分更改为小于60分，则该学生的相应学分更改为0。
SELECT*INTOgradescopyFROMgrades
GO
CREATETRIGGERtrig_changegrade
ONgradescopy
AFTERUPDATE
AS
BEGIN
UPDATEgradescopy
SETcredit=0
WHEREgrade<60andstud_idIN（SELECTstud_idFROMinserted）
ANDstud_idIN（SELECTstud_idFROMdeletedWHEREgrade>=60）
END
UPDATEgradescopy
SETgrade=58
WHEREstud_id=20023001ANDcid='C801'
SELECT*FROMgradescopy
WHEREstud_id=20023001ANDcid='C801'
5、完成教科书456页如下习题：
[7.11]a：
CREATETRIGGERagent_city
ONagent
AFTERINSERT
AS
IF（（SELECTcityFROMinserted）NOTIN（SELECTcityFROMcustomers））
BEGIN
PRINT'thecityisnotexist,pleaseenteragain!
'
END
GO
INSERTINTOagent
VALUES（'a07','Gray','NewYork',6）
[7.11]b：
DROPTRIGGERtrig_prod
SELECT*INTOproducts_copyFROMproducts
GO
CREATETRIGGERtrig_prod
ONorders
AFTERINSERT
AS
BEGIN
UPDATEproducts_copy
SETquantity=quantity-（SELECTqtyFROMinserted）
WHEREpid=（SELECTpidFROMinserted）
END
GO
INSERTINTOorders
VALUES（'1027','may','c002','a05','p03',800,704.00）
GO
SELECT*
FROMproducts_copy
执行插入语句之前：
执行插入语句之后：
升级会员 