115 一般警察特考二等 刑事警察人員類別電子監察組 資料庫管理與運用 考古題詳解

核心觀念

本題考查 SQL 的下列基本操作：

1. GROUP BY：依指定欄位分組。
2. 聚合函數：

• COUNT(*)：計算每組資料筆數。
• SUM(StockQuantity)：計算每組庫存總量。

3. HAVING：針對分組後的聚合結果進行條件篩選。
4. JOIN：利用兩表之間的外鍵與主鍵關聯資料。
5. WHERE：在分組前，先篩選符合條件的個別資料列。

題目中的 Product.ManufactureID 對應 Manufacturer.ManufacturerID。雖然題目文字中的欄位名稱略有差異，SQL 應依此關聯鍵進行連接。

---

（一）輸出每一個類別各有幾種產品

解題方法

題目要求「每一個類別」分別統計產品數量，因此應：

1. 以 Category 分組。
2. 使用 COUNT(*) 計算每一組的產品筆數。
3. 輸出類別與產品數量。

SQL

SELECT Category,
 COUNT(*) AS ProductCount
FROM Product
GROUP BY Category;

解題說明

GROUP BY Category 會將相同類別的產品歸為同一組；COUNT(*) 則計算每組包含幾筆產品資料。

由於題目說明每一個 ProductID 對應一種特定產品，且每筆產品資料代表一種產品，因此使用 COUNT(*) 即可計算各類別的產品種類數。

若資料表可能存在同一產品重複記錄，才需使用：

COUNT(DISTINCT ProductID)

但依題目所給定的產品編號唯一性，使用 COUNT(*) 即可。

---

（二）找出庫存量不足的產品編號及製造商名稱、電話

解題方法

安全庫存量為 $10$，庫存量不足表示：

$$StockQuantity < 10$$

題目要求同時輸出：

• 產品編號：來自 Product
• 製造商名稱：來自 Manufacturer
• 製造商電話：來自 Manufacturer

因此必須：

1. 以 Product.ManufactureID 與 Manufacturer.ManufacturerID 進行內部連接。
2. 以 WHERE 篩選 StockQuantity < 10 的產品。
3. 輸出所需欄位。

SQL

SELECT P.ProductID,
 M.ManuName,
 M.Phone
FROM Product AS P
JOIN Manufacturer AS M
 ON P.ManufactureID = M.ManufacturerID
WHERE P.StockQuantity < 10;

解題說明

JOIN 的連接條件為：

P.ManufactureID = M.ManufacturerID

其中：

• P.ManufactureID 是產品表中的製造商編號。
• M.ManufacturerID 是製造商表的主鍵。

WHERE P.StockQuantity < 10 是針對每一筆產品資料進行篩選，故應放在 WHERE 子句，而不是 HAVING 子句。

若安全庫存量為 10，庫存量剛好等於 10 並不算不足，因此不能寫成：

WHERE P.StockQuantity <= 10

---

（三）找出所有產品總庫存量少於 1000 的類別

解題方法

題目要求計算「各類別所有產品的總庫存量」，因此應：

核心觀念

本題考查資料庫查詢處理中的兩種基本策略：

1. 檔案掃描（file scan）：逐一讀取資料檔案中的資料頁與資料列，檢查是否符合查詢條件。
2. 索引查詢（index lookup）：利用依據 ProductID 建立的索引，快速定位符合條件的資料列，再取得其 Price 欄位。

查詢句為：

SELECT Price
FROM Product
WHERE ProductID = 'P01';

其處理重點是：

• 先判斷 ProductID = 'P01' 是否成立。
• 對符合條件的資料列，只輸出 Price 欄位。
• 索引通常只負責定位資料列，不一定包含完整的 Price 欄位，因此常須再回到資料檔案取值。

以下假設：

• ProductID 已建立索引。
• ProductID 具唯一性，因此最多只有一筆符合資料。
• 索引為常見的 B+ Tree 索引。
• 資料表採一般資料檔案儲存，未特別假設資料已依 ProductID 排序。

