# 114下-資管3C資料庫設計 期末專題
第十七組：英文補習班後台管理系統
組員: 410630460 徐薏蓁

## 一、專題說明

本專題以我在英文補習班四年的打工經驗出發，設計一套專屬於補習班的資料庫管理系統。針對目前仍使用紙本記錄學生繳費、聯絡人查詢等問題，建立數位化後台以簡化行政流程、提升資料一致性與查詢效率。

**系統涵蓋模組：**

- 學生與教師基本資料管理
- 學生緊急聯絡人資料
- 班級資訊與課程報名
- 學生繳費記錄
- 教師授課安排與教學時數
- 薪資計算與家長聯絡支援

**技術面特色：**

- 使用 ERD 設計並正規化至 3NF
- Stored Procedure 自動計算教師薪資
- 索引優化查詢效能
- Trigger 記錄資料異動
- Transaction 維護資料一致性
- 結合 DBeaver 與 Node.js 前端互動實作

---

## 二、專題實作內容

### 1. 資料庫需求分析與規劃

#### a. 系統功能構想

- **學生報名與管理**
- **課程與班級管理**
- **教師排課與時數統計**
- **繳費管理**
- **資料查詢與報表輸出**

#### b. ER Diagram

ER圖：

ER 圖說明：

| 資料表 | 主鍵 / 外鍵 | 屬性 / 說明 |
|---------------|-------------|-------------|
| Students | PK: StudentID | ChineseName, EnglishName, Gender, BirthDate, Address, ClassID |
| Teachers | PK: TeacherID | ChineseName, EnglishName, Gender, BirthDate, Address, HourlyRate, Phone |
| Classes | PK: ClassID | ClassName, Tuition, Schedule |
| Enrollment | PK: EnrollID <br> FK: StudentID, ClassID | Term, EnrollDate |
| Payment | PK: PaymentID <br> FK: EnrollID, StudentID | PayDate, Amount, Method |
| TeacherClass | PK: TeacherID + ClassID | HoursWorked |
| WorkHours | PK: WorkID <br> FK: TeacherID | WorkDate, Hours |
| EmergencyContacts | PK: ContactID <br> FK: StudentID | Name, Relation, Phone |
| PaymentLog | PK: LogID <br> FK: StudentID | LogMessage, CreatedAt |
| PaymentSummary | View | StudentID, EnglishName, ClassName, Amount, PayDate |

---

#### 2. 正規化過程說明

##### a. 初始未正規化資料範例
學生 S001，中文名「吳品霏」、英文名「Nova」，女性，生日 2007/01/23，地址「新北市蘆洲區中正路123號5樓」，就讀班級 C01「Puppy」，學費 9000 元，每週二、五 16:30-18:30，授課老師 T003（陳筠潔 Amy），女性，生日 1992/03/28，住在台北市大同區承德路五段88號，時薪 700 元，電話 [phone]；她在 2025 春季學期報名，報名編號為 E001，報名日期為 2025/02/01，付款紀錄為 P001，於 2025/02/10 繳費 9000 元，使用現金支付；緊急聯絡人為「吳媽媽」，關係為「母親」，電話 [phone]。

##### b. 1NF → 2NF 處理方式

- 拆分成多張表（Students, Classes, Teachers, Enrollment, Payment, EmergencyContacts）
- 消除部分依賴（如：學生地址依賴 StudentID，教師時薪依賴 TeacherID）
- 建立外鍵關聯保持結構完整

##### c. 2NF → 3NF 處理方式

- 消除傳遞依賴（如 ClassID → Tuition，不應存在於 Students 表）
- 保留 ClassID 作為外鍵，詳細資訊改由 JOIN 查詢取得

##### d. 資料表設計總覽

| 資料表 | 主鍵 | 外鍵 |
|----------------|------|------|
| Students | StudentID | ClassID → Classes |
| Teachers | TeacherID | 無 |
| Classes | ClassID | 無 |
| Enrollment | EnrollID | StudentID → Students <br> ClassID → Classes |
| Payment | PaymentID | EnrollID → Enrollment <br> StudentID → Students |
| PaymentLog | LogID | StudentID → Students |
| PaymentSummary | *(View)* | 無 |
| Teaches | TeacherID + ClassID | TeacherID → Teachers <br> ClassID → Classes |
| WorkHours | WorkID / TeacherID + WorkDate | TeacherID → Teachers |
| EmergencyContacts | ContactID | StudentID → Students |

---

### 2. 基本操作與進階功能

#### a. Schema 建立與資料插入

CREATE TABLE Class (
    ClassID      INT PRIMARY KEY,
    ClassName    VARCHAR(100) NOT NULL,
    Schedule     VARCHAR(50),        -- 上課時間或期別說明
    TeacherID    INT NOT NULL,
    Fee          DECIMAL(7,2),
    FOREIGN KEY (TeacherID) REFERENCES Teacher(TeacherID)
);

INSERT INTO Classes (ClassID, ClassName, Tuition, Schedule) VALUES
('C01', 'Puppy', 9000.00, '每週二、五 16:30-18:30'),
('C02', 'Sun', 9000.00, '每週二、五 16:30-18:30'),
('C03', 'Grass', 10800.00, '每週一、四 16:30-18:30'),
('C04', 'Nike', 14400.00, '每週六 09:00-12:00'),
('C05', 'Capybara', 10800.00, '每週一、四 19:00-21:00');

#### b. 索引與效能考量：為常用查詢欄位建立索引

-- 學生班級查詢
CREATE INDEX idx_students_class ON Students(ClassID);

-- 查詢某學生報名課程
CREATE INDEX idx_enroll_student ON Enrollment(StudentID);

