SQLServer数据库管理期末论文袁瑞萍.docx

1、SQLServer数据库管理期末论文袁瑞萍 SQL Server数据库管理 课程论文（报告、案例分析）院 系 信息学院 专 业 电子商务 班 级 学生姓名 学 号 任课教师 袁瑞萍 2013 年 12 月 30 日员工管理系统数据库管理与开发一、需求分析(一)系统目标设计出具有员工管理、保险管理、工资管理和考勤管理等功能的系统(二)系统功能划分及描述1、信息输入功能（1）输入员工的基本信息。包括：员工编号、姓名、性别、出生年月、参加工作时间、所属部门、职务、职称、政治面貌、婚姻状况等基本信息。（2）输入员工的工资信息。包括：基本工资、岗位工资、住房补贴、津贴、工会会费、水电费、住房公积金、养老

2、保险、奖惩。（3）输入员工的部门信息。包括：部门编号、部门名称、部门负责人、部门人数。2、数据修改删除功能（1）修改和删除员工的基本信息。当单位人员的信息发生变化，如职称的变化，工作部门变动，或调离单位等，系统应能修改员工的信息或将其从员工信息表中删除。（2）修改和删除员工的工资信息。员工升职加薪、工资普掉是企业中常见的事情，这就需要系统能方便对员工工资进行个别及批量的修改；如员工调离本单位，就应当从员工信息表中删除这个员工信息，将其工资信息表中相应的数据删除。（3）修改和删除部门信息。当撤销一个部门时，能从部门信息表中将其删除。而当一个部门的名称发生改变时，系统中所有该项部门的名称应一致的改

3、变。3、查询和统计功能（1）能够查询指定员工的基本信息。（2）能够查询指定某个部门或全部部门的基本信息。（3）查询某个员工的工资信息。（4）统计、汇总指定年份企业各部门或某个部门每个月或全年工资总额，汇总各部门的人数或本单位的总人数。（5）工资表月工资的生成功能。生成当月所有员工或某个部门的工资记录，同时能进行员工工资的计算，即计算应发金额、应扣金额及实发金额。二、数据库概念结构设计（一）系统概念模型图一 E-R总图图二 员工基本信息图三 部门实体与考勤实体E-R图图四 津贴实体与月工资实体E-R图（二）详细说明1、系统涉及的实体集部门实体集：具有属性部门号、部门名称、部长和人数员工实体集：具

4、有属性职工编号、姓名、性别、出生日期、参加工作日期、所属部门、职务、政治面貌、婚姻状况和电话号码出勤实体集：职工编号、月份、缺勤天数和奖惩工资月工资实体集：惩奖、津贴、工会会费、住房公积金、养老保险和基本工资津贴实体集：职工编号、月份、加班天数和津贴工资2、系统涉及的联系一个部门能有多名员工，一个员工只能属于一个部门，所以部门和员工的联系是1:M的联系一个员工可以有多次出勤记录但一个出勤记录只对应着一个员工，所以员工和出勤的联系是1:M的联系一个员工可以有多中津贴但一个津贴只对应着一个员工，所以员工和津贴的联系是1:M的联系一个员工可以有多分工资但一分工资只对应着一个员工，所以员工和工资的联系

5、是1:M的联系一个人的出勤影响着一个人的工资，一个人的工资也因一个人的出勤变动而变动，所以出勤和工资的联系时1：1一个人的津贴影响着一个人的工资，一个人的工资也因一个人的津贴变动而变动，所以津贴和工资的联系时1：1三、逻辑结构设计将E-R模型转换为关系模式（一）员工实体集员工（emp_no,emp_name,sex,Department_id,Emp_time,Marry,Telephon）分别表示（员工号，员工名称，性别，员工部门，入公司时间，婚姻状况，电话号码）（二）员工津贴实体集津贴（emp_no,Jiaban_month,jbtianshu,Jiaban_jiangjin）分别表示（员

6、工号，加班时间，加班天数，加班工资）（三）考勤实体集考勤（emp_no,Kaoqin_month,queqin,Kaoqin_gongzi）分别表示（员工号，时间，缺勤天数，考勤工资）（四）工资实体集工资（Zw,Jibengongzi）分别表示（职位，基本工资）（五）员工月工资实体集员工月工资（emp_no,jbgz,jt,Kq,ygz_total,ygz_data）分别表示（员工号，基本工资，津贴，缺勤扣除，总月工资，月工资时间）（六）部门实体集部门（department_id,department_name,manager,dep_renshu）分别表示（部门号，部门名称，部长，人数）四、