---

解題方法一：直接掃描資料檔案

處理步驟

第一步：解析查詢句

資料庫管理系統先解析 SQL：

SELECT Price
FROM Product
WHERE ProductID = 'P01';

可拆解為：

• 資料表：Product
• 查詢條件：ProductID = 'P01'
• 輸出欄位：Price

第二步：開啟 Product 的資料檔案

系統取得 Product 表格所對應的資料檔案，並由資料檔案的第一個資料頁開始讀取。

資料表通常以資料頁（data page）為單位存放多筆資料列，因此系統實際上是依序讀取資料頁，再逐筆檢查其中的資料列。

第三步：逐一檢查資料列

對每一筆資料列進行條件判斷：

$$\text{ProductID} = 'P01'$$

例如：

ProductID	ProductName	Price
P02	Keyboard	800
P01	Mouse	500
P03	Monitor	6000

系統依序檢查：

• P02 = P01，不符合，略過。
• P01 = P01，符合。
• P03 = P01，不符合，略過。

第四步：輸出符合資料列的 Price

找到符合條件的資料列後，只取出其 Price 欄位。

本例輸出：

Price
500

第五步：完成掃描

若已掃描完整個資料檔案，系統即可確定所有符合條件的資料列均已找出，完成查詢。

即使 ProductID 具唯一性，檔案掃描通常仍須掃描完整個資料檔案，因為未使用索引時，系統無法僅憑前面讀到的資料判斷後面是否還有符合條件的資料。

---

解題方法二：使用 ProductID 索引

處理步驟

第一步：解析查詢句並辨識可使用的索引

系統解析出查詢條件：

WHERE ProductID = 'P01'

由於條件使用的欄位正是索引鍵 ProductID，因此可以使用該索引進行查詢。

第二步：從索引根節點開始搜尋

以 B+ Tree 索引為例，系統由根節點開始搜尋索引鍵值 'P01'。

B+ Tree 的內部節點包含鍵值範圍與子節點指標，系統依照 'P01' 所屬的範圍，選擇下一層節點。

第三步：逐層向下走到葉節點

系統持續依據鍵值範圍往下搜尋，直到抵達葉節點。

B+ Tree 的葉節點通常包含：

• 索引鍵值，例如 P01
• 對應資料列的位置資訊，例如 RID（Record Identifier）
• 對應資料頁編號與資料列位置

索引葉節點可能呈現如下：

ProductID	資料位置
P01	Page 8, Slot 3

第四步：在葉節點比對 P01

系統在葉節點中尋找：

$$\text{ProductID} = 'P01'$$

若找到 P01，便取得對應資料列的位置。

若索引為唯一索引，找到該筆資料後即可確定不會有其他相同的 ProductID。若索引非唯一，則須繼續取得所有相同索引鍵值所對應的資料位置。

第五步：依資料位置讀取資料列

由於本題索引是依 ProductID 建立，索引內容不一定包含 Price，因此系統依照索引提供的資料位置，回到 Product 資料檔案，讀取完整資料列。

例如：

Page 8, Slot 3
ProductID = P01
ProductName = Mouse
Price = 500

第六步：輸出 Price

系統從該資料列取出 Price 欄位並輸出：

Price
500

第七步：完成查詢

若 ProductID 為唯一索引，找到該筆資料後即可完成查詢。若索引允許重複值，則須處理所有符合 P01 的資料列，再輸出每一筆的 Price。

---

兩種策略的成本概念

設：

• $B$：Product 資料檔案所占的資料頁數
• $h$：索引 B+ Tree 的高度
• $s$：符合查詢條件的資料列數
• $C_{\text{data}}$：讀取符合資料列所需的資料頁成本

檔案掃描成本

直接掃描通常須讀取整個資料檔案，因此 I/O 成本約為：

$$C_{\text{file scan}} \approx B$$

若資料檔案很大，即使只尋找一筆資料，也可能必須讀取大量資料頁。

