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

实验7 索引和数据完整性

1、目的与要求

（1）掌握索引的使用方法

（2）掌握数据完整性的实现方法

2、实验内容

（1）建立索引

（2）数据完整性

3、实验步骤

（1）建立索引

①对yggl数据库的employees表中的departmentid列建立索引。

Use yggl

If exists(select name from sysindexes where name=’depart_ind’

Drop index employees.depart_ind)

Go

Create index depart_ind on employees(departmentid)

②对pxscj数据库的kcb的课程号列建立索引。（唯一聚集索引）

Use pxscj

If exists(select name from sysindexes where name=’kc_id_ind’)

Drop index kc_id_ind

Go

Create 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

)

Go

Create default today as getdate()

Go

Exec 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 employees5

drop 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 employees

add constraint depart check(departmentid>=1 and departmentid<=5 )

⑦创建一个规则对象，用以限制输入到该规则所绑定的列中的值只能是该规则中列出的值。

create rule list_rule

as @list in('财务部','研发部','人力资源部','销售部')

go

exec 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 cascade

on delete cascade

)

4、思考与练习

⑴使用alter table语句为表employees添加一个新列address,并为该列定义unique约束。

alter table employees5

add 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_rule

as @range>=0 and @range<=20

go

exec sp_bindrule'year_rule','employees.workyear'

go

(若改为@range like [0-20] 对不对?)

⑷删除上述建立的规则对象。

exec sp_unbindrule'employees.workyear'

go

drop rule year_rule

go

⑸创建完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 output

as

begin

declare @number2 int

set @number2=(select count(*)from employees)

set @number1=@number2

end

declare @num int

exec test @num output

select @num

(2)

create procedure compa @id1 char(6),@id2 char(6),@bj int output as

begin

declare @sr1 float,@sr2 float

select @sr1=income-outcome from salary where employeeid=@id1

select @sr2=income-outcome from salary where employeeid=@id2

if @sr1>@sr2

set @bj=0

else

set @bj=1

end

declare @bj int

exec compa '000001','108991',@bj output select @bj

⑶

①

use yggl

go

create trigger employeesins on dbo.employees

for insert,update

as

begin

if((select ins.departmentid from inserted ins)not in (select departmentid from departments))

Rollback

End

②

Use yggl

Go

Create trigger departmentsupdate on dbo.departments

For update

As

Begin

update employees

set departmentid=(select departmentid from inserted) Where departmentid=(select departmentid from deleted) End

Go

③

Use yggl

Go

Create trigger departmentsdelete on dbo.departments

For delete

As

Begin

Delete from employees

Where departmentid=(select departmentid from deleted) End

Go

4、思考与练习

⑴创建添加职员记录的存储过程employeeadd

Use yggl

Go

Create 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)) As

Begin

Insert into employees

Values(@employeeid,@name,@education,@birthday,@workyear,@sex,@address,@phon enumber,@departmentid)

End

Return

Go

use yggl

exec employeeadd '990230','刘朝','本科','840909',2,1,'武汉小洪山号',

'85465213','3'

Go

⑵创建修改职员记录的存储过程employeeupdate

Use yggl

Go

Create procedure employeeupdate

(@empid char(6), @employeeid char(6),@name char(10),@education char(4),@birthday datetime,@workyear tinyint,@sex bit,@address char(40),@phonenumber

char(12),@departmentid char(3))

As

Begin

Update employees

Set employeeid=@employeeid,

Name=@name,

Education=@education,

Birthday=@birthday,

Workyear=@workyear,

Sex=@sex,

Address=@address,

Phonenumber=@phonenumber,

Departmentid=@departmentid

Where employeeid=@empid

End

Return

use yggl

exec employeeupdate '990230','990232','刘平','本科','840909',2,1,'武汉小洪山号',

'85465213','2'

go

⑶创建删除职员记录的存储过程employeedelete

Use yggl

Go

Create procedure employeedelete

(@employeeid char(6))

As

Begin

Delete from employees

Where employeeid=@employeeid

End

Return

Go

use yggl

exec employeedelete '990232'

go

⑷创建存储过程，要求当一个员工的工作年份大于6年时将其转到经理办公室工作。

create procedure test1 @eid char(6)

as

begin

declare @wyear int

set @wyear=(select workyear from employees where employeeid=@eid)

if @wyear>6

update employees

set departmentid=(select departmentid from departments where departmentname='经理办公室')

where employeeid=@eid

end

select * from employees

exec test1 '000001'

⑸创建存储过程，根据每个员工的学历将收入提高500元。

create procedure test2 @eid char(6)

as

declare @edu char(4)

set @edu=(select education from employees where employeeid=@eid) if @edu='本科'

update salary

set income=income+500

where employeeid=@eid

end

select * from employees

go

select * from salary

exec test2 '010008'

5、实验小结