2010上半年數(shù)據(jù)庫(kù)系統(tǒng)工程師考試下午真題及解析

1、2010上半年數(shù)據(jù)庫(kù)系統(tǒng)工程師考試下午真題及解析（1） 五年高考三年模擬相當(dāng)于高考“武功秘籍”中的九陰真經(jīng)。海量的題庫(kù)，對(duì)真題詳盡的解析，備受老師和學(xué)生的追捧?？梢?jiàn)，真題是應(yīng)對(duì)考試的上好資料，下面希賽軟考學(xué)院為你整理了2010上半年數(shù)據(jù)庫(kù)系統(tǒng)工程師考試下午真題及解析，助你修煉出一身“絕技”，應(yīng)對(duì)來(lái)年的數(shù)據(jù)庫(kù)系統(tǒng)工程師考試。試題一閱讀下列說(shuō)明和圖，回答問(wèn)題1至問(wèn)題4，將解答填入答題紙的對(duì)應(yīng)欄內(nèi)。說(shuō)明 某大型企業(yè)的數(shù)據(jù)中心為了集中管理、控制用戶(hù)對(duì)數(shù)據(jù)的訪(fǎng)問(wèn)并支持大量的連接需求，欲構(gòu)建數(shù)據(jù)管理中問(wèn)件，其主要功能如下： (1)數(shù)據(jù)管理員可通過(guò)中間件進(jìn)行用戶(hù)管理、操作管理和權(quán)限管理。用戶(hù)管理維護(hù)用戶(hù)信息

2、，用戶(hù)信息(用戶(hù)名、密碼)存儲(chǔ)在用戶(hù)表中；操作管理維護(hù)數(shù)據(jù)實(shí)體的標(biāo)準(zhǔn)操作及其所屬的后端數(shù)據(jù)庫(kù)信息，標(biāo)準(zhǔn)操作和后端數(shù)據(jù)庫(kù)信息存放在操作表中；權(quán)限管理維護(hù)權(quán)限表，該表存儲(chǔ)用戶(hù)可執(zhí)行的操作信息。 (2)中間件驗(yàn)證前端應(yīng)用提供的用戶(hù)信息。若驗(yàn)證不通過(guò)，返回非法用戶(hù)信息；若驗(yàn)證通過(guò)，中間件將等待前端應(yīng)用提交操作請(qǐng)求。 (3)前端應(yīng)用提交操作請(qǐng)求后，中間件先對(duì)請(qǐng)求進(jìn)行格式檢查。如果格式不正確，返回格式錯(cuò)誤信息；如果格式正確，則進(jìn)行權(quán)限驗(yàn)證(驗(yàn)證用戶(hù)是否有權(quán)執(zhí)行請(qǐng)求的操作)，若用戶(hù)無(wú)權(quán)執(zhí)行該操作，則返回權(quán)限不足信息，否則進(jìn)行連接管理。 (4)連接管理連接相應(yīng)的后臺(tái)數(shù)據(jù)庫(kù)并提交操作。連接管理先檢查是否存在空閑

3、的數(shù)據(jù)庫(kù)連接，如果不存在，新建連接；如果存在，則重用連接。 (5)后端數(shù)據(jù)庫(kù)執(zhí)行操作并將結(jié)果傳給中間件，中間件對(duì)收到的操作結(jié)果進(jìn)行處理后，將其返回給前端應(yīng)用。 現(xiàn)采用結(jié)構(gòu)化方法對(duì)系統(tǒng)進(jìn)行分析與設(shè)計(jì)，獲得如圖1-1所示的頂層數(shù)據(jù)流圖和圖1-2所示的0層數(shù)據(jù)流圖。問(wèn)題1 使用說(shuō)明中的詞語(yǔ)，給出圖1-1中的實(shí)體E1E3的名稱(chēng)。問(wèn)題2 使用說(shuō)明中的詞語(yǔ)，給出圖1-2中的數(shù)據(jù)存儲(chǔ)D1D3的名稱(chēng)。問(wèn)題3 給出圖1-2中加工P的名稱(chēng)及其輸入、輸出流。除加工P的輸入與輸出流外，圖1-2還缺失了兩條數(shù)據(jù)流，請(qǐng)給出這兩條數(shù)據(jù)流的起點(diǎn)和終點(diǎn)。注：名稱(chēng)使用說(shuō)明中的詞匯，起點(diǎn)和終點(diǎn)均使用圖1-2中的符號(hào)或詞匯。問(wèn)題4

