2021年上半年《數(shù)據(jù)庫(kù)系統(tǒng)工程師》案例分析真題

1、2021 年上半年《數(shù)據(jù)庫(kù)系統(tǒng)工程師》案例分析真題 年上半年《數(shù)據(jù)庫(kù)系統(tǒng)工程師》案例分析真題注意：圖片可根據(jù)實(shí)際需要調(diào)整大小 注意：圖片可根據(jù)實(shí)際需要調(diào)整大小卷面總分：5 分 答題時(shí)間：240 分鐘試卷題量：5 題 練習(xí)次數(shù)：3 次問(wèn)答題 （共 5 題，共 5 分） 1.某競(jìng)賽管理系統(tǒng)的部分?jǐn)?shù)據(jù)庫(kù)關(guān)系模式如下：選手: PLAYER(PnO Phame, Sex. Region, Tel),各屬性分別表示參賽選手的編號(hào)、姓名、性別、地區(qū)和

2、聯(lián)系電 話:競(jìng)賽項(xiàng)目: CONTEST(CnO, Chame, 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)一

3、個(gè)選手參加一項(xiàng)競(jìng)賽有一個(gè)名次和一-個(gè)積分，名次有 4 個(gè)取值(“一”“二” “三”無(wú)”)。另外，競(jìng)賽所 在城市不能為空。 根據(jù)以上描述，回答下列問(wèn)題，將 SQL 語(yǔ)句的空缺部分補(bǔ)充完整?！締?wèn)題 1】 (5 分) 請(qǐng)將下面創(chuàng)建選手參賽表 PC 的 SQL 語(yǔ)句補(bǔ)充完整，要求定義實(shí)體完整性約束、參照完整性約束，以及其他完整性約束。 CREATE TABLE PC( Pno CHAR(10) REFERENCES(a)(Pno),Cno CH

4、AR(3) REFERENCES(b)(Cno)， City CHAR(20)(c)Rank CHAR(20)(d) PointSMALLIT, (e)【問(wèn)題 2】(6 分) 查詢所有未參加正確答案： 本題解析： 【問(wèn)題 1】 （a）：PLAYER（b）：CONTEST （c）：NOT NULL （d）：CHECK(Rank in(’一’, ’二’, ’三’, ’無(wú)’))（e）：PRIMARY KEY (Pno,Cno) 【問(wèn)題

5、2】f: PLAYER g: not in h: pnoi: pc.cno=contest.cno j: and k: order by【問(wèn)題 3】l：delete m：PC n：deleteo：CONTEST2.某數(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èn)題 1】 (6 分) 假設(shè)各數(shù)據(jù)項(xiàng)的初始值為: 1-22, J=45, K-9,系統(tǒng)出錯(cuò)

6、恢復(fù)后，I,J,K 的數(shù)值會(huì)恢復(fù)為多少？【問(wèn)題 2】 (4 分) 請(qǐng)給出系統(tǒng)恢復(fù)時(shí)需要重做(Redo)的事務(wù)列表和需要撤銷(undo)的事務(wù)列表。 【問(wèn)題 3】 (5 分)假設(shè)掉電造成磁盤介質(zhì)損壞，數(shù)據(jù)庫(kù)無(wú)法啟動(dòng)，請(qǐng)用 100 字以內(nèi)的文字簡(jiǎn)要說(shuō)明其恢復(fù)過(guò)程。正確答案： 本題解析： 【問(wèn)題 1】 I,J,K 的數(shù)值會(huì)恢復(fù)為 I=3,J=5,K=9 【問(wèn)題 2】4.為防控新冠疫情，一些公共設(shè)施需要定期消毒，管理部門為高效完成

7、工作并記承必要的工作信息，設(shè)計(jì)了相應(yīng)的數(shù)據(jù)庫(kù)，其中有一個(gè)表用來(lái)記承公共汽車的消毒情況，表的結(jié)構(gòu)如下：消毒記錄(日期，車牌號(hào)，行駛路線，消毒人員工號(hào)，消毒人員姓名)其中車牌號(hào)和消毒人員工號(hào)唯一,同輛車保持固定的行駛路線。假設(shè)同一人員每天可以負(fù)責(zé)多輛車的消毒工作。 【問(wèn)題 1】(6 分)給出消毒記錄表中成立且左側(cè)只有一個(gè)屬性的所有函數(shù)依賴關(guān)系。題中設(shè)計(jì)的消毒記錄表是否滿足 2NF請(qǐng)用 100 字以內(nèi)的文字說(shuō)明原因?！締?wèn)題 2】 (6 分)

