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

实验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(20)te>pdbforintegrity名称匹:数据库常规数据交件事务日志tempdbforintegrit无 （服务器默认设置）状态：（未知）所有者：（未知）创建日期：（未知）太小：（未知）可用空间：（未知）用户数：（未知）备份上次数据库备份：就护维护计划:排序规则名称:上故事舞日志备份:确定帮助.EK'商 eapdbf orint egrity"中在 H (local)"上)的新表ilbuJV.-.解dbi一 MmsdlNorlpublw 3W-JI舒逃wJIn 受躁 J壬?-洋 汶 a 亨 inH没有什么异常，一切正常(2)练习违反实体完整性的插入操作。无法插入(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','12345',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 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 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 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.,.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 DELETE 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 FOREIGN 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)为了练习多重级联删除，建立Stu_Card表，包括card_id (char(14)，stu_id， remained_money (decimal(10,2)三列，令 stu_id 为参照 Stu_Union 表的外键，令 card_id为其主键，并插入数据。insert stu_unionvalues20l02,、' yuajnan,'M'22f ' IM') goinsert stu_urLions tu_uni onvalues (' 20l03?,'王三22/ IM,)goinsert stu_unionvalues 20T04,/ 王四,/M 22/ IM')go|select *from stu 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('9558844022312','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 (? 9558844022313,/ 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-201205091440 (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 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、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(card_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),primary 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) 创建一个班里的学生互助表，规定：包括学生编号，学生姓名，学生的帮 助对象，每个学生有且仅有一个帮助对象，帮助对象也必须是班里的学生。create 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,申派看与项卜槌, 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),mymonitor 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 e»pdbf orint egrity. QT-201205091440Ad*inist rat orconst raintleader foreit ableMonitorconst 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一、实验日的学习用户自定义约束，并实践用户自定义完整性，利用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 CONSTRAINT 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，, ' M'21, J IM') go select * from workers<Ui1NUIffiERNMESEKSAGEDEPARTMENT1n：dJTLeuseM21IM(3)练习插入违反U2约束的例子，U2规定元组的sage属性的值必须<=28。INSERT WORKERSVALUES(T,'NAMEUSE1','M',29,'IM')GOSELECT *FROM WORKERS（4）去除U2约束。alter table workersdrop u2(5)重新插入(3)中想要插入的数据，观察结果，分析原因。INSERT WORKERSVALUES(T,'NAMEUSE1','M',29,'IM')GOSELECT *FROM WORKERS(6)创建规则rule_sex，规定插入或更新的值只能是M或F，并绑定到worker的sex字段。CREATE RULE RULE_SEX AS VALUE IN ('F','M')GOEXEC SP_BINDRULE RULE_SEX, 'WORKERS.SEX'(7)练习违反规则rule_sex的插入操作。INSERT WORKERSVALUES(T,NAMEUSER2,W,18,IM)usenaMeINSERT workersVALUES 1，/NMEUSER2，/W，, 18,' IM')服务器：消息81宓，级别16,状态9,行1 将截断字符串或二进制数据。倍句已终止。(8)加入约束U3,令sage的值=0。ALTER TABLE Workers ADD CONSTRAINT U3 CHECK (Sage>=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 WORKERS. SAGE?LI网榕前消息9. 4触发器一、实验日的通过实验加深对数据完整性的理解，学会创建和使用触发器。二、实验内容(1)为worker表建立触发器T1,当插入或是更新表中数据时，保证所操作的 记录的sage值大于0。CREATE TRIGGER T1 ON WORKERSFOR UPDATE,INSERTASIF (SELECT Sage FROM INSERTED)<1BEGINPRINT '年龄必须是大于0的整数！操作失败！ROLLBACK TRANSACTIONEND(2)为worker表建立触发器T2，禁止删除编号为00001的CEO。CREATE TRIGGER T2 ON WORKERSFOR DELETEASIF (SELECT NUMBER FROM DELETED)=00001 BEGINPRINT 禁止删除编号为00001的CEOROLLBACK TRANSACTIONEND(3) worker表中的人员的编号是唯一且不可改变的，创建触发器T3实现更新 中编号的不可改变性。CREATE TRIGGER T3 ON WORKERSFOR UPDATEASIF UPDATE(NUMBER)BEGINPRINT 不得更新该数据ROLLBACK TRANSACTIONEN D(4)练习违反T1触发器的约束的插入操作。INSERT WORKERSVALUES('00001','name2','F',0,'IM')(5)练习违反T1触发器的约束的更新操作。-INSERT WORKERS-VALUES('00001','name2','F',12,'IM')-GOUPDATE WORKERSSET SAGE=0WHERE SAGE=12(6)练习违反T2触发器的约束的插入操作。-INSERT WORKERS -VALUES('00001','NAME1','F',27,'IM')DELETE WORKERSWHERE NUMBER='00001'(7) 练习违反T3触发器的约束的更新操作。NOffiERNMESEKSAGEDEPARTMENT11n：dJTLeuseM21IM21NMEUSElM29IM300001namelF27IM400001name 2F12IM口网格瑾1消息批查询完成。QT-2012INSERT WORKERSVALUES('00002','name3','F',12,'IM')GOUPDATE WORKERSSET NUMBER='0000'WHERE NUMBER='00002'SELECT *FROM WORKERS(8) 建立一个在worker表上的触发器T4,要求插入记录的sage值必须比表中 已记录的最大sage值大。CREATE TRIGGER T4 ON WORKERSFOR 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