使用SQL对数据进行完整性控制

实验四索引、数据完整性与安全性一、实验目的(1) 掌握利用SQL Server Management Studio和SQL语言建立、删除索引的方法；(2) 掌握利用SQL Server Management Studio和SQL语言实现数据完整性的方法；(3) 掌握在SQL Server Management Studio中实现数据安全性管理的方法。

二、实验原理1.索引在关系型数据库中，索引是一种可以加快数据检索的数据库结构。

SQL Server系统中主要有两种类型的索引，即聚集索引、非聚集索引。

（1）聚集索引聚集索引定义了数据在表中存储的物理顺序。

一个表只能定义一个聚集索引。

（2）非聚集索引非聚集索引并不存储表数据本身。

相反，非聚集索引只存储指向表数据的指针，该指针作为索引键的一部分，因此，在一个表中同时可以存在多个非聚集索引。

（3）利用SQL命令建立索引简化语法格式：CREATE [UNIQUE] [CLUSTERED|NONCLUSTERED]INDEX index_name ON {table|view}(column|ASC|DESC][,…n])其中：UNIQUE。

可选。

该选项用于通知SQL Server索引中列出的列的值是每行唯一的。

如果试图插入重复的行，则该选项会强制SQL Server返回一个错误信息。

CLUSTERED或NONCLUSTERED。

可选。

如果这两个选项都没有被明确列出，则默认将索引创建为NONCLUSTERED（非聚集索引）。

（4）通过SQL命令删除索引语法格式：DROP INDEX ‘table.index|view.index’[,…n]2.表主键和UNIQUE约束表主键通过表数据中一个列或者多个列组合的数据来唯一标识表中的每一行数据。

即表主键就是用来约束数据表中不能存在相同的两行数据。

在SQL Server系统中，定义表的主键可以在创建表的同时定义，也可以给已有的表添加主键。

使用SQL对数据进行完整性控制（3类完整性、CHECK短语、CONSTRAIN子句、触发器）。

用实验证实，当操作违反了完整性约束条件时，系统是如何处理的。

根据以下要求认真填写实验报告，记录所有的实验用例。

3类完整性
1.实体完整性：
（列级约束条件）
当操作违反了完整性约束条件时：
检查主码的各个属性是否为空，只要有一个为空就拒绝插入；
检查主码值是否唯一，如果不唯一则拒绝插入；
2.参照完整性:
当操作违反了完整性约束条件时：
①sc表中增加一个元组，该元组的sno属性值在表student中找不到一个元组，其sno属性值与之相等；（系统拒绝）
②sc表中修改一个元组，修改后该元组的sno属性值在表student中找不到一个元组，其sno 属性值与之相等；（系统拒绝）
③从student表中删除一个元组，造成sc表中某些元组的sno属性值在表student中找不到一个元组，其sno属性值与之相等；（系统拒绝/级联删除/设置为空值）
④从student表中修改一个元组，造成sc表中某些元组的sno属性值在表student中找不到一个元组，其sno属性值与之相等；（系统拒绝/级联删除/设置为空值）
3.用户定义的完整性：
列值非空、列值唯一、检查列值是否满足一个条件表达式（check短语）
当往表中插入元组或修改属性的值时，关系数据库管理系统将检查属性上的约束条件是否被满足，如果不满足则操作被拒绝执行：
Check语句并没有起约束作用：（在MySQL中check子句会被分析，但是是忽略的）
CONSTRAIN子句
触发器。

