实验3 数据完整性和安全性管理
3.1 数据完整性管理
一、实验目的
? 掌握域完整性的实现方法。 ? 掌握实体完整性的实现方法。 ? 掌握参照完整性的方法。
二、实验内容
数据库的完整性设置。
三、实验步骤
? 可视化界面的操作方法： ? 实体完整性
1．将student表的“sno”字段设为主键：在表设计界面中，单击左边的行选定块，选定“sno”字段，单击工具按钮
设置主键。如图1所示：
图1
1． 将“sc”表的“sno”和“cno”设置为主键：在表设计界面中，单击并按住Ctrl键
拖动左边的行选定块，选定sno和cno字段，单击工具按钮所示：
设置主键。如图2
图2
? 域完整性 3. 将“ssex”字段设置为只能取“男”，“女”两值：在表设计界面，点击鼠标右键——CHECK约束,——添加约束，添加CK_student_sex名称，然后在约束表达式框中输入“ ssex in ('男','女') ”。如图3所示。
图3
? 参照完整性
4. 将“student”表和“sc”表中的“sno”字段设为参照: 打开“sc”表的设计界面, 点击工具栏按钮
, 在弹出的属性(properties)对话框中点击“新建”按钮，在“主键表(Primary key
table)”下拉框中选择“student”表，在其下的字段选择框中选择“sno”，在“外键表(Foreign
key table)”下拉框中选择“sc”表，在其下的字段选择框中选择“sno”，单击关闭即可。见图4。
图4
? 命令方式操作方法：
? 实体完整性
1．将“student”表的“sno”字段设为主键： 当“student”表已存在则执行：
alter table student add constraint pk_sno primary key (sno) 当“student”表不存在则执行:
Create table student(sno CHAR(5) primary key ,
sname CHAR(10) NOT NULL, ssex CHAR(2), sage int,
sdept CHAR(4))
注:可用命令“drop table student”删除“student”表
2. 添加一身份证号字段,设置其惟一性.(注: 操作前应删除表中的所有记录) Alter table student add id char(18) unique (id) 3. 将“sc”表的“sno”和“cno”设置为主键： 当“sc”表已存在则执行：
alter table sc add constraint PK_SnoCno primary key (sno,cno) 当“sc”表不存在则执行：
Create table sc(sno CHAR(5),
cno CHAR(2),
grade INT NULL,
constraint PK_SnoCno primary key (sno,cno))
? 域完整性
4. 将“ssex”字段设置为只能取“男”，“女”两值： 当“student”表已存在则执行：
alter table student add constraint CK_Sex check (ssex in ('男' ,'女')) 当“student”表不存在则执行：
Create table student(sno CHAR(5) primary key ,
sname CHAR(10),
ssex CHAR(2) check (ssex in ('男' ,'女')) , sage int, sdept CHAR(4))
5. 设置学号字段只能输入数字:
alter table student add constraint CK_Sno_Format check (sno like '[0-9][0-9][0-9][0-9][0-9]')
6. 设置身份证号的输入格式:
alter table student add constraint CK_ID_Format check ((id like
'[0-9][0-9][0-9][0-9][0-9][0-9][1-2][0-9][0-9][0-9][0-1][0-9][0-3][0-9][0-9][0-9][0-9]_') OR (id like '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-1][0-9][0-3][0-9][0-9][0-9][0-9]'))
7. 设置18位身份证号的第7位到第10位为合法的年份(1900-2050) alter table student add constraint CK_ID_Format2 check (len(id)=18 and
( (convert(smallint,substring(id,7,4) )>=1900) and(convert(smallint,substring(id,7,4) )<=2050)) ) 6. 设置男生的年龄必须大于22, 女生的年龄必须大于20.
Alter table student add constraint CK_age check (ssex='男' and sage>=22 or ssex='女' and sage>=20 )
? 参照完整性
9. 将“student”表和“sc”表中的“sno”字段设为参照: 当“sc”表已存在则执行：
alter table sc add constraint FP_sno foreign key (sno) references student(sno )
当“sc”表不存在则执行：
Create table sc(sno CHAR(5) constraint FP_sno
foreign key references student(sno), cno CHAR(2),grade INT NULL,
constraint PK_SnoCno primary key (sno,cno) )
? 完整性验证
1． 实体完整性： 在“student”表数据浏览可视化界面中输入学号相同的两条记录将会出现错误如图5所示：
图5
或者在命令窗口输入下面两条命令也会出现错误提示：
insert into student values('95003','张三','男',24,'CS','42222919901012903X')
insert into student values('95001','李四','女',21,'CS','422229199510129031')