111 地方特考三等 資訊處理 資料庫應用 考古題詳解

核心觀念

這題考 ER 圖轉關聯綱要，重點是辨認弱實體、識別關係，以及依照關係基數決定外鍵放在哪張表。

• **弱實體「分館」**以「博物館」識別。圖中的 BID 是部分鍵，LID 是博物館的主鍵，因此分館的完整主鍵為 $(LID, BID)$。
• 一對多關係「保存」中，分館最多對應多個館藏物，每件館藏物恰屬於一個分館。因此將分館主鍵放進「館藏物」表作為外鍵。
• 多對多關係「申請」中，博物館與會員兩端的最大基數都是 $*$，因此另建關係表，以兩端主鍵組合作為複合主鍵。

解題方法

先建立各實體的關係表，再依關係種類處理：

1. 強實體直接以自身主鍵建立關係表。
2. 弱實體將識別它的強實體主鍵併入，與部分鍵組成複合主鍵。
3. 一對多關係將「一」端主鍵放入「多」端表作為外鍵。
4. 多對多關係另建關係表，放入兩端主鍵，並以兩者組合作為主鍵。

依此可得：

• 博物館：博物館(LID) 主鍵：LID

• 分館：分館(LID, BID, Address) 主鍵：(LID, BID)；LID 為參照博物館的外鍵。

• 館藏物：館藏物(C_Number, LID, BID) 主鍵：C_Number；(LID, BID) 為參照分館的外鍵。

• 會員：會員(MID, Address) 主鍵：MID

• 申請：申請(LID, MID) 主鍵：(LID, MID)；LID、MID 分別參照博物館與會員。

核心觀念

本題考查 SQL 的字串比對、WHERE 篩選、GROUP BY 分組，以及 HAVING 對分組結果的篩選。

• LIKE '%中山%'：% 代表任意長度的字元，因此可比對欄位中任意位置含有「中山」的資料。
• WHERE：先篩選個別資料列，再進行分組與彙總。
• GROUP BY：依指定欄位分組。
• COUNT(*)：計算每組的資料列數。
• HAVING：分組後，依彙總結果篩選群組。

解題方法

（一）列出街道含「中山」的員工編號與姓名

以 LIKE 搜尋 Street 欄位中任何位置出現「中山」的資料：

SELECT SID, Name
FROM Employee
WHERE Street LIKE '%中山%';

符合條件的街道及員工如下：

城市欄位沒有納入條件，因此不論員工居住在哪個城市都會列入。查詢沒有指定 ORDER BY，結果列的排列順序不保證固定。

（二）計算高薪員工人數，並保留人數至少為 2 的部門

先依 WHERE Salary > 10000 篩選員工，再按 Dnumber 分組並計算人數：

核心觀念

功能相依 $X\rightarrow Y$ 表示：在關聯 $R$ 的所有合法資料列中，只要兩列的 $X$ 屬性值相同，兩列的 $Y$ 屬性值也必須相同。$X$ 稱為決定項，$Y$ 稱為被決定項。

企業中的功能相依來自業務規則與資料定義，例如「每個員工編號只對應一位員工」。找出功能相依時，應訪談業務人員、檢視作業規則與資料字典，再以現有資料檢查是否有違反情形。資料樣本可以找出反例、否定一項功能相依；但即使樣本中沒有反例，也不能單憑樣本證明該相依對所有合法資料都成立。

解題方法

逐項比較決定項 $X$ 相同的資料列：

• 若找到兩列的 $X$ 值相同、$Y$ 值不同，便有反例，該功能相依「已可確認不存在」。
• 若目前資料中找不到反例，只能判定「還有可能存在」，不能據此確認它必然成立。

各項功能相依判斷

1. $A1\rightarrow A2$：已可確認不存在。 第 1、3、4 列的 $A1$ 都是 $1$，但對應的 $A2$ 分別是 $2、3、11$。相同的決定項對應到不同的 $A2$，已構成反例。

