sql4.txt

教学管理系统 数据库系列

/*  Table TEACHER  */
CREATE TABLE TEACHER (
  ID CHAR(6) NOT NULL,
  NAME VARCHAR(20) NOT NULL,
  DEPARTMENT CHAR(2) NOT NULL
);
/*  PrimaryKey TEACHER_ID  */
ALTER TABLE TEACHER
  ADD CONSTRAINT TEACHER_ID 
  PRIMARY KEY (ID);

/*  Table STUDENT 此表可沿用第二章所用的STUDENT表 */
CREATE TABLE STUDENT (
  ID CHAR(6) NOT NULL,
  NAME VARCHAR(20) NOT NULL,
  CLASS CHAR(4),
);
/*  PrimaryKey ID  */
ALTER TABLE STUDENT
  ADD CONSTRAINT ID 
  PRIMARY KEY (ID);

/*  Table COURSE  */
CREATE TABLE COURSE (
  ID CHAR(10) NOT NULL,
  NAME VARCHAR(50) NOT NULL,
  TEACHER CHAR(6) NOT NULL,
  CREDIT_HOUR SMALLINT DEFAULT 0,
  PERIOD SMALLINT DEFAULT 0,
  CLASSTIME VARCHAR(20),
  CLASSROOM VARCHAR(20),
  INTRO VARCHAR(4000),
  STATE CHAR(1)
);
/*  PrimaryKey COURSE_ID  */
ALTER TABLE COURSE
  ADD CONSTRAINT COURSE_ID 
  PRIMARY KEY (ID);

/*  Table COURSE_SELECT  */
CREATE TABLE COURSE_SELECT (
  ID INTEGER NOT NULL,
  COURSE CHAR(10) NOT NULL,
  STUDENT CHAR(6) NOT NULL
);
/*  PrimaryKey COURSE_SELECT_ID  */
ALTER TABLE COURSE_SELECT
  ADD CONSTRAINT COURSE_SELECT_ID 
  PRIMARY KEY (ID);

/*  Table SCORE  */
CREATE TABLE SCORE (
  ID INTEGER NOT NULL,
  STUDENT CHAR(6) NOT NULL,
  COURSE CHAR(10) NOT NULL,
  SCORE FLOAT
);
/*  PrimaryKey SCORE_ID  */
ALTER TABLE SCORE
  ADD CONSTRAINT SCORE_ID 
  PRIMARY KEY (ID);

/*  Table USER_PASS  */
CREATE TABLE USER_PASS (
  ID CHAR(6) NOT NULL,
  PASSWD CHAR(15) NOT NULL,
  AUTHORITY CHAR(1) NOT NULL
);
/*  PrimaryKey USER_ID  */
ALTER TABLE USER_PASS
  ADD CONSTRAINT USER_ID 
  PRIMARY KEY (ID);

/*  Table COURSE_STATE  */
CREATE TABLE COURSE_STATE (
  CODE CHAR(1) NOT NULL,
  DESCRIPTION VARCHAR(20) NOT NULL
);
/*  PrimaryKey COURSE_STATE_CODE  */
ALTER TABLE COURSE_STATE
  ADD CONSTRAINT COURSE_STATE_CODE 
  PRIMARY KEY (CODE);

/*  Table COUNTER 此表可沿用第二章创建的COUNTER表 */
CREATE TABLE COUNTER (
  ID CHAR(1) NOT NULL,
  COUNTER_VALUE INTEGER NOT NULL
);
/*  PrimaryKey COUNTER_ID  */
ALTER TABLE COUNTER
  ADD CONSTRAINT COUNTER_ID 
  PRIMARY KEY (ID);

/*  ForeignKey COURSE_STATE  */
ALTER TABLE COURSE
  ADD CONSTRAINT COURSE_STATE 
  FOREIGN KEY (STATE) 
  REFERENCES COURSE_STATE;
/*  ForeignKey TEACHER  */
ALTER TABLE COURSE
  ADD CONSTRAINT TEACHER 
  FOREIGN KEY (TEACHER) 
  REFERENCES TEACHER;
/*  ForeignKey COURSE1  */
ALTER TABLE COURSE_SELECT
  ADD CONSTRAINT COURSE1 
  FOREIGN KEY (COURSE) 
  REFERENCES COURSE;
/*  ForeignKey STUDENTS1  */
ALTER TABLE COURSE_SELECT
  ADD CONSTRAINT STUDENTS1 
  FOREIGN KEY (STUDENT) 
  REFERENCES STUDENT;
/*  ForeignKey COURSE2  */
ALTER TABLE SCORE
  ADD CONSTRAINT COURSE2 
  FOREIGN KEY (COURSE) 
  REFERENCES COURSE;
/*  ForeignKey STUDENTS2  */
ALTER TABLE SCORE
  ADD CONSTRAINT STUDENTS2 
  FOREIGN KEY (STUDENT) 
  REFERENCES STUDENT;