# 資料庫期末專題
## 一、專題說明
### 主題：高鐵訂票與乘車管理系統
* 我們想要模擬一套完整的高鐵訂票與乘車管理平台，包含乘客資訊管理、列車班次與時刻查詢、訂票流程、座位分配、訂單整合與付款紀錄等功能，透過資料庫的結構設計與 SQL 操作，使訂票資料具備一致性、完整性與可擴充性，並能支援真實的高鐵訂票場景。

### 資料表內容
| 資料表名稱 | 功能描述 |
|----------------------|--------------------------------------------------|
| `Passengers`（乘客） | 記錄會員 / 旅客基本資料 |
| `Trains`（列車） | 各班次列車基本資料 |
| `Stations`（車站） | 高鐵所有站點 |
| `TrainSchedules`（行駛時刻） | 某列車經過哪些站點，抵達 / 離站時間 |
| `Tickets`（訂票紀錄） | 訂票資訊，含起訖站、座位、價格等 |
| `Seats`（座位） | 座位資料，與列車與車廂關聯 |
| `Orders`（訂單） | 一筆交易可能包含多張票 |
| `PaymentRecords`（付款） | 記錄付款狀況與方式 |

### 查詢重點
1. 特定時間某列車的訂票率 / 座位剩餘狀況
2. 乘客歷史搭乘紀錄（含起訖站與時刻）
3. 某日各站上下車人數統計表
4. 熱門路段排行（例如：台北 ⇌ 台中）
5. 座位安排報表（可依時間 / 列車查詢空位）
6. 月營收統計 / 支付方式分布
7. 搭乘高峰時段分析（例如週五晚上最多人搭）

## 二、專題實作內容
### 1. 資料庫需求分析與規劃
#### 系統功能構想
本系統模擬高鐵訂票平台的核心功能，針對乘車流程中常見操作進行資料庫設計。主要功能包括：
- **訂票**：乘客選擇車次、起訖站、日期與座位進行購票
- **出票**：系統自動指派座位，產生電子票券記錄
- **查詢訂單**：乘客可查詢自己的歷史訂票紀錄
- **查詢時刻**：查詢指定列車的行經站點與時刻表
- **空位查詢**：根據班次與時間查詢剩餘座位
- **線上付款**：支援信用卡與轉帳付款
- **統計分析（管理員）**：熱門路段、訂票率、搭乘高峰分析

#### ER Diagram

#### 正規化
* 尚未正規化的資料表

| 訂單編號 | 乘客ID | 姓名 | Email | 電話 | 票券編號 | 列車ID | 車次號碼 | 車次類型 | 起站ID | 起站名稱 | 迄站ID | 迄站名稱 | 座位ID | 車廂 | 座號 | 搭乘日期 | 起站時間 | 迄站時間 | 票價 | 訂單日期 | 付款編號 | 付款方式 |
|----------|--------|--------|------------------|-------------|----------|--------|----------|----------|--------|----------|--------|----------|--------|------|------|--------------|----------|----------|------|--------------|----------|----------|
| O001 | P001 | 王小明 | [e-mail] | 0912345678 | TK001 | T100 | 1201 | 標準車次 | S01 | 台北 | S05 | 台中 | S1001 | 2 | 5A | 2025-07-01 | 08:00 | 09:35 | 1200 | 2025-06-15 | PM001 | 信用卡 |
| O002 | P002 | 陳美麗 | [e-mail] | 0987654321 | TK002 | T101 | 1233 | 自由座車次 | S05 | 台中 | S08 | 左營 | S2001 | 3 | 8C | 2025-07-01 | 09:30 | 11:10 | 1100 | 2025-06-20 | PM002 | 轉帳 |

* 第一正規化（1NF）資料表

說明：
    - 每一張表對應一個「單一主題」：如乘客、列車、票券、付款
    - 每張表有清楚的主鍵（PK）與外鍵（FK）
    - 所有資料欄位都是原子值，不再混合或重複
    - 表與表之間關聯清楚，可串聯查詢

**Passengers（乘客）**

| PassengerID (PK) | Name | Email | Phone |
|------------------|--------|------------------|-------------|
| P001 | 王小明 | [e-mail] | 0912345678 |
| P002 | 陳美麗 | [e-mail] | 0987654321 |

**Orders（訂單）**

| OrderID (PK) | PassengerID (FK) | OrderDate |
|--------------|------------------|-------------|
| O001 | P001 | 2025-06-15 |
| O002 | P002 | 2025-06-20 |

**Trains（列車）**

| TrainID (PK) | TrainNumber | TrainType |
|--------------|-------------|-----------|
| T100 | 1201 | 標準車次 |
| T101 | 1233 | 自由座車次 |

**Stations（車站）**

| StationID (PK) | StationName |
|----------------|-------------|
| S01 | 台北 |
| S05 | 台中 |
| S08 | 左營 |

**Seats（座位）**

| SeatID (PK) | TrainID (FK) | Carriage | SeatNumber |
|-------------|--------------|----------|-------------|
| S1001 | T100 | 2 | 5A |
| S2001 | T101 | 3 | 8C |

**Tickets（票券）**

| TicketID (PK) | OrderID (FK) | TrainID (FK) | SeatID (FK) | FromStationID (FK) | ToStationID (FK) | TravelDate | DepartureTime | ArrivalTime | Price |
|----------------|---------------|---------------|--------------|---------------------|-------------------|---------------|----------------|--------------|--------|
| TK001 | O001 | T100 | S1001 | S01 | S05 | 2025-07-01 | 08:00 | 09:35 | 1200 |
| TK002 | O002 | T101 | S2001 | S05 | S08 | 2025-07-01 | 09:30 | 11:10 | 1100 |

**PaymentRecords（付款）**

| PaymentID (PK) | OrderID (FK) | PaymentDate | Amount | PaymentMethod |
|----------------|---------------|--------------|--------|----------------|
| PM001 | O001 | 2025-06-15 | 1200 | 信用卡 |
| PM002 | O002 | 2025-06-20 | 1100 | 轉帳 |

* 第二正規化（2NF）資料表

說明：
    - 在符合 1NF 的基礎上
    - 消除「部分依賴」：所有非主鍵欄位必須完全依賴整個主鍵
      （若主鍵是複合鍵，則不能只依賴其中一部分）
      
Tickets 表原本包含許多非主鍵欄位（如時間與站名），
但這些欄位其實應該依賴行程（Schedule），而不是票券本身，
因此我們將時刻表資訊獨立成一張 `TrainSchedules` 表。

**Tickets（票券）**
> 表示一張票對應的搭乘行程與座位資訊

| TicketID (PK) | OrderID (FK) | ScheduleID (FK) | SeatID (FK) | Price |
|---------------|---------------|------------------|-------------|--------|
| TK001 | O001 | SCH001 | S1001 | 1200 |
| TK002 | O002 | SCH002 | S2001 | 1100 |

