111 高普考高考二級 資訊處理 高等資料庫設計 考古題詳解

核心觀念

本題評量關聯式資料庫管理系統（RDBMS）的兩大核心技術能力：

1. 聚合運算與條件過濾（Aggregation & Grouping）：使用 COUNT 聚合函數統計分組次數，並熟練區分 WHERE（列過濾）與 HAVING（分組聚合結果過濾）子句的執行順序。同時搭配**純量子查詢（Scalar Subquery）**在比較運算式右側提供基準純量值。
2. 使用者自訂函數（User-Defined Function, UDF）：將重複的資料庫查詢邏輯封裝為純量函數。依據 ISO/IEC 9075 SQL 標準（SQL:1999/PSM 規格）或特定商用資料庫擴充語法（如 Microsoft SQL Server T-SQL、PostgreSQL PL/pgSQL、Oracle PL/SQL）定義輸入參數與回傳值，並在資料操作語言（DML）的查詢句中呼叫。

---

解題方法

（一）使用標準 COUNT 函數之 SQL 查詢

1. 分組與統計：以借書者編號 ID 進行分組（GROUP BY ID），並以 COUNT(*) 計算每位借書者借閱的書籍總筆數。
2. 計算基準值：利用子查詢從 BORROWER 表格中計算編號為 'A001' 的借書總量：

SELECT COUNT(*) FROM BORROWER WHERE ID = 'A001'

該子查詢回傳單一整數（純量值）。 3. 分組過濾：因比對條件建立於「分組後的聚合統計值」，必須使用 HAVING 子句，並透過比較運算子 > 將每位使用者的借閱數與子查詢回傳的基準數值進行比較。

（二）設計 BookCount 函數與改寫查詢句

1. 函數設計邏輯：

• 函數名稱為 BookCount，接收參數 id（型態為 VARCHAR）。
• 函數本體執行 SELECT COUNT(*) INTO ... WHERE BORROWER.ID = id，統計該借書者的借閱總數。
• 回傳型態定義為整數（INT 或 INTEGER）。
• 題目明確要求「若非使用 SQL 標準語法，請註明所使用的語法為哪個軟體所支援」。因此提供符合 ISO/IEC SQL:1999（SQL/PSM）標準語法，並補充國內國家考試常見之 Microsoft SQL Server（T-SQL）語法以供參照。

2. 改寫主查詢句：

• 封裝為函數後，計算任意借書者的書籍數只需呼叫 BookCount(ID)，計算基準值則為 BookCount('A001')。
• 可直接在 WHERE 子句使用 BookCount(ID) > BookCount('A001')，配合 SELECT DISTINCT ID 列出符合條件之借書者編號；亦可搭配 GROUP BY ID 呈現。

---

解題技巧與常見陷阱

1. WHERE 與 HAVING 的混用陷阱： 聚合函數（如 COUNT(*)）絕不可寫在 WHERE 子句中，因為 WHERE 的執行階段先於 GROUP BY，此時群組尚未建立。必須使用 HAVING COUNT(*) > (...) 才能正確過濾分組後的結果。
2. COUNT(*) 與 COUNT(DISTINCT ISBN) 的選擇：

核心觀念

本題評量關聯式資料庫（RDBMS）中**實體儲存結構與查詢最佳化（Query Optimization）**的核心機制，主要涵蓋以下兩大主軸：

1. $B^+$-tree 索引內部結構與特性：

• 節點功能劃分：$B^+$-tree 將節點明確劃分為「內部節點（Internal / Non-leaf Nodes）」與「葉節點（Leaf Nodes）」，內部節點僅負責索引路由，葉節點負責儲存鍵值與實際資料參照。
• 結構平衡與巡訪效率：具備平衡樹（Balanced Tree）的高度平衡特性、大扇出度（Fan-out）以降低樹高與磁碟 I/O，以及葉節點串列鏈結（Linked List）以支援範圍查詢。

2. 查詢述詞（Predicate）與索引鍵匹配（Index Matching）：

• 索引的搜尋加速機制取決於 WHERE 條件中的搜尋鍵（Search Key）是否能構成可搜尋述詞（SARGable Predicate）。
• 索引依據建立的屬性欄位進行排序，只有當篩選條件對應到索引屬性時，查詢最佳化器才能發動「索引搜尋（Index Seek）」；反之，若篩選條件不含索引鍵，則無法利用該索引過濾資料，將導致「全表掃描（Full Table Scan）」。

