● 試題三（共15分）

閱讀下列說明，回答問題1至問題3，將解答填入答題紙的對應欄內。

【說明】

某醫院為了更好的為患者服務、提高醫院管理水平，擬開發滿足自身特點的信息系統。其部分需求及設計如下：

【需求描述】

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

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

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

（4）病人憑醫生開具的處方可在醫院購買藥品。醫院記錄藥品的條碼、名稱、價格、生產廠商等信息。

【邏輯結構設計】

根據上述需求，設計出如下關系模式

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

患者（身份證號，醫?？ㄌ?，姓名，性別，民族，出生日期）

藥品（條碼，名稱，價格，生產廠商）

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

日期，服用劑量，藥品數量，購買日期）

【問題1】（5分）

對關系“患者”，請回答以下問題：

（1）給出函數依賴集。

（2）給出所有候選碼。

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

【問題2】（7分）

對關系“病例”，存在如下的數據依賴：

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

請回答以下問題

（1）該關系模式存在哪些問題？

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

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

【問題3】（3分）

針對【問題2】的規范化要求，如果設計者分解出的多個關系模式其中中之一如下：

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

如果醫院要求統計每名醫生（編號和姓名）每天接診的患者所購買的各個藥品的條碼和數量?；谏鲜鲈O計，實現該統計共需哪幾個關系？為提高該統計效率，允許對范式條件放寬要求，請修改“購藥”關系模式以優化該統計。

● 試題四（共15分）

閱讀下列說明，回答問題1至問題4，將解答填入答題紙的對應欄內。

【說明】

某汽車租賃公司建立汽車租賃管理系統，其數據庫的部分關系模式如下：

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

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

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

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

相關關系模式的屬性及說明如下

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

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

（3）用戶每租用一次汽車，向租用記錄中添加一條租用記錄，租用時間默認為系統當前時間，歸還時間為空值，并將所租汽車狀態變為已租。用戶還車時，修改歸還時間為系統當前時間，并將該汽車狀態改為待租。要求用戶不能同時租用兩輛及以上汽車.

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

根據以上描述，回答下列問題題，將SQL語句的空缺部分補充完整。

【問題1】（4分）

請將下面建立租用記錄表的SQL語句補充完整，要求定義主碼完整性約束和引用完整

性約束。

CREATE TABLE BORROWS(

BRID  CHAR(20)(a),

UserId CHAR(10)  (b),

Cld CHAR(10)  (c),

STime DATETIME  (d),

ETime DATETIME,

);

【問題2】（4分）

當歸還時間為空值時，表示用戶還未還車，系統每天調用事務程序從用戶余額中自動

扣除當日租金，每個事務修改一條用戶記錄中的余額值。由用戶表上的觸發器實現業務：如用戶當日余額不足，不扣除當日租金，自動向不良記錄表中加入一條記錄，記錄中的BId取值由 UserId＋系統當前日期構成， Btime采用 GETDATE()函數取系統當前時間。補全創建觸發器 Bad_TRG的SQL語句。

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函數將日期型數據改為字符串型

//CONCATO函數實現字符串拼接

FROM BORROWS

WHERE     (h)     AND ETime IS NULL;

END

【問題3】（4分）

不良記錄是按日記錄的，因此用戶一次租車可能會產生多條不良記錄。創建不良記錄

單視圖 BADS_Detail，統計每次租車產生的不良記錄租金費用總和大于200的記錄，屬性有 UserId、Name、BRId、CId、 Stime、 Etime和 total（表示未繳納租金總和）。補全建視圖 BADS_Detail的SQL語句。

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)  ;

【問題4】（3分）

查詢租用了型號為“A8”且不良記錄次數大于等于2的用戶，輸出用戶編號、姓名，

并按用戶姓名降序排序輸出。

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上半年數據庫系統工程師下午真題匯總

>>>>>>查看更多數據庫系統工程師歷年真題