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

1、2021年上半年數(shù)據(jù)庫系統(tǒng)工程師下午試題2021年上半年數(shù)據(jù)庫系統(tǒng)工程師下午試題試題一（共15分）閱讀下列說明，回答問題1和問題2，將解答填入答題紙的對應(yīng)欄內(nèi)?！菊f明】假設(shè)某大型商業(yè)企業(yè)由商品配送中心和連鎖超市組成，其中商品配送中心包括采購、財務(wù)、配送等部門。為實現(xiàn)高效管理，設(shè)計了商品配送中心信息管理系統(tǒng)，其主要功能描述如下：1. 系統(tǒng)接收由連鎖超市提出的供貨請求，并將其記錄到供貨請求記錄文件。2. 在接到供貨請求后，從商品庫存記錄文件中進(jìn)行商品庫存信息查詢。如果庫存滿足供貨請求，則給配送處理發(fā)送配送通知；否則，向采購部門發(fā)出缺貨通知。3. 配送處理接到配送通知后，查詢供貨請求記錄文件，更新商

2、品庫存記錄文件，并向配送部門發(fā)送配送單，在配送貨品的同時記錄配送信息至商品配送記錄文件。4. 采購部門接到缺貨通知后，與供貨商洽談，進(jìn)行商品采購處理，合格商品入庫，并記錄采購清單至采購清單記錄文件、向配送處理發(fā)出配送通知，同時通知財務(wù)部門給供貨商支付貨款。該系統(tǒng)采用結(jié)構(gòu)化方法進(jìn)行開發(fā)，得到待修改的數(shù)據(jù)流圖（如圖1-1所示）?！締栴}1】（8）使用【說明】中的詞語，給出圖 1-1 中外部實體 E1 至 E4 的名稱和數(shù)據(jù)存儲 D1 至D4的名稱。 【問題2】（7分）圖 1-1 中存在四處錯誤數(shù)據(jù)流，請指出各自的起點和終點；若將上述四條錯誤數(shù)據(jù)流刪除，為保證數(shù)據(jù)流圖的正確性，應(yīng)補充三條數(shù)據(jù)流，請給出

3、所補充數(shù)據(jù)流的起點和終點。（起點和終點請采用數(shù)據(jù)流圖1-1中的符號或名稱）試題二（共15 分）閱讀下列說明，回答問題1至問題3，將解答填入答題紙的對應(yīng)欄內(nèi)?！菊f明】某網(wǎng)上書店后臺數(shù)據(jù)庫的部分關(guān)系模式如下：會員（會員編號，用戶名，密碼，姓名，地址，郵編，電話，消費額，積分）圖書（圖書編號，類型名稱，圖書名稱，作者，出版社，出版日期，ISBN，價格）訂單（訂單編號，用戶名，銷售額，訂購日期，出貨日期）訂單明細(xì)（訂單明細(xì)編號，訂單編號，圖書編號，數(shù)量）【問題1】（3分）下面是創(chuàng)建訂單關(guān)系的SQL語句，訂單編號唯一識別一個訂單，用戶名為訂購圖書的會員用戶名，且不能為空。要求訂購日期不能大于出貨日期。請

4、將空缺部分補充完整。CREATE TABLE 訂單(訂單編號 CHAR(6) （a），用戶名 VARCHAR(40) NOT NULL （b）， ,銷售額 FLOAT ,訂購日期 DATE NOT NULL ,出貨日期 DATE （c） ) ;【問題2】（10分）請完成下列查詢的SQL語句。（1）查詢名稱中包含“數(shù)據(jù)庫”的圖書的圖書名稱，作者，出版社和出版日期。SELECT （d）FROM 圖書WHERE 圖書名稱 （e） ;（2）查詢提供銷售（圖書表中有）但沒有銷售過（沒在訂單明細(xì)表中出現(xiàn)）的圖書名稱和出版社。SELECT 圖書名稱, 出版社FROM圖書WHERE NOT EXISTS (S

5、ELECT （f）FROM訂單明細(xì)WHERE （g） ) ;（3）查詢訂購圖書數(shù)量最多的會員名及其訂購的數(shù)量。SELECT 用戶名, （h）FROM訂單, 訂單明細(xì)WHERE （i）GROUP BY 用戶名HAVING （j）( SELECT SUM(數(shù)量)FROM 訂單, 訂單明細(xì)WHERE 訂單.訂單編號 = 訂單明細(xì).訂單編號GROUP BY 用戶名) ;（4）為了統(tǒng)計會員的購買行為信息，實施有意義的客戶關(guān)懷策略，查詢會員的平均訂購間隔時間，考慮多次購買圖書和一次購買圖書的情況（其中，DATEDIFF 函數(shù)表示兩個日期之間的天數(shù)）。SELECT 用戶名, CASE WHEN （k）THE