---

解題方法

本題解題步驟分為兩部分循序切入：

1. 結構剖析：

• 針對指令 CREATE INDEX fastindex ON USER (Name);，此為建置於 Name 屬性上的次要索引（Secondary / Non-clustered Index）。
• 依據資料庫理論推導內部節點（鍵值與子樹指標）與葉節點（鍵值、紀錄指標、前後節點指標）的存放內容。
• 歸納出 $B^+$-tree 在平衡度、I/O 效能、範圍查詢支援上的核心特性。

2. 查詢比對：

• 分析查詢句一與查詢句二的 WHERE 子句所引用的屬性。
• 比對過濾屬性與索引鍵（Name）之一致性：查詢句一以 Name 篩選，符合索引鍵；查詢句二以 ID 篩選，不符合索引鍵。
• 評估查詢計畫執行路徑（Index Seek 搭配回表存取 vs. Full Table Scan），確立效能提升之歸屬。

---

申論題詳解

一、 $B^+$-tree 節點存放資料與結構特性

依指令建立之 fastindex 是以屬性 Name 作為搜尋鍵（Search Key）的次要索引，其節點內容與結構特性如下：

(一) 內部節點（Internal / Non-leaf Nodes）存放資料

內部節點主要作為導引路由（Router），引導搜尋路徑走向目標葉節點，其內容不存放指向資料表紀錄的指標：

1. 搜尋鍵值（Search Key Values）：存放分割鍵值（此處為部分排序後的 Name 字串），作為各子樹區間的分界值。
2. 樹指標（Child / Node Pointers）：存放指向下一層子節點（可以是內部節點或葉節點）的記憶體或磁區指標。
3. 節點結構表示：若內部節點有 $n$ 個樹指標，則其內部包含 $n-1$ 個鍵值，格式為： $\langle P_1, K_1, P_2, K_2, \dots, K_{n-1}, P_n \rangle$ 其中 $P_i$ 為樹指標，$K_i$ 為 Name 鍵值；任一子樹 $P_i$ 內的所有鍵值 $K$ 滿足 $K_{i-1} \le K < K_i$。

(二) 葉節點（Leaf Nodes）存放資料

葉節點為樹結構的最底層，集中存放所有實際索引資料與導向實體紀錄之參照：

1. 搜尋鍵值（Search Key Values）：存放資料表中所有紀錄的 Name 屬性值，且所有鍵值依字典序（Lexicographical Order）嚴格排序。
2. 紀錄指標（Record Pointers / RIDs）：存放指向實體資料檔（Data File）中該筆紀錄所在位置的指標（如 Row Identifier, RID），用以定位該筆資料的 ID 與 Address。
3. 兄弟節點指標（Sibling Pointers）：存放指向相鄰左、右葉節點的雙向指標（Next / Prev Leaf Pointer），將所有葉節點串聯為雙向鏈結串列（Doubly Linked List）。

(三) $B^+$-tree 結構之主要特性

1. 所有資料鍵與紀錄指標皆置於葉層：內部節點僅儲存索引鍵，不儲存資料指標，因此內部節點可容納極高的分支度（High Fan-out），大幅降低樹高（Tree Height，通常僅需 $3 \sim 4$ 層），顯著減少磁碟 I/O 存取次數。

核心觀念

本題測驗分散式系統與資料庫管理系統（DBMS）在「交易處理（Transaction Processing）」的核心設計哲學，涵蓋以下核心重點：

1. 傳統關聯式交易模型（ACID）：以單機或強一致性環境為核心，強調交易的正確性、完整性與嚴格序列化（Serializability）。
2. 分散式交易模型（BASE）：針對分散式大數據架構，依循 CAP 定理（Brewer's CAP Theorem）於網路分割容忍度（Partition Tolerance, P）成立之前提下，對一致性（Consistency, C）所做的權衡折衷。
3. NoSQL 資料庫架構特徵：橫向擴充（Scale-out）、分散式副本同步機制（Replication）以及對高可用性（Availability, A）與低延遲（Low Latency）的優先追求。

---

解題方法

本題屬於標準的比較與申論題型，答題應採取「定義拆解 $\to$ 特性對比 $\to$ 實務論證」的架構切入：

1. 第一部分（名詞定義與特性）：