**TrainSchedules（行程）**
> 負責描述行車班次、起訖站、時間與日期，獨立成為一張主表

| ScheduleID (PK) | TrainID (FK) | FromStationID (FK) | ToStationID (FK) | TravelDate | DepartureTime | ArrivalTime |
|------------------|---------------|---------------------|-------------------|---------------|----------------|--------------|
| SCH001 | T100 | S01 | S05 | 2025-07-01 | 08:00 | 09:35 |
| SCH002 | T101 | S05 | S08 | 2025-07-01 | 09:30 | 11:10 |

**Passengers（乘客）**

| PassengerID (PK) | Name | Email | Phone |
|------------------|--------|------------------|-------------|
| P001 | 王小明 | [e-mail] | 0912345678 |
| P002 | 陳美麗 | [e-mail] | 0987654321 |

**Orders（訂單）**

| OrderID (PK) | PassengerID (FK) | OrderDate |
|--------------|------------------|-------------|
| O001 | P001 | 2025-06-15 |
| O002 | P002 | 2025-06-20 |

**Trains（列車）**

| TrainID (PK) | TrainNumber | TrainType |
|--------------|-------------|-----------|
| T100 | 1201 | 標準車次 |
| T101 | 1233 | 自由座車次 |

**Stations（車站）**

| StationID (PK) | StationName |
|----------------|-------------|
| S01 | 台北 |
| S05 | 台中 |
| S08 | 左營 |

**Seats（座位）**

| SeatID (PK) | TrainID (FK) | Carriage | SeatNumber |
|-------------|--------------|----------|-------------|
| S1001 | T100 | 2 | 5A |
| S2001 | T101 | 3 | 8C |

**PaymentRecords（付款）**

| PaymentID (PK) | OrderID (FK) | PaymentDate | Amount | PaymentMethod |
|----------------|---------------|--------------|--------|----------------|
| PM001 | O001 | 2025-06-15 | 1200 | 信用卡 |
| PM002 | O002 | 2025-06-20 | 1100 | 轉帳 |

* 第三正規化（3NF）資料表

說明：
    - 在符合 2NF 的基礎上
    - 消除「遞移依賴」
    - 每個非主鍵欄位必須只依賴主鍵，不能間接依賴其他非主鍵

在 2NF 中，`Trains` 表包含 `TrainNumber` 與 `TrainType`，但 TrainNumber 是展示用車次號，
TrainType 其實是依賴 TrainNumber，不是 TrainID，這屬於遞移依賴 

→ 不符合 3NF，所以我們將他拆分為新的 `TrainNumbers` 表

**Trains（列車）**
> 每台列車對應一個車次號碼

| TrainID (PK) | TrainNumber (FK) |
|--------------|------------------|
| T100 | 1201 |
| T101 | 1233 |

**TrainNumbers（車次資訊）**
> 車次號碼與車種為靜態對照表

| TrainNumber (PK) | TrainType |
|------------------|-----------|
| 1201 | 標準車次 |
| 1233 | 自由座車次 |

**Passengers（乘客）**

| PassengerID (PK) | Name | Email | Phone |
|------------------|--------|------------------|-------------|
| P001 | 王小明 | [e-mail] | 0912345678 |
| P002 | 陳美麗 | [e-mail] | 0987654321 |

**Orders（訂單）**

| OrderID (PK) | PassengerID (FK) | OrderDate |
|--------------|------------------|-------------|
| O001 | P001 | 2025-06-15 |
| O002 | P002 | 2025-06-20 |

**Stations（車站）**

| StationID (PK) | StationName |
|----------------|-------------|
| S01 | 台北 |
| S05 | 台中 |
| S08 | 左營 |

**Seats（座位）**

| SeatID (PK) | TrainID (FK) | Carriage | SeatNumber |
|-------------|--------------|----------|-------------|
| S1001 | T100 | 2 | 5A |
| S2001 | T101 | 3 | 8C |

**TrainSchedules（行程）**

| ScheduleID (PK) | TrainID (FK) | FromStationID (FK) | ToStationID (FK) | TravelDate | DepartureTime | ArrivalTime |
|------------------|---------------|---------------------|-------------------|---------------|----------------|--------------|
| SCH001 | T100 | S01 | S05 | 2025-07-01 | 08:00 | 09:35 |
| SCH002 | T101 | S05 | S08 | 2025-07-01 | 09:30 | 11:10 |

**Tickets（票券）**

| TicketID (PK) | OrderID (FK) | ScheduleID (FK) | SeatID (FK) | Price |
|---------------|---------------|------------------|-------------|--------|
| TK001 | O001 | SCH001 | S1001 | 1200 |
| TK002 | O002 | SCH002 | S2001 | 1100 |

**PaymentRecords（付款）**

| PaymentID (PK) | OrderID (FK) | PaymentDate | Amount | PaymentMethod |
|----------------|---------------|--------------|--------|----------------|
| PM001 | O001 | 2025-06-15 | 1200 | 信用卡 |
| PM002 | O002 | 2025-06-20 | 1100 | 轉帳 |

### 資料表設計說明（第三正規化後）
#### 主鍵設計原則
* 每張表必須有唯一識別一筆資料的欄位。

#### 外鍵設計目標
* 保持資料一致性（資料正規化後，避免重複欄位）。
* 能夠串接其他表格以進行查詢（JOIN 查詢）。

**Passengers（乘客）**
* 主鍵（PK）：PassengerID，唯一識別每位乘客。

- Passengers 是主表，用來儲存每位乘客的基本資料。
- 主鍵是 PassengerID，用來唯一識別每位乘客。
- 外鍵的設計邏輯是「用來參考其他資料表的主鍵」，而乘客表不需要參考其他表格，因此不會出現外鍵欄位。

**Orders（訂單）**
* 主鍵（PK）：OrderID，識別一筆完整的訂單。
* 外鍵（FK）：PassengerID → Passengers，表示該訂單屬於哪位乘客。

**PaymentRecords（付款紀錄）**
* 主鍵（PK）：PaymentID，識別每一筆付款。
* 外鍵（FK）：OrderID → Orders，表示該筆付款屬於哪張訂單。

**TrainNumbers（車次資訊）**
* 主鍵（PK）：TrainNumber，識別一個公用的車次號碼，與列車類型綁定。

這張表沒有外鍵，因為這是一張靜態對照表：
- TrainNumber 是主鍵，表示唯一的車次編號。
- TrainType 是對應的類別名稱（例如標準車次、自由座車次）。
- 這張表的目的是供其他資料表查詢或對應「車次屬性」，自己本身並不依賴其他表。

