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

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

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

某社區(qū)蔬菜團(tuán)購(gòu)網(wǎng)站，為規(guī)范商品收發(fā)流程，便于查詢客戶訂單情況，需要開發(fā)個(gè)信息系統(tǒng)。請(qǐng)根據(jù)下述需求描述完成該系統(tǒng)的數(shù)據(jù)庫(kù)設(shè)計(jì)。

【需求描述】

(1)記錄蔬菜供應(yīng)商的信息，包括供應(yīng)商編號(hào)、地址和一個(gè)電話。

(2)記錄社區(qū)團(tuán)購(gòu)點(diǎn)的信息，包括團(tuán)購(gòu)點(diǎn)編號(hào)、地址和一個(gè)電話。

(3)記錄客戶信息，包括客戶姓名和一個(gè)電話?？蛻艨梢栽诓煌纳鐓^(qū)團(tuán)購(gòu)點(diǎn)下訂單，不直接與蔬菜供應(yīng)商發(fā)生聯(lián)系。

(4)記錄客戶訂單信息，包括訂單編號(hào)、團(tuán)購(gòu)點(diǎn)編號(hào)、客戶電話、訂單內(nèi)容和日期。

【概念模型設(shè)計(jì)】根據(jù)需求階段收集的信息，設(shè)計(jì)的實(shí)體聯(lián)系圖（不完整）如圖1-1所示。

【問題1】（6分）

根據(jù)需求描述，補(bǔ)充圖1-1的實(shí)體聯(lián)系圖。

【問題2】（4分）

補(bǔ)充下列邏輯結(jié)構(gòu)設(shè)計(jì)中的（a）、（b）兩處空缺，并描述完整性約束關(guān)系。

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

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

蔬菜供貨商(供貨商編號(hào)，地址，電話)

社區(qū)團(tuán)購(gòu)點(diǎn)(團(tuán)購(gòu)點(diǎn)編號(hào)，地址，電話)

供貨(供貨商編號(hào)，(a))

客戶(姓名，客戶電話)

訂單(訂單編號(hào)，團(tuán)購(gòu)點(diǎn)編號(hào)，(b)，訂單內(nèi)容，日期)

【問題3】（5分）

若社區(qū)蔬菜團(tuán)購(gòu)網(wǎng)站還兼有代收快遞的業(yè)務(wù)，請(qǐng)?jiān)黾有碌摹翱爝f”實(shí)體，并給出客戶實(shí)體和快遞實(shí)體之間的“收取”聯(lián)系，對(duì)圖1-1進(jìn)行補(bǔ)充?！翱爝f”關(guān)系模式包括快遞編號(hào)、客戶電話和日期

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

【說(shuō)明】

為防控新冠疫情，一些公共設(shè)施需要定期消毒，管理部門為高效完成工作并記錄必要的工作信息，設(shè)計(jì)了相應(yīng)的數(shù)據(jù)庫(kù)，其中有一個(gè)表用來(lái)記錄公共汽車的消毒情況，表的結(jié)構(gòu)如下:

消毒記錄(日期，車牌號(hào)，行駛路線，消毒人員工號(hào)，消毒人員姓名)

其中車牌號(hào)和消毒人員工號(hào)唯一，同一輛車保持固定的行駛路線。假設(shè)同一人員每天可以負(fù)責(zé)多輛車的消毒工作。

請(qǐng)回答以下問題。

【問題1】（6分）

給出消毒記錄表中成立且左側(cè)只有一個(gè)屬性的所有函數(shù)依賴關(guān)系。題中設(shè)計(jì)的消毒記錄表是否滿足2NF請(qǐng)用100字以內(nèi)的文字說(shuō)明原因。

【問題2】（6分）

如果要將消毒記錄表規(guī)范化為滿足3NF，請(qǐng)用100字以內(nèi)的文字簡(jiǎn)要說(shuō)明解決方案，并給出各個(gè)新表的主碼和外碼。

【問題3】（3分）

如果每輛車每日有多次消毒，需要記錄每次消毒的消毒時(shí)間，在問題2設(shè)計(jì)結(jié)果的基礎(chǔ)上，如何在不破壞3NF且不增加冗余的前提下做到？請(qǐng)簡(jiǎn)單說(shuō)明方案。

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

