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

計算機三級考試是全國計算機等級考試(簡稱NCRE)四個等級中的一個等級，下面本站小編為大家搜尋整理了關於2016年計算機三級資料庫分析真題，歡迎各位考生進行參考，希望您喜歡!想了解更多相關資訊請持續關注我們應屆畢業生考試網。

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)【解題思路】

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

　　【參考答案】

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