**Trains（列車）**
* 主鍵（PK）：TrainID，識別每一台實體列車。
* 外鍵（FK）：TrainNumber → TrainNumbers，表示這台列車使用的是哪一個車次號碼/類型。

**Stations（車站）**
* 主鍵（PK）：StationID，識別車站名稱的代碼，避免儲存重複文字（如「台北」）。

- Stations 是對照表，用來列出所有可用的車站（如「台北」、「台中」、「左營」）。
- 每筆車站資料由 StationID 作為主鍵唯一標識。
- 它不需要參考其他表格的主鍵，因此不含外鍵。

**TrainSchedules（行程時刻表）**
* 主鍵（PK）：ScheduleID，識別一個完整的行程（列車+時間+起訖站）。
* 外鍵（FK）：
* TrainID → Trains：指定是哪一台列車。
* FromStationID, ToStationID → Stations：指定起站與迄站。

**Seats（座位）**
* 主鍵（PK）：SeatID，識別一個車廂內的座位。
* 外鍵（FK）：TrainID → Trains，表示該座位屬於哪一列車。

**Tickets（票券）**
* 主鍵（PK）：TicketID，識別一張票。
* 外鍵（FK）：
* OrderID → Orders：票是由哪張訂單購買。
* ScheduleID → TrainSchedules：票是搭哪一個班次。
* SeatID → Seats：該票對應哪個座位。

### 2. 資料表建立與基本操作
#### 2.1 Schema 建立與初始資料插入 (CREATE TABLE, INSERT)
* **建立 `TrainNumbers` 表（車次號碼與類型）並插入資料**

CREATE TABLE TrainNumbers (
    TrainNumber VARCHAR(10) PRIMARY KEY,
    TrainType VARCHAR(20) NOT NULL
);

INSERT INTO TrainNumbers (TrainNumber, TrainType) VALUES
('1201', '商務車次'),
('1202', '自由座車次'),
('1203', '商務車次'),
('1204', '商務車次'),
('1205', '標準車次'),
('1206', '標準車次'),
('1207', '標準車次'),
('1208', '自由座車次'),
('1209', '商務車次'),
('1210', '自由座車次'),
('1211', '標準車次'),
('1212', '自由座車次'),
('1213', '商務車次'),
('1214', '標準車次'),
('1215', '商務車次'),
('1216', '自由座車次'),
('1217', '標準車次'),
('1218', '商務車次'),
('1219', '自由座車次'),
('1220', '標準車次'),
('1221', '標準車次'),
('1222', '自由座車次'),
('1223', '自由座車次'),
('1224', '標準車次'),
('1225', '自由座車次'),
('1226', '標準車次'),
('1227', '標準車次'),
('1228', '標準車次'),
('1229', '商務車次'),
('1230', '自由座車次');


* **建立 `Trains` 表（列車 ID 對應車次號碼）並插入資料**

CREATE TABLE Trains (
    TrainID VARCHAR(10) PRIMARY KEY,
    TrainNumber VARCHAR(10),
    FOREIGN KEY (TrainNumber) REFERENCES TrainNumbers(TrainNumber)
);

INSERT INTO Trains (TrainID, TrainNumber) VALUES
('T100', '1201'),
('T101', '1202'),
('T102', '1203'),
('T103', '1204'),
('T104', '1205'),
('T105', '1206'),
('T106', '1207'),
('T107', '1208'),
('T108', '1209'),
('T109', '1210'),
('T110', '1211'),
('T111', '1212'),
('T112', '1213'),
('T113', '1214'),
('T114', '1215'),
('T115', '1216'),
('T116', '1217'),
('T117', '1218'),
('T118', '1219'),
('T119', '1220'),
('T120', '1221'),
('T121', '1222'),
('T122', '1223'),
('T123', '1224'),
('T124', '1225'),
('T125', '1226'),
('T126', '1227'),
('T127', '1228'),
('T128', '1229'),
('T129', '1230');


* **建立 `Passengers` 表並插入資料**

CREATE TABLE Passengers (
    PassengerID VARCHAR(10) PRIMARY KEY,
    Name VARCHAR(50) NOT NULL,
    Email VARCHAR(100),
    Phone VARCHAR(15)
);

INSERT INTO Passengers (PassengerID, Name, Email, Phone) VALUES
('P001', '張郁婷', '[e-mail]', '0910724665'),
('P002', '韓俊宏', '[e-mail]', '0996298318'),
('P003', '李柏翰', '[e-mail]', '0923094649'),
('P004', '金淑玲', '[e-mail]', '0949478566'),
('P005', '莫琬婷', '[e-mail]', '0946212918'),
('P006', '田雅琪', '[e-mail]', '095107623'),
('P007', '謝慧君', '[e-mail]', '0742590197'),
('P008', '沈佩君', '[e-mail]', '01364661396'),
('P009', '劉淑貞', '[e-mail]', '0189775894'),
('P010', '李家銘', '[e-mail]', '0361110093'),
('P011', '潘宜君', '[e-mail]', '0954248743'),
('P012', '余心怡', '[e-mail]', '0956608372'),
('P013', '潘雅琪', '[e-mail]', '0238884392'),
('P014', '張承翰', '[e-mail]', '0030701858'),
('P015', '胡佳玲', '[e-mail]', '09976008844'),
('P016', '邱怡君', '[e-mail]', '0981729937'),
('P017', '曾雅芳', '[e-mail]', '0981526665'),
('P018', '林佳慧', '[e-mail]', '0932041812'),
('P019', '楊宗憲', '[e-mail]', '0918252734'),
('P020', '曾宛君', '[e-mail]', '0953875437'),
('P021', '吳芝儀', '[e-mail]', '0973642981'),
('P022', '蕭文隆', '[e-mail]', '0970039543'),
('P023', '吳彥志', '[e-mail]', '0915488160'),
('P024', '劉玉婷', '[e-mail]', '0920136683'),
('P025', '賴怡君', '[e-mail]', '0930758043'),
('P026', '曾琬婷', '[e-mail]', '0958003724'),
('P027', '江柏翰', '[e-mail]', '0920508322'),
('P028', '簡家豪', '[e-mail]', '0917691346'),
('P029', '黃思妤', '[e-mail]', '0984298373'),
('P030', '謝柏宇', '[e-mail]', '0932224455');


* **建立 `Orders` 表並插入資料**

CREATE TABLE Orders (
    OrderID VARCHAR(10) PRIMARY KEY,
    PassengerID VARCHAR(10),
    OrderDate DATE,
    FOREIGN KEY (PassengerID) REFERENCES Passengers(PassengerID)
);

