索引和数据完整性实验报告

实验7 索引和数据完整性1、目的与要求（1）掌握索引的使用方法（2）掌握数据完整性的实现方法2、实验内容（1）建立索引（2）数据完整性3、实验步骤（1）建立索引①对yggl数据库的employees表中的departmentid列建立索引。

Use ygglIf exists(select name from sysindexes where name=’depart_ind’Drop index employees.depart_ind)GoCreate index depart_ind on employees(departmentid)②对pxscj数据库的kcb的课程号列建立索引。

（唯一聚集索引）Use pxscjIf exists(select name from sysindexes where name=’kc_id_ind’)Drop index kc_id_indGoCreate unique clustered index kc_in_ind on kcb(课程号)（2）数据完整性①建立一个规则对象，输入4个数字，每一位的范围分别是[0-3][0-9][0-6][0-9],然后把它绑定到book表的book_id字段上，再解除规则，最后删除规则。

Create table book(Book_id char(6) not null primary key,Name varchar(20) not null,Hire_date datetime not null,Cost int check(cost>=0 and cost<=500) null)GoCreate default today as getdate()GoExec sp_binddefault ‘today’,’book.[hire_date]’Go②创建一个表employees5,只含employeeid,name,sex和education列。

将name设为主键，作为列name的约束。

对employeeid列进行unique约束，并作为表的约束。

create table employees5(employeeid char(6)not null,name char(10)not null primary key,sex tinyint,education char(4),constraint uk_id unique(employeeid))③删除上例中创建的unique约束。

alter table employees5drop constraint uk_id④创建新表student,只考虑“号码”和“性别”两列，性别只能包含男或女。

create table student(号码char(6)not null,性别char(2)not null check(性别in('男','女')))⑤创建新表salary2,结构与salary相同，但salary2表不允许outcome列大于income列。

create table salary2(employeeid char(6)not null,income float not null,outcome float not null,check(income>=outcome))⑥对yggl数据库中的employees表进行修改，为其增加“departmentid”字段的check约束。

alter table employeesadd constraint depart check(departmentid>=1 and departmentid<=5 )⑦创建一个规则对象，用以限制输入到该规则所绑定的列中的值只能是该规则中列出的值。

create rule list_ruleas @list in('财务部','研发部','人力资源部','销售部')goexec sp_bindrule'list_rule','departments.departmentname'go⑧创建一个表salary3，要求所有salary3表上employeeid列的值都要出现在salary表中，利用参照完整性约束实现，要求当删除或修改salary表上的employeeid列时，salary3表中的employeeid值也会随之变化。

create table salary3(employeeid char(6)not null primary key,income float not null,outcome float not null,foreign key(employeeid)references salary(employeeid)on update cascadeon delete cascade)4、思考与练习⑴使用alter table语句为表employees添加一个新列address,并为该列定义unique约束。

alter table employees5add address varchar(40)constraint addr_uk unique nonclustered(address)(在修改前，employees5表中不能有记录，否则会报错，因为address列出现为null的重复值。

)⑵创建一个表employees6,只考虑“学号”和“出生日期”两列,出生日期必须晚于1980-01-01。

create table employees6(学号char(6)not null,出生日期datetime not null check(出生日期>'1980-01-01'))⑶建立一个规则对象，限制值在0~20，然后把它绑定到employees表的workyear字段上。

create rule year_ruleas @range>=0 and @range<=20goexec sp_bindrule'year_rule','employees.workyear'go(若改为@range like [0-20] 对不对?)⑷删除上述建立的规则对象。

exec sp_unbindrule'employees.workyear'godrop rule year_rulego⑸创建完salary3表后，初始化该表的数据与salary表相同。

删除salary表中一行数据，再看看salary3表的内容，会发生什么情况?(略)⑹使用alter table语句向salary表中的employeeid列上添加一个外键，要求当employees表中要删除或修改与employeeid值有关的行时，检查salary表有没有与该employeeid 值相关的记录，如果存在，则拒绝更新employees表。

5、实验小结实验8 存储过程和触发器1、目的与要求（1）掌握存储过程的使用方法（2）掌握触发器的使用方法（3）了解inserted逻辑表和deleted逻辑表的使用2、实验内容⑴创建存储过程，使用employees表中的员工人数来初始化一个局部变量，并调用这个存储过程。

⑵创建存储过程，比较两个员工的实际收入，若前者比后者高就输出0，否则输出1。

⑶创建触发器对于YGGL数据库，表employees的departmentid列与表departments的departmentid 列应满足参照完整性规则，即：①向employees表添加1条记录时，该记录的departmentid值在departments表中应存在。

②修改departments表的departmentid字段值时，该字段在employees表中的对应值也相应修改。

③删除departments表中1条记录时，该记录departmentid字段值在employees表中对应的记录也应删除。

3、实验步骤⑴create procedure test @number1 int outputasbegindeclare @number2 intset @number2=(select count(*)from employees)set @number1=@number2enddeclare @num intexec test @num outputselect @num(2)create procedure compa @id1 char(6),@id2 char(6),@bj int output asbegindeclare @sr1 float,@sr2 floatselect @sr1=income-outcome from salary where employeeid=@id1select @sr2=income-outcome from salary where employeeid=@id2if @sr1>@sr2set @bj=0elseset @bj=1enddeclare @bj intexec compa '000001','108991',@bj output select @bj⑶①use ygglgocreate trigger employeesins on dbo.employeesfor insert,updateasbeginif((select ins.departmentid from inserted ins)not in (select departmentid from departments))RollbackEnd②Use ygglGoCreate trigger departmentsupdate on dbo.departmentsFor updateAsBeginupdate employeesset departmentid=(select departmentid from inserted) Where departmentid=(select departmentid from deleted) EndGo③Use ygglGoCreate trigger departmentsdelete on dbo.departmentsFor deleteAsBeginDelete from employeesWhere departmentid=(select departmentid from deleted) EndGo4、思考与练习⑴创建添加职员记录的存储过程employeeaddUse ygglGoCreate procedure employeeadd(@employeeid char(6),@name char(10),@education char(4),@birthday datetime,@workyear tinyint,@sex bit,@address char(40),@phonenumber char(12),@departmentid char(3)) AsBeginInsert into employeesValues(@employeeid,@name,@education,@birthday,@workyear,@sex,@address,@phon enumber,@departmentid)EndReturnGouse ygglexec employeeadd '990230','刘朝','本科','840909',2,1,'武汉小洪山号','85465213','3'Go⑵创建修改职员记录的存储过程employeeupdateUse ygglGoCreate procedure employeeupdate(@empid char(6), @employeeid char(6),@name char(10),@education char(4),@birthday datetime,@workyear tinyint,@sex bit,@address char(40),@phonenumberchar(12),@departmentid char(3))AsBeginUpdate employeesSet employeeid=@employeeid,Name=@name,Education=@education,Birthday=@birthday,Workyear=@workyear,Sex=@sex,Address=@address,Phonenumber=@phonenumber,Departmentid=@departmentidWhere employeeid=@empidEndReturnuse ygglexec employeeupdate '990230','990232','刘平','本科','840909',2,1,'武汉小洪山号','85465213','2'go⑶创建删除职员记录的存储过程employeedeleteUse ygglGoCreate procedure employeedelete(@employeeid char(6))AsBeginDelete from employeesWhere employeeid=@employeeidEndReturnGouse ygglexec employeedelete '990232'go⑷创建存储过程，要求当一个员工的工作年份大于6年时将其转到经理办公室工作。