20142014 年上半年數據庫系統工程師考試下午真題 標準參考答案 年上半年數據庫系統工程師考試下午真題 標準參考答案 1 閱讀下列說明和圖 回答問題 1 至問題 4 將解答填入答題紙的對應欄內 說明 某巴士維修連鎖公司欲開發巴士維修系統 以維護與維修相關的信息 該系統的 主要功能 如下 1 記錄巴士 ID 和維修問題 巴士到車庫進行維修 系統將巴士基本信息和 ID 記錄在巴士 列表文件中 將待維修機械問題記錄在維修記錄文件中 并生成維修訂單 2 確定所需部件 根據維修訂單確定維修所需部件 并在部件清單中進行標記 3 完成維修 機械師根據維修記錄文件中的待維修機械問題 完成對巴士的維修 登記維 修情況 將機械問題維修情況記錄在維修記錄文件中 將所用部件記錄在部件 清單中 并 將所用部件清單發送給庫存管理系統以對部件使用情況進行監控 巴士司機可查看已維修 機械問題 4 記錄維修工時 將機械師提供的維修工時記錄在人事檔案中 將維修總結發送 給主管進 行績效考核 5 計算維修總成本 計算部件清單中實際所用部件 人事檔案中所用維修工時的 總成本 將維修工時和所用部件成本詳細信息給會計進行計費 現采用結構化方法對巴士維修系統進行分析與設計 獲得如圖 1 1 所示的上下文 數據流圖 和圖 1 2 所示的 0 層數據流圖 問題 1 5 分 使用說明中的詞語 給出圖 1 1 中的實體 E1 E5 的名稱 問題 2 4 分 使用說明中的詞語 給出圖 1 2 中的數據存儲 D1 D4 的名稱 問題 3 3 分 說明圖 1 2 中所存在的問題 問題 4 3 分 根據說明和圖中術語 釆用補充數據流的方式 改正圖 1 2 中的問題 要求給出 所補充數 據流的名稱 起點和終點 參考答案 問題 1 5 分 E1 巴士司機 E2 機械師 E3 會計 E4 主管 E5 庫存管理系統 問題 2 4 分 D1 巴士列表文件 D2 維修記錄文件 D3 部件清單 D4 人事檔案 問題 3 3 分 缺失以下數據流 1 名稱 待維修機械問題 起點 D2 終點 3 完成維修 2 名稱 實際所用部件 起點 D3 終點 5 計算總成本 問題 4 3 分 補充以下數據流 1 名稱 待維修機械問題 起點 D2 終點 3 完成維修 2 名稱 實際所用部件 起點 D3 終點 5 計算總成本 試題分析 根據第 3 點 巴士司機可查看已維修機械問題 可知 E1 為巴士司機 根據第 3 點 機械 師根據維修記錄文件中的待維修機械問題 完成對巴士的維修 登記維修情況 可知 E2 為機械師 根據第 5 點 將維修工時和所用部件成本詳細信息給會計進行計費 可知 E3 為會計 根據第 4 點 將機械師提供的維修工時記錄在人事檔案中 將維修總結發送 給主 管進行績效考核 可知 E4 為主管 根據第 3 點 將所用部件清單發送給庫存管理系統以 對部件使用情況進行監控 可知 E5 為庫存管理系統 根據第 1 點 系統將巴士基本信息和 ID 記錄在巴士列表文件中 可知 D1 為巴士列表文件 根據第 1 點 將待維修機械問題記錄在維修記錄文件中 并生成維修訂單 可知 D2 為維 修記錄文件 根據第 2 點 根據維修訂單確定維修所需部件 并在部件清單中進行標記 可知 D3 為部件清單 根據第 4 點 將機械師提供的維修工時記錄在人事檔案中 可知 D4 為人事檔案 根據系統主要功能及 0 層數據流圖 不難得知缺失的數據流 2 閱讀下列說明 回答問題 1 至問題 3 將解答填入答題紙的對應欄內 說明 某健身俱樂部要開發一個信息管理系統 該信息系統的部分關系模式如下 員工 員工身份證號 姓名 工種 電話 住址 會貝 會員手機號 姓名 折扣 項目 項目名稱 項目經理 價格 預約單 會員手機號 預約曰期 項目名稱 使用時長 消費 流水號 會員手機號 項目名稱 消費金額 消費日期 有關關系模式的屬性及相關說明如下 1 俱樂部有多種健身項目 不同的項目每小時的價格不同 俱樂部實行會員制 且需要電 話或在線提前預約 2 每個項目都有一個項目經理 一個經理只能負責一個項目 3 俱樂部對會員進行積分 達到一定積分可以進行升級 不同的等級具有不同的折扣 根據以上描述 回答下列問題 問題 1 請將下面創建消費關系的 SQL 語句的空缺部分補充完整 要求指定關系的主碼 外碼 以 及消費金額大于零的約束 CREATE TABLE 消費 流水號 CHAR 12 a PRIMARY KEY 會員手機號 CHAR 11 項目名稱 CHAR 8 消費金額 NUMBER b CHECK 消費金額 0 消費日期 DATE c FOREIGN KEY 會員手機號 REFERENCES 會員 會員手機號 d FOREIGN KEY 項目名稱 REFERENCES 項目 項目名稱 問題 2 1 手機號為的客戶預約了 2014 年 3 月 18 日兩個小時的羽毛球場地 消費流水號由系統自 動生成 請將下面 SQL 語句的空缺部分補充完整 INSERT into 消費 流水號 會員手機號 項目名稱 消費金額 消費日期 SELECT 1 羽毛球 e 項目 價格 會員 折扣 2014 3 18 FROM 會員 項目 預約單 WHERE 預約單 項目名稱 項目 項目名稱 AND f 預約單 會員手機號 會員 會員手機號 AND 項目 項目名稱 羽毛球 AND 會員 會員手機號 2 需要用觸發器來實現會員等級折扣的自動維護 函數 float vip value char 11 會員 手機號 依據輸入的手機號計算會員的折扣 請將下面 SQL 語句的空缺部分補充 完整 CREATE TRIGGER VIP TRG AFTER g UPDATE 0N h 會員 REFERENCING new row AS nrow FOR EACH ROW BEGIN UPDATE 會員 SET i 折扣 vip value nrow 會員手機號 WHERE j 會員手機號 nrow 會員手機號 END 問題 3 請將下面 SQL 語句的空缺部分補充完整 1 俱樂部年底對各種項目進行績效考核 需要統計出所負責項目的消費總金額 大于等于十 萬元的項目和項目經理 并按消費金額總和降序輸出 SELECT 項目 項目名稱 項目經理 SUM 消費金額 FROM 項目 消費 WHERE k 項目 項目名 消費 項目名 GROUP BY l 項目名 HAVING SUM 消費金額 ORDER BY m SUM 消費金額 DESC 查詢所有手機號碼以 888 結尾 姓 王 的員工姓名和電話 SELECT 姓名 電話 FROM 員工 WHERE 姓名 n LIKE 王 AND 電話 o LIKE 888 3 閱讀下列說明 回答問題 1 至問題 3 將解答填入答題紙的對應欄內 說明 某圖書館的管理系統部分需求和設計結果描述如下 圖書館的主要業務包括以下幾項 1 對所有圖書進行編目 每一書目包括 ISBN 號 書名 出版社 作者 排名 其中一部 書可以有多名作者 每名作者有唯一的一個排名 2 對每本圖書進行編號 包括書號 ISBN 號 書名 出版社 破損情況 存 放位置和定 價 其中每一本書有唯一的編號 相同 ISBN 號的書集中存放 有相同的 存儲位置 相同 ISBN 號的書或因不同印刷批次而定價不同 3 讀者向圖書館申請借閱資格 辦理借書證 以后憑借書證從圖書館借閱圖書 辦理借 書證時需登記身份證號 姓名 性別 出生年月日 并交納指定金額的押金 如果所借圖 書定價較高時 讀者還須補交押金 還書后可退還所補交的押金 4 讀者借閱圖書前 可以通過 ISBN 號 書名或作者等單一條件或多條件組合 進行查詢 根據查詢結果 當有圖書在庫時 讀者可直接借閱 當所查書目的所有圖 書己被他人借走 時 讀者可進行預約 待他人還書后 由館員進行電話通知 5 讀者借書時 由系統生成本次借書的唯一流水號 并登記借書證號 書號 借書日期 其中同時借多本書使用同一流水號 每種書目都有一個允許一次借閱的借 書時長 一般為 90 天 不同書目有不同的借書時長 并且可以進行調整 但調整前所 借出的書 仍按原 借書時長進行處理 6 讀者還書時 要登記還書日期 如果超出借書時長 要繳納相應的罰款 如果所還圖書 由借書者在持有期間造成破損 也要進行登記并進行相應的罰款處罰 初步設計的該圖書館管理系統 其關系模式如圖 4 1 所示 問題 1 對關系 借還 請回答以下問題 1 列舉出所有候選鍵 2 根據需求描述 借還關系能否實現對超出借書時長的情況進行正確判定 用 60 字以內 文字簡要敘述理由 如果不能 請給出修改后的關系模式 只修改相關關系 模式屬注時 仍使用原關系名 如需分解關系模式 請在原關系名后加 1 2 等進 行區別 問題 2 對關系 圖書 請回答以下問題 1 寫出該關系的函數依賴集 2 判定該關系是否屬于 BCNF 用 60 字以內文字簡要敘述理由 如果不是 請 進行修改 使其滿足 BCNF 如果需要修改其它關系模式 請一并修改 給出修改后 的關系模式 只修 改相關關系模式屬性時 仍使用原關系名 如需分解關系模式 請 在原關系名后加 1 2 等進行區別 問題 3 對關系 書目 請回答以下問題 1 它是否屬于第四范式 用 60 字以內文字敘述理由 2 如果不是 將其分解為第四范式 分解后的關系名依次為 書目 1 書目 2 如果 在解決 問題 1 問題 2 時 對該關系的屬性進行了修改 請沿用修改后的屬性 參考答案 問題 1 1 流水號 書號 借書證號 書號 借書日期 2 不能 原因是 當更新借書時長時 就會導致原來借書使用的時長丟失 并采用最新 更新的時長 借還 1 流水號 書號 借書證號 借書日期 應還日期 借還 2 流水號 書號 還書日期 借還 3 流水號 書號 罰款金額 罰款原因 問題 2 1 書號 ISBN 號 書名 出版社 破壞情況 存放位置 定價 ISBN 號 書名 出版社 存放位置 2 不屬于 原因是 存在傳遞依賴 書目 ISBN 號 書名 出版社 作者 排名 存放位置 借書時長 圖書 1 書號 ISBN 號 價格 問題 3 1 否 存在多值依賴 ISBN 號 作者 書目 1 ISBN 號 書名 出版社 存放位置 借書時長 書目 2 ISBN 號 作者 排名 4 閱讀下列說明 回答問題 1 至問題 3 將解答填入答題紙的對應欄內 說明 某高速路不停車收費系統 ETC 的業務描述如下 1 車輛駛入高速路入口站點時 將駛入信息 ETC 卡號 入口編號 駛入時間 寫入登 記表 2 車輛駛出高速路出口站點 收費口 時 將駛出信息 ETC 卡號 出口編號 駛出時 間 寫入登記表 根據入口編號 出口編號及相關收費標準 清算應繳費用 并從綁定的 信用卡中扣除費用 一張 ETC 卡號只能綁定一張信用卡號 針對企業用戶 一張信用卡號 可以綁定多 個 ETC 卡號 使用表綁定 ETC 卡號 信用卡號 來描述綁定關系 從信用卡 信 用卡號 余額 表中扣除費用 針對上述業務描述 完成下列問題 問題 1 在不修改登記表的表結構和保留該表歷史信息的前提下 當車輛駛入時 如何保 證當前 ETC 卡已經清算過 而在駛出時又如何保證該卡已駛入而未駛出 請用 100 字以內文字簡 述處理方案 問題 2 當車輛駛出收費口時 從綁定信用卡余額中扣除費用的偽指令如下 讀取信用卡 余額到變 量 X 記為 x R A 扣除費用指令 x x a 寫信用卡余額指令記為 W A x 1 當兩個綁定到同一信用卡號的車輛同時經過收費口時 可能的指令執行序 列為 xl R A xl xl al x2 R A x2 x2 a2 W A xl W A x2 此時會出現什么問 題 100 字以內 2 為了解決上述問題 弓丨入獨占鎖指令 XLock A 對數據 A 進行加鎖 解鎖指 令 Unlock A 對數據 A 進行解鎖 請補充上述執行序列 使其滿足 2PL 協議 問題 3 下面是用 E SQL 實現的費用扣除業務程序的一部分 請補全空缺處的代碼 CREATE PROCEDURE 扣除 IN ETC 卡號 VARCHAR 20 IN 費用 FLOAT BEGIN UPDATE 信用卡 SET 余額 余額一 費用 FROM 信用卡 綁定 WHERE 信用卡 信用卡號 綁定 信用卡號 AND a if error then ROLLBACK else b END 參考答案 問題 1 新增一個收費記錄表 ETC 卡 駛入時間 費用 狀態 狀態為 已繳 未繳 當駛入時判斷收費記錄表的狀態是否為已繳 當駛出時查詢是否有該 ETC 卡未繳費的記錄 問題 2 出現丟失更新 問題 3 a 綁定 ETC 卡號 ETC 卡號 b COMMIT 5 閱讀下列說明和圖 回答問題 1 至問題 3 將解答填入答題紙的對應欄內 說明 某家電銷售電子商務公司擬開發一套信息管理系統 以方便對公司的員工 家電銷售 家 電廠商和客戶等進行管理 需求分析 1 系統需要維護電子商務公司的員工信息 客戶信息 家電信息和家電廠商信息等 員 工信息主要包括 工號 姓名 性別 崗位 身份證號 電話 住址 其中崗位包括部門 經理和客服等 客戶信息主要包括 客戶 ID 姓名 身份證號 電話 住址 賬戶余額 家電信息主要包括 家電條碼 家電名稱 價格 出廠日期 所屬廠商 家電廠商信息包 括 廠商 ID 廠商名稱 電話 法人代表信息 廠址 2 電子商務公司根據銷售情況 由部門經理向家電廠商訂購各類家電 每個家電廠商只 能由一名部門經理負責 3 客戶通過瀏覽電子商務公司網站查詢家電信息 與客服溝通獲得優惠后 在線購買 概念模型設計 根據需求階段收集的信息 設計的實體聯系圖 不完整 如圖 2 1 所示 邏輯結構設計 根據概念模型設計階段完成的實體聯系圖 得出如下關系模式 不完整 客戶 客戶 ID 姓名 身份證號 電話 住址 賬戶余額 員工 工號 姓名 性別 崗位 身份證號 電話 住址 家電 家電條碼 家電名稱 價格 出廠日