师生帮手（2）

昨天设计出来了数据的第一版，现在飞哥和根儿正在修改中，两位大神保佑找到问题不要太多吐槽，我要开始设计服务器模型了，请把每一个epoll用正确，这非常重要，采用什么模式呢，ET还是LT。读写的时候细节。UDP（内网负载发送消息）和TCP（工作过程）等，下午服务器交工第一版。

放出数据库总体图：

代码

DROP DATABASE if EXISTS TSHH;

CREATE DATABASE TSHH;                      /*创建数据库*/
USE  TSHH;                                  

DROP TABLE IF EXISTS ts;                  /*教师学生信息表*/
DROP TABLE IF EXISTS ts_passwd;           /*教师学生密码表*/
DROP TABLE IF EXISTS teacher_plan;        /*教学计划安排表*/
DROP TABLE IF EXISTS single_class;        /*单个班级表*/
DROP TABLE IF EXISTS big_class;           /*单个班级组成的大班*/
DROP TABLE IF EXISTS college;             /*学院*/
DROP TABLE IF EXISTS dept;                /*专业*/
DROP TABLE IF EXISTS course;              /*班级*/
DROP TABLE IF EXISTS schedule_course;     /*课表*/
DROP TABLE IF EXISTS datad;               /*上传的资料*/
DROP TABLE IF EXISTS download;            /*下载的资料*/
DROP TABLE IF EXISTS question;            /*问题*/
DROP TABLE IF EXISTS answer;              /*答案*/
DROP TABLE IF EXISTS message;             /*消息*/
DROP TABLE IF EXISTS rollcall;            /*点名记录*/
DROP TABLE IF EXISTS rollcall_nothere;    /*点名不在的人*/
DROP TABLE IF EXISTS classroom;           /*教室的地图*/

/*教师学生信息表*/
CREATE TABLE ts (
  ts_id                 INT NOT NULL AUTO_INCREMENT,  /*主键*/
  ts_num                VARCHAR(20),                  /*老师教工号/学生学号*/
  ts_name               VARCHAR(20),                  /*姓名*/
  college_id            INT,                          /*所在学院  外键*/
  dept_id               INT,                          /*所在系    外键*/
  single_class_id       INT,                          /*学生的所在班级  外键*/
  ts_introduction       VARCHAR(200),                  /*自我介绍*/
  ts_head_image         LONGBLOB,                      /*头像*/
  PRIMARY KEY (ts_id)
);

/*教师学生密码表*/
CREATE TABLE ts_passwd (
  ts_passwd_id           INT NOT NULL AUTO_INCREMENT, /*主键*/
  ts_id                  INT,                         /*老师学生ID 外键*/
  ts_passwd_content      VARCHAR(100),               /*密码*/
  PRIMARY KEY (ts_passwd_id)
);

/*教学计划安排表*/
CREATE TABLE teacher_plan (
  teacher_plan_id         INT NOT NULL AUTO_INCREMENT, /*主键*/
  ts_id                   INT,                         /*老师ID 外键*/
  teacher_plan_week       INT,                         /*周*/
  schedule_course_id      INT,                         /*确定每一节课 外键*/
  teacher_plan_content    VARCHAR(200),                /*安排的上课内容，预习内容*/
  teacher_plan_type       SMALLINT,                   /* 0:上课 1：实验 2：上机*/
  PRIMARY KEY (teacher_plan_id)
);

 /*单个班级表*/
CREATE TABLE single_class (
  single_class_id         INT NOT NULL AUTO_INCREMENT,  /*主键*/
  single_class_name       VARCHAR(20),                  /*班级名称 eg:1404*/
  dept_id                 INT,                          /*所在系    外键*/
  college_id              INT ,                         /*所在学院  外键*/
  PRIMARY KEY (single_class_id)
);

/*单个班级组成的大班*/
CREATE TABLE big_class (
  big_class_id            INT NOT NULL AUTO_INCREMENT,  /*主键*/
  big_class_num           INT,                          /*大班的编号*/
  big_class_name          VARCHAR(40),                  /*大班名称*/
  ts_id                   INT,                          /*老师ID 外键*/
  single_class_id         INT,                          /*单个班级ID* 外键*/
  course_id               INT,                          /*课程ID    外键*/
  PRIMARY KEY (big_class_id)
);

