北邮数据库实验五 数据库完整性与安全性实验

实验五数据库完整性与安全性实验

1、实验目的

1.通过对完整性规则的定义实现，熟悉了解kingbase中完整性保证的规则和实现方法，加

深对数据完整性的理解。

2.通过对安全性相关内容的定义，熟悉了解kingbase中安全性的内容和实现方法，加深对

数据库安全性的理解

2、实验环境

操作系统：Microsoft Windows 7旗舰版（32位）。

数据库版本：MySQL 6.2

3、实验内容

完整性实验：

（1）分别定义学生数据库中各基表的主键、外键，实现实体完整性约束和参照完整性约束；

在create table 时已经定义了各个基表的主键。因此，首先对每个基表删除主键，再添加主键。实现实体完整性约束。如下图。

删除主键：

添加主键：

sc表中的cno和sno分别是course表和student表的外关键字。下图为给sc表添加student和course的外键，实现参照完整性约束。

alter table sc add constraint fk_student foreign key(sno) references student(sno);

alter table sc add constraint fk_course foreign key(cno) references course(cno);

主键和外键均添加成功，输入show create table 命令来查看各基表信息。

（2）分别向学生表、课程表插入具有相同学号和相同课程编号的学生数据和课程数据，验证其实体完整性约束；

向课程表中插入课程号为C01的课程。

向学生表中插入学号为30201的学生。

由上面两个实验来看，分别对student和course表插入具有相同学号和相同课程号的学生信息和课程信息，都显示sql语句错误信息为：Duplicate entry *** for key ‘PRIMARY’。验证了实体完整性约束。

（3）向学生选课表中插入一条数据，课程编号是课程表中没有的，验证参照完整性约束；

插入成绩信息是学号为31428（student中存在），课程号为C07（course中不存在）的学生成绩信息。受参照完整性约束，插入失败。

（4）删除学生表中的所有数据，验证参照完整性约束；

因为sc表中的sno是student表的外键，删除学生表中的所有数据违背了参照完整性约束，所以无法删除。

（5）定义存储过程，完成查询某个学生的选课情况，并执行。

本次实验设计的存储过程完成的是查询某个学生的选课情况。参数指定为sno学号。

存储过程是一组为了完成特定功能的SQL 语句集，存储在数据库中经过第一次编译后再次调用不需要再次编译，用户通过指定存储过程的名字并给出参数来执行它。

因为在存储过程中会用到结束符‘；’，所以首先用delimiter修改结束符为//，创建

存储过程的sql语句为：

create procedure search_sc（num char（6））

begin

select sno，cno from sc where sno=num；

end

其中定义了一个读入的参数为num，用来读入所要查询的学生的学号。

创建成功。

调用该存储过程，查看学号为‘31428’的学生的选课信息。

（6）定义触发器，当向学生表插入新的一条记录时，将所有学生出生日期加1；并对其进行测试。

触发器的功能是监视某种情况，并触发某种操作。创建触发器的语句为：create trigger update_bdate

after insert on student

for each row

begin

update student set bdate=bdate+1;

end

表示当对student表进行插入操作后，对student表进行出生年月日加1的sql语句的执行。

向student里插入一条新纪录，触发触发器update_bdate的操作，但是出现了下述

错误信息：it is already used by statement which invoked this stored function/trigger。

经过了解，mysql中需要复制重建一个新的table，然后通过对旧table的操作来触发对新table 的操作。

create table new_student select * from student; //建立新表

create trigger t_bdate //建立触发器

before insert on student for each row

update new_student

set bdate=date_add(bdate,interval 1 day)

insert into student //插入查询语句

values('31011','lihua','男','1991-01-01 00:00:00','计算机','3147')

查看更新后的student表和new_student表如下：student表中的数据没有发生改变，只是插入了一条新的学生信息，而new_student表中所有学生信息的出生日期都为1月2号，比student表中的日期加1，但是没有新插入的学生信息。

安全性实验内容

（1）定义一新的登陆帐号、数据库用户，并授予其访问学生数据库的读权限；

创建新的登陆账号、数据库用户有两种方式，分别为grant 授予权限时创建和create user。下面分别用两种方式创建用户。

grant usage on *.* to 'lqy1'@'localhost' identified by '123456' with grant option;

with grant option 是使lqy1用户拥有授予权限的权利，是一句表示权限传递的语句。