2019年上半年 數(shù)據(jù)庫(kù)系統(tǒng)工程師 下午試卷 案例

1、2019 年上半年 數(shù)據(jù)庫(kù)系統(tǒng)工程師 下午試卷 案例【說(shuō)明】學(xué)校欲開(kāi)發(fā)一學(xué)生跟蹤系統(tǒng)，以更自動(dòng)化、更全面地對(duì)學(xué)生在校情況（到課情況和健康狀態(tài)等相關(guān)信息）進(jìn)行管理和追蹤，使家長(zhǎng)能及時(shí)了解子女的到課情況和健康狀態(tài)，并在有健康問(wèn)題時(shí)及時(shí)與醫(yī)護(hù)機(jī)構(gòu)對(duì)接。該系統(tǒng)的主要功能是：采集學(xué)生狀態(tài)。通過(guò)學(xué)生卡傳感器，采集學(xué)生心率、體溫（攝氏度）等健康指標(biāo)及其所在位置等信息并記錄。每張學(xué)生卡有唯一的標(biāo)識(shí)（ID）與一個(gè)學(xué)生對(duì)應(yīng)。健康狀態(tài)告警。在學(xué)生健康狀態(tài)出現(xiàn)向題時(shí)，系統(tǒng)向班主任、家長(zhǎng)和醫(yī)護(hù)機(jī)構(gòu)健康服務(wù)系統(tǒng)發(fā)出健康狀態(tài)警告，由醫(yī)護(hù)機(jī)構(gòu)健康服務(wù)系統(tǒng)通知相關(guān)醫(yī)生進(jìn)行處理。到課檢查。綜合比對(duì)學(xué)生狀態(tài)、課表以及所處校園場(chǎng)所之

2、間的信息對(duì)學(xué)生到課情況進(jìn)行判定。對(duì)曠課學(xué)生，向其家長(zhǎng)和班主任發(fā)送曠課警告。在校情況匯總。定期匯總在校情況，并將報(bào)告發(fā)送給家長(zhǎng)和班主任。家長(zhǎng)注冊(cè)。家長(zhǎng)注冊(cè)使用該系統(tǒng)，指定自己子女，經(jīng)學(xué)校管理人員審核后，向家長(zhǎng)發(fā)送注冊(cè)結(jié)果?；A(chǔ)信息管理。學(xué)校管理人員對(duì)學(xué)生及其所用學(xué)生卡和班主任 、課表（班級(jí)、上課時(shí)間及場(chǎng)所等）、校園場(chǎng)所（名稱和所在位置區(qū)域）等基礎(chǔ)信息進(jìn)行管理，對(duì)家長(zhǎng)注冊(cè)申請(qǐng)進(jìn)行審核，將家長(zhǎng) ID 加入學(xué)生信息記錄中使家長(zhǎng)與其子女進(jìn)行關(guān)聯(lián)，一個(gè)學(xué)生至少有一個(gè)家長(zhǎng)， 可以有多個(gè)家長(zhǎng)。課表信息包括班級(jí)、班主任、時(shí)間和位置等?，F(xiàn)采用結(jié)構(gòu)化方法對(duì)學(xué)生跟蹤系統(tǒng)進(jìn)行分析與設(shè)計(jì)，獲得如圖 1-1 所示的上下文數(shù)