8、如果要將消毒記錄表規(guī)范化為滿足 3NF,請(qǐng)用 100 字以內(nèi)的文字簡(jiǎn)要說(shuō)明解決方案，并給出各個(gè)新表的 主碼和外碼?！締?wèn)題 3】 (3 分) 如果每輛車每日有多次消毒，需要記錄每次消毒的消毒時(shí)間，在問(wèn)題 2 設(shè)計(jì)結(jié)果的基礎(chǔ)上，如何在不破 壞 3NF 且不增加冗余的前提下做到？請(qǐng)簡(jiǎn)單說(shuō)明方案。正確答案： 本題解析： 【問(wèn)題 1】 左側(cè)只有一個(gè)屬性的函數(shù)依賴關(guān)系有 2 個(gè)，分別為：車牌號(hào)->行駛路線 消毒人員工號(hào)->消毒人員姓

9、名 不滿足 2NF，根據(jù)題干可以得到消毒記錄表的候選鍵為（日期，車牌號(hào)） ，由于存在 車牌號(hào)->行駛路線，屬于部分依賴。根據(jù) 2NF 的要求，不存在非主數(shù)據(jù)對(duì)主屬性的部分依賴，所以不滿足 2NF。 【問(wèn)題 2】由于消毒記錄表不滿足 2NF，需要將消毒記錄表進(jìn)行拆分成多個(gè)表，拆分的表如下： 消毒記錄 1（車牌號(hào)，行駛路線） 消毒記錄 2（消毒人員工號(hào)，消毒人員姓名）消毒記錄 3（車牌號(hào)，日期，消毒人員工號(hào)） 由于拆分后的三個(gè)表都不

10、存在非主屬性對(duì)主屬性的部分和傳遞函數(shù)依賴，所以都滿足 3NF。5.某企業(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 n

11、ums, book price, order date,amount)，其中屬性分別為:訂單編碼、書籍編碼、書籍?dāng)?shù)量、書籍價(jià)格、訂單日期和總金額。書籍再購(gòu)額度表: booklimit(book no, sale_ type, limitamount),其中屬性含義分別為: 書籍編碼、銷售分類、再購(gòu)額度；書籍最低庫(kù)存表: bookminlevel(book no, leve) ，其中屬性含義分別為:書籍編碼，書籍最低庫(kù)存數(shù)量；書籍采購(gòu)表:

12、 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ù)

13、量時(shí)，根據(jù)書籍的銷售分類以及書籍再購(gòu)額度表中的再購(gòu)額度，生成書籍采購(gòu)表中的采購(gòu)訂單，完成自動(dòng)補(bǔ)貨操作。 【問(wèn)題 1】(5 分)系統(tǒng)定期掃描書籍銷售訂單表，根據(jù)書籍總的銷售情況來(lái)確定書籍的銷售類別。下面是系統(tǒng)中設(shè)置某書籍銷售類別的存儲(chǔ)過(guò)程，結(jié)束時(shí)需顯式提交返回。請(qǐng)補(bǔ)全空缺處的代碼。 CREATE PROCEDURE UpdateBookSaleType(IN bno varchar(20))DECLAREall_nums number(6

14、); BEGINSELECT (a) (book_nums) INTO all_nums FROM orders WHERE book_no = (b) ; IF all_nums ＜ (c) THENUPDATE books SET sale_type = 0 WHERE book_no = bno; ELSEUPDATE books SET sale_type = (d) WHERE book_no = bno; END IF; (