索引查詢成本

索引查詢通常包括：

核心觀念

本題考查兩項資料完整性限制：

1. PRIMARY KEY：主鍵完整性，確保每筆資料具有唯一且不可為空的識別值。
2. NOT NULL：非空值限制，確保指定欄位建立資料時必須提供值。
3. FOREIGN KEY ... ON DELETE CASCADE：參照完整性，確保子表格資料所參照的母表格資料被刪除時，相關子表格資料同步刪除。

---

一、PRIMARY KEY 的限制

1. BOOK 表格

primary key (ISBN)

表示 ISBN 是 BOOK 表格的主鍵，具有以下限制：

• 每一本書的 ISBN 必須唯一，不得與其他書籍重複。
• ISBN 不得為 NULL。
• 新增資料時必須提供 ISBN。
• 修改 ISBN 時，不得修改成已存在於其他資料列的值。
• 刪除或修改資料時，仍須符合相關參照完整性限制。

例如，下列資料不可同時存在：

ISBN Title
9781234567890 資料庫系統
9781234567890 作業系統

因為兩筆資料的主鍵 ISBN 重複。

---

2. BORROWER 表格

primary key (No, ISBN)

這是由 No 與 ISBN 組成的複合主鍵。限制如下：

• (No, ISBN) 的組合必須唯一。
• No 不得為 NULL。
• ISBN 不得為 NULL。
• 同一位借閱者可以借閱不同書籍。
• 同一本書可以被不同借閱者借閱。
• 同一位借閱者不得對同一本書建立兩筆完全相同的借閱資料。

例如：

No ISBN
A0001 9781234567890
A0001 9789876543210
A0002 9781234567890

以上資料的複合主鍵均不重複，因此可以存在。

但下列資料不可同時存在：

No ISBN
A0001 9781234567890
A0001 9781234567890

因為兩筆資料的 (No, ISBN) 組合完全相同。

---

二、NOT NULL 的限制

原始定義中只有 DueDate 明確指定：

DueDate date not null

因此：

• 新增 BORROWER 資料時，DueDate 必須提供值。
• DueDate 不得為 NULL。
• 若未提供 DueDate，且沒有預設值，新增資料會失敗。
• 修改資料時，也不得將 DueDate 修改為 NULL。

例如，下列資料不合法：

insert into BORROWER (No, ISBN, DueDate)
values ('A0001', '9781234567890', null);

必須注意，雖然 No 與 ISBN 的欄位定義中沒有個別寫出 NOT NULL，但因為兩者共同構成主鍵，所以主鍵欄位本身仍不得為 NULL。

因此，BORROWER 實際上至少具有下列非空限制：

No 不得為 NULL，因為屬於 PRIMARY KEY
ISBN 不得為 NULL，因為屬於 PRIMARY KEY
DueDate 不得為 NULL，因為明確指定 NOT NULL

核心觀念

本題考查下列關聯式資料庫正規化觀念：

1. MongoDB 文件中的巢狀物件與陣列，如何轉換為關聯式資料表。
2. 第一正規化（1NF）：每一欄位必須具備不可再分割的單一值。
3. 函數相依（Functional Dependency, FD）。
4. 主鍵、候選鍵與外鍵的設計。
5. 第二正規化（2NF）：非主鍵屬性不得只相依於複合主鍵的一部分。
6. 第三正規化（3NF）：非主鍵屬性不得經由其他非主鍵屬性間接相依於主鍵。

3NF 的判定原則為：對每一個非平凡函數相依 $X \to A$，必須符合下列任一條件：

• $X$ 是超鍵；或
• $A$ 是候選鍵中的屬性，也就是主屬性（prime attribute）。

---

解題方法

原始 JSON 包含四類資料：

• 貼文本身的資料：postId、content、createdAt
• 作者資料：author.id、author.name
• 貼文與標籤的多對多關係：一則貼文可有多個標籤
• 貼文與按讚使用者的多對多關係：一則貼文可被多位使用者按讚