3、據(jù)流圖和圖 1-2 所示的 0 層數(shù)據(jù)流圖。問(wèn)題：1.1（5 分）使用說(shuō)明中的詞語(yǔ)，給出圖 1-1 中的實(shí)體 E1E5 的名稱。問(wèn)題：1.2（4 分）使用說(shuō)明中的詞語(yǔ)，給出圖 1-2 中的數(shù)據(jù)存儲(chǔ) D1 D4 的名稱。問(wèn)題：1.3（3 分）根據(jù)說(shuō)明和圖中術(shù)語(yǔ)，補(bǔ)充圖 1-2 中缺失的數(shù)據(jù)流及其起點(diǎn)和終點(diǎn)（三條即可）。問(wèn)題：1.4（3 分）根據(jù)說(shuō)明中術(shù)語(yǔ)，說(shuō)明圖 1-1 中數(shù)據(jù)流學(xué)生狀態(tài)和學(xué)生信息的組成?！菊f(shuō)明】某創(chuàng)業(yè)孵化基地管理若干孵化公司和創(chuàng)業(yè)公司，為規(guī)范管理創(chuàng)業(yè)項(xiàng)目投資業(yè)務(wù)，需要開(kāi)發(fā)一個(gè)信息系統(tǒng)。請(qǐng)根據(jù)下述需求描述完成該系統(tǒng)的數(shù)據(jù)庫(kù)設(shè)計(jì)。【需求描述】記錄孵化公司和創(chuàng)業(yè)公司的信息。孵化公司信

4、息包括公司代碼、公司名稱、法人代表名稱、注冊(cè)地址和一個(gè)電話；創(chuàng)業(yè)公司信息包括公司代碼、公司名稱和一個(gè)電話。孵化公司和創(chuàng)業(yè)公司的公司代碼編碼不同。統(tǒng)一管理孵化公司和創(chuàng)業(yè)公司的員工。員工信息包括工號(hào)、身份證號(hào)、姓名、性別、所屬公司代碼和一個(gè)手機(jī)號(hào)，工號(hào)唯一只每位員工。記錄投資方信息，投資方信息包括投資方編號(hào)、投資方名稱和一個(gè)電話。投資方和創(chuàng)業(yè)公司之間依靠孵化公司牽線建立創(chuàng)業(yè)項(xiàng)目合作關(guān)系，具體實(shí)施由孵化公司的一位員工負(fù)責(zé)協(xié)調(diào)投資方和創(chuàng)業(yè)公司的一個(gè)創(chuàng)業(yè)項(xiàng)目。一個(gè)創(chuàng)業(yè)項(xiàng)目只屬于一個(gè)創(chuàng)業(yè)公司，但可以接受若干投資方的投資。創(chuàng)業(yè)項(xiàng)目信息包括項(xiàng)目編號(hào)、創(chuàng)業(yè)公司代碼、 投資方編號(hào)和孵化公司員工工號(hào)?！靖拍钅Ｐ驮O(shè)計(jì)

5、】根據(jù)需求階段收集的信息，設(shè)計(jì)的實(shí)體聯(lián)系圖（不完整）如圖2-1 所示?！具壿嫿Y(jié)構(gòu)設(shè)計(jì)】根據(jù)概念模型設(shè)計(jì)階段完成的實(shí)體聯(lián)系圖，得出如下關(guān)系模式（不完整）: 孵化公司（公司代碼,公司名稱，法人代表名稱，注冊(cè)地址， 電話）創(chuàng)業(yè)公司（公司代碼，公司名稱，電話）員工（工量，身份證號(hào)，姓名，性別，（a），手機(jī)號(hào)）投資方（投資方編號(hào)、投資方名稱，電話）項(xiàng)目（項(xiàng)目編號(hào),創(chuàng)業(yè)公司代碼，（b），孵化公司員工工號(hào)）問(wèn)題：2.1（5 分）根據(jù)問(wèn)題描述，補(bǔ)充圖 2-1 的實(shí)體聯(lián)系圖。問(wèn)題：2.3（6 分）問(wèn)題：2.2（4 分）補(bǔ)充邏輯結(jié)構(gòu)設(shè)計(jì)結(jié)果中的（a）、（b）兩處空缺及完整性約束關(guān)系。是否需要增加新的實(shí)體來(lái)存儲(chǔ)投資

