2018年上半年數(shù)據(jù)庫系統(tǒng)工程師考試數(shù)據(jù)庫系統(tǒng)應(yīng)用技術(shù)下午真題

2018年上半年數(shù)據(jù)庫系統(tǒng)工程師考試數(shù)據(jù)庫系統(tǒng)應(yīng)用技術(shù)下午真題(總分：75.00，做題時間：150分鐘)一、案例分析題(總題數(shù)：5，分?jǐn)?shù)：75.00)試題一

閱讀下列說明和圖，回答問題，將解答填入答題紙的對應(yīng)欄內(nèi)?！菊f明】某醫(yī)療護(hù)理機(jī)構(gòu)為老年人或有護(hù)理需求者提供專業(yè)護(hù)理?，F(xiàn)欲開發(fā)一基于Web的醫(yī)療管理系統(tǒng)，以改善醫(yī)療護(hù)理效率。該系統(tǒng)的主要功能如下：（1）通用信息查詢。客戶提交通用信息查詢請求，查詢通用信息表，返回查詢結(jié)果。

（2）醫(yī)生聘用。醫(yī)生提出應(yīng)聘／辭職申請，交由主管進(jìn)行聘用／解聘審批，更新醫(yī)生表，并給醫(yī)生反饋聘用／解聘結(jié)果；刪除解聘醫(yī)生的出診安排。

（3）預(yù)約處理。醫(yī)生安排出診時間，存入醫(yī)生出診時間表；根據(jù)客戶提交的預(yù)約查詢請求，查詢在職醫(yī)生及其出診時間等預(yù)約所需數(shù)據(jù)并返回；創(chuàng)建預(yù)約，提交預(yù)約請求，在預(yù)約表中新增預(yù)約記錄，更新所約醫(yī)生出診時間并給醫(yī)生發(fā)送預(yù)約通知；給客戶反饋預(yù)約結(jié)果。

（4）藥品管理。醫(yī)生提交處方，根據(jù)藥品名稱從藥品數(shù)據(jù)中查詢相關(guān)藥品庫存信息，開出藥品，更新對應(yīng)藥品的庫存以及預(yù)約表中的治療信息；給醫(yī)生發(fā)送“藥品已開出”反饋。

（5）報告創(chuàng)建。根據(jù)主管提交的報表查詢請求（報表類型和時間段），從預(yù)約數(shù)據(jù)、通用信息、藥品庫存數(shù)據(jù)、醫(yī)生以及醫(yī)生出診時間中進(jìn)行查詢，生成報表返回給主管。

現(xiàn)采用結(jié)構(gòu)化方法對醫(yī)療管理系統(tǒng)進(jìn)行分析與設(shè)計，獲得如圖1－1所示的上下文數(shù)據(jù)流圖和圖1－2所示的0層數(shù)據(jù)流圖。

（分?jǐn)?shù)：15）(1).使用說明中的詞語，給出圖1－1中的實體E1～E3的名稱。（分?jǐn)?shù)：3）__________________________________________________________________________________________

正確答案：(E1：客戶

E2：醫(yī)生

E3：主管)解析：(2).使用說明中的詞語，給出圖1－2中的數(shù)據(jù)存儲D1～D5的名稱。（分?jǐn)?shù)：5）__________________________________________________________________________________________

正確答案：(D1：通用信息表

D2：預(yù)約表

D3：醫(yī)生表

D4：出診時間表

D5：藥品庫存表)解析：(3).使用說明和圖中術(shù)語，補充圖1－2中缺失的數(shù)據(jù)流及其起點和終點。（分?jǐn)?shù)：4）__________________________________________________________________________________________

正確答案：(序號缺失數(shù)據(jù)流起點終點1刪除解聘醫(yī)生出診安排P2D42查詢在職醫(yī)生P3D33查詢出診時間P3D44更新預(yù)約表中治療信息P4D2)解析：(4).使用說明中的詞語，說明“預(yù)約處理”可以分解為哪些子加工？并說明建模圖1－1和圖1－2時如何保持?jǐn)?shù)據(jù)流圖平衡？（分?jǐn)?shù)：3）__________________________________________________________________________________________

