数据库系统原理实验06数据库控制数据完整性.docx
- 文档编号:3738877
- 上传时间:2023-05-06
- 格式:DOCX
- 页数:9
- 大小:20.23KB
数据库系统原理实验06数据库控制数据完整性.docx
《数据库系统原理实验06数据库控制数据完整性.docx》由会员分享,可在线阅读,更多相关《数据库系统原理实验06数据库控制数据完整性.docx(9页珍藏版)》请在冰点文库上搜索。
数据库系统原理实验06数据库控制数据完整性
2011-2012学年第二学期课程实验报告
课程名称:
数据库系统原理实验名称:
数据库控制
(二):
数据完整性
姓名
班级
学号
实验台编号
同组同学
实验课表现
出勤、表现得分25%
25
实验报告
得分50%
实验总分
操作结果得分25%
25
实验目的
1.掌握数据完整性的概念及分类
2.掌握各种完整性的实现方法
3.掌握触发器的概念及工作原理
4.掌握触发器的创建和使用方法
实验内容(实验步骤和实验结果的简单描述,方便同学自己以后阅读)
以下题目均在STUxxxx(学生管理)数据库中完成
1.用户要求:
XS表中的学生的专业只能是“计算机”,“数学”,“电子”中的任意一个,请问如何解决该问题?
请写出一种具体的解决方法。
2.用户要求:
XS表中的学生的年龄必须在18岁到30岁之间,请问如何解决该问题?
请写出一种具体的解决方法。
3.用户要求:
在输入XS表中的数据时,若“专业”列没有提供数据,系统应自动取值“计算机”,请问如何解决该问题?
请写出一种具体的解决方法。
4.用户要求:
XS_KC表中的成绩列的取值只能为0—100之间的整数,且在用户没有输入数据时自动取值0,请问如何解决该问题?
请写出一种具体的解决方法。
5.编写语句为XS_KC表的“学号”列添加外键约束,该约束参照XS表的主键“学号”列。
该约束用于实现何种完整性?
向具有该约束的表中添加数据或修改数据时,应注意什么?
6.用户要求:
XS_KC表中的“课程号”列的取值应依赖于KC表中“课程号”的取值,且当KC表中某个“课程号”的取值发生改变时,XS_KC表中的对应的“课程号”也应级联更新,当KC表中某个“课程号”被删除时,XS_KC表中对应该“课程号”的数据也应级联删除,请问如何解决该问题?
请写出具体的解决方法。
7.创建一触发器,当向XS_KC表插入一记录时,检查该记录的学号在XS表中是否存在,检查该记录的课程号在KC表中是否存在,若有一项为否,则不允许插入。
8.创建一触发器,当在XS表中删除一个学生的基本信息时,级联删除该学生的学习信息(即该学生在XS_KC表中的数据)。
9.在XS_KC表上创建一触发器,若对学号列和课程号列修改,且修改后的学号或课程号在XS表和KC表中不存在,则给出提示信息,并取消修改操作。
10.在XS_KC表上创建一触发器,当对学生的学习成绩进行插入或修改时,根据学习成绩级联更新该学生在XS表中的总学分(对于插入操作,如果成绩及格,该学生的总学分应为原来的总学分加上该门课程的规定学分,否则总学分不变;对于修改操作,若原成绩不及格而修改后的成绩及格,则该学生的总学分应为原来的总学分加上该门课程的规定学分,若原成绩及格而修改后的成绩不及格,则该学生的总学分应为原来的总学分减去该门课程的规定学分)。
1.用户要求:
XS表中的学生的专业只能是“计算机”,“数学”,“电子”中的任意一个,请问如何解决该问题?
请写出一种具体的解决方法。
altertablexswithnocheck
addconstraintC1
check(专业in('计算机','电子','数学'))
2.用户要求:
XS表中的学生的年龄必须在18岁到30岁之间,请问如何解决该问题?
请写出一种具体的解决方法。
altertablexswithnocheck
addconstraintC2
check(2010-year(出生日期)between18and30)
3.用户要求:
在输入XS表中的数据时,若“专业”列没有提供数据,系统应自动取值“计算机”,请问如何解决该问题?
请写出一种具体的解决方法。
sp_helpconstraintxs
altertablexsdropconstraintDF_XS_专业
altertablexsaddconstraintxs_专业
default'计算机'for专业
4.用户要求:
XS_KC表中的成绩列的取值只能为0—100之间的整数,且在用户没有输入数据时自动取值0,请问如何解决该问题?
请写出一种具体的解决方法。
sp_helpconstraintxs_kc
altertablexs_kcdropconstraintDF__XS_KC__成绩__060DEAE8
altertablexs_kcaddconstraint成绩
check(成绩between0and100),default0for成绩
5.编写语句为XS_KC表的“学号”列添加外键约束,该约束参照XS表的主键“学号”列。
该约束用于实现何种完整性?
向具有该约束的表中添加数据或修改数据时,应注意什么?
altertablexs_kc
addconstraintxs_kc_学号foreignkey(学号)referencesxs(学号);
6.用户要求:
XS_KC表中的“课程号”列的取值应依赖于KC表中“课程号”的取值,且当KC表中某个“课程号”的取值发生改变时,XS_KC表中的对应的“课程号”也应级联更新,当KC表中某个“课程号”被删除时,XS_KC表中对应该“课程号”的数据也应级联删除,请问如何解决该问题?
请写出具体的解决方法。
altertablexs_kc
addconstraintxs_kc_kechenghao
foreignkey(课程号)referenceskc(课程号)
ondeletecascade
onupdatecascade
7.创建一触发器,当向XS_KC表插入一记录时,检查该记录的学号在XS表中是否存在,检查该记录的课程号在KC表中是否存在,若有一项为否,则不允许插入。
createtriggertronxs_kcafterinsert
asselect学号,课程号fromxs_kc
where学号notin(select学号fromxs)
or课程号notin(select课程号fromkc)
begin
raiserror('不允许插入',16,1)
rollbacktransaction
end
8.创建一触发器,当在XS表中删除一个学生的基本信息时,级联删除该学生的学习信息(即该学生在XS_KC表中的数据)。
altertablexs_kcdropconstraintxs_kc_学号
createtriggertri1onxsafterdelete
asdeletexswhere学号in(select学号fromxs_kc)
9.在XS_KC表上创建一触发器,若对学号列和课程号列修改,且修改后的学号或课程号在XS表和KC表中不存在,则给出提示信息,并取消修改操作。
createtriggertrionxs_kcforupdate
asselect学号,课程号fromxs_kc
where学号notin(select学号fromxs)
or课程号notin(select课程号fromkc)
begin
raiserror('不允许修改',16,1)
rollbacktransaction
end
10.在XS_KC表上创建一触发器,当对学生的学习成绩进行插入或修改时,根据学习成绩级联更新该学生在XS表中的总学分(对于插入操作,如果成绩及格,该学生的总学分应为原来的总学分加上该门课程的规定学分,否则总学分不变;对于修改操作,若原成绩不及格而修改后的成绩及格,则该学生的总学分应为原来的总学分加上该门课程的规定学分,若原成绩及格而修改后的成绩不及格,则该学生的总学分应为原来的总学分减去该门课程的规定学分)。
createtriggertg8onxs_kcforinsert,update
asif((select成绩frominserted))>=60
begininsertintoxs(总学分)
selectxs.总学分+kc.学分fromxs,inserted,kc
whereinserted.学号=xs.学号andinserted.课程号=kc.课程号end
elseif((select成绩fromxs_kc)<60and(select成绩frominserted)>=60)
beginupdatexssetxs.总学分=xs.总学分+kc.学分frominserted,xs,kc
whereinserted.学号=xs.学号andinserted.课程号=kc.课程号end
elseif((select成绩fromxs_kc)>=60and(select成绩frominserted)<60)
beginupdatexssetxs.总学分=xs.总学分-kc.学分frominserted,xs,kc
whereinserted.学号=xs.学号andinserted.课程号=kc.课程号end
实验过程中遇到的问题以及如何解决的?
(可以写多条,是否认真填写将影响实验成绩)
本次实验的体会(可以写多条,是否认真填写将影响实验成绩)
思考题
1.总结一下完整性约束的作用、主要类型及定义方法。
完整性约束的作用:
为了防止不符合规范的数据进入数据库,在用户对数据进行插入、修改、删除等操作时,DBMS自动按照一定的约束条件对数据进行监测,使不符合规范的数据不能进入数据库,以确保数据库中存储的数据正确、有效、相容。
完整性约束的类型:
可分为三种类型:
与表有关的约束、域约束、断言
1)与表有关的约束:
是表中定义的一种约束。
可在列定义时定义该约束,此时称为列约束,也可以在表定义时定义约束,此时称为表约束。
2)域约束:
在域定义中被定义的一种约束,它与在特定域中定义的任何列都有关系
3)断言:
在断言定义时定义的一种约束,它可以与一个或多个表进行关联。
完整性约束的定义:
1、实体完整性和主码
实体完整性是通过主码的定义来实现的。
一旦某个属性或属性组被定义为主码,该主码的每个属性就不能为空值,并且在表中不能出现主码值完全相同的两个记录。
主码可以在CREATETABLE语句中使用PRIMARYKEY定义。
有两种定义主码的方法:
一种是在属性后增加关键字,另一种是在属性表中加入额外的定义主码的子句:
PRIMARYKEY(主码属性名表)。
(1)属性后增加关键字定义
CREATETABLEStudentInfo
(
StudentIDchar(8)PRIMARYKEY,
StudentNamevarchar(10),
StudentSexbit
);
(2)加入额外的定义主码的子句
CREATETABLEStudentInfo
(
StudentIDchar(8),
StudentNamevarchar(10),
StudentSexbit,
PRIMARYKEY(StudentID)
);
如果表的主码只含有单个属性,上面的两种方法都可以使用。
如果主码由多个属性组成,只能使用第二种方法。
除了主码,SQL提供了类似候选码的说明方法,使用关键字UNIQUE定义(与候选码不同的是:
定义为UNIQUE的属性可以定义为空值,但只能有一个记录该属性的值为NULL),说明该属性(或属性组)的值不能重复。
一个表中只能有一个主码,但可以有多个“UNIQUE”定义。
2、参照完整性(引用完整性)约束和外部码
在更新记录时,参照完整性保持表之间已定义的关系。
参照完整性基于外键与主键之间或外键与唯一键之间的关系。
参照完整性确保键值在所有表中一致。
这样的一致性要求不能引用不存在的值,如果键值更改了,那么在整个数据库中,对该键值的所有引用要进行一致的更改。
强制参照完整性时,SQLServer禁止用户进行下列操作:
<1>当主表中没有关联的记录时,将记录添加到相关表中。
<2>更改主表中的值并导致相关表中的记录孤立。
<3>从主表中删除记录,但仍存在与该记录匹配的相关记录。
(1)外部码约束的说明
说明外部码的方法有两种:
<1>在该属性的说明(属性名、类型)后直接加上关键字REFERENCES,后跟对应表的主码说明
格式为:
REFERENCES<父表名>(<属性名>)
说明RelationInfo表中StudentID为外部码,参照关系为StudentInfo。
CREATETABLERelationInfo
(
RelationIDintidentity(1,1),
StudentIDchar(8)REFERENCESStudentInfo(StudentID),
DepartIDchar(4)
);
<2>在CREATETABLE语句的属性清单后,加上外部码的说明子句
格式为:
FOREIGN(<属性名表>)REFERENCES<父表名>(<属性名表>)
CREATETABLERelationInfo
(
RelationIDintidentity(1,1),
StudentIDchar(8),
DepartIDchar(4),
FOREIGNKEY(StudentID)REFERENCESStudentInfo(StudentID)
);
(2)参照完整性约束的实现策略
当用户的操作违反了上述规则时,SQL提供了两种可选方案供数据库实现者使用:
RESTRICT(限制策略);CASCADE(级联策略);
<1>限制策略
限制策略是SQL的默认策略,任何违反参照完整性的更新均被系统拒绝。
<2>级联策略
当用户删除或更新外键所指向的键时,SQL提供了另一种方案,即级联策略。
通过在REFERENCES子句后添加ONDELETE和ONUPDATE子句实现:
[ONDELETE{CASCADE|NOACTION}]
[ONUPDATE{CASCADE|NOACTION}]
如果没有指定ONDELETE或ONUPDATE,则默认为NOACTION。
ONDELETENOACTION
指定如果试图删除某行,而该行含有由其它表的现有行中的外键所引用的键,则产生错误并回滚DELETE。
ONUPDATENOACTION
指定如果试图更新某行中的键值,而该行含有由其它表的现有行中的外键所引用的键,则产生错误并回滚UPDATE。
CASCADE允许在表间级联键值的删除或更新操作,这些表的外键关系可追溯到执行修改的表。
不能为任何具有timestamp列的外键和主键指定CASCADE。
ONDELETECASCADE
指定如果试图删除某行,而该行含有由其它表的现有行中的外键所引用的键,则也将删除所有包含那些外键的行。
如果在目标表上也定义了级联引用操作,则对从那些表中删除的行同样采取指定的级联操
ONUPDATECASCADE
指定如果试图更新某行中的键值,而该行的键值由其它表的现有行中的外键所引用,则所有外键值也将更新成为该键指定的新值。
如果在目标表上也定义了级联引用操作,则对在那些表中更新的键值同样采取指定的级联操作。
3、用户自定义完整性约束
SQL提供非空约束、对属性的CHECK约束、对元组的CHECK约束、触发器等来实现用户的完整性要求。
<1>基于属性的CHECK约束
使用CHECK(检查)子句可保证属性值满足某些前提条件。
CHECK子句的一般格式为:
CHECK<条件>
属性的CHECK约束既可跟在属性的定义后,也可在定义语句中另增一子句加以说明。
设定StudentInfo表中age值不能小于18、大于65。
只需将age属性说明为如下形式:
ageintCHECK(age>=18andage<=65)
<2>基于元组的约束
CREATETABLEsalary
(
Enochar(4),
Basepaydecimal(7,2),
Insuredecimal(7,2),
Funddecimal(7,2),
CHECK(Insure+Fund ); 上面例子中,CHECK约束涉及到表中多个属性,为元组约束。 2.总结SQLServer触发器的特点及创建语法。 SQLServer触发器的特点: 自动执行。 触发器在对表的数据作了任何修改(比如手工输入或者应用程序的操作)之后立即被激活。 级联更新。 触发器可以通过数据库中的相关表进行层叠更改,这比直接把代码写在前台的做法更安全合理。 强化约束。 触发器可以引用其它表中的列,能够实现比CHECK约束更为复杂的约束。 跟踪变化。 触发器可以阻止数据库中未经许可的指定更新和变化。 强制业务逻辑。 触发器可用于执行管理任务,并强制影响数据库的复杂业务规则 DELIMITER| CREATETRIGGER` <[BEFORE|AFTER]><[INSERT|UPDATE|DELETE]> ON FOREACHROW BEGIN --dosomething END| 教师评价 实验态度A.很认真B.认真C.比较认真D.不认真 独立完成情况A.很好B.好C.比较好D.不好 收获和体会A.多B.比较多C.少D.很少 思考题回答情况A.很好B.好C.比较好D.不好 评阅教师: 日期:
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 数据库 系统 原理 实验 06 控制 数据 完整性