6、N DATEDIFF(MAX(訂購日期), MIN(訂購日期) / （l）ELSE DATEDIFF (CURRENT_TIMESTAMP, MIN(訂購日期)END AS AVG_GAPFROM 訂單（m） ;【問題3】（2分）會員訂購圖書后，將本次訂購的銷售額累加到該會員的消費額中，并按照本次訂單的銷售額計算積分累加到該會員的積分中（每20元增加1個積分，不足20元不計入積分）。下面用觸發(fā)器實現(xiàn)該需求，請?zhí)畛淇杖辈糠?。CREATE TRIGGER 會員積分_TRIGGER AFTER （n）REFERENCING NEW ROW AS NROWBEGINUPDATE 會員SET 消費額 =

7、 消費額 + NROW.銷售額, （o）WHERE 用戶名 = NROW.用戶名END試題三（共15分）閱讀下列說明，回答問題1至問題3，將解答填入答題紙的對應(yīng)欄內(nèi)?！驹囶}說明】某集團(tuán)公司擁有多個大型連鎖商場，公司需要構(gòu)建一個數(shù)據(jù)庫系統(tǒng)便于管理其業(yè)務(wù)運作活動?！拘枨蠓治鼋Y(jié)果】1. 商場需要記錄的信息包括商場編號（商場編號不重復(fù)）、商場名稱、地址和聯(lián)系電話。某商場信息如表3-1所示。2. 每個商場包含不同的部門，部門需要記錄的信息包括部門編號（不同商場的部門編號不同）、部門名稱、位置分布和聯(lián)系電話。某商場的部門信息如表3-2所示。3. 每個部門雇用了多名員工處理日常事務(wù)，每名員工只能屬于一個部門

8、（新進(jìn)員工在培訓(xùn)期不隸屬于任何部門）。員工需要記錄的信息包括員工編號、姓名、崗位、電話號碼和工資。員工信息如表3-3所示。4. 每個部門的員工中有一個是經(jīng)理，每個經(jīng)理只能管理一個部門。系統(tǒng)要記錄每個經(jīng)理的任職時間。 【概念模型設(shè)計】根據(jù)需求階段收集的信息，設(shè)計的實體聯(lián)系圖和關(guān)系模式（不完整）如下：【關(guān)系模式設(shè)計】商場（商場編號，商場名稱，地址，聯(lián)系電話）部門（部門編號，部門名稱，位置分布，聯(lián)系電話， （a） ）員工（員工編號，姓名，崗位，電話號碼，工資， （b） ）經(jīng)理（ （c） ，任職時間）【問題1】（7分）根據(jù)問題描述，補充四個聯(lián)系，完善圖3-1的實體聯(lián)系圖。【問題2】（6 分）根據(jù)實體聯(lián)

9、系圖，將關(guān)系模式中的空（a）（c）補充完整，并分別給出部門、員工和經(jīng)理關(guān)系模式的主鍵和外鍵。【問題3】（2分）為了使商場有緊急事務(wù)時能聯(lián)系到輪休的員工，要求每位員工必須且只能登記一位緊急聯(lián)系人的姓名和聯(lián)系電話（假設(shè)不同員工可以登記相同的緊急聯(lián)系人）。中還需添加的實體是 （d） ，該實體與圖3-1中的員工關(guān)系存在（e）聯(lián)系。給出該實體的關(guān)系模式。試題四（共15分）閱讀下列說明，回答問題至問題3，將解答填入答題紙的對應(yīng)欄內(nèi)?！菊f明】M公司為某賓館設(shè)計賓館機票預(yù)訂系統(tǒng)，初步的需求分析結(jié)果如下：（1）客戶可以在提前預(yù)訂或直接入住時向賓館提供相關(guān)信息， 賓館登記的客戶信息包括：客戶編號，姓名，性別，類型

