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

核心觀念

本題考查 ER 模型中實體、屬性、候選鍵、關係與基數的辨識，以及如何將 ER 圖轉換為關聯綱目。題目沒有涉及法條。

每個實體的唯一屬性可作為主鍵；有複合唯一條件時，以複合鍵標示。關係若帶有自身屬性，該屬性應放在關係上；轉成關聯綱目時，依關係基數決定外鍵的位置，或另建關聯表。

解題方法

先列出四個實體及其鍵，再辨識題幹要求記錄的關係：

1. 疫苗：名稱與製造國合起來唯一，因此主鍵為 $(name, madeBy)$。
2. 施打輪：輪數唯一，因此主鍵為 $num$。
3. 登記人：識別號唯一，因此主鍵為 $pId$。
4. 接種站：識別號唯一，因此主鍵為 $sId$。

接著把題幹中需要保存的資訊轉成關係：

• 每一施打輪使用哪一種疫苗：建模為「使用」關係。
• 每一施打輪有哪些接種站：建模為「設置於」關係。
• 登記人在哪一輪、哪一站接種，以及接種日期：建模為「預約接種」關係，接種日期是關係屬性。

本解答採以下合理假設：每一施打輪使用一種疫苗，一種疫苗可用於多個施打輪；每一施打輪可安排多個接種站，每個接種站可服務多個施打輪；每位登記人在每一施打輪至多有一筆接種紀錄，每筆紀錄對應一個接種站。若實際業務允許同一施打輪使用多種疫苗，則「使用」關係須改為多對多，並以關聯表保存。

（一）實體關係圖

以下以文字式 ER 圖表示；1、N 分別表示一與多，方括號中的欄位為關係屬性。

疫苗 (Vaccine) 1 ─── 使用 ─── N 施打輪 (Round)

施打輪 (Round) N ─── 設置於 ─── N 接種站 (Station)

登記人 (Person) 1 ─── 預約接種 [接種日期] ─── N 施打輪 (Round)
 │
 └──────── N 接種站 (Station)

「預約接種」是同時連結登記人、施打輪與接種站的三元關係；接種日期依附於該筆預約接種紀錄。每筆紀錄對應一位登記人、一個施打輪及一個接種站；每位登記人可有多筆不同施打輪的紀錄。

各實體屬性與主鍵如下：

Vaccine(name, phase, madeBy, amount) PK = (name, madeBy)
Round(num, rBdate, rEdate, dose) PK = num
Person(pId, name, bDate, gender) PK = pId
Station(sId, name, capacity) PK = sId

其中 (name, madeBy) 的兩個欄位共同唯一，不是各自唯一；ER 圖中應將兩者一同標示為疫苗的複合鍵。

（二）關聯綱目

以下以 PK 表示主鍵、FK 表示外鍵：

核心觀念

本題考查函數相依（FD）、多值相依（MVD）、候選鍵，以及以 3NF 合成法分解關聯綱目。

• 屬性閉包 $X^+$：由一組屬性 $X$ 出發，反覆套用相依規則，能推得的所有屬性。
• 候選鍵：能決定關聯中所有屬性，且本身沒有多餘屬性的最小屬性集合。
• 3NF：對每一個非平凡函數相依 $X\rightarrow A$，至少符合以下一項：

1. $X$ 是超鍵；或
2. $A$ 是主屬性，也就是屬於某個候選鍵的屬性。

• 本題另有 MVD $\{dateTime,author\}\twoheadrightarrow\{reply\}$，表示在固定發文時間與發文人時，回答值可獨立於其餘非鍵屬性變動。MVD 不直接用來推導函數相依的候選鍵，但在分解時須注意回答可能有多筆。

以下假設題目列出的相依為完整的設計依據，且屬性值皆為單一值。

（一）推導關聯鍵

令

$$A=\{dateTime,author\}$$

由題目給定的函數相依：

$$\{dateTime,author\}\rightarrow\{forum,title,content\}$$

且由 $\{forum\}\rightarrow\{owner\}$、$\{author\}\rightarrow\{school\}$，可繼續推得 $owner$ 與 $school$。因此：

$$A^+=\{dateTime,author,forum,title,content,owner,school\}$$

但目前尚未包含 $reply$。題目給的是 MVD，而非 FD；MVD 不表示一個 $dateTime,author$ 組合只能對應一個 $reply$，所以不能據此把 $reply$ 加入一般 FD 閉包。就候選鍵而言，$reply$ 可以是該發文的多筆回答之一，因此以包含回答資料的原始關聯來識別完整 tuple，需將 $reply$ 納入鍵。

另外，$dateTime$ 與 $author$ 都沒有出現在任何相依右側，無法由其他屬性推得；$reply$ 也沒有由其他屬性函數決定。因此，候選鍵必須包含這三者。集合 $\{dateTime,author,reply\}$ 可決定其餘文章屬性，且刪除其中任一屬性便不能識別原始關聯中的完整 tuple。

（二）分解為 3NF，並標示主鍵與外鍵

先將每個函數相依整理成 3NF 關聯：

1. $\{dateTime,author\}\rightarrow\{forum,title,content\}$：建立文章關聯。
2. $\{forum\}\rightarrow\{owner\}$：建立討論版關聯。
3. $\{author\}\rightarrow\{school\}$：建立作者關聯。