6、額和投資時(shí)間？如果增加新的實(shí)體，請(qǐng)給出新實(shí)體的關(guān)系模式，并對(duì)圖 2-1 進(jìn)行補(bǔ)充。如果不需要增加新的實(shí)若創(chuàng)業(yè)項(xiàng)目的信息還需要包括投資額和投資時(shí)間，那么： 體，請(qǐng)將“投資額”和“投資時(shí)間”兩個(gè)屬性補(bǔ)充并連線到圖 2-1 合適的對(duì)象上，并對(duì)變化的關(guān)系模式進(jìn)行修改。【說(shuō)明】某快遞公司對(duì)每個(gè)發(fā)出的快遞進(jìn)行跟蹤管理，需要建立一個(gè)快遞跟蹤管理系統(tǒng)，對(duì)該公司承接的快遞業(yè)務(wù)進(jìn)行有效管理?！拘枨竺枋觥抗驹诿總€(gè)城市的每個(gè)街道都設(shè)有快遞站點(diǎn)。這些站點(diǎn)負(fù)責(zé)快遞的接收和投遞。站點(diǎn)信息包括站點(diǎn)地址、站點(diǎn)名稱、責(zé)任人、一部聯(lián)系電話、開(kāi)始營(yíng)業(yè)時(shí)間、結(jié)束營(yíng)業(yè)時(shí)間。每個(gè)站點(diǎn)每天的營(yíng)業(yè)時(shí)間相同。每個(gè)站點(diǎn)只能有一個(gè)責(zé)任人。系統(tǒng)內(nèi)需

7、記錄快遞員、發(fā)件人的基本信息。這些信息包括姓名、身份證號(hào)、一個(gè)聯(lián)系地址、一部聯(lián)系電話。快遞站點(diǎn)的責(zé)任人由快遞員兼任，且每個(gè)快遞站點(diǎn)只有一個(gè)責(zé)任人。每個(gè)快遞員只負(fù)責(zé)一個(gè)快遞站點(diǎn)的攬件和快遞派送業(yè)務(wù)。發(fā)件人和快遞員需實(shí)名認(rèn)證?？爝f需要提供詳實(shí)的信息，包括發(fā)件人姓名、身份證號(hào)、一部發(fā)件人電話號(hào)碼、發(fā)件人地址、收件站點(diǎn)、收件人姓名、收件地址、一部收件人電話、投遞時(shí)間、物品類別、物品名稱及物品價(jià)值。每個(gè)發(fā)件人和收件人在系統(tǒng)里只能登記一個(gè)電話和地址。每個(gè)快遞員接手一份快遞后，需在系統(tǒng)中錄入每個(gè)快遞的當(dāng)前狀態(tài)信息，包括當(dāng)前位置、收到時(shí)間、當(dāng)前快遞員和上一段快遞員。狀態(tài)信息包括待攬件、投遞中、已簽收。如果快遞

8、已簽收，應(yīng)記錄簽收人姓名及一個(gè)聯(lián)系電話。每個(gè)快遞在一個(gè)站點(diǎn)只能對(duì)應(yīng)一個(gè)負(fù)責(zé)的快遞員。注：試題不需要考慮快遞退回的相關(guān)問(wèn)題?！具壿嫿Y(jié)構(gòu)設(shè)計(jì)】根據(jù)上訴需求需求，設(shè)計(jì)出如下關(guān)系模式：快遞（快遞編號(hào)，收件人姓名，收件地址編號(hào)，收件人電話，投遞時(shí)間，物品類別，物品名稱，物品價(jià)值），其中收件地址編號(hào)是地址實(shí)體的地址編號(hào)。快遞員（姓名，身份證號(hào),電話號(hào)碼，聯(lián)系地址編號(hào)，工作站點(diǎn)編號(hào)） 快遞站點(diǎn)（站點(diǎn)編號(hào)，站點(diǎn)名稱，責(zé)任人編號(hào)，站點(diǎn)地址編號(hào)，開(kāi)始營(yíng)業(yè)時(shí)間，聯(lián)系電話，結(jié)束營(yíng)業(yè)時(shí)間）。責(zé)任人編號(hào)是負(fù)責(zé)該站點(diǎn)的快遞員的身份證號(hào)。地址（地址編號(hào)，所在省，所在市，所在街道，其他），其他信息是需補(bǔ)充的地址信息。快遞投遞（

