Oracle员工工资管理系统(共29页).docx

精选优质文档-倾情为你奉上大型数据库技术设计报告题 目: 员工工资管理系统数据库设计 学生姓名: 李龙 学 号: 0 专业班级: 网工13101班 指导教师: 易明 设计时间: 2015年下学期 指导老师意见:签名:日期:评定成绩:专心-专注-专业目录1. 需求分析实现员工基本信息管理、工资信息管理、部门信息管理等功能，包括员工信息的添加、修改、删除及查询；工资信息的添加、修改、删除及查询等。2. 概念模型设计1. 员工信息E-R图（如图1所示）图 1员工信息E-R图2. 部门信息E-R图（如图2所示）图 2部门信息E-R图3. 工资实体局部E-R模型设计（如同3所示）图 3工资信息E-R图4. 职务信息E-R图（如图4所示）图 4职务信息E-R图5. 考勤信息E-R图（如图5所示）图 5考勤信息E-R图6. 整体简洁E-R图（如图6所示）图 6整体简洁E-R图7. 用户信息E-R图（如图7所示）图 7用户信息E-R图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,'刘备','男','',TO_DATE('2009-02-12','yyyy-mm-dd'),10,'三');insert into emp values(2,'吕布','男','',TO_DATE('2010-03-02','yyyy-mm-dd'),10,'六');insert into emp values(3,'貂蝉','女','',TO_DATE('2011-11-15','yyyy-mm-dd'),10,'五');insert into emp values(4,'赵云','男','',TO_DATE('2012-11-11','yyyy-mm-dd'),11,'六');insert into emp values(5,'诸葛亮','男','',TO_DATE('2014-04-19','yyyy-mm-dd'),11,'四');insert into emp values(6,'关羽','男','',TO_DATE('2013-05-14','yyyy-mm-dd'),11,'五');insert into emp values(7,'黄忠','男','',TO_DATE('2014-02-12','yyyy-mm-dd'),12,'二');insert into emp values(8,'鲁肃','男','',TO_DATE('2014-02-13','yyyy-mm-dd'),12,'九');insert into emp values(9,'周瑜','男','',TO_DATE('2012-04-14','yyyy-mm-dd'),12,'十');insert into emp values(10,'曹操','男','',TO_DATE('2012-02-15','yyyy-mm-dd'),13,'六');insert into emp values(11,'颜良','女','',TO_DATE('2014-06-13','yyyy-mm-dd'),13,'七');insert into emp values(12,'文丑','男','',TO_DATE('2013-03-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,'项目部','李龙','');insert into dept(deptno,dname,manager,mtel)values(11,'人事部','李梦龙','');insert into dept(deptno,dname,manager,mtel)values(12,'财务部','李欣龙','');insert into dept(deptno,dname,manager,mtel)values(13,'IT部','李龙龙','');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,'李龙','总经理','','男','一');insert into duty(dutyno,duname,job,tel,sex,rank) values(102,'李小龙','副经理','','男','二');insert into duty(dutyno,duname,job,tel,sex,rank) values(103,'李子龙','秘书','','女','三');insert into duty(dutyno,duname,job,tel,sex,rank) values(104,'李梦龙','总经理','','女','一');insert into duty(dutyno,duname,job,tel,sex,rank) values(105,'李泽龙','助理','','女','四');insert into duty(dutyno,duname,job,tel,sex,rank) values(106,'李云龙','秘书','','女','五');insert into duty(dutyno,duname,job,tel,sex,rank) values(107,'李欣龙','总经理','','女','二');insert into duty(dutyno,duname,job,tel,sex,rank) values(108,'李好龙','副经理','','男','三');insert into duty(dutyno,duname,job,tel,sex,rank) values(109,'李坏龙','秘书','','男','七');insert into duty(dutyno,duname,job,tel,sex,rank) values(110,'李龙龙','总经理','','男','一');insert into duty(dutyno,duname,job,tel,sex,rank) values(111,'李章龙','秘书','','女','八');insert into duty(dutyno,duname,job,tel,sex,rank) values(112,'李宇龙','助理','','女','九');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('2010-12-3','yyyy-mm-dd'),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-10-12','yyyy-mm-dd'),2,1,0,1);insert into attend (day,empno,leave,late,absence)values(to_date('2014-11-11','yyyy-mm-dd'),3,0,0,0);insert into attend (day,empno,leave,late,absence)values(to_date('2013-7-14','yyyy-mm-dd'),4,1,0,0);insert into attend (day,empno,leave,late,absence)values(to_date('2012-11-12','yyyy-mm-dd'),5,1,1,0);insert into attend (day,empno,leave,late,absence)values(to_date('2013-11-14','yyyy-mm-dd'),6,1,0,0);insert into attend (day,empno,leave,late,absence)values(to_date('2014-6-21','yyyy-mm-dd'),7,0,0,0);insert into attend (day,empno,leave,late,absence)values(to_date('2015-1-22','yyyy-mm-dd'),8,1,0,0);insert into attend (day,empno,leave,late,absence)values(to_date('2014-4-24','yyyy-mm-dd'),9,0,1,0);insert into attend (day,empno,leave,late,absence)values(to_date('2015-10-11','yyyy-mm-dd'),10,1,0,0);insert into attend (day,empno,leave,late,absence)values(to_date('2010-12-1','yyyy-mm-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 deptno 2) 调用视图（sal_avg）中的记录select * from sal_avg;3. 创建存储过程1) 为员工表增加一个字段用于调整员工工资2) 创建存储过程，调整某一员工的工资（chang_sal）create or replace procedure addsalary(empnos in emp.empno%type,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.deptno%type)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图，并且仔细思考各个E-R图之间的相互联系，属性与属性之间的联系，并且按照所画的E-R图，建立表，然后设立主键和外键还有check约束，在做这个课程设计之前，我对这些根本一点概念都不懂，通过一步步编写代码，查找资料，不断的排错，已经能够对这些概念有了初步的了解，和编写能力，第一天建表的时候，插入数据，后来经过查询，发现一系列问题，然后重新画图建表，终于把各个表之间的联系完美的搭建了起来，通过这次课程设计，我学会了一些oracle相关语句，例如添加约束add constraint，多表链接查询，子查询，group by . Having，order by . desc/asc等等。还有在我经过多次排错做完了题目之后，让我掌握了几个排错方法，在帮助其他同学完成课程设计的时候也得到了锻炼，让我对这门技术有了浓厚的学习兴趣和动力，这次课程设计让我懂得了从理论到实践真的很重要，只有用心用力去做，一定会受益良多。8. 参考文献马晓玉，数据库管理 Oracle 10g，清华大学出版社 ，2007年王匡胤，数据库原理与设计，电子工业出版社，2011年