因此，應將資料拆分為「使用者」、「貼文」、「標籤」、「貼文標籤關聯」、「貼文按讚關聯」五個關聯。

若直接將 tags 與 likedby 儲存在貼文表中，會產生陣列或重複群組，不符合 1NF；若將作者姓名、標籤名稱重複放在各筆貼文資料中，也會產生更新異常與傳遞相依問題。

---

一、使用者表 USER

關聯模式：

$$\text{USER}(\underline{\text{UserID}},\ \text{UserName})$$

欄位說明：

欄位	說明
UserID	使用者識別碼，主鍵
UserName	使用者名稱

主鍵：

$$\text{PK} = \{\text{UserID}\}$$

函數相依：

$$\text{UserID} \to \text{UserName}$$

說明：

每一個 UserID 對應唯一的使用者名稱。UserID 是主鍵，因此是超鍵，符合 3NF。

---

二、貼文表 POST

關聯模式：

$$\text{POST}(\underline{\text{PostID}},\ \text{AuthorID},\ \text{Content},\ \text{CreatedAt})$$

欄位說明：

欄位	說明
PostID	貼文識別碼，主鍵
AuthorID	作者識別碼，外鍵，參照 USER(UserID)
Content	貼文內容
CreatedAt	發表時間

主鍵：

$$\text{PK} = \{\text{PostID}\}$$

外鍵：

$$\text{AuthorID} \to \text{USER}(\text{UserID})$$

函數相依：

$$\text{PostID} \to \text{AuthorID},\ \text{Content},\ \text{CreatedAt}$$

說明：

PostID 能唯一決定該貼文的作者、內容與發表時間。作者姓名不直接存放在 POST 表，而是透過 AuthorID 參照 USER 表取得：

$$\text{PostID} \to \text{AuthorID} \to \text{UserName}$$

將 UserName 移至 USER 表後，POST 表中不存在「非主鍵屬性決定另一個非主鍵屬性」的傳遞相依。因此，POST 符合 3NF。

---

三、標籤表 TAG

關聯模式：

$$\text{TAG}(\underline{\text{TagID}},\ \text{TagName})$$

欄位說明：

欄位	說明
TagID	標籤識別碼，主鍵
TagName	標籤名稱，應設定唯一限制

主鍵：

$$\text{PK} = \{\text{TagID}\}$$

函數相依：

$$\text{TagID} \to \text{TagName}$$

若規定標籤名稱不可重複，還可有：

$$\text{TagName} \to \text{TagID}$$

此時 TagID 與 TagName 均可作為候選鍵，且每一個決定項均為超鍵。故 TAG 符合 3NF。

---

四、貼文標籤關聯表 POST_TAG

由於一則貼文可有多個標籤，一個標籤也可以出現在多則貼文中，兩者是多對多關係，因此建立關聯表：

$$\text{POST\_TAG}(\underline{\text{PostID},\ \text{TagID}})$$

欄位說明：

欄位	說明
PostID	貼文識別碼，外鍵
TagID	標籤識別碼，外鍵

主鍵：

$$\text{PK} = \{\text{PostID},\text{TagID}\}$$

外鍵：

$$\text{PostID} \to \text{POST}(\text{PostID})$$ $$\text{TagID} \to \text{TAG}(\text{TagID})$$

在此表中，一筆資料只表示「某篇貼文使用某個標籤」。若沒有其他屬性，則不存在非平凡的非鍵函數相依，只有複合主鍵本身決定該筆關聯資料。因此符合 3NF。

複合主鍵也能防止同一篇貼文重複使用同一個標籤：

$$(\text{PostID},\text{TagID})$$

不得重複。

---

五、貼文按讚關聯表 POST_LIKE

一篇貼文可以被多位使用者按讚，一位使用者也可以替多篇貼文按讚，因此建立另一個多對多關聯表：

$$\text{POST\_LIKE}(\underline{\text{PostID},\ \text{UserID}})$$

欄位說明：