9、快遞編號(hào)，快遞員編號(hào)，發(fā)件人姓名，發(fā)件人身份證號(hào)， 發(fā)件人電話號(hào)碼，發(fā)件人地址編號(hào)），其中發(fā)件人地址編號(hào)為發(fā)件人地址的地址編號(hào)，攬件站點(diǎn)編號(hào)為接收該快遞的站點(diǎn)編號(hào)?？爝f跟蹤（快遞編號(hào)，當(dāng)前快遞員編號(hào)，上一段快遞員編號(hào)，當(dāng)前狀態(tài)，收到時(shí)間，當(dāng)前站點(diǎn)編號(hào)）。快遞簽收（快遞編號(hào),簽收人姓名，簽收人聯(lián)系電話）。問(wèn)題：3.1(6 分)對(duì)關(guān)系“快遞投遞”，請(qǐng)回答以下問(wèn)題：列舉出所有候選鍵。它是否為 3NF,用 100 字以內(nèi)文字簡(jiǎn)要敘述理由。將其分解為 BCNF, 分解后的關(guān)系名依次為：快遞投遞 1,快遞投遞 2，并用下劃線標(biāo)示分解后各關(guān)系模式的主鍵。問(wèn)題：3.2(6 分)對(duì)關(guān)系“快遞跟蹤”，請(qǐng)回 以下問(wèn)

10、題：列舉出所有候選鍵。它是否為 2NF ，用 100 字以內(nèi)文字簡(jiǎn)要敘述理由。(3) 將其分解為 BCNF ，分解后的關(guān)系名依次為：快遞跟蹤 1，快遞跟蹤 2，并用下劃線標(biāo)示分解后各關(guān)系模式的主鍵。問(wèn)題：3.3(3 分)快遞公司會(huì)根據(jù)快遞物品和距離收取快遞費(fèi)，每件快遞需由發(fā)件人或收件人支付快遞費(fèi)給公司。同一個(gè)發(fā)件人同時(shí)發(fā)起多個(gè)快遞，必須分別支付?？爝f公司提供預(yù)支付和到付兩種支付方式。為了統(tǒng)計(jì)快遞費(fèi)的支付情況(詳細(xì)金額和時(shí)間)，試增加快遞費(fèi)支付關(guān)系模式，用 100 字以內(nèi)文字簡(jiǎn)要敘述解決方案?！菊f(shuō)明】某學(xué)生信息管理系統(tǒng)的部分?jǐn)?shù)據(jù)庫(kù)關(guān)系模式如下：學(xué)生：Student ( stuno, stunam

11、e, stuage, stusex, schno),各屬性分別表示學(xué)生的學(xué)號(hào)、姓名、年齡、性別，以及學(xué)生所屬學(xué)院的編號(hào)；學(xué)院：School ( schno， schname, schstunum ),各屬性分別表示學(xué)院的編號(hào)、名稱及學(xué)生人數(shù)；俱樂(lè)部：Club (clubno,clubname, clubyear, clubloc ),各屬性分別表示俱樂(lè)部的編號(hào)、名稱、成立年份和活動(dòng)地點(diǎn)；參加：JoinClub ( stuno, clubno. joinyear ),各屬性分別表示學(xué)號(hào)、俱樂(lè)部編號(hào)，以及學(xué)生加入俱樂(lè)部的年份。有關(guān)關(guān)系模式的說(shuō)明如下：學(xué)生的性別取值為F和M (F 表示女性，M 表示

12、男性)。刪除一個(gè)學(xué)院的記錄時(shí)，通過(guò)外鍵約束級(jí)聯(lián)刪除該學(xué)院的所有學(xué)生記錄。學(xué)院表中的學(xué)生人數(shù)值與學(xué)生表中的實(shí)際人數(shù)要完全保持一致。也就是說(shuō)，當(dāng)學(xué)生表中增減記錄時(shí)，就要自動(dòng)修改相應(yīng)學(xué)院的人數(shù)。問(wèn)題：4.1(4 分)請(qǐng)將下面創(chuàng)建學(xué)生表的 SQL 語(yǔ)句補(bǔ)充完整，要求定義實(shí)體完整性約束、參照完整性約束，以及其他完整性約束。CREATE TABLE Student (stuno CHAR(ll)（ ）， stuname VARCHAR（ ）, stuage SMALLINT, stusex CHAR(l)（ ），schno CHAR(3) （ ） ON DELETE（ ）)；問(wèn)題：4.2(5 分)創(chuàng)建倶樂(lè)

