重庆大学数据完整性控制第四次实验.doc

重 庆 大 学

学 生 实 验 报 告

实验课程名称 数据库原理与设计

开课实验室 DS1501

学 院 软件学院 年级 2013级 专业班

学 生 姓 名 学 号

开 课 时 间 至 学年第 1 学期

总 成 绩

教师签名

软件学院 制

《 数据库原理与设计 》实验报告

开课实验室： 201年 　月日
学院
软件学院
年级、专业、班

姓名

成绩

课程
名称
数据库原理与设计
实验项目
名 称
数据完整性控制
数据安全性控制
指导教师

一、实验目的
重点掌握如下知识点的相关操作：
实验6、数据完整性控制
实验9、数据库安全性控制
二、实验环境
PC一台，操作系统为win8.1
SQLServer2008

三、实验内容
主要实验内容如下：
实验6 数据完整性控制
6.1 实体完整性
6.2 参照完整性
6.3 用户定义的完整性
6.4 触发器
实验9 数据库安全性控制
9.1 创建登录账户
9.2 创建数据库用户和角色
9.3 权限管理
9.4 删除数据库用户和登录账户

四、实验过程原始记录(对每个实验内容给出一两个代表性图片)

实验六、数据完整性控制

习题1.对Library中的表实现实体完整性并验证。
①　 给各个表设置主键
对Book表填加主键，输入T-SQL语句如下：
use Library
alter table Book
add constraint pk_bno primary key(Bno)
执行成功，结果如下：

对Reader表填加主键，输入T-SQL语句如下：
use Library
alter table Reader
add constraint PK_Reader primary key(Rno)
执行成功，结果如下：

对Borrow表填加主键，输入T-SQL语句如下：
use library
alter table Borrow
add constraint ab_Rno primary key(Rno,Bno)
②　 给各个表创建UNIQUE约束
在Book表中对书名创建UNIQUE约束，输入T-SQL语句如下：
use Library
alter table Book
add constraint Book_uni unique(Btitle)
执行成功，结果如下：

在Reader表中对读者名创建UNIQUE约束，输入T-SQL语句如下：
use Library
alter table Reader
add constraint Reader_uni unique(Rname)
执行成功，结果如下：

习题2.实现各个表之间的参照完整性并验证。
为Borrow表添加外键，输入T-SQL语句如下：
use Library
alter table Borrow
add constraint fk_borrow_book
foreign key(bno)references Book (pk_bno)
alter table Borrow
add constraint fk_borrow_reader
foreign key(rno)references Reader (Rno)
执行成功，验证结果如下：

习题3.实现下列约束并验证。
①　 姓名不能为空值。
输入T-SQL语句如下：
use Library
alter table Reader with check
add constraint ck_rname check ((Rname)is not null)
执行成功，验证结果如下：

试图向Reader表中插入姓名为空的一行记录，失败，结果如图：

②　 性别应该为“男”或“女”。
输入T-SQL语句如下：
use Library
alter table Reader with check
add constraint ck_Rsex check (Rsex in ('男','女'))
执行成功，验证结果如下：

测试，试图向Reader中插入性别为“中”的记录，执行失败。如图：

③　 年龄必须在0~100之间。
输入T-SQL语句如下：
use Library
alter table Reader with check
add constraint ck_Rage check (Rage>0 and Rage<100)
执行成功，验证结果如下：

测试，试图向Reader中插入年龄为200的记录，执行失败。如图：

④　 价格必须大于0。
输入T-SQL语句如下：
use Library
alter table Book with check
add constraint ck_Bprice check (Bprice>0 )

执行成功，验证结果如下：

测试，试图向Book表中插入价格为0的记录，执行失败。如图：

⑤　 借阅日期默认为当前日期。
输入T-SQL语句如下：
use Library
alter table Borrow
add constraint ck_BorrowDate default(GetDate())for BorrowDate
执行成功，验证结果如下：

测试，试图向Borrow表中插入BorrowDate为NULL的记录，BorrowDate默认被设置为
当前时间。如图：
use Library
insert into Borrow values('R06','B06',' ',' ')

习题4.创建一个触发器，当删除Reader 表中读者信息时，同时删除Borrow表中该读者的记录。
输入T-SQL语句如下：
create trigger trigger_
on Reader
instead of
delete
as
delete Borrow
where Rno in(select Rno from deleted)
执行成功，验证结果如下：

测试，在读者表中删除R05的信息，经验证Borrow表中记录被级联删除：

习题5.创建一个表BOOK——Count，用来记录每本书被借阅的次数。
l 创建该表，并向其中加入数据。T-SQL语句如下：
use Library
create table Book_Count (
Bno char(8) not null primary key,
BorrowTimes char(10))
insert into Book_Count values ('B01','3')
insert into Book_Count values ('B02','2')
insert into Book_Count values ('B03','2')
insert into Book_Count values ('B04','0')
insert into Book_Count values ('B05','1')
insert into Book_Count values ('B06','1')
l 创建成功，结果如下：

