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

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

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

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

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

5、根據(jù)上述說明，由SQL定義的“職工”和“部門”的關(guān)系模式，以及統(tǒng)計(jì)各部門的人數(shù)C、工資總數(shù)Totals、平均工資Averages的D_S視圖如下所示，請(qǐng)?jiān)诳杖碧幪钊胝_的內(nèi)容。（6分）Create Table部門( 部門號(hào) CHAR(1) （a） , 部門名 CHAR(16), 負(fù)責(zé)人代碼 CHAR(4), 任職時(shí)間 DATE, （b）(職工號(hào)); Create Table 職工( 職工號(hào) CHAR(4), 姓名 CHAR(8), 年齡 NUMBER(3), 月工資 NUMBER(4), 部門號(hào) CHAR(1), 電話 CHAR(8), 辦公室 CHAR(8), (a)(職工號(hào)), (b)(部

6、門號(hào)), CHECK ( （d） ); Create View D_S(D,C,Totals,Averages) As ( Select 部門號(hào), （e） from 職工（f）【問題2】對(duì)于表2-2、表2-3所示的“職工”和“部門”關(guān)系，請(qǐng)指出下列各行是否可以插入，為什么？（3分）【問題3】在問題1定義的視圖D_S上，下面哪個(gè)查詢或更新是允許執(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】查詢每個(gè)部門中月工資最高的“職工號(hào)”的SQL查詢語句如下： Select 職工號(hào) from 職工 E where 月工資=（Select Max(月工資) from 職工 as M where M. 部門號(hào)=E. 部門號(hào)） （1）請(qǐng)用30字以內(nèi)文字簡(jiǎn)要說明該查詢語句對(duì)查詢效率的影響。（3分） （2）對(duì)該查詢語句進(jìn)行修改，使它既可以完成相同功能，又可以提高查詢效率。（3分）【問題5】假定分別在“職工”關(guān)系中的“年齡”和“月

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

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

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

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

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

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

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