# 資料庫管理 - 第一組
<p style="text-align:center;">組員
<br>吳秉哲 資管二 B12705049
<br>詹詠翔 資管二 B12705063
<br>趙子佾 資管二 B12705064
</p>

---

<p style="text-align:center;">
專案資料
<br><a href="https://github.com/eric2969/DB2024_project">GitHub</a>
<br><a href="https://youtu.be/lWL2WpN3t9U">YouTube</a>
</p>

## 1. 系統分析
本系統旨在管理一家量販店。其中資料表包含，員工、商品、供應商、顧客(會員和非會員)、訂單。顧客將會被會被分為會員以及非會員。

Relations: $supply$、$buy$、$cancel$、$fix$、$return$、$exchange$
### 1.1 系統功能
#### 1.1.1 關於商店的背景設定
使用者為顧客，顧客可以是會員或是非會員。非會員能夠自由瀏覽本商店所有的商品，並知道剩餘的商品數量。但只有會員能透過我們的系統下訂單購買本店的商品，並使用多元支付方式，如現金、信用卡、轉帳等等方法付款。對於這家量販店的會員，我們會儲存該會員的姓名、電話、生日、地址以及電子郵件信箱。會員可以使用電子郵件登入帳號，更改會員的個人資料，也能購買商品產生訂單。除此之外，針對現場購物的體驗有任何不滿意的部分也可以透過意見表回饋，這個表單當然也支援客訴員工。

#### 1.1.2 給使用者的功能
我們的使用者可以使用以下功能：
- **創建會員資料**： 顧客可以建立以及修改自己的帳號基本資料如姓名、電話以及地址等。
- **下訂單**：顧客必須創建會員資料，透過系統下訂單購買商品，訂單支持現金、信用卡、轉帳等多種支付方式。
- **會員管理**：會員可以登入、購買商品並產生訂單。
- **意見回饋**：顧客可以透過意見表回饋購物體驗，支持客訴。
- **查詢訂單**：顧客可以查詢之前已經購買的訂單狀態。
- **取消訂單**：顧客可以取消之前成立的訂單。

#### 1.1.3 給管理員的功能
我們的管理員可以使用以下功能:
- **出缺勤查詢**：員工可以查看自己的出缺勤紀錄，提出請假申請。
- **客訴查詢**：員工可以查看自己受到的客訴原因，並進行處理和跟進。
- **查看庫存**：員工可以查詢商品的存貨量，確認是否有貨物可以立即提供客戶。
- **訂單查詢**：員工可以查詢負責訂單的詳細資訊，包括訂單編號、日期、支付方式。
- **訂單處理**：員工可以處理負責訂單的取消、出貨等。

## 2. 系統設計
### 2-1. 前後端設計
前端的部分我們預計使用網頁方式且使用apache web server服務呈現，並透過ajax的方式與後端進行資料傳輸並呈現，而後端部分會使用php撰寫，並且在後端與MySQL進行連接。並且為了簡化伺服器及網站的架設過程，我們會使用xampp建立網站及資料庫。
### 2-2. 實體關聯圖(ER diagram)
下方圖片是我們系統的 ER diagram，其中一共有六個實體(entity)、八個關係(relationship)、兩個弱實體(weak entity)、兩個弱關係(weak relationship)。六個實體分別是 $EMPLOYEE$、$MEMBER$、$DEPT$、$ORDER$、$MERCHANDISE$、$SUPPLIER$。八個關係分別是$Manages$、$Employs$、$Complain$、$In\_charge\_of$、$Make$、$Includes\_of$、$Supplies$、$Restock$。兩個弱實體分別是$ATTENDANCE$、$ORDER\_DETAIL$。兩個弱關係分別是$Record$、$Has$。
$EMPLOYEE:$ 代表所有的員工，每個員工都會有編號並記錄一些個人資訊。除此之外，每個員工每天的上下班打卡時間都會記錄，提早離開也會記錄原因($Work\_type$)
$MEMBER:$ 代表所有會員，每個會員都有編號，並記錄一些基本資訊。會員可以買東西($Make$)，也可以客訴商場($Complain$)。
$DEPT:$ 代表每個部門。每個部門也都會有管理者($Manage$)，也會管理員工($Employ$)。
$ORDER:$ 代表每一筆訂單，除了會有編號以便交易資訊($ORDER\_DETAIL$)紀錄詳細購物資訊外，也會記錄訂單目前進度($Status$)、收件人地址、收件人電話等。
$MERCHANDISE:$ 代表所有的商品，會有品編號被記錄於交易資訊，也會有該商品目前價格開始日期($Start\_date$)。除此之外也會記錄該商品是由哪個供應商提供($Supplies$)，在何時進貨($Restock$)。
$SUPPLIER:$ 代表供應商，除了透過編號紀錄進貨和供貨資訊外，也記錄聯絡資訊。


