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

據悉行政院農業委員會欲建立一個全國養殖場、肉品交易、販售的資料庫。目前已 確定的資料如下： 國內各養殖場的資料需要建立起來。養殖場資料有負責人姓名、他的身分證號碼、 養殖場容量、地址。 目前有些養殖場有排泄物淨化處理廠，有些養殖場沒有。如果有排泄物淨化處理廠 的，則要儲存的資料有廠名、具有的設施、淨化程度。其中各處理廠的廠名，都各 不相同。 各養殖場所飼養的，可能是禽類的（如雞、鴨、鵝等），也可能是牲畜類的（如牛、 豬等）。我們統稱之為「禽畜」。其中，每一隻禽畜（例如，一隻豬，或一隻雞）都 有一個全國唯一的編號，以方便我們將來做追蹤。並且我們還要儲存每一隻禽畜的 種類、重量和防疫資料。其中，防疫資料包括以下三項：疫苗種類、注射日期與審 核單位。 養殖場所產出的禽畜，會被賣到全國數個肉品交易市場。我們要儲存每個肉品交易 市場的市場名稱和交易金額。其中市場名稱，是具有全國唯一性的資料。 我們需要建立養殖場、禽畜和肉品交易市場，三者之間的關係，以便將來追蹤每一 隻禽畜是來自那一個養殖場，以及是從那一個肉品交易市場交易出去的。並且，在 這三者的關係裡，我們要儲存出貨日期和批號。 此外，我們還有販售商店的資料要儲存，它們包括商店的店名和地址。其中店名是 全國唯一的，而同一家的販售商店的地址可能不只一處。例如，臺北、臺中和高雄， 都有某一家販售商店。 ⒎養殖場不能把禽畜直接賣給販售商店，販售商店必須去肉品交易市場購買禽畜肉品。 根據以上的敘述，請用實體-關係模型（Entity-Relationship Model）來建立此資料庫， 繪製出其綱目圖（Schema diagram）。（20 分）

以運動健身資訊系統為例，假設有如下的系統需求： ⑴可以查詢會員的基本資料（包括姓名、生日、電話、加入日期、會員等級、所屬 店家（即加入時的店家））及學費繳交狀態（即學費到期日）。 ⑵可以查詢某一特定會員於特定日期時間內的運動資料清單（包括運動日到店運動 的日期時間、店家名稱）。 ⑶可以查詢教練的基本資料（包括姓名、生日、電話、到職日、職等）及受訓資料 （包括日期、地點、受訓內容）。 ⑷可以查詢某一特定教練於特定日期時間內的工作清單，如2017/10/09 早上十點到 十二點AA 分店，下午兩點到六點BB 分店……。 ⑸可以查詢某一店家的店長、電話、地址及註冊的會員。 其中 z 每次受訓活動的日期長短不一、受訓的教練群不同、主題不定但同一次的受訓活 動只會有一個主題、一個日期只會有一種受訓活動。 z 會員可以到不同的店家運動。 z 店長由教練兼任，一家店只會有一位店長，一位教練也只能兼任一個店長。 請為如上的系統設計相關的EER data model，您的data model 中應符合關連式資料庫 的定義及BCNF、註明合適的primary key 及foreign key、資料表關連的maximum 與 minimum cardinality。（25 分）

有一個關聯式資料庫（Relational database），內有下列兩個關聯（Relation），畫有實 底線的屬性（Attribute）為主鍵（Primary key），畫有虛底線的屬性為外來鍵（Foreign key）： 教師(教師編號, 姓名,系別,電話,研究室號碼,專長) 助教(助教編號,姓名,系別,電話,實驗室號碼,年級,教師編號) 試寫出SQL 指令，以查詢出教師編號小於等於50 之教師的姓名、研究室號碼與專 長。（5 分） 試完成下列之SQL 指令，以查詢出教師編號為001 之姓名與系別，以及該教師之 輔佐助教的姓名、系別與電話。（15 分） ________________________________________________ FROM _________________________________________ WHERE _________________________________________