13、部人數(shù)視圖，能統(tǒng)計(jì)每個(gè)倶樂(lè)部已加入學(xué)生的人數(shù)，屬性有 clubno clubname 和clubstunum。對(duì)于暫時(shí)沒(méi)有學(xué)生參加的俱樂(lè)部，其人數(shù)為 0。此視圖的創(chuàng)建語(yǔ)句如下，請(qǐng)補(bǔ)全。CREATE VIEW CS_NUMBER ( clubno, clubname, clubstunum ) ASSELECT JoinClub.clubno,（ ） , （ ） FROM JoinClub, ClubWHERE JoinClub.clubno = Club.clubno（ ） BY JoinClub.clubno（ ）SELECT clubno, clubname, 0 FROM Club WH

14、ERE clubno NOT 04(SELECT DISTINCT clubno FROM（ ）);問(wèn)題：4.3(4 分)每當(dāng)系統(tǒng)中新加或刪除一個(gè)學(xué)生，就需要自動(dòng)修改相應(yīng)學(xué)院的人數(shù)，以便保持系統(tǒng)中學(xué)生人數(shù)的完整性與一致性。此功能由下面的觸發(fā)器實(shí)現(xiàn)，請(qǐng)補(bǔ)全。CREATE TRIGGER STU_NUM_TRGAFTER INSERT OR DELETE ON（ ）REFERENCING new row AS nrow, old row AS orow FOR EACH（ ） BEGINIF INSERTING THENUPDATE School（ ） END IF;IF DELETING THE

15、NUPDATE School（ ）; END IF;END;問(wèn)題：4.4(2 分)查詢年齡小于 19 歲的學(xué)生的學(xué)號(hào)、姓名及所屬學(xué)院名，要求輸出結(jié)果把同一個(gè)學(xué)院的學(xué)生排在一起。此功能由下面的 SQL 語(yǔ)句實(shí)現(xiàn)，請(qǐng)補(bǔ)全。SELECT stuno, stuname, schname FROM Student, School WHERE Student.schno = School.schnoAND stuage 發(fā)件人身份證號(hào)，發(fā)件人身份證號(hào)-（發(fā)件人姓名，發(fā)件人電話號(hào)碼，發(fā)件人地址編號(hào)），所以不滿足 3NF。分解后的關(guān)系模式：快遞投遞 1（發(fā)件人身份證號(hào)，發(fā)件人姓名，發(fā)件人電話號(hào)碼，發(fā)件人地址編

16、號(hào)）快遞投遞 2（快遞編號(hào)，快遞員編號(hào)，發(fā)件人身份證號(hào)） 注：根據(jù)題干有遺漏，所以還可增加快遞投遞 3（快遞員編號(hào)，攬件站點(diǎn)編號(hào)）答案解析：（1）候選碼：（快遞編號(hào)，收到時(shí)間）滿足 2NF。所有非主屬性都完全依賴于碼，滿足 2NF。快遞跟蹤 1（快遞編號(hào)，收到時(shí)間，當(dāng)前狀態(tài)）；快遞跟蹤 2（快遞編號(hào)，收到時(shí)間，當(dāng)前快遞員編號(hào)，上一段快遞員編號(hào)）快遞跟蹤 3（當(dāng)前快遞員編號(hào)，當(dāng)前站點(diǎn)編號(hào)）答案解析：快遞費(fèi)支付（支付編號(hào)，快遞編號(hào)，支付金額，支付方式， 快遞員編號(hào)/收費(fèi)人，支付狀態(tài)，支付人身份證號(hào)，支付時(shí)間） 其他能夠描述清楚即可。4.答案解析：（a）PRIMARY KEYCHECK VALUES