4、在繪制數(shù)據(jù)流圖時(shí)，需要注意加工的繪制。請(qǐng)給出三種在繪制加工的輸入、輸出時(shí)可能出現(xiàn)的錯(cuò)誤。試題一分析本題考查數(shù)據(jù)流圖(DFD)的應(yīng)用，是比較傳統(tǒng)的題目，要求考生細(xì)心分析題目中所描述的內(nèi)容。 DFD是一種便于用戶(hù)理解、分析系統(tǒng)數(shù)據(jù)流程的圖形工具，是系統(tǒng)邏輯模型的重要組成部分。問(wèn)題1本問(wèn)題考查頂層DFD。項(xiàng)層DFD一股用來(lái)確定系統(tǒng)邊界，將待開(kāi)發(fā)系統(tǒng)看作是一個(gè)加工，因此圖中只有唯一的一個(gè)加工和一些外部實(shí)體，以及這兩者之問(wèn)的輸入輸出數(shù)據(jù)流。題目要求根據(jù)描述確定圖中的外部實(shí)體。分析題目中的描述，并結(jié)合已經(jīng)在頂層數(shù)據(jù)流圖中給出的數(shù)據(jù)流進(jìn)行分析。題目中有信息描述：數(shù)據(jù)管理員可通過(guò)中間件進(jìn)行用戶(hù)管理、操作管理和

5、權(quán)限管理；前端應(yīng)用提交操作請(qǐng)求；連接管理連接相應(yīng)的后臺(tái)數(shù)據(jù)庫(kù)并提交操作。由此可知該中間件系統(tǒng)有數(shù)據(jù)管理員、前端應(yīng)用和后端數(shù)據(jù)庫(kù)三個(gè)外部實(shí)體。對(duì)應(yīng)圖1-1中數(shù)據(jù)流和實(shí)體的對(duì)應(yīng)關(guān)系，可知E1為前端應(yīng)用，E2為數(shù)據(jù)管理員，E3為后端數(shù)據(jù)庫(kù)。問(wèn)題2本問(wèn)題考查0層DFD中數(shù)據(jù)存儲(chǔ)的確定。說(shuō)明中描述：用戶(hù)信息(用戶(hù)名、密碼)存儲(chǔ)在用戶(hù)表中；標(biāo)準(zhǔn)操作和后端數(shù)據(jù)庫(kù)信息存放在操作表中；權(quán)限管理維護(hù)信息存放在權(quán)限表中。因此數(shù)據(jù)存儲(chǔ)為用戶(hù)表、操作表以及權(quán)限表。再根據(jù)圖1-2中D1的輸入數(shù)據(jù)流從用戶(hù)管理來(lái)，D2的輸入數(shù)據(jù)流從操作管理來(lái)，D3的輸入數(shù)據(jù)流從權(quán)限管理來(lái)，所以D1為用戶(hù)表，D2為操作表，D3為權(quán)限表。問(wèn)題3

6、本問(wèn)題考查0層DFD中缺失的加工和數(shù)據(jù)流。比較圖1-1和圖1-2，可知頂層DFD中的操作結(jié)果和處理后的操作結(jié)果沒(méi)有在0層DFD中體現(xiàn)。再根據(jù)描述：后端數(shù)據(jù)庫(kù)執(zhí)行操作并將結(jié)果傳給中間件，中間件對(duì)收到的操作結(jié)果進(jìn)行處理后，將其返回給前端應(yīng)用?？芍?，需要有操作結(jié)果處理，因此P為操作結(jié)果處理，其輸入流從后端數(shù)據(jù)庫(kù)E3來(lái)的操作結(jié)果，輸出結(jié)果為處理后的操作結(jié)果，并返回給前端應(yīng)用E1。 考查完P(guān)及其輸入輸出流之后，對(duì)圖1-2的內(nèi)部數(shù)據(jù)流進(jìn)行考查，以找出缺失的另外2條數(shù)據(jù)流。從圖中可以看出D2和D3只有輸入流沒(méi)有輸出流，這是常見(jiàn)DFD設(shè)計(jì)時(shí)的錯(cuò)誤，所以首先考查D2和D3的輸出流。描述中：權(quán)限驗(yàn)證是驗(yàn)證用戶(hù)是否