正確答案：(預(yù)約處理分解為：安排出診、創(chuàng)建預(yù)約、提交預(yù)約請求、發(fā)送預(yù)約通知。

即保持父圖與子圖之間的平衡：父圖中某個加工的輸入輸出數(shù)據(jù)流必須與其子圖的輸入輸出數(shù)據(jù)流在數(shù)量上和名字上相同。父圖的一個輸入(或輸出)數(shù)據(jù)流對應(yīng)于子圖中幾個輸入(或輸出)數(shù)據(jù)流，而子圖中組成的這些數(shù)據(jù)流的數(shù)據(jù)項全體正好是父圖中的這一個數(shù)據(jù)流。)解析：試題二

閱讀下列說明，回答問題，將解答填入答題紙的對應(yīng)欄內(nèi)?！菊f明】某海外代購公司，為擴(kuò)展公司業(yè)務(wù)，需要開發(fā)一個信息化管理系統(tǒng)。請根據(jù)公司現(xiàn)有業(yè)務(wù)及需求完成該系統(tǒng)的數(shù)據(jù)庫設(shè)計。

【需求描述】

（1）記錄公司員工信息。員工信息包括工號、身份證號、姓名、性別和一個手機(jī)號，工號唯一標(biāo)識每位員工，員工分為代購員和配送員。

（2）記錄采購的商品信息。商品信息包括商品名稱、所在超市名稱、采購價格、銷售價格和商品介紹，系統(tǒng)內(nèi)部用商品條碼唯一標(biāo)識每種商品。一種商品只在一家超市代購。

（3）記錄顧客信息。顧客信息包括顧客真實姓名、身份證號（清關(guān)繳稅用）、一個手機(jī)號和一個收貨地址，系統(tǒng)自動生成唯一的顧客編號。

（4）記錄托運公司信息。托運公司信息包括托運公司名稱、電話和地址，系統(tǒng)自動生成唯一的托運公司編號。

（5）顧客登錄系統(tǒng)之后，可以下訂單購買商品。訂單支付成功后，系統(tǒng)記錄唯一的支付憑證編號。顧客需要在訂單里指定運送方式：空運或海運。

（6）代購員根據(jù)顧客的訂單在超市采購對應(yīng)商品，一份訂單所含的多個商品可能由多名代購員從不同超市采購。

（7）采購?fù)甑纳唐方挥膳渌蛦T根據(jù)顧客訂單組合裝箱，然后交給托運公司運送。托運公司按顧客訂單核對商品名稱和數(shù)量，然后按顧客的地址進(jìn)行運送?！靖拍钅Ｐ驮O(shè)計】

根據(jù)需求階段收集的信息，設(shè)計的實體聯(lián)系圖（不完整）如如圖2－1所示

【邏輯結(jié)構(gòu)設(shè)計】根據(jù)概念模型設(shè)計階段完成的實體聯(lián)系圖，得出如下關(guān)系模式（不完整）：

員工（工號，身份號，姓名，性別，手機(jī)號）

商品（條碼，商品名稱，所在超市名稱，采購價格，銷售價格，商品介紹）

顧客（編號，姓名，身份證號，手機(jī)號，（a））

托運公司（托運公司編號，托運公司名稱，電話，地址）

訂單（訂單1D，畫品條碼，順編號，（b），運送方式，支付憑證編號）

代購（代購ID，代購員工號，訂單D2，（c））

運送（運送ID，配送員工號，托運公司編號，訂單ID，發(fā)運時間）（分?jǐn)?shù)：15）(1).根據(jù)問題描述，補充圖2－1的實體聯(lián)系圖。（分?jǐn)?shù)：3）__________________________________________________________________________________________

正確答案：()解析：(2).補充邏輯結(jié)構(gòu)設(shè)計結(jié)果中的（a）、（b）、（c）三處空缺。（分?jǐn)?shù)：6）__________________________________________________________________________________________

正確答案：((a)、收貨地址

(b)、商品數(shù)量

(c)、商品條碼)解析：(3).為方便顧客，允許顧客在系統(tǒng)中保存多組收貨地址。請根據(jù)此需求，增加“顧客地址”弱實體，對圖2－1進(jìn)行補充，并修改“運送”關(guān)系模式。（分?jǐn)?shù)：6）__________________________________________________________________________________________

正確答案：()解析：試題三

