軟考上半年數(shù)據(jù)庫系統(tǒng)工程師考試真題下午題

1、2018年上半年數(shù)據(jù)庫系統(tǒng)工程師考試真題（下午題）試題一 （共15分）閱讀下列說明和圖，回答問題1至問題4，將解答填入答題紙的對應(yīng)欄內(nèi)。【說明】某醫(yī)療護理機構(gòu)為老人或有護理需求者提供專業(yè)護理，現(xiàn)欲開發(fā)一基于Web的醫(yī)療管理系統(tǒng)，以改善醫(yī)療護理效率。該系統(tǒng)的主要功能如下：（1）通用信息查詢?？蛻籼峤煌ㄓ眯畔⒉樵冋埱?，查詢通用信息表，返回查詢結(jié)果。（2）醫(yī)生聘用。醫(yī)生提出應(yīng)聘/辭職申請，交由主管進行聘用/解聘審批，更新醫(yī)生表，并給醫(yī)生反饋聘用/解聘結(jié)果；刪除解聘醫(yī)生出診安排。（3）預(yù)約處理。醫(yī)生安排出診時間，存入醫(yī)生出診時間表；根據(jù)客戶提交的預(yù)約查詢請求，查詢在職醫(yī)生及其出診時間等預(yù)約所需數(shù)據(jù)并返

2、回；創(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ī)生出診時間中進行查詢，生產(chǎn)報表返回給主管?，F(xiàn)采用結(jié)構(gòu)化方法對醫(yī)療管理系統(tǒng)進行分析與設(shè)計，獲得如圖1-1所示的上下文數(shù)據(jù)流圖和圖1-2所示的0層數(shù)據(jù)流圖。 【問題1】（3分）使用說明中的詞語，給出圖1-1中的實體E1E3的名

3、稱?！締栴}2】（5分）使用說明中的詞語，給出圖1-2中的數(shù)據(jù)存儲D1D5的名稱?！締栴}3】（4分）使用說明和圖中的術(shù)語，補充圖1-2中缺失的數(shù)據(jù)流及其起點和終點?！締栴}4】（3分）使用說明中的詞語，說明“預(yù)約處理”可以分解為哪些子加工？并說明建模圖1-1和圖1-2如何保持?jǐn)?shù)據(jù)流圖平衡？試題二 （共15分）閱讀下列說明和圖，回答問題1至問題3，將解答填入答題紙的對應(yīng)欄內(nèi)?！菊f明】某海外代購公司，為擴展公司業(yè)務(wù)，需要開發(fā)一個信息化管理系統(tǒng)。請根據(jù)公司現(xiàn)有業(yè)務(wù)及需求完全該系統(tǒng)的數(shù)據(jù)庫設(shè)計?！拘枨竺枋觥?（1）記錄公司員工信息。員工信息包括工號、身份證號、姓名、性別和一個手機號，工號唯一標(biāo)識每位員工，

4、員工分為代購員和配送員。（2）記錄采購的商品信息。商品信息包括商品名稱、所在超市名稱、采購價格、銷售價格和商品介紹，系統(tǒng)內(nèi)部用商品條碼唯一標(biāo)識每種商品。一種商品只在一家超市代購。（3）記錄顧客信息。顧客信息包括顧客真實姓名、身份證號（清關(guān)繳稅用）、一個手機號和一個收貨地址，系統(tǒng)自動生成唯一的顧客編號。（4）記錄托運公司信息。托運公司信息包括托運公司名稱、電話和地址，系統(tǒng)自動生成唯一的托運公司編號。（5）顧客登錄系統(tǒng)之后，可以下訂單購買商品。訂單支付成功后，系統(tǒng)記錄唯一的支付憑證編號。顧客需要在訂單里指定運送方式：空運或海運。（6）代購員根據(jù)顧客的訂單在超市采購對應(yīng)商品，一份訂單所含的多個商品可

5、能由多名代購員從不同超市采購。（7）采購?fù)甑纳唐方挥膳渌蛦T根據(jù)顧客訂單組合裝箱，然后交給托運公司運送。托運公司按顧客訂單核對商品名稱和數(shù)量，然后按顧客的地址進行運送?！靖拍钅Ｐ驮O(shè)計】根據(jù)需求階段收集的信息，設(shè)計的實體聯(lián)系圖（不完整），如圖2-1所示： 【邏輯結(jié)構(gòu)設(shè)計】根據(jù)概念模型設(shè)計階段完成的實體聯(lián)系圖，得出如下的關(guān)系模式（不完整）：員工（工號，身份證號，姓名，性別，手機號）商品（條碼，商品名稱，所在超市名稱，采購價格，銷售價格，商品介紹）顧客（編號，姓名，身份證號，手機號，（a）托運公司（托運公司編號，托運公司名稱，電話，地址）訂單（訂單ID，商品條碼，顧客編號，（b），運送方式，支付憑證編