7、数据库物理结构设计（一）把关系模型转化为表结构列明数据类型可否为空说明emp_novarchar(8)NOT NULL员工号（主键）emp_namevarchar(10)NULL员工名称sexchar(2)NULL性别department_idchar(4)NOT NULL员工部门emp_timevarchar2(6)NOT NULL入公司时间marryvarchar(4)NULL婚姻状况telephonchar(11)NULL电话号码表（1）员工基本情况表列明数据类型可否为空说明department_idchar(4)not null部门号department_namevarchar(6)n

8、ot null部门名称managervarchar(6)null部长dep_renshuvarchar(4)null人数表（2） 部门列明数据类型可否为空说明emo_novarcher(8)not null职工编号 kaoqin_monthvarchar(10)not null月份queqinchar(2)null缺勤天数kaoqin_gongzifloatnull奖惩工资表（3） 考勤列明数据类型可否为空说明emp_novarchar(8)not null职工编号jiaban_monthvarchar(10)not null月份jiabantianshuchar(2)null加班天数jiab

9、an_jiangjinfloatnull津贴工资表（4） 津贴列明数据类型可否为空说明zhiwu varchar(10)not null职务jibengongzi float defaultnull基本工资表（5） 基本工资列明数据类型可否为空说明emp_novarchar(8)not nulldeparment_idchar(4)not nulljibengognzifloatnullzhufangbutie floatnull住房补贴gz_monthvarchar(10)not nulljiaban_jiangjinfloatnull奖惩kaoqin_gongzifloatnull津贴go