閱讀下列說明，回答問題，將解答填入答題紙的對應(yīng)欄內(nèi)。【說明】某醫(yī)院為了更好的為患者服務(wù)、提高醫(yī)院管理水平，擬開發(fā)滿足自身特點的信息系統(tǒng)。其部分需求及設(shè)計如下：

【需求描述】

（1）患者到醫(yī)院就診，需提供本人醫(yī)保卡或身份證，系統(tǒng)根據(jù)醫(yī)?？ɑ蛏矸葑C從外部醫(yī)保信息庫獲取患者的其他詳細(xì)信息，包括醫(yī)?？ㄌ枴⑸矸葑C號、姓名、性別、民族、出生日期等信息。

（2）醫(yī)生信息包括醫(yī)生編號、姓名、性別、出生日期、職稱等信息。

（3）通過患者自述、化驗結(jié)果分析等信息，醫(yī)生對患者的病情進(jìn)行診斷，開具處方，并填寫病例。病例同時包含診斷和處方信息，包括編號、患者姓名、病情描述、診斷結(jié)論、主治醫(yī)生、藥品名稱、藥品數(shù)量、服用劑量等，處方中一般會有多種藥品。

（4）病人憑醫(yī)生開具的處方可在醫(yī)院購買藥品。醫(yī)院記錄藥品的條碼、名稱、價格、生產(chǎn)廠商等信息。【邏輯結(jié)構(gòu)設(shè)計】

根據(jù)上述需求，設(shè)計出如下關(guān)系模式

醫(yī)生（編號，姓名，性別，出生日期，職稱）

患者（身份證號，醫(yī)保卡號，姓名，性別，民族，出生日期）

藥品（條碼，名稱，價格，生產(chǎn)廠商）

病例（編號，藥品條碼，患者身份證號，主治醫(yī)生編號，病情描述，診斷結(jié)論，診斷

日期，服用劑量，藥品數(shù)量，購買日期）（分?jǐn)?shù)：15）(1).對關(guān)系“患者”，請回答以下問題：

（1）給出函數(shù)依賴集。

（2）給出所有候選碼。

（3）判定屬于第幾范式，并說明理由。（分?jǐn)?shù)：5）__________________________________________________________________________________________

正確答案：(（1）身份證號”(姓名，性別，民族，出生日期，醫(yī)保卡號)，醫(yī)?？ㄌ枴?身份證號，姓名，性別，民族，出生日期)

（2）身份證號或醫(yī)保卡號

（3）屬于BCNF，不存在非主屬性/主屬性對碼的部分函數(shù)依賴和傳遞函數(shù)依賴。)解析：(2).對關(guān)系“病例”，存在如下的數(shù)據(jù)依賴：

DD＝｛編編號→（患者身份證號，主治醫(yī)生編號，病情描述，診斷結(jié)論，診斷日期），編號，藥品條碼）→→（服用劑量，藥品數(shù)量，購買日期）｝

請回答以下問題：

（1）該關(guān)系模式存在哪些問題？

（2）該關(guān)系模式是否屬于4NF？請給給出理由。

（3）如果“病例”不是4NF，請分解，并指出分解后的關(guān)系模式所屬范式。分解后的關(guān)系名依次為：病例1，病例2，...。（分?jǐn)?shù)：7）__________________________________________________________________________________________

正確答案：(

(1)由于一個處方中會有多種藥品，存在有數(shù)據(jù)冗余，會造成數(shù)據(jù)的插入、更新、刪除等異常。

(2)不是4NF，存在非主屬性對碼的部分函數(shù)依賴，不是BCNF，且存在平凡函數(shù)的多值依賴。

(3)病例1(編號，患者身份證號，主治醫(yī)生編號，病情描述，診斷結(jié)論，診斷日期)。

(4)病例2(編號，藥品條碼，服用劑量，藥品數(shù)量，購買日期)。)解析：(3).針對【問題2】的規(guī)范化要求，如果設(shè)計者分解出的多個關(guān)系模式其中之一如下：

購藥（病例編號，藥品條碼，服用劑量，藥品數(shù)量，購買日期）

