数据库课程设计源代码

use kjq111007307

/*创建一个部门信息表

包含“部门号，部门名，部门经理，人数”属性列*/

create table department

(depart_no char (2) primary key ,

depart_name char (30) not null,

depart_manage char (6) not null,

depart_people int not null

)

/*创建一个职位信息表

包含“职位，基本薪资, 福利, 失业保险, 住房公积金”属性列*/

create table position

(pos char (30) primary key ,

basesalary float not null,

benefits float not null,

insurances float not null,

housing_funds float not null

)

/*创建一个职工信息表

包含" 职工号，职工名，性别，年龄，学历，部门号，职位" 属性列*/

create table staff_message

(staff_no char (4) primary key ,

staff_name char (10) not null,

staff_sex char (2) check (staff_sex in(' 男' , ' 女' )),

staff_age int not null,

staff_edu char (10) not null,

staff_dep char (2) not null,

staff_job char (30) not null,

foreign key (staff_dep) references department(depart_no),

foreign key (staff_job) references position(pos )

)

/*创建一个员工考勤表

包含“职工号，年月，迟到，缺勤，加班”属性列*/

create table staff_days

(staff_no char (4),

month_date char (6),

staff_late int not null,

staff_absent int not null,

workoverdays int not null,

primary key (staff_no, month_date)

)

/*创建一个薪资表

包含“职工号，年月，奖金，罚金，真实薪资”属性列*/

create table salary

(staff_no char (4),

month_date char (6),

addsalary float not null,

subsalary float not null,

relsalary float not null,

primary key (staff_no, month_date)

)

create index salary_index on salary(month_date asc , staff_no asc ); /*部门表信息的录入*/

/*经理室*/

insert

into department

values ('01' , 'manage_department', ' 王栋' , '1' );

/*财务科*/

insert

into department

values ('02' , 'financial_department', ' 张鹏' , '3' );

/*技术科*/

insert

into department

values ('03' , 'plan_department', ' 代淑英' , '5' );

/*销售科*/

insert

into department

values ('04' , 'market_department', ' 金加容' , '6' );

/*职位表信息的录入*/

/*经理*/

insert

into position

values ('manager' , '4500' , '1125' , '-45' , '-36' );

/*副经理*/

insert

into position

values ('assistant_manager', '4000' , '1000' , '-40' , '-32' );

/*办事*/

insert

into position

values ('clerk' , '3500' , '875' , '-35' , '-28' );

/*助理*/

insert

into position

values ('assistant' , '2000' , '500' , '-20' , '-16' );

/*职工信息表信息的录入*/

insert

into staff_message

values ('0101' , ' 王栋' , ' 男' , '38' , ' 硕士' , '01' , 'manager' );

insert

into staff_message

values ('0201' , ' 张鹏' , ' 男' , '35' , ' 硕士' , '02' , 'manager' );

insert

into staff_message

values ('0202' , ' 程雷' , ' 男' , '30' , ' 本科' , '02' , 'clerk' );

insert

into staff_message

values ('0203' , ' 王晶晶' , ' 女' , '29' , ' 本科' , '02' , 'assistant' );

insert

into staff_message

values ('0301' , ' 代淑英' , ' 女' , '35' , ' 硕士' , '03' , 'manager' );

insert

into staff_message

values ('0302' , ' 刘燕' , ' 女' , '30' , ' 本科' , '03' , 'assistant_manager');

insert

into staff_message

values ('0303' , ' 杨浩' , ' 男' , '27' , ' 本科' , '03' , 'clerk' );

insert

into staff_message

values ('0304' , ' 程伟' , ' 男' , '31' , ' 本科' , '03' , 'clerk' );

insert

into staff_message

values ('0305' , ' 唐琦' , ' 女' , '25' , ' 本科' , '03' , 'assistant' );

insert

into staff_message

values ('0401' , ' 金加容' , ' 女' , '34' , ' 本科' , '04' , 'manager' );

insert

into staff_message

