重庆大学数据完整性控制第四次实验讲述.docx
《重庆大学数据完整性控制第四次实验讲述.docx》由会员分享,可在线阅读,更多相关《重庆大学数据完整性控制第四次实验讲述.docx(25页珍藏版)》请在冰豆网上搜索。
重庆大学数据完整性控制第四次实验讲述
重庆大学
学生实验报告
实验课程名称数据库原理与设计
开课实验室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中的表实现实体完整性并验证。
1 给各个表设置主键
对Book表填加主键,输入T-SQL语句如下:
useLibrary
altertableBook
addconstraintpk_bnoprimarykey(Bno)
执行成功,结果如下:
对Reader表填加主键,输入T-SQL语句如下:
useLibrary
altertableReader
addconstraintPK_Readerprimarykey(Rno)
执行成功,结果如下:
对Borrow表填加主键,输入T-SQL语句如下:
uselibrary
altertableBorrow
addconstraintab_Rnoprimarykey(Rno,Bno)
2 给各个表创建UNIQUE约束
在Book表中对书名创建UNIQUE约束,输入T-SQL语句如下:
useLibrary
altertableBook
addconstraintBook_uniunique(Btitle)
执行成功,结果如下:
在Reader表中对读者名创建UNIQUE约束,输入T-SQL语句如下:
useLibrary
altertableReader
addconstraintReader_uniunique(Rname)
执行成功,结果如下:
习题2.实现各个表之间的参照完整性并验证。
为Borrow表添加外键,输入T-SQL语句如下:
useLibrary
altertableBorrow
addconstraintfk_borrow_book
foreignkey(bno)referencesBook(pk_bno)
altertableBorrow
addconstraintfk_borrow_reader
foreignkey(rno)referencesReader(Rno)
执行成功,验证结果如下:
习题3.实现下列约束并验证。
1 姓名不能为空值。
输入T-SQL语句如下:
useLibrary
altertableReaderwithcheck
addconstraintck_rnamecheck((Rname)isnotnull)
执行成功,验证结果如下:
试图向Reader表中插入姓名为空的一行记录,失败,结果如图:
2 性别应该为“男”或“女”。
输入T-SQL语句如下:
useLibrary
altertableReaderwithcheck
addconstraintck_Rsexcheck(Rsexin('男','女'))
执行成功,验证结果如下:
测试,试图向Reader中插入性别为“中”的记录,执行失败。
如图:
3 年龄必须在0~100之间。
输入T-SQL语句如下:
useLibrary
altertableReaderwithcheck
addconstraintck_Ragecheck(Rage>0andRage<100)
执行成功,验证结果如下:
测试,试图向Reader中插入年龄为200的记录,执行失败。
如图:
4 价格必须大于0。
输入T-SQL语句如下:
useLibrary
altertableBookwithcheck
addconstraintck_Bpricecheck(Bprice>0)
执行成功,验证结果如下:
测试,试图向Book表中插入价格为0的记录,执行失败。
如图:
5 借阅日期默认为当前日期。
输入T-SQL语句如下:
useLibrary
altertableBorrow
addconstraintck_BorrowDatedefault(GetDate())forBorrowDate
执行成功,验证结果如下:
测试,试图向Borrow表中插入BorrowDate为NULL的记录,BorrowDate默认被设置为
当前时间。
如图:
useLibrary
insertintoBorrowvalues('R06','B06','','')
习题4.创建一个触发器,当删除Reader表中读者信息时,同时删除Borrow表中该读者的记录。
输入T-SQL语句如下:
createtriggertrigger_
onReader
insteadof
delete
as
deleteBorrow
whereRnoin(selectRnofromdeleted)
执行成功,验证结果如下:
测试,在读者表中删除R05的信息,经验证Borrow表中记录被级联删除:
习题5.创建一个表BOOK——Count,用来记录每本书被借阅的次数。
●创建该表,并向其中加入数据。
T-SQL语句如下:
useLibrary
createtableBook_Count(
Bnochar(8)notnullprimarykey,
BorrowTimeschar(10))
insertintoBook_Countvalues('B01','3')
insertintoBook_Countvalues('B02','2')
insertintoBook_Countvalues('B03','2')
insertintoBook_Countvalues('B04','0')
insertintoBook_Countvalues('B05','1')
insertintoBook_Countvalues('B06','1')
●创建成功,结果如下:
●创建两个触发器(BOOK中插入新书时更新Book_Count&Borrow中插入更新时更新Book_Count),T-SQL语句如下:
useLibrary
go
createtriggertg_insertonBook
forinsert
as
declare@Bno1char(8)
select@Bno1=pk_bnofrominserted
insertintoBook_Countvalues(@Bno1,'0')
createtriggertg_countonBorrow
forinsert
as
declare@Bnochar(8)
select@Bno=Bnofrominserted
updateBook_CountsetBorrowTimes=BorrowTimes+1
whereBno=@Bno
●向Borrow表中再插入3条数据,并查询Book_Count表。
执行成功,结果如下:
useLibrary
insertintoBorrowvalues('R02','B04','','')
insertintoBorrowvalues('R02','B05','','')
insertintoBorrowvalues('R02','B06','','')
●向Book表中插入一条新记录,同时查看Book_Count中是否有更新且初始借阅次数置为0,
执行成功,结果如下:
实验九、数据库安全性控制
习题1.创建一个Windows认证的登录账户newreader,只允许该用户对数据库Library查询
●添加名为newreader的新账户,并对其授予登录权限,设置只能对Library进行访问。
●验证可用WinTest账户登录成功。
●创建数据库用户newreader:
●在对象资源管理器中验证,已成功创建名为nesreader的用户:
●使用newreader账户登录,可以展开Library数据库,说明创建用户成功。
●向用户newreader授予查询Library数据库权限:
●使用账户newreader登录,验证可以查询Library数据库中内容:
习题2.创建一个Windows认证的登陆账户Student,并将其设置为系统管理员账户。
习题3.创建SQLSever认证的登录账户SQLTeacher,并将其设置允许使用Library进行查询,对
表Book的列Bauthor进行插入、修改和删除操作。
●新建查询输入T-SQL语句如下:
useLibrary
go
sp_addlogin'SQLTeacher','12345'
●验证可以成功登录。
●使用如下T-SQL语句为登陆账号SQLTeacher创建数据库用户:
useLibrary
go
sp_grantdbaccess'SQLTeacher','SQLTeacher'
●执行成功,可以正常展开数据库,验证已成功添加用户SQLTeacher:
●对用户SQLTeacher授予查询数据库Library的权限:
●新建查询来对BOOK的列执行相应的操作。
本应该是这样的:
useLibrary
grantinsert,update,delete
onBook(Bauthor)toSQLTeacher
但我认为不能单独对表中一列信息执行插入或删除操作,只能修改其中内容。
而事实证明也确实无法执行:
若只授予更新权限,则顺利执行:
●执行成功,测试更新操作。
习题4.创建SQLSever认证的登陆账户SQLAdmin,并将其设置为允许使用数据库Library进行查
询,对表Reader进行插入、修改和删除操作。
●创建SQLServer登陆账户并成功登陆:
●使用如下T-SQL语句为登陆账号SQLTeacher创建数据库用户:
useLibrary
go
sp_grantdbaccess'SQLAdmin','SQLAdmin'
●执行成功,可以正常展开数据库,验证已成功添加用户SQLTeacher:
●对用户SQLTeacher授予查询数据库Library的权限:
●使用以下T-SQL语句对Reader表授予插入、修改和删除的操作:
useLibrary
go
grantinsert,update,deleteonReader
toSQLAdmin
●执行成功,为了验证,输入以下T-SQL语句视图插入一行数据:
useLibrary
insertintoReadervalues('R09','何夕','女','22','研究生')
●插入成功,查询结果如下:
习题5.创建一个新角色Newstudent,使其具有对数据库Library进行任何操作的权限,并将上面创
建的用户全部添加到此角色中。
●使用图形界面为newreader创建新角色Newstudent,并将架构设置为db_owner(可执行数据
库所有配置和维护活动)
●或使用如下T-SQL语句将SQLAdmin和SQLTeacher加入该角色:
useLibrary
go
sp_addrolemember'Newstudent','SQLAdmin'
useLibrary
go
sp_addrolemember'Newstudent','SQLTeacher'
●验证,已成功创建该角色。
5、出现问题和解决方案(列出遇到的问题和解决方案,列出没有解决的问题)
实验九中的习题3执行出现问题,描述如下:
新建查询对BOOK的列执行相应的操作时发生问题:
本应该是这样的:
useLibrary
grantinsert,update,delete
onBook(Bauthor)toSQLTeacher
但我认为不能单独对表中一列信息执行插入或删除操作,只能修改其中内容。
而事实证明也确实无法执行:
若只授予更新权限,则顺利执行: