2016計算機三級資料庫分析真題

2016年上半年計算機考試已經過去了，yjbys小編第一時間為大家分享的是本次計算機三級資料庫的考試真題，希望對大家有所幫助!

　　Q1:

設某全國性的運輸企業建立了大型OLTP系統，並在該系統之上建立了資料倉庫。OLTP系統和資料倉庫中有如下資料表：

運輸明細表(運輸單ID，傳送站ID，終到站ID，貨物ID，貨物重量，運輸價格，發貨日期)

彙總表1(傳送站ID，終到站ID，貨物ID，發貨日期，總重，總運價)

彙總表2(傳送站ID，終到地區ID，貨物ID，發貨日期，總重，總運價)

彙總表3(傳送站ID，終到站ID，貨物ID，發貨月份，總重，總運價)

彙總表4(傳送地區ID，終到地區ID，貨物類別ID，發貨日期，總重，總運價)

該企業管理的貨運站約有100個，貨物約有500種共10類，各彙總表都建有主碼，且各表有合理的維護策略，在每次維護後資料能保持一致。設有檢視V，該檢視的訪問頻率很高，其查詢結果模式為(傳送地區ID，終到站ID，發貨月份，總重，總運價)，該檢視現以彙總表1為計算資料來源。經監控發現，彙總表1的被訪問頻率過高，導致系統整體效能下降，而其它彙總表被訪問頻率較低。在不增加彙總表和索引的情況下，請給出一個改善系統服務效能的優化方案，並簡要說明理由。

　　A1:

由於彙總表1和檢視的模式訪問頻率都很高，而且檢視的資料來源來自彙總表1，又因為其他彙總表的訪問率較低，所以只需要將檢視的資料來源繫結為彙總表3，因為彙總表3也可以滿足檢視的輸出模式。這樣不僅提升了彙總表3的資料訪問率，而且降低了彙總表1的資料訪問率，系統性能和服務效能得到了很大的優化。又因為貨物約有500種，共10類，可以再建立一個檢視繫結資料來源為彙總表4，這樣就可以充分利用匯總表4的資料資訊，從而可以進一步完善系統性能的優化。

　　Q2:

在進行某學校教務管理系統的資料庫設計時，資料庫設計人員設計瞭如下幾個關係模式：

系(系號，系名)，系號為主碼

學生(學號，姓名，所在系號)，學號為主碼

課程(課程號，課程名，開課系號)，課程號為主碼

選課(學號，課程號，選課時間)，學號和課程號為主碼(8分)

開發人員在將關係模式實施到SQL Server 2008的"教務"資料庫時，使用瞭如下表結構定義語句：

CREATE TABLE 系 (

系號 varchar(10) NOT NULL ,

系名 varchar(100))

CREATE TABLE 學生 (

學號 varchar(50) NOT NULL ,

姓名 varchar(50),

所在系號 varchar(10))

CREATE TABLE課程 (

課程號 varchar(50) NOT NULL ,

課程名 varchar(100),

開課系號 varchar(10))

CREATE TABLE 選課 (

學號 varchar(50) NOT NULL ,

課程號 varchar(50) NOT NULL ,

選課時間 datetime )

在執行如下查詢語句時發現執行效率很低：

SELECT * FROM 選課 JOIN 學生 ON 學生.學號= 選課.學號

JOIN 系 ON 系.系號 = 學生.所在系號

JOIN 課程 ON 課程.課程號 = 選課.課程號

WHERE 系.系號=′012′

AND convert(varchar(10),選課時間,120)>= ′2010-01-01′

(1)在查詢原因時發現建表語句有問題。請指出問題並說明該問題是否會影響此查詢語句的執行效率。(4分)

(2)設已在"選課"表的"選課時間"列及"學生"表的"所在系號"列上建立了索引。請問這兩個索引是否能夠提高該查詢語句的執行效率?如果不能，請說明原因。(4分)

　　A2:

(1) 【解題思路】

本題中查詢語句的功能是得到12系全體學生在2010年1月1日後的選課情況的彙總表。在每個資料表的定義時都必須嚴格定義表中的完整性約束條件，包括主鍵的設定，否則之後會出現主鍵有相同值的情況，破壞了資料的完整性。

【參考答案】

建表時沒有設定主鍵，也沒有說明外來鍵。但不會影響此查詢語句的執行效率

(2)【解題思路】

建立索引是加快查詢速度的有效手段，使用者可以根據應用環境的需要建立一個或多個索引，以提供多種存取路徑，加快查詢速度。索引就像書的目錄一樣為我們將記錄按規定的列進行了排序，這樣當我們要訪問滿足這些列的某些條件的記錄時，索引會為我們減少查詢資料庫中的地址範圍，大大節省了時間。所以根據WHERE條件後的欄位對錶建立索引對於提高查詢效率是有幫助的。

【參考答案】

"選課"表的"選課時間"列上建立了索引，從而能夠提高執行效率。經常出現在Where子句中的欄位，特別是大表的欄位，應該建立索引。索引的作用就類似於書的目錄，即會按照章節的順序排列。因此如果在一本數百頁的書裡面查詢某個章節位置的時候，就可以只掃描書的目錄。掃描的範圍縮了n倍，查詢的效率自然就會提高。另外，在SQLServer記憶體夠用的情況下，索引會被放到記憶體中，在記憶體中查詢自然又會提高效率，所以必須合理利用索引。

　　Q3:

某商場商品經營管理系統使用SQL Server 2008資料庫管理系統，此係統上線執行1年後，業務人員使用某統計功能(此功能每月使用一次)時發現速度很慢。該統計功能主要執行的SQL語句如下：

SELECT 商品號,SUM(銷售數量*銷售價格) 銷售額

FROM 銷售明細

GROUP BY 商品號;

該銷售明細表的建表語句如下：

CREATE TABLE 銷售明細(

序列號 intIDENTITY(1,1) NOT NULL,

商品號 intNOT NULL,

銷售日期 datetime NULL,

銷售數量 intNOT NULL,

銷售價格 intNOT NULL

);

並在銷售明細表上建有如下索引：

CREATE index ix_銷售明細_商品號 on 銷售明細(商品號);

某技術人員提出通過執行下述語句以提高此查詢的執行效率：

CREATE VIEW 商品銷售額檢視

WITH SCHEMABINDING

AS

SELECT 商品號,SUM(銷售數量*銷售價格) 銷售額,

COUNT_BIG(*) cnt

FROM dbo.銷售明細

GROUP BY 商品號;

CREATE UNIQUE CLUSTERED INDEXix_商品銷售額

ON 商品銷售額檢視(商品號);(10分)

(1)請分析該技術人員給出的語句功能以及對原有查詢語句的效能影響，並給出原因。

(2)此商場的銷售量很大，每天有大量資料插入到銷售明細表中。請從資料庫整體效能角度分析，此技術人員提出的優化方法是否合適，並給出原因。

　　A3:

(1)【解題思路】

該技術人員使用了帶有索引的檢視，將所關心的資料(商品號，銷售額，該商品號在表中出現的次數)從銷售明細表中提取出來建立檢視，並對該檢視建立按商品號排序的聚簇索引，這樣大大減少了在搜尋不同商品的銷售額時呼叫的資料表的規模，從而提高了查詢效率。由於表的資料規模很大，建立該檢視後，同一種商品不會多次出現在表中，而是通過一個計數變數cnt表示，這樣在檢索時大大減少了檢索規模。建立索引時，UNIQUE關鍵字表明此索引的每一個索引值只對應唯一的資料記錄。CLUSTER表示要建立的索引是聚簇索引，所謂聚簇索引是指索引項的順序與表中記錄的物理順序一致的索引組織。

【參考答案】

語句功能：建立包含(商品號，銷售額，該商品表中出現次數)的帶索引的檢視，並建立按商品對應銷售額UNIQUE聚簇排序的索引，大大縮小了查詢語句的查詢範圍，提高了查詢效率。原因：檢視中將間接相關的屬性列(序列號，銷售日期，商品號，銷售數量，銷售價格)轉換成了目標屬性列，減少了搜尋空間;同時建立UNIQUE CLUSTERED索引，使查詢商品號的資料記錄唯一，降低了搜尋範圍，提高了搜尋效率。

(2)【解題思路】