values ('0402' , ' 吴辉' , ' 男' , '35' , ' 本科' , '04' , 'assistant_manager');

insert

into staff_message

values ('0403' , ' 陈睿' , ' 男' , '32' , ' 本科' , '04' , 'clerk' );

insert

into staff_message

values ('0404' , ' 万莉' , ' 女' , '36' , ' 本科' , '04' , 'clerk' );

insert

into staff_message

values ('0405' , ' 方冬雨' , ' 女' , '31' , ' 本科' , '04' , 'clerk' );

insert

into staff_message

values ('0406' , ' 陈晨' , ' 女' , '27' , ' 专科' , '04' , 'assistant' );

insert

into staff_days

values ('0101' , '201205' , '0' , '0' , '0' );

insert

into staff_days

values ('0201' , '201205' , '0' , '0' , '0' );

insert

into staff_days

values ('0202' , '201205' , '0' , '0' , '0' );

insert

into staff_days

values ('0203' , '201205' , '0' , '0' , '0' );

insert

into staff_days

values ('0301' , '201205' , '0' , '0' , '0' );

insert

into staff_days

values ('0302' , '201205' , '0' , '0' , '0' );

insert

into staff_days

values ('0303' , '201205' , '0' , '0' , '0' );

insert

into staff_days

values ('0304' , '201205' , '0' , '0' , '0' );

insert

into staff_days

values ('0305' , '201205' , '0' , '0' , '0' );

insert

into staff_days

values ('0401' , '201205' , '0' , '0' , '0' );

insert

into staff_days

values ('0402' , '201205' , '0' , '0' , '0' );

insert

into staff_days

values ('0403' , '201205' , '0' , '0' , '0' );

insert

into staff_days

values ('0404' , '201205' , '0' , '0' , '0' );

insert

into staff_days

values ('0405' , '201205' , '0' , '0' , '0' );

insert

into staff_days

values ('0406' , '201205' , '0' , '0' , '0' );

insert

into salary

values ('0101' , '201205' , '0' , '0' , '5544' );

insert

into salary

values ('0201' , '201205' , '0' , '0' , '5544' );

insert

into salary

values ('0202' , '201205' , '0' , '0' , '4312' );

insert

into salary

values ('0203' , '201205' , '0' , '0' , '2464' );

insert

into salary

values ('0301' , '201205' , '0' , '0' , '5544' );

insert

into salary

values ('0302' , '201205' , '0' , '0' , '4928' );

insert

into salary

values ('0303' , '201205' , '0' , '0' , '4312' );

insert

into salary

values ('0304' , '201205' , '0' , '0' , '4312' );

insert

into salary

values ('0305' , '201205' , '0' , '0' , '2464' );

insert

into salary

values ('0401' , '201205' , '0' , '0' , '5544' );

insert

into salary

values ('0402' , '201205' , '0' , '0' , '4928' );

insert

into salary

values ('0403' , '201205' , '0' , '0' , '4312' );

insert

into salary

values ('0404' , '201205' , '0' , '0' , '4312' );

insert

into salary

values ('0405' , '201205' , '0' , '0' , '4312' );

insert

into salary

values ('0406' , '201205' , '0' , '0' , '2464' );

/*******************触发器***********************/

/*创建一个触发器当修改考勤表中的加班或迟到或缺勤时，薪资表中的数据有自动更新的功能*/ create trigger xinzi1

on staff_days

for update

as

/*定义三个变量late,absent,overdays 当考勤表有更新时用来记录新的数据*/

declare @late int , @absent int , @overdays int

/*给变量赋值*/

select @late=staff_late from inserted

select @absent=staff_absent from inserted

select @overdays=workoverdays from inserted

/*定义变量来记录组成最终真实薪资的各项薪资*/

declare @kkbasesalary float , @kkaddsalary float ,

@kksubsalary float , @kkbenefits float ,

@kkinsurances float , @kkhousing_funds float , @kkrelsalarys float

/*给组成最终真实薪资的各项薪资赋值*/

