● 試題三（共15分）

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

【說(shuō)明】

某醫(yī)院為了更好的為患者服務(wù)、提高醫(yī)院管理水平，擬開(kāi)發(fā)滿(mǎn)足自身特點(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)、姓名、性別、出生日期、職稱(chēng)等信息。

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

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

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

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

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

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

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

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

日期，服用劑量，藥品數(shù)量，購(gòu)買(mǎi)日期）

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

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

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

（2）給出所有候選碼。

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

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

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

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

請(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)買(mǎi)日期）

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

● 試題四（共15分）

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

【說(shuō)明】

某汽車(chē)租賃公司建立汽車(chē)租賃管理系統(tǒng)，其數(shù)據(jù)庫(kù)的部分關(guān)系模式如下：

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

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

租用記錄： BORROWS（BRId， UserId，CId， STime， ETime），各屬性分別表示租用編號(hào)、用戶(hù)編號(hào)、汽車(chē)編號(hào)、租用用時(shí)間、歸還時(shí)間；

不良記錄：BADS（BId， UserId．BRId， BTime），各屬性分別表示不良記錄編號(hào)、用戶(hù)編號(hào)、租用編號(hào)、不良記錄時(shí)間。

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

（1）用戶(hù)租用汽車(chē)時(shí)，其用用戶(hù)表中的余額不能小于500，否則不能租用。

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

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

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

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

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

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

性約束。

CREATE TABLE BORROWS(

BRID  CHAR(20)(a),

UserId CHAR(10)  (b),

Cld CHAR(10)  (c),

STime DATETIME  (d),

ETime DATETIME,

);

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

當(dāng)歸還時(shí)間為空值時(shí)，表示用戶(hù)還未還車(chē)，系統(tǒng)每天調(diào)用事務(wù)程序從用戶(hù)余額中自動(dòng)

扣除當(dāng)日租金，每個(gè)事務(wù)修改一條用戶(hù)記錄中的余額值。由用戶(hù)表上的觸發(fā)器實(shí)現(xiàn)業(yè)務(wù)：如用戶(hù)當(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ǔ)句。

CREATE TRIGGER Bad_TRG (e) UPDATE of Balance ON USERS

Referencing new row as nrow

For each row

When nrow.Balance< 0

BEGIN

(f) ;

//插入不良記錄

INSERT INTO BADS

SELECT CONCAT(BORROWs. UserId, CONVERT(varchar(100),

GETDATE(), 10)), BORROWS UserId, BRID, (g)

// CONVERTO函數(shù)將日期型數(shù)據(jù)改為字符串型

//CONCATO函數(shù)實(shí)現(xiàn)字符串拼接

FROM BORROWS

WHERE     (h)     AND ETime IS NULL;

END

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

不良記錄是按日記錄的，因此用戶(hù)一次租車(chē)可能會(huì)產(chǎn)生多條不良記錄。創(chuàng)建不良記錄

單視圖 BADS_Detail，統(tǒng)計(jì)每次租車(chē)產(chǎn)生的不良記錄租金費(fèi)用總和大于200的記錄，屬性有 UserId、Name、BRId、CId、 Stime、 Etime和 total（表示未繳納租金總和）。補(bǔ)全建視圖 BADS_Detail的SQL語(yǔ)句。

CREATE VIEW (i) AS

SELECT  BADS. UserId, USERS. Name, BADS.BRId, CARS. Cld, Stime, Etime,

(j) AS total

FROM BORROWS BADS. CARS. USERS

WHERE BORROWS.BRId=BADS. BRId

AND  BORROWS.Cid=CARS. Cld

AND  (k)   =BADS. UserId

GROUP BY BADS. UserId, USERS. Name, BADS.BRID, CARS. CId, Stime, Etime

HAVING (1)  ;

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

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

并按用戶(hù)姓名降序排序輸出。

SELECT USERS. UserId, Name

FROM USERS，BORROWS, CARS

WHERE USERS. UserId= BORROWS. UserId AND BORROWS.Cid= CARS. CId

AND (m)  AND EXISTS(

SELECT * FROM BADS

WHERE BADS. UserId=BORROWS.UserId AND(n)

GROUP BY UserId

HAVING COUNT(*)>= 2)

ORDER BY （0） ；

>>>>>>2018上半年數(shù)據(jù)庫(kù)系統(tǒng)工程師下午真題匯總

>>>>>>查看更多數(shù)據(jù)庫(kù)系統(tǒng)工程師歷年真題