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

重 庆 大 学学 生 实 验 报 告实验课程名称 数据库原理与设计 开课实验室 DS1501 学 院 软件学院 年级 2013级 专业班 学 生 姓 名 学 号 开 课 时 间 至 学年第 1 学期总 成 绩教师签名软件学院 制 数据库原理与设计 实验报告 开课实验室： 201年 月日学院软件学院年级、专业、班姓名成绩课程名称数据库原理与设计实验项目名 称数据完整性控制数据安全性控制指导教师一、实验目的重点掌握如下知识点的相关操作：实验6、数据完整性控制实验9、数据库安全性控制二、实验环境PC一台，操作系统为win8.1SQLServer2008 三、实验内容主要实验内容如下：实验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 checkadd 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中插入性别为“中”的记录，执行失败。如图： 年龄必须在0100之间。 输入T-SQL语句如下： use Library alter table Reader with checkadd constraint ck_Rage check (Rage0 and Rage0 ) 执行成功，验证结果如下： 测试，试图向Book表中插入价格为0的记录，执行失败。如图： 借阅日期默认为当前日期。 输入T-SQL语句如下： use Library alter table Borrow add constraint ck_BorrowDate default(GetDate()for BorrowDate执行成功，验证结果如下：测试，试图向Borrow表中插入BorrowDate为NULL的记录，BorrowDate默认被设置为当前时间。如图： use Libraryinsert 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.创建一个表BOOKCount，用来记录每本书被借阅的次数。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+1where Bno=Bnol 向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,12345l 验证可以成功登录。 l 使用如下T-SQL语句为登陆账号SQLTeacher创建数据库用户： use Library gosp_grantdbaccess SQLTeacher,SQLTeacherl 执行成功，可以正常展开数据库，验证已成功添加用户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 gosp_grantdbaccess SQLAdmin,SQLAdminl 执行成功，可以正常展开数据库，验证已成功添加用户SQLTeacher： l 对用户SQLTeacher授予查询数据库Library的权限：l 使用以下T-SQL语句对Reader表授予插入、修改和删除的操作： use Library go grant insert,update,delete on Readerto SQLAdmin l 执行成功，为了验证，输入以下T-SQL语句视图插入一行数据： use Libraryinsert 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,SQLTeacherl 验证，已成功创建该角色。 五、 出现问题和解决方案（列出遇到的问题和解决方案，列出没有解决的问题）实验九中的习题3执行出现问题，描述如下： 新建查询对BOOK的列执行相应的操作时发生问题： 本应该是这样的： use Library grant insert,update,delete on Book(Bauthor) to SQLTeacher 但我认为不能单独对表中一列信息执行插入或删除操作，只能修改其中内容。 而事实证明也确实无法执行： 若只授予更新权限，则顺利执行：