### 2-3. 關係示意圖(Relational schema diagram)
下圖為上方 ER diagram 轉換所得到的 Relational scheme，其中有11個關聯(relation)。
$DEPT:$ 承襲自 ER diagram，並以 $DeptID$ 作為主鍵(primary key)，$MgnID$ 則參考 $EmpID$。
$EMPLOYEE:$ 來自 ER diagram，並以 $EmpID$ 作為主鍵，$Emp\_dept$ 則參考 $DeptID$。
$ORDER:$ 來自 ER diagram，以 $OrdID$ 作為主鍵，$CusID$ 參考 $MemID$，$Handle\_by$ 參考 $EmpID$。
$SUPPLIER:$ 以 $SuppID$ 作為主鍵。
$MERCHANDISE:$ 以$MerID$ 作為主鍵。
$SUPPLIES:$ 參考 $SuppID$、$MerID$，並搭配時間作為主鍵。如此設計是為了保證一個商品僅由一個供應商提供。
$ORDER\_DETAIL:$ 參考 $OrdID$、$MerID$ 作為主鍵。
$MEMBER:$ 來自 ER diagram，以$MemID$ 作為主鍵。
$COMPLAINT:$ 以 $CompID$ 作為主鍵，其中 $Complaint$ 參考 $MerID$、參考 $EmpID$。
$ATTENDANCE:$ 參考 $EmpID$ 並搭配 $Create\_date$ 和 $Check\_in$ 作為主鍵。
$Work\_type:$ 是為了達成 4NF，同時讓這一欄位可以填更多不同的狀態而從 $ATTENDANCE$ 中拆分出來的。


### 2-4. Data Dictionary
- ADMINS 表格
| 欄位名稱 | 資料型別 | 說明 | Keys | 限制 | 定義域 |
|:---------:|:--------:|:--------:|:-----------:|:----:|:------:|
| username | TEXT | 員工帳號 | | Not NULL | |
| password | TEXT | 員工密碼 | | Not NULL | |
| indice | INT | 員工編號 | PK, FK: EMPLOYEE(EmpID) | Not NULL | |
- ATTENDANCE 表格
| 欄位名稱 | 資料型別 | 說明 | Keys | 限制 | 定義域 |
|:---------:|:--------:|:--------:|:-----------:|:----:|:------:|
|EmpID|INT|員工編號|PK| Not NULL | |
|Create_date|DATE|建立時間|PK| Not NULL | |
|Check_in|DATETIME|上班打卡時間|PK| Not NULL | |
|Check_out|DATETIME|下班打卡時間|| Not NULL | |
|Work_type|INT|班別|| Not NULL | |
- COMPLAINT 表格
| 欄位名稱 | 資料型別 | 說明 | Keys | 限制 | 定義域 |
|:---------:|:--------:|:--------:|:-----------:|:----:|:------:|
|compID|INT|投訴編號|PK| Not NULL | |
|complainant|TEXT|投訴人姓名|| Not NULL | |
|EmpID|INT|投訴員工編號|| Not NULL | |
|email|TEXT|投訴人信箱|| Not NULL | |
|reason|TEXT|投訴人意見|| Not NULL | |
|create_time|DATETIME|投訴時間|| Not NULL | |
- DEPT 表格
| 欄位名稱 | 資料型別 | 說明 | Keys | 限制 | 定義域 |
|:---------:|:--------:|:--------:|:-----------:|:----:|:------:|
| DeptID | INT | 部門編號 | PK | Not NULL | |
| Dept_name | VARCHAR | 部門名稱 | | Not NULL | |
| MgnID | INT | 經理員工編號 | FK: EMPLOYEE(EmpID) | Not NULL | |

- EMPLOYEE 表格
| 欄位名稱 | 資料型別 | 說明 | Keys | 限制 | 定義域 |
|:---------:|:--------:|:------------:|:----------------:|:---:|:---:|
| EmpID | INT | 員工編號 | PK | Not NULL, Unique | |
| Emp_name | VARCHAR | 員工姓名 | | Not NULL | |
| Emp_bth | DATE | 員工生日 | | Not NULL | |
| Emp_phone | VARCHAR | 員工電話 | | Not NULL | |
| Emp_dept | INT | 所屬部門編號 | FK: DEPT(DeptID) | | |