INSERT INTO Orders (OrderID, PassengerID, OrderDate) VALUES
('O001', 'P001', '2025-06-30'),
('O002', 'P002', '2025-06-18'),
('O003', 'P003', '2025-06-05'),
('O004', 'P004', '2025-06-21'),
('O005', 'P005', '2025-06-11'),
('O006', 'P006', '2025-06-22'),
('O007', 'P007', '2025-06-20'),
('O008', 'P008', '2025-06-30'),
('O009', 'P009', '2025-06-14'),
('O010', 'P010', '2025-06-25'),
('O011', 'P011', '2025-06-10'),
('O012', 'P012', '2025-06-06'),
('O013', 'P013', '2025-06-08'),
('O014', 'P014', '2025-06-08'),
('O015', 'P015', '2025-06-19'),
('O016', 'P016', '2025-06-24'),
('O017', 'P017', '2025-06-21'),
('O018', 'P018', '2025-06-29'),
('O019', 'P019', '2025-06-16'),
('O020', 'P020', '2025-06-24'),
('O021', 'P021', '2025-06-12'),
('O022', 'P022', '2025-06-19'),
('O023', 'P023', '2025-06-11'),
('O024', 'P024', '2025-06-05'),
('O025', 'P025', '2025-06-21'),
('O026', 'P026', '2025-06-27'),
('O027', 'P027', '2025-06-22'),
('O028', 'P028', '2025-06-24'),
('O029', 'P029', '2025-06-03'),
('O030', 'P030', '2025-06-13');


* **建立 `Stations` 表並插入資料**

CREATE TABLE Stations (
    StationID VARCHAR(10) PRIMARY KEY,
    StationName VARCHAR(50) NOT NULL
);

INSERT INTO Stations (StationID, StationName) VALUES
('S001', '台北'),
('S002', '板橋'),
('S003', '桃園'),
('S004', '中壢'),
('S005', '新竹'),
('S006', '竹南'),
('S007', '苗栗'),
('S008', '豐原'),
('S009', '台中'),
('S010', '彰化'),
('S011', '員林'),
('S012', '南投'),
('S013', '斗六'),
('S014', '嘉義'),
('S015', '新營'),
('S016', '台南'),
('S017', '善化'),
('S018', '岡山'),
('S019', '楠梓'),
('S020', '左營'),
('S021', '高雄'),
('S022', '屏東'),
('S023', '潮州'),
('S024', '枋寮'),
('S025', '台東'),
('S026', '知本'),
('S027', '花蓮'),
('S028', '宜蘭'),
('S029', '羅東'),
('S030', '基隆');


* **建立 `Seats` 表並插入資料**

CREATE TABLE Seats (
    SeatID VARCHAR(10) PRIMARY KEY,
    TrainID VARCHAR(10),
    Carriage INT,
    SeatNumber VARCHAR(10),
    FOREIGN KEY (TrainID) REFERENCES Trains(TrainID)
);

INSERT INTO Seats (SeatID, TrainID, Carriage, SeatNumber) VALUES
('S0001', 'T100', 5, '5B'),
('S0002', 'T101', 2, '8B'),
('S0003', 'T102', 3, '7F'),
('S0004', 'T103', 2, '2D'),
('S0005', 'T104', 3, '7C'),
('S0006', 'T105', 4, '9E'),
('S0007', 'T106', 5, '1A'),
('S0008', 'T107', 4, '8B'),
('S0009', 'T108', 3, '4C'),
('S0010', 'T109', 1, '7B'),
('S0011', 'T110', 2, '5F'),
('S0012', 'T111', 3, '4F'),
('S0013', 'T112', 5, '9A'),
('S0014', 'T113', 3, '8B'),
('S0015', 'T114', 2, '7F'),
('S0016', 'T115', 3, '2D'),
('S0017', 'T116', 1, '4D'),
('S0018', 'T117', 2, '3B'),
('S0019', 'T118', 4, '6A'),
('S0020', 'T119', 3, '1F'),
('S0021', 'T120', 5, '8E'),
('S0022', 'T121', 2, '5C'),
('S0023', 'T122', 3, '3B'),
('S0024', 'T123', 5, '4B'),
('S0025', 'T124', 4, '5C'),
('S0026', 'T125', 4, '3F'),
('S0027', 'T126', 2, '6F'),
('S0028', 'T127', 5, '4C'),
('S0029', 'T128', 5, '4B'),
('S0030', 'T129', 3, '8F');


* **建立 `TrainSchedules` 表並插入資料**

CREATE TABLE TrainSchedules (
    ScheduleID VARCHAR(10) PRIMARY KEY,
    TrainID VARCHAR(10),
    FromStationID VARCHAR(10),
    ToStationID VARCHAR(10),
    TravelDate DATE,
    DepartureTime TIME,
    ArrivalTime TIME,
    FOREIGN KEY (TrainID) REFERENCES Trains(TrainID),
    FOREIGN KEY (FromStationID) REFERENCES Stations(StationID),
    FOREIGN KEY (ToStationID) REFERENCES Stations(StationID)
);

INSERT INTO TrainSchedules (ScheduleID, TrainID, FromStationID, ToStationID, TravelDate, DepartureTime, ArrivalTime) VALUES
('TS001', 'T100', 'S001', 'S005', '2025-07-01', '08:17', '10:33'),
('TS002', 'T101', 'S001', 'S005', '2025-07-01', '08:11', '10:33'),
('TS003', 'T102', 'S001', 'S005', '2025-07-05', '08:17', '10:35'),
('TS004', 'T103', 'S001', 'S005', '2025-07-03', '08:12', '10:29'),
('TS005', 'T104', 'S001', 'S009', '2025-07-05', '08:45', '10:02'),
('TS006', 'T105', 'S001', 'S009', '2025-07-10', '08:16', '10:51'),
('TS007', 'T106', 'S005', 'S009', '2025-07-14', '08:34', '10:19'),
('TS008', 'T107', 'S001', 'S009', '2025-07-08', '08:20', '10:06'),
('TS009', 'T108', 'S005', 'S009', '2025-07-09', '08:21', '10:17'),
('TS010', 'T109', 'S001', 'S009', '2025-07-11', '08:33', '10:03'),
('TS011', 'T110', 'S005', 'S009', '2025-07-05', '08:59', '10:19'),
('TS012', 'T111', 'S005', 'S009', '2025-07-01', '08:02', '10:53'),
('TS013', 'T112', 'S001', 'S005', '2025-07-03', '08:14', '10:13'),
('TS014', 'T113', 'S001', 'S005', '2025-07-03', '08:16', '10:19'),
('TS015', 'T114', 'S001', 'S009', '2025-07-02', '08:40', '10:49'),
('TS016', 'T115', 'S001', 'S009', '2025-07-12', '08:48', '10:17'),
('TS017', 'T116', 'S001', 'S005', '2025-07-13', '08:28', '10:14'),
('TS018', 'T117', 'S001', 'S009', '2025-07-10', '08:02', '10:30'),
('TS019', 'T118', 'S001', 'S009', '2025-07-02', '08:59', '10:32'),
('TS020', 'T119', 'S001', 'S005', '2025-07-11', '08:55', '10:49'),
('TS021', 'T120', 'S005', 'S009', '2025-07-14', '08:07', '10:53'),
('TS022', 'T121', 'S005', 'S009', '2025-07-03', '08:37', '10:57'),
('TS023', 'T122', 'S001', 'S009', '2025-07-04', '08:04', '10:39'),
('TS024', 'T123', 'S001', 'S005', '2025-07-05', '08:42', '10:17'),
('TS025', 'T124', 'S001', 'S005', '2025-07-07', '08:24', '10:57'),
('TS026', 'T125', 'S005', 'S009', '2025-07-09', '08:37', '10:37'),
('TS027', 'T126', 'S005', 'S009', '2025-07-05', '08:35', '10:16'),
('TS028', 'T127', 'S001', 'S005', '2025-07-13', '08:30', '10:53'),
('TS029', 'T128', 'S001', 'S005', '2025-07-01', '08:54', '10:26'),
('TS030', 'T129', 'S001', 'S005', '2025-07-03', '08:52', '10:33');


