下半年數(shù)據(jù)庫系統(tǒng)工程師下午試卷

1、1.試題一（15分） 閱讀下列說明和數(shù)據(jù)流圖，回答問題1至問題3，將解答填入答題紙的對應欄內(nèi)?！菊f明】 某圖書管理系統(tǒng)的主要功能是圖書管理和信息查詢。對于初次借書的讀者，系統(tǒng)自動生成讀者號，并與讀者基本信息（姓名、單位、地址等）一起寫入讀者文件。 系統(tǒng)的圖書管理功能分為四個方面：購入新書、讀者借書、讀者還書以及圖書注銷。 1.購入新書時需要為該書編制入庫單。入庫單內(nèi)容包括圖書分類目錄號、書名、作者、價格、數(shù)量和購書日期，將這些信息寫入圖書目錄文件并修改文件中的庫存總量（表示到目前為止，購入此種圖書的數(shù)量）。 2.讀者借書時需填寫借書單。借書單內(nèi)容包括讀者號和所借圖書分類目錄號。系統(tǒng)首先檢查該讀

2、者號是否有效，若無效，則拒絕借書；若有效，則進一步檢查該讀者已借圖書是否超過最大限制數(shù)（假設每位讀者能同時借閱的書不超過5本），若已達到最大限制數(shù)，則拒絕借書；否則允許借書，同時將圖書分類目錄號、讀者號和借閱日期等信息寫入借書文件中。 3.讀者還書時需填寫還書單。系統(tǒng)根據(jù)讀者號和圖書分類目錄號，從借書文件中讀出與該圖書相關的借閱記錄，標明還書日期，再寫回到借書文件中，若圖書逾期，則處以相應的罰款。 4.注銷圖書時，需填寫注銷單并修改圖書目錄文件中的庫存總量。 系統(tǒng)的信息查詢功能主要包括讀者信息查詢和圖書信息查詢。其中讀者信息查詢可得到讀者的基本信息以及讀者借閱圖書的情況；圖書信息查詢可得到圖書

3、基本信息和圖書的借出情況。 圖書管理系統(tǒng)的頂層圖如圖1-1所示；圖書管理系統(tǒng)的第0層DFD圖如圖1-2所示，其中，加工2的細化圖如圖1-3所示?！緮?shù)據(jù)流圖1-1】【數(shù)據(jù)流圖1-2】【數(shù)據(jù)流圖1-3】【問題1】（2分） 數(shù)據(jù)流圖1-2中有兩條數(shù)據(jù)流是錯誤的，請指出這兩條數(shù)據(jù)流的起點和終點?！締栴}2】（6分） 數(shù)據(jù)流圖1-3中缺少三條數(shù)據(jù)流，請指出這三條數(shù)據(jù)流的起點和終點?！締栴}3】（7分） 根據(jù)系統(tǒng)功能和數(shù)據(jù)流圖填充下列數(shù)據(jù)字典條目中的（1）和（2）： 查詢請求信息=查詢讀者請求信息| 查詢圖書請求信息 讀者情況=讀者號姓名所在單位借書情況 管理工作請求單= （1） 入庫單= （2）15分參考答

4、案：問題1 起點：讀者文件 終點；登記讀者信息或3 起點：處理查詢請求 或2 終點：讀者文件問題2 起點；圖書目錄文件 起點：借書文件 起點；借書文件問題3 (1)入庫單借書單還書單注銷單 (2)分類目錄號+書名+作者+價格+數(shù)量+購書日期2.試題二（20分） 閱讀下列說明，回答問題1至問題5，將解答填入答題紙的對應欄內(nèi)?！菊f明】 某工廠的信息管理數(shù)據(jù)庫的部分關系模式如下所示： 職工（職工號，姓名，年齡，月工資，部門號，電話，辦公室） 部門（部門號，部門名，負責人代碼，任職時間） 關系模式的主要屬性、含義及約束如表2-1所示，“職工”和“部門”的關系示例分別如表2-2和表2-3所示?！締栴}1】

