数据库 实验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_unionvalues('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)insertScholarshipvalues('whatmeaning','12345',10000)insertScholarshipvalues(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))insertcoursevalues('1','数据库',3)insertcoursevalues('2','数学',3)insertcoursevalues('3','物理',4)（2）在数据库tempdbforintegrity中,建立表SC，包括sno，cno及grade三列。令sno和cno分别为参照Stu_Union表以及Course表的外键，设定级联删除，并令（sno，cno）为其主键。在不违反参照完整性的前提下，插入数据。CREATETABLESC(CNOCHAR(4),SNOCHAR(5),GRADEINTPRIMARYKEY(CNO,SNO),FOREIGNKEY(CNO)REFERENCESCOURSE(CNO),FOREIGNKEY(SNO)REFERENCESSTU_UNION(SNO),)INSERTSCVALUES('1','35',76)insertsc(cno,sno,grade)values('2','35',88)insertsc(cno,sno,grade)values('3','35',99)（3）练习违反参照完整性的插入数据。（4）在Stu_Union中删除数据，练习级联删除。DELETEFROMSTU_UNIONWHERESNO='35'解决方案：CREATETABLESC(CNOCHAR(4),SNOCHAR(5),GRADEINTPRIMARYKEY(CNO,SNO),FOREIGNKEY(CNO)REFERENCESCOURSE(CNO)ONDELETECASCADE,FOREIGNKEY(SNO)REFERENCESSTU_UNION(SNO)ONDELETECASCADE,)删除成功（5）在Course中删除数据，练习级联删除。DELETEFROMcourseWHERECNO='1'（6）用altertable语句将SC表中的ondeletecascade改为ondeleterestrict。重新插入SC的数据。重复本实验中的（4）、（5），观察结果，分析原因。ALTERTABLESCDROPCONSTRAINTFK_SC_Sno;ALTERTABLESCADDCONSTRAINTFK_SC_CnoFOREIGNKEY(Cno)REFERENCESCourse(Cno)ONDELETERESTRICT（7）用altertable语句将SC表中的ondeletecascade改为ondeletesetNULL。重新插入SC的数据。重复本实验中的（4）、（5），观察结果，分析原因。alterTABLESCDROPCONSTRAINTFK_SC_CNOALTERADDCONSTRAINTFK_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('9558844022312','20101',11111.11)--goinsertstu_cardvalues('9558844022313','20102',15000)goinsertstu_cardvalues('9558844022314','20103',11111)goinsertstu_cardvalues('9558844022315','20104',11112)goselect*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_cardvalues('1','9558844022312',11111.11)goinsertICBC_cardvalues('2','9558844022313',15000)goinsertICBC_cardvalues('3','9558844022314',1111)goinsertICBC_cardvalues('4','9558844022315',11112)goselect*fromICBC_card（3）通过删除Stu_Union表中的一条记录，观察Stu_Union、Stu_Card、ICBC_Card三个表的多重级联删除。alterTABLEstu_cardDROPCONSTRAINTFK_stu_card_SNOGOALTERTABLEstu_cardADDCONSTRAINTFK_stu_card_SNOFOREIGNKEY(SNO)REFERENCESSTU_UNION(SNO)ONDELETECASCADEALTERTABLEicbc_cardADDCONSTRAINTFK_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_courseaddconstraintFK_listen_courseforeignkey(tno)referencesteacher_course(tno)goaltertableteacher_courseaddconstraintFK_teacher_courseforeignkey(cno)referenceslisten_course(cno)（2）创建一个班里的学生互助表，规定：包括学生编号，学生姓名，学生的帮助对象，每个学生有且仅有一个帮助对象，帮助对象也必须是班里的学生。createtablestudent(snochar(10),cnamechar(20),helperchar(20),primarykey(sno))altertablestudentaddconstraintfk_studentforeignkey(helper)referencesstudent(helper)（3）学校学生会的每个部门都有一个部长，每个部长领导多个部员，每个部只有一个部员有监察评测部长的权力。请给出体现这两种关系（即领导和评测）的两张互参照的表的定义。createtableleader(sidchar(9),snamevarchar(20),myleaderchar(9)primarykey(sid))createtablemonitor(sidchar(9),snamevarchar(20),mymonitorchar(9),primarykey(sid))altertableleaderaddconstraintFK_leaderforeignkey(myleader)referencesMonitor(sid)goaltertableMonitoraddconstraintFK_Monitorforeignkey(mymonitor)referencesleader(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