Oracle员工工资管理系统.doc

大型数据库技术设计报告题目：员工工资管理系统数据库设计学生姓名:李龙学号: 201317030120 专业班级：网工13101班指导教师:易明设计时间: 2015年下学期指导老师意见:签名：日期：评定成绩:目录1. 需求分析12. 概念模型设计11. 员工信息E-R图（如图1所示）12. 部门信息E-R图（如图2所示）23。 工资实体局部E-R模型设计(如同3所示）24. 职务信息ER图（如图4所示）35. 考勤信息ER图（如图5所示）36. 整体简洁ER图(如图6所示）47. 用户信息E-R图（如图7所示）43。 逻辑结构设计51。 关系模型设计52. 数据库表结构的设计54. 数据库的实现71。 创建用户72. 为用户分配权限73。 用新创建的用户登录数据库74. 创建表空间75. 在表空间上创建员工信息表76. 向员工信息表中插入记录87。 查询员工信息表中的记录98. 在表空间上创建部门信息表99。 向部门信息表中插入记录910。 查询部门信息表中的记录1011. 创建工资信息表1012. 向工资信息表中插入记录1013. 查询工资信息表中的记录1114。 创建职务信息表1115。 向职务信息表中插入记录1116。 查询职务信息表中的数据1317. 创建考勤信息表1318. 向考勤信息表中插入记录1319. 查询考勤信息表中的记录1520。 创建用户信息表1521. 向用户信息表中插入记录1522. 向用户信息表中的性别建立B树索引1523。 给员工信息表建立外键和CHCEK约束1624。 给职务信息表建立外键和CHCEK约束1625. 给考勤信息表建立外键约束165。 主要代码171。 查询代码172。 创建视图203. 创建存储过程214. 创建触发器当某员工辞职后删除该员工记录236. 权限设置及数据库备份231. 创建用户(lilong）并为其分配所有权限232。 创建用户（lixiaolong)并为其分配查询权限243。 导出用户数据247. 心得体会258。 参考文献261. 需求分析实现员工基本信息管理、工资信息管理、部门信息管理等功能，包括员工信息的添加、修改、删除及查询；工资信息的添加、修改、删除及查询等。2. 概念模型设计1. 员工信息E-R图（如图1所示）图Error! Bookmark not defined.员工信息E-R图2. 部门信息E-R图(如图2所示）图Error! Bookmark not defined.部门信息E-R图3. 工资实体局部ER模型设计（如同3所示)图1工资信息E-R图4. 职务信息E-R图（如图4所示）图2职务信息E-R图5. 考勤信息E-R图(如图5所示）图3考勤信息ER图6. 整体简洁E-R图(如图6所示）图Error! Bookmark not defined.整体简洁E-R图7. 用户信息ER图(如图7所示）图 7用户信息ER图3. 逻辑结构设计1. 关系模型设计1) 员工信息对应的关系模型员工（员工编号(主键）,姓名，性别,联系电话，入厂日期，部门编号（外键），工资等级（外键）2) 部门信息对应的关系模型部门(部门编号(主键)，部门名称，部门经理）3) 工资信息对应的关系模型工资（工资等级（主键），基本工资,年终奖)4) 职务信息对应的关系模型管理人员(职务编号（主键），联系电话，职位，工资级别(外键),性别）5) 考勤信息对应的关系模型考勤（员工编号（主键一部分），日期（主键一部分)，请假次数，迟到次数，缺勤次数）6) 用户信息对应的关系模型用户(用户名，密码，权限）2. 数据库表结构的设计1) 员工信息表结构2) 部门信息表结构3) 工资信息表结构4) 职务人员信息表结构5) 考勤信息表结构4. 数据库的实现1. 创建用户以管理员身份登录数据库，创建新用户create user lilong identified by 123;2. 为用户分配权限grant create session,resource,dba to lilong；3. 用新创建的用户登录数据库conn lilong/1234. 创建表空间create tablespace testdatafile D:李龙课程设计test size 10mautoextend on next 1m maxsize 100mextent management local；5. 在表空间上创建员工信息表create table emp(empno number（4) primary key,ename varchar2(10) not null，sex char（2) not null，tel nvarchar2(11) ,hiredate date not null，deptno number（4) not null,rank char(2) not null)tablespace test6. 向员工信息表中插入记录insert into emp values（1,刘备,男，11711602970,TO_DATE(20090212,yyyymmdd)，10,三)；insert into emp values(2,吕布，男，12307366965,TO_DATE(201003-02,yyyy-mmdd)，10，六);insert into emp values(3，貂蝉,女,18107348365，TO_DATE（20111115,yyyymmdd），10，五);insert into emp values(4,赵云,男，13307304515，TO_DATE（201211-11,yyyy-mm-dd）,11,六）;insert into emp values（5,诸葛亮，男,18711602970,TO_DATE(2014-0419,yyyymmdd），11,四）；insert into emp values(6，关羽，男,18107304565，TO_DATE(201305-14,yyyymmdd),11，五);insert into emp values（7，黄忠,男，13307302265,TO_DATE（201402-12,yyyy-mm-dd),12，二);insert into emp values(8,鲁肃，男,13407304565，TO_DATE（2014-0213，yyyy-mmdd)，12，九)；insert into emp values(9，周瑜,男，18117304765,TO_DATE（201204-14，yyyymm-dd）,12，十）;insert into emp values（10，曹操,男，12307304165，TO_DATE（2012-0215，yyyy-mm-dd），13,六)；insert into emp values(11,颜良,女,1307304465,TO_DATE（201406-13，yyyy-mmdd）,13，七）;insert into emp values(12,文丑，男，13027304565,TO_DATE（201303-10，yyyy-mm-dd）,13，八);7. 查询员工信息表中的记录8. 在表空间上创建部门信息表create table dept（deptno number（4) primary key，dname varchar2(10) not null，manager varchar2（10） not null）tablespace test9. 向部门信息表中插入记录insert into dept(deptno,dname,manager,mtel）values(10,项目部,李龙,17711602970);insert into dept（deptno,dname,manager，mtel)values(11,人事部,李梦龙,18307304565）；insert into dept(deptno，dname，manager,mtel）values（12，财务部，李欣龙,13116023970);insert into dept（deptno，dname,manager,mtel)values（13,IT部，李龙龙,18707602750)；10. 查询部门信息表中的记录11. 创建工资信息表create table sal(rank char（2) primary key,basalary number(4） not null,bonus number（4) default 0)tablespace test12. 向工资信息表中插入记录insert into sal(rank,basalary，bonus)values（一,9800，3000）；insert into sal（rank，basalary，bonus)values（二,9500,2500）；insert into sal（rank，basalary,bonus)values(三,9200,2300）；insert into sal(rank,basalary,bonus)values（四，9000，2200)；insert into sal（rank,basalary,bonus)values(五，8000，2100）;insert into sal（rank,basalary，bonus)values（六,7000,2000);insert into sal(rank，basalary,bonus）values(七,6000，1800）;insert into sal(rank，basalary，bonus)values（八,5000，1600)；insert into sal（rank,basalary，bonus）values（九，4000，1400);insert into sal（rank，basalary，bonus）values（十,3000，1200);13. 查询工资信息表中的记录14. 创建职务信息表create table duty(dutyno number（4) primary key，dname varchar2（10） not null，job varchar2（10) not null，tel nvarchar2(11） ，sex char（2) not null，rank char(2) ）tablespace test 15. 向职务信息表中插入记录insert into duty(dutyno，duname，job，tel，sex，rank) values（101,李龙，总经理，17711602970,男，一）；insert into duty（dutyno,duname,job，tel,sex,rank) values（102,李小龙,副经理，18307366965,男,二);insert into duty（dutyno，duname,job，tel，sex,rank） values（103,李子龙,秘书，18107346365,女,三);insert into duty(dutyno，duname,job,tel，sex，rank） values（104,李梦龙，总经理，18307304565,女，一);insert into duty（dutyno,duname,job,tel，sex，rank） values(105,李泽龙,助理,18711602970,女，四）；insert into duty(dutyno，duname，job，tel，sex，rank) values（106，李云龙,秘书，13661602970，女,五）;insert into duty(dutyno，duname，job,tel,sex，rank） values（107,李欣龙，总经理,13116023970,女，二)；insert into duty（dutyno，duname，job,tel，sex,rank） values(108,李好龙，副经理,13765602950,男,三)；insert into duty(dutyno,duname，job，tel,sex,rank) values（109，李坏龙,秘书,18712602570，男,七);insert into duty(dutyno,duname，job,tel，sex，rank) values(110，李龙龙,总经理，18707602750,男,一）;insert into duty(dutyno,duname，job，tel，sex,rank） values（111,李章龙,秘书,18707362710,女，八）;insert into duty(dutyno,duname，job，tel,sex，rank） values(112,李宇龙,助理，18711812720,女,九)；16. 查询职务信息表中的数据17. 创建考勤信息表create table attend(day date ，empno number（4），leave number（4） default 0，late number（4) default 0，absence number（4) default 0,primary key (day，empno）tablespace test18. 向考勤信息表中插入记录insert into attend (day,empno，leave,late,absence）values(to_date(201012-3，yyyymmdd）,1，1,0，0)；insert into attend （day,empno,leave，late，absence)values(to_date(2011-10-2，yyyy-mm-dd),4,0,1,0);insert into attend (day,empno，leave，late,absence)values（to_date（2012-1012，yyyy-mm-dd），2，1,0,1);insert into attend (day，empno，leave，late,absence)values（to_date(2014-1111,yyyy-mmdd）,3，0,0，0）;insert into attend （day，empno，leave，late，absence)values(to_date（2013714,yyyy-mm-dd），4,1,0，0）;insert into attend (day，empno，leave，late,absence）values（to_date（20121112，yyyy-mmdd），5，1,1，0）；insert into attend (day，empno,leave,late，absence)values（to_date（2013-1114，yyyymmdd),6，1,0，0）；insert into attend （day,empno，leave,late,absence)values(to_date（2014621,yyyy-mm-dd）,7,0,0,0);insert into attend （day,empno，leave,late，absence）values(to_date（2015122，yyyy-mm-dd)，8,1，0，0）；insert into attend （day，empno，leave，late,absence)values(to_date(2014-4-24，yyyy-mmdd）,9,0，1,0）；insert into attend （day，empno，leave，late,absence)values（to_date（2015-1011，yyyy-mmdd），10，1，0,0);insert into attend (day，empno,leave,late,absence)values(to_date（201012-1，yyyymm-dd）,11,0,0,1）;19. 查询考勤信息表中的记录20. 创建用户信息表create table users(username char（10） not null，password char（30) not null,juris char（30）)tablespace test；21. 向用户信息表中插入记录insert into users(username,password,juris)values（lilong，123,所有权限）；insert into users(username,password，juris)values（lixiaolong,123，查询）;22. 向用户信息表中的性别建立B树索引create index empshow_index ON emp(sex)cablespace test23. 给员工信息表建立外键和CHCEK约束alter table empadd constraint emp_dept foreign key （deptno)references dept(deptno);alter table empadd constraint emp_sal foreign key （rank）references sal（rank);alter table empadd constraint check_sexcheck(sex=男or sex=女);24. 给职务信息表建立外键和CHCEK约束alter table dutyadd constraint duty_sal foreign key （rank）references sal(rank)；alter table dutyadd constraint check_sex1check（sex=男or sex=女）;25. 给考勤信息表建立外键约束alter table attendadd constraint attend_emp foreign key (empno)references emp（empno)；5. 主要代码1. 查询代码1) 查询员工号为1的部门号select dname from emp,deptwhere empno=1 and emp。deptno=dept.deptno;2) 查询员工赵云的部门经理是谁select manager from emp,deptwhere ename=赵云 and emp.deptno=dept。deptno； 3) 查询所有员工早退情况并按早退次数降序排列select leave，ename from emp,attendwhere emp。empno=attend。empnoorder by leave desc4) 查询员工工资等级为二的员工编号、姓名和部门名称select empno 员工编号，ename 员工姓名，dname 部门名称from emp,deptwhere rank=二 and emp。deptno=dept.deptno；5) 查询11号部门工资最高的员工编号，员工姓名，工资和年终奖金select empno 员工编号，ename 姓名，basalary 工资,bonus 年终奖金from emp,salwhere deptno=11 and basalary=(select max（basalary) from emp，sal where deptno=11）6) 查询所有员工的工资并按升序排列select basalary 工资，ename 员工姓名from emp,salwhere emp.rank=sal.rankorder by basalary asc7) 查询工资级别等于五的姓名和该人的部门经理的电话号码select ename 员工名字,mtel 经理电话from emp,deptwhere rank=五 and emp。deptno=dept。deptno8) 查询缺勤次数最少的员工名字,电话和他的部门经理select ename 员工姓名，tel 电话号码, manager 部门经理from emp,dept，attendwhere absence in (select min（absence）from attend) and emp.deptno=dept。deptno and emp.empno=attend。empno9) 查询职务编号为104的姓名,职务，工资select duname 姓名，job 职务，basalary 工资from duty，salwhere dutyno=104 and duty.rank=sal.rank10) 查询性别为男的入厂时间并按从早到晚排序select hiredate 入厂时间，ename 姓名 from empwhere sex=男order by hiredate asc;2. 创建视图1) 创建视图(sal_avg）查询各部门平均工资create or replace view sal_avgas select deptno,avg（basalary) 平均工资from emp,salwhere emp。rank=sal.rankgroup by deptno2) 调用视图（sal_avg）中的记录select from sal_avg；3. 创建存储过程1) 为员工表增加一个字段用于调整员工工资2) 创建存储过程，调整某一员工的工资（chang_sal）create or replace procedure addsalary（empnos in emp.empnotype，addsal in emp.change_sal%type)asbeginupdate emp set change_sal=change_sal+addsal where empno=empnos；end addsalary；给2号员工的工资增加200exec change_sal（2,200)；3) 创建一个调整员工部门的存储过程create or replace procedure change_empdept（empnos in emp。empno%type,deptnos in dept.deptnotype)asbeginupdate emp set deptno=deptnos where empno=empnos;exceptionwhen no_data_found thendbms_output。put_line(没有找到数据);end change_empdept;执行存储过程exec change_empdept（3,12）;查看结果4. 创建触发器当某员工辞职后删除该员工记录create or replace trigger removeafter delete on empfor each rowbegin delete emp where emp。empno=：old.empno;end；6. 权限设置及数据库备份1. 创建用户(lilong）并为其分配所有权限create user lilong identified by 123;grant create session,resource，dba to lilong;grant create any sequence to lilong; grant create any table to lilong; grant delete any table to lilong; grant insert any table to lilong； grant select any table to lilong； grant unlimited tablespace to lilong; grant execute any procedure to lilong; grant update any table to lilong； grant create any view to lilong; 2. 创建用户(lixiaolong)并为其分配查询权限create user lixiaolong identified by 123；grant create session to lixiaolong；grant select any table to lixiaolong；conn lixiaolong/123;3. 导出用户数据c：exp userid=lilong/123 file=c:data log=c:log7. 心得体会在刚开始做这个题目设计的时候，一片迷茫，不知道从何下手，在看oracle教材相关内容之后,理解了E-R图,实体和属性之间的联系，知道了画E-R图,并且仔细思考各个ER图之间的相互联系,属性与属性之间的联系，并且按照所画的ER图,建立表，然后设立主键和外键还有check约束，在做这个课程设计之前，我对这些根本一点概念都不懂，通过一步步编写代码，查找资料,不断的排错，已经能够对这些概念有了初步的了解，和编写能力，第一天建表的时候，插入数据，后来经过查询，发现一系列问题，然后重新画图建表，终于把各个表之间的联系完美的搭建了起来，通过这次课程设计,我学会了一些oracle相关语句,例如添加约束add constraint，多表链接查询，子查询，group by 。. Having，order by .。 desc/asc等等。还有在我经过多次排错做完了题目之后,让我掌握了几个排错方法，在帮助其他同学完成课程设计的时候也得到了锻炼,让我对这门技术有了浓厚的学习兴趣和动力，这次课程设计让我懂得了从理论到实践真的很重要，只有用心用力去做，一定会受益良多.8. 参考文献马晓玉，数据库管理 Oracle 10g,清华大学出版社 ，2007年王匡胤，数据库原理与设计,电子工业出版社，2011年24