5、根據(jù)上述說明，由SQL定義的“職工”和“部門”的關系模式，以及統(tǒng)計各部門的人數(shù)C、工資總數(shù)Totals、平均工資Averages的D_S視圖如下所示，請在空缺處填入正確的內(nèi)容。（6分）Create Table部門( 部門號 CHAR(1) （a） , 部門名 CHAR(16), 負責人代碼 CHAR(4), 任職時間 DATE, （b）(職工號); Create Table 職工( 職工號 CHAR(4), 姓名 CHAR(8), 年齡 NUMBER(3), 月工資 NUMBER(4), 部門號 CHAR(1), 電話 CHAR(8), 辦公室 CHAR(8), (a)(職工號), (b)(部

6、門號), CHECK ( （d） ); Create View D_S(D,C,Totals,Averages) As ( Select 部門號, （e） from 職工（f）【問題2】對于表2-2、表2-3所示的“職工”和“部門”關系，請指出下列各行是否可以插入，為什么？（3分）【問題3】在問題1定義的視圖D_S上，下面哪個查詢或更新是允許執(zhí)行的，為什么？（3分） （1）Update D_S set D=3 where D=4; （2）Delete from D_S where C4; （3）Select D, Averages from D_S where C(Select C from

7、D_S where D=:dept); （4）Select D,C From D_S where Totals10000; （5）Select * from D_S;【問題4】查詢每個部門中月工資最高的“職工號”的SQL查詢語句如下： Select 職工號 from 職工 E where 月工資=（Select Max(月工資) from 職工 as M where M. 部門號=E. 部門號） （1）請用30字以內(nèi)文字簡要說明該查詢語句對查詢效率的影響。（3分） （2）對該查詢語句進行修改，使它既可以完成相同功能，又可以提高查詢效率。（3分）【問題5】假定分別在“職工”關系中的“年齡”和“月

8、工資”字段上創(chuàng)建了索引，如下的Select查詢語句可能不會促使查詢優(yōu)化器使用索引，從而降低查詢效率，請寫出既可以完成相同功能又可以提高查詢效率的SQL語句。（2分） Select 姓名,年齡,月工資 from 職工 where 年齡45 or 月工資=500 AND 月工資45； union Select 姓名，年齡，月工資 from 職工 where 年齡 月工資1000；3.試題三（20分） 閱讀下列說明，回答問題1至問題5，將解答填入答題紙的對應欄內(nèi)?！菊f明】 某倉儲超市采用POS（Point of Sale）收銀機負責前臺的銷售收款，為及時掌握銷售信息，并依此指導進貨，擬建立商品進、銷

9、、存數(shù)據(jù)庫管理系統(tǒng)。該系統(tǒng)的需求分析已經(jīng)基本完成，下面將進入概念模型的設計?！拘枨蠓治鼋Y果】 1. 銷售業(yè)務由POS收銀機來輔助實現(xiàn)。POS機外接條碼閱讀器，結帳時收銀員將商品的條碼通過閱讀器輸入POS機中。所售商品數(shù)量默認值為1，可以由收銀員修改。POS機根據(jù)輸入的商品信息，打印出圖3-1所示的購物清單。 2. 將經(jīng)銷的商品分為直銷商品和庫存商品兩大類。直銷商品的保質(zhì)期較短，如食品類，由供應商直接送達超市，管理員將過期的商品返還給供應商處理；庫存商品由采購員向供應商提交訂購單，供應商根據(jù)訂購單送貨。超市會不定期對庫存商品按照折扣率進行打折優(yōu)惠。 直銷商品和庫存商品的送貨單樣表分別如圖3-2、

10、圖3-3所示，其中直銷商品生產(chǎn)批號的前六位表示生產(chǎn)日期。 3超市的硬件拓撲結構如圖3-4所示。 4業(yè)務處理過程：由POS機存儲每一筆銷售記錄，在每個工作日結束前匯總當日各商品的銷售量至中心數(shù)據(jù)庫（銷售日匯總）；根據(jù)當日的銷售日匯總更新存貨表；每筆進貨記入進貨表中，并及時更新存貨表?！靖拍钅Ｐ驮O計】根據(jù)需求階段收集的信息，設計的實體聯(lián)系圖和關系模式（不完整）如下：1實體聯(lián)系圖2關系模式 銷售詳單（銷售流水號，商品編碼，數(shù)量，金額，收銀員，時間） 銷售日匯總（日期，商品編碼，數(shù)量） 存貨表（商品編碼，數(shù)量） 進貨表（送貨號碼，商品編碼，數(shù)量，日期） 商品（ （b） ）【問題1】 對直銷商品和庫存商

11、品進行概括，給出超類和子類，填入圖3-5中（a）處所示的虛線框內(nèi)，并補充聯(lián)系。（5分）【問題2】 根據(jù)你的實體聯(lián)系圖，完成（b）處的商品關系模式，并增加子類型的實體關系模式。（3分）【問題3】 對所有關系模式，以下劃線指出各關系模式的主鍵。（4分）【問題4】 如果將商品信息只存儲在中心數(shù)據(jù)庫中，與在各POS機上存儲其備份相比，從前臺銷售效率和更新商品庫兩方面論述各自的優(yōu)缺點（不超過300字）。（4分）【問題5】 如果考慮引入積分卡，根據(jù)累積消費金額計算積分點，再根據(jù)積分點在顧客購物時進行現(xiàn)金返還，并修改顧客的累積消費金額和積分點。請給出新增加的積分卡關系模式，并對銷售詳單關系模式進行修正，指出

12、修正后關系模式和新增關系模式的候選鍵和外鍵。（4分）20分參考答案：4.試題四（20分） 閱讀下列說明，回答問題1至問題3，將解答填入答題紙的對應欄內(nèi)?！菊f明】 M公司為某旅游公司設計機票銷售專用數(shù)據(jù)庫，其關系模式如圖4-1所示。 圖4-1機票銷售專用數(shù)據(jù)庫 關系模式的主要屬性、含義及約束如表4-1所示，屬性間的函數(shù)依賴關系如圖4-2所示，屬性間函數(shù)依賴的標記方法如圖4-3所示。 表4-1 主要屬性、含義及約束 旅客旅行前需要向旅行社提出申請，說明要參加的旅行團隊。旅行社建立的旅行申請包括，旅行出發(fā)日期和到達日期的機票預訂、購票等信息。旅行社還需要為每個團隊制定“旅程”和“搭乘航班”表。有關“

13、旅程”和“搭乘航班”的示例如表4-2、表4-3所示?！締栴}1】對關系“航班”，請回答以下問題：（6分） （1）列舉出所有不屬于任何候選鍵的屬性（非鍵屬性）。 （2）關系“航班”可達到第幾范式，用不超過60個字的內(nèi)容敘述理由?！締栴}2】對關系“旅客”，請回答以下的問題：（6分） （1）針對“旅客”關系，用100字以內(nèi)文字簡要說明會產(chǎn)生什么問題，并加以修正。 （2）列出修正后的關系模式的所有候選鍵。 （3）把“旅客”分解為第三范式，并用圖4-1所示的關系模式的形式表示，分解后的關系名依次取旅客1、旅客2、?！締栴}3】對關系“搭乘航班”，請回答以下的問題：（8分） （1）把非平凡的多值依賴屬性（圖4

14、-2中沒有表示）的例子用滿足圖4-3的方式表示出來。 （2）關系“搭乘航班”是boyce codd范式而不是第四范式，請用200字以內(nèi)文字闡述理由。 （3）把“搭乘航班”關系分解成第四范式，并采用圖4-1所示的關系模式的形式表示，分解后的關系名依次取搭乘航班1、搭乘航班2、。20分參考答案：問題1 (1)“航班”關系模式的候選鍵為(航班名，飛行日期)，非鍵屬性為；航空公司名稱，出發(fā)地點，出發(fā)時間，目的地，到達時間。 (2)“航班”是屬于1NF的。因為非主屬性航空公司名稱，出發(fā)地點，目的地不完全函數(shù)依賴于候選鍵(航班名，飛行日期)。 該關系模式存在如下函數(shù)依賴： 航班名航空公司名稱，出發(fā)地點，目的地； (航班名，飛行日期)出發(fā)時間，到達時間。問題2 1(1)在題中給出的“旅客”關系中，不同的團隊會有相同的旅客編號，所以，旅客編號不能作為候選鍵