创建表格的sql.txt

关于如何在pb8.0中进行数据库开发及实例.

6.3.1 创建员工基本状况表格PERSONNEL
CREATE TABLE "TMS".PERSONNEL 
(
EMP_NO  NUMBER(6,0)  NOT NULL, 
EMP_NAME  VARCHAR2(10)  NULL,
DEPT_ID  NUMBER(3,0)  NULL,
EMP_XL  VARCHAR2(10)  NULL,
EMP_GENDER  VARCHAR2(2)  NULL, 
EMP_BIRTHDAY  DATE  NULL, 
EMP_HOMETOWN  VARCHAR2(8)  NULL, 
EMP_COUNTRY  VARCHAR2(10)  NULL, 
EMP_NATION  VARCHAR2(10)  NULL, 
EMP_ID  NUMBER(20,0)  NULL, 
EMP_MARRIAGE  VARCHAR2(2)  NULL, 
EMP_HEALTH  VARCHAR2(20)  NULL, 
EMP_STARTWORK  DATE  NULL, 
EMP_STATE  VARCHAR2(10)  NULL, 
EMP_STATE_DATE  DATE  NULL,
EMP_HOMEADRESS  VARCHAR2(10)  NULL, 
EMP_TELENO  NUMBER(10,0)  NULL, 
EMP_EMAIL  VARCHAR2(20)  NULL, 
JOB_ID  NUMBER(3,0)  NULL,  
CONSTRAINT  PERSONNEL_EMP_NO_PK  PRIMARY KEY (EMP_NO),
CONSTRAINT   PERSONNEL_DEPT_ID _FK  FOREIGN KEY (DEPT_ID) 
               REFERENCES  "TMS"."DEPT"( DEPT_ID),
CONSTRAINT   PERSONNEL_JOB_ID _FK  FOREIGN KEY (JOB_ID) 
               REFERENCES  "TMS"."JOB"( JOB_ID)
)
TABLESPACE "USER_DATA";
6.3.2 创建员工成绩表格SCORE
CREATE TABLE "TMS".SCORE
(
EMP_NO  NUMBER(6)  NOT NULL,
COURSE_ID  NUMBER(3)  NULL,  
S_DATE  DATE  NULL,  
S_PLACE  VARCHAR2(10)  NULL, 
TEACHER_ID  NUMBER(6)  NULL, 
SCORE_1  NUMBER(3)  NULL, 
SCORE_2  NUMBER(3)  NULL, 
SCORE_NOTES  VARCHAR2(10)  NULL, 
PASS  NUMBER(1)  NULL,  
CONSTRAINT  SCORE_EMP_NO_PK  PRIMARY KEY (EMP_NO) ,
CONSTRAINT  SCORE_COURSE_ID_FK  FOREIGN KEY  (COURSE_ID)
               REFERENCES  "TMS"."COURSE"(COURSE_ID), 
CONSTRAINT  SCORE_TEACHER_ID_FK  FOREIGN KEY (TEACHER_ID) 
               REFERENCES "TMS"."TEACHER"(TEACHER_ID)
) 
TABLESPACE "USER_DATA";
6.3.3 创建课程表格COURSE
CREATE TABLE  "TMS".COURSE
(
COURSE_ID  NUMBER(3)  NULL, 
SUBJECT  VARCHAR2(10)  NULL, 
COURSE_CNAME  VARCHAR2(20)  NULL, 
COURSE_ENAME  VARCHAR2(10)  NULL, 
COURSE_DESC  VARCHAR2(40)  NULL, 
COURSE_DURATION  NUMBER(3)  NULL, 
COURSE_BOOK  VARCHAR2(10)  NULL, 
COURSE_EQU  NUMBER(3)  NULL, 
COURSE_BEFORE  NUMBER(3)  NULL, 
COURSE_MARK  NUMBER(1)  NULL, 
DEPT_ID  NUMBER(3)  NULL,  
CONSTRAINT  COURSE_COURSE_ID_PK  PRIMARY KEY (COURSE_ID)
)
TABLESPACE "USER_DATA";
6.3.4 创建工作岗位情况表JOB
CREATE  TABLE  "TMS". JOB
(
JOB_ID  NUMBER(3)  NOT NULL,
JOB_NAME  VARCHAR2(10)  NULL, 
JOB_POWER  VARCHAR2(20)  NULL, 
CONSTRAINT  JOB_JOB_ID_PK  PRIMARY KEY (JOB_ID)
)  
TABLESPACE "USER_DATA";
6.3.5 创建部门信息表DEPT
CREATE  TABLE  "TMS".DEPT
(
DEPT_ID  NUMBER(3)  NOT NULL,
DEPT_NAME  VARCHAR2(10)  NULL, 
DEPT_MANAGER  NUMBER(6)  NULL, 
DEPT_VICEMANAGER  NUMBER(6)  NULL,
CONSTRAINT  DEPT_DEPT_ID_PK  PRIMARY KEY (DEPT_ID),
CONSTRAINT  DEPT_MANAGER_FK  FOREIGN KEY (DEPT_MANAGER) 
               REFERENCES  "TMS"."PERSONNEL"(EMP_NO),
CONSTRAINT  DEPT_VICEMANAGER_FK FOREIGN KEY 
               (DEPT_VICEMANAGER) 
               REFERENCES  "TMS"."PERSONNEL"(EMP_NO)
)  
TABLESPACE "USER_DATA";
6.3.6创建培训需求表COURSE_NEED
CREATE TABLE "TMS".COURSE_NEED 
(
COURSE_ID  NUMBER(3)  NULL, 
COURSE_NEED  NUMBER(6)  NULL
CONSTRAINT  COURSE_NEED_COURSE_ID _FK  FOREIGN KEY (COURSE_ID) 
               REFERENCES  "TMS"."COURSE"( COURSE_ID)
CONSTRAINT  COURSE_NEED_COURSE_NEED _FK  FOREIGN KEY
              (COURSE_NEED)  REFERENCES  "TMS"."PERSONNEL"( EMP_NO)
) 
TABLESPACE "USER_DATA";
6.3.7创建企业培训计划表COURSE_AVAILABLE
CREATE TABLE "TMS".COURSE_AVAILABLE
(
COURSE_ID  VARCHAR2(10)  NULL, 
COURSE_DATE_BEGIN  DATE  NULL, 
COURSE_DATE_OVER  DATE  NULL, 
TEACHER_ID  NUMBER(6)  NULL, 
COURSE_PERSONNUMBER  NUMBER(2)  NULL, 
COURSE_PLACE  VARCHAR2(10)  NULL,  
CONSTRAINT COURSE_AVAILABLE_COURSE_ID_PK PRIMARY KEY (COURSE_ID)
CONSTRAINT  COURSE_AVAILABLE_COURSE_ID _FK  FOREIGN KEY
               (COURSE_ID)  REFERENCES  "TMS"."COURSE"( COURSE_ID)
CONSTRAINT  COURSE_AVAILABLE_TEACHER_ID _FK  FOREIGN KEY
               (TEACHER_ID) REFERENCES  "TMS"."TEACHER"( TEACHER_ID)
) 
TABLESPACE "USER_DATA";
6.3.8创建个人培训计划表PERSONNEL_PLAN
CREATE  TABLE  "TMS".PERSONNEL_PLAN 
(
EMP_NO  NUMBER(6)  NULL, 
COURSE_ID  NUMBER(3)  NULL, 
COURSE_DATE_BEGIN  DATE  NULL, 
COURSE_DATE_OVER  DATE  NULL
CONSTRAINT  PERSONNEL_PLAN_EMP_NO_FK  FOREIGN  KEY (EMP_NO) 
               REFERENCES  "TMS"."PERSONNEL"(EMP_NO),
CONSTRAINT  PERSONNEL_PLAN _COURSE_ID _FK  FOREIGN KEY
              (COURSE_ID)  REFERENCES  "TMS"."COURSE"( COURSE_ID)
)
TABLESPACE "USER_DATA";
6.3.9创建课程评价表COURSE_EVALUATION
CREATE  TABLE  "TMS".COURSE_EVALUATION 
(
COURSE_ID  NUMBER(3)  NULL, 
EVALUATION  VARCHAR2(40)  NULL,  
EVALUATION_DATE  DATE  NULL
CONSTRAINT  COURSE_EVALUATION _COURSE_ID _FK  FOREIGN KEY
              (COURSE_ID)  REFERENCES  "TMS"."COURSE"( COURSE_ID)
) 
TABLESPACE "USER_DATA";
6.3.10创建教员信息表TEACHER
CREATE  TABLE  "TMS".TEACHER 
(
TEACHER_ID  NUMBER(6)  NOT NULL, 
TEACHER_NAME  VARCHAR2(10)  NULL,
TEACHER_XL  VARCHAR2(10)  NULL, 
TEACHER_DATE_START  DATE  NULL, 
TEACHER_EVALUATION  VARCHAR2(40)  NULL,  
CONSTRAINT TEACHER_TEACHER_ID_PK PRIMARY KEY (TEACHER_ID)
)
TABLESPACE "USER_DATA";
6.3.11创建培训资源信息表TM_RESOURCE
CREATE  TABLE "TMS".TM_RESOURCE 
(
RESOURCE_ID  NUMBER(3)  NULL, 
RESOURCE_NAME  VARCHAR2(10)  NULL, 
RESOURCE_MARK  NUMBER  NULL, 
RESOURCE_PRICE  VARCHAR2(10)  NULL, 
RESOURCE_QUATITY  NUMBER(2)  NULL, 
RESOURCE_REM  VARCHAR2(40)  NULL, 
CONSTRAINT  RESOURCE_RESOURCE_ID_PK 
               PRIMARY KEY  (RESOURCE_ID)
)
TABLESPACE "USER_DATA";
6.3.12创建教材信息表BOOK
CREATE  TABLE  "TMS".BOOK  
(
BOOK_ID  NUMBER(3)  NULL, 
BOOK_NAME  VARCHAR2(20)  NULL, 
BOOK_AUTHER  VARCHAR2(10)  NULL, 
BOOK_STATE  NUMBER(1)  NULL, 
BOOK_COURSE_ID  NUMBER(3)  NULL,
BOOK_QUATITY  NUMBER(3)  NULL,
BOOK_PRICE  NUMBER(3)  NULL,
CONSTRAINT  BOOK_BOOK_ID_PK  
               PRIMARY KEY (BOOK_ID),
CONSTRAINT  BOOK_COURSE_ID_FK  FOREIGN  KEY (BOOK_COURSE_ID) 
               REFERENCES "TMS"."COURSE"(COURSE_ID)
)TABLESPACE "USER_DATA";
6.3.13创建管理TMS系统的用户口令表USER_PSWD。
CREATE  TABLE  "TMS".USER_PSWD 
(
TMS_USER  VARCHAR2(20)  NULL, 
TMS_PSWD  VARCHAR2(10)  NULL,  
CONSTRAINT  TMS_USER_PK  PRIMARY KEY (TMS_USER)
)TABLESPACE "USER_DATA";