如果醫(yī)院要求統(tǒng)計每名醫(yī)生（編號和姓名）每天接診的患者所購買的各個藥品的條碼和數(shù)量?；谏鲜鲈O(shè)計，實現(xiàn)該統(tǒng)計共需哪幾個關(guān)系？為提高該統(tǒng)計效率，允許對范式條件放寬要求，請修改“購藥”關(guān)系模式以優(yōu)化該統(tǒng)計。（分?jǐn)?shù)：3）__________________________________________________________________________________________

正確答案：(購藥(病例編號，藥品條碼，服用劑量，藥品數(shù)量，購買日期，醫(yī)生編號、醫(yī)生姓名。)解析：試題四

閱讀下列說明，回答問題，將解答填入答題紙的對應(yīng)欄內(nèi)?！菊f明】某汽車租賃公司建立汽車租賃管理系統(tǒng)，其數(shù)據(jù)庫的部分關(guān)系模式如下：

用戶：USERS（UserId，Name，Balance），各屬性分別表示用戶編號、姓名、余額；

汽車：CARS（CId，Ctype，CPrice，CStatus）各屬性分別表示汽車編號、型號、價格（日租金）、狀態(tài)；

租用記錄：BORROWS（BRId，UserId，CId，STime，ETime），各屬性分別表示租用編號、用戶編號、汽車編號、租用時間、歸還時間；

不良記錄：BADS（BId，UserId．BRId，BTime），各屬性分別表示不良記錄編號、用戶編號、租用編號、不良記錄時間。

相關(guān)關(guān)系模式的屬性及說明如下：

（1）用戶租用汽車時，其用戶表中的余額不能小于500，否則不能租用。

（2）汽車狀態(tài)為待租和已租，待租汽車可以被用戶租用，已租汽車不能租用。

（3）用戶每租用一次汽車，向租用記錄中添加一條租用記錄，租用時間默認(rèn)為系統(tǒng)當(dāng)前時間，歸還時間為空值，并將所租汽車狀態(tài)變?yōu)橐炎?。用戶還車時，修改歸還時間為系統(tǒng)當(dāng)前時間，并將該汽車狀態(tài)改為待租。要求用戶不能同時租用兩輛及以上汽車.

（4）租金從租用時間起按日自動扣除。

根據(jù)以上描述，回答下列問題，將SQL語句的空缺部分補充完整。（分?jǐn)?shù)：15）(1).請將下面建立租用記錄表的SQL語句補充完整，要求定義主碼完整性約束和引用完整性約束。CREATETABLEBORROWS(BRID

CHAR(20)(a),UserIdCHAR(10)

(b),CldCHAR(10)

(c),STimeDATETIME

(d),ETimeDATETIME,);（分?jǐn)?shù)：4）__________________________________________________________________________________________

正確答案：(（a）PRIMARYKEY

（b）REFERENCESUSERS(Userld)

（c）REFERENCESCARS(CId)

（d）DEFAULTGETDATE()或DEFAULTNOW())解析：(2).當(dāng)歸還時間為空值時，表示用戶還未還車，系統(tǒng)每天調(diào)用事務(wù)程序從用戶余額中自動扣除當(dāng)日租金，每個事務(wù)修改一條用戶記錄中的余額值。由用戶表上的觸發(fā)器實現(xiàn)業(yè)務(wù)：如用戶當(dāng)日余額不足，不扣除當(dāng)日租金，自動向不良記錄表中加入一條記錄，記錄中的BId取值由UserId＋系統(tǒng)當(dāng)前日期構(gòu)成，Btime采用GETDATE()函數(shù)取系統(tǒng)當(dāng)前時間。補全創(chuàng)建觸發(fā)器Bad_TRG的SQL語句。CREATETRIGGERBad_TRG

(e)

UPDATEofBalanceONUSERSReferencingnewrowasnrowForeachrowWhennrow.Balance<0BEGIN(f);//插入不良記錄INSERTINTOBADSSELECTCONCAT(BORROWs.UserId,CONVERT(varchar(100),GETDATE(),10)),BORROWSUserId,BRID,(g)//CONVERTO函數(shù)將日期型數(shù)據(jù)改為字符串型//CONCATO函數(shù)實現(xiàn)字符串拼接FROMBORROWSWHERE

(h)

ANDETimeISNULL;END（分?jǐn)?shù)：4）__________________________________________________________________________________________