6、號）代購（代購ID，代購員工號，訂單D2，（c）運送（運送ID，配送員工號，托運公司編號，訂單ID，發(fā)運時間）【問題1】（3分）根據(jù)問題描述，補充圖2-1的實體聯(lián)系圖?！締栴}2】（6分）補充邏輯結(jié)構(gòu)設(shè)計結(jié)果中的（a）、（b）、（c）三處空缺?！締栴}3】（6分）為方便顧客，允許顧客在系統(tǒng)中保存多組收貨地址。請根據(jù)此需求，增加“顧客地址”弱實體，對圖2-1進行補充，并修改“運送”關(guān)系模式。試題三 （共15分）閱讀下列說明和圖，回答問題1至問題3，將解答填入答題紙的對應(yīng)欄內(nèi)?！菊f明】某醫(yī)院為了更好的為患者服務(wù)、提高醫(yī)院管理水平，擬開發(fā)滿足自身特點的信息系統(tǒng)。其部分需求及設(shè)計如下：【需求描述】 （1）

7、患者到醫(yī)院就診，需提供本人醫(yī)?？ɑ蛏矸葑C，系統(tǒng)根據(jù)醫(yī)?？ɑ蛏矸葑C從外部醫(yī)保信息庫獲取患者的其他詳細信息，包括醫(yī)?？ㄌ?、身份證號、姓名、性別、民族、出生日期等信息。（2）醫(yī)生信息包括醫(yī)生編號、姓名、性別、出生日期、職稱等信息。（3）通過患者自述、化驗結(jié)果分析等信息，醫(yī)生對患者的病情進行診斷，開具處方，并填寫病歷。病歷同時包含診斷和處方信息，包括編號、患者姓名、病情描述、診斷結(jié)論、主治醫(yī)生、藥品名稱、藥品數(shù)量、服用劑量等，處方中一般會有多種藥品。（4）病人憑醫(yī)生開具的處方可在醫(yī)院購買藥品。醫(yī)院記錄藥品的條碼、名稱、價格、生產(chǎn)廠商等信息?！具壿嫿Y(jié)構(gòu)設(shè)計】根據(jù)上述需求，設(shè)計出如下關(guān)系模式：醫(yī)生（編號，

8、姓名，性別，出生日期，職稱）患者（身份證號，醫(yī)?？ㄌ?，姓名，民族，出生日期）藥品（條碼，名稱，價格，生產(chǎn)廠商）病例（編號，藥品條碼，患者身份證號，主治醫(yī)生編號，病情描述，診斷結(jié)論，診斷日期，服用劑量，藥品數(shù)量，購買日期）【問題1】（5分）對關(guān)系“患者”，請回答以下問題：（1）給出函數(shù)依賴集。（2）給出所有候選碼。（3）判定屬于第幾范式，并說明理由。【問題2】（7分）對關(guān)系“病例”，存在如下的數(shù)據(jù)依賴：F=編號（患者身份證號，主治醫(yī)生編號，病情描述，診斷結(jié)論，診斷日期），（編號，藥品條碼）（服用劑量，藥品數(shù)量，購買日期）請回答以下問題：（1）該關(guān)系模式存在哪些問題？（2）該關(guān)系模式是否屬于4NF

9、？請給出理（3）如果“病例”不是4NF，請分解，并指出分解后的關(guān)系模式所屬范式。分解后的關(guān)系名依次為：病例1，病例2，?！締栴}3】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)計。試題四 （共15分）閱讀下列說明和圖，回答問題1至問題4，將解答填入答題紙的對應(yīng)欄內(nèi)?！菊f明】某汽車租賃公司建立汽車租賃管理系統(tǒng)，其數(shù)據(jù)

10、庫的部分關(guān)系模式如下：用戶：USERS（UserID，Name，Balance），各屬性分別表示用戶編號、姓名、余額；汽車：CARS（CID，Ctype，Cprice，CStatus），各屬性分別表示汽車編號、型號、價格（日租金）、狀態(tài)；租用記錄：BORROWS（BRID，UserID，CID，STime，ETime），各屬性分別表示租用編號、用戶編號、租用編號、不良記錄時間。相關(guān)關(guān)系模式的屬性及說明如下：（1）用戶租用汽車時，其用戶表中的余額不能小于500，否則不能租用。（2）汽車狀態(tài)為待租和已租，待租汽車可以被用戶租用，已租汽車不能租用。（3）用戶每租用一次汽車，向租用記錄中添加一條租用記