* **建立 `Tickets` 表並插入資料**

CREATE TABLE Tickets (
    TicketID VARCHAR(10) PRIMARY KEY,
    OrderID VARCHAR(10),
    ScheduleID VARCHAR(10),
    SeatID VARCHAR(10),
    Price INT,
    FOREIGN KEY (OrderID) REFERENCES Orders(OrderID),
    FOREIGN KEY (ScheduleID) REFERENCES TrainSchedules(ScheduleID),
    FOREIGN KEY (SeatID) REFERENCES Seats(SeatID)
);

INSERT INTO Tickets (TicketID, OrderID, ScheduleID, SeatID, Price) VALUES
('TK001', 'O001', 'TS001', 'S0001', 1300),
('TK002', 'O002', 'TS002', 'S0002', 1000),
('TK003', 'O003', 'TS003', 'S0003', 1100),
('TK004', 'O004', 'TS004', 'S0004', 1000),
('TK005', 'O005', 'TS005', 'S0005', 1000),
('TK006', 'O006', 'TS006', 'S0006', 1200),
('TK007', 'O007', 'TS007', 'S0007', 1200),
('TK008', 'O008', 'TS008', 'S0008', 1100),
('TK009', 'O009', 'TS009', 'S0009', 1100),
('TK010', 'O010', 'TS010', 'S0010', 1100),
('TK011', 'O011', 'TS011', 'S0011', 1000),
('TK012', 'O012', 'TS012', 'S0012', 1200),
('TK013', 'O013', 'TS013', 'S0013', 1100),
('TK014', 'O014', 'TS014', 'S0014', 1300),
('TK015', 'O015', 'TS015', 'S0015', 1200),
('TK016', 'O016', 'TS016', 'S0016', 1000),
('TK017', 'O017', 'TS017', 'S0017', 1200),
('TK018', 'O018', 'TS018', 'S0018', 1200),
('TK019', 'O019', 'TS019', 'S0019', 1000),
('TK020', 'O020', 'TS020', 'S0020', 1000),
('TK021', 'O021', 'TS021', 'S0021', 1100),
('TK022', 'O022', 'TS022', 'S0022', 1000),
('TK023', 'O023', 'TS023', 'S0023', 1100),
('TK024', 'O024', 'TS024', 'S0024', 1200),
('TK025', 'O025', 'TS025', 'S0025', 1300),
('TK026', 'O026', 'TS026', 'S0026', 1300),
('TK027', 'O027', 'TS027', 'S0027', 1100),
('TK028', 'O028', 'TS028', 'S0028', 1100),
('TK029', 'O029', 'TS029', 'S0029', 1100),
('TK030', 'O030', 'TS030', 'S0030', 1200);


* **建立 `PaymentRecords` 表並插入資料**

CREATE TABLE PaymentRecords (
    PaymentID VARCHAR(10) PRIMARY KEY,
    OrderID VARCHAR(10),
    PaymentDate DATE,
    Amount INT,
    PaymentMethod VARCHAR(20),
    FOREIGN KEY (OrderID) REFERENCES Orders(OrderID)
);

INSERT INTO PaymentRecords (PaymentID, OrderID, PaymentDate, Amount, PaymentMethod) VALUES
('PM001', 'O001', '2025-06-14', 1000, '信用卡'),
('PM002', 'O002', '2025-06-30', 1000, '信用卡'),
('PM003', 'O003', '2025-06-08', 1100, '轉帳'),
('PM004', 'O004', '2025-06-25', 1100, '信用卡'),
('PM005', 'O005', '2025-06-01', 1300, '現金'),
('PM006', 'O006', '2025-06-07', 1000, '轉帳'),
('PM007', 'O007', '2025-07-01', 1200, 'LinePay'),
('PM008', 'O008', '2025-06-12', 1100, '信用卡'),
('PM009', 'O009', '2025-06-24', 1100, '現金'),
('PM010', 'O010', '2025-06-30', 1300, '信用卡'),
('PM011', 'O011', '2025-06-11', 1100, '信用卡'),
('PM012', 'O012', '2025-06-18', 1300, '街口支付'),
('PM013', 'O013', '2025-06-17', 1200, 'LinePay'),
('PM014', 'O014', '2025-06-09', 1200, 'LinePay'),
('PM015', 'O015', '2025-06-27', 1000, '現金'),
('PM016', 'O016', '2025-06-06', 1300, '街口支付'),
('PM017', 'O017', '2025-06-02', 1200, '街口支付'),
('PM018', 'O018', '2025-06-27', 1000, '轉帳'),
('PM019', 'O019', '2025-06-19', 1300, '現金'),
('PM020', 'O020', '2025-06-17', 1200, 'LinePay'),
('PM021', 'O021', '2025-06-29', 1200, '現金'),
('PM022', 'O022', '2025-06-24', 1100, '轉帳'),
('PM023', 'O023', '2025-06-26', 1200, '街口支付'),
('PM024', 'O024', '2025-07-01', 1100, '現金'),
('PM025', 'O025', '2025-06-17', 1200, '街口支付'),
('PM026', 'O026', '2025-06-17', 1200, '信用卡'),
('PM027', 'O027', '2025-06-08', 1200, '轉帳'),
('PM028', 'O028', '2025-06-03', 1200, '信用卡'),
('PM029', 'O029', '2025-06-29', 1100, '現金'),
('PM030', 'O030', '2025-06-20', 1200, '現金');


#### 2.2 索引與效能考量：為常用查詢欄位建立索引
* `Orders`: 查詢乘客訂單

CREATE INDEX idx_orders_passenger ON Orders(PassengerID);

* `Tickets`: 查詢訂單對應票券