7、有權(quán)執(zhí)行請(qǐng)求的操作，若用戶(hù)有權(quán)執(zhí)行該操作，進(jìn)行連接管理；連接管理連接相應(yīng)的后臺(tái)數(shù)據(jù)庫(kù)并提交操作；權(quán)限表存儲(chǔ)用戶(hù)可執(zhí)行的操作信息。因此，權(quán)限驗(yàn)證有從權(quán)限表D3來(lái)的輸入數(shù)據(jù)流。而要連接后端數(shù)據(jù)庫(kù)，需要數(shù)據(jù)庫(kù)信息，從權(quán)限驗(yàn)證的輸出流中包含有數(shù)據(jù)庫(kù)信息可知，權(quán)限驗(yàn)證需要獲取到數(shù)據(jù)庫(kù)信息，所以還需從操作表D2來(lái)的輸入流。問(wèn)題4本問(wèn)題考查在繪制數(shù)據(jù)流圖中加工繪制時(shí)的注意事項(xiàng)。繪制加工時(shí)可能出現(xiàn)的錯(cuò)誤有：加工的輸入、輸出時(shí)可能出現(xiàn)只有輸入而無(wú)輸出、只有輸出而無(wú)輸入、輸入的數(shù)據(jù)流無(wú)法通過(guò)加工產(chǎn)生輸出流以及輸入的數(shù)據(jù)流與輸出的數(shù)據(jù)流名稱(chēng)相同等錯(cuò)誤。參考答案 問(wèn)題1E1：前端應(yīng)用 E2：數(shù)據(jù)管理員 E3：后端數(shù)據(jù)

8、庫(kù)問(wèn)題2D1：用戶(hù)表 D2：操作表 D3：權(quán)限表問(wèn)題3P的名稱(chēng)：操作結(jié)果處理問(wèn)題4 在繪制數(shù)據(jù)流圖的加工時(shí)，可能出現(xiàn)的輸入、輸出錯(cuò)誤： 只有輸入而無(wú)輸出或者黑洞 只有輸出而無(wú)輸入或者奇跡 輸入的數(shù)據(jù)流無(wú)法通過(guò)加工產(chǎn)生輸出流或者灰洞 輸入的數(shù)據(jù)流與輸出的數(shù)據(jù)流名稱(chēng)相同。試題二閱讀下列說(shuō)明，回答問(wèn)題1至問(wèn)題3，將解答填入答題紙的對(duì)應(yīng)欄內(nèi)。 說(shuō)明 天津市某銀行信息系統(tǒng)的數(shù)據(jù)庫(kù)部分關(guān)系模式如下所示： 客戶(hù) (客戶(hù)號(hào)，姓名，性別，地址，郵編，電話(huà)) 賬戶(hù) (賬戶(hù)號(hào)，客戶(hù)號(hào)，開(kāi)戶(hù)支行號(hào)，余額) 支行(支行號(hào)，支行名稱(chēng)，城市，資產(chǎn)總額) 交易 (交易號(hào)，賬戶(hù)號(hào)，業(yè)務(wù)金額，交易日期) 其中，業(yè)務(wù)金額為正值表示

9、客戶(hù)向賬戶(hù)存款；為負(fù)值表示取款。問(wèn)題1以下是創(chuàng)建賬戶(hù)關(guān)系的SQL語(yǔ)句，賬戶(hù)號(hào)唯一識(shí)別一個(gè)賬戶(hù)，客戶(hù)號(hào)為客戶(hù)關(guān)系的唯一標(biāo)識(shí)，且不能為空。賬戶(hù)余額不能小于1.00元。請(qǐng)將空缺部分補(bǔ)充完整。CREATE TABLE賬戶(hù)(賬戶(hù)號(hào)CHAR(19) (a) ,客戶(hù)號(hào)CHAR(10) (b)開(kāi)戶(hù)支行號(hào)CHAR(6) NOT NULL,余額NUMBER(8,2) (c) );問(wèn)題2(1)現(xiàn)銀行決策者希望查看在天津市各支行開(kāi)戶(hù)且20XX年9月使用了銀行存取服務(wù)的所有客戶(hù)的詳細(xì)信息，請(qǐng)補(bǔ)充完整相應(yīng)的查詢(xún)語(yǔ)句。(交易日期形式為'2000-01-01')SELECT DISTINCT客戶(hù).*FROM客