由於 $(dateTime,author)$ 不是完整候選鍵（同一文章可有多筆回答），回答應另以「文章識別欄位加回答值」作為複合鍵，將 MVD 所表達的多筆回答分開儲存。

分解如下：

關聯綱目	主鍵（PK）	外鍵（FK）	說明
Article(dateTime, author, forum, title, content)	(dateTime, author)	author → Author.author；forum → Forum.forum	儲存文章本身；文章識別欄位決定版面、標題與內容。
Forum(forum, owner)	forum	無	每個討論版對應一位版主。

核心觀念

本題考查 SQL 的資料更新、選擇與投影、分組彙總，以及外連接。

• UPDATE 用於修改資料表欄位；以子查詢計算的平均星等更新商品資料。
• WHERE 篩選符合條件的資料，SELECT 列出指定欄位。
• GROUP BY 依商品與賣家分組，搭配 SUM 計算總購買量。
• 外連接可保留沒有符合交易紀錄的賣家或商品；本題第（四）小題要求列出「每一位賣家」，因此應保留所有商品資料，即使該商品在 2021 年沒有交易。

依題目所述，Product 的主鍵為 (pId, seller)，因此商品及賣家應以這兩個欄位共同識別。

解題方法

（一）更新各商品與賣家的星等

對每一組 (pId, seller)，計算 Transaction.star 的平均值，再更新 Product.star。使用相關子查詢，讓子查詢中的交易列與外層商品列以商品編號及賣家名稱配對：

UPDATE Product AS P
SET star = (
 SELECT AVG(T.star)
 FROM Transaction AS T
 WHERE T.pId = P.pId
 AND T.seller = P.seller
);

AVG 會忽略 NULL 星等。若某商品沒有任何交易，平均值沒有可計算的資料，結果為 NULL；依此寫法，該商品的 Product.star 也會更新為 NULL。

（二）列出藍芽耳機的賣家名稱與商品名稱

此題只需從 Product 篩選類別，再投影賣家名稱與商品名稱：

SELECT seller, name
FROM Product
WHERE category = 'Bluetooth earphone';

題目未要求去除重複列，故不需加上 DISTINCT。

（三）列出總購買量至少 1000 的藍芽耳機及其賣家

先將藍芽耳機依 (pId, seller) 分組，再用 SUM(amount) 計算每組交易的總購買量。分組欄位同時保留商品名稱與賣家名稱，以便輸出：

SELECT P.seller, P.name, P.star
FROM Product AS P
JOIN Transaction AS T
 ON T.pId = P.pId
 AND T.seller = P.seller
WHERE P.category = 'Bluetooth earphone'
GROUP BY P.pId, P.seller, P.name, P.star
HAVING SUM(T.amount) >= 1000;

此處商品類別條件屬於單筆商品資料的篩選，因此放在 WHERE；總購買量是分組後的彙總結果，因此門檻條件放在 HAVING。商品須與交易連接，才能計算購買量；以 (pId, seller) 連接可確保交易歸屬於正確的商品與賣家。

（四）列出每位賣家名稱及其 2021 年有賣出的商品名稱

核心觀念

本題考查 SQL 的巢狀子查詢、相關子查詢與 EXISTS，以及如何將巢狀查詢改寫為連接查詢，並搭配索引改善查詢效率。

原查詢以 User 為外層資料表，逐一檢查使用者是否符合日期條件，並透過相關子查詢確認該使用者是否曾交易過指定類別、星等為 5 的商品。EXISTS 判斷子查詢是否至少回傳一筆資料；IN 則判斷外層值是否包含在子查詢結果中。

（一）查詢意義

查詢結果列出加入日期為 2020 年（含）以後，且曾購買星等為 5 的 Bluetooth earphone 商品之使用者編號與姓名。

判斷條件如下：

1. YEAR(rdate) >= '2020'：使用者加入年份為 2020 年或以後。
2. T.uId = U.uId：交易紀錄屬於目前檢查的使用者。
3. star = 5：交易資料中的星等為 5。
4. pId = T.pId 且 category = 'Bluetooth earphone'：該交易的商品編號對應到類別為 Bluetooth earphone 的商品。

符合上述條件的使用者，其 uId 會出現在子查詢結果中，因此通過外層的 IN 條件。

（二）效率問題與改寫

效率不佳處

原查詢包含巢狀的相關子查詢：內層 Transaction 子查詢參照外層 User 的 U.uId，而其中的 EXISTS 又逐筆查詢 Product。資料庫若未能有效將子查詢轉換為連接或半連接操作，可能需要反覆檢查相關資料。

此外，YEAR(rdate) 對欄位套用函數，可能使資料庫無法直接利用 rdate 上的索引進行範圍搜尋。

改寫方式

將條件改為連接查詢，並把日期條件改寫成範圍比較：

SELECT DISTINCT U.uId, U.name
FROM User AS U
JOIN Transaction AS T
 ON T.uId = U.uId
JOIN Product AS P
 ON P.pId = T.pId
 AND P.seller = T.seller
WHERE U.rdate >= '2020-01-01'
 AND T.star = 5
 AND P.category = 'Bluetooth earphone';

原題的 EXISTS 只要求商品資料至少存在一筆符合條件的紀錄，因此連接後使用 DISTINCT 去除同一使用者因多筆符合交易而產生的重複列。連接條件包含 P.seller = T.seller，是因為題目指定 Product 的主鍵由 (pId, seller) 組成；只比對 pId 不足以完整對應商品。