CREATE INDEX idx_tickets_order ON Tickets(OrderID);
CREATE INDEX idx_tickets_schedule ON Tickets(ScheduleID);
CREATE INDEX idx_tickets_seat ON Tickets(SeatID);

* `TrainSchedules`: 查詢班表與站點

CREATE INDEX idx_schedules_train ON TrainSchedules(TrainID);
CREATE INDEX idx_schedules_fromstation ON TrainSchedules(FromStationID);
CREATE INDEX idx_schedules_tostation ON TrainSchedules(ToStationID);

* `PaymentRecords`: 查詢付款對應訂單

CREATE INDEX idx_payments_order ON PaymentRecords(OrderID);

* `Stations`: 若搭配統計可加速 LIKE/分組查詢

CREATE INDEX idx_stations_name ON Stations(StationName);

#### 2.3 交易 (Transaction) 機制：BEGIN、COMMIT、ROLLBACK 範例
* 正常交易流程
> 用到 BEGIN、COMMIT
> 說明：只要所有 SQL 正常執行，使用 COMMIT 將交易正式提交

-- 開始交易
BEGIN;

-- 新增票券資料
INSERT INTO Tickets (TicketID, OrderID, ScheduleID, SeatID, Price)
VALUES ('TK901', 'O001', 'TS001', 'S0001', 1200);

-- 新增付款資料
INSERT INTO PaymentRecords (PaymentID, OrderID, PaymentDate, Amount, PaymentMethod)
VALUES ('PM901', 'O001', CURDATE(), 1200, '信用卡');

-- 確認無誤，送出交易
COMMIT;

* 驗證結果
- TK901 與 PM901 都會寫入資料庫
- 資料一致，交易完整

SELECT * FROM Tickets WHERE TicketID = 'TK901';
SELECT * FROM PaymentRecords WHERE PaymentID = 'PM901';


* 中途取消交易
> 用到 BEGIN、ROLLBACK
> 說明：即使第一筆 SQL 成功，也因為 ROLLBACK，整筆交易會完全不寫入資料庫

-- 開始交易
BEGIN;

-- 嘗試新增票券
INSERT INTO Tickets (TicketID, OrderID, ScheduleID, SeatID, Price)
VALUES ('TK902', 'O002', 'TS002', 'S0002', 1100);

-- 忽然發現輸入錯誤（或按錯按鈕）
-- 取消整筆交易
ROLLBACK;

* 驗證結果
* 不會新增 TK902，交易被取消
* 系統狀態維持原樣

SELECT * FROM Tickets WHERE TicketID = 'TK902';


* 錯誤觸發自動失敗
> 用到 BEGIN（錯誤後會觸發系統隱性 ROLLBACK）
>
> 說明：
> 1. 因為 SQL 發生錯誤（如主鍵衝突），交易會自動失敗
> 2. MariaDB 會隱性執行 ROLLBACK → 不會寫入任何資料

-- 開始交易
BEGIN;

-- 嘗試插入已存在的主鍵
INSERT INTO PaymentRecords (PaymentID, OrderID, PaymentDate, Amount, PaymentMethod)
VALUES ('PM001', 'O003', CURDATE(), 1300, '街口支付'); -- PM001 已存在！

-- 如果上面那行失敗，以下這行不會執行
INSERT INTO Tickets (TicketID, OrderID, ScheduleID, SeatID, Price)
VALUES ('TK903', 'O003', 'TS003', 'S0003', 1300);

-- 系統偵測錯誤，自動觸發 ROLLBACK（MariaDB 會終止整筆交易）
COMMIT;

* 驗證結果
* 由於 PM001 主鍵已存在 → 觸發錯誤
* 全部操作 自動失敗，不會寫入任何資料

SELECT * FROM Tickets WHERE TicketID = 'TK903';   


* 自行 ROLLBACK：模擬錯誤後回滾
> 需先關閉Auto-Commit

START TRANSACTION;

UPDATE Tickets SET Price = 999 WHERE TicketID = 'TK001';  -- 正常

DELETE FROM Passengers WHERE PassengerID = 'P404';  -- 錯誤，P404 不存在

ROLLBACK;  -- 手動回滾

* 驗證 rollback 是否成功

SELECT * FROM Tickets WHERE TicketID = 'TK001';  -- 價格應維持原值


## 三、進階 SQL 功能應用
### 1. 查詢重點
**1. 特定時間某列車的訂票率 / 座位剩餘狀況**

-- 查詢列車 T100 在 2025-07-01 的訂位與剩餘狀況
SELECT 
    ts.TrainID,
    COUNT(t.TicketID) AS 已訂座位數,
    COUNT(se.SeatID) AS 總座位數,
    COUNT(se.SeatID) - COUNT(t.TicketID) AS 剩餘座位數,
    ROUND(COUNT(t.TicketID) / COUNT(se.SeatID) * 100, 2) AS 訂票率百分比
FROM TrainSchedules ts
JOIN Seats se ON se.TrainID = ts.TrainID
LEFT JOIN Tickets t ON t.SeatID = se.SeatID AND t.ScheduleID = ts.ScheduleID
WHERE ts.TrainID = 'T100' AND ts.TravelDate = '2025-07-01'
GROUP BY ts.TrainID;


**2. 乘客歷史搭乘紀錄（含起訖站與時刻）**

-- 查詢某位乘客（P001）的所有歷史搭乘紀錄
SELECT 
    p.Name,
    ts.TravelDate,
    s1.StationName AS 起站,
    s2.StationName AS 訖站,
    ts.DepartureTime,
    ts.ArrivalTime,
    t.TicketID
FROM Passengers p
JOIN Orders o ON p.PassengerID = o.PassengerID
JOIN Tickets t ON o.OrderID = t.OrderID
JOIN TrainSchedules ts ON t.ScheduleID = ts.ScheduleID
JOIN Stations s1 ON ts.FromStationID = s1.StationID
JOIN Stations s2 ON ts.ToStationID = s2.StationID
WHERE p.PassengerID = 'P001'
ORDER BY ts.TravelDate DESC;


**3. 某日各站上下車人數統計表**

-- 查詢 2025-07-01 當天各站上下車人數
SELECT 
    s.StationName,
    SUM(CASE WHEN ts.FromStationID = s.StationID THEN 1 ELSE 0 END) AS 上車人數,
    SUM(CASE WHEN ts.ToStationID = s.StationID THEN 1 ELSE 0 END) AS 下車人數
FROM Tickets t
JOIN TrainSchedules ts ON t.ScheduleID = ts.ScheduleID
JOIN Stations s ON s.StationID IN (ts.FromStationID, ts.ToStationID)
WHERE ts.TravelDate = '2025-07-01'
GROUP BY s.StationName;


**4. 熱門路段排行（例如：台北 ⇌ 台中）**