17、 IN(F, M)或CHECK (stusex IN(F,M)或其他等價(jià)形式REFERNCES School(schno)CASCADE答案解析：（e）Club.clubnameCOUNT(DISTINCT(JoinClub.stuno) as clubstunumGROUPUNIONJoinClub答案解析：（j）StudentROWSET schstunum=schstunum+1 where School.schno= nrow.schnoSET schstunum=schstunum-1 where School.schno=orow.schno 答案解析：（n）order（o）Stu

18、dent.schno 或 School.schno5.答案解析：（a）SERIALIZABLE（b）Amts（c）COMMIT; return 0;（a）空根據(jù)題干要求要求(不考慮并發(fā)性能)在保證余額匯總交易正確性的前提下，不能影響其他存取款或轉(zhuǎn)賬交易的正確性。，因此對(duì)效率不做要求，對(duì)隔離要求比較高，選擇SERIALIZABLE 方式。READ COMMITTED指定在讀取數(shù)據(jù)時(shí)控制共享鎖以避免臟讀，但數(shù)據(jù)可在事務(wù)結(jié)束前更改，從而產(chǎn)生不可重復(fù)讀取或幻像數(shù)據(jù)。該選項(xiàng)是SQL Server 的默認(rèn)值。READ UNCOMMITTED執(zhí)行臟讀或 0 級(jí)隔離鎖定，這表示不發(fā)出共享鎖，也不接受排它鎖。當(dāng)

19、設(shè)置該選項(xiàng)時(shí)，可以對(duì)數(shù)據(jù)執(zhí)行未提交讀或臟讀；在事務(wù)結(jié)束前可以更改數(shù)據(jù)內(nèi)的數(shù)值，行也可以出現(xiàn)在數(shù)據(jù)集中或從數(shù)據(jù)集消失。該選項(xiàng)的作用與在事務(wù)內(nèi)所有語(yǔ)句中的所有表上設(shè)置NOLOCK 相同。這是四個(gè)隔離級(jí)別中限制最小的級(jí)別。REPEATABLE READ鎖定查詢中使用的所有數(shù)據(jù)以防止其他用戶更新數(shù)據(jù)，但是其他用戶可以將新的幻像行插入數(shù)據(jù)集，且幻像行包括在當(dāng)前事務(wù)的后續(xù)讀取中。因?yàn)椴l(fā)低于默認(rèn)隔離級(jí)別，所以應(yīng)只在必要時(shí)才使用該選項(xiàng)。SERIALIZABLE在數(shù)據(jù)集上放置一個(gè)范圍鎖，以防止其他用戶在事務(wù)完成之前更新數(shù)據(jù)集或?qū)⑿胁迦霐?shù)據(jù)集內(nèi)。這是四個(gè)隔離級(jí)別中限制最大的級(jí)別。因?yàn)椴l(fā)級(jí)別較低，所以應(yīng)只在必要時(shí)才使用該選項(xiàng)。該選項(xiàng)的作用與在事務(wù)內(nèi)所有SELECT 語(yǔ)句中的所有表上設(shè)置HOLDLOCK 相同?？帐菍?duì)參數(shù)的返回，此時(shí)參數(shù)Atms 是存儲(chǔ)過(guò)程內(nèi)部定義的變量， 不需要加冒號(hào)。空是事務(wù)正常提