sql3.txt

学生管理系统 数据库系列

/*  Table STUDENT  */
CREATE TABLE STUDENT (
  ID CHAR(6) NOT NULL,
  NAME VARCHAR(20) NOT NULL,
  SEX CHAR(1) DEFAULT 'M' NOT NULL,
  CLASS CHAR(4),
  DEPARTMENT SMALLINT,
  BIRTHDAY TIMESTAMP,
  NATIVE_PLACE VARCHAR(50)
);
/*  PrimaryKey ID  */
ALTER TABLE STUDENT
  ADD CONSTRAINT ID 
  PRIMARY KEY (ID);

/*  Table CHANGE  */
CREATE TABLE CHANGE (
  ID SMALLINT NOT NULL,
  STUDENT CHAR(6) NOT NULL,
  CHANGE CHAR(1) NOT NULL,
  REC_TIME TIMESTAMP NOT NULL,
  DESCRIPTION VARCHAR(4000)
);
/*  PrimaryKey CHANGE_ID  */
ALTER TABLE CHANGE
  ADD CONSTRAINT CHANGE_ID 
  PRIMARY KEY (ID);

/*  Table REWARD  */
CREATE TABLE REWARD (
  ID SMALLINT NOT NULL,
  STUDENT CHAR(6) NOT NULL,
  LEVELS CHAR(1) NOT NULL,
  REC_TIME TIMESTAMP NOT NULL,
  DESCRIPTION VARCHAR(4000)
);
/*  PrimaryKey REWARD_ID  */
ALTER TABLE REWARD
  ADD CONSTRAINT REWARD_ID 
  PRIMARY KEY (ID);

/*  Table PUNISHMENT  */
CREATE TABLE PUNISHMENT (
  ID SMALLINT NOT NULL,
  STUDENT CHAR(6) NOT NULL,
  LEVELS CHAR(1) NOT NULL,
  REC_TIME TIMESTAMP NOT NULL,
  ENABLE CHAR(1) DEFAULT 'T' NOT NULL,
  DESCRIPTION VARCHAR(4000)
);
/*  PrimaryKey PUNISHMENT_ID  */
ALTER TABLE PUNISHMENT
  ADD CONSTRAINT PUNISHMENT_ID 
  PRIMARY KEY (ID);

/*  Table DEPARTMENT  */
CREATE TABLE DEPARTMENT (
  ID SMALLINT NOT NULL,
  NAME VARCHAR(50) NOT NULL
);
/*  PrimaryKey DEPARTMENT_ID  */
ALTER TABLE DEPARTMENT
  ADD CONSTRAINT DEPARTMENT_ID 
  PRIMARY KEY (ID);

/*  Table CLASS  */
CREATE TABLE CLASS (
  ID CHAR(4) NOT NULL,
  NAME VARCHAR(50) NOT NULL,
  MONITOR CHAR(6),
  DEPARTMENT SMALLINT NOT NULL
);
/*  PrimaryKey CLASS_ID  */
ALTER TABLE CLASS
  ADD CONSTRAINT CLASS_ID 
  PRIMARY KEY (ID);

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

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

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

/*  Table COUNTER  */
CREATE TABLE COUNTER (
  ID CHAR(1) NOT NULL,
  COUNTER_VALUE SMALLINT NOT NULL
);
/*  PrimaryKey COUNTER_ID  */
ALTER TABLE COUNTER
  ADD CONSTRAINT COUNTER_ID 
  PRIMARY KEY (ID);

/*  ForeignKey CHANGE  */
ALTER TABLE CHANGE
  ADD CONSTRAINT CHANGE 
  FOREIGN KEY (CHANGE) 
  REFERENCES CHANGE_CODE;
/*  ForeignKey STUDENT1  */
ALTER TABLE CHANGE
  ADD CONSTRAINT STUDENT1 
  FOREIGN KEY (STUDENT) 
  REFERENCES STUDENT;
/*  ForeignKey MONITOR  */
ALTER TABLE CLASS
  ADD CONSTRAINT MONITOR 
  FOREIGN KEY (MONITOR) 
  REFERENCES STUDENT;
/*  ForeignKey PUNISH  */
ALTER TABLE PUNISHMENT
  ADD CONSTRAINT PUNISH 
  FOREIGN KEY (LEVELS) 
  REFERENCES PUNISH_LEVELS;
/*  ForeignKey STUDENT3  */
ALTER TABLE PUNISHMENT
  ADD CONSTRAINT STUDENT3 
  FOREIGN KEY (STUDENT) 
  REFERENCES STUDENT;
/*  ForeignKey REWARD  */
ALTER TABLE REWARD
  ADD CONSTRAINT REWARD 
  FOREIGN KEY (LEVELS) 
  REFERENCES REWARD_LEVELS;
/*  ForeignKey STUDENT2  */
ALTER TABLE REWARD
  ADD CONSTRAINT STUDENT2 
  FOREIGN KEY (STUDENT) 
  REFERENCES STUDENT;
/*  ForeignKey CLASS  */
ALTER TABLE STUDENT
  ADD CONSTRAINT CLASS 
  FOREIGN KEY (CLASS) 
  REFERENCES CLASS;