考慮下列關聯資料表： 教務資料(教授姓名, 教授工作證號碼, 研究室編號, 研究室所在建築物, 課程編號, 課程名稱, 教室所在建築物, 教室編號, 修課成績, 學生姓名, 學生證號碼) 該關聯資料表「教務資料」有以下限制： 每位教授的教授工作證號碼是唯一的；每位學生的學生證號碼是唯一的；每門課的課 程編號是唯一的；每間研究室的研究室編號是唯一的；每間教室的教室編號是唯一的。 每位教授都分配有一間且僅有一間研究室，每一間研究室可供給零位以上教授使用。 每門課由一位且僅由一位教授上該門課，每位教授至少上一門課。 每位學生至少修一門課，每門課有零位以上學生修該課程。 每門課只會在一間教室上課，每間教室都可供零門課以上使用。 修課成績指某位學生修某門課的成績。 試依前述限制，描述所有可能的功能相依性（functional dependency）。（10 分） 試問如何以功能相依性的規則，依前述限制來決定出關聯資料表「教務資料」的 主要鍵（primary key）。（10 分） 請判定上述關聯資料表「教務資料」是否有符合第三正規化之要求，如不符合的 話，試問將如何修改使其能符合第三正規化之要求，並標示出修改後每一關聯資 料表的主要鍵（primary key）。（10 分） 試請依據小題已修改成符合第三正規化要求後之關聯資料表，寫一SQL 程式片 段，依學生證號碼由小而大的順序，輸出每一位學生的學生證號碼、學生姓名， 及其所修的每一門課的課程名稱與所獲得的成績。（10 分） 106年公務人員高等考試三級考試試題 全一張 （背面） 類 科：資訊處理 科 目：資料庫應用

一個關連R（A, B, C, D, E），其中A, B, C, D, E 分別代表一個或一組欄位。它們之間 存在以下的關係：{E ⊆ B, C→D, AB→C}。（每小題10 分，共20 分） 問此關連是否符合第二正規化的要求？若是，說明原因。若否，如何作第二正規化？ 問此關連是否符合第三正規化的要求？若是，說明原因。若否，如何作第三正規化？ 106年公務人員特種考試關務人員考試、 106年公務人員特種考試身心障礙人員考試及 106年國軍上校以上軍官轉任公務人員考試試題 全一張 （背面） 考試別： 關務人員考試 等 別： 三等考試 類 科： 資訊處理 科 目： 資料庫應用

假設現有如下寵物就醫資訊的關連式資料庫，請使用SQL 回答下列問題： Pet(PetID, PetName, Sex, OwnerID, OwnerName, TEL, Species, BirthYear, BirthMonth) MedicalRecord(PetID, VetID, Date_Time, DiagnosisContent) FKs: PetID ref. Pet(PetID), VetID ref. Veterinarian(VetID) Veterinarian(VetID, VetName, Specialty) 其中PetID 為P 初診日期_兩碼流水號組成，如P2017110101 VetID 為V 四碼流水號組成，如V0001 定義資料表Veterinarian（定義中應適當表示primary）。（5 分） 請找出BirthYear 為2017 寵物的所有就醫紀錄，結果請顯示PetName, VetName， 就醫的Date_Time 及DiagnosisContent，並依Date_Time 由新至舊進行排序。（5 分） 刪除PetID 為P2017110101 的寵物及其相關的就醫紀錄（多表格資料刪除，請注意 刪除的順序）。（5 分） 106年公務、關務人員升官等考試、106年交通 事業鐵路、公路、港務人員升資考試試題 代號：26230 全一張 （背面） 等 級： 薦任 類科（別）： 資訊處理 科 目： 資料庫應用

在資料庫設計過程中，假設所有Relations 都已處於第一正規形式（First Normal Form），若要使資料庫所有的Relations 都能處於Boyce-Codd Normal Form（BCNF 正 規形式） 則在資料庫設計過程所進行的正規化，可能需要處理這些Relations 的那些資料狀 態？試舉例加以說明。（10 分） 試對前述所舉例，需要處理的資料狀態，進行正規化處理，並列出正規化處理之 結果。（10 分）

