数据库实验9 数据控制.docx

《数据库实验9 数据控制.docx》由会员分享，可在线阅读，更多相关《数据库实验9 数据控制.docx（32页珍藏版）》请在三一办公上搜索。

1、实验9数据控制(完整性部分)实验91实体完整性一、实验目的学习实体完整性的建立，以及实践违反实体完整性的结果。二、实验内容与要求(1)在数据库 tempdbforintegrity 中建立表 Stu_Union,包括 sno(char(5), sname(char(8), ssex(char(1), sage(int), sdept(char(20)五个列，对 sno 进行 主键约束，并且在没有违反实体完整性的前提下插入并更新一条记录CREATE TABLE Stu_Union(sno char(5),sname char(8),ssex char(1),sage int,sdept char

2、(20)tepdbforintegrity名称匹:数据库常规数据交件事务日志tempdbforintegrit无 （服务器默认设置）状态：（未知）所有者：（未知）创建日期：（未知）太小：（未知）可用空间：（未知）用户数：（未知）备份上次数据库备份：就护维护计划:排序规则名称:上故事舞日志备份:确定帮助.EK商 eapdbf orint egrity中在 H (local)上)的新表ilbuJV.-.解dbi一 MmsdlNorlpublw 3W-JI舒逃wJIn 受躁 J壬?-洋 汶 a 亨 inH没有什么异常，一切正常(2)练习违反实体完整性的插入操作。无法插入(3)练习违反实体完整性的更新

3、操作。insert stu_unionvalues（12345,张海,M,22,信工）(4 ) 在数据库 tempdbforintegrity 中建立表 Scholarship , 包括 M_ID(varchar(30), Stu_id (char(10), R_Money(int)三个。首先向该表插入 数据，练习当与现有的数据环境不等时，无法建立实体完整性以及参照完整 性。create table Scholarship(M_ID varchar(30),Stu_id char(10),R_Money int)insert Scholarshipvalues(what meaning,123

4、45,10000)insert Scholarshipvalues(null,12346,10000)M13年&月22日星期六10:40:01数据座：tempdbFor integrity 应用往序：MS SQLEM - Data Tools*/Scholarship表-木能修涉表。ODBC 错误： MicrosoFtODBC SQL Seruer DriuerSQL Server无*去将 NULL 值插入歹lj M ID,表tempdbForintegrity .dbo.Tmp Scholarship；该列不允 I午空值.insert失败。一MicrosoftODBC SQL Seruer

5、DriuerSQL SeruerH吾句己当冬止。实验92参照完整性一、实验日的学习建立外键，以及利用FOREIGN KEY-REFERENCES子句以及各种约束保证参照完整性。二、实验内容(一)、参照完整性(1)为练习参照完整性，在数据库tempdbforintegrity中建立表Course，包括 cno(char(4), cname(varchar(50), cpoints (int)三列，令 cno 为其主键； 并且在表Course中插入数据。为下面的实验步骤做预先准备。CREATE TABLE COURSE(CNO CHAR(4),CNAME VARCHAR(50) ,CPOINTS

6、INT,PRIMARY KEY(CNO)insert coursevalues(1,数据库,3)insert coursevalues(2,数学,3)insert coursevalues(3，物理,4)(2) 在数据库 tempdbforintegrity 中,建立表 SC， 包括 sno， cno 及 grade 三列。令sno和cno分别为参照Stu_Union表以及Course表的外键，设定级联删除，并 令(sno, cno)为其主键。在不违反参照完整性的前提下，插入数据。CREATE TABLE SC(CNO CHAR(4),SNO CHAR(5),GRADE INTPRIMARY

7、KEY(CNO,SNO),FOREIGN KEY (CNO) REFERENCES COURSE(CNO),FOREIGN KEY (SNO) REFERENCES STU_UNION(SNO),)INSERT SCVALUES(T,35,76)insert sc(cno,sno,grade)values(2,35,88)insert sc(cno,sno,grade)values(3,35,99)(3) 练习违反参照完整性的插入数据。(4)在Stu_Union中删除数据，练习级联删除。DELETEFROM STU_UNIONWHERE SNO=35服务器：.消息京T,缀别1&状态1，行1.,

8、.DELETE 语句与 COLNMN REFERENCE 约束FK_SC_sno_1DE57479冲突。该冲突发生干数据库temp dbf or integrity,表SC, column , sno, o 厝句已终止n一一 一一-解决方案：CREATE TABLE SC(CNO CHAR(4),SNO CHAR(5),GRADE INTPRIMARY KEY(CNO,SNO),FOREIGN KEY (CNO) REFERENCES COURSE(CNO)ON DELETE CASCADE,FOREIGN KEY (SNO) REFERENCES STU_UNION(SNO)ON DELET

9、E CASCADE,)DELETEFROM STU_UNION WERE SNO :, 35?所影响的行数为1行）删除成功 （5）在Course中删除数据，练习级联删除。DELETEFROM courseWHERE CNO=1(6)用 alter table 语句将 SC 表中的 on delete cascade 改为 on delete restricto 重新 插入SC的数据。重复本实验中的（4）、（5），观察结果，分析原因。ALTER TABLE SCDROP CONSTRAINT FK_SC_Sno;ALTER TABLE SCADD CONSTRAINT FK_SC_Cno FOR

10、EIGN KEY(Cno) REFERENCES Course(Cno) ON DELETE RESTRICT(7)用 alter table 语句将 SC 表中的 on delete cascade 改为 on delete set NULL o 重新插入SC的数据。重复本实验中的(4)、(5),观察结果，分析原因。alter TABLE SCDROP CONSTRAINT FK_SC_CNOALTERADD CONSTRAINT FK_SC_CNO FOREIGN KEY (CNO)REFERENCES COURSE(CNO) ON DELETE SET NULL(二)、多重级联删除(1)

11、为了练习多重级联删除，建立Stu_Card表，包括card_id (char(14)，stu_id， remained_money (decimal(10,2)三列，令 stu_id 为参照 Stu_Union 表的外键，令 card_id为其主键，并插入数据。insert stu_unionvalues20l02,、 yuajnan,M22f IM) goinsert stu_urLions tu_uni onvalues ( 20l03?,王三22/ IM,)goinsert stu_unionvalues 20T04,/ 王四,/M 22/ IM)go|select *from stu

12、unionsnoSn：dJTLHssexsagesdept120101Shanghaif21IM220102yuajnarLM22IM320103王三M22IM420104王四M22IM口网格卸消息批查询完成。QT-201205091440 (8.0)QT-；CREATE TABLE Stu_Card(card_id char(14),sno char(5),remained_money decimal(10,2)primary key(card_id),foreign key(sno)references stu_union(sno)-insert stu_card-values(95588

13、44022312,20101,11111.11)-goinsert stu_cardvalues(9558844022313,20102,15000)goinsert stu_cardvalues(9558844022314,20103,11111)goinsert stu_cardvalues(9558844022315,20104,11112)goselect *from stu_cardXXL-CJ. L d L LL_ U LL U.values(? 9558844022312,/ 20101 11111.11)goinsert stu_cardvalues (? 9558844022

14、313,/ 20102?, 15000)goinsert stu_cardvalues (? 9558844022314,/ 20103?, 11111)goinsert stu_cardvalues 9558844022315?/ 20104?, 11112)goselect *11111card_idsnor emaine d_money95588440223122010111111.11295588440223132010215000.00395588440223142010311111.00495588440223152010411112.00口网格瑾1消息批查询完成-QT-20120

15、5091440 (8. 0)QT-201205091440Admini st(2)为了练习多重级联删除，建立ICBC_Card表，包括bank_id (char(20), stu_card_id, restored_money(decimal(10,2)三列0，令 stu_card_id 为参照 Stu_Card 表的外键，令bank_id为其主键，并插入数据。create table ICBC_Card(bank_id char(20),stu_card_id char(14),restored_money decimal(10,2),Primary key(bank_id),Foreign

16、 key(stu_card_id) references stu_card(card_id)insert ICBC_cardvalues(1,9558844022312,11111.11)goinsert ICBC_cardvalues(2,9558844022313,15000)goinsert ICBC_cardvalues(3,9558844022314,1111) goinsert ICBC_card values(4,9558844022315,11112) g select *from ICBC_card(3)通过删除 Stu_Union 表中的一条记录，观察 Stu_Union、

17、Stu_Card、ICBC_Card三个表的多重级联删除。alter TABLE stu_cardDROP CONSTRAINT FK_stu_card_SNOGOALTER TABLE stu_cardADD CONSTRAINT FK_stu_card_SNO FOREIGN KEY (SNO)REFERENCES STU_UNION(SNO) ON DELETE CASCADEALTER TABLE icbc_cardADD CONSTRAINT FK_icbc_card_stu_card_id FOREIGN KEY (stu_card_id)REFERENCES STU_card(c

18、ard_id) ON DELETE CASCADE(三)、表的互参照问题及其解决方法(1) 建立教师授课和教师听课关系的两张表，规定一个教师可以授多门课，但 是每门课程只能指定一个教师去授课，同时，规定一门课程可以由多位老师听课， 但是每位老师只能听一门课程。所以要为两张表建立相互之间的参照关系。create table teacher_course(tno char(4),tname char(10),cno char(4),primary key(tno)create table listen_course(tno char(4),tname char(10),cno char(4),pr

19、imary key(cno,tname)alter table listen_courseadd constraint FK_listen_course foreign key(tno)references teacher_course(tno)g。alter table teacher_courseadd constraint FK_teacher_course foreign key(cno)references listen_course(cno)(2) 创建一个班里的学生互助表，规定：包括学生编号，学生姓名，学生的帮 助对象，每个学生有且仅有一个帮助对象，帮助对象也必须是班里的学生。c

20、reate table student(sno char(10),cname char(20),helper char(20),primary key(sno)alter table studentadd constraint fk_student foreign key(helper) references student(helper)alter taDie stuaentadd constraint fk_student foreign key helper references student helperJill服务器：消息璋花，级别6,状态1，行1、荏彼引用表, student,申

21、派看与项卜槌, fk student1的引用列的列表匹配的主中或候选键。服务器：消息1花”级别16,状态1,什1未能创建约束c请参阅前面的错误信息.(3) 学校学生会的每个部门都有一个部长，每个部长领导多个部员，每个部只 有一个部员有监察评测部长的权力。请给出体现这两种关系(即领导和评测)的 两张互参照的表的定义。create table leader (sid char(9),sname varchar(20),myleader char(9)primary key(sid)create table monitor(sid char(9),sname varchar(20),mymonito

22、r char(9),primary key(sid)alter table leaderadd constraint FK_leader foreign key(myleader)referencesMonitor(sid)g。alter table Monitoradd constraint FK_Monitor foreign key(mymonitor)references leader(sid)查询QT-201205091440. t epdbf orint egrity. QT-201205091440Ad*inist rat orconst raintleader foreit a

23、bleMonitorconst raintMonitor forei命令已成功完成。Hl*.汕上-dba. leader国列 启 口索引 白! dbo.FK leader 17F790F9 (dbo. FK_leader夕卜辟约束)国口相关性亩口触发器+| db-| db+ _|列+ _|索引-约束fit dbo.PK_moni tor_l9DFD96Bu. li 5ten cuni-sho. moni torfit db o. FK_Moni t or 由卜键约束 j Q相关性亩触发器rin II it9.3用户自定义完整性cs; 叩t able一、实验日的学习用户自定义约束，并实践用户自定

24、义完整性，利用SQL查询分析器用 短语NOT NULL、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。CREATE TABLE WORKERS(NUMBER CHAR(5),NAME CHAR(8) CONSTRAINT U1 UNIQUE,SEX CHAR(1),SAGE INT CONSTR

25、AINT U2 CHECK(SAGE=28),DEPARTMENT CHAR(20)(2)在worker表中插入一条合法记录。Insert workersValues(T,nameuse,M,21,IM)伸 查询 QT-201205091440. usenase. QT-201205091Insert workersValues r j J nanieuse，, M21, J IM) go select * from workersUi1NUIffiERNMESEKSAGEDEPARTMENT1n：dJTLeuseM21IM(3)练习插入违反U2约束的例子，U2规定元组的sage属性的值必须=

26、0)布查询QT-201205091440.usenaMe.QT-201205091440AdAinisALTER TABLE Workers ADD CONSTRAINT U3 CHECK (Sage -=0 J命令已成功完成。LI瓯1松 百I酒日(9)加入规则R2,确保插入的记录的sage值在1至到 100之间，并绑定到sage属性上。Create rule R2 AS SAGE BEWTEEN 1 AND 100GOSP_BINDRULE R2,WORKERS.SAGECreate rule R2 AS SAGE BETV/EEN 1 AND 100GOSP_BINDRULE R2 WOR

27、KERS. SAGE?LI网榕前消息9. 4触发器一、实验日的通过实验加深对数据完整性的理解，学会创建和使用触发器。二、实验内容(1)为worker表建立触发器T1,当插入或是更新表中数据时，保证所操作的 记录的sage值大于0。CREATE TRIGGER T1 ON WORKERSFOR UPDATE,INSERTASIF (SELECT Sage FROM INSERTED)(SELECT MAX(SAGE) FROMWORKERS)BEGINPRINT 必须输入的值比表中已记录的最大sage值大。ROLLBACK TRANSACTIONEND(9) 建立一个在worker表上的触发器T5，要求当更新一个记录的时候，表中 的记录的sage值要比原记录的sage值大，因为一般工资级别只能升不能降。CREATE TRIGGER T5 ON WORKERSFOR UPDATEASIF (SELECT SAGE FROM INSERTED )(SELECT SAGE FROM DELETED)BEGINPRINT 必须输入的值比原记录的sage值大。,ROLLBACK TRANSACTIONEND