人力资源管理数据库设计与实现.doc

模块一：Create database staff2create table information( yid int identity(1,1) primary key, info_name char(20) , info_sex char(2), info_card char(20), info_state char(25), info_adress char(50),);select *from information create table wage( yid tinyint identity(1,1) primary key, wage_name_id int not null, wage_totle float(20), /*本月总工资*/ wage_pay float (15), /*本月已发工资*/ wage_pay_date char(15) not null );select *from wageCreate table state( state_id tinyint identity(1,1) primary key, state_name varchar(16) UNIQUE); select *from state模块二 /*查询员工基本信息的视图*/Create view information_view(info_id,info_name,info_sex,info_card,info_state,info_adress)asselect *from information/*查询员工工资的视图*/create view wage(wage_id,wage_name,wage_totle,wage_pay,wage_pay_date)asselect *from wage/*查询员工等级表*/create view state(state_id,state_name)asselect *from state/*建立索引*/create unique index info_nameindex on information(info_name)exec sp_helpindex information模块四：/*建立触发器*/当删除information中某项基本信息，触发wage表，删除相应的记录create trigger information_deleteon informationfor deleteasdeclare id intselect id = id from deleteddelete wagewhere id = id/*创建各个存储过程*/*在information插入一条新记录*/create procedure info_pro2(id int,name char(20),info_sex char(2),info_card char(20),info_state char(25),info_adress char(50)asinsertinto informationvalues (id,name,info_sex,info_card,info_state,info_adress)执行：exec info_pro2 id=20,name=萍媛,info_sex=女,info_card=536819900221,info_state=经理,info_adress=江西/*创建一个查询存储过程*/create procedure info_prodasselect *from informationexec info_prod/*创建一个删除存储过程*/create procedure info_prod3asselect *from information模块五：select * from wage /*创建一表格，用于实现工资差记录*/create table s_wage(wage_name_id char(20), s_wage money)/*实现工资差的记录*/create procedure my_pro_swageas begin declare id char(20)declare mycursor cursor for select wage_name_id from wageopen mycursorfetch next from mycursor into idwhile fetch_status=0 begin declare wage_totle money,wage_pay money select wage_totle=wage_totle,wage_pay=wage_pay from wage where wage_name_id=id insert into s_wage values(id,wage_totle-wage_pay) fetch next from mycursor into id end close mycursor deallocate mycursorend /*执行*/exec my_pro_swageselect * from wageselect *from information /*建立一个触发器，用于满足当infomation改变时wage有所记录*/create trigger my_