2022年5月數(shù)據(jù)庫(kù)系統(tǒng)工程師下午題-4

精品文檔-下載后可編輯年5月數(shù)據(jù)庫(kù)系統(tǒng)工程師下午題2022年5月數(shù)據(jù)庫(kù)系統(tǒng)工程師下午題

1.【問(wèn)答題】15分|閱讀下列說(shuō)明和圖，回答問(wèn)題1至問(wèn)題4，將解答填入答題紙的對(duì)應(yīng)欄內(nèi)。

【說(shuō)明】

某醫(yī)療護(hù)理機(jī)構(gòu)為老年人或有護(hù)理需求者提供專業(yè)護(hù)理?，F(xiàn)欲開(kāi)發(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í)間表；根據(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ù)存信息，開(kāi)出藥品，更新對(duì)應(yīng)藥品的庫(kù)存以及預(yù)約表中的治療信息；給醫(yī)生發(fā)送“藥品已開(kāi)出”反饋。（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)表返回給主管。

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

【問(wèn)題1】（3分）

使用說(shuō)明中的詞語(yǔ)，給出圖1－1中的實(shí)體E1～E3的名稱。

【問(wèn)題2】（5分）

使用說(shuō)明中的詞語(yǔ)，給出圖1－2中的數(shù)據(jù)存儲(chǔ)D1～D5的名稱。

【問(wèn)題3】（4分）

使用說(shuō)明和圖中術(shù)語(yǔ)，補(bǔ)充圖1－2中缺失的數(shù)據(jù)流及其起點(diǎn)和終點(diǎn)。

【問(wèn)題4】（3分）

使用說(shuō)明中的詞語(yǔ)，說(shuō)明“預(yù)約處理”可以分解為哪些子加工？并說(shuō)明建模圖1－1和圖1－2時(shí)如何保持?jǐn)?shù)據(jù)流圖平衡？

2.【問(wèn)答題】15分|閱讀下列說(shuō)明，回答問(wèn)題1至問(wèn)題3，將解答填入答題紙的對(duì)應(yīng)欄內(nèi)。

【說(shuō)明】

某海外代購(gòu)公司，為擴(kuò)展公司業(yè)務(wù)，需要開(kāi)發(fā)一個(gè)信息息化管理系統(tǒng)。請(qǐng)根據(jù)公司現(xiàn)有業(yè)務(wù)及需求完成該系統(tǒng)的數(shù)據(jù)庫(kù)設(shè)計(jì)。

【需求描述】

（1）記錄公司員工信息。員工信息包括工號(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)。顧客需要在訂單里指定運(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所示。

【邏輯模型設(shè)計(jì)】

根據(jù)概念模型設(shè)計(jì)階段完成的實(shí)體聯(lián)系圖，得出如下關(guān)系模式（不完整）：

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

商品（條碼，商品名稱，所在超市名稱，采購(gòu)價(jià)格，銷售價(jià)格，商品介紹）

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

托運(yùn)公司（托運(yùn)公司編號(hào)，托運(yùn)公司名稱，電話，地址）

訂單（訂單1D，畫(huà)品條碼，順編號(hào)，（b），運(yùn)送方式，支付憑證編號(hào)）

代購(gòu)（代購(gòu)1D，代購(gòu)員工號(hào)，訂單D2，（c））

運(yùn)送（運(yùn)送1D，配送員工號(hào)，托運(yùn)公司編號(hào)，訂單1D，發(fā)運(yùn)時(shí)間）

根據(jù)以上描述，回答下列問(wèn)題：

【問(wèn)題1】

根據(jù)問(wèn)題描述，補(bǔ)充圖2-1的實(shí)體聯(lián)系圖。

【問(wèn)題2】

補(bǔ)充邏輯結(jié)構(gòu)設(shè)計(jì)結(jié)果中的（a）、（b）、（c）三處空缺。

【問(wèn)題3】

為方便顧客，允許顧客在系統(tǒng)中保存多組收貨地址。請(qǐng)根據(jù)此需求，增加“顧客地址”弱實(shí)體，對(duì)圖2-1進(jìn)行補(bǔ)充，并修改“運(yùn)送”關(guān)系模式。

3.【問(wèn)答題】15分|閱讀下列說(shuō)明，回答問(wèn)題1至問(wèn)題3，將解答填入答題紙的對(duì)應(yīng)欄內(nèi)。

【說(shuō)明】

某醫(yī)院為了更好的為患者服務(wù)、提高醫(yī)院管理水平，擬開(kāi)發(fā)滿足自身特點(diǎn)的信息系統(tǒng)。其部分需求及設(shè)計(jì)如下。

【需求描述】

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

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

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

（4）病人憑醫(yī)生開(kāi)具的處方可在醫(yī)院購(gòu)買藥品。醫(yī)院記錄藥品的條碼、名稱、價(jià)格、生產(chǎn)廠商等信息。

【邏輯結(jié)構(gòu)設(shè)計(jì)】

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

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

患者（身份證號(hào)，醫(yī)?？ㄌ?hào)，姓名，性別，民族，出生日期）

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