-- 統計各路段搭乘次數（不分方向）
SELECT 
    CONCAT(LEAST(s1.StationName, s2.StationName), ' ⇌ ', GREATEST(s1.StationName, s2.StationName)) AS 路段,
    COUNT(*) AS 搭乘次數
FROM Tickets t
JOIN TrainSchedules ts ON t.ScheduleID = ts.ScheduleID
JOIN Stations s1 ON ts.FromStationID = s1.StationID
JOIN Stations s2 ON ts.ToStationID = s2.StationID
GROUP BY 路段
ORDER BY 搭乘次數 DESC
LIMIT 5;


**5. 座位安排報表（可依時間 / 列車查詢空位）**

-- 查詢列車 T100 在 2025-07-01 的所有空位座號
SELECT se.SeatNumber
FROM Seats se
JOIN TrainSchedules ts ON se.TrainID = ts.TrainID
LEFT JOIN Tickets t ON se.SeatID = t.SeatID AND t.ScheduleID = ts.ScheduleID
WHERE ts.TrainID = 'T100' AND ts.TravelDate = '2025-07-01'
  AND t.TicketID IS NULL;


**6. 月營收統計 / 支付方式分布**

-- 查詢 2025 年各月份每種支付方式的總收入
SELECT 
    DATE_FORMAT(PaymentDate, '%Y-%m') AS 月份,
    PaymentMethod,
    SUM(Amount) AS 總收入
FROM PaymentRecords
WHERE YEAR(PaymentDate) = 2025
GROUP BY 月份, PaymentMethod
ORDER BY 月份, PaymentMethod;


**7. 搭乘高峰時段分析（例如週五晚上最多人搭）**

-- 查詢高峰時段：星期幾 + 小時區間 的搭乘人次統計
SELECT 
    DAYNAME(ts.TravelDate) AS 星期,
    HOUR(ts.DepartureTime) AS 出發小時,
    COUNT(*) AS 搭乘人次
FROM Tickets t
JOIN TrainSchedules ts ON t.ScheduleID = ts.ScheduleID
GROUP BY 星期, 出發小時
ORDER BY 搭乘人次 DESC
LIMIT 5;


### 2. 進階 SQL 功能應用
#### 2.1 複雜查詢與子查詢：聚合函數、GROUP BY、HAVING、視圖 (View)
* **各出發站班次總數與平均票價**

SELECT 
    s.StationName AS 出發站,
    COUNT(ts.ScheduleID) AS 班次數量,
    AVG(t.Price) AS 平均票價,
    SUM(t.Price) AS 總收入
FROM TrainSchedules ts
JOIN Stations s ON ts.FromStationID = s.StationID
JOIN Tickets t ON ts.ScheduleID = t.ScheduleID
GROUP BY s.StationName;


* **查詢付款總額 > 1200 元的乘客**

SELECT 
    p.Name,
    SUM(pr.Amount) AS 總付款金額
FROM Passengers p
JOIN Orders o ON p.PassengerID = o.PassengerID
JOIN PaymentRecords pr ON o.OrderID = pr.OrderID
GROUP BY p.Name
HAVING SUM(pr.Amount) > 1200;


* **子查詢：訂過新竹為目的地的乘客**

SELECT DISTINCT p.Name
FROM Passengers p
WHERE p.PassengerID IN (
    SELECT o.PassengerID
    FROM Orders o
    JOIN Tickets t ON o.OrderID = t.OrderID
    JOIN TrainSchedules ts ON t.ScheduleID = ts.ScheduleID
    WHERE ts.ToStationID = 'S005'  -- 新竹
);


* **建立視圖：乘客訂票總覽**

CREATE VIEW PassengerOrderSummary AS
SELECT 
    p.PassengerID,
    p.Name,
    COUNT(o.OrderID) AS 訂單數量,
    SUM(pr.Amount) AS 總付款,
    MIN(pr.PaymentDate) AS 最早付款日,
    MAX(pr.PaymentDate) AS 最晚付款日
FROM Passengers p
LEFT JOIN Orders o ON p.PassengerID = o.PassengerID
LEFT JOIN PaymentRecords pr ON o.OrderID = pr.OrderID
GROUP BY p.PassengerID, p.Name;


* **查詢視圖**

SELECT * FROM PassengerOrderSummary;


#### 2.2 Stored Procedure / Function：實作商業邏輯或報表計算
**1. 建立 Stored Procedure：查詢乘客訂票紀錄與總付款**

功能說明：
    • 傳入乘客姓名（或 ID）
    • 顯示該乘客的所有訂單明細與付款總額

* **建立 Stored Procedure：查詢乘客訂票紀錄與總付款**

CREATE PROCEDURE GetPassengerOrders(IN p_name VARCHAR(50))
BEGIN
    SELECT 
        p.Name,
        o.OrderID,
        pr.PaymentDate,
        pr.Amount,
        t.TicketID,
        t.Price
    FROM Passengers p
    JOIN Orders o ON p.PassengerID = o.PassengerID
    JOIN PaymentRecords pr ON o.OrderID = pr.OrderID
    JOIN Tickets t ON o.OrderID = t.OrderID
    WHERE p.Name = p_name;
END;

* **確定是否正確建立 Procedure**

SHOW PROCEDURE STATUS WHERE Db = 'HSR';


* **執行方式：**

CALL GetPassengerOrders('王小明'); --無此資料，僅測試


CALL GetPassengerOrders('吳芝儀');


**2. 建立 Function：計算某班次平均票價**

功能說明：
    • 傳入 ScheduleID
    • 回傳該班次的票券平均價格

* **建立 Function：計算某班次平均票價**

CREATE FUNCTION GetAvgTicketPrice(sch_id VARCHAR(10))
RETURNS DECIMAL(10,2)
DETERMINISTIC
BEGIN
    DECLARE avg_price DECIMAL(10,2);

    SELECT AVG(Price)
    INTO avg_price
    FROM Tickets
    WHERE ScheduleID = sch_id;

    RETURN avg_price;
END;

* **確定是否正確建立 Function**

SHOW FUNCTION STATUS WHERE Db = 'HSR';


* **執行方式**

SELECT GetAvgTicketPrice('TS001') AS 平均票價; 


#### 2.3 Trigger

Trigger 是一種 自動執行的 SQL 程式塊，當特定表格發生：
    •    INSERT
    •    UPDATE
    •    DELETE

就會自動觸發相對應的邏輯。

* **範例一：插入 PaymentRecords 後，自動更新乘客付款總額欄位**
1. 在 `Passengers` 表新增一個欄位 `TotalPaid`

ALTER TABLE Passengers ADD COLUMN TotalPaid INT DEFAULT 0;


2. 建立 `AFTER INSERT Trigger`

說明：
    • 每當有一筆付款紀錄插入
    • 自動查對應乘客 → 幫他更新累積付款金額