| Referential Trigger | On Update | On delete |
| :-: | :-: | :-: |
| Emp_dept | Cascade | Set NULL |

- MEMBER 表格
| 欄位名稱 | 資料型別 | 說明 | Keys | 限制 | 定義域 |
|:------------:|:--------:|:------------:|:-----------:|:----:|:------:|
| MemID | INT | 會員編號 | PK | Not NULL, Unique | |
| Mem_email | VARCHAR | 會員電子郵件 | | Not NULL, Unique | |
| Mem_name | VARCHAR | 會員名稱 | | Not NULL | | |
| Mem_address | TEXT | 會員地址 | | | |
| Mem_phone | VARCHAR | 會員電話 | | Not NULL | |
| Mem_bth | DATE | 會員生日 | | | |
| Mem_pass | TEXT | 會員密碼 | | Not NULL | |

- MERCHANDISE 表格
| 欄位名稱 | 資料型別 | 說明 | Keys | 限制 | 定義域 |
|:---------:|:--------:|:--------:|:-----------:|:----:|:------:|
| merID | INT | 商品編號 | PK | Not NULL, Unique | |
| mer_name | TEXT | 商品名稱 | | Not NULL | |
| retail_price | INT | 商品價格 | | Not NULL | |
| remain | INT | 商品剩餘數量 | | Not NULL | |
| start_date | DATE | 商品開賣日期 | | | |
| mer_pic | TEXT | 商品照片資料 | | | |

- ORDER 表格
| 欄位名稱 | 資料型別 | 說明 | Keys | 限制 | 定義域 |
|:---------------:|:--------:|:------------:|:-------------------:|:----:|:------:|
| OrdID | INT | 訂單編號 | PK | Not NULL, Unique | |
| Create_date | DATETIME | 訂單日期時間 | | Not NULL | |
| Way\_to\_pay | VARCHAR | 支付方式 | | Not NULL | \{Cash, Credit Card, Debit Card, Transfer, Prepaid\} |
| status | INT | 訂單狀態 | | Not NULL, Default: Normal | \{Pending,Delivering, Canceled\} |
| EmpID | INT | 負責員工編號 | FK: EMPLOYEE(EmpID) | Not NULL | |
| MemID | INT | 會員編號 | FK: MEMBER(MemID) | Not NULL | |
| Income | INT | 該訂單的實收金額| | Not NULL| |
| Phone | text | 收件者電話 | | Not NULL | |
| Address | text | 收件者地址 | | Not NULL | |

| Referential Trigger | On Update | On delete |
| :-: | :-: | :-: |
| EmpID | Cascade | Set NULL |
| MemID | Cascade | Set NULL |

- ORDER_DETAIL 表格
| 欄位名稱 | 資料型別 | 說明 | Keys | 限制 | 定義域 |
|:--------:|:--------:|:--------:|:----------------------:|:----:|:------:|
| OrdID | INT | 訂單編號 | PK, FK: ORDER(OrdID) | Not NULL | |
| MerID | INT | 商品編號 | PK, FK: MERCHANDISE(MerID) | Not NULL | |
| Quantity | INT | 購買的商品數 | | Not NULL | |

| Referential Trigger | On Update | On delete |
| :-: | :-: | :-: |
| OrdID | Cascade | Cascade |

- SUPPLIER 表格
| 欄位名稱 | 資料型別 | 說明 | Keys | 限制 | 定義域 |
|:----------:|:--------:|:----------:|:-----------:|:----:|:------:|
| SuppID | INT | 供應商編號 | PK | Not NULL, Unique | |
| Supp_name | VARCHAR | 供應商名 | Not NULL | |
| Supp_phone | VARCHAR | 供應商電話 | | Not NULL | |

- SUPPLIES 表格
| 欄位名稱 | 資料型別 | 說明 | Keys | 限制 | 定義域 |
|:----------:|:--------:|:----------:|:-----------:|:----:|:------:|
| SuppID | INT | 供應商編號 |PK, FK:SUPPLIER(SuppID) | Not NULL| |
| MerID | INT | 商品編號 | PK, FK:MERCHANDISE(MerID) | Not NULL| |
| Price | INT | 進貨價格 | | Not NULL | |
| Price_start_date | DATETIME | 進貨價開始日期 | | Not NULL | |

| Referential Trigger | On Update | On delete |
| :-: | :-: | :-: |
| SuppID | Cascade | Set NULL |
| MerID | Cascade | Set NULL |