10、nghuihuifeifloatnull工会会费zhfgjjin floatnull住房公积金yanglaobaoxian floatnull养老保险wagecount floatnull表（6） 月工资五、数据库实施-创建数据库create database 工资管理on( name = sales_dat, filename = D:sql_datasales.mdf)go-创建表create table 员工( emp_no varchar(8) not null primary key, -职工编号 emp_name varchar(10) not null, -姓名 sex char

11、(2) -性别 check(sex = 男 or sex = 女), birth_date datetime not null, -出生日期 hir_date datetime not null, -参加工作时间 department_id char(4) not null, -所属部门 zhiwu varchar(10) not null, -职务 zhengzhimm varchar(10), -政治面貌 marry varchar(4), -婚姻状况 telephon char(11), -电话号码 constraint old check(datediff(year,birth_dat

12、e,hir_date)18)create table 部门( department_id char(4) not null primary key, -部门号 department_name varchar(6) not null, -部门名称 manager varchar(6), -部长 dep_renshu varchar(4) -人数)create table 考勤( emp_no varcher(8) not null, -职工编号 kaoqin_month varchar(10) not null, -月份 queqin char(2), -缺勤天数 kaoqin_gongzi f

13、loat, -奖惩工资 constraint e_k primary key(emp_no,kaoqin_month)create table 津贴( emp_no varchar(8) not null, -职工编号 jiaban_month varchar(10) not null, -月份 jiabantianshu char(2), -加班天数 jiaban_jiangjin float, -津贴工资 constraint e_j primary key(emp_no,jiaban_month) create table 基本工资( zhiwu varchar(10) not null

14、 primary key,-职务 jibengongzi float default 1500, -基本工资)create table 月工资( emp_no varchar(8) not null, deparment_id char(4) not null, jibengognzi float default 1500, zhufangbutie float, -住房补贴 gz_month varchar(10) not null, jiaban_jiangjin float, -奖惩 kaoqin_gongzi float, -津贴 gonghuihuifei float, -工会会费

15、zhfgjjin float, -住房公积金 yanglaobaoxian float, -养老保险 wagecount as jibengongzi+zhufangbutie+jiaban_jiangjin+kaoqin_gongzi+gonghuihuifei+zhfgjjin+yanglaobaoxian constraint e_g primary key (emp_no,gz_month)go-插入数据insert 员工 values(20070101,狄一,男,1985-1-1,2007-1-1,01,部长,团员,否,1234567890)insert 员工 values(2007

16、0102,狄二,女,1985-1-2,2007-1-2,01,员工,党员,否,1234567891)insert 部门 values(01,人事部,狄一,20)insert 部门 values(02,财务部,狄六,10)insert 考勤 values(20070101,200905,0,0)insert 考勤 values(20070101,200906,2,-60)insert 津贴 values(20070101,20000905,05,15)insert 津贴 values(20070101,20000906,0,0)insert 基本工资 values(员工,1500)insert

17、基本工资 values(组长,2000) insert 月工资 values(20070101,01,2000,0,20000905,0,15,0,0,0)insert 月工资 values(20070102,01,1500,0,20000905,-30,0,0,0,0) go-创建、绑定默认值create default gongzi_defa as 0exec sp_bindefault gongzi_defa,考勤.kaoqin_gongziexec sp_bindefault gongzi_defa,津贴.jiaban_jiangjinexec sp_bindefault gongzi

18、_defa,月工资.gonghuihuifeiexec sp_bindefault gongzi_defa,月工资.zhfgjjinexec sp_bindefault gongzi_defa,月工资.yanglaobaoxianexec sp_bindefault gongzi_defa,月工资.zhufangbutie六、设计和创建存储过程-创建存储过程-a.存储过程查询员工基本信息create proc ygxx2yg_id varchar(10)asselect * from 员工 where emp_no = yg_id-b.存储过程查询指定某个部门或全部部门的基本信息create

19、proc bumen2bumen_id2 varchar(4)asselect * from 部门 where department_id = bumen_id2create proc bumenqasselect * from 部门-c.存储过程查询某个员工的工资信息create proc gzxxyg_id varchar(10)asselect * from 月工资where epm_no = yg_id-d.汇总部门月份工资create proc gzhzdep_id varchar(4),month_year varchar(10)asselect department_id,gz_

20、month from 月工资where department_id = dep_id and gz_month = month_yearorder by department_id ,gz_month-e.汇总工资总和create proc sum_sumasselect department_name,gz_month,sum(wagecount) from 部门,月工资where 部门.department_id = 月工资.department_id group by department_name,gz_month七、设计和创建函数-创建函数-a.函数查询员工基本信息create fu

21、nction ygxx(yg_id varchar(10)returns tableasreturn(select * from 员工 where emp_no = yg_id)-b.函数查询部门信息create function bumen(bumen_id varchar(4)returns tableasreturn(select * from 部门 where department_id = bumen_id)八、设计和创建触发器-创建触发器-a.员工表中删除一条信息，其他表相应员工信息删除create trigger em on 员工for deleteasdelete from 考

22、勤 where emp_no in(select emp_no from deleted)delete from 津贴 where emp_no in(select emp_no from deleted)delete from 月工资 where emp_no in(select emp_no from deleted)-b.一个部门删除，此部门中的员工信息删除create trigger bm on 部门for deleteasdelete from 员工 where department_id in (select department_id from deleted)-c.员工职务变动

23、，基本工资改变create trigger jbgz on 员工for update,insertasdeclare q varchar(12)declare m varchar(12)declare s floatselect q = emp_no from deletedselect m = zhiwu from insertedselect s = jibengongzi from 基本工资 where zhiwu = mupdate 月工资set jibengongzi = s where 月工资.emp_no = q九、总结（一）人员分工情况组内人员负责任务ER图的编写及相关实体集的

24、描述；搜集数据并插入对整个系统功能进行划分；创建数据库及数据表将ER模型转化为关系模式；将系统模型表格化；设计并创建存储过程；系统调试任务分工；系统函数和触发器的设计及创建；系统调试；撰写报告论文（二）收获及体会尽管完成了此次老师布置的期末大作业，但由于时间仓促，所以只满足了部分作业要求，而且并没有过多考虑到系统的技术含量，以及在后期调试过程中出现的部分问题依旧没有很好地解决。但是在这过程中，我们对于遇到的问题进行了积极地探讨，虽然有些问题最终也没有很好地解决，但是却使我们小组的成员更加熟练地掌握了Microsoft SQL Server 数据库的应用，积累了一定的系统开发经验，使我们对系统开发的知识有了更深刻的理解、更贴切的感受。同时，通过这次大作业，我们也发现了许多自身的问题。例如：对基本的SQL语句掌握不牢固，通过翻阅教材后才能编写。同时我们对许多原来不太清楚的知识点有了更进一步的了解，写代码的时候一定要细心，注意代码中的一切标点符号都要用英文，创建存储过程时，需要用到变量的要先声明变量，创建了外键约束时，要先对不被约束的表进行操作。