• 清楚列出 ACID 與 BASE 每個字母對應的英文全名。
• 分別說明各字母背後的技術定義、核心要求及其實作手段（如 WAL、鎖定機制、降級服務、非同步複製等）。

2. 第二部分（NoSQL 的立場與觀點闡述）：

• 明確表態：NoSQL「大多支援 BASE」。
• 論證基礎：引入分散式系統的 CAP 定理 與 PACELC 定理 作為理論支撐。
• 架構分析：解釋大數據環境為何必須以「最終一致性」取代「即時強一致性」，藉此換取水平擴展能力（Scalability）與高寫入吞吐量（Throughput）。
• 延伸補充（展現高分鑑別度）：提及現代 NoSQL 發展趨勢（如文件資料庫局部支援多文件 ACID），但歸結其底層巨量分散式架構的核心本質仍為 BASE。

---

選項分析

本題為高考二級申論題，非選擇題，故無個別選項分析。閱卷評分重點在於：

1. 專有名詞精準度：英文全名與中文譯名是否完全正確。
2. 特性闡述深度：是否清楚區分 ACID 的「強一致性」與 BASE 的「弱／最終一致性」。
3. 理論連結度：說明 NoSQL 偏向 BASE 時，是否成功結合 CAP 定理與分散式複製延遲進行申論。

---

解題技巧

1. 結構對稱法：回答 ACID 與 BASE 時，務必按照字母順序逐項條列，並採「英文全名 $\to$ 核心要求 $\to$ 實作意義」的標準格式撰寫，版面清爽易讀。
2. 避免語意混淆：

• ACID 的「C（Consistency）」代表資料完整性限制（Integrity Constraints）；而 CAP 定理的「C」則是指分散式多副本的單一視圖／原子一致性（Linearizability），作答時精準區隔可展現扎實觀念。

3. 論證不走極端：論述 NoSQL 時，立場要堅定指出「絕大多數以 BASE 為主」，同時補上一筆「部分新型 NoSQL 提供選擇性或局部 ACID（可調式一致性 Tunable Consistency）」，能讓答案展現全面性與深度。

---

【答案】

一、ACID 與 BASE 之英文全名、特性與要求

（一）ACID 模型（傳統關聯式資料庫之交易準則）

ACID 模型旨在確保交易在並行執行與系統故障時，資料庫仍能維持絕對的正確性與完整性：

1. A — Atomicity（不可分割性 / 原子性）

• 特性與要求：交易是資料庫操作的最小不可分割單元。交易中的所有 SQL 操作要麼全部成功執行（Commit），要麼全部不執行並回復至交易前狀態（Rollback），不允許停留在「部分完成」的中間狀態。
• 技術實作：通常透過復原記錄檔（Undo Log / Write-Ahead Logging, WAL）實現。

2. C — Consistency（一致性）

• 特性與要求：交易執行前後，資料庫必須始終維持在合法的狀態，必須滿足所有預先定義的結構限制與規則（包含實體完整性、參考完整性、CHECK 限制以及業務邏輯規則）。
• 技術實作：由資料庫系統的完整性檢查機制與交易邏輯共同防護。

3. I — Isolation（隔離性）

• 特性與要求：多個交易同時（Concurrent）並行執行時，任一交易的中間未完成狀態對其他交易是不可見的；其執行結果必須與各交易依序單獨執行（Serial Execution）的結果完全一致。

核心觀念

本題評量資料倉儲（Data Warehousing, DW）與線上分析處理（OLAP）的兩大核心底層架構：

1. 多維度模型之星狀綱要（Star Schema）設計：

• 事實表（Fact Table）：位於星狀架構核心，記錄數值型可加性量值（Additive Measures）及關聯各維度的外來鍵（Foreign Keys）。
• 維度表（Dimension Table）：圍繞事實表，儲存描述性文字屬性（Contextual Attributes），不進行完全正規化（採去正規化 Denormalized 設計），以換取查詢時免除多表反覆關聯（Join）的效能。
• 代理鍵（Surrogate Key）機制：採用無業務意義的整數流水號作為主鍵，隔離來源系統（OLTP）自然鍵的變更，並強化查詢與索引效能。

2. 以欄為主之儲存架構（Column-Oriented Storage）：

