-
Notifications
You must be signed in to change notification settings - Fork 1
/
data.sql
303 lines (281 loc) · 12.5 KB
/
data.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
# 更新日志
# 12/1 13:44
# 为topic和topic_reply增加了anonymity enum(‘0’, ‘1’)表示匿名与否的属性
# 其他略...
# 对file，topic等一些可以删除的数据在引用时增加delete cascade
# 增加feedback表，增加feedback表中的status属性表示反馈是否解决
# 12/14
# 把 stu_class和tea_class合并成一张user_class表
# 12.15
# 增加message表，帖子topic表里增加top属性表示是否置顶
# 12.20
# feedback表中contact属性选填，表示联系方式
# feedback表中type属性，表示反馈类型
set names utf8;
drop database if exists se_system;
create database se_system CHARACTER SET utf8;
drop user if exists se_admin;
create user se_admin identified by '(>_0)';
grant all on se_system.* to se_admin;
use se_system;
create table user (
id varchar(30) primary key,
name varchar(10) not null,
password varchar(16) not null,
type set('A', 'T', 'S', 'TA') not null, # A: admin, T: teacher, S: student, TA: teacher assistant
major_class varchar(50), # 如果type=S，账户资料页可以设置专业班级属性？
email varchar(50),
phone varchar(11)
) CHARACTER SET = utf8;
insert into user values ('3140100000', '陈然', '123456', 'S', '', '', ''),
('3140100001', '杨逸杰', '123456', 'S', '', '', ''),
('3140100002', '金鹏', '123456', 'S', '', '', ''),
('3140100003', '张昌琳', '123456', 'S', '', '', ''),
('3140100004', '杨璞', '123456', 'S', '', '', ''),
('3140100005', '杨伟民', '123456', 'S', '', '', ''),
('T001', '邢卫', '123456', 'T', '', '', ''),
('T002', '林海', '123456', 'T', '', '', ''),
('admin', '管理员', '123456', 'A', '', '', ''),
('se_admin', '(>_0)', '2333', 'A', '', '', ''),
('3140100006', '助教杨', '123456', 'S,TA', '', '', '');
# 教师姓名，总体介绍，教学风格，以往教学，科研成果，出版书籍，所获荣誉
create table teacher (
tea_id varchar(30),
name varchar(10), # 感觉加上查询会方便一点。。
intro text,
style text, # ...
previous_teaching text,
research text,
book text,
honor text,
primary key (tea_id),
foreign key (tea_id)
references user(id) on update cascade
) CHARACTER SET = utf8;
insert into teacher(tea_id, name) values ('T001', '邢卫');
insert into teacher(tea_id, name) values ('T002', '林海');
create table course (
id int primary key AUTO_INCREMENT,
title varchar(50) not null,
description text,
plan text, # 教学计划
background text,
assess text, # 考核
textbook text,
homework_intro text,
basic_request text
) CHARACTER SET = utf8;
insert into course(title, description, plan, background, assess, textbook, homework_intro, basic_request) values ('软件需求工程', '软需课程简介', '软需教学日历', '软需教学背景', '软需考核方式', '软需使用教材', '软需大作业介绍', '软需基础要求');
insert into course(title) values ('软件工程管理');
insert into course(title) values ('软件工程基础');
insert into course(title) values ('软件测试与质量保证');
# 课程在不同年份不同学期的班级
create table class (
id int AUTO_INCREMENT,
course_id int,
year int not null,
semester varchar(10) not null,
section varchar(20) not null,
no int, # 给班级编个号？
end enum('0', '1') default '0',
primary key (id, course_id),
foreign key (course_id)
references course(id) on update cascade
) CHARACTER SET = utf8;
insert into class(course_id, year, semester, section) values ('1', '2016', '秋冬', '周一第6、7、8节');
insert into class(course_id, year, semester, section) values ('1', '2016', '秋冬', '周五第3、4、5节');
insert into class(course_id, year, semester, section) values ('2', '2016', '秋冬', '周二第3、4、5节');
# 用户、课程、班级 多对多关系
create table user_class (
user_id varchar(30),
class_id int,
course_id int,
user_type set('A', 'T', 'S', 'TA') not null, # A: admin, T: teacher, S: student, TA: teacher assistant
primary key (user_id, class_id, course_id),
foreign key (user_id)
references user(id) on update cascade
) CHARACTER SET = utf8;
insert into user_class values ('3140100000', '1', '1', 'S'),
('3140100001', '1', '1', 'S'),
('3140100002', '1', '1', 'S'),
('3140100003', '2', '1', 'S'),
('3140100004', '2', '1', 'S'),
('3140100005', '2', '1', 'S'),
('T001', '1', '1', 'T'),
('T001', '2', '1', 'T'),
('T002', '1', '1', 'T'),
('T002', '2', '1', 'T');
# 包括教师，学生上传的文件
create table file (
id int AUTO_INCREMENT,
uploader_id varchar(30) not null,
upload_time datetime not null,
name varchar(100) not null,
filepath varchar(100) not null,
size int not null,
mark varchar(10), # 备用...
primary key (id),
foreign key (uploader_id)
references user(id) on update cascade
) CHARACTER SET = utf8;
# 以往优秀成果/参考资料/视音频资料/宣传版课件，（教学课件不和course绑定,和class绑定）
create table file_course (
course_id int,
file_id int,
type set('0', '1', '2', '3', '4', '5'), # 多设几个备用...
primary key (course_id, file_id),
foreign key (course_id)
references course(id) on update cascade,
foreign key (file_id)
references file(id) on update cascade on delete cascade
) CHARACTER SET = utf8;
# 课件（或者其他和class绑定的文件）
# 课件与class是多对一关系其实可以不建表，比如在file中增加class_id属性，用mark标记是否公开
create table file_class (
class_id int,
file_id int,
public enum('0', '1') default '1', # 0 不公开 1 公开 insert操作的时候默认用1吧
share enum('0', '1'), # 备用...也许多个班级可以共享课件0 0
foreign key (class_id)
references class(id) on update cascade,
foreign key (file_id)
references file(id) on update cascade on delete cascade
) CHARACTER SET = utf8;
create table homework (
id int AUTO_INCREMENT,
class_id int not null,
creator_id varchar(30) not null,
post_time datetime not null, # 后端insert用 post_time=NOW()
update_time datetime,
due_time datetime not null,
title varchar(30) not null,
content text, # 选择题怎么建表？
file_id int,
primary key (id),
foreign key (creator_id)
references user(id) on update cascade,
foreign key (class_id)
references class(id) on update cascade,
foreign key (file_id)
references file(id)
) CHARACTER SET = utf8;
# 发布作业时添加的附件，与homework是多对一关系可以不建表，比如在file中增加hw_id属性
create table file_homework (
file_id int,
hw_id int,
primary key (file_id, hw_id),
foreign key (file_id)
references file(id) on update cascade on delete cascade,
foreign key (hw_id)
references homework(id) on update cascade on delete cascade
) CHARACTER SET = utf8;
# 学生提交
create table submit_homework (
hw_id int,
stu_id varchar(30),
file_id int, # 提交作业时的附件
content text, # 选择题答案怎么建表
submit_time datetime not null,
score int default -1, # 选择题后端或许可以把作业改了直接填分数
comment text,
primary key (hw_id, stu_id),
foreign key (hw_id)
references homework(id) on update cascade on delete cascade,
foreign key (stu_id)
references user(id) on update cascade,
foreign key (file_id)
references file(id) on update cascade on delete cascade
) CHARACTER SET = utf8;
create table announce (
id int AUTO_INCREMENT,
creator_id varchar(30) not null,
post_time datetime not null,
title varchar(50) not null,
content text,
update_time datetime,
primary key (id),
foreign key (creator_id)
references user(id) on update cascade
) CHARACTER SET = utf8;
# 论坛里能上传附件吗0 0
create table topic (
id int AUTO_INCREMENT,
creator_id varchar(30) not null,
course_id int not null,
post_time datetime not null,
title varchar(50) not null,
last_reply_time datetime,
content text,
reply_num int default 0, # 回复数
click_num int default 0, # 查看次数
anonymity enum('0', '1') default '0', # insert默认0
top enum('0', '1') default '0', #是否置顶
primary key (id),
foreign key (creator_id)
references user(id) on update cascade,
foreign key (course_id)
references course(id) on update cascade
) CHARACTER SET = utf8;
insert into topic(creator_id, course_id, post_time, title, content, reply_num) values ('3140100005', 1, NOW(), '请问各位感觉UML考试的难度如何？', '感觉我太菜了都不会做啊，希望老师改卷手下留情', 6);
create table topic_reply (
id int AUTO_INCREMENT,
topic_id int,
creator_id varchar(30) not null,
post_time datetime not null,
content text,
anonymity enum('0', '1') default '0', # insert默认0
primary key (id, topic_id),
foreign key (creator_id)
references user(id) on update cascade,
foreign key (topic_id)
references topic(id) on update cascade on delete cascade
) CHARACTER SET = utf8;
insert into topic_reply(topic_id, creator_id, post_time, content, anonymity) values (1, '3140100003', NOW(), '实在是太简单啦', '0');
insert into topic_reply(topic_id, creator_id, post_time, content, anonymity) values (1, '3140100000', NOW(), '仰慕0 0', '1');
insert into topic_reply(topic_id, creator_id, post_time, content, anonymity) values (1, '3140100001', NOW(), '仰慕0 0', '0');
insert into topic_reply(topic_id, creator_id, post_time, content, anonymity) values (1, '3140100002', NOW(), '仰慕0 0', '0');
insert into topic_reply(topic_id, creator_id, post_time, content, anonymity) values (1, '3140100004', NOW(), '仰慕0 0', '1');
insert into topic_reply(topic_id, creator_id, post_time, content, anonymity) values (1, '3140100005', NOW(), '仰慕0 0', '1');
create table link (
id int AUTO_INCREMENT,
course_id int not null,
content varchar(50) not null,
url varchar(100) not null,
primary key (id),
foreign key (course_id)
references course(id) on update cascade on delete cascade
);
insert into link(course_id, content, url)
values (1, 'CC98软件工程版', 'http://www.cc98.org/list.asp?boardid=74'),
(1, '浙江大学计算机学院中文网', 'http://cspo.zju.edu.cn/'),
(1, '浙江大学教务系统', 'http://jwbinfosys.zju.edu.cn/default2.aspx'),
(1, '浙江大学缘网', 'http://luckweb.057101.com/bt2/index.asp');
# post_time为最后一次修改时间，修改包括插入和已阅读
create table feedback (
id int AUTO_INCREMENT,
title varchar(50) not null,
content text,
post_time datetime not null,
contact varchar(30),
type enum('0', '1', '2', '3') default '0', # 0其他， 1网站界面，2网站内容，3教学安排
status enum('0', '1') default '0', # 0未解决，1解决了
primary key (id)
) CHARACTER SET = utf8;
insert into feedback(title, content, post_time) values ('关于前端的建议', '有些地方比较粗糙，如果能多增加一些细节会更好一些', NOW());
create table message (
id int AUTO_INCREMENT,
creator_id varchar(30),
reciever_id varchar(30),
class_id int,
course_id int,
title varchar(50) not null,
content text,
post_time datetime not null,
primary key (id),
foreign key (creator_id) references user(id) on update cascade,
foreign key (creator_id) references user(id) on update cascade
) CHARACTER SET = utf8;
insert into message(creator_id, reciever_id, class_id, course_id, title, content, post_time)
values('T001', '3140100000', '1', '1', 'uml考试', '2016.1.8早上8：00——9：00uml考试，闭卷，考完试进行答辩', NOW());
insert into message(creator_id, reciever_id, class_id, course_id, title, content, post_time)
values('T002', '3140100000', '1', '1', '答辩须知', '2016.1.8早上8：00——9：00uml考试，考完试进行答辩，答辩要求展示网站和ppt，抽签决定顺序', NOW());