2018年下半年下午(案例分析)-數(shù)據(jù)庫(kù)系統(tǒng)工程師(考試真題)

1、2018年下半年-數(shù)據(jù)庫(kù)系統(tǒng)工程師(考試真題及答案)案例分析2018年下半年下午(案例分析)-數(shù)據(jù)庫(kù)系統(tǒng)工程師(考試真題)【案例一】：閱讀下列說(shuō)明和圖，回答問(wèn)題1至問(wèn)題4,將解答填入答題紙的對(duì)應(yīng)欄內(nèi)。說(shuō)明某醫(yī)療護(hù)理機(jī)構(gòu)為老年人或有護(hù)理需求者提供專業(yè)護(hù)理?，F(xiàn)欲開發(fā)一基 于Web的醫(yī)療管理系統(tǒng)，以改善醫(yī)療護(hù)理效率。該系統(tǒng)的主要功能如下：(1)通用信息查詢。客戶提交通用信息查詢請(qǐng)求，查詢通用信息表，返回 查詢結(jié)果。(2)醫(yī)生聘用。醫(yī)生提出應(yīng)聘，辭職申請(qǐng)，交由主管進(jìn)行聘用)解聘審批， 更新醫(yī)生表并給醫(yī)生反饋聘用)解聘結(jié)果；刪除解聘醫(yī)生的出診安排。(3)預(yù)約處理。醫(yī)生安排出診時(shí)間，存入醫(yī)生出診時(shí)間表 ；

2、根據(jù)客戶提交 的預(yù)約查詢請(qǐng)求，查詢?cè)诼氠t(yī)生及其出診時(shí)間等預(yù)約所需數(shù)據(jù)并返回；創(chuàng)建預(yù)約， 提交預(yù)約請(qǐng)求，在預(yù)約表中新增預(yù)約記錄，更新所約醫(yī)生出診時(shí)間并給醫(yī)生發(fā)送 預(yù)約通知；給客戶反饋預(yù)約結(jié)果。(4)藥品管理。醫(yī)生提交處方，根據(jù)藥品名稱從藥品數(shù)據(jù)中查詢相關(guān)藥品 庫(kù)存信息，開出藥品，更新對(duì)應(yīng)藥品的庫(kù)存以及預(yù)約表中的治療信息；給醫(yī)生發(fā)送藥品已開出”反饋。(5)報(bào)告創(chuàng)建。根據(jù)主管提交的報(bào)表查詢請(qǐng)求(報(bào)表類型和時(shí)間段)，從預(yù) 約數(shù)據(jù)、通用信息、藥品庫(kù)存數(shù)據(jù)、醫(yī)生以及醫(yī)生出診時(shí)間中進(jìn)行查詢，生成報(bào) 表返回給主管?，F(xiàn)采用結(jié)構(gòu)化方法對(duì)醫(yī)療管理系統(tǒng)進(jìn)行分析與設(shè)計(jì)，獲得如圖1-1所示的,上下文數(shù)據(jù)流圖和圖1-2所示

3、的0層數(shù)據(jù)流圖。問(wèn)題1.1 （3分）使用說(shuō)明中的詞語(yǔ)，給出圖1-1中的實(shí)體E1 E3的名稱問(wèn)題1.2 （5分）使用說(shuō)明中的詞語(yǔ)，給出圖1-2中的數(shù)據(jù)存儲(chǔ)D1 D5的名稱。問(wèn)題1.3 （4分）使用說(shuō)明和圖中術(shù)語(yǔ)，補(bǔ)充圖1-2中缺失的數(shù)據(jù)流及其起點(diǎn)和終點(diǎn)。問(wèn)題1.4 （3分）使用說(shuō)明中的詞語(yǔ)，說(shuō)明“預(yù)約處理”可以分解為哪些子加工 ？并說(shuō)明建 模圖1-1和圖1-2時(shí)如何保持?jǐn)?shù)據(jù)流圖平衡？【案例二】：閱讀下列說(shuō)明，回答問(wèn)題1至問(wèn)題3,將解答填入答題紙的對(duì)應(yīng)欄內(nèi)。說(shuō) 明某海外代購(gòu)公司，為擴(kuò)展公司業(yè)務(wù)，需要開發(fā)一一個(gè)信息化管理系統(tǒng)。請(qǐng) 根據(jù)公司現(xiàn)有業(yè)務(wù)及需求完成該系統(tǒng)的數(shù)據(jù)庫(kù)設(shè)計(jì)。需求描述（1）記錄公司員

