資訊處理 104 年資料庫應用考古題

請舉例說明在實體關係圖（ER diagram ）中，為何一個三元關係（ternary relationship）不等於三個二元關係（binary relationship）的結合？並畫出其實體關係 圖。（15 分）

考慮以下關聯資料表： 人事資料（教授姓名, 教授工作證號碼, 教授研究室編號, 實驗室編號, 助理姓名, 助 理工作證號碼, 助理研究室編號, 學生姓名, 學生證號碼） 假如該關聯資料表「人事資料」有以下限制： ˙每位教授都分配有一間研究室及一間實驗室，且僅分配有一間研究室及一間實驗 室。每一間研究室頂多分配給一位教授或一位助理。每一間實驗室都頂多分配給 一位教授及該教授指導的學生。 ˙每位教授都有分配一位且僅有一位固定的助理，但一位助理可同時協助至少一位 教授處理工作。 ˙每位助理都有分配一間且僅有一間研究室。 ˙學生都有一位且僅有一位指導教授，而一位指導教授可以同時指導零位以上學生。 ˙每位學生也都會在其指導教授的實驗室裡。 試依前述限制，描述所有可能的功能相依性（functional dependency）。（10 分） 試問如何以功能相依性的規則，依前述限制來決定出關聯資料表「人事資料」的 主要鍵（primary key）。（10 分） 試依前述限制繪出實體關聯圖（entity relationship diagram）。（10 分） 請判定上述關聯資料表「人事資料」是否有符合第三正規化之要求，如為否，試 問將如何修改使其能符合第三正規化要求之最少數個關聯資料表，並標示出關聯 資料表之主要鍵（primary key）。（10 分） 如每一位學生有行政相關業務需協助時，得找其指導教授所分配的助理來協助。 試請依據前一題已修改成符合第三正規化之要求後之關聯資料表，寫一SQL 程式 片段，來輸出每一位學生需找那位助理協助，該程式片段應輸出每一位學生的姓 名及所對應助理的姓名。（10 分） 104年公務人員升官等考試、104年關務人員升官等考試 104年交通事業公路、港務人員升資考試試題 代號：26230 全一張 （背面） 等 級： 薦任 類科（別）： 資訊處理 科 目： 資料庫應用

考慮以下有關案件資料的關聯綱目（Relational schema）， Case（cId, cDateTime, cArea, cLocation, cDesc） // 案件（編號，案件日期時間，案件發生的行政區，確切地點，案件描述） StakeHolder（sId, sName, sAddress, sPhone） // 利害關係人（Id，姓名，住址，電話） Relationship（cId, sId, role） // 案件利害關係人（案件編號，利害關係人Id, 在此案件扮演角色） 請寫出以下查詢之SQL 敘述，請注意，每一小題只能用一個SQL 敘述表達。 列出2015 年5 月份在臺北市萬華區所發生的所有案件之案件編號、案件地點、案 件描述和報案人Id。請注意「臺北市萬華區」為cArea 的值，「報案人」則為role 的值。（10 分） 列出2015 年5 月份發生在臺北市但沒有目擊者案件之案件編號、案件地點和案件 描述。請注意「目擊者」為role 的值。（10 分） 列出2014 年報案次數超過5 次的報案人之Id、姓名、住址和電話。（10 分）

在關聯式代數運算子中（relational algebra's operators），自然合併（natural join）和 交集（intersection）都不是原始（primitive）運算子，請問如何用其他的原始關聯式 代數運算子達到相同的運算結果？（10 分）

以下Loan 與Borrower 為兩個關聯資料表。當中Loan 記錄著每一件貸款資料； Borrower 記錄著每一位借款人資料，BorrowerLname 與BorrowerFname 分別為借款 人的姓與名字。請寫一SQL 程式片段，依姓名順序輸出每一位借款人的姓與名字及 其借款次數。（10 分） Loan LoanID BorrowerID LoanDate DueDate ReturnDate 123 874 12-23-2013 01-24-2014 01-24-2014 124 874 12-23-2013 01-24-2014 NULL 125 876 12-29-2013 01-28-2014 NULL 126 874 01-25-2014 02-21-2014 NULL Borrower BorrowerID BorrowerFname BorrowerLname 874 Leon Small 875 Gary Lowe 876 Mark Sanchez 877 Clywd Morgan