【說(shuō)明】

某競(jìng)賽管理系統(tǒng)的部分?jǐn)?shù)據(jù)庫(kù)關(guān)系模式如下:

選手：PLAYER(Pno,Pname,Sex,Region,Tel),各屬性分別表示參賽選手的編號(hào)、姓名、性別、地區(qū)和聯(lián)系電話；

競(jìng)賽項(xiàng)目:CONTEST(Cno,Cname,Type,Date),各屬性分別表示競(jìng)賽項(xiàng)目的編號(hào)、名稱、類別和舉辦日期；

選手參賽：PC(Pno,Cno,City,Rank,Point),各屬性分別表示選手編號(hào)、競(jìng)賽項(xiàng)目編號(hào)、競(jìng)賽所在城市、選手取得的名次和積分。

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

(1)下劃線標(biāo)出的屬性是表的主碼。

(2)選手參賽表的屬性Pno和Cno分別參照了選手表和競(jìng)賽項(xiàng)目表的主碼。

(3)一個(gè)選手參加一項(xiàng)競(jìng)賽有一個(gè)名次和一個(gè)積分，名次有4個(gè)取值(“一”、“二”、“三”、“無(wú)”)。另外，競(jìng)賽所在城市不能為空。

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

【問題1】（5分）

請(qǐng)將下面創(chuàng)建PC的SQL語(yǔ)句補(bǔ)充完整，要求定義實(shí)體完整性約束、參照完整性約束，以及其他完整性約束。

CREATETABLEPC(

PnoCHAR(10)REFERENCES(a)(Pno),

CnoCHAR(3)REFERENCES(b)(Cno),

CityCHAR(20)(c)，

RankCHAR(20)(d)，

PointSMALLIT,

（e））；

【問題2】（6分）

查詢所有未參加’AI’類別競(jìng)賽的選手，要求輸出選手的編號(hào)(Pno)，查詢結(jié)果按照選手編號(hào)的升序排列。此功能由下面的SQL語(yǔ)句實(shí)現(xiàn)，請(qǐng)補(bǔ)全。

SELECTPnoFROM(f)_

WHEREPno(g)(

SELECT(h)FROMPC,Contest

WHERE(i)_

(j)Type=’AI’)

(k)Pon;

【問題3】（4分）

由于某種原因，編號(hào)為’TE06’的競(jìng)賽項(xiàng)目在正式舉辦前被取消了。而此前系統(tǒng)中已經(jīng)記錄了些選手的報(bào)名參賽情況，因此需要在系統(tǒng)中刪除’E06'的競(jìng)賽項(xiàng)目記錄，以及該競(jìng)賽的所有報(bào)名參賽紀(jì)錄。根據(jù)問題1在選手參賽表PC上定義的參照完整性約束，此功能可以由下面的SQL語(yǔ)句實(shí)現(xiàn)，請(qǐng)補(bǔ)全。

(l)FROM(m)WHERECno=‘E06’;

(n)FROM(o)WHERECno=‘E06’;

4.【問答題】10分|試題四（共15分）

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

【說(shuō)明】

某企業(yè)網(wǎng)上書城系統(tǒng)的部分關(guān)系模式如下：

書籍信息表:books(book_no,book_name,press_no,ISBN,price,sale_type,all_nums),其中屬性含義分別為:書籍編碼、書籍名稱、出版商編碼、ISBN、銷售價(jià)格、銷售分類、當(dāng)前庫(kù)存數(shù)量；

書籍銷售訂單表:orders(order_no,book_no,book_nums,book_price,order_date,amount),其中屬性分別為:訂單編碼、書籍編碼、書籍?dāng)?shù)量、書籍價(jià)格、訂單日期和總金額；

書籍再購(gòu)額度表:booklimit(book_no,sale_type,limit_amount),其中屬性含義分別為:書籍編碼、銷售分類、再購(gòu)額度；

書籍最低庫(kù)存表:bookminlevel(book_no,leve),其中屬性含義分別為:書籍編碼，書籍最低庫(kù)存數(shù)量；

書籍采購(gòu)表:bookorders(book_no,order_amount),其中屬性含義分別為:書籍編碼和采購(gòu)數(shù)量。