11、錄，租用時間默認(rèn)為系統(tǒng)當(dāng)前時間，歸還時間為空值，并將所租汽車狀態(tài)變?yōu)橐炎?。用戶還車時，修改歸還時間為系統(tǒng)當(dāng)前時間，并將汽車狀態(tài)改為待租。要求用戶不能同時租用兩輛及以上汽車。（4）租金從租用時間起按日自動扣除。根據(jù)以上描述，回答下列問題，將SQL語句的空缺部分補充完整。【問題1】（4分）請將下面建立租用記錄表的SQL語句補充完整，要求定義主碼完整性約束和引用完整性約束。CREATE TABLE BORROWS（ BRID CHAR（20） （a） ， UserID CHAR（10） （b） ， CID CHAR（10） （c） ， STime DATETIME （d） ， ETime DATET

12、IME，）；【問題2】（4分）當(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語句。CREATE TRIGGER Bad_TRG （e） UPDATE OF Balance ON USERS Referencing new row as nrow For each row When nr

13、ow.Balance<0 BEGIN （f） ； /插入不良記錄 INSERT INTO BADS SELECT CONCAT（BORROWS.UserID,CONVERT（varchar(100)，GETDATE（），10），BORROWS，UserID，BRID， （g） ； /CONVERT（）函數(shù)將日期型數(shù)據(jù)改為字符串型 /CONCAT（）函數(shù)實現(xiàn)字符串拼接 FROM BORROWS WHERE （h） AND ETime IS NULL； END【問題3】（4分）不良記錄是按日記錄的，因此用戶一次租車可能會產(chǎn)生多條不良記錄。創(chuàng)建不良記錄單視圖BADS_Detail，統(tǒng)計每次租車

14、產(chǎn)生的不良記錄租金費用總和大于200的記錄，屬性有UserID、Name、BRID、CID、STime、ETime和total（表示未繳納租金總和）。補全建視圖BADS_Detail的SQL語句。CREATE VIEW （i） AS SELECT BADS.UserID，USERS.Name，BADS.BRID，CARS.CID，STime，ETime， （j） AS total FROM BORROWS BADS.CARS，USERS WHERE BORROWS.BRID=BADS.BRID AND BORROWS.CID=CARS.CID AND （k） =BADS.UserID GROU

15、P BY BADS.UserID,USERS.Name，BADS.BRID，CARS.CID，STime，ETime HAVING （l） ；【問題4】（3分）查詢租用了型號為“A8”且不良記錄次數(shù)大于等于2的用戶，輸出用戶編號、姓名，并按用戶姓名降序輸出。SELECT USERS，UserID，NameFROM USERS，BORROWS，CARSWHERE USERS.UserID=BORROWS.UserID AND BORROWS.CID=CARS.CID AND （m） AND EXISTS（SELECT * FROM BADS WHERE BADS.UserID=BORROWS.U

16、serID AND （n） GROUP BY UserID HAVING COUNT（*）>=2)ORDER BY (o) ;試題五 （共15分）閱讀下列說明和圖，回答問題1至問題3，將解答填入答題紙的對應(yīng)欄內(nèi)?！菊f明】某超市銷售系統(tǒng)的部分關(guān)系模式如下商品表：Commodity（Ccode，Cname，price，qty），其中屬性含義分別為：商品編號、商品名稱、價格、庫存量，有專門的事務(wù)保證庫存量足夠大，銷售時無需檢測。銷售表：Sale（Sno，Ccode，amount，Stime），其中屬性含義分別為：銷售編號，商品編號、數(shù)量、時間。其銷售業(yè)務(wù)規(guī)則如下：顧客在超市挑選好商品后，帶商品

17、到結(jié)算處結(jié)算付款，結(jié)算處有多名結(jié)算員使用多臺機器進行結(jié)算。結(jié)算員負責(zé)掃顧客購買商品的條碼和數(shù)量，由系統(tǒng)后臺結(jié)算程序計算出顧客購買商品的總金額，修改商品表的商品庫存量，并將銷售信息寫入銷售表。請根據(jù)上述描述，回答以下問題?！締栴}1】（3分）假設(shè)有兩個顧客同時購買同一條碼的商品，結(jié)算事務(wù)修改該商品的庫存量（記為數(shù)據(jù)項X）部分的調(diào)度如圖5-1所示。 如果購買前X的初值為10，則上述調(diào)度執(zhí)行完成后，X的值是多少？屬于哪一類不一致性？【問題2】（6分）引入獨占鎖指令Clock（）和解鎖指令Unlock（），對【問題1】中的調(diào)度進行重寫，要滿足兩段鎖協(xié)議，且事務(wù)T1、T2首條指令的相關(guān)請求時間與【問題1】中的相同?！締栴}3】（6分）CREATE PROCEDURE buy（IN：CommNo VARCHAR（20），IN：AmountBuy INT） BEGIN If（：AmountBuy<1） return-1； SET TRANSACTION ISOL ATION LEVEL （a） ； BEGIN TRANSACTION； /插入銷售記錄 I