112 公務人員升官等薦任 資訊處理 資料庫應用 考古題詳解

核心觀念

本題考查資料庫的三層式架構（three-schema architecture），也稱為 ANSI/SPARC 架構。它依資料抽象化程度，將資料庫描述分成實體層、邏輯層及視界層。由下而上，關注焦點從資料如何儲存，逐步提升到資料代表什麼，以及不同使用者如何看到資料。

解題方法

作答時可依序掌握三個問題：

1. 資料實際如何存放？——實體層。
2. 整個資料庫包含哪些資料及其關係？——邏輯層。
3. 特定使用者或應用程式能看到哪些資料？——視界層。

各層功能

一、實體層（physical level）

描述資料在儲存媒體中的實際組織方式，例如檔案配置、資料頁面、索引結構、資料的存取路徑及壓縮方式。此層主要供資料庫管理系統及系統管理者處理，以有效利用儲存空間並提升存取效率。

二、邏輯層（logical level）

描述整個資料庫的整體結構與內容，例如有哪些實體、屬性、資料表、欄位、資料型別，以及資料表之間的關係與完整性限制。此層關注資料的意義與相互關聯，不需指定資料在磁碟上的實際存放方式。

三、視界層（view level）

描述個別使用者或應用程式所看到的資料庫局部內容。資料庫可為不同使用者建立不同的視界，例如一般職員只能查看員工姓名與部門，薪資資料則僅開放給具權限的人員。視界有助於簡化使用者操作，也能配合存取控制與資料保護。

核心觀念

本題考查實體關係模式（Entity-Relationship model, ER model）的基本建模能力：從需求辨認實體集合、屬性、主鍵、關係集合、關係屬性，並標示實體間的對應數量限制（mapping cardinality）。

題目主要有三類實體：

• 物品：以物品編號識別，記錄名稱、製造廠商與價錢。
• 會員：以會員編號識別，記錄姓名、地址與電話。
• 訂單：以訂單編號識別，記錄成立日期與應付總金額。

會員與訂單之間是「下訂」關係：一筆訂單只能屬於一位會員；一位會員可下多筆訂單。訂單與物品之間是「包含」關係：一筆訂單可包含多項物品，同一物品也可出現在多筆訂單中，因此為多對多關係。每項物品在一筆訂單中的購買個數，應記錄在「包含」關係上，因為它取決於特定的訂單與物品組合。

解題方法

先將需求中的名詞辨認為實體或屬性，再依各項資料的語意判定主鍵與關係：

1. 「物品、會員、訂單」各自具有獨立編號與多項描述資料，適合建成實體集合。
2. 物品編號、會員編號、訂單編號分別作為各實體集合的主鍵。
3. 「由哪個會員下訂」形成會員與訂單間的「下訂」關係。因一筆訂單只能隸屬一位會員，所以從訂單看向會員是多對一；從會員看向訂單則是一對多。
4. 「包含哪些物品」形成訂單與物品間的「包含」關係。訂單可有多項物品，物品也能出現在多筆訂單，故為多對多。
5. 「每個物品被購買的個數」是訂單與物品配對後才有意義的資料，故屬於「包含」關係的屬性，而不是物品或訂單的單獨屬性。

ER 圖

下圖以文字標示主鍵（PK）、關係及數量限制；1 表示一，N 表示多。

核心觀念

本題考查關聯式資料庫的多表連接、條件篩選、分組彙總，以及找出彙總值最大所使用的子查詢。

• Member 以 accountNO 識別會員。
• ShoppingRecord 以 orderID 識別購買紀錄，並以 accountNO 對應會員。
• ShoppingList 記錄每筆訂單包含的貨品與數量，以 (orderID, itemNO) 為複合主鍵。
• Item 以 itemNO 識別貨品，並記載單價 price。

因此，會員的購買明細須沿著 Member → ShoppingRecord → ShoppingList 連接；要計算全體會員購買各貨品的總金額，則須再連接 Item。

解題方法

（一）列出「張三」的所有購買紀錄

先用 Member 找出姓名為「張三」的會員，再以 accountNO 連接 ShoppingRecord。題目要求輸出紀錄編號及應付金額，因此選取 orderID 與 amount。

SELECT SR.orderID, SR.amount
FROM Member AS M
JOIN ShoppingRecord AS SR
 ON M.accountNO = SR.accountNO
WHERE M.name = '張三';

JOIN 會以會員帳號配對其購買紀錄；WHERE 則限定會員姓名。若有多位同名「張三」，查詢會列出所有同名會員的購買紀錄，符合題目以姓名指定會員的條件。

（二）找出張三購買總數量至少 5 個的貨品

每筆訂單中的貨品數量記在 ShoppingList.quantity。先依會員、訂單及清單連接資料，再依貨品編號分組，對每種貨品的數量加總。題目要的是「購買總數量」，所以判斷條件必須套用於加總結果，使用 HAVING SUM(...) >= 5。

SELECT SL.itemNO, SUM(SL.quantity) AS total_quantity
FROM Member AS M
JOIN ShoppingRecord AS SR
 ON M.accountNO = SR.accountNO
JOIN ShoppingList AS SL
 ON SR.orderID = SL.orderID
WHERE M.name = '張三'
GROUP BY SL.itemNO
HAVING SUM(SL.quantity) >= 5;

WHERE 先篩選張三的訂單，GROUP BY 再將該會員的明細依貨品編號分組，SUM 計算各貨品的累計購買數量。門檻「5 個以上（含）」包含 5，因此使用 >= 5。

（三）找出全體會員累計花最多錢購買的貨品

核心觀念

本題考查關聯式資料庫的第一正規化（First Normal Form, 1NF），以及 XML、JSON 如何以巢狀結構表示資料。第一正規化的重點是：每個資料列、每個欄位都應存放單一且不可再分的值，不可在單一欄位中重複記錄多個值，也不可用重複欄位表示同一類資料。

判斷 1NF 時，可檢查：

• 每個儲存格是否只含一個值。
• 是否以「電話1、電話2、電話3」等重複欄位記錄同一屬性。
• 是否把一串項目存進單一欄位，導致無法直接以欄位值查詢、排序或更新。

解題方法

先以一筆資料同時具有多項明細為例，展示不符合 1NF 的表格；再說明其查詢與異動問題，並將明細拆成一筆一項的資料列，或拆成主檔與明細表。最後用相同主題呈現 XML 或 JSON 巢狀資料，說明巢狀表示法如何保留資料的階層與群組關係。

第一正規化不符合的例子與問題

假設以「一筆訂單」為單位，將多項商品塞進同一筆資料：

「商品編號」、「商品名稱」與「數量」欄位各自存有多個值，因此不符合 1NF。這種設計會造成以下問題：

1. 查詢困難：查詢所有購買 P02 的訂單時，必須解析欄位中的清單，難以直接比對與建立索引。
2. 更新不一致：商品名稱若有變更，可能要修改多筆清單中的部分內容，容易漏改或誤改。
3. 新增與刪除不便：新增一項商品可能需要改寫整個欄位；刪除其中一項商品也必須操作欄位內的清單。
4. 資料完整性較難維護：資料庫不容易針對清單中的每個商品編號，分別設定外鍵或檢查數量等限制。

符合 1NF 的簡化表示，可將每項商品各列一筆：

實務上通常進一步拆成訂單主檔與訂單明細表，例如：

訂單主檔