### 2-5. 正規化設計
- 1NF : 資料已最小化，且每個資料都依賴主鍵，故符合1NF。
- 2NF : 每個資料表都完全依賴所有主鍵，沒有部份依賴的產生，故符合2NF。
- 3NF : 每個資料表中都沒有出現遞移相依的狀況，故符合3NF。
- BCNF : 每個資料表沒有產生主鍵依賴於非主鍵資料以及主鍵之間互相依賴的狀況，故符合BCNF。
- 4NF : 每個資料表中皆沒有出現多個一對多的資料合併的狀況，故符合4NF。

## 3. 系統實作

### 3-1. 系統架構
我們在前端使用 HTML、CSS 進行使用者和管理員介面的設計以及頁面之間的連結轉換。在按鈕和其他互動介面的部分我們使用 Javascript 處理。接著透過 ajax 的方式發送請求與後端的 php 進行傳輸。接著透過 php 和 MySQL 資料庫進行溝通。

前端的部分我們預計使用網頁方式且使用apache web server服務呈現，並且為了簡化伺服器及網站的架設過程，我們會使用xampp建立網站及資料庫。
### 3-2. 資料庫建置方式及資料來源說明
我們為了能夠有符合我們資料庫規則得資料，我們先找到一些真實存在的地理位置、使用者、管理者、商品名稱。再使用python亂數生成將資料隨機連結後賦予價格、年齡、ID、金額等隨機屬性，最後再將所生成得資料打包成SQL後匯出。總共生成了2001個使用者、2007筆訂單、對應著100098個商品($order\_detail$)以及53個員工。

### 3-3. 重要功能及SQL指令
1. 使用者
- 訂單成立指令

BEGIN;
SELECT `remain` FROM Merchandise WHERE `MerID` = ? FOR UPDATE;
if(`remain` - $amount >= 0)
    UPDATE Merchandise SET `remain` = `remain` - ? WHERE `merID` = ?;
    COMMIT;
else
    ROLLBACK;

這個指令在確認商品是否有足夠的數量提供客戶下單，若可以則`COMMIT`，不行則`ROLLBACK`。
- 存取訂單商品詳細資料

SELECT 
    od.MerID AS `MerID`, 
    od.Quantity AS `Quantity`, 
    mer.retail_price AS `Price`, 
    mer.mer_name AS `Name` 
FROM order_detail AS od 
JOIN merchandise AS mer ON od.MerID=mer.merID 
WHERE OrdID = ?;

這個指令會將下訂的商品明細與商品資料進行`JOIN`並取出訂單商品資訊。
- 獲取商品完整資料

SELECT 
    `MerID`, 
    `Mer_name`, 
    `Retail_price`, 
    `Mer_pic`, 
    `remain` 
FROM Merchandise WHERE `MerID` = ?;

這個指令會獲取指定商品編號的商品詳細資訊。
- 新增訂單申訴訊息

SELECT 
    mem.`Mem_name`, 
    mem.`Mem_email`, 
    ods.`EmpID` 
FROM `member` mem 
JOIN `orders` ods ON mem.`MemID` = ods.`CusID` 
WHERE ods.`OrdID` = ?;

INSERT INTO `complaint`(
    `complainant`, 
    `EmpID`, 
    `email`, 
    `reason`) 
VALUES (?, ?, ?, ?);

該指令會將訂單輸入的聯絡資料取出，並和申訴訊息一起輸入申訴的資料中。
- 取消訂單

UPDATE orders SET `status` = 2 WHERE `OrdID` = ?

該指令會將訂單資料更改為取消的狀態後進行更新。

2. 管理者
- 更新商品資訊

UPDATE Merchandise SET 
    mer_name=?,
    retail_price=?,
    remain=?,
    mer_pic=? 
WHERE `merID` = ?;

該指令會將商品資料依照管理者輸入的內容進行更新。
- 取得被投訴訊息

SELECT 
    `compID`, 
    `complainant`, 
    `email`, 
    `reason`, 
    `create_time` 
FROM `complaint` a LEFT 
JOIN `admins` b ON a.`EmpID` = b.`indice` 
WHERE b.`username` = ? OR a.`EmpID` = -1 
ORDER BY a.`create_time` DESC;

該指令會存取屬於投訴自己以及所有人的申訴資料。
- 登入查詢

SELECT `password` FROM admins WHERE `username` = ?;

該指令會尋找有沒有該帳戶的人，並存取該帳戶的密碼雜湊資料進行比對登入。
- 存取負責訂單

SELECT 
    `CusID`, 
    `OrdID`, 
    `Way_to_pay`, 
    `create_time`, 
    `income`, 
    `status`, 
    `Address`, 
    `Name`, 
    `Phone` 
