数据库课设

  1 SET NAMES utf8mb4;
  2 SET FOREIGN_KEY_CHECKS = 0;
  3 
  4 -- ----------------------------
  5 -- Table structure for administrator
  6 -- ----------------------------
  7 DROP TABLE IF EXISTS `administrator`;
  8 CREATE TABLE `administrator`  (
  9   `AID` varchar(25) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '管理员编号',
 10   `ID_number` varchar(25) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '管理员身份证号',
 11   `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '管理员姓名',
 12   `phone_number` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '管理员手机号',
 13   PRIMARY KEY (`AID`) USING BTREE
 14 ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
 15 
 16 -- ----------------------------
 17 -- Table structure for advisor
 18 -- ----------------------------
 19 DROP TABLE IF EXISTS `advisor`;
 20 CREATE TABLE `advisor`  (
 21   `SID` varchar(25) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '学生学号',
 22   `TID` varchar(25) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '教师编号',
 23   PRIMARY KEY (`SID`) USING BTREE,
 24   INDEX `TID`(`TID`) USING BTREE,
 25   CONSTRAINT `SID` FOREIGN KEY (`SID`) REFERENCES `student` (`SID`) ON DELETE CASCADE ON UPDATE CASCADE,
 26   CONSTRAINT `TID` FOREIGN KEY (`TID`) REFERENCES `teacher` (`TID`) ON DELETE SET NULL ON UPDATE CASCADE
 27 ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
 28 
 29 -- ----------------------------
 30 -- Table structure for department
 31 -- ----------------------------
 32 DROP TABLE IF EXISTS `department`;
 33 CREATE TABLE `department`  (
 34   `DID` varchar(25) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '学院编号',
 35   `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '学院名称',
 36   `address` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '学院所在位置',
 37   PRIMARY KEY (`DID`) USING BTREE
 38 ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
 39 
 40 -- ----------------------------
 41 -- Table structure for has_major
 42 -- ----------------------------
 43 DROP TABLE IF EXISTS `has_major`;
 44 CREATE TABLE `has_major`  (
 45   `MID` varchar(25) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '专业编号',
 46   `DID` varchar(25) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '学院编号',
 47   PRIMARY KEY (`MID`) USING BTREE,
 48   INDEX `DID`(`DID`) USING BTREE,
 49   CONSTRAINT `DID` FOREIGN KEY (`DID`) REFERENCES `department` (`DID`) ON DELETE CASCADE ON UPDATE CASCADE,
 50   CONSTRAINT `MID` FOREIGN KEY (`MID`) REFERENCES `major` (`MID`) ON DELETE CASCADE ON UPDATE CASCADE
 51 ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
 52 
 53 -- ----------------------------
 54 -- Table structure for major
 55 -- ----------------------------
 56 DROP TABLE IF EXISTS `major`;
 57 CREATE TABLE `major`  (
 58   `MID` varchar(25) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '专业编号',
 59   `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '专业名称',
 60   `type` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '专业类型(理学、工学等)',
 61   `study_time` int(255) NULL DEFAULT NULL COMMENT '修读年限',
 62   PRIMARY KEY (`MID`) USING BTREE
 63 ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
 64 
 65 -- ----------------------------
 66 -- Table structure for rewards_punishment
 67 -- ----------------------------
 68 DROP TABLE IF EXISTS `rewards_punishment`;
 69 CREATE TABLE `rewards_punishment`  (
 70   `RID` varchar(25) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '奖惩编号',
 71   `type` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '奖惩类型(奖励或惩罚)',
 72   `detial` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '奖惩详情',
 73   INDEX `RID`(`RID`) USING BTREE
 74 ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
 75 
 76 -- ----------------------------
 77 -- Table structure for sec_time
 78 -- ----------------------------
 79 DROP TABLE IF EXISTS `sec_time`;
 80 CREATE TABLE `sec_time`  (
 81   `CID` varchar(25) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '课程号',
 82   `time_ID` varchar(25) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '时间号',
 83   PRIMARY KEY (`CID`, `time_ID`) USING BTREE,
 84   INDEX `time_ID`(`time_ID`) USING BTREE,
 85   CONSTRAINT `CID` FOREIGN KEY (`CID`) REFERENCES `sections` (`CID`) ON DELETE CASCADE ON UPDATE CASCADE,
 86   CONSTRAINT `time_ID` FOREIGN KEY (`time_ID`) REFERENCES `time` (`time_ID`) ON DELETE CASCADE ON UPDATE CASCADE
 87 ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
 88 
 89 -- ----------------------------
 90 -- Table structure for sections
 91 -- ----------------------------
 92 DROP TABLE IF EXISTS `sections`;
 93 CREATE TABLE `sections`  (
 94   `CID` varchar(25) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '课程编号',
 95   `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '课程名',
 96   PRIMARY KEY (`CID`) USING BTREE
 97 ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
 98 
 99 -- ----------------------------
100 -- Table structure for stud_major
101 -- ----------------------------
102 DROP TABLE IF EXISTS `stud_major`;
103 CREATE TABLE `stud_major`  (
104   `SID` varchar(25) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '学生学号',
105   `MID` varchar(25) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '专业编号',
106   PRIMARY KEY (`SID`) USING BTREE,
107   INDEX `stud_major_MID`(`MID`) USING BTREE,
108   CONSTRAINT `stud_major_MID` FOREIGN KEY (`MID`) REFERENCES `major` (`MID`) ON DELETE CASCADE ON UPDATE CASCADE,
109   CONSTRAINT `stud_major_SID` FOREIGN KEY (`SID`) REFERENCES `student` (`SID`) ON DELETE CASCADE ON UPDATE CASCADE
110 ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
111 
112 -- ----------------------------
113 -- Table structure for stud_re
114 -- ----------------------------
115 DROP TABLE IF EXISTS `stud_re`;
116 CREATE TABLE `stud_re`  (
117   `RID` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '奖惩编号',
118   `SID` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '学生学号',
119   PRIMARY KEY (`RID`) USING BTREE,
120   INDEX `stud_re_SID`(`SID`) USING BTREE,
121   CONSTRAINT `stud_re_RID` FOREIGN KEY (`RID`) REFERENCES `rewards_punishment` (`RID`) ON DELETE CASCADE ON UPDATE CASCADE,
122   CONSTRAINT `stud_re_SID` FOREIGN KEY (`SID`) REFERENCES `student` (`SID`) ON DELETE CASCADE ON UPDATE CASCADE
123 ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
124 
125 -- ----------------------------
126 -- Table structure for student
127 -- ----------------------------
128 DROP TABLE IF EXISTS `student`;
129 CREATE TABLE `student`  (
130   `SID` varchar(25) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '学号',
131   `ID_number` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '身份证号',
132   `birthday` date NULL DEFAULT NULL COMMENT '生日 YYYY-MM-DD',
133   `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '姓名',
134   `sex` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '性别',
135   `phone_number` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '手机号',
136   `age` int(255) NULL DEFAULT NULL COMMENT '年龄',
137   PRIMARY KEY (`SID`) USING BTREE
138 ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
139 
140 -- ----------------------------
141 -- Table structure for takes
142 -- ----------------------------
143 DROP TABLE IF EXISTS `takes`;
144 CREATE TABLE `takes`  (
145   `SID` varchar(25) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '学号',
146   `CID` varchar(25) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '课程号',
147   `grade` decimal(5, 3) NULL DEFAULT NULL COMMENT '考试分数',
148   `test_date` datetime(0) NULL DEFAULT NULL COMMENT '考试时间YYYY-MM-DD HH:MM:SS',
149   `test_address` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '考试地点',
150   `test_type` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '考试类型',
151   PRIMARY KEY (`SID`, `CID`) USING BTREE,
152   INDEX `takes_CID`(`CID`) USING BTREE,
153   CONSTRAINT `takes_CID` FOREIGN KEY (`CID`) REFERENCES `sections` (`CID`) ON DELETE CASCADE ON UPDATE CASCADE,
154   CONSTRAINT `takes_SID` FOREIGN KEY (`SID`) REFERENCES `student` (`SID`) ON DELETE CASCADE ON UPDATE CASCADE
155 ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
156 
157 -- ----------------------------
158 -- Table structure for teach_major
159 -- ----------------------------
160 DROP TABLE IF EXISTS `teach_major`;
161 CREATE TABLE `teach_major`  (
162   `MID` varchar(25) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '专业编号',
163   `TID` varchar(25) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '教师编号',
164   PRIMARY KEY (`TID`) USING BTREE,
165   INDEX `teach_major_MID`(`MID`) USING BTREE,
166   CONSTRAINT `teach_major_MID` FOREIGN KEY (`MID`) REFERENCES `major` (`MID`) ON DELETE CASCADE ON UPDATE CASCADE,
167   CONSTRAINT `teach_major_TID` FOREIGN KEY (`TID`) REFERENCES `teacher` (`TID`) ON DELETE CASCADE ON UPDATE CASCADE
168 ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
169 
170 -- ----------------------------
171 -- Table structure for teacher
172 -- ----------------------------
173 DROP TABLE IF EXISTS `teacher`;
174 CREATE TABLE `teacher`  (
175   `TID` varchar(25) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '教师编号',
176   `ID_number` varchar(25) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '教师身份证号',
177   `birthday` date NULL DEFAULT NULL COMMENT '教师生日',
178   `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '教师姓名',
179   `sex` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '教师性别',
180   `phone_number` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '教师电话号码',
181   PRIMARY KEY (`TID`, `ID_number`) USING BTREE,
182   INDEX `TID`(`TID`) USING BTREE
183 ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
184 
185 -- ----------------------------
186 -- Table structure for teaches
187 -- ----------------------------
188 DROP TABLE IF EXISTS `teaches`;
189 CREATE TABLE `teaches`  (
190   `CID` varchar(25) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '课程号',
191   `TID` varchar(25) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '教师编号',
192   PRIMARY KEY (`CID`, `TID`) USING BTREE,
193   INDEX `teaches_TID`(`TID`) USING BTREE,
194   CONSTRAINT `teaches_CID` FOREIGN KEY (`CID`) REFERENCES `sections` (`CID`) ON DELETE CASCADE ON UPDATE CASCADE,
195   CONSTRAINT `teaches_TID` FOREIGN KEY (`TID`) REFERENCES `teacher` (`TID`) ON DELETE CASCADE ON UPDATE CASCADE
196 ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
197 
198 -- ----------------------------
199 -- Table structure for time
200 -- ----------------------------
201 DROP TABLE IF EXISTS `time`;
202 CREATE TABLE `time`  (
203   `weekday` int(25) NULL DEFAULT NULL COMMENT '星期几',
204   `lesson` int(25) NULL DEFAULT NULL COMMENT '第几大节',
205   `start_week` int(255) NULL DEFAULT NULL COMMENT '第几周开课',
206   `end_week` int(255) NULL DEFAULT NULL COMMENT '第几周结课',
207   `time_ID` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '时间号',
208   PRIMARY KEY (`time_ID`) USING BTREE
209 ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
210 
211 SET FOREIGN_KEY_CHECKS = 1;

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for administrator
-- ----------------------------
DROP TABLE IF EXISTS `administrator`;
CREATE TABLE `administrator`  (
  `AID` varchar(25) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '管理员编号',
  `ID_number` varchar(25) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '管理员身份证号',
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '管理员姓名',
  `phone_number` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '管理员手机号',
  PRIMARY KEY (`AID`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for advisor
-- ----------------------------
DROP TABLE IF EXISTS `advisor`;
CREATE TABLE `advisor`  (
  `SID` varchar(25) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '学生学号',
  `TID` varchar(25) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '教师编号',
  PRIMARY KEY (`SID`) USING BTREE,
  INDEX `TID`(`TID`) USING BTREE,
  CONSTRAINT `SID` FOREIGN KEY (`SID`) REFERENCES `student` (`SID`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `TID` FOREIGN KEY (`TID`) REFERENCES `teacher` (`TID`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for department
-- ----------------------------
DROP TABLE IF EXISTS `department`;
CREATE TABLE `department`  (
  `DID` varchar(25) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '学院编号',
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '学院名称',
  `address` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '学院所在位置',
  PRIMARY KEY (`DID`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for has_major
-- ----------------------------
DROP TABLE IF EXISTS `has_major`;
CREATE TABLE `has_major`  (
  `MID` varchar(25) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '专业编号',
  `DID` varchar(25) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '学院编号',
  PRIMARY KEY (`MID`) USING BTREE,
  INDEX `DID`(`DID`) USING BTREE,
  CONSTRAINT `DID` FOREIGN KEY (`DID`) REFERENCES `department` (`DID`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `MID` FOREIGN KEY (`MID`) REFERENCES `major` (`MID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for major
-- ----------------------------
DROP TABLE IF EXISTS `major`;
CREATE TABLE `major`  (
  `MID` varchar(25) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '专业编号',
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '专业名称',
  `type` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '专业类型(理学、工学等)',
  `study_time` int(255) NULL DEFAULT NULL COMMENT '修读年限',
  PRIMARY KEY (`MID`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for rewards_punishment
-- ----------------------------
DROP TABLE IF EXISTS `rewards_punishment`;
CREATE TABLE `rewards_punishment`  (
  `RID` varchar(25) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '奖惩编号',
  `type` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '奖惩类型(奖励或惩罚)',
  `detial` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '奖惩详情',
  INDEX `RID`(`RID`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for sec_time
-- ----------------------------
DROP TABLE IF EXISTS `sec_time`;
CREATE TABLE `sec_time`  (
  `CID` varchar(25) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '课程号',
  `time_ID` varchar(25) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '时间号',
  PRIMARY KEY (`CID`, `time_ID`) USING BTREE,
  INDEX `time_ID`(`time_ID`) USING BTREE,
  CONSTRAINT `CID` FOREIGN KEY (`CID`) REFERENCES `sections` (`CID`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `time_ID` FOREIGN KEY (`time_ID`) REFERENCES `time` (`time_ID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for sections
-- ----------------------------
DROP TABLE IF EXISTS `sections`;
CREATE TABLE `sections`  (
  `CID` varchar(25) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '课程编号',
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '课程名',
  PRIMARY KEY (`CID`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for stud_major
-- ----------------------------
DROP TABLE IF EXISTS `stud_major`;
CREATE TABLE `stud_major`  (
  `SID` varchar(25) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '学生学号',
  `MID` varchar(25) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '专业编号',
  PRIMARY KEY (`SID`) USING BTREE,
  INDEX `stud_major_MID`(`MID`) USING BTREE,
  CONSTRAINT `stud_major_MID` FOREIGN KEY (`MID`) REFERENCES `major` (`MID`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `stud_major_SID` FOREIGN KEY (`SID`) REFERENCES `student` (`SID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for stud_re
-- ----------------------------
DROP TABLE IF EXISTS `stud_re`;
CREATE TABLE `stud_re`  (
  `RID` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '奖惩编号',
  `SID` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '学生学号',
  PRIMARY KEY (`RID`) USING BTREE,
  INDEX `stud_re_SID`(`SID`) USING BTREE,
  CONSTRAINT `stud_re_RID` FOREIGN KEY (`RID`) REFERENCES `rewards_punishment` (`RID`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `stud_re_SID` FOREIGN KEY (`SID`) REFERENCES `student` (`SID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student`  (
  `SID` varchar(25) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '学号',
  `ID_number` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '身份证号',
  `birthday` date NULL DEFAULT NULL COMMENT '生日 YYYY-MM-DD',
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '姓名',
  `sex` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '性别',
  `phone_number` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '手机号',
  `age` int(255) NULL DEFAULT NULL COMMENT '年龄',
  PRIMARY KEY (`SID`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for takes
-- ----------------------------
DROP TABLE IF EXISTS `takes`;
CREATE TABLE `takes`  (
  `SID` varchar(25) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '学号',
  `CID` varchar(25) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '课程号',
  `grade` decimal(5, 3) NULL DEFAULT NULL COMMENT '考试分数',
  `test_date` datetime(0) NULL DEFAULT NULL COMMENT '考试时间YYYY-MM-DD HH:MM:SS',
  `test_address` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '考试地点',
  `test_type` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '考试类型',
  PRIMARY KEY (`SID`, `CID`) USING BTREE,
  INDEX `takes_CID`(`CID`) USING BTREE,
  CONSTRAINT `takes_CID` FOREIGN KEY (`CID`) REFERENCES `sections` (`CID`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `takes_SID` FOREIGN KEY (`SID`) REFERENCES `student` (`SID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for teach_major
-- ----------------------------
DROP TABLE IF EXISTS `teach_major`;
CREATE TABLE `teach_major`  (
  `MID` varchar(25) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '专业编号',
  `TID` varchar(25) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '教师编号',
  PRIMARY KEY (`TID`) USING BTREE,
  INDEX `teach_major_MID`(`MID`) USING BTREE,
  CONSTRAINT `teach_major_MID` FOREIGN KEY (`MID`) REFERENCES `major` (`MID`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `teach_major_TID` FOREIGN KEY (`TID`) REFERENCES `teacher` (`TID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for teacher
-- ----------------------------
DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher`  (
  `TID` varchar(25) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '教师编号',
  `ID_number` varchar(25) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '教师身份证号',
  `birthday` date NULL DEFAULT NULL COMMENT '教师生日',
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '教师姓名',
  `sex` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '教师性别',
  `phone_number` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '教师电话号码',
  PRIMARY KEY (`TID`, `ID_number`) USING BTREE,
  INDEX `TID`(`TID`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for teaches
-- ----------------------------
DROP TABLE IF EXISTS `teaches`;
CREATE TABLE `teaches`  (
  `CID` varchar(25) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '课程号',
  `TID` varchar(25) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '教师编号',
  PRIMARY KEY (`CID`, `TID`) USING BTREE,
  INDEX `teaches_TID`(`TID`) USING BTREE,
  CONSTRAINT `teaches_CID` FOREIGN KEY (`CID`) REFERENCES `sections` (`CID`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `teaches_TID` FOREIGN KEY (`TID`) REFERENCES `teacher` (`TID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for time
-- ----------------------------
DROP TABLE IF EXISTS `time`;
CREATE TABLE `time`  (
  `weekday` int(25) NULL DEFAULT NULL COMMENT '星期几',
  `lesson` int(25) NULL DEFAULT NULL COMMENT '第几大节',
  `start_week` int(255) NULL DEFAULT NULL COMMENT '第几周开课',
  `end_week` int(255) NULL DEFAULT NULL COMMENT '第几周结课',
  `time_ID` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '时间号',
  PRIMARY KEY (`time_ID`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

SET FOREIGN_KEY_CHECKS = 1;

posted @ 2020-11-25 14:19 Echo宝贝儿阅读(155) 评论(0) 收藏举报

刷新页面返回顶部

数据库课设

公告