一實體關聯圖（ER-diagram）包含有三個實體Project、Employee 及Family。當中： Project 含有三個屬性P-No、P-Name 及DueDate，且P-No 為該實體的主要鍵 （primary key）。 Employee 含有三個屬性Emp#、E-Name 及Office，且Emp#為該實體的主要鍵。 Family 含有兩個屬性Name 及DOB，且Family 為一弱實體（weak entity）。 另Project 與Employee 間有一Assignment 的關聯（relationship），該關聯顯示： 每一Project 被指定給至少有一位以上Employee 在負責。 每一Employee 需負責零個以上Project。 且每一個Assignment都會記錄，負責每一Project的每一位Employee，在該Project 所扮演的角色、參與該Project 的起始日期及結束日期。 Employee 與Family 間也有一Dependent 的關聯。該關聯描述： 每一位Employee 能扶養零位以上Family。 每一位Family 只能登記為某一位Employee 的扶養人，且每一位Family 都必須 為某一位，且只有一位Employee 所扶養。 試以最適當且最少個關聯資料表（relation），來描述上述之實體關聯圖。需寫出該 些關聯資料表的名稱、所含之屬性及主要鍵。（10 分） 對於上述之關聯資料表（relation），於撰寫程式時，那些屬性之間應設定為參考完 整性限制（Referential integrity constraint），那些外來鍵屬性（foreign key）必須設 為不可為空值（Non-Null）。（10 分）

有一軟體應用程式設計公司，專門幫政府各級部門設計應用程式。此公司現有的資 料庫綱目（database schema）如下：（每小題10 分，共40 分） 設計團隊（團隊名稱，負責人姓名，負責人年齡） 政府單位（單位名稱，單位所在地，主管姓名） 軟體系統（系統名，語言，售價，所需開發時間） 執行（設計團隊名稱，政府單位名稱，軟體系統名，安裝日期） 用關連式代數（relational algebra）查詢去年一整年（亦即，20160101 <= 安裝日期 <= 20161231），這家公司為政府那些單位安裝了軟體系統，列出這些政府單位的名 稱，以及所安裝的軟體系統售價。若為同一單位開發了不只一個軟體系統，則列 出這些軟體系統售價的總和。 用關連式代數查詢有那些設計團隊，他們有開發過不低於一千萬元的軟體系統。 列出這些設計團隊的名稱。 用SQL 語法列出所有單位所在地是 “臺北” 的政府單位，或者購置過軟體系統名 是 “資料探勘系統” 的政府單位，列出這些單位的名稱。 針對每一個設計團隊，用SQL 語法列出此團隊名稱，以及它為政府單位所製作過 之軟體系統的平均售價。

請將如下的資料表內容，以符合BCNF 的關連式資料庫資料表來表示。（25 分） 同一航班編號固定飛行相同的路線（出發地與目的地為固定）、一天最多飛行一次、 起降時間可能因天候季節或特殊原因而有不同。 Flight_Seat_Info CID Name Sex FlightNo Dep. Dest. DepD DepT ArrD ArrT SeatNo A123 張三 M BR26 TPE SEA 20170807 2340 20170807 1950 11A A321 李四 BR26 TPE SEA 20170807 2340 20170807 1950 22B A111 張三 BR26 TPE SEA 20171002 2350 20171002 1940 33C A321 李四 BR26 TPE SEA 20171002 2350 20171002 1940 55D A123 張三 CI5235 SEA TPE 20171009 0200 20171010 0540 66E A321 張三 CI5235 SEA TPE 20171009 0200 20171010 0540 11F

在關聯式資料庫（Relational database）裡，資料庫管理者可以create “View”這種東西 給使用者使用： 試問View 是什麼？（5 分） 資料庫使用View 有什麼好處？（15 分）