FROM orders 
JOIN admins a ON EmpID = a.indice 
WHERE a.username = ? 
    AND create_time BETWEEN ? 
    AND DATE_ADD(?, INTERVAL 2 DAY) 
GROUP BY EmpID 
ORDER BY create_time DESC

該指令會使用自己的名字去尋找對應員工編號的負責訂單詳細資訊，且建立時間需要在設定的開始及結束日期的前後兩天之間。
- 註冊管理者帳號

SELECT `password` FROM admins WHERE `username` = ?;
if($num_row > 0){
    echo "User exists!";
    die();
} else {
INSERT INTO employee (
    `EmpID`, 
    `Emp_name`, 
    `Emp_bth`, 
    `Emp_phone`, 
    `Emp_dept`) 
VALUES (?, ?, ?, ?, ?);
}

這個指令會先尋找該帳號名稱有沒有被使用過，如果有則傳送錯誤訊息，若沒有則依照輸入的員工資訊建立員工帳號資料。

### 3-4. SQL 指令效能優化與索引建立分析
下方的三張圖展示了對於同一個 Query 建立 index 與否、對哪些資料建立 index 在執行時間方面和 Query plan 方面產生的變化。由下方的資訊可以看出，在是否建立 index 方面，有建立的 Query 明顯在執行時間上有所提升。然而在對其他相關資料表建立 index 後可以發現 Query planner 仍然選擇相同的 index，因此其他的 index 並沒有太大的幫助。
> 圖一、 No index

> 圖二、Index on $order\_detail$
> 

> 圖三、 Index on both $order\_detail$ and $merchandise$
> 

### 3-5 交易管理及併行控制
我們會在成立訂單的時候進行交易並行控制，以避免同個訂單被過多人同時下單造成庫存被超訂的狀況。首先我們在下訂單時會先`BEGIN`，並開始檢查每個商品的剩餘數量是否足夠完成訂單。若其中有一個商品不足則`ROLLBACK`並回傳商品不足，若足夠的話則先將數量更新並加上`WRITE LOCK`以避免被其他客戶端使用資料。當所有下訂的商品都足夠且更新好剩餘數量，則進行`COMMIT`確認修改剩餘數量。`COMMIT`成功後，則新增訂單明細加入至資料庫中，並回傳交易成功。

## 4. 分工資訊
- 前端實作: 吳秉哲、詹詠翔、趙子佾
- 後端實作: 詹詠翔、趙子佾
- 資料庫實作: 吳秉哲、趙子佾
- 系統規劃與設計: 吳秉哲、詹詠翔

## 5. 專案心得
- 吳秉哲
在這次的專案之前我從未碰過一個網頁的後端，也從沒有實際將前端和後端連接起來的經驗，因此我在這個部分花費了很多時間學習，也因此給隊友帶來了許多麻煩。但在這個過程中我對於前後端的概念也更加清楚，對於這兩者之間資料傳輸可能產生的問題也稍有認識。最重要的是我在這個過程中實際設計了一個系統並在這個過程中體驗到了許多作業中未曾體驗過的難處。最讓我印象深刻的就是我們資料庫中儲存的資料欄位該如何設計，有些table多了一個欄位似乎非常合理，但仔細思考並經過正規化後會發現實際上不應該這麼設計。這個經驗讓我對於生活中一些看似非常違反常理的設計稍微有點改觀。
- 詹詠翔
這次的專案比以往的都還要來的龐大很多，雖然之前資管導論就已經用php還有mysql架過網站，所實現的功能都沒有這次來的多。甚至以前根本就沒有考慮過資料正規化的問題。這次除了練習我的架站能力之外，多的是學到我以前所不知道的用法。實際參與整個從前端到後端再到資料庫的過程，雖然讓我連夜爆肝了許多天，體驗到了開發人員從頭開使設計一個網站的辛苦。但也在連結html、javascript、php、sql上多了許多經驗。
- 趙子佾
我認為這次的專案相對於之前所進行的專案架構規模大很多，因為雖然我之前在高中社團或是資管導論中也曾製作過全端的網頁內容，但由於這堂課程比較著重在資料庫的關聯大小上，因此也讓整體的專案變得更複雜，也更需要考慮更多在系統上的設計以及效能表現。另外在正規化的部分我也有感悟到小傑在上課提到為何大部分系統都只會設計到3NF的原因，因為若是使用到4NF會讓整體的系統變得異常複雜，會增加開發人員的負擔。最後這次的專案讓我體驗到更大規模的系統要如何設計，才能讓取得在開發成本以及系統效能之間的平衡點。