大型数据库课程设计.docx
《大型数据库课程设计.docx》由会员分享，可在线阅读，更多相关《大型数据库课程设计.docx（16页珍藏版）》请在冰豆网上搜索。
大型数据库课程设计
本科生课程设计
课程名称Oracle大型数据库课程设计
课程编号s1620025-1
学号200711621230
学生姓名王旭浩
所在专业计算机科学与技术
所在班级1072
指导教师杨亚菁
成绩
教师签字年月日
课程设计时间：
2010年06月15日
至2010年06月19日
一、设计总说明
员工管理系统数据库设计
设计说明：
本设计主要是设计数据库，并根据需要设计相关的触发器及存储过程。
在设计过程中要根据需求，设计规范的数据库。
数据库设计是指对于一个给定的应用环境，构造最优的数据库模式，建立数据库及其应用系统，达到有效地存储数据以满足用户信息要求和处理要求。
二、系统设计
1.需求分析
员工管理系统数据库设计
1.设计说明：
本设计主要是设计数据库，并根据需要设计相关的触发器及存储过程。
在设计过程中要根据需求，设计规范的数据库。
数据库设计是指对于一个给定的应用环境，构造最优的数据库模式，建立数据库及其应用系统，达到有效地存储数据以满足用户信息要求和处理要求。
2.概要设计：
E-R图设计：
步骤如下：
1定义实体：
岗位实体：
岗位（岗位编号、岗位名称、是否作废）
民族实体：
民族（民族编号、民族名称、是否作废）
部门实体：
部门（部门编号、部门名称、负责人、上级部门、地址、电话）
员工实体：
员工（员工编号、员工名称、所属部门、所属岗位、所属民族、性别、出生日期、工作日期、照片、简历）
②定义联系：
员工实体和岗位实体、民族实体、部门实体都是M:
1关系。
员工管理系统的E-R模型图
3.详细设计
根据E-R设计的数据表如下：
1岗位实体：
岗位（岗位编号、岗位名称、是否作废）
Createtabledcpost
（PostNochar（3）primarykey,
postnamevarchar2（30）,
ifvoidnumber
（1）default0constraintDCPost_Chk2check（ifvoid=0orifvoid=1）
）;
2民族实体：
民族（民族编号、民族名称、是否作废）
createtabledcnation（
nationnochar（3）primarykey,
nationnamevarchar2（30）,
ifvoidnumber
（1）default0constraintDCNation_Chk1check（ifvoid=0orifvoid=1）
）;
3部门实体：
部门（部门编号、部门名称、负责人、上级部门、地址、电话）
Createtabledepartment
（DepartmentNochar（3）primarykeynotnull,
denamevarchar2（30）,
sectionvarchar2（30）,
divisionvarchar2（30）,
addressvarchar2（30）,
telephonevarchar2（30）
）;
④员工实体：
员工（员工编号、员工名称、所属部门、所属岗位、所属民族、性别、出生日期、工作日期、照片、简历）
CreatetableEmployee
（EmployeeNovarchar2（8）primarykeynotnull,
EmployeeNamevarchar2（30）,
DepartmentNochar（3）,
PostNochar（3）,
NationNochar（3）,
sexvarchar2
（2）,
birthdaydate,
workdatedate,
photovarchar2（200）,
resumevarchar2（200）,
constraintemp_depforeignkey（DepartmentNo）referencesdepartment（DepartmentNo）,
constraintemp_postforeignkey（PostNo）referencesdcpost（PostNo）,
constraintemp_nationforeignkey（NationNo）referencesdcnation（NationNo）
）;
4．设计数据字典，触发器，存储过程设计。
①数据字典：
管理信息系统会涉及大量的静态数据，如商品单位、所属民族、所在岗位、这些数据，其数据的组织结构基本一致，有少量的数据可能今后不用，为了便于查询，可以为静态数据设置一个是否作废的属性，该属性用于历史数据查询和统计，而在事务处理中，已作废的数据不被检索和使用。
岗位字典DCPost：
名称
数据类型
大小
比例
不为空
默认值
约束
POSTNO
CHAR
3
主键
POSTNAME
VARCHAR2
30
IFVOID
NUMBER
1
0
取0或1
民族字典dcnation：
名称
数据类型
大小
比例
不为空
默认值
约束
NATIONNO
CHAR
3
主键
NATIONNAME
VARCHAR2
30
IFVOID
NUMBER
1
0
取0或1
部门字典department：
名称
数据类型
大小
比例
不为空
默认值
约束
DEPARTMENTNO
CHAR
3
主键
DENAME
VARCHAR2
30
SECTION
VARCHAR2
30
DIVISION
VARCHAR2
30
ADDRESS
VARCHAR2
30
TELEPHONE
VARCHAR2
30
员工字典employee：
名称
数据类型
大小
比例
不为空
默认值
约束
EMPLOYEENO
VARCHAR2
8
主键
EMPLOYEENAME
VARCHAR2
30
DEPARTMENTNO
CHAR
3
外键（参照department表）
POSTNO
CHAR
3
外键（参照dcpost表）
NATIONNO
CHAR
3
外键（参照dcnation表）
SEX
VARCHAR2
2
BIRTHDAY
DATE
WORKDATE
DATE
PHOTO
VARCHAR2
200
RESUME
VARCHAR2
200
设计触发器
为了保证员工编号的正确性，为员工表的插入和修改设计触发器。
④触发器，存储过程清单
（1）插入触发器代码：
rem*****************************************************
rem**创建触发器
rem**功能：
当插入员工编号时，编号必须满足为数字
rem******************************************************
CREATEorREPLACETRIGGEREmployeeIns
beforeinsert
ONEmployee
FOReachrow
declare
myErrorEXCEPTION;/*出错处理*/
varLeftchar（4）;/*编号前4位*/
varMidchar（4）;/*编号第5位*/
varRightchar（3）;/*编号后3位*/
inumber;
begin
ifLENGTH（:
new.EmployeeNo）!
=8then
RAISEmyError;
endif;
varLeft:
=SUBSTR（:
new.EmployeeNo,1,4）;
varMid:
=SUBSTR（:
new.EmployeeNo,5,1）;
varRight:
=SUBSTR（:
new.EmployeeNo,6,3）;
ifvarMid!
='-'then
RAISEmyError;
endif;
foriin1..4
loop
if（SUBSTR（varLeft,i,1）<'0'orSUBSTR（varLeft,i,1）>'9'）then
RAISEmyError;
exit;
endif;
endloop;
foriin1..3
loop
if（SUBSTR（varRight,i,1）<'0'orSUBSTR（varRight,i,1）>'9'）then
RAISEmyError;
exit;
endif;
endloop;
EXCEPTION
WHENmyError
THEN
:
new.EmployeeNo:
=NULL;
end;
（2）修改触发器代码：
功能：
当修改编号时，编号必须满足为数字
rem*****************************************************
rem**创建触发器
rem**功能：
当修改编号时，编号必须满足为数字
rem******************************************************
CREATEorREPLACETRIGGEREmployeeUpdate
beforeupdateofEmployeeNo
ONEmployee
FOReachrow
declare
myErrorEXCEPTION;/*出错处理*/
varLeftchar（4）;/*编号前4位*/
varMidchar（4）;/*编号第5位*/
varRightchar（3）;/*编号后3位*/
inumber;
begin
ifLENGTH（:
new.EmployeeNo）!
=8then
RAISEmyError;
endif;
varLeft:
=SUBSTR（:
new.EmployeeNo,1,4）;
varMid:
=SUBSTR（:
new.EmployeeNo,5,1）;
varRight:
=SUBSTR（:
new.EmployeeNo,6,3）;
ifvarMid!
='-'then
RAISEmyError;
endif;
foriin1..4
loop
if（SUBSTR（varLeft,i,1）<'0'orSUBSTR（varLeft,i,1）>'9'）then
RAISEmyError;
exit;
endif;
endloop;
foriin1..3
loop
if（SUBSTR（varRight,i,1）<'0'orSUBSTR（varRight,i,1）>'9'）then
RAISEmyError;
exit;
endif;
endloop;
EXCEPTION
WHENmyError
THEN
:
new.EmployeeNo:
=:
old.EmployeeNo;
:
new.EmployeeName:
=:
old.EmployeeName;
:
new.DepartmentNo:
=:
old.DepartmentNo;
:
new.sex:
=:
old.sex;
:
new.birthday:
=:
old.birthday;
:
new.workdate:
=:
old.workdate;
:
new.NationNo:
=:
old.NationNo;
:
new.PostNo:
=:
old.PostNo;
end;
设计存储过程：
对员工编号采用存储过程的方法输入
----------------------创建产生员工编号过程-------------------
rem*****************************************************
rem**产生员工编号过程
rem**功能：
根据输入的年份，自动产生员工编号
rem**输入参数：
sYear年份
rem**输出参数：
returnEmployeeNo员工编号
rem******************************************************
CREATEorREPLACEPROCEDUREproEmployeeNo（sYear varchar2,returnEmployeeNo OUTvarchar2）
is
xnumber; /*得到员工编号后三位的流水号*/
varEmployeeNo1varchar2（8）;/*某年份的最大员工编号*/
varEmployeeNo2varchar2（8）;/*新的员工编号*/
begin
varEmployeeNo1:
='';
selectNVL（max（EmployeeNo）,'0'）intovarEmployeeNo1
fromEmployee
whereEmployeeNolikesYear||'%';
ifvarEmployeeNo1='0'then/*无该年份的员工，编号从001开始*/
varEmployeeNo2:
=sYear||'-001';
else
x:
=to_number（SUBSTR（varEmployeeNo1,6,3））+1;
ifx<10thenvarEmployeeNo2:
=sYear||'-00'||ltrim（to_char（x））;
elsifx<100thenvarEmployeeNo2:
=sYear||'-0'||ltrim（to_char（x））;
else
varEmployeeNo2:
=sYear||'-'||ltrim（to_char（x））;
endif;
endif;
returnEmployeeNo:
=varEmployeeNo2;
endproEmployeeNo;
插入数据：
------------------------插入的数据--------------------
--------岗位表
Insertintodcpost（PostNo,postname,ifvoid）
Values（'1','生产员','1'）;
Insertintodcpost（PostNo,postname,ifvoid）
Values（'2','销售员','1'）;
-----------民族表
Insertintodcnation（NationNo,nationname,ifvoid）
Values（'1','汉族','0'）;
Insertintodcnation（NationNo,nationname,ifvoid）
Values（'2','蒙古','0'）;
-----------部门表
Insertintodepartment（DepartmentNo,dename,section,division,address,telephone）Values（'1','销售部','市场部','小宝','安B710','2475089'）;
Insertintodepartment（DepartmentNo,dename,section,division,address,telephone）Values（'1','销售部','市场部','小猫','安B710','2475088'）;
---------------员工表
InsertintoEmployee（EmployeeNo,EmployeeName,DepartmentNo,PostNo,NationNo,sex,birthday,workdate,photo,resume）
Values（'2010-001','小王','1','1','1','男',date'1988-02-01',date'2010-02-01','001.jpg','001.doc'）;
insertintoEmployee（EmployeeNo,EmployeeName,DepartmentNo,PostNo,NationNo,Sex,EmployeeDate,WorkDate,Photo,Resume）
values（:
id,'小风','1','1','1','男',date'1979-02-01',date'2006-02-01','002.jpg','002.doc'）;
5.调试与测试：
测试插入触发器：
如果插入员工表的编号不是"****-***"格式会出现错误，插入不进数据：
如果是数字则能正常插入：
select*fromemployee
测试修改触发器：
如果当修改编号时，编号不为数字则修改不了编号：
如果当修改编号时，编号格式正确则能正常修改编号：
测试产生员工编号过程：
variableidvarchar2（10）;
execproEmployeeNo（'2008',:
id）;
printid;
insertintoEmployee（EmployeeNo,EmployeeName,DepartmentNo,PostNo,NationNo,Sex,Birthday,WorkDate,Photo,Resume）
values（:
id,'小风','1','1','1','男',date'1979-02-01',date'2006-02-01','002.jpg','002.doc'）;
升级会员 