4、工信息。員工信息包括工號(hào)、身份證號(hào)、姓名、性別和一 個(gè)手機(jī)號(hào)，工號(hào)唯一標(biāo)識(shí)每位員工，員工分為代購(gòu)員和配送員。（2）記錄采購(gòu)的商品信息。商品信息包括商品名稱、所在超市名稱、采購(gòu) 價(jià)格、銷售價(jià)格和商品介紹，系統(tǒng)內(nèi)部用商品條碼唯一標(biāo)識(shí)每種商品。一種商品 只在一家超市代購(gòu)。（3）記錄顧客信息。顧客信息包括顧客真實(shí)姓名、身份證號(hào)（清關(guān)繳稅用）、 一個(gè)手機(jī)號(hào)。和一個(gè)收貨地址，系統(tǒng)自動(dòng)生成唯一 -的顧客編號(hào)（4）記錄托運(yùn)公司信息。托運(yùn)公同信息包括托運(yùn)公司名稱、電話和地址， 系統(tǒng)自動(dòng)生成唯一的托運(yùn)公司編號(hào)。（5）顧客登錄系統(tǒng)之后，可以下訂單購(gòu)買商品。訂單支付成功后，系統(tǒng)記 錄唯一-的支付憑證編號(hào)。顧客需要在訂

5、單里指定運(yùn)送方式 ：空運(yùn)或海運(yùn)。（6）代購(gòu)員根據(jù)顧客的訂單在超市采購(gòu)對(duì)應(yīng)商品，一份訂單所含的多個(gè)商 品可能由多名代購(gòu)員從不同超市采購(gòu)。（7）采購(gòu)?fù)甑纳唐方挥膳渌蛦T根據(jù)顧客訂單組合裝箱，然后交給托運(yùn)公司 運(yùn)送。托運(yùn)公司按顧客訂單核對(duì)商品名稱和數(shù)量，然后按顧客的地址進(jìn)行運(yùn)送。概念模型設(shè)計(jì)根據(jù)需求階段收集的信息，設(shè)計(jì)的實(shí)體聯(lián)系圖（不完整）如圖2-1所示 邏輯結(jié)構(gòu)設(shè)計(jì)根據(jù)概念模型設(shè)計(jì)階段完成的實(shí)體聯(lián)系圖，得出如下關(guān)系模式（不完整）:員工（u工號(hào)/u,身份號(hào)，姓名，性別，手機(jī)號(hào)）商品（u條碼/u,商品名稱，所在超市名稱，采購(gòu)價(jià)格，銷售價(jià)格，商 品介紹）顧客（u編號(hào)/u,姓名，身份證號(hào)，手機(jī)號(hào)，（a）托運(yùn)

6、公司（u托運(yùn)公司編號(hào)/u,托運(yùn)公司名稱，電話，地址）訂單（u訂單ID,商品條碼/u,顧客編號(hào)，（b），運(yùn)送方式，支付憑證 編號(hào)）代購(gòu)（u代購(gòu)ID,代購(gòu)員工號(hào)，訂單ID/u, （c） 了運(yùn)送（u運(yùn)送ID,配送員工號(hào)，托運(yùn)公司編號(hào)，訂單ID/u,發(fā)運(yùn)時(shí)間）問(wèn)題2.1 （3分）根據(jù)問(wèn)題描述，補(bǔ)充圖2- 1的實(shí)體聯(lián)系圖。問(wèn)題2.2 （6分）補(bǔ)充邏輯結(jié)構(gòu)設(shè)計(jì)結(jié)果中的（a）、（b） 、（c）三處空缺。問(wèn)題2.3 （6分）為方便顧客，允許顧客在系統(tǒng)中保存多組收貨地址。 請(qǐng)根據(jù)此需求，增加 “顧客地址”弱實(shí)體，對(duì)圖2- 1進(jìn)行補(bǔ)充，并修改“運(yùn)送”關(guān)系模式?！景咐浚洪喿x下列說(shuō)明，回答問(wèn)題1至問(wèn)題3,將解答填