10、戶(hù),賬戶(hù),支行,交易WHERE客戶(hù).客戶(hù)號(hào)=賬戶(hù).客戶(hù)號(hào) AND賬戶(hù).開(kāi)戶(hù)支行號(hào)=支行.支行號(hào)AND(d) AND交易.賬戶(hù)號(hào)=賬戶(hù).賬戶(hù)號(hào) AND(e)上述查詢(xún)優(yōu)化后的語(yǔ)句如下，請(qǐng)補(bǔ)充完整。SELECT DISTINCT客戶(hù).*FROM 客戶(hù),賬戶(hù), (f) AS新支行， (g) AS新交易WHERE客戶(hù).客戶(hù)號(hào)=賬戶(hù).客戶(hù)號(hào)AND賬戶(hù).開(kāi)戶(hù)支行號(hào)=新支行.支行號(hào)AND新交易.賬戶(hù)號(hào)=賬戶(hù).賬戶(hù)號(hào)； (2)假定一名客戶(hù)可以申請(qǐng)多個(gè)賬戶(hù)，給出在該銀行當(dāng)前所有賬戶(hù)余額之和超過(guò)百萬(wàn)的客戶(hù)信息并按客戶(hù)號(hào)降序排列。SELECT *FROM客戶(hù)WHERE (h)(SELECT客戶(hù)號(hào)FROM賬戶(hù)GROUP

11、 BY客戶(hù)號(hào) (i) )ORDER BY (j)問(wèn)題3(1)為賬戶(hù)關(guān)系增加一個(gè)屬性“賬戶(hù)標(biāo)記”，缺省值為0，取值類(lèi)型為整數(shù)；并將當(dāng)前賬戶(hù)關(guān)系中所有記錄的“賬戶(hù)標(biāo)記”屬性值修改為0。請(qǐng)補(bǔ)充相關(guān)SQL語(yǔ)句。ALTER TABLE 賬戶(hù) (k) DEFAULT 0；UPDATE 賬戶(hù) (l) (2)對(duì)于每筆金額超過(guò)10萬(wàn)元的交易，其對(duì)應(yīng)賬戶(hù)標(biāo)記屬性值加1，給出觸發(fā)器實(shí)現(xiàn)的方案。CREATE TRIGGER 交易_觸發(fā)器 (m) ON交易R(shí)EFERENCING NEW ROW AS 新交易FOR EACH ROWWHEN (n) BEGIN ATOMICUPDATE 賬戶(hù) SET 賬戶(hù)標(biāo)記=賬戶(hù)標(biāo)記+

12、1WHERE (o)COMMIT WORK;END試題二分析本題考查SQL語(yǔ)句的基本語(yǔ)法與結(jié)構(gòu)知識(shí)。 此類(lèi)題目要求考生掌握SQL語(yǔ)句的基本語(yǔ)法和結(jié)構(gòu)，認(rèn)真閱讀題目給出的關(guān)系模式，針對(duì)題目的要求具體分析并解答。本試題已經(jīng)給出了4個(gè)關(guān)系模式，需要分析每個(gè)實(shí)體的屬性特征及實(shí)體之間的聯(lián)系，補(bǔ)充完整SQL語(yǔ)句。問(wèn)題1由于問(wèn)題1中“賬戶(hù)號(hào)唯一識(shí)別一個(gè)賬戶(hù)”可知賬戶(hù)號(hào)為賬戶(hù)關(guān)系的主鍵，即不能為空且唯一標(biāo)識(shí)一條賬戶(hù)信息，因此需要用PRIMARY KEY對(duì)該屬性進(jìn)行主鍵約束；又由于“客戶(hù)號(hào)為客戶(hù)關(guān)系的唯一標(biāo)識(shí)，且不能為空”可知客戶(hù)號(hào)為客戶(hù)關(guān)系的主鍵，在賬戶(hù)關(guān)系中應(yīng)作外鍵，用FOREIGN KEY對(duì)該屬性進(jìn)行外鍵

