數(shù)據(jù)庫(kù)原理與應(yīng)用實(shí)驗(yàn)3-1

1、實(shí)驗(yàn)3.1 數(shù)據(jù)完整性管理一、實(shí)驗(yàn)?zāi)康膌 掌握域完整性的實(shí)現(xiàn)方法。l 掌握實(shí)體完整性的實(shí)現(xiàn)方法。l 掌握參照完整性的方法。二、實(shí)驗(yàn)內(nèi)容數(shù)據(jù)庫(kù)的完整性設(shè)置。三、實(shí)驗(yàn)步驟可視化：1）實(shí)體完整性2）域完整性 3）參照完整性命令方式：1）實(shí)體完整性1將“student”表的“sno”字段設(shè)為主鍵：alter table student add constraint pk_sno primary key (sno)2. 添加一身份證號(hào)字段,設(shè)置其惟一性.(注: 操作前應(yīng)刪除表中的所有記錄)Alter table student add id char(18) unique (id)3. 將“sc”表的“

2、sno”和“cno”設(shè)置為主鍵：alter table sc add constraint PK_SnoCno primary key (sno,cno)2）域完整性4. 將“ssex”字段設(shè)置為只能取“男”，“女”兩值：alter table student add constraint CK_Sex check (ssex in (男 ,女)5. 設(shè)置學(xué)號(hào)字段只能輸入數(shù)字:alter table student add constraint CK_Sno_Format check (sno like0-90-90-90-90-9)6. 設(shè)置身份證號(hào)的輸入格式:alter table stu

3、dent add constraint CK_ID_Format check (id like0-90-90-90-90-90-91-20-90-90-90-10-90-30-90-90-90-9_) OR (idlike 0-90-90-90-90-90-90-90-90-10-90-30-90-90-90-9)7. 設(shè)置18 位身份證號(hào)的第7 位到第10 位為合法的年份(1900-2050)alter table student add constraint CK_ID_Format2 check (len(id)=18 and( (convert(smallint,substring(i

4、d,7,4) )=1900) and(convert(smallint,substring(id,7,4) )=22 or ssex=女 andsage=20 )3）參照完整性9. 將“student”表和“sc”表中的“sno”字段設(shè)為參照:alter table sc add constraint FP_sno foreign key (sno) references student(sno )完整性驗(yàn)證1 實(shí)體完整性： 在“student”表數(shù)據(jù)瀏覽可視化界面中輸入學(xué)號(hào)相同的兩條記錄將會(huì)出現(xiàn)錯(cuò)誤或者在命令窗口輸入下面兩條命令也會(huì)出現(xiàn)錯(cuò)誤提示：insert into student val

5、ues(95003,張三,男,24,CSinsert into student values(95001,李四,女,21,CS下面的語(yǔ)句用來(lái)驗(yàn)證“sc”表中的實(shí)體完整性：insert into sc values(95002, 10,65)insert into sc values(95002, 10,90)2. 域完整性:使用下面的語(yǔ)句驗(yàn)證“ssex”字段的域完整性：insert into student values(95009,張勻,大,20,CS,3參照完整性：使用下

6、面的語(yǔ)句“驗(yàn)證”sc 表中的“sno”字段的域完整性(假設(shè)student 表中沒(méi)有學(xué)號(hào)為“95998”的學(xué)生記錄)：insert into sc values(98998, 10,98)四、實(shí)驗(yàn)任務(wù):1、建立課程的實(shí)體完整性, 和課程號(hào)cno 的參照完整性;2、對(duì)HRM 數(shù)據(jù)庫(kù)，練習(xí)建立三個(gè)表的主外鍵約束、唯一約束、取空值約束、用戶(hù)自定義的約束（參考HRM 數(shù)據(jù)庫(kù)表定義圖中說(shuō)明）；主鍵約束：Departments:altertabledepartmentsaddconstraintFK_DEPARTMENTIDprimarykey(departmentid)Employee:altertabl

7、eemployeeaddconstraintFK_EMPLOYEEIDprimarykey(employeeid)外鍵約束:Salary:altertablesalaryaddconstraintFK_EmployeeIDforeignkey(EmployeeID)referencesEmployee(Employeeid)Employee:altertableemployeeaddconstraintFK_EMPLOYEE_DEPARTMENTforeignkey(DepartmentID)referencesDepartments(DepartmentID)唯一約束：Departments

8、：altertableDepartmentsaddconstraintUNIQUE_DepartmentUNIQUE(DepartmentID)Employee:altertableEmployeeaddconstraintUNIQUE_EmployeeUNIQUE(DepartmentID)Salary:altertableSalaryaddconstraintUNIQUE_SalaryUNIQUE(EmployeeID)非空約束：Employee：altertableEmployeealtercolumnEmployeeIDchar(6)notnullaltertableEmployeea

9、ltercolumnNamechar(10)notnullaltertableEmployeealtercolumnBirthdayDatetimenotnullaltertableEmployeealtercolumnSexBitnotnullaltertableEmployeealtercolumnDepartmentIDchar(3)notnull表Departments:altertableDepartmentsaltercolumnDepartmentIDchar(3)notnull表Salary:altertableSalaryaltercolumnEmployeeIDchar(6)notnullaltertableSalaryaltercolumnIncomeFloat(8)notnullaltertableSalaryaltercolumnOutComeFloat(8)notnull用戶(hù)自定義約束：altertableEmpl