7、入答題紙的對(duì)應(yīng)欄內(nèi)。說(shuō)明某醫(yī)院為了更好的為患者服務(wù)、提高醫(yī)院管理水平，擬開發(fā)滿足自身特點(diǎn) 的信息系統(tǒng)。其部分需求及設(shè)計(jì)如下：需求描述（1）患者到醫(yī)院就診，需提供本人醫(yī)?？ɑ蛏矸葑C，系統(tǒng)根據(jù)醫(yī)?？ɑ蛏?份證從外部醫(yī)保信息庫(kù)獲取患者的其他詳細(xì)信息，包括醫(yī)?？ㄌ?hào)、身份證號(hào)、姓 名、性別、民族、出生日期等信息。（2）醫(yī)生信息包括醫(yī)生編號(hào)、姓名、性別、出生日期、職稱等信息。（3）通過(guò)患者自述、化驗(yàn)結(jié)果分析等信息，醫(yī)生對(duì)患者的病情進(jìn)行診斷， 開具處方，并填寫病例。病例同時(shí)包含診斷和處方信息，包括編號(hào)、患者姓名、 病情描述、診斷結(jié)論、主治醫(yī)生、藥品名稱、藥品數(shù)量、服用劑量等，處方中一 般會(huì)有多種藥品。（4）

8、病人憑醫(yī)生開具的處方可在醫(yī)院購(gòu)買藥品。醫(yī)院記錄藥品的條碼、名 稱、價(jià)格、生產(chǎn)廠商等信息。邏輯結(jié)構(gòu)設(shè)計(jì)1根據(jù)上述需求，設(shè)計(jì)出如下關(guān)系模式，醫(yī)生（u編號(hào)/u,姓名，性別，出生日期，職稱）患者（身份證號(hào)，醫(yī)保卡號(hào)，姓名，性別，民族，出生日期 ）藥品（u條碼/u,名稱，價(jià)格，生產(chǎn)廣商）病例（u編號(hào)，藥品條碼，患者身份證號(hào)，主治醫(yī)生編號(hào)/u,病情描述, 診斷結(jié)論，診斷日期，服用劑量，藥品數(shù)量，購(gòu)買日期 ）問(wèn)題3.1 （5分）對(duì)關(guān)系“患者”，請(qǐng)回答以下問(wèn)題（1）給出函數(shù)依賴集。（2）給出所有候選碼。（3）判定屬于第幾范式，并說(shuō)明理由。問(wèn)題3.2 （7分）對(duì)關(guān)系“病例”，存在如下的數(shù)據(jù)依賴：DD=編號(hào)&am

9、p;rarr;（患者身份證號(hào)，主治醫(yī)生編號(hào)，病情描述，診斷結(jié)論， 診斷日期），（編號(hào)，藥品條碼）→→（服用劑量，藥品數(shù)量，購(gòu)買日期） 請(qǐng)回答以下問(wèn)題（1）該關(guān)系模式存在哪些問(wèn)題？（2）該關(guān)系模式是否屬于4NF?青給出理由。（3）如果“病例”不是4NF,請(qǐng)分解，并指出分解后的關(guān)系模式所屬范式。分解后的關(guān)系名依次為：病例1,病例2,.問(wèn)題3.3 （3分）針對(duì)問(wèn)題2的規(guī)范化要求，如果設(shè)計(jì)者分解出的多個(gè)關(guān)系模式其中之一 如下：購(gòu)藥（病例編號(hào)，藥品條碼，服用劑量，藥品數(shù)量，購(gòu)買日期 ）如果醫(yī)院要求統(tǒng)計(jì)每名醫(yī)生（編號(hào)和姓名）每天接診的患者所購(gòu)買的各個(gè) 藥品的條碼和數(shù)量。

