createtables.sql

这是一个科研成果管理系统,适用于中小型管理.

CREATE TABLE "dba"."code" ("code" char(3) NOT NULL DEFAULT NULL, "c_name" char(16) NOT NULL DEFAULT NULL , PRIMARY KEY ("code"));

CREATE TABLE "dba"."employees" ("em_code" char(6) NOT NULL DEFAULT NULL, "name" char(8) NOT NULL DEFAULT NULL, "sex" char(2) NOT NULL DEFAULT NULL, "birthdate" date DEFAULT NULL, "tech_title" char(8) DEFAULT NULL, "address" char(20) DEFAULT NULL, "telephone" char(20) DEFAULT NULL, "d_code" char(3) DEFAULT NULL, "picture" long binary DEFAULT NULL, "record" long varchar DEFAULT NULL , PRIMARY KEY ("em_code"));

CREATE TABLE "dba"."items" ("it_code" char(4) NOT NULL DEFAULT NULL, "itemname" char(30) NOT NULL DEFAULT NULL, "first_depart" char(20) DEFAULT NULL, "second_depart" char(20) DEFAULT NULL, "level" char(4) DEFAULT NULL, "leader" char(8) DEFAULT NULL, "fund" numeric(7,0) DEFAULT NULL, "contract_date" date DEFAULT NULL, "finish_date" date DEFAULT NULL, "status" char(4) DEFAULT NULL, "contents" long varchar DEFAULT NULL, "remarks" long varchar DEFAULT NULL , PRIMARY KEY ("it_code"));

CREATE TABLE "dba"."member" ("it_code" char(4) NOT NULL DEFAULT NULL, "em_code" char(6) NOT NULL DEFAULT NULL, "order" integer NOT NULL DEFAULT NULL, "duty" char(28) NOT NULL DEFAULT NULL , PRIMARY KEY ("it_code", "em_code") , FOREIGN KEY "for2" ("em_code" ) REFERENCES "dba"."employees" ON DELETE RESTRICT , FOREIGN KEY "[foreign key 1]" ("it_code" ) REFERENCES "dba"."items" ON DELETE RESTRICT );

CREATE TABLE "dba"."prize" ("prize_code" char(4) NOT NULL DEFAULT NULL, "prize_date" date DEFAULT NULL, "conferer" char(8) DEFAULT NULL, "prize_level" char(4) DEFAULT NULL, "it_code" char(4) DEFAULT NULL , PRIMARY KEY ("prize_code")) ;

CREATE TABLE dba.login (username varchar(20) NOT NULL, password varchar(24) NOT NULL , CONSTRAINT Primary Key PRIMARY KEY NONCLUSTERED (username)) ;