有關(guān)關(guān)系模式的說(shuō)明如下：

(1)下劃線標(biāo)出的屬性是表的主碼。

(2)根據(jù)書籍銷售情況來(lái)確定書籍的銷售分類:銷售數(shù)量小于1萬(wàn)的為普通類型，其值為0;1萬(wàn)及以上的為熱銷類型，其值為1

(3)系統(tǒng)具備書籍自動(dòng)補(bǔ)貨功能，涉及到的關(guān)系模式有:書籍再購(gòu)額度表、書籍最低庫(kù)存表、書籍采購(gòu)表。其業(yè)務(wù)邏輯是:當(dāng)某書籍庫(kù)存小于其最低庫(kù)存數(shù)量時(shí)，根據(jù)書籍的銷售分類以及書籍再購(gòu)額度表中的再購(gòu)額度，生成書籍采購(gòu)表中的采購(gòu)訂單，完成自動(dòng)補(bǔ)貨操作。

【問題1】（5分）

系統(tǒng)定期掃描書籍銷售訂單表，根據(jù)書籍總的銷售情況來(lái)確定書籍的銷售類別。下面是系統(tǒng)中設(shè)置某書籍銷售類別的存儲(chǔ)過(guò)程，結(jié)束時(shí)需顯式提交返回。請(qǐng)補(bǔ)全空缺處的代碼。

CREATEPROCEDUREUpdateBookSaleType(INbnovarchar(20))

DECLARE

all_numsnumber(6);

BEGIN

SELECT(a)（book_nums)INTOall_numsFROMorders

WHEREbook_no=(b)；

IFall_nums(c)THEN

UPDATEbooksSETsale_type=0WHEREbook_no=bno;

ELSE

UPDATEbooksSETsale_type=(d)WHEREbook_no=bno;

ENDIF;

(e)；

END;

【問題2】（6分）

下面是系統(tǒng)中自動(dòng)補(bǔ)貨功能對(duì)應(yīng)的觸發(fā)器，請(qǐng)補(bǔ)全空缺處的代碼。

CREATETRIGGERBookOrdersTrigger（f)update

Of（g)onbooks

（h)_

WHEN（i)＜(SELECTlevelFROMbookminlevel

WHEREbookminlevel.book_no=OLD.book_no)

AND（j)＞＝(SELECTlevelFROMbookminlevel

WHEREbookminlevel.book_no=OLD.book_no)

BEGIN

INSERTINTO（k)_

（SELECTbook_no,limit_amount

FROMbooklimitasTMP

WHERETMP.book_no=OLD.book_no

ANDTMP.sale_type=OLD.sale_type);

END;

【問題3】（4分）

假設(shè)用戶1和用戶2同時(shí)購(gòu)買同一書籍，對(duì)應(yīng)事務(wù)的部分調(diào)度序列如表4-1所示(事務(wù)中未進(jìn)行并發(fā)控制)，其中T0時(shí)刻該書籍的庫(kù)存數(shù)量all_nums=500。

表4-1事務(wù)運(yùn)行部分調(diào)度示意表

請(qǐng)說(shuō)明T4時(shí)刻，用戶2事務(wù)讀取到的all_nums數(shù)值是多少?

請(qǐng)說(shuō)明T8時(shí)刻，all_nums數(shù)據(jù)是否出現(xiàn)不一致性問題?如出現(xiàn)，請(qǐng)說(shuō)明屬于哪一種數(shù)據(jù)不一致性。

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

【說(shuō)明】

某數(shù)據(jù)庫(kù)系統(tǒng)采用數(shù)據(jù)轉(zhuǎn)儲(chǔ)方式對(duì)數(shù)據(jù)和日志文件進(jìn)行離線備份，用檢查點(diǎn)機(jī)制進(jìn)行恢復(fù)。假設(shè)部分其日志文件如表5-1所示。

日志記錄內(nèi)容中：表示事務(wù)Ti開始執(zhí)行，表示事務(wù)Ti提交，表示事務(wù)Ti將數(shù)據(jù)項(xiàng)D的值由V1修改為V2。例如：表示事務(wù)T1將數(shù)據(jù)項(xiàng)I的值由22修