select @kkbasesalary=(select basesalary

from position

where pos=(select staff_job

from staff_message

where staff_no=(select staff_no from

inserted ))

)

select @kkaddsalary=(select workoverdays from inserted)*30

select @kksubsalary=(select staff_late from inserted)*(-10)+(select staff_absent from inserted)*(-30)

select @kkbenefits=@kkbasesalary*(0.25)

select @kkinsurances=@kkbasesalary*(-0.01)

select @kkhousing_funds=@kkbasesalary*(-0.008)

/*用组成真实薪资的各项薪资来给真实薪资赋值*/

select

@kkrelsalarys=@kkbasesalary+@kkaddsalary+@kksubsalary+@kkbenefits+ @kkinsurances+@kkhousing_funds

/*利用变量对薪资表中的' 加班费，迟到缺勤扣除费' 进行更新*/

update salary

set addsalary=@kkaddsalary

where staff_no=(select staff_no from inserted)

and month_date=(select month_date from inserted)

update salary

set subsalary=@kksubsalary

where staff_no=(select staff_no from inserted)

and month_date=(select month_date from inserted)

update salary

set relsalary=@kkrelsalarys

where staff_no=(select staff_no from inserted)

and month_date=(select month_date from inserted)

/*创建一个触发器当对考勤表插入新数据时，薪资表有自动对应插入新数据的功能*/

create trigger xinzi2

on staff_days

for insert

as

begin

/*定义变量来记录薪资表中应插入的各项数据*/

declare @jjstaff_no char (4), @jjmonth_date char (6)

declare @jjbasesalary float , @jjaddsalary float ,

@jjsubsalary float , @jjbenefits float ,

@jjinsurances float , @jjhousing_funds float , @jjrelsalarys float

/*给各个变量赋值*/

select @jjstaff_no=(select staff_no

from inserted

)

select @jjmonth_date=(select month_date

from inserted

)

select @jjbasesalary=(select basesalary

from position

where pos=(select staff_job

from staff_message

where staff_no=(select staff_no from

inserted ))

)

select @jjaddsalary=0

select @jjsubsalary=0

select @jjbenefits=@jjbasesalary*(0.25)

select @jjinsurances=@jjbasesalary*(-0.01)

select @jjhousing_funds=@jjbasesalary*(-0.008)

select @jjrelsalarys=@jjbasesalary+@jjbenefits+

@jjinsurances+@jjhousing_funds

/*将新值插入到薪资表中*/

insert

into salary

values (@jjstaff_no, @jjmonth_date, @jjaddsalary,

@jjsubsalary, @jjrelsalarys)

end

/*对员工信息表创建一个触发器实现当登记新来员工信息时, 薪资表和

考勤表中自动对应增加新记录的功能*/

create trigger staffmessage1

on staff_message

for insert

as

begin

/*定义变量来记录待插入薪资表和考勤表的数据*/

declare @hhstaff_no char (4)

select @hhstaff_no=(select staff_no from inserted)

/*向考勤表中自动插入新员工的薪资数据, 同时薪资表也会自动插入新数据*/

insert

into staff_days

values (@hhstaff_no, '201206' , 0, 0, 0)

end

/*对员工信息表创建一个触发器来实现当某员工信息从信息表中删除时，薪资表和考勤表能自 动删除关于该员工的信息的功能*/

create trigger staffmessage2

on staff_message

for delete

as

begin

/*删除辞职员工在薪资表中的记录*/

delete

from salary

where staff_no=(select staff_no from deleted)

/*删除辞职员工在考勤表中的记录*/

delete

from staff_days

where staff_no=(select staff_no from deleted)

end

/******************存储过程********************/

/*创建一个存储过程实现向部门表录入新数据的功能*/

create procedure department_insert

@aadepart_no char (2), @aadepart_name char (30),

@aadepart_manage char (6), @aadepart_people int

as

begin

insert

into department

values (@aadepart_no, @aadepart_name, @aadepart_manage, @aadepart_people) end

/*创建一个存储过程实现向职位表录入新数据的功能*/

create procedure position_insert

