sql8.txt

员工培训管理系统

/* Table: COURSE, Owner: MISDBA */
CREATE TABLE COURSE (ID CHAR(10) NOT NULL,
        NAME VARCHAR(10) NOT NULL,
        TEACHER CHAR(6) NOT NULL,
        INTRO VARCHAR(400),
        BOOK VARCHAR(100),
        CLASSROOM VARCHAR(50),
        NUMBER SMALLINT,
        CLASSTIME VARCHAR(50),
        STATE CHAR(1) NOT NULL,
CONSTRAINT COURSE_ID PRIMARY KEY (ID));

/* Table: TRAINING_PLAN, Owner: MISDBA */
CREATE TABLE TRAINING_PLAN (ID INTEGER NOT NULL,
        PERSON CHAR(6) NOT NULL,
        COURSE CHAR(10) NOT NULL,
        SCORE SMALLINT,
        APPRAISEMENT CHAR(1),
        EXAM_DATE TIMESTAMP,
CONSTRAINT TRAINING_PLAN_ID PRIMARY KEY (ID));

/* Table: COURSE_STATE, Owner: MISDBA */
CREATE TABLE COURSE_STATE (CODE CHAR(1) NOT NULL,
        DESCRIPTION VARCHAR(10) NOT NULL,
CONSTRAINT COURSE_STATE_CODE PRIMARY KEY (CODE));

/* Table: APPRAISEMENT, Owner: MISDBA */
CREATE TABLE APPRAISEMENT (CODE CHAR(1) NOT NULL,
        DESCRIPTION VARCHAR(10) NOT NULL,
CONSTRAINT APPRAISEMENT_CODE PRIMARY KEY (CODE));

/*  ForeignKey COURSE_STATE  */
ALTER TABLE COURSE
  ADD CONSTRAINT COURSE_STATE 
  FOREIGN KEY (STATE) 
  REFERENCES COURSE_STATE;
/*  ForeignKey PERSON11  */
ALTER TABLE COURSE
  ADD CONSTRAINT PERSON11 
  FOREIGN KEY (TEACHER) 
  REFERENCES PERSON;
/*  ForeignKey APPRAISEMENT  */
ALTER TABLE TRAINING_PLAN
  ADD CONSTRAINT APPRAISEMENT 
  FOREIGN KEY (APPRAISEMENT) 
  REFERENCES APPRAISEMENT;
/*  ForeignKey COURSE  */
ALTER TABLE TRAINING_PLAN
  ADD CONSTRAINT COURSE 
  FOREIGN KEY (COURSE) 
  REFERENCES COURSE;
/*  ForeignKey PERSON12  */
ALTER TABLE TRAINING_PLAN
  ADD CONSTRAINT PERSON12 
  FOREIGN KEY (PERSON) 
  REFERENCES PERSON;