由於檢視是不實際儲存資料的虛表，因此對檢視的更新最終要轉換為對基本表的更新。而使用者通過檢視對資料進行增加、刪除、修改時，有意或無意地對不屬於檢視範圍內的基本表資料進行操作，會破壞資料的一致性。而且檢視中的資料本身就是冗餘的，每次對錶進行修改時，同時也要對相應的檢視進行修改，這大大增加了系統的負擔。

【參考答案】

不合適，每天大量的插入操作在修改表的同時也要對檢視進行修改，增加了系統的負擔，然而該統計功能一個月才用一次，這樣導致系統的利用率也較為低下。

　　Q4:

某教務管理系統使用SQL Server 2008資料庫管理系統，資料庫軟硬體配置資訊如下：

Ⅰ.資料庫執行在兩路Intel Xeon E5-2609 2.4GHz CPU(每路CPU4核心)，128GB記憶體、2塊300GB15000轉SAS硬碟(RAID 1)的伺服器上;

Ⅱ.伺服器作業系統為Windows 2003 32位企業版，安裝SP2補丁;

Ⅲ.資料庫為SQL Server 2008 Enterprise(32 位)，資料庫中總資料量近130GB。

近一個學期以來，使用者反映系統執行緩慢，經程式設計師定位，確定為資料庫伺服器響應緩慢，需要進行調優。

　　A4：

【解題思路】

資料庫效能優化的基本原則就是通過儘可能少的磁碟訪問獲得所需要的資料。SQL SERVER效能優化一般從資料庫設計、應用程式編碼、硬體優化、資料庫索引、SQL語句、事務處理幾個方面入手考慮問題。

(1) 分析階段：在系統分析階段往往有太多需要關注的地方，系統各種功能性、可用性、可靠性、安全性需求吸引了我們大部分的注意力，但必須注意的是，效能往往是很重要的非功能性需求，必須根據系統的特點確定其實時性需求、響應時間的需求、硬體的配置等。最好能有各種需求量化的指標。

(2) 設計階段：例如資料庫邏輯設計規範化;合理的冗餘;主鍵的設計;外來鍵的設計 ;欄位的設計 ;資料庫物理儲存和環境的設計;資料庫的物理儲存、作業系統環境及網路環境的設計，皆使得我們的系統在將來能適應較多使用者的併發操作和較大的資料處理量。 這裡需要注意檔案組的作用，適用檔案組可以有效的把I/O操作分散到不同的物理硬碟，提高併發能力。

(3) 系統設計：整個系統的設計，特別是系統結構的設計對效能具有很大的影響。對於一般的OLTP系統，可以選擇C/S結構、三層的C/S結構等，不同的系統結構其效能的關鍵也有所不同。系統設計階段應歸納些業務邏輯在資料庫程式設計階段實現，資料庫程式設計包括資料庫儲存過程、觸發器和函式。用資料庫程式設計實現業務邏輯的好處是減少網路流量並能更充分利用資料庫的預編譯和快取功能;索引設計階段可以根據功能和效能的需求進行初步的索引設計，這裡需要根據預計的資料量和查詢來設計索引，可能與將來實際使用時有所區別。

(4) 編碼階段：編碼階段首先需要所有程式設計師具備優化意識，也就是在實現功能的同時具備考慮優化效能的思想。資料庫是能進行集合運算的工具，所謂集合運算實際是批量運算，即是儘量減少在客戶端進行大資料量的迴圈操作，而用SQL語句或者儲存過程代替。這個階段主要是注意在SQL語句等方面的優化，如：儘量少做重複的工作，用SELECT後跟需要的欄位代替SELECT*語句，注意事務和鎖 ，注意臨時表和表變數的用法，慎用遊標和觸發器，儘量使用索引等。

(5) 硬體優化：RAID (獨立磁碟冗餘陣列)是由多個磁碟驅動器(一個陣列)組成的磁碟系統。通過將磁碟陣列當作一個磁碟來對待，基於硬體的RAID允許使用者管理多個磁碟。使用基於硬體的RAID與基於作業系統的RAID相比較可知，基於硬體的RAID能夠提供更佳的效能，如果使用基於作業系統的RAID，那麼它將佔據其他系統需求的CPU週期，通過使用基於硬體的RAID，使用者在不關閉系統的情況下能夠替換髮生故障的驅動器。利用資料庫分割槽技術，可均勻地把資料分佈在系統的磁碟中，平衡I/O 訪問，避免I/O 瓶頸等。