数据库实验9数据控制完整性Word文档下载推荐.docx
- 文档编号:21618272
- 上传时间:2023-01-31
- 格式:DOCX
- 页数:28
- 大小:1.04MB
数据库实验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
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 数据库 实验 数据 控制 完整性
![提示](https://static.bdocx.com/images/bang_tan.gif)