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

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

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

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

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

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

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

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

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

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

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

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

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

13、部人數(shù)視圖，能統(tǒng)計每個倶樂部已加入學(xué)生的人數(shù)，屬性有 clubno clubname 和clubstunum。對于暫時沒有學(xué)生參加的俱樂部，其人數(shù)為 0。此視圖的創(chuà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（ ）);問題：4.3(4 分)每當(dāng)系統(tǒng)中新加或刪除一個學(xué)生，就需要自動修改相應(yīng)學(xué)院的人數(shù)，以便保持系統(tǒng)中學(xué)生人數(shù)的完整性與一致性。此功能由下面的觸發(fā)器實現(xiàn)，請補(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;問題：4.4(2 分)查詢年齡小于 19 歲的學(xué)生的學(xué)號、姓名及所屬學(xué)院名，要求輸出結(jié)果把同一個學(xué)院的學(xué)生排在一起。此功能由下面的 SQL 語句實現(xiàn)，請補(bǔ)全。SELECT stuno, stuname, schname FROM Student, School WHERE Student.schno = School.schnoAND stuage 發(fā)件人身份證號，發(fā)件人身份證號-（發(fā)件人姓名，發(fā)件人電話號碼，發(fā)件人地址編號），所以不滿足 3NF。分解后的關(guān)系模式：快遞投遞 1（發(fā)件人身份證號，發(fā)件人姓名，發(fā)件人電話號碼，發(fā)件人地址編

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

17、 IN(F, M)或CHECK (stusex IN(F,M)或其他等價形式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)賬交易的正確性。，因此對效率不做要求，對隔離要求比較高，選擇SERIALIZABLE 方式。READ COMMITTED指定在讀取數(shù)據(jù)時控制共享鎖以避免臟讀，但數(shù)據(jù)可在事務(wù)結(jié)束前更改，從而產(chǎn)生不可重復(fù)讀取或幻像數(shù)據(jù)。該選項是SQL Server 的默認(rèn)值。READ UNCOMMITTED執(zhí)行臟讀或 0 級隔離鎖定，這表示不發(fā)出共享鎖，也不接受排它鎖。當(dāng)

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