code5.txt

delphi renyuanguanlixinxioxitong

rem *****************************************************
rem ** 第五章使用的数据库脚本
rem **删除表
rem *****************************************************
drop table EmployeeOther;
drop table Employee;
drop table Emp;
drop table Department;
drop table DCNation;
drop table DCPost;


rem ****************************************************
rem ** 创建部门表
rem ****************************************************
CREATE TABLE Department (
　　DepartmentNo 	 char(3) 	    Not null ,  /*部门编号*/
　　DepartmentName 	 varchar2(30) unique NULL,       /*部门名称*/
　　UpperDepartmentNo    char(3)	　   NULL,       /*上级部门*/
    Manager              varchar(20)        NULL,       /*部门负责人*/
    Address              varchar2(30)       NULL,       /*部门地址*/
    Telephone            varchar2(20)       Null,       /*部门电话*/
        constraint Department_PK primary key (DepartmentNo) 
) ;

insert into Department values('001','江西财经大学','000', null,null,null);
insert into Department values('002','人事处',      '001',null,null,null);
insert into Department values('003','教务处',      '001',null,null,null);

insert into Department values('004','信息学院',  '001',null,null,null);
insert into Department values('005','信息系',    '004',null,null,null);
insert into Department values('006','信息研究室','005',null,null,null);
insert into Department values('007','计算机系',  '004',null,null,null);
insert into Department values('008','数量经济系','004',null,null,null);
insert into Department values('009','办公室',    '004',null,null,null);

insert into Department values('010','会计学院',   '001',null,null,null);
insert into Department values('011','会计系',     '010',null,null,null);
insert into Department values('012','会计教研室', '011',null,null,null);
insert into Department values('013','会计研究室', '012',null,null,null);
insert into Department values('014','政策研究室', '012',null,null,null);
insert into Department values('015','会电教研室', '011',null,null,null);
insert into Department values('016','审计系',     '010',null,null,null);
insert into Department values('017','审计教研室', '016',null,null,null);
insert into Department values('018','财务系',     '010',null,null,null);

insert into Department values('019','工商学院', '001',null,null,null);
insert into Department values('020','经济学院', '001',null,null,null);
insert into Department values('021','统计系',   '001',null,null,null);

rem ******************************************************
rem ** 创建民族表
rem ******************************************************
CREATE TABLE DCNation (
   NationNo 	char(3) 		Not null ,    　　　/*编号*/
   NationName 	varchar2(30)   UNIQUE	NULL ,        　　 /*名称*/
   IfVoid       Number(1) default 0 
　　　　check (IfVoid between 0 and 1)  Not null,      　　/*是否作废*/
   constraint DCNation_PK primary key(NationNo)
) ;

insert into DCNation values('001','汉族',0);
insert into DCNation values('002','壮族',0);
insert into DCNation values('003','苗族',0);

rem ******************************************************
rem ** 创建岗位表
rem ******************************************************
CREATE TABLE DCPost (
   PostNo 	char(3) 		  Not null ,      /*编号*/
   PostName 	varchar2(30)   UNIQUE	  NULL ,          /*名称*/
   IfVoid       Number(1) default 0 
　　　　check (IfVoid between 0 and 1)  　Not null,      /*是否作废*/
   constraint DCPost_PK primary key(PostNo)
) ;

insert into DCPost values('001','校长',0);
insert into DCPost values('002','书记',0);
insert into DCPost values('003','院长',0);
insert into DCPost values('004','教师',0);

rem ******************************************************
rem ** 创建员工基本信息表1
rem ******************************************************
CREATE TABLE Employee (
  EmployeeNo 	char(8) 			    Not null ,      /*编号*/
  EmployeeName 	varchar2(30) 			    Not null ,      /*姓名*/
  DepartmentNo  char(3)                             Not null ,      /*所属部门*/
  sex 	        char(2) check (sex in('男','女'))   NULL ,          /*性别*/
  EmployeeDate 	date 			            NULL ,          /*出生日期*/
  WorkDate 	date 			            NULL ,          /*工作日期*/
  NationNo      char(3)                             NULL,           /*民族*/
  PostNo        char(3)                             Null,           /*岗位*/
  sal           number                              Null,           /*基本工资*/
  comm          number                              NUll,           /*补贴*/ 
  constraint Employee_PK primary key (EmployeeNo),
  constraint EmployeeDepartment foreign key(DepartmentNo) references Department(DepartmentNo),
  constraint EmployeeNation foreign key(NationNo) references DCNation(NationNo),
  constraint EmployeePost   foreign key(PostNo)   references DCPost(PostNo)
) ;