承上題，假設我們想記錄案件的相關監視器影像（VideoClip），於是設計了一個資 料表如下： VideoClip（cNo, type, location, length, content, monitor, installDate, area） // 監視器影像（案件編號，刑案種類，安裝地點，時間長度，檔案內容，監視器Id， 監視器安裝日期，所屬行政區） 有以下的函數相依（functional dependency）： {cNo}→{type} {cNo, monitor}→{content, length} {monitor}→{installDate, location} {location}→{area} 請用以上函數相依推論出主鍵，請注意必須用推論的方式來證明，否則不給分。 （10 分） 請分解VideoClip 關聯綱目，使其滿足第二正規式（2NF）但不滿足第三正規式 （3NF）。（8 分） 請分解VideoClip 關聯綱目，使其滿足第三正規式（3NF）。（7 分） 104年公務人員特種考試警察人員、一般警察人員考試及104年 特種考試交通事業鐵路人員、退除役軍人轉任公務人員考試試題 代號：71050 全一張 （背面） 類 科 別： 資訊處理

基於關聯式模式（relational model）的理論，請列出關聯表（relation）的四大特性 （property），並說明之。（20 分）

在設計關聯式資料庫時，常會對關聯資料表進行正規化。 試問對關聯資料表進行正規化之優點及缺點為何？（10 分） 如整個資料庫系統中對某一資料表的資料內容，只會有讀資料的動作，試問對該 資料表進行正規化有何優缺點？（10 分）

假設你要替員工教育訓練系統設計資料庫。該資料庫必須儲存課程、上課員工和問 卷。具體資料需求如下： 課程（Course）：包括課程編號（cId）、課程名稱（cName）、課程時 間（cDateTime）和地點（cLocation）。其中課程編號為唯一。 員工（Employee）：包括工號（eId）、姓名（eName）、部門（eDep）和電 話（eTel）。其中電話可有多個，且須記載型態（type）和號碼（number）。工號 為唯一。員工上過的課程必須記載。 問卷題目（Question）：每一門課程有一份問卷，一份問卷則有多個問題，每一問 題包括題號（qNum）、題目敘述（qDesc）、題目型態（qType）。此外，每一位 上課員工對於每一問題所回答的內容必須記載。 請依以上需求畫出實體關係圖（ERD），有任何假設也請陳述清楚。（20 分） 請將所繪出的ERD 轉成關聯資料庫綱目（schema），每一關聯綱目需列出所有屬 性，其主鍵（primary key）用底線表示，外鍵（foreign key）和相對的主鍵也請明 確表示。（10 分）

請說明何謂邏輯資料庫設計（logical database design）和實體資料庫設計（physical database design）？（15 分）

以下為在同步處理系統裡，執行交易（transaction）時常被執行的動作： read_item(Ti, A, B)：在交易Ti 中，讀取資料A 的內容，並將其存入資料B 中。 write_item(Ti, A, B)：在交易Ti 中，將資料B 的內容寫入資料A 中。 read_lock(A)：對資料A 進行鎖定，使其他交易無法對其進行讀取的動作。 write_lock(A)：對資料A 進行鎖定，使其他交易無法對其進行寫入的動作。 unlock (A)：解除對資料A 的鎖定。 設T1 與T2 為系統中正在被執行的兩個交易（transaction），而X 與Y 為T1 與T2 中 所共用的兩個變數。T1 與T2 之程式指令如下所示，並設在執行T1 與T2 前，X = 20； Y = 30。請討論在執行完T1 與T2 後，變數X 與Y 會有何可能結果。（20 分） T1 T2 read_lock(Y); read_lock(X); read_item (T1, Y, A); read_item (T2, X, C); unlock (Y); unlock(X); read_lock(X); read_lock(Y); read_item (T1, X, B); read_item (T2, Y, D); unlock (X); unlock(Y); A:=A+B; C:=C+D; write_lock(Y); write_lock(X); write_item(T1, Y, A); write_item(T2, X, C); unlock (Y); unlock (X);

為保護個人隱私，一些有機密考量的資料庫系統只允許彙總性資料的查詢，且每一 筆彙總資料是由至少5 筆原始資料所產生。 請說明為何要這樣設計。（5 分） 這樣的方式是否還是有可能洩密？請舉例說明。（10 分）

關聯表R{A,B,C,D,E,F,G}其所有功能相依性（functional dependence）如下所示： A → B, BC → DE, AEF → G 以上述三條功能相依性，請算出集合{A,C}的封閉性（即closure {A,C}+）。（10 分） 功能相依性ACF → DG 可否由上述三條功能相依性推導出來？如果可以，請列 出推導過程。（10 分）

在下列表格，針對各種正規化判定其是否具有各項特性，將其填滿"Y"（有）或"N" （沒有）。（20 分） 特性（property） 第三正規化型式 （3NF） Boyce-Codd 正規 化型式（BCNF） 第四正規化型式 （4NF） 消除因功能相依性的冗餘 （FD redundancy） Y 消除因多重值相依性的冗餘 （MVD redundancy） 保存功能相依性 保存多重值相依性 N