---
title: 資料主體分析
description: 單獨拉出來的資料主體分析，因為直接嵌進 report 比較方便，所以用 h3
---


### 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
);

<!-- ---

為了讓 replyId 可以 references document & comment，建立 superclass

create table comment_area (
    areaId	varchar(32),
	primary key (areaId)
) ENGINE=INNODB;

create table document (
    docId      varchar(32),
    primary key (docId),
    foreign key (docId) references comment_area(areaId)
) ENGINE=INNODB;

create table comment (
    comId      varchar(32),
    replyId    varchar(32),
    content     varchar(32),
    primary key (comId),
    foreign key (comId) references comment_area(areaId),
    foreign key (replyId) references comment_area(areaId)
) ENGINE=INNODB;

insert into comment_area values ('001');
insert into comment_area values ('002');
insert into comment_area values ('003');
insert into document values ('001');
insert into comment values ('002', '001', 'reply to document');
insert into comment values ('003', '002', 'reply to comment of document');

SELECT *
FROM comment
WHERE replyId = '001';

SELECT *
FROM comment
WHERE replyId = '002';

-->