l 创建两个触发器（BOOK中插入新书时更新Book_Count & Borrow 中插入更新时更新Book_Count），T-SQL语句如下：
use Library
go
create trigger tg_insert on Book
for insert
as
declare @Bno1 char(8)
select @Bno1=pk_bno from inserted
insert into Book_Count values(@Bno1,'0')

create trigger tg_count on Borrow
for insert
as
declare @Bno char(8)
select @Bno=Bno from inserted
update Book_Count set BorrowTimes=BorrowTimes+1
where Bno=@Bno

l 向Borrow表中再插入3条数据，并查询Book_Count 表。执行成功，结果如下：
use Library
insert into Borrow values('R02','B04','','')
insert into Borrow values('R02','B05','','')
insert into Borrow values('R02','B06','','')

l 向Book表中插入一条新记录，同时查看Book_Count中是否有更新且初始借阅次数置为0，
执行成功，结果如下：

实验九、 数据库安全性控制
习题1.创建一个Windows认证的登录账户newreader，只允许该用户对数据库Library查询
l 添加名为newreader的新账户，并对其授予登录权限，设置只能对Library进行访问。

l 验证可用WinTest账户登录成功。

l 创建数据库用户newreader：

l 在对象资源管理器中验证，已成功创建名为nesreader的用户：

l 使用newreader账户登录，可以展开Library数据库，说明创建用户成功。

l 向用户newreader授予查询Library数据库权限：

l 使用账户newreader登录，验证可以查询Library数据库中内容：

习题2.创建一个Windows认证的登陆账户Student，并将其设置为系统管理员账户。

习题3.创建SQL Sever 认证的登录账户SQL Teacher，并将其设置允许使用Library进行查询，对
表Book的列Bauthor 进行插入、修改和删除操作。
l 新建查询输入T-SQL语句如下：
use Library
go
sp_addlogin 'SQLTeacher','12345'

l 验证可以成功登录。

l 使用如下T-SQL语句为登陆账号SQLTeacher创建数据库用户：
use Library
go
sp_grantdbaccess 'SQLTeacher','SQLTeacher'
l 执行成功，可以正常展开数据库，验证已成功添加用户SQLTeacher：

l 对用户SQLTeacher授予查询数据库Library的权限：

l 新建查询来对BOOK的列执行相应的操作。
本应该是这样的：
use Library
grant insert,update,delete
on Book(Bauthor) to SQLTeacher
但我认为不能单独对表中一列信息执行插入或删除操作，只能修改其中内容。
而事实证明也确实无法执行：

若只授予更新权限，则顺利执行：

l 执行成功，测试更新操作。

习题4.创建SQL Sever认证的登陆账户SQLAdmin，并将其设置为允许使用数据库Library进行查
询，对表Reader进行插入、修改和删除操作。
l 创建SQL Server登陆账户并成功登陆：

l 使用如下T-SQL语句为登陆账号SQLTeacher创建数据库用户：
use Library
go
sp_grantdbaccess 'SQLAdmin','SQLAdmin'
l 执行成功，可以正常展开数据库，验证已成功添加用户SQLTeacher：

l 对用户SQLTeacher授予查询数据库Library的权限：

l 使用以下T-SQL语句对Reader表授予插入、修改和删除的操作：
use Library
go
grant insert,update,delete on Reader
to SQLAdmin

l 执行成功，为了验证，输入以下T-SQL语句视图插入一行数据：
use Library
insert into Reader values('R09','何夕','女','22','研究生')

l 插入成功，查询结果如下：

习题5.创建一个新角色Newstudent，使其具有对数据库Library进行任何操作的权限，并将上面创
建的用户全部添加到此角色中。
l 使用图形界面为newreader创建新角色Newstudent，并将架构设置为db_owner(可执行数据
库所有配置和维护活动)

l 或使用如下T-SQL语句将SQLAdmin和SQLTeacher加入该角色：
use Library
go
sp_addrolemember 'Newstudent','SQLAdmin'
use Library
go
sp_addrolemember 'Newstudent','SQLTeacher'
l 验证，已成功创建该角色。

五、 出现问题和解决方案（列出遇到的问题和解决方案，列出没有解决的问题）
实验九中的习题3执行出现问题，描述如下：
新建查询对BOOK的列执行相应的操作时发生问题：
本应该是这样的：
use Library
grant insert,update,delete
on Book(Bauthor) to SQLTeacher
但我认为不能单独对表中一列信息执行插入或删除操作，只能修改其中内容。
而事实证明也确实无法执行：

若只授予更新权限，则顺利执行：