@bbpos char (30), @bbbasesalary float , @bbbenefits float , @bbinsurances float , @bbhousing_funds float

as

begin

insert

into position

values (@bbpos, @bbbasesalary, @bbbenefits, @bbinsurances, @bbhousing_funds) end

/*创建一个存储过程实现向职工信息表录入新数据的功能*/

create procedure staffmessage_insert

@ccstaff_no char (4), @ccstaff_name char (10), @ccstaff_sex char (2), @ccstaff_age int , @ccstaff_edu char (10), @ccstaff_dep char (2),

@ccstaff_job char (30)

as

begin

insert

into staff_message

values (@ccstaff_no, @ccstaff_name, @ccstaff_sex,

@ccstaff_age, @ccstaff_edu, @ccstaff_dep, @ccstaff_job)

end

/*创建一个存储过程实现在新的一月时向考勤表中插入新数据的功能*/

create procedure kaoqin_insert

@qqstaff_no char (4), @qqmonth_date char (6),

@qqstaff_late int , @qqstaff_absent int ,

@qqworkoverdays int

as

begin

insert

into staff_days

values (@qqstaff_no, @qqmonth_date, @qqstaff_late, @qqstaff_absent, @qqworkoverdays )

end

/*创建一个存储过程实现只能查看自己的基本信息的功能*/

create proc select_staffmessage

@staff_number char (4)

as

select *

from staff_message

where staff_no=@staff_number;

/*创建一个存储过程实现只能查看自己的工资情况的功能*/

create proc select_salary

@staff_num char (4)

as

/*查看自己某年某月的福利，失业保险，住房公积金，奖金，罚金，最终真实工资*/

select

salary . staff_no, salary . month_date, position . benefits , position . insurances ,

position . housing_funds, salary . addsalary , salary . subsalary , salary . relsala ry

from salary, staff_message, position

where salary. staff_no=@staff_num

and salary. staff_no=staff_message. staff_no

and staff_message. staff_job=position . pos ;

/*创建一个存储过程实现只能查看自己的考勤信息的功能*/

create proc select_staffdays

@staff_no char (4)

as

select *

from staff_days

where staff_no=@staff_no;

/*****************实现功能的代码********************/

/*查看五个基本表*/

select *

from department;

select *

from position;

select *

from staff_message;

select *

from staff_days

select *

from salary;

/*查看某员工自己的信息*/

exec select_staffmessage

'0202' ;

/*查看某员工自己的考勤记录*/

exec select_staffdays

'0202' ;

/*查看某员工自己的薪资*/

exec select_salary

'0202' ;

/*更新考勤表薪资表会自动对应更新，*/

update staff_days

set staff_late=3

where staff_no='0101' and month_date='201205' ;

select *

from staff_days;

select *

from salary;

/*向考勤表插入信息，薪资表也会自动对应插入信息*/

exec kaoqin_insert

'0101' , '201206' , '0' , '0' , '0' ;

select *

from staff_days;

select *

from salary;

/*向员工信息表插入信息，考勤表和薪资表也会自动对应插入新信息*/ exec staffmessage_insert

'0204' , ' 陈珍' , ' 女' , '29' , ' 本科' , '02' , 'clerk' ;

select *

from staff_message;

select *

from staff_days;

select *

from salary;

/*删除员工信息表的信息，考勤表和信息表也会自动删除对应信息*/ delete

from staff_message

where staff_no='0204' ;

select *

from staff_message;

select *

from staff_days;

select *

from salary;

/*按部门查询员工的基本信息*/

select *

from staff_message

where staff_dep='03' ;

/*按年月查看员工的考勤记录*/

select *

from staff_days

where month_date='201205' ;

/*按部门查看所有员工薪资总数*/

select sum (relsalary )

from staff_message, salary

where salary. staff_no=staff_message. staff_no

and staff_dep='04' ;

/*按职位查看所有员工薪资总数*/

select sum (relsalary )

from staff_message, salary

where salary. staff_no=staff_message. staff_no and staff_job='clerk' ;