10、，身份證號，聯(lián)系方式，預(yù)訂日期，入住時間和離開時間等信息。其中類型字段說明客戶是普通客戶或 VIP 客戶，不同的客戶類型享受訂票的折扣額度不同。直接入住的客戶其預(yù)訂日期取空值。 （2）需要預(yù)訂機票的客戶應(yīng)填寫“機票預(yù)訂”表，提供飛行日期、航班號、出發(fā)時間、目的地等信息。賓館根據(jù)客戶訂票信息購票后，生成“客戶訂單”表，并根據(jù)客戶類型確定相應(yīng)的折扣額度。“機票預(yù)訂”和“客戶訂單”表如表4-1、4-2所示。【邏輯結(jié)構(gòu)設(shè)計】根據(jù)需求階段收集的信息，設(shè)計的關(guān)系模式如圖4-1所示。關(guān)系模式的主要屬性、含義及約束如表4-3所示。【問題1】（6分）對關(guān)系“客戶”，請回答以下的問題：（1）若選定（客戶編號, 預(yù)

11、訂日期）作主碼，未預(yù)訂而直接入住的客戶信息能否錄入客戶表？如不能，請說明原因。（2）對“客戶”關(guān)系增加一個流水號屬性作為主碼，“客戶”關(guān)系屬于第幾范式？還存在哪些問題？（3）將增加入住標(biāo)識屬性后的“客戶”關(guān)系分解為第三范式，分解后的關(guān)系名依次取客戶1、客戶2、。 【問題2】（6分）對關(guān)系“航班”，請回答以下問題：（1）列舉出“航班”關(guān)系中所有不屬于任何候選碼的屬性（非碼屬性）。（2）該關(guān)系模式可達(dá)到第幾范式？用不超過60個字的內(nèi)容敘述理由?！締栴}3】（3 分）對于沒有預(yù)訂客房或入住賓館的客戶，需要在 （a） 關(guān)系中修改其 （b） 屬性的值域，以滿足這類客戶在賓館預(yù)訂機票的需求。試題五（共15分

12、）閱讀下列說明，回答問題1至問題3，將解答填入答題紙的對應(yīng)欄內(nèi)?！菊f明】某停車場有多個入口和出口，車輛進(jìn)入時從入口處由系統(tǒng)查詢可用的停車位，從出口駛出時系統(tǒng)將其剛使用的車位標(biāo)記為空車位。假設(shè)實現(xiàn)停車場管理的偽指令如表5-1所示：根據(jù)上述描述，在入口處的偽代碼程序為：x = Get();IF x =NULL THEN return 0;Writ(x, 1);【 問題1】（3）若兩輛車在不同的入口處同時執(zhí)行上述代碼，會出現(xiàn)什么問題？（100字以內(nèi)描述）【問題2】（8分）為保證入口處偽代碼正確地并發(fā)執(zhí)行，引入共享鎖指令 SLock(T)和獨占鎖指令XLock(T)對表 T 進(jìn)行加鎖；Upgrade(

13、T)對表 T 所加的共享鎖升級為獨占鎖；解鎖指令Unlock(T)對表T 進(jìn)行解鎖。（1）請修改上述入口處的偽代碼程序，使其滿足2PL協(xié)議。（2）滿足2PL協(xié)議的入口處的偽代碼程序，在并發(fā)執(zhí)行時是否會產(chǎn)生死鎖?若是，給出一個產(chǎn)生死鎖的調(diào)度?！締栴}3】（4分）若停車位表的關(guān)系模式為：park(parkno, isused)，其中parkno為停車位號，isused為停車位標(biāo)志，0為空，1為非空。下面是用E-SQL實現(xiàn)的查詢空車位的函數(shù)Get()，請補全空缺處的代碼。SET TRANSACTION ISOLATION LEVEL SERIALIZABLEEXEC SQL DECLARE getbl

14、k CURSOR FOR（a） ;EXEC SQL OPEN getblk ;EXEC SQL FETCH getblk INTO :Hparkno; / Hparkno為已聲明的主變量IF SQLCA.sqlcode = 100 THENEXEC SQL CLOSE getblk ; Return NULL;ELSE（b） ;END IF試題中通用的表示規(guī)則關(guān)于概念模型、關(guān)系模式的標(biāo)記，試題中若無特殊聲明，將使用如下的通用標(biāo)記規(guī)則。1概念模型的標(biāo)記規(guī)則分別如圖1、圖2和圖3所示。其中：（1）實體類型用長方形表示，弱實體用雙線長方形表示。長方形內(nèi)為實體類型名稱。（2）實體類型間的聯(lián)系用菱形表示，弱聯(lián)系用雙線菱形表示，菱形內(nèi)寫上聯(lián)系的名稱，并在聯(lián)系與實體間的連線上注明