• 列式儲存（Row-Oriented, 如傳統 RDBMS）：同一筆紀錄（Record/Tuple）之所有欄位連續存放在相同或相鄰的硬碟區塊（Disk Block/Page）中。
• 欄式儲存（Column-Oriented, 如 ClickHouse, Snowflake, Redshift）：同一屬性欄位之所有數值連續存放在磁碟中。
• 核心優勢原理：
• I/O 剪裁（Projection/Column Pruning）：分析型查詢通常僅讀取少數特定欄位進行聚合運算（如 SUM, AVG），欄式儲存只載入必要區塊，大幅減少磁碟 I/O。
• 極高壓縮比（High Compression Ratio）：同欄資料具有相同資料型態與高局部重複性（Low Cardinality），適合執行行程編碼（Run-Length Encoding, RLE）、字典編碼（Dictionary Encoding）或點位圖（Bitmap）壓縮。
• 向量化運算與快取友善（Vectorized Execution）：記憶體連續載入同欄資料，利於 CPU 循序預取（Prefetching）與單指令多資料流（SIMD）並行指令執行。

---

解題方法

1. 第一部分：星狀綱要設計

• 分析主題：定義「書籍銷售紀錄」為事實核心。
• 維度識別：
• 書店保留 20 年紀錄 $\rightarrow$ 必設時間維度（Dim_Date），支援歷年趨勢分析。
• 顧客年齡、性別 $\rightarrow$ 顧客維度（Dim_Customer），支援客群輪廓分析。
• 書籍類別（科幻、文學等） $\rightarrow$ 書籍維度（Dim_Book），支援暢銷與分類營收分析。
• 量值設計：選用可聚合之數值，如銷售數量、銷售單價、折扣金額、實付淨額。
• 架構特徵：事實表以 $N:1$ 直接連結各維度表，維度表彼此不互相串接，維持標準星狀結構。

2. 第二部分：欄式儲存範例與優點說明

• 提取事實表中的 3 筆範例資料，明確寫出在硬碟上的連續二進位排列邏輯。
• 對比「列式儲存」按行排列與「欄式儲存」按欄切片儲存的硬碟布局。
• 深入從「I/O 消耗」、「壓縮編碼」、「CPU 快取/向量化處理」三個層面論述其相較於列式儲存的主要優勢。

---

選項分析

本題為高考二級之技術申論題，非選擇題。在設計取捨上的比較論述如下：

• 星狀綱要（Star Schema）vs. 雪花綱要（Snowflake Schema）：
• 本題指定「星狀綱要」，其特點在於維度表保持去正規化（例如書籍分類直接存於 Dim_Book，不再拉出獨立的 Dim_Category）。此法雖增加維度表儲存冗餘，但換取了最精簡的 Join 深度，符合大數據歷史查詢的最佳效能準則。
• 列式儲存（Row-Oriented）vs. 欄式儲存（Column-Oriented）：
• 列式儲存適合 OLTP 系統，因其單筆交易之新增、修改、刪除涉及該列的所有欄位；然而資料倉儲為 OLAP 系統，多為批次寫入（Append-only）且查詢時常針對幾億筆歷史紀錄的特定 1 至 2 個數值做聚合計算，採用欄式儲存為絕對優勢首選。

---

解題技巧

1. 時間維度千萬不能漏：題目提到「保留了過去 20 年來」的交易紀錄，在資料倉儲設計中，絕對不能只把交易日期當作事實表的一個簡單時間戳記（Timestamp），務必拉出一個專門的 Dim_Date 維度表，才能進行以年、季、月、工作日/假日為單位的 OLAP 切片（Slice/Dice）與下鑽（Drill-down）分析。
2. 代理鍵（Surrogate Key）必備：在維度表設計時，維度主鍵應使用整數型態代理鍵（如 Customer_Key），而非來源業務系統的自然鍵（如身分證字號或帳號），這是閱卷委員評判考生是否具備專業資料倉儲實務觀念的關鍵給分點。
3. 儲存對比表列清晰化：在回答硬碟物理儲存方式時，使用方塊示意或橫向陣列排列表現磁碟連續磁區（Disk Blocks）的資料配置，能使抽象觀念具象化，得分效果最佳。

---

【答案】

一、網路書店資料倉儲之星狀綱要（Star Schema）設計

根據題目需求，建立以「書籍銷售」為分析核心之星狀綱要。該系統包含 1 個中心事實表及 3 個維度表，結構如下圖與規格說明：