2. $A1\rightarrow A3$：還有可能存在。 $A1=1$ 時，對應的 $A3$ 均為 $3$；其餘 $A1$ 值各自只出現一次。目前資料沒有相同 $A1$ 卻有不同 $A3$ 的反例。

3. $\{A1,A5\}\rightarrow A3$：還有可能存在。 決定項 $(A1,A5)$ 相同的資料列只有 $(1,60)$ 重複出現，而這兩列的 $A3$ 都是 $3$。

核心觀念

本題為關聯式資料庫綱目設計與正規化理論的經典題型，評量重點涵蓋以下核心理論與定理：

1. 屬性閉包（Attribute Closure, $X^+$）與候選鍵（Candidate Key）：

• 候選鍵為具備「唯一性（Uniqueness）」與「最小性（Minimality）」的超鍵（Superkey）。
• 屬性分類判定法：若屬性僅出現在功能相依（FD）的左側，或從未出現在任何 FD 中，該屬性必然屬於所有候選鍵的一部分（核心屬性）。

2. 正規化階層定義（Normal Forms）：

• 第一正規化（1NF）：所有屬性值皆為單一值且不可再分割（Atomic）。
• 第二正規化（2NF）：符合 1NF，且「非主屬性（Non-prime Attribute）」完全功能相依於候選鍵（即不存在部分功能相依 Partial Functional Dependency）。
• 第三正規化（3NF）：符合 2NF，且非主屬性不遞移相依於候選鍵。
• BCNF（Boyce-Codd Normal Form）：關聯中所有非顯然（Non-trivial）功能相依 $X \rightarrow Y$，$X$ 皆為超鍵（Superkey）。

3. 關聯綱目分解性質（Properties of Decomposition）：

• 無損連接（Lossless-Join Decomposition）：若關聯 $R$ 分解為 $R_1$ 與 $R_2$，其充要條件為 $(R_1 \cap R_2) \rightarrow (R_1 - R_2)$ 或 $(R_1 \cap R_2) \rightarrow (R_2 - R_1)$ 成立（Heath 定理）。
• 保持相依性（Dependency Preservation）：分解後各子關聯的功能相依聯集之閉包，須等於原關聯功能相依集合之閉包，即 $(F_1 \cup F_2)^+ = F^+$。

解題方法與詳細推導

（一）表格 $R$ 之候選鍵判定

1. 屬性分類分析：

• 關聯屬性集合：$U = \{A1, A2, A3, A4, A5\}$。
• 功能相依集合：$F = \{ \{A1, A3\} \rightarrow A5,\ A1 \rightarrow A2 \}$。
• 僅出現在左側（L）：$A3$。
• 僅出現在右側（R）：$A2, A5$。
• 雙邊皆未出現（N）：$A4$。
• 雙邊皆有出現（B）：無；而 $A1$ 僅出現在左側（L）。

2. 候選鍵必要屬性判定：

• 屬性 $A1, A3$ 僅作為決定項存在，無法由其他屬性推導導出。
• 屬性 $A4$ 未參與任何功能相依，無法由任何屬性決定。
• 因此，任何候選鍵必須包含核心屬性集合 $K_0 = \{A1, A3, A4\}$。

3. 屬性閉包推導： $(\{A1, A3, A4\})^+ = \{A1, A3, A4\}$ 依據 $A1 \rightarrow A2$，加入 $A2$： $(\{A1, A3, A4\})^+ = \{A1, A2, A3, A4\}$ 依據 $\{A1, A3\} \rightarrow A5$，加入 $A5$： $(\{A1, A3, A4\})^+ = \{A1, A2, A3, A4, A5\} = R$
4. 最小性驗證：

• $(\{A1, A3\})^+ = \{A1, A2, A3, A5\} \neq R$（缺少 $A4$）
• $(\{A1, A4\})^+ = \{A1, A2, A4\} \neq R$
• $(\{A3, A4\})^+ = \{A3, A4\} \neq R$ 任何真子集均無法決定全體屬性，故 $\{A1, A3, A4\}$ 滿足最小性。