病例（編號(hào)，藥品條碼，患者身份證號(hào)，主治醫(yī)生編號(hào)，病情描述，診斷結(jié)論，診斷日期，服用劑量，藥品數(shù)量，購(gòu)買日期）

【問(wèn)題1】（5分）

對(duì)關(guān)系“患者”，請(qǐng)回答以下問(wèn)題：

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

（2）給出所有候選碼。

（3）判定屬于第幾范式，并說(shuō)明理由。

【問(wèn)題2】（7分）

對(duì)關(guān)系“病例”，存在如下的數(shù)據(jù)依賴：

FD＝｛編號(hào)→（患者身份證號(hào)，主治醫(yī)生編號(hào)，病情描述，診斷結(jié)論，診斷日期），（編號(hào)，藥品條碼）→（服用劑量，藥品數(shù)量，購(gòu)買日期）｝，請(qǐng)回答以下問(wèn)題。

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

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

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

【問(wèn)題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ù)量?；谏鲜鲈O(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ì)。

4.【問(wèn)答題】15分|閱讀下列說(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)、姓名、余額；

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

租用記錄：BORROWS（BRId，UserId，CId，STime，ETime），各屬性分別表示租用編號(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í)間，歸還時(shí)間為空值，并將所租汽車狀態(tài)變?yōu)橐炎狻Ｓ脩暨€車時(shí)，修改歸還時(shí)間為系統(tǒng)當(dāng)前時(shí)間，并將該汽車狀態(tài)改為待租。要求用戶不能同時(shí)租用兩輛及以上汽車。

（4）租金從租用時(shí)間起按日自動(dòng)扣除。

根據(jù)以上描述，回答下列問(wèn)題題，將SQL語(yǔ)句的空缺部分補(bǔ)充完整。

【問(wèn)題1】（4分）

（1）請(qǐng)將下面建立租用記錄表的SQL語(yǔ)句補(bǔ)充完整，要求定義主碼完整性約束和引用完整性約束。

CREATETABLEBORROWS(

BRIDCHAR(20)(a),

UserIdCHAR(10)(b),

CldCHAR(10)(c),

STimeDATETIME(d),

ETimeDATETIME,

);

【問(wèn)題2】（4分）

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

CREATETRIGGERBad_TRG(e)UPDATE

ofBalanceONUSERS

Referencingnewrowasnrow

Foreachrow

Whennrow.Balance0

BEGIN

(f);

//插入不良記錄

INSERTINTOBADS

SELECTCONCAT(BORROWs.UserId,CONVERT(varchar(100),GETDATE(),10)),BORROWSUserId,BRID,(g)

//CONVERTO函數(shù)將日期型數(shù)據(jù)改為字符串型，CONCATO函數(shù)實(shí)現(xiàn)字符串拼接

FROMBORROWS

WHERE(h)ANDETimeISNULL;

END；

【問(wèn)題3】（4分）

不良記錄是按日記錄的，因此用戶一次租車可能會(huì)產(chǎn)生多條不良記錄。創(chuàng)建不良記錄單視圖BADS_Detail，統(tǒng)計(jì)每次租車產(chǎn)生的不良記錄租金費(fèi)用總和大于200的記錄，屬性有UserId、Name、BRId、CId、Stime、Etime和total（表示未繳納租金總和）。補(bǔ)全建視圖BADSDetail的SQL語(yǔ)句。

CREATEVIEW(i)AS

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

FROMBORROWS,BADS,CARS,USERS

WHEREBORROWS.BRId=BADS.BRIdANDBORROWS.Cid=CARS.Cld

AND(k)=BADS.UserId

GROUPBYBADS.UserId,USERS.Name,BADS.BRID,CARS.CId,Stime,EtimeHAVING(l);

【問(wèn)題4】（3分）

查詢租用了型號(hào)為“A8”且不良記錄次數(shù)大于等于2的用戶，輸出用戶編號(hào)、姓名，并按用戶姓名降序排序輸出。

SELECTUSERS.UserId,Name

FROMUSERS，BORROWS,CARS

WHEREUSERS.UserId=BORROWS.UserIdANDBORROWS.Cid=CARS.CId

AND(m)ANDEXISTS(

SELECT*FROMBADS

WHEREBADS.UserId=BORROWS.UserIdAND(n)

GROUPBYUserIdHAVINGCOUNT(*)=2)

ORDERBY（0）；

5.【問(wèn)答題】15分|閱讀下列說(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ì)算出顧客購(gòu)買商品的總金額，修改商品表的商品庫(kù)存量，并將銷售信息寫(xiě)入銷售表。

請(qǐng)根據(jù)上述描述，回答以下問(wèn)題。

【問(wèn)題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)題2】（6分）

引入獨(dú)占鎖指令Xlock()和解鎖指令Unlock(