E/R-diagram

user 儲存使用者的帳號資料

Attributes meaning
userId PK, AUTOINCREMENT
name 使用者顯示的名字
email 使用者的信箱
role 使用者的帳號權限身分
contribution 貢獻得分
createTime 創建時間
googleId Google OAuth 資訊

comment_area 作為 document/comment 的 superclass

Attributes meaning
areaId PK, AUTOINCREMENT,讓 document/comment references 過來

document 考古題資料

Attributes meaning
docId PK, 同時也是 FK,reference comment_areaId
courseId 所屬課程的 courseId
userId 上傳者的 ID
year 考古題所屬學年
semester 考古題所屬學期
title 考古題的名稱,例如:期中考題、期末、A/B 卷等
description 關於此份考古題的詳細說明
createTime 創建時間
lastUpdateTime 最後更新時間
folderPath 考古題內容資料夾路徑

announcement 布告欄

Attributes meaning
annId PK, AUTOINCREMENT,生成一組公告的 ID
userId 上傳者的 ID
title 公告標題
content 公告內容
pinned 是否置頂
createTime 創建時間
lastUpdateTime 最後更新時間

comment 留言

Attributes meaning
comId PK, 同時也是 FK,reference comment_areaId
replyId 回復的該則留言的 comment_areaId
userId 留言者的 ID
content 留言內容
createTime 創建時間
lastUpdateTime 最後更新時間

course 課程

Attributes meaning
courseId PK, AUTOINCREMENT,使用課程代碼或額外生成一組ID
courseName 課程名字
deptName 開課系所
category 課程分類
description 課程資訊

vote 評分

Attributes meaning
userId 評分者 FK
docId 被評分的考古題 FK
score 1 or -1,代表正評或負評

PK 為 (userId, docId)


DDL.sql

CREATE TABLE if not exists user ( userId int not null auto_increment, name varchar(16) not null, email varchar(40) not null, role tinyint check (role >= 0 and role <= 2) default 2, contribution int not null default 0, createTime datetime not null, googleId varchar(32) not null, primary key (userId), unique key (email) ); CREATE TABLE if not exists course ( courseId int not null auto_increment, courseName varchar(32) not null, deptName varchar(32) not null, category varchar(16) default '', description varchar(4096) default '', primary key (courseId) ); CREATE TABLE if not exists comment_area ( areaId int not null auto_increment, primary key (areaId) ); CREATE TABLE if not exists document ( docId int not null, courseId int default null, userId int default null, year smallint not null, semester tinyint not null, title varchar(64) not null, description varchar(4096) default '', createTime datetime not null, lastUpdateTime datetime not null, folderPath varchar(1024) not null, primary key (docId), foreign key (docId) references comment_area(areaId) on update cascade on delete cascade, foreign key (courseId) references course (courseId) on update cascade on delete set null, foreign key (userId) references user (userId) on update cascade on delete set null ); CREATE TABLE if not exists announcement ( annId int not null auto_increment, userId int default null, title varchar(64) not null, content varchar(4096) not null default '', pinned bool not null default false, createTime datetime not null, lastUpdateTime datetime not null, primary key (annId), foreign key (userId) references user (userId) on update cascade on delete set null ); CREATE TABLE if not exists comment ( comId int not null, replyId int not null, userId int default null, content varchar(4096) not null, createTime datetime not null, lastUpdateTime datetime not null, primary key (comId), foreign key (comId) references comment_area(areaId) on update cascade on delete cascade, foreign key (replyId) references comment_area(areaId) on update cascade on delete cascade, foreign key (userId) references user (userId) on update cascade on delete set null ); CREATE TABLE if not exists vote ( userId int not null, docId int not null, score smallint check (score = -1 or score = 1), primary key (userId, docId), foreign key (userId) references user(userId) on update cascade on delete cascade, foreign key (docId) references document(docId) on update cascade on delete cascade );