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;
