kygl.sql

PB科研管理系统

drop table employees;
CREATe TABLE "DBA"."employees"
            ("em_code" char(6)   not null,
             "name" char(8)      not null,
             "sex" char(2)       not null,
             "birthdate" date,
             "tech_title" char(8),
             "address" char(20) ,
             "telephone" char(20),
             "d_code"char(3),
             "picture" long binary,
             "record" long varchar,
             PRIMARY KEY("em_code"));
drop table items;              
CREATe TABLE "DBA"."ITEMS"
            ("it_code" char(4) not null,
             "itemname" char(30) not null,
             "first_depart" char(20),
             "second_depart" char(20),
             "level" char(4),
             "leader" char(8),
             "fund" numeric(7,0),
             "contract_date" date,
             "finish_date" date,
             "status" char(4),
             "contents" long varchar,
             "remarks" long varchar,
             PRIMARY KEY("IT_CODE"));
drop table member;
CREATe TABLE "DBA". "MEMBER"
            ("it_code" char(4) not null,
             "em_code"  char(6) not null,
             "order" integer,
             "duty" char(28),
            primary key ("it_code","em_code"));
drop table prize;
CREATE TABLE "DBA"."PRIZE"
            ("prize_code" char(4) not null,
             "prize_date" date,
             "conferer" char(8),
             "prize_level" char(4),
             "it_code" char(4),
        	    PRIMARY KEY("PRIZE_CODE"));
drop table code;
CREATE TABLE "DBA"."CODE"
            ("code" char(3) not null,
             "c_name" char(16) not null,
            PRIMARY KEY ("code"));
drop table yhkl;
create table yhkl(
yhlb   char(10)      not null,  //用户类别
yhmc   char(10)     not null,  //用户名称
yhkl   char(20)      not null,  //用户口令
primary key(yhmc));


insert into "employees" ("em_code","name","sex","birthdate", "tech_title","telephone","d_code")
	Values ('100001','刘俊海','男','1948/09/12','B01','39314588','A04');   
insert into "employees" ("em_code","name","sex","birthdate","tech_title","telephone","d_code")
	values ('100002','王沛奇','男','1963/11/24','B02','3878866','A04');
insert into "employees"("em_code","name","sex","birthdate", "tech_title","telephone","d_code")
	values ('100003','丁宏伟','男','1970/04/27','B03','37626346','A04');
insert into "employees" ("em_code","name","sex","birthdate", "tech_title","telephone","d_code")
	values ('100004','郝丽萍','女','1968/07/05','B03','','A04');
insert into "employees" ("em_code","name","sex","birthdate", "tech_title","telephone","d_code")
	values ('100005','张志文','男','1958/08/21','B02','38868733','A03');
insert into "employees" ("em_code","name","sex","birthdate", "tech_title","telephone","d_code")
	values ('100006','严华武','男','1949/05/16','B02','37754685','A03');
insert into "employees" ("em_code","name","sex","birthdate", "tech_title","telephone","d_code")
	values ('100007','杨文彬','男','1965/06/18','B05','38848573','A04');
insert into "employees" ("em_code","name","sex","birthdate", "tech_title","telephone","d_code")
	values ('100008','赵磊','男','1975/01/30','B04','','A04');
insert into "employees" ("em_code","name","sex","birthdate", "tech_title","telephone","d_code")
	values ('100009','陈伟峰','男','1959/05/24','B02','36937222','A04');   
insert into "employees" ("em_code","name","sex","birthdate", "tech_title","telephone","d_code")
	values ('100010','潘浩林','男','1968/03/18','B03','34456832','A03');   
insert into "employees" ("em_code","name","sex","birthdate", "tech_title","telephone","d_code")
	values ('100011','徐艳丽','男','1969/06/28','B06','36590562','A03');     
insert into "employees" ("em_code","name","sex","birthdate", "tech_title","telephone","d_code")
	values ('100012','刘刚','男','1971/07/14','B0','3481908','A04');   

insert into "items"("it_code","itemname","first_depart","second_depart","level","leader","fund","contract_date","status")
	values ('2001','电子售票订票系统统计子系统','铁道部','A04','部级','100009',10,'1996/12/02','在研');
insert into "items"("it_code","itemname","first_depart","second_depart","level","leader","fund","contract_date","finish_date","status")
	values ('2002','饲料配料自动化','','A04','','100007',5,'1990/03/15','1992/12/22','完成');