表格 $R$ 的候選鍵只有一組：$\{A1, A3, A4\}$。

（二）違反正規化之原因分析

1. 主屬性與非主屬性劃分：

• 候選鍵為 $\{A1, A3, A4\}$。
• 主屬性（Prime Attributes）：$A1, A3, A4$。
• 非主屬性（Non-prime Attributes）：$A2, A5$。

2. 正規化條件檢視：

• 題目給定所有屬性值均為 atomic，滿足 1NF。
• 檢視 2NF（消除非主屬性對候選鍵的部分相依）：
• 功能相依 $A1 \rightarrow A2$：$A1 \subset \{A1, A3, A4\}$（$A1$ 為候選鍵的真子集），非主屬性 $A2$ 僅相依於部分鍵 $A1$。
• 功能相依 $\{A1, A3\} \rightarrow A5$：$\{A1, A3\} \subset \{A1, A3, A4\}$，非主屬性 $A5$ 僅相依於部分鍵 $\{A1, A3\}$。

3. 結論： 表格 $R$ 存在部分功能相依（Partial Functional Dependency），嚴重違反第二正規化（2NF）規定，最高僅符合 1NF。此設計會衍生資料重複儲存（Data Redundancy），以及新增、刪除、修改異常（Anomalies）。

（三）評估資料表切割為 $R1(A1, A4, A5)$ 與 $R2(A2, A3, A4, A5)$

(a) 判定新表格 $R1$ 與 $R2$ 的候選鍵

• $R1(A1, A4, A5)$ 之投影功能相依： 計算 $R1$ 屬性子集於 $F$ 下之閉包，並投影至 $R1$：

• $(A1)^+ \cap R1 = \{A1, A2\} \cap \{A1, A4, A5\} = \{A1\}$

• $(A4)^+ \cap R1 = \{A4\}$

• $(A5)^+ \cap R1 = \{A5\}$

• $(\{A1, A4\})^+ \cap R1 = \{A1, A2, A4\} \cap \{A1, A4, A5\} = \{A1, A4\}$

• $(\{A1, A5\})^+ \cap R1 = \{A1, A2, A5\} \cap \{A1, A4, A5\} = \{A1, A5\}$

• $(\{A4, A5\})^+ \cap R1 = \{A4, A5\}$ $R1$ 中不存在任何非顯然（Non-trivial）功能相依，$F_{R1} = \emptyset$。 因此，$R1$ 的候選鍵為全體屬性集合 $\{A1, A4, A5\}$。

• $R2(A2, A3, A4, A5)$ 之投影功能相依： 計算 $R2$ 屬性子集於 $F$ 下之閉包，並投影至 $R2$： 原 FD 中的決定項均含有 $A1$，而 $R2$ 不包含屬性 $A1$。 $R2$ 任一真子集的閉包皆僅能推導出其本身，不存在非顯然功能相依，$F_{R2} = \emptyset$。 因此，$R2$ 的候選鍵為全體屬性集合 $\{A2, A3, A4, A5\}$。

(b) 是否滿足 Lossless Join

• 兩子表格的交集屬性為： $R1 \cap R2 = \{A1, A4, A5\} \cap \{A2, A3, A4, A5\} = \{A4, A5\}$
• 計算交集屬性之閉包： $(\{A4, A5\})^+ = \{A4, A5\}$
• 檢驗是否構成超鍵：
• $(\{A4, A5\})^+ \not\supseteq R1$（無法決定 $A1$）
• $(\{A4, A5\})^+ \not\supseteq R2$（無法決定 $A2, A3$）
• 依據二元分解無損連接判定準則，交集屬性並非任一關聯的超鍵，因此不滿足 Lossless Join（屬於有損分解 Lossy Decomposition）。

(c) 是否會喪失任何功能相依

• 檢驗 $FD1: \{A1, A3\} \rightarrow A5$： 屬性 $A1$ 僅在 $R1$，$A3$ 僅在 $R2$，兩者被切割分散於不同資料表，無法局部驗證。