/*学院*/
CREATE TABLE college (
  college_id              INT NOT NULL AUTO_INCREMENT,  /*主键*/
  college_name            VARCHAR(20),                  /*学院名称*/
  college_introduction    VARCHAR(200) ,                 /*学院介绍*/
  PRIMARY KEY (college_id)
);


/*专业*/
CREATE TABLE dept (
  dept_id                 INT NOT NULL AUTO_INCREMENT, /*主键*/
  college_id              INT,                         /*属于的学院 外键*/
  dept_name               VARCHAR(20),                 /*专业名称*/
  dept_introduction       VARCHAR(200) ,                /*专业介绍*/
  PRIMARY KEY (dept_id)
);

/*班级*/
CREATE TABLE course (
  course_id               INT NOT NULL AUTO_INCREMENT, /*主键*/
  dept_id                 INT,                         /*属于的系  外键*/
  course_name             VARCHAR(20),                 /*课程名*/
  course_introduction     VARCHAR(200) ,                /*课程介绍*/
  PRIMARY KEY (course_id)
);

/*课表*/
CREATE TABLE schedule_course (
  schedule_course_id      INT NOT NULL AUTO_INCREMENT, /*主键*/
  dept_id                 INT,                          /*属于的系 外键*/
  schedule_course_day     INT,                          /*1-7 分别表示周一到周天*/
  schedule_course_course  INT,                          /*1-4 分别标识每天1-4节课*/
  schedule_course_content varchar(200) ,                /*具体的课表内容*/
  PRIMARY KEY (schedule_course_id)
);

/*上传的资料*/
CREATE TABLE datad (
  datad_id                INT NOT NULL AUTO_INCREMENT, /*主键*/
  ts_id                   INT,                         /*老师/学生ID  外键*/
  datad_uploadtime        DATETIME,                    /*资料上传时间*/
  datad_size              INT,                         /*资料大小*/
  datad_content           VARCHAR(200),                /*资料的路径  */
  datad_downloadtimes     INT,                          /*下载次数*/
  PRIMARY KEY (datad_id)
);

/*下载的资料*/
CREATE TABLE download (
  download_id             INT NOT NULL AUTO_INCREMENT, /*主键*/
  datad_id                INT,                         /* 资料序号  外键*/
  download_time           DATETIME ,                    /*下载时间*/
  PRIMARY KEY (download_id)
);

/*问题*/
CREATE TABLE question (
  question_id             INT NOT NULL AUTO_INCREMENT, /*主键*/
  ts_id                   INT,                         /*提问者*/
  question_ask_time       DATETIME,                    /*问题的提出时间*/
  question_content        VARCHAR(200),                /*问题的内容*/
  question_answertimes    INT,                         /*问题的回答个数*/
  question_type           SMALLINT,                     /*数字分别表示不同的 科目*/
  big_class_id            INT,                           /*问题提出的大班ID  外键*/
  PRIMARY KEY (question_id)
);

/*答案*/
CREATE TABLE answer (
  answer_id               INT NOT NULL AUTO_INCREMENT,  /*主键*/
  question_id             INT,                          /*问题ID 外键*/
  answer_content          VARCHAR(1000),                /*问题的答案*/
  answer_time             DATETIME,                      /*问题回答时间*/
  PRIMARY KEY (answer_id)
);

/*消息*/
CREATE TABLE message (
  message_id              INT NOT NULL AUTO_INCREMENT, /*主键*/
  message_type            SMALLINT,                    /*0：公告 1：回答问题 2:私信*/
  ts_id_from              INT,                         /*消息来源*/
  ts_id_to                INT,                         /*消息目的*/
  message_content         VARCHAR(200),                /*消息内容*/
  message_status          SMALLINT ,                    /*0：未读 1：已读*/
  PRIMARY KEY (message_id)
);

/*点名记录*/
CREATE TABLE rollcall (
  rollcall_id             INT NOT NULL AUTO_INCREMENT, /*主键*/
  schedule_week           INT,                         /*周*/
  schedule_course_id      INT,                         /*确定哪一节课*/
  big_class_id            INT,                         /*大班的ID*/
  ts_id                   INT,                          /*老师的ID*/
  PRIMARY KEY (rollcall_id)
);