10、基于.上述設(shè)計(jì)，實(shí)現(xiàn)該統(tǒng)計(jì)共需哪幾個(gè)關(guān)系？為提高該統(tǒng)計(jì) 效率，允許對(duì)范式條件放寬要求，請(qǐng)修改“購(gòu)藥”關(guān)系模式以優(yōu)化該統(tǒng)計(jì)?！景咐摹浚洪喿x下列說(shuō)明，回答問(wèn)題1至問(wèn)題4,將解答填入答題紙的對(duì)應(yīng)欄內(nèi)。說(shuō) 明某汽車租賃公司建立汽車租賃管理系統(tǒng)，其數(shù)據(jù)庫(kù)的部分關(guān)系模式如下：用戶：USERS （ Userid, Name , Balance）,各屬性分別表示用戶編號(hào)、 姓名、余額；汽車：CARSCid, Ctype, CPrice, CStatus）各屬性分別表示汽車編號(hào)、 型號(hào)、價(jià)格（日租金）、狀態(tài)；租用記錄：BORROWS （BRid, Userid, Cid, STime, ETime） ,各屬性

11、分別 表示租用編號(hào)、用戶編號(hào)、汽車編號(hào)、租用時(shí)間、歸還時(shí)間；不良記錄:BADS （Bid , Userid. BRid , BTime）,各屬性分別表示不良 記錄編號(hào)、用戶編號(hào)、租用編號(hào)、不良記錄時(shí)間。相關(guān)關(guān)系模式的屬性及說(shuō)明如 下：（1）用戶租用汽車時(shí)，其用戶表中的余額不能小于500，否則不能租用。（2）汽車狀態(tài)為待租和已租，待租汽車可以被用戶租用，已租汽車不能租(3)用戶每租用一次汽車，向租用記錄中添加一條租用記錄，租用時(shí)間默 認(rèn)為系統(tǒng)當(dāng)前時(shí)，問(wèn)，歸還時(shí)間為空值，并將所租汽車狀態(tài)變?yōu)橐炎?。用戶還車 時(shí)，修改歸還時(shí)間為系統(tǒng)當(dāng)前時(shí)間，并將該汽車狀態(tài)改為待租。要求用戶不能同 時(shí)租用兩輛及以上汽車

12、。(4)租金從租用時(shí)間起按日自動(dòng)扣除.根據(jù)以上描述，回答下歹I問(wèn)題，將 SQL語(yǔ)句的空缺部分補(bǔ)充完整。問(wèn)題4.1 (4分)請(qǐng)將下面建立租用記錄表的SQL語(yǔ)句補(bǔ)充完整，要求定義主碼完整性約束 和引用完整性約束。CREATE TABLE BORROWS(BRID CHAR(20) (a),Userld CHAR(10) (b),Cld CHAR(10) (c),STime DATETIME (d),ETime DATETIME,)；問(wèn)題4.2 (4分)當(dāng)歸還時(shí)間為空值時(shí)，表示用戶還未還車，系統(tǒng)每天調(diào)用事務(wù)程序從用戶 余額中自動(dòng)扣除當(dāng)日租金，每個(gè)事務(wù)修改一條用戶記錄中的余額值。由用戶表上的觸發(fā)器實(shí)現(xiàn)