insert into Employee values('1968-001','王红',  '001','女',TO_DATE('1952 12 21','YYYY MM DD'),TO_DATE('1968 12 21','YYYY MM DD'),'001','001',3300,200);
insert into Employee values('1968-002','黎明',  '002','女',TO_DATE('1952 11 11','YYYY MM DD'),TO_DATE('1968 12 21','YYYY MM DD'),'001','002',3500,300);
insert into Employee values('1970-001','涂洪',  '003','男',TO_DATE('1954 10 01','YYYY MM DD'),TO_DATE('1970 12 21','YYYY MM DD'),'001','003',3300,200);
insert into Employee values('1974-001','王小兵','004','男',TO_DATE('1968 05 20','YYYY MM DD'),TO_DATE('1974 12 21','YYYY MM DD'),'002','004',2800,150);
insert into Employee values('1975-001','黄奋',  '005','男',TO_DATE('1968 05 26','YYYY MM DD'),TO_DATE('1975 12 21','YYYY MM DD'),'002','004',2500,100);
insert into Employee values('1988-001','李志强','006','男',TO_DATE('1971 12 21','YYYY MM DD'),TO_DATE('1988 12 21','YYYY MM DD'),'001','004',2000,null);
insert into Employee values('1988-002','李立',  '007','女',TO_DATE('1971 10 21','YYYY MM DD'),TO_DATE('1988 12 21','YYYY MM DD'),'001','004',1900,Null);
insert into Employee values('1990-001','黄小红','008','女',TO_DATE('1972 01 09','YYYY MM DD'),TO_DATE('1990 12 21','YYYY MM DD'),'003','003',1600,null);
insert into Employee values('1990-002','黄勇',  '009','男',TO_DATE('1972 11 21','YYYY MM DD'),TO_DATE('1990 12 21','YYYY MM DD'),'001','003',1800,100);
insert into Employee values('1990-003','王红',  '010','男',TO_DATE('1972 07 15','YYYY MM DD'),TO_DATE('1990 12 21','YYYY MM DD'),'001','004',1700,NUll);

rem ******************************************************
rem ** 创建员工基本表2
rem ******************************************************
CREATE TABLE Emp (
  EmpNo 	char(8) 			    Not null ,      /*编号*/
  EName 	varchar2(30) 			    Not null ,      /*姓名*/
  DeptNo        char(3)                             Not null ,      /*所属部门*/
  sal           number                              Null,           /*基本工资*/
  comm          number                              NUll             /*补贴*/ 
);

insert into Emp values('1968-001','王红','001',3300,200);
insert into Emp values('1968-002','黎明','002',3500,300);
insert into Emp values('1970-001','涂洪','003',3300,200);
insert into Emp values('1974-001','王小兵','004',2800,150);
insert into Emp values('1975-001','黄奋','005', 2500,100);
insert into Emp values('1988-001','李志强','006',2000,null);
insert into Emp values('1988-002','李立',  '007',1900,Null);
insert into Emp values('1990-001','黄小红','008',1600,null);
insert into Emp values('1990-002','黄勇',  '009',1800,100);
insert into Emp values('1990-003','王红',  '010',1700,NUll);

rem ******************************************************
rem ** 创建员工简历和照片表
rem ******************************************************
CREATE TABLE EmployeeOther(
  EmployeeNo 	char(8) 	    Not null ,  /*编号*/
  EmployeeMemo 	varchar2(1000) 	    NULL ,      /*简历*/
  EmployeeImage  blob               NULL ,      /*照片*/
  constraint EmployeeImage_PK primary key (EmployeeNo),
  constraint EmployeeImageEmployeeFK1 foreign key(EmployeeNo) 
references Employee(EmployeeNo)
) ;

commit;