info,home_info.Home_number,home_info.Home_phone,home_info.Home_address from worker_info,degree_info,pro_info,home_info,department_info where worker_info.Worker_no=degree_info.Worker_no and worker_info.Worker_no=pro_info.Worker_no
and worker_info.Worker_no=home_info.Worker_no and
department_info.Department_name=worker_info.Department_name with check option
4.4．2
建立一个用于查看员工常用信息的视图
create view search as
select worker_info.Worker_no '员工编号',worker_info.Worker_name '员工姓名',worker_info.Sex '性别',worker_info.Age '年龄',worker_info.Department_name '部门',
worker_info.Post '职位',degree_info.Degree'学位',home_info.Marriage_info '婚姻状况',worker_info.Work_date '工作时间'
from worker_info,degree_info,home_info,department_info where worker_info.Worker_no=degree_info.Worker_no and worker_info.Worker_no=home_info.Worker_no with check option
4.4．3
查询员工学历视图
create view show_degree(员工编号,姓名,性别,学历号,学位,专业,毕业学校,毕业时间) as
select worker_info.Worker_no,worker_info.Worker_name,worker_info.Sex, degree_info.Degree_no,degree_info.Degree,
degree_info.Major,degree_info.School,degree_info.Gradute_date from worker_info,dergee_info
where worker_info.Worker_no=degree_info.Worker_no with check option
4.4．4
查询部门工作量
create view show_load(员工编号,员工姓名,部门,工作量 ) as select
worker_info.worker_no,worker_info.worker_name,worker_info.Department_name,department_info.Workeload
18
from department_info,worker_info where
department_info.department_name=worker_info.department_name select * from show_load with check option
4.4．5
查询员工职称
create view show_degree(员工编号,姓名,性别,职称号,评定时间,评定单位,评定职称,备注) as select
worker_info.Worker_no,worker_info.Worker_name,worker_info.Sex,pro_info.Pro_no,pro_info.Assess_date,pro_info.Assess_unit,
pro_info.Assess_title,pro_info.Remark from worker_info,pro_info,home_info where worker_info.Worker_no=pro_info.Worker_no
4.4．6
查询员工学历
create view show_degree(员工编号,姓名,性别,学历号,学位,专业,毕业学校,毕业时间) as
select
worker_info.Worker_no,worker_info.Worker_name,worker_info.Sex,degree_info.Degree_no,
degree_info.Degree,degree_info.Major,degree_info.School,degree_info.Gradute_date
from worker_info,degree_info where
worker_info.Worker_no=degree_info.Worker_no with check option
4.5 创建游标 4.5．1
创建查询未婚员工的游标
declare search_marriage1 cursor for select worker_no from home_info
where worker_no in(select Worker_no from home_info where marriage_info='未婚')
open search_marriage declare @no varchar(15)
fetch next from search_marriage into @no while @@FETCH_STATUS = 0 begin
select * from worker_info,degree_info,pro_info,home_info where worker_info.Worker_no=@no and home_info.Worker_no=@no
19
and pro_info.Worker_no=@no and degree_info.Worker_no=@no fetch next from search_marriage into @no end
close search_marriage
4.5．2
创建查询已婚员工的游标
declare search_marriage2 cursor for select worker_no from home_info
where worker_no in(select Worker_no from home_info where marriage_info='已婚')
open search_marriage2 declare @no varchar(15)
fetch next from search_marriage2 into @no while @@FETCH_STATUS = 0 begin
select * from worker_info,degree_info,pro_info,home_info where worker_info.Worker_no=@no and home_info.Worker_no=@no and pro_info.Worker_no=@no and degree_info.Worker_no=@no fetch next from search_marriage2 into @no end
close search_marriage2
4.5．3
创建查询某部门员工的游标
declare search_department cursor for select worker_no from worker_info where Department_name=@name open search_department declare @no1 varchar(15)
fetch next from search_department into @no1 while @@FETCH_STATUS = 0 begin select
worker_info.Worker_name,worker_info.Department_name,home_info.Marriage_info,degree_info.Major,worker_info.Work_date
from worker_info,degree_info,pro_info,home_info where worker_info.Worker_no=@no1 and home_info.Worker_no=@no1 and pro_info.Worker_no=@no1 and degree_info.Worker_no=@no1 fetch next from search_department into @no1 end
close search_department
deallocate search_department
20
4.5．4
创建查询有某学历员工的游标
declare search_degree cursor for select worker_no from degree_info
where worker_no in(select Worker_no from degree_info where Degree=@name) open search_degree
declare @no1 varchar(15)
fetch next from search_degree into @no1 while @@FETCH_STATUS = 0 begin select
worker_info.Worker_name,worker_info.Department_name,home_info.Marriage_info,degree_info.Major,degree_info.degree, worker_info.Work_date
from worker_info,degree_info,pro_info,home_info where worker_info.Worker_no=@no1 and home_info.Worker_no=@no1 and pro_info.Worker_no=@no1 and degree_info.Worker_no=@no1 fetch next from search_degree into @no1 end
close search_degree
deallocate search_degree
4.6 创建事物处理 4.6．1
查询部门人数
begin transaction
select department_no,department_name,Manager_no, Real_number,Need_number from department_info commit transaction;
21
总结
通过本次为期一周的数据库原理课程设计，让我对数据库系统的设计流程有了更深层次的了解，学会将理论与实际相结合解决问题，也加强了从整体思考问题的能力。在本次课程设计中也有许多不足之处，例如表与表之间的关系太过与单一，这样使得表之间的联系不是很紧密，像是对人事管理系统的需求分析不熟悉，导致对模块的分配不够完善，还有就是对PowerDesigner的运用以及SQL Sever2008和PowerDesigner之间的联合运用不够熟练，希望在以后的学习中能够对数据库系统的设计更加熟悉。
22