-- 查詢某班被哪些學生選
CREATE INDEX idx_enroll_class ON Enrollment(ClassID);

-- 查詢付款紀錄對應哪筆報名
CREATE INDEX idx_payment_enroll ON Payment(EnrollID);

-- 查老師上班時數（常 JOIN）
CREATE INDEX idx_work_teacher ON WorkHours(TeacherID);

-- 老師模糊查詢
CREATE INDEX idx_teacher_name ON Teachers(ChineseName);

#### c. 交易 (Transaction) 機制：BEGIN、COMMIT、ROLLBACK 範例

const express = require('express');
const router = express.Router();
const sequelize = require('../database');
const Enrollment = require('../models/Enrollment');
const Payment = require('../models/Payment');

router.post('/', async (req, res) => {
  const { studentId, classId, term, amount, method } = req.body;

  const t = await sequelize.transaction();
  try {
    const enroll = await Enrollment.create({
      StudentID: studentId,
      ClassID: classId,
      Term: term,
      EnrollDate: new Date()
    }, { transaction: t });

    await Payment.create({
      EnrollID: enroll.EnrollID,
      Amount: amount,
      PayDate: new Date(),
      Method: method
    }, { transaction: t });

    await t.commit();
    res.json({ message: '報名與繳費成功！' });
  } catch (err) {
    await t.rollback();
    res.status(500).json({ message: '交易失敗，已回滾。', error: err.message });
  }
});

module.exports = router;

---
### 3.進階 SQL 功能應用

#### a. 複雜查詢與子查詢：聚合函數、GROUP BY、HAVING、視圖 (View)
##### 聚合函數(計算教師薪資)

CREATE PROCEDURE CalcSalary(IN tid VARCHAR(10))
BEGIN
  SELECT 
    t.TeacherID, 
    t.EnglishName, 
    SUM(w.Hours * t.HourlyRate) AS MonthlySalary
  FROM 
    Teachers t
    JOIN WorkHours w ON t.TeacherID = w.TeacherID
  WHERE 
    t.TeacherID = tid
  GROUP BY 
    t.TeacherID, t.EnglishName;
END;

##### GROUP BY、Having(查詢繳費超過 12000 元的學生)

SELECT StudentID, SUM(Amount) AS TotalPaid
FROM Payment
GROUP BY StudentID
HAVING SUM(Amount) > 12000;

##### 建立視圖

CREATE VIEW PaymentSummary AS
SELECT 
  s.StudentID,
  s.EnglishName,
  c.ClassName,
  p.Amount,
  p.PayDate
FROM Payment p
JOIN Students s ON p.StudentID = s.StudentID
JOIN Enrollment e ON p.EnrollID = e.EnrollID
JOIN Classes c ON e.ClassID = c.ClassID;

#### b.Stored Procedure / Function：實作商業邏輯或報表計算

CALL CalcSalary('T001');

#### c.Trigger：自動處理插入、更新、刪除事件

CREATE TRIGGER trg_PaymentLog_AfterInsert
AFTER INSERT ON Payments
FOR EACH ROW
INSERT INTO PaymentLog (StudentID, LogMessage, CreatedAt)
VALUES (
  NEW.StudentID,
  CONCAT('完成繳費，金額：', NEW.Amount, '，方式：', NEW.Method),
  NOW()
);

INSERT INTO Payments (EnrollID, StudentID, PayDate, Amount, Method)
VALUES (1, 'S001', '2025-06-18', 9000, '轉帳');

SELECT * FROM PaymentLog WHERE StudentID = 'S001';

#### d.安全性與權限管理 (可選)：建立 ROLE、分配權限

CREATE ROLE 'accounting';

GRANT SELECT, INSERT ON JoyEnglish.Payments TO 'accounting';

CREATE USER 'joyuser'@'localhost' IDENTIFIED BY 'joypass';

GRANT 'accounting' TO 'joyuser'@'localhost';

SHOW GRANTS FOR 'joyuser'@'localhost';

---
### 4. 功能測試

#### a. 功能查詢清單

查詢 Sun 班(C02)所有學生

SELECT *
FROM Students
WHERE ClassID = 'C02';

查詢某位學生的上課與繳費歷史

SELECT s.StudentID, c.ClassName, SUM(p.Amount) AS TotalPaid, MAX(p.PayDate) AS LastPaymentDate
FROM Students s
JOIN Enrollment e ON s.StudentID = e.StudentID
JOIN Classes c ON e.ClassID = c.ClassID
LEFT JOIN Payment p ON e.EnrollID = p.EnrollID
WHERE s.StudentID = 'S003'
GROUP BY s.StudentID, c.ClassName;

#### b.資料一致性與異常情況測試：外鍵約束、Transaction Rollback
交易失敗則回滾:

#### c.比較索引前後查詢時間
系統對 WorkHours.WorkDate 建立索引。原查詢執行計畫為 type=ALL（全表掃描）；建立索引後為 type=ref，表示已使用索引查找，效能提升明顯。

EXPLAIN
SELECT *
FROM WorkHours
WHERE WorkDate = '2025-03-04';

CREATE INDEX idx_WorkDate ON WorkHours(WorkDate);

EXPLAIN
SELECT *
FROM WorkHours
WHERE WorkDate = '2025-03-04';

---
### 5. 文件與報告
**a. 成果 Demo**
[DEMO影片](https://youtu.be/EVE_OiU-QcU)
**b. 組員分工說明**
| 組員 | 學號 | 分工 |
|----------------|------|------|
| 徐薏蓁 | 410630460 | 資料庫設計、SQL實作、DBeaver操作、文件撰寫與簡報報告 |

**c. GitHub**
[GitHub](https://youtu.be/EVE_OiU-QcU)