正確答案：((e)AFTER

(f）ROLLBACK

(g)GETDATE()

(h)nrow.Userld二BORROWS.Userld)解析：(3).不良記錄是按日記錄的，因此用戶一次租車可能會產(chǎn)生多條不良記錄。創(chuàng)建不良記錄單視圖BADS_Detail，統(tǒng)計每次租車產(chǎn)生的不良記錄租金費用總和大于200的記錄，屬性有UserId、Name、BRId、CId、Stime、Etime和total（表示未繳納租金總和）。補全建視圖BADS_Detail的SQL語句。CREATEVIEW

(i)

ASSELECT

BADS.UserId,USERS.Name,BADS.BRId,CARS.Cld,Stime,Etime,(j)

AStotalFROMBORROWSBADS.CARS.USERSWHEREBORROWS.BRId=BADS.BRIdAND

BORROWS.Cid=CARS.CldAND

(k)

=BADS.UserIdGROUPBYBADS.UserId,USERS.Name,BADS.BRID,CARS.CId,Stime,EtimeHAVING

(l)

;（分?jǐn)?shù)：4）__________________________________________________________________________________________

正確答案：((i)BADS_detail

(j)Count(*)*CARS.CPrice

(k)USERS.Userld

(l)Count(*)*CARS.＞200)解析：(4).查詢租用了型號為“A8”且不良記錄次數(shù)大于等于2的用戶，輸出用戶編號、姓名，并按用戶姓名降序排序輸出。SELECTUSERS.UserId,NameFROMUSERS，BORROWS,CARSWHEREUSERS.UserId=BORROWS.UserIdANDBORROWS.Cid=CARS.CIdAND(m)

ANDEXISTS(SELECT*FROMBADSWHEREBADS.UserId=BORROWS.UserIdAND(n)GROUPBYUserIdHAVINGCOUNT(*)>=2)ORDERBY（o）

；（分?jǐn)?shù)：3）__________________________________________________________________________________________

正確答案：((m)BADS.BRId二BORROWS.BRId

(n)CARS.Ctype二’A8'

(o)NameDESC)解析：試題五

閱讀下列說明，回答問題，將解答填入答題紙的對應(yīng)欄內(nèi)?！菊f明】某超市銷售系統(tǒng)的部分關(guān)系模式如下：

商品表：Commodity（Ccode，Cname，price，qty），其中屬性含義分別為：商品編號、商品名稱、價格、庫存量，有專門的事務(wù)保證庫存量足夠大，銷售時無需檢測。

銷售表：Sale（Sno，Ccode，amount，Stime），其中屬性含義分別為：銷售編號，商品編號、數(shù)量、時間。

其銷售業(yè)務(wù)規(guī)則如下：顧客在超市挑選好商品后，帶商品到結(jié)算處結(jié)算付款，結(jié)算處有多名結(jié)算員使用多臺機(jī)器進(jìn)行結(jié)算。結(jié)算員負(fù)責(zé)掃顧客購買商品的條碼和數(shù)量，由系統(tǒng)后臺結(jié)算程序計算出顧客購買商品的總金額，修改商品表的商品庫存量，并將銷售信息寫入銷售表。

請根據(jù)上述描述，回答以下問題。（分?jǐn)?shù)：15）(1).假設(shè)有兩個顧客同時購買同一條碼的商品，結(jié)算事務(wù)修改該商品的庫存量（記為數(shù)據(jù)項X）部部分的調(diào)度如圖5－1所示。T1T2a←Read（X）；

a←Read（X）；a=a-1

Write（X，a）；

a=a-2

Write（X，a）；圖5-1

部分結(jié)算事務(wù)調(diào)度圖

如果購買前X的初值為10，則上述調(diào)度執(zhí)行完成后，X的值是多少？屬于哪一類不一致性？（分?jǐn)?shù)：3）__________________________________________________________________________________________

正確答案：(X的值是8，屬于更新丟失引起的不一致性。)解析：(2).引入獨占鎖指令Clock()和解鎖指令Unlock()，對【問題1】中的調(diào)度進(jìn)行重寫，要求滿足兩段鎖協(xié)議，且事務(wù)T1、T2首條指令的相對請求時間與【問題1】中的相同。（分?jǐn)?shù)：6）_____________________________________