13、約束；由“賬戶(hù)余額不能小于1.00元”可知需要限制賬戶(hù)余額屬性值的范圍，通過(guò)CHECK約束來(lái)實(shí)現(xiàn)。從上分析可見(jiàn)，完整的SQL語(yǔ)句如下：CREATE TABLE賬戶(hù)(賬戶(hù)號(hào)CHAR(19) PRIMARY KEY,客戶(hù)號(hào)CHAR(10) FOREIGN KEY(客戶(hù)號(hào))REFERENcEs客戶(hù)(客戶(hù)號(hào))， 開(kāi)戶(hù)支行號(hào)CHAR(6) NOT NULL， 余額 NUMBER(8,2) CHECK(余額1.00);注：PRIMARY KEY可替換為NOT NULL UNIQUE或NOT NULL PRIMARY KEY。 問(wèn)題2SQL查詢(xún)通過(guò)SELECT語(yǔ)句實(shí)現(xiàn)。 (1)根據(jù)問(wèn)題2要求應(yīng)在表連接條件的

14、基礎(chǔ)上，需要添加兩個(gè)條件：支行關(guān)系的城市屬性值為“天津市”，即支行城市='天津市'；在20XX年9月存在交易記錄，由于交易日期形式為'2000-01-01'，所以需要通過(guò)模糊匹配來(lái)實(shí)現(xiàn)，用LIKE關(guān)鍵詞和通配符表示，即交易交易日期LIKE'2009-09-%'。 WHERE子句中條件的先后順序會(huì)對(duì)執(zhí)行效率產(chǎn)生影響。假如解析器是按照先后順序依次解析并列條件，優(yōu)化的原則是：表之間的連接必須出現(xiàn)在其他WHERE條件之后，那些可以過(guò)濾掉最多條記錄的條件盡可能出現(xiàn)在WHERE子句中其他條件的前面。要實(shí)現(xiàn)上述優(yōu)化過(guò)程，可以重新組織WHERE條件的順序或者通過(guò)

15、嵌套查詢(xún)以縮小連接記錄數(shù)目的規(guī)模來(lái)實(shí)現(xiàn)。 根據(jù)問(wèn)題2要求，考生需要添加兩個(gè)子查詢(xún)以縮小參與連接的記錄的數(shù)目，即篩選出天津市的所有支行(SELECT+FROM支行WHERE城市='天津市')，而且找到20XX年9月發(fā)生的交易記錄(SELECT+FROM交易WHERE交易日期LIKE'2009-09-%')，然后再做連接查詢(xún)。 (2)根據(jù)問(wèn)題2要求，可通過(guò)予查詢(xún)實(shí)現(xiàn)“所有賬戶(hù)余額之和超過(guò)百萬(wàn)的客戶(hù)信息”的查詢(xún)；對(duì)SUM函數(shù)計(jì)算的結(jié)果應(yīng)通過(guò)HAVING條件語(yǔ)句進(jìn)行約束；降序通過(guò)DESC關(guān)鍵字來(lái)實(shí)現(xiàn)。SELECT * FROM 客戶(hù) WHERE 客戶(hù)號(hào) IN(SELEC

16、T 客戶(hù)號(hào) FROM 賬戶(hù) GROUP BY客戶(hù)號(hào)HAVING SUM(余額)1000000.00) ORDER BY客戶(hù)號(hào) DESC; 問(wèn)題3 (1)關(guān)系模式的修改通過(guò)ALTER語(yǔ)句來(lái)實(shí)現(xiàn)，使用ADD添加屬性；使用SET修改屬性值。 ALTER TABLE 賬戶(hù) ADD 賬戶(hù)標(biāo)記 INT DEFAULT 0; UPDATE 賬戶(hù) SET 賬戶(hù)標(biāo)記 =0; (2)創(chuàng)建觸發(fā)器可通過(guò)CREATE TRIGGER語(yǔ)句實(shí)現(xiàn)，問(wèn)題3要求考生掌握該語(yǔ)句的基本語(yǔ)法結(jié)構(gòu)。按照問(wèn)題3要求，在交易關(guān)系中插入一條記錄時(shí)觸發(fā)器應(yīng)自動(dòng)執(zhí)行，故需要?jiǎng)?chuàng)建基于INSERT類(lèi)型的觸發(fā)器，其觸發(fā)條件是新插入交易記錄的金額屬性值1

17、00000.00；最后添加表連接條件。完整的觸發(fā)器實(shí)現(xiàn)方案如下： CREATE TRIGGER交易觸發(fā)器AFTER INSERT ON交易 REFERENCING NEW ROW AS新交易 FOR EACH ROWWHEN新交易.金額100000.00 BEGIN ATOMICUPDATE 賬戶(hù) SET 賬戶(hù)標(biāo)記=賬戶(hù)標(biāo)記+1 WHERE 賬戶(hù).賬戶(hù)號(hào)=新交易.賬戶(hù)號(hào); COMMIT WORK; END參考答案 問(wèn)題1(a)PRIMARY KEY/NOT NULL UNIQUE/NOT NULL PRIMARY KEY(b)FOREIGN KEY(客戶(hù)號(hào))REFERENCES客戶(hù)(客戶(hù)號(hào))(C)CHECK(余額1.00)問(wèn)題2(d)支行.城市='天津市' (e)交易.交易日期 LIKE'2009-09-%'或等價(jià)表示 注：(d)(e)