数据库实验9 数据控制完整性.docx
- 文档编号:26368323
- 上传时间:2023-06-18
- 格式:DOCX
- 页数:33
- 大小:539.75KB
数据库实验9 数据控制完整性.docx
《数据库实验9 数据控制完整性.docx》由会员分享,可在线阅读,更多相关《数据库实验9 数据控制完整性.docx(33页珍藏版)》请在冰豆网上搜索。
数据库实验9数据控制完整性
实验9数据控制(完整性部分)
实验9.1实体完整性
一、实验目的
学习实体完整性的建立,以及实践违反实体完整性的结果。
二、实验内容与要求
(1)在数据库tempdbforintegrity中建立表Stu_Union,包括sno(char(5)),sname(char(8)),ssex(char
(1)),sage(int),sdept(char(20))五个列,对sno进行主键约束,并且在没有违反实体完整性的前提下插入并更新一条记录
CREATETABLEStu_Union(snochar(5),
snamechar(8),
ssexchar
(1),
sageint,
sdeptchar(20))
。
没有什么异常,一切正常
(2)练习违反实体完整性的插入操作。
无法插入
(3)练习违反实体完整性的更新操作。
insertstu_union
values('12345','张海','M',22,'信工')
(4)在数据库tempdbforintegrity中建立表Scholarship,包括M_ID(varchar(30)),Stu_id(char(10)),R_Money(int))三个。
首先向该表插入数据,练习当与现有的数据环境不等时,无法建立实体完整性以及参照完整性。
createtableScholarship(
M_IDvarchar(30),Stu_idchar(10),R_Moneyint)
insertScholarship
values('whatmeaning','12345',10000)
insertScholarship
values(null,'12346',10000)
实验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
values('1','数据库',3)
insertcourse
values('2','数学',3)
insertcourse
values('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(CNO)REFERENCESCOURSE(CNO),
FOREIGNKEY(SNO)REFERENCESSTU_UNION(SNO),
)
INSERTSC
VALUES('1','35',76)
insertsc(cno,sno,grade)
values('2','35',88)
insertsc(cno,sno,grade)
values('3','35',99)
(3)练习违反参照完整性的插入数据。
(4)在Stu_Union中删除数据,练习级联删除。
DELETE
FROMSTU_UNION
WHERESNO='35'
解决方案:
CREATETABLESC(
CNOCHAR(4),SNOCHAR(5),GRADEINT
PRIMARYKEY(CNO,SNO),
FOREIGNKEY(CNO)REFERENCESCOURSE(CNO)
ONDELETECASCADE,
FOREIGNKEY(SNO)REFERENCESSTU_UNION(SNO)
ONDELETECASCADE,
)
删除成功
(5)在Course中删除数据,练习级联删除。
DELETE
FROMcourse
WHERECNO='1'
(6)用altertable语句将SC表中的ondeletecascade改为ondeleterestrict。
重新插入SC的数据。
重复本实验中的(4)、(5),观察结果,分析原因。
ALTERTABLESC
DROPCONSTRAINTFK_SC_Sno;
ALTERTABLESC
ADDCONSTRAINTFK_SC_CnoFOREIGNKEY(Cno)REFERENCESCourse(Cno)ONDELETERESTRICT
(7)用altertable语句将SC表中的ondeletecascade改为ondeletesetNULL。
重新插入SC的数据。
重复本实验中的(4)、(5),观察结果,分析原因。
alterTABLESC
DROPCONSTRAINTFK_SC_CNO
ALTER
ADDCONSTRAINTFK_SC_CNOFOREIGNKEY(CNO)
REFERENCESCOURSE(CNO)ONDELETESETNULL
(二)、多重级联删除
(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,2)
primarykey(card_id),
foreignkey(sno)referencesstu_union(sno)
)
--insertstu_card
--values('12','20101',11111.11)
--go
insertstu_card
values('13','20102',15000)
go
insertstu_card
values('14','20103',11111)
go
insertstu_card
values('15','20104',11112)
go
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_id)referencesstu_card(card_id))
insertICBC_card
values('1','12',11111.11)
go
insertICBC_card
values('2','13',15000)
go
insertICBC_card
values('3','14',1111)
go
insertICBC_card
values('4','15',11112)
go
select*
fromICBC_card
(3)通过删除Stu_Union表中的一条记录,观察Stu_Union、Stu_Card、ICBC_Card三个表的多重级联删除。
alterTABLEstu_card
DROPCONSTRAINTFK_stu_card_SNO
GO
ALTERTABLEstu_card
ADDCONSTRAINTFK_stu_card_SNOFOREIGNKEY(SNO)
REFERENCESSTU_UNION(SNO)ONDELETECASCADE
ALTERTABLEicbc_card
ADDCONSTRAINTFK_icbc_card_stu_card_idFOREIGNKEY(stu_card_id)
REFERENCESSTU_card(card_id)ONDELETECASCADE
(三)、表的互参照问题及其解决方法
(1)建立教师授课和教师听课关系的两张表,规定一个教师可以授多门课,但是每门课程只能指定一个教师去授课,同时,规定一门课程可以由多位老师听课,但是每位老师只能听一门课程。
所以要为两张表建立相互之间的参照关系。
createtableteacher_course
(
tnochar(4),
tnamechar(10),
cnochar(4),
primarykey(tno)
)
createtablelisten_course
(
tnochar(4),
tnamechar(10),
cnochar(4),
primarykey(cno,tname)
)
altertablelisten_course
addconstraintFK_listen_courseforeignkey(tno)referencesteacher_course(tno)
go
altertableteacher_course
addconstraintFK_teacher_courseforeignkey(cno)referenceslisten_course(cno)
(2)创建一个班里的学生互助表,规定:
包括学生编号,学生姓名,学生的帮助对象,每个学生有且仅有一个帮助对象,帮助对象也必须是班里的学生。
createtablestudent
(
snochar(10),
cnamechar(20),
helperchar(20),
primarykey(sno)
)
altertablestudent
addconstraintfk_studentforeignkey(helper)referencesstudent(helper)
(3)学校学生会的每个部门都有一个部长,每个部长领导多个部员,每个部只有一个部员有监察评测部长的权力。
请给出体现这两种关系(即领导和评测)的两张互参照的表的定义。
createtableleader(
sidchar(9),
snamevarchar(20),
myleaderchar(9)
primarykey(sid))
createtablemonitor
(
sidchar(9),
snamevarchar(20),
mymonitorchar(9),
primarykey(sid)
)
altertableleader
addconstraintFK_leaderforeignkey(myleader)references
Monitor(sid)
go
altertableMonitor
addconstraintFK_Monitorforeignkey(mymonitor)references
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(8)CONSTRAINTU1UNIQUE,
SEXCHAR
(1),
SAGEINTCONSTRAINTU2CHECK(SAGE<=28),
DEPARTMENTCHAR(20)
)
(2)在worker表中插入一条合法记录。
Insertworkers
Values(‘1’,’nameuse’,’M’,21,’IM’)
(3)练习插入违反U2约束的例子,U2规定元组的sage属性的值必须<=28。
INSERTWORKERS
VALUES('1','NAMEUSE1','M',29,'IM')
GO
SELECT*
FROMWORKERS
(4)去除U2约束。
altertableworkers
dropu2
(5)重新插入(3)中想要插入的数据,观察结果,分析原因。
INSERTWORKERS
VALUES('1','NAMEUSE1','M',29,'IM')
GO
SELECT*
FROMWORKERS
(6)创建规则rule_sex,规定插入或更新的值只能是M或F,并绑定到worker的sex字段。
CREATERULERULE_SEXAS@VALUEIN('F','M')
GO
EXECSP_BINDRULERULE_SEX,'WORKERS.[SEX]';
(7)练习违反规则rule_sex的插入操作。
INSERTWORKERS
VALUES(‘1’,’NAMEUSER2’,’W’,18,’IM’)
(8)加入约束U3,令sage的值>=0。
ALTERTABLEWorkersADDCONSTRAINTU3CHECK(Sage>=0)
(9)加入规则R2,确保插入的记录的sage值在1到100之间,并绑定到sage属性上。
CreateruleR2AS@SAGEBEWTEEN1AND100
GO
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
AS
IF(SELECTNUMBERFROMDELETED)=’00001’
BEGIN
PRINT‘禁止删除编号为00001的CEO’
ROLLBACKTRANSACTION
END
(3)worker表中的人员的编号是唯一且不可改变的,创建触发器T3实现更新中编号的不可改变性。
CREATETRIGGERT3ONWORKERS
FORUPDATE
AS
IFUPDATE(NUMBER)
BEGIN
PRINT‘不得更新该数据’
ROLLBACKTRANSACTION
END
(4)练习违反T1触发器的约束的插入操作。
INSERTWORKERS
VALUES('00001','name2','F',0,'IM')
(5)练习违反T1触发器的约束的更新操作。
--INSERTWORKERS
--VALUES('00001','name2','F',12,'IM')
--GO
UPDATEWORKERS
SETSAGE=0
WHERESAGE=12
(6)练习违反T2触发器的约束的插入操作。
--INSERTWORKERS
--VALUES('00001','NAME1','F',27,'IM')
DELETEWORKERS
WHERENUMBER='00001'
(7)练习违反T3触发器的约束的更新操作。
INSERTWORKERS
VALUES('00002','name3','F',12,'IM')
GO
UPDATEWORKERS
SETNUMBER='0000'
WHERENUMBER='00002'
SELECT*
FROMWORKERS
(8)建立一个在worker表上的触发器T4,要求插入记录的sage值必须比表中已记录的最大sage值大。
CREATETRIGGERT4ONWORKERS
FORINSERT
AS
IF(SELECTSAGEFROMINSERTED)>(SELECTMAX(SAGE)FROMWORKERS)
BEGIN
PRINT'必须输入的值比表中已记录的最大sage值大。
'
ROLLBACKTRANSACTION
END
(9)建立一个在worker表上的触发器T5,要求当更新一个记录的时候,表中的记录的sage值要比原记录的sage值大,因为一般工资级别只能升不能降。
CREATETRIGGERT5ONWORKERS
FORUPDATE
AS
IF(SELECTSAGEFROMINSERTED)>(SELECTSAGEFROMDELETED)
BEGIN
PRINT'必须输入的值比原记录的sage值大。
'
ROLLBACKTRANSACTION
END
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 数据库 实验9 数据控制完整性 实验 数据 控制 完整性