設X 與Y 為資料庫中的兩個變數，其中X 的原始值為30，Y 的原始值為40。另 P1 與P2 為兩位不同程式，當中P1 將依序執行X = X + Y 及Y = Y－4 兩個指令，同時 P2 也將執行Y = X + Y + 1 的指令。假設該資料庫系統沒有良好同步處理 （synchronization processing）之機制，試問執行後(X, Y)之最後值可能為何，需寫出 您的推導步驟。（10 分）

有一個交易執行程序（Transaction execution schedule），記錄了二筆交易（transactions） 的執行步驟如下：（每小題10 分，共20 分） Time Transaction A Transaction B 1 Begin（A） 2 Begin（B） 3 Read（X） 4 Read（X）

以下圖為例，如果最後一次系統無誤的checkpoint 在時間ti，接著在下一個checkpoint 點時間tj 時，發現系統錯誤，請問在資料庫復原的階段，就進行中的T1, T2, T3, T4, T5 等transactions 而言，那些 transactions 應該作rollback（也就是undo）處理？又那些 transactions 應該作rollforward（也就是re-do）處理？原因為何？（15 分）

在關聯式資料庫（Relational database）系統，使用者可以對Relation 的Attribute 建 index。試詳細說明： 建index 可以獲得那些好處？（10 分） 建index 需要付出那些代價？（10 分） 106年公務人員特種考試警察人員、一般警察 人員考試及106年特種考試交通事業鐵路 人員、退除役軍人轉任公務人員考試試題 全一張 （背面） 考試別： 鐵路人員考試 等 別： 高員三級考試 類科別： 資訊處理 科 目： 資料庫應用

有關資料庫中交易（transaction）管理之永久性（Durability, Permanency）特性，為一 旦交易全部執行，且經過確認（Commit）後，即使未來發生系統當機或毀損，其對 資料庫所做的變更則永遠有效。為能從各種故障回復，當交易進行中，系統常以維 護一個日誌（Log），來提供交易錯誤或故障時，所需的復原資訊。若交易被正常完 成時，資料庫管理系統（database management system）會進行交易Commit 動作，否 則將對此交易進行Rollback 動作。試問資料庫管理系統將如何動作，即便是資料庫 管理系統正處理日誌（Log）資料時，系統發生故障了，也能確保上述交易管理之永 久性。（6 分）當系統故障時，系統如何處理？（4 分）

X = X + 5

請說明在交易資料的處理中，何謂dirty read problem？可以用什麼方式加以解決？ （10 分）  請說明如下的DBMS，那些是SQL based，那些是NoSQL based DBMS。（10 分） Cassandra, DB2, HBase, Hive, MariaDB, MongoDB, MySQL, Oracle DB, PostgreSQL, Redis system break down checkpoint successful checkpoint not commit yet commit commit not commit yet commit T1 T4 T5 T3 T2 ti tj Time Line

有一個關聯式資料庫（Relational database），其內有下列之關聯（Relation），關聯名 稱為TEST，主鍵（Primary key）為ID。試問這個資料在使用上，有怎樣的問題或困 擾存在？（20 分） TEST ID Name Class Score Dept 01 N1 211 85 07 N2 312 90 01 05 N7 325 70 02 07 N5 512 60 09 15 N3 217 03

請從每筆資料的長度大小之差異大否，整個檔案儲存資料總量龐大否，需常進行搜 尋資料動作否，須常進行更新資料動作否等因素，從搜尋速度及使用記憶體空間方 面考量，來探討採用循序檔案搜尋法、直接檔案搜尋法及雜湊搜尋（hashing search） 法之適用性，並說明原因。（20 分）

Write（X）

End（A）

Write（X） 9 End（B） 請問這執行會導致怎樣的資料更新問題（update anomaly），並說明問題是在那個時 間點，如何發生的？ 什麼是二階段鎖定協定（Two-Phase Locking Protocol）？若使用二階段鎖定協定， 會怎樣執行這二個交易？