学生学号实验课成绩武汉理工大学学生实验报告书课程名称数据库系统概论开课学院计算机科学与技术学院指导老师学生姓名专业班级2011 —2012 学年第二学期实验课程名称：数据库系统概论实验项目名称关系代数、ALPHA语言、QBE语言查询实验成绩实验者专业班级组别同组者实验日期年月日第一部分：实验分析与设计（可加页）一、实验内容描述（问题域描述）设有一个SPJ数据库，包括S,P,J,SPJ 4个关系模式：S(SNO,SNAME,STATUS,CITY）P(PNO,PNAME,COLOR,WEIGHT)J(JNO,JNAME,CITY)SPJ(SNO,PNO,JNO,QTY)试着用关系代数、ALPHA语言、QBE语言完成如下查询：1 求供应工程J1零件的供应商号码SNO；2 求供应工程J1零件P1的供应商号码SNO；3 求供应工程J1零件为红色的供应商号码SNO；4 求没有使用天津供应商生产的红色零件的工程号JNO；5 求至少用了供应商S1所供应的全部零件的工程号JNO。

二、实验基本原理与设计（包括实验方案设计，实验手段的确定，试验步骤等，用硬件逻辑或者算法描述）一般关系运算：1.选择：它是在关系R中选择满足给定条件的诸元组，记作σF（R）={t|t∈R∧F(t)=’真’}2.投影：是从R中选择出若干属性列组成新的关系，记作πA（R）{t【A】| t∈R}3.连接 :是从两个关系的笛卡尔积中选取属性间满足一定条件的元组。

4.除运算：R与S的除运算得到一个新的关系P（X），P是R中满足下列条件的元组在X属性列上得投影：元组在X上分量值X的象集Ys包含S在Y上得投影的集合。

三、主要仪器设备及耗材本次实验为理论性实验，只需要对关系代数有一定了解并能写出查询语句集合，不需要什么试验设备。

第二部分：实验调试与结果分析（可加页）一、调试过程（包括调试方法描述、实验数据记录，实验现象记录，实验过程发现的问题等）查询资料，对自己写的查询语句进行验证，仔细检查是否正确。

实验八数据的安全性、完整性及权限控制一、实验目的加深对数据库安全性和完整性知识的理解，了解SQL Server的数据库安全性和完整性控制机制，掌握SQL Server中用户、角色及操作权限的创建和管理方法。

二、实验内容及步骤1.通过系统管理平台，将图书-读者数据库和学生-课程数据库附加进来。

操作方法：启动SQL Server2005系统后，在“对象资源管理器”中，选中“数据库”，鼠标右键，选择“附加”分别将图书-读者数据库和学生-课程数据库附加到当前数据库中。

2.将SQL服务器设置为“SQL Server和Windows身份验证模式”。

操作方法：在“对象资源管理器”中，选择SQL服务器，鼠标右键，选择“属性”，在打开的“服务器属性”窗口中，左侧的“选择页”中选择“安全性”，将“服务器身份验证”模式改为“SQL Server和Windows身份验证模式”。

3.新建登录用户名为“login1”，使用SQL Server身份验证。

操作方法：在“对象资源管理器”中，选择“安全性——登录名”，鼠标右键，选择“新建登录名”，在打开的“登录名——新建”窗口中，进行如下操作：左侧的“选择页”中选择“常规”时在右侧，输入登录名为“login1”，选择“SQL Server 身份验证”，输入“密码”及“确认密码”，如将密码设置为“123”，将下面的三个复选框全部取消。

单击“确定”按钮，完成新建登录用户的操作。

退出SQL Server 2005系统。

重新启动SQL Server，在“连接到服务器”对话框中，在“身份验证”中选择“SQL Server身份验证”，在“登录名”中输入“login1”，密码中输入“123”，点击“连接”进行登录。

4.在学生-课程数据库中，新建数据库用户名为“user1”（登录用户名为“login1”），新建数据库角色名为“rose1”。

操作方法：打开SQL Server管理平台，在其“对象资源管理器”面板中依次展开结点“服务器——数据库——安全性——用户”，右击“用户”，在打开的菜单中选择“新建用户”命令，在“用户名”中输入“user1”，单击“登录名”右侧的“”按钮，搜索登录用户名或直接输入用户的登录名“login1”。

sql语句对数据库表进⾏加锁和解锁锁是数据库中的⼀个⾮常重要的概念，它主要⽤于多⽤户环境下保证数据库完整性和⼀致性。

我们知道，多个⽤户能够同时操纵同⼀个数据库中的数据，会发⽣数据不⼀致现象。

即如果没有锁定且多个⽤户同时访问⼀个数据库，则当他们的事务同时使⽤相同的数据时可能会发⽣问题。

这些问题包括：丢失更新、脏读、不可重复读和幻觉读：1．丢失更新：当两个或多个事务选择同⼀⾏，然后基于最初选定的值更新该⾏时，会发⽣丢失更新问题。

每个事务都不知道其它事务的存在。

最后的更新将重写由其它事务所做的更新，这将导致数据丢失。

例如，两个编辑⼈员制作了同⼀⽂档的电⼦复本。

每个编辑⼈员独⽴地更改其复本，然后保存更改后的复本，这样就覆盖了原始⽂档。

最后保存其更改复本的编辑⼈员覆盖了第⼀个编辑⼈员所做的更改。

如果在第⼀个编辑⼈员完成之后第⼆个编辑⼈员才能进⾏更改，则可以避免该问题。

2．脏读脏读就是指当⼀个事务正在访问数据，并且对数据进⾏了修改，⽽这种修改还没有提交到数据库中，这时，另外⼀个事务也访问这个数据，然后使⽤了这个数据。

因为这个数据是还没有提交的数据，那么另外⼀个事务读到的这个数据是脏数据，依据脏数据所做的操作可能是不正确的。

例如，⼀个编辑⼈员正在更改电⼦⽂档。

在更改过程中，另⼀个编辑⼈员复制了该⽂档（该复本包含到⽬前为⽌所做的全部更改）并将其分发给预期的⽤户。

此后，第⼀个编辑⼈员认为⽬前所做的更改是错误的，于是删除了所做的编辑并保存了⽂档。

分发给⽤户的⽂档包含不再存在的编辑内容，并且这些编辑内容应认为从未存在过。

如果在第⼀个编辑⼈员确定最终更改前任何⼈都不能读取更改的⽂档，则可以避免该问题。

3．不可重复读不可重复读是指在⼀个事务内，多次读同⼀数据。

在这个事务还没有结束时，另外⼀个事务也访问该同⼀数据。

那么，在第⼀个事务中的两次读数据之间，由于第⼆个事务的修改，那么第⼀个事务两次读到的的数据可能是不⼀样的。

这样就发⽣了在⼀个事务内两次读到的数据是不⼀样的，因此称为是不可重复读。

实验三、四 创建表及输入数据及完整性约束本实验需要4学时。

一、实验目的要求学生熟练掌握和使用Transact-SQL、SQL Server企业管理器创建表、索引和修改表结构，并学会使用SQL Server 查询分析器接收Transact-SQL语句和进行结果分析。

学生熟练掌握使用 SQL、Transact-SQL和SQL Server企业管理器向数据库输入数据、修改数据和删除数据的操作。

二、实验内容1 创建表、确定表的主码和约束条件。

为主码建索引。

2 查看和修改表结构。

3 输入数据、修改数据和删除数据三、实验步骤（1）通过企业管理器，在建好的图书借阅数据库中建立图书、读者和借阅3个表，其结构为；图书（书号,类别,出版社,作者,书名,定价,作者）.读者(编号，姓名,单位,性别,电话)．借阅(书号,读者编号,借阅日期)要求为属性选择合适的数据类型，定义每个表的主码.是否允许空值和默认值等列级数据约束。

（2）在企业管理器中建立图书、读者和借阅3个表的表级约束．每个表的主码约束．借阅表与图书表间、借阅表与读者表之间的外码约束，要求按语义先确定外码约束表达式．再通过操作予以实现．实现借阅表的书号和读者编号的惟一性约束：实现读者性别只能是“男”或“女”的Check(检查)约束。

（3）通过企业管理器，在图书借阅数据库的图书、读者和借阅3个表中各输入10条记录。

要求记录不仅满足数据约束要求．还要有表间关联的记录。

（4）通过企业管理器实现对图书借阅数据库的图书、读者和借阅3个表中数据的插入。

删除和修改操作。

（5）通过企业管理器实现对学生选课库的数据增加、数据删除和数据修改操作、要求学生、课程和选课表中各有10条以上的记录。

四、实验方法1 新建表在 SQL Serve 2000的数据库中，文件夹是按数据库对象的类型建立的．文件夹名是该数据库对象名。

当在企业管理器中选择服务器和数据库文件夹，并打开已定义好的图书．读者数据库后，会发现它自动设置了关系图、表、视图、存储过程、用户、角色、规则、默认等文件夹。