/*点名不在的人*/
CREATE TABLE rollcall_nothere (
  rollcall_nothere_id     INT NOT NULL AUTO_INCREMENT, /*主键*/
  rollcall_id             INT,                         /*点名ID 外键*/
  ts_id                   INT,                          /*学生ID */
  PRIMARY KEY (rollcall_nothere_id)
);

/*教室的地图*/
CREATE TABLE classroom (
  classroom_id            INT NOT NULL AUTO_INCREMENT, /*主键*/
  classroom_name          VARCHAR(20),                 /*教室名称*/
  classroom_way           VARCHAR(20) ,                /*教室路径*/
  PRIMARY KEY (classroom_id)
);

alter table  ts add constraint FK_ts_college foreign key (college_id)
      references college (college_id) on delete restrict on update restrict;

alter table  ts add constraint FK_ts_dept foreign key (dept_id)
      references dept (dept_id) on delete restrict on update restrict;

alter table  ts add constraint FK_ts_single_class foreign key (single_class_id)
      references single_class (single_class_id) on delete restrict on update restrict;

alter table  ts_passwd add constraint FK_ts_passwd_ts foreign key (ts_id)
      references ts (ts_id) on delete restrict on update restrict;

alter table  teacher_plan add constraint FK_teacher_plan_ts foreign key (ts_id)
      references ts (ts_id) on delete restrict on update restrict;

alter table  teacher_plan add constraint FK_teacher_plan_schedule foreign key (schedule_course_id)
      references schedule_course (schedule_course_id) on delete restrict on update restrict;

alter table  single_class add constraint FK_single_class_dept foreign key (dept_id)
      references dept (dept_id) on delete restrict on update restrict;

alter table  single_class add constraint FK_single_class_college foreign key (college_id)
      references college (college_id) on delete restrict on update restrict;

alter table  big_class add constraint FK_big_class_ts foreign key (ts_id)
      references ts (ts_id) on delete restrict on update restrict;

alter table  big_class add constraint FK_big_class_single_class foreign key (single_class_id)
      references single_class (single_class_id) on delete restrict on update restrict;

alter table  big_class add constraint FK_big_class_course foreign key (course_id)
      references course (course_id) on delete restrict on update restrict;

alter table  dept add constraint FK_dept_college foreign key (college_id)
      references college (college_id) on delete restrict on update restrict;

alter table  course add constraint FK_course_dept foreign key (dept_id)
      references dept (dept_id) on delete restrict on update restrict;

alter table  schedule_course add constraint FK_dept_schedule foreign key (dept_id)
      references dept (dept_id) on delete restrict on update restrict;

alter table  datad add constraint FK_datad_ts foreign key (ts_id)
      references ts (ts_id) on delete restrict on update restrict;

alter table  download add constraint FK_download_datad foreign key (datad_id)
      references datad (datad_id) on delete restrict on update restrict;

alter table  question add constraint FK_question_big_class foreign key (big_class_id)
      references big_class (big_class_id) on delete restrict on update restrict;

alter table  answer add constraint FK_answer_question foreign key (question_id)
      references question (question_id) on delete restrict on update restrict;

alter table  message add constraint FK_message_ts foreign key (ts_id_from)
      references ts (ts_id) on delete restrict on update restrict;

alter table  message add constraint FK_message_ts_to foreign key (ts_id_to)
      references ts (ts_id) on delete restrict on update restrict;

alter table  rollcall add constraint FK_rollcall_big_class foreign key (big_class_id)
      references big_class (big_class_id) on delete restrict on update restrict;

alter table  rollcall add constraint FK_rollcall_ts foreign key (ts_id)
      references ts (ts_id) on delete restrict on update restrict;

alter table  rollcall_nothere add constraint FK_rollcall_nothere_rollcall foreign key (rollcall_id)
      references rollcall (rollcall_id) on delete restrict on update restrict;

alter table  rollcall_nothere add constraint FK_rollcall_nothere_ts foreign key (ts_id)
      references ts (ts_id) on delete restrict on update restrict;