13、業(yè)務(wù)：如用戶當(dāng)日余額不足，不扣除當(dāng)日租金自動(dòng)向不良記錄表中 加入一條記錄，記錄中的BId取值由Userld +系統(tǒng)當(dāng)前日期構(gòu)成，Btime采用 GETDATE0數(shù)取系統(tǒng)當(dāng)前時(shí)間。補(bǔ)全創(chuàng)建觸發(fā)器Bad_TRG勺SQL語(yǔ)句。CREATE TRIGGER Bad_TRG (e) UPDATE of Balance ON USERS Referencing new row as nrow For each rowWhen nrow.Balance< 0BEGIN(F);插入不良記錄INSERT INTO BADSSELECT CONCAT(BORROWs. Userld, CONVERT

14、(varchar(100),GETDATEO, 10), BORROWS Userld, BRID, (g)/ CONVERTCB數(shù)將日期型數(shù)據(jù)改為字符串型/ CONCATC8數(shù)實(shí)現(xiàn)字符串拼接FROM BORROWSWHERE (h) AND ETime IS NULL;END問(wèn)題4.3 (4分)不良記錄是按日記錄的，因此用戶一次租車可能會(huì)產(chǎn)生多條不良記錄。創(chuàng)建不良記錄單視圖BADS_ Detail,統(tǒng)計(jì)每次租車產(chǎn)生的不良記錄租金費(fèi)用總和大于200 的記錄，屬性萬(wàn) Userid、Name BRId、 CId、Stime、Etime 和 total (表 示未繳納租金總和)。補(bǔ)全創(chuàng)建視圖BADS

15、_ Detail的SQL語(yǔ)句。CREATE VIEW (i) ASSELECT BADS. Userid, USERS. Name, BADS.BRId, CARS. Cld, Stime, Etime,(j) AS totalFROM BORROWS BADS. CARS. USERSWHERE BORROWS.BRId=BADS. BRIdAND BORROWS.Cid=CARS. CldAND (k) =BADS. UseridGROUP BY BADS. Userid, USERS. Name, BADS.BRID, CARS. Cid, Stime, EtimeHAViNG (l);

16、問(wèn)題4.4 (3分)查詢租用了型號(hào)為“ A8'且不良記錄次數(shù)大于等于2的用戶，輸出用戶編號(hào)、姓 名，并按用戶姓名降序排序輸出。SELECT USERS. Userid, NameFROM USERS, BORROWS, CARSWHERESERS.Userid= BORROWUserid ANDBORROWS.CidCARS.CidAND (m)AND EXiSTS(SELECT* FROM BADSWHERE BADS. Userid=BORROWS.UseridAND (n)GROUP BY UseridHAViNG COUNT(*)>= 2)ORDER BY (O);【案例

17、五】：閱讀下列說(shuō)明，回答問(wèn)題1至問(wèn)題3,將解答填入答題紙的對(duì)應(yīng)欄內(nèi)。說(shuō) 明某超市銷售系統(tǒng)的部分關(guān)系模式如下商品表：Commodity. （Ccode, Cname , price, qty） ,其中屬性含義分 別為：商品編號(hào)、商品名稱、價(jià)格、庫(kù)存量，有專門的事務(wù)保證庫(kù)存量足夠大， 銷售時(shí)無(wú)需檢測(cè)。銷售表：Sale （Sno, Ccode, amount, Stime）, 其中屬性含義分別為:銷 售編號(hào)，商品編號(hào)、數(shù)量、時(shí)間。其銷售業(yè)務(wù)規(guī)則如下：顧客在超市挑選好商品后，帶商品到結(jié)算處結(jié)算付 款,結(jié)算處有多名結(jié)算員使用多臺(tái)機(jī)器進(jìn)行結(jié)算。結(jié)算員負(fù)責(zé)掃顧客購(gòu)買商品的 條碼和數(shù)量，由系統(tǒng)后臺(tái)結(jié)算程序計(jì)算

18、出顧客購(gòu)買商品的總金額，修改商品表的 商品庫(kù)存量，并將銷售信息寫入銷售表。請(qǐng)根據(jù)上述描述，回答以下問(wèn)題。問(wèn)題5.1（3分）假設(shè)有兩個(gè)顧客同時(shí)購(gòu)買同一條碼的商品，結(jié)算事務(wù)修改該商品的庫(kù)存量（記為數(shù)據(jù)項(xiàng)X）部分的調(diào)度如圖5-1所示。如果購(gòu)買前X的初值為10,則上述調(diào)度執(zhí)行完成后，X的值是多少？屬于 哪一類不一致性？問(wèn)題5.2 （6分）引入獨(dú)占鎖指令XlockO和解鎖指令Unlock。，對(duì)問(wèn)題11中的調(diào)度進(jìn)行重 寫，要求滿足兩段鎖協(xié)議，且事務(wù) T1、T2首條指令的相對(duì)請(qǐng)求時(shí)間與問(wèn)題11 中的相同。問(wèn)題5.3 （6分）下面是用SQL實(shí)現(xiàn)的結(jié)算程序，請(qǐng)補(bǔ)全空缺處的代碼。要求在保證銷售信息不丟失的前提下，達(dá)到最大的系統(tǒng)并發(fā)度。CREATE PROCEDURE buy（IN: CommNo VARCHAR（20）, IN: AmountBuy INT） BEGIN/輸入合法性驗(yàn)證if(: AmountBuy<1) return-1;SET TRANSACTION ISOLATION LEVEL (a);BEGIN TR