CREATE TRIGGER trg_update_total_paid
AFTER INSERT ON PaymentRecords
FOR EACH ROW
BEGIN
    UPDATE Passengers
    SET TotalPaid = TotalPaid + NEW.Amount
    WHERE PassengerID = (
        SELECT PassengerID
        FROM Orders
        WHERE OrderID = NEW.OrderID
        LIMIT 1
    );
END;

* **測試插入付款紀錄**

INSERT INTO PaymentRecords (PaymentID, OrderID, PaymentDate, Amount, PaymentMethod)
VALUES ('PM998', 'O001', CURDATE(), 1000, 'Line Pay');


這筆資料會觸發上面那個 Trigger：
- 自動去找到 O001 的乘客（是 P001）
- 然後幫他把 TotalPaid +1000

* **查詢乘客付款總額**

SELECT Name, TotalPaid FROM Passengers WHERE PassengerID = 'P001';


* **範例二：刪除訂單時，自動刪除相關票券**

說明：
    • 當你刪除某筆 Orders 記錄
    • 相關票券（Tickets）會一起被刪除，避免遺留不一致資料

CREATE TRIGGER trg_delete_tickets_on_order_delete
AFTER DELETE ON Orders
FOR EACH ROW
BEGIN
    DELETE FROM Tickets
    WHERE OrderID = OLD.OrderID;
END;

* **測試刪除訂單**

START TRANSACTION;
DELETE FROM PaymentRecords WHERE OrderID = 'O002';
DELETE FROM Tickets WHERE OrderID = 'O002';
DELETE FROM Orders WHERE OrderID = 'O002';
COMMIT;

* **驗證是否也刪除了票券**

SELECT * FROM Tickets WHERE OrderID = 'O002';


* **範例三：修改票價時自動更新月營收報表**
* 新增一個資料表(為了驗證觸發器新增的表格)

-- 建立月營收表格
CREATE TABLE MonthlyRevenue (
    YearMonth VARCHAR(7) PRIMARY KEY,     -- 格式: '2025-07'
    TotalRevenue INT DEFAULT 0
);

-- 初始化幾個月份的營收資料
INSERT INTO MonthlyRevenue (YearMonth, TotalRevenue) VALUES
('2025-06', 0),
('2025-07', 0),
('2025-08', 0);

* 修改票價時自動更新該月總營收

CREATE TRIGGER trg_update_revenue_on_ticket_update
AFTER UPDATE ON Tickets
FOR EACH ROW
BEGIN
    DECLARE rev_month VARCHAR(7);

    SELECT DATE_FORMAT(ts.TravelDate, '%Y-%m') INTO rev_month
    FROM TrainSchedules ts
    WHERE ts.ScheduleID = NEW.ScheduleID;

    UPDATE MonthlyRevenue
    SET TotalRevenue = TotalRevenue + (NEW.Price - OLD.Price)
    WHERE YearMonth = rev_month;
END;

* 測試修改票價

UPDATE Tickets SET Price = 1200 WHERE TicketID = 'TK005';


* 查詢月營收

SELECT * FROM MonthlyRevenue;


#### 2.4 安全性與權限管理目標
* **Step 1：建立帳號**

-- 建立一般人員帳號 user_clerk，僅能查詢與新增
CREATE USER 'user_clerk'@'localhost' IDENTIFIED BY 'pass123';

-- 建立管理人員帳號 user_mgr，擁有完整權限
CREATE USER 'user_mgr'@'localhost' IDENTIFIED BY 'admin999';

* **Step 2：直接對帳號賦權（不使用角色）**

-- 使用者 user_clerk 可以查詢與新增 Passengers 與 Orders
GRANT SELECT, INSERT ON HSR.Passengers TO 'user_clerk'@'localhost';
GRANT SELECT, INSERT ON HSR.Orders TO 'user_clerk'@'localhost';

-- 使用者 user_mgr 擁有所有權限
GRANT ALL PRIVILEGES ON HSR.* TO 'user_mgr'@'localhost';

* **Step 3：查詢使用者與權限**

-- 顯示所有使用者
SELECT user, host FROM mysql.user;

-- 查詢特定帳號的權限
SHOW GRANTS FOR 'user_clerk'@'localhost';

* 顯示所有使用者
* 查詢特定帳號的權限

* **Step 4：測試權限效果**

-- 測試查詢乘客
SELECT * FROM HSR.Passengers;

-- 測試新增乘客
INSERT INTO HSR.Passengers (PassengerID, Name, Email, Phone)
VALUES ('P999', '測試用戶', '[e-mail]', '0999123456');

* 測試查詢乘客
* 測試新增乘客

* **測試不能執行的操作（如刪除資料）**

-- user_clerk 不該能執行 DELETE
DELETE FROM HSR.Passengers WHERE PassengerID = 'P001';


* **驗證結果說明**

| 測試項目 | 結果 | 說明 |
|----------------------|------------|--------------------------------------|
| 查詢 Passengers | 成功 | 已授權 `SELECT` 權限 |
| 新增 Passengers | 成功 | 已授權 `INSERT` 權限 |
| 刪除 Passengers | 失敗 | 未授權 `DELETE`，且有外鍵限制保護 |
| 管理所有資料 | 僅限 `user_mgr` | 擁有 `ALL PRIVILEGES` 權限 |

## 四、功能測試
### 1. 功能查詢清單：設計並附上 SQL 與執行結果

已經寫在「三、進階 SQL 功能應用」裡的「1. 查詢重點」

### 2. 資料一致性與異常情況測試：外鍵約束、Transaction Rollback

已經寫在「二、專題實作內容」裡的「2.3 交易 (Transaction) 機制」

### 3. EXPLAIN 效能分析

-- 建立索引（如未建立）
CREATE INDEX idx_name ON Passengers(Name);

-- 使用索引查詢
EXPLAIN SELECT * FROM Passengers WHERE Name = '張郁婷';


-- 移除索引（如已建立）
DROP INDEX idx_name ON Passengers;

-- 執行無索引查詢分析
EXPLAIN SELECT * FROM Passengers WHERE Name = '張郁婷';


**分析結果**

| 查詢條件 | 是否使用索引 | 掃描方式 | 掃描筆數 | 備註 |
|------------|---------------|----------|----------|----------------|
| Name = '張郁婷' | idx_name | ref | 1 | 效率高，使用索引 |
| Name = '張郁婷' | 無索引 | ALL | 32 | 效率低，全表掃描 |

## 五、組員分工說明

| 學號 | 姓名 | 工作內容 |
| -------- | -------- | -------- |
| 411630212 | 黃鈞琳 | 正規化、資料庫編寫、功能測試、撰寫文件|
| 411630121 | 曾若嬅 | 資料庫編寫、功能測試、撰寫文件|