insert into "items"("it_code","itemname","first_depart","second_depart","level","leader","fund","contract_date","finish_date","status")
	values ('2003','制药厂管理信息系统','河北省','A04','','100001',10,'1987/05/12','1989/12/22','完成');
insert into "items"("it_code","itemname","first_depart","second_depart","level","leader","fund","contract_date","finish_date","status")
	values ('2004','电厂自动控制系统','电力部','A03','部级','100005',15,'1995/02/18','1996/12/20','完成');
insert into "items"("it_code","itemname","first_depart","second_depart","level","leader","fund","contract_date","status")
	values ('2005','电子售票系统20版的优化','铁道部','A03','部级','100002',10,'1998/01/18','在研');

insert into "MEMBER"("it_code","em_code","order","duty")
values ('2001','100009',1,'负责人');
insert into "MEMBER"("it_code","em_code","order","duty")
values ('2001','100002',2,'系统设计');
insert into "MEMBER"("it_code","em_code","order","duty")
values ('2001','100007',3,'软件设计');
insert into "MEMBER"("it_code","em_code","order","duty")
values ('2002','100001',1,'负责人');
insert into "MEMBER"("it_code","em_code","order","duty")
values ('2002','100002',2,'硬件设计');
insert into "MEMBER"("it_code","em_code","order","duty")
values ('2002','100010',3,'软件设计');
insert into "MEMBER"("it_code","em_code","order","duty")
values ('2002','100012',4,'系统测试');
insert into "MEMBER"("it_code","em_code","order","duty")
values ('2003','100009',1,'总体设计');
insert into "MEMBER"("it_code","em_code","order","duty")
values ('2003','100003',2,'网络设计');
insert into "MEMBER"("it_code","em_code","order","duty")
values ('2003','100004',3,'软件设计');
insert into "MEMBER"("it_code","em_code","order","duty")
values ('2003','100011',4,'系统维护');
insert into "MEMBER"("it_code","em_code","order","duty")
values ('2004','10005',1,'总体设计');
insert into "MEMBER"("it_code","em_code","order","duty")
values ('2004','100006',2,'');
insert into "MEMBER"("it_code","em_code","order","duty")
values ('2004','100010',3,'');
insert into "MEMBER"("it_code","em_code","order","duty")
values ('2004','100011',4,'');
insert into "MEMBER"("it_code","em_code","order","duty")
values ('2005','100002',1,'总体设计');
insert into "MEMBER"("it_code","em_code","order","duty")
values ('2005','100009',2,'');
insert into "MEMBER"("it_code","em_code","order","duty")
values ('2005','100008',3,'');

insert into "prize"("prize_code","prize_date","conferer","prize_level","it_code")
values ('3001','1996/3/5','河北省','c13','2002');
insert into "prize"("prize_code","prize_date","conferer","prize_level","it_code")
values ('3002','1997/12/16','学院','c31','2004');
insert into "prize"("prize_code","prize_date","conferer","prize_level","it_code")
values ('3003','1997/12/28','电力部','c12','2003');

insert into "code"("code","c_name")
values ('A01','化工系');
insert into "code"("code","c_name")
values ('A02','机械系');
insert into "code"("code","c_name")
values ('A03','电子系');
insert into "code"("code","c_name")
values ('A04','计算机系');
insert into "code"("code","c_name")
values ('A05','经济管理系');
insert into "code"("code","c_name")
values ('B01','教授');
insert into "code"("code","c_name")
values ('B02','副教授');
insert into "code"("code","c_name")
values ('B03','讲师');
insert into "code"("code","c_name")
values ('B04','助教');
insert into "code"("code","c_name")
values ('B05','高级工程师');
insert into "code"("code","c_name")
values ('B06','工程师');
insert into "code"("code","c_name")
values ('C01','国家一等奖');
insert into "code"("code","c_name")
values ('C02','国家二等奖');
insert into "code"("code","c_name")
values ('C03','国家三等奖'); 
insert into "code"("code","c_name")
values ('C11','省部一等奖');
insert into "code"("code","c_name")
values ('C12','省部二等奖');
insert into "code"("code","c_name")
values ('C13','省部三等奖');
insert into "code"("code","c_name")
values ('C31','院一等奖');
insert into "code"("code","c_name")
values ('C32','院二等奖');
insert into "code"("code","c_name")
values ('C33','院三等奖');