三、設(shè)計(jì)與應(yīng)用題(共30分)
1．已知有如下關(guān)系模式：Rl(a，b，c)，R2(c，d，a)，R3(e，f)，R4(a,e，g)，其中標(biāo)下劃線的屬性是主碼。請(qǐng)將上述關(guān)系模式用適當(dāng)?shù)腅R圖表示出來(lái)，并用下劃線標(biāo)注出作為實(shí)體主碼的屬性。(10分)
2．設(shè)在SQL Server 2008某數(shù)據(jù)庫(kù)中有商品表和銷售表，兩個(gè)表的定義如下：
CREATE TABLE商品表(
商品號(hào)char(10)PRIMARY KEY，
商品名varchar(40)，
類別varchar(20)，
進(jìn)貨單價(jià)int)
CREATE TABLE銷售表(
商品號(hào)char(10)，
銷售時(shí)間datetime，
銷售數(shù)量int，
銷售單價(jià)int，
PRIMARY KEY(商品號(hào)，銷售時(shí)問(wèn)))
下面是一個(gè)用戶定義的多語(yǔ)句表值函數(shù)，它接受類別作為輸入?yún)?shù)，返回該類別下的每種商品在2012年的銷售總利潤(rùn)，并將結(jié)果按照銷售總利潤(rùn)的降序輸出。請(qǐng)補(bǔ)全該函數(shù)定義代碼。(10分)
CREATE FUNCTION f_Profit(@lb char(10))【1】@ProfitTable【2】(
商品號(hào)char(10)，
總利潤(rùn)int)
AS
BEGIN
INSERT INTO@ProfitTable
【3】
【4】
END
3．某教務(wù)管理系統(tǒng)使用SQL Server 2008數(shù)據(jù)庫(kù)管理系統(tǒng)，數(shù)據(jù)庫(kù)軟硬件配置信息如下：
Ⅰ．?dāng)?shù)據(jù)庫(kù)運(yùn)行在兩路Intel Xeon E5-2609 2．4GHz CPU(每路CPU4核心)，128GB內(nèi)存、2塊300GB15000轉(zhuǎn)SAS硬盤(RAID l)的服務(wù)器上；
Ⅱ．服務(wù)器操作系統(tǒng)為Windows 2003 32位企業(yè)版，安裝SP2補(bǔ)??；
Ⅲ．?dāng)?shù)據(jù)庫(kù)為SQL Server 2008 Enterprise(32位)，數(shù)據(jù)庫(kù)中總數(shù)據(jù)量近130GB。
近一個(gè)學(xué)期以來(lái)，用戶反映系統(tǒng)運(yùn)行緩慢，經(jīng)程序員定位，確定為數(shù)據(jù)庫(kù)服務(wù)器響應(yīng)緩慢，需要進(jìn)行調(diào)優(yōu)。(10分)
三、設(shè)計(jì)與應(yīng)用題
1．【解題思路】
ER模型和關(guān)系模式相互轉(zhuǎn)換的一般規(guī)則如下：
(1)將每一個(gè)實(shí)體類型轉(zhuǎn)換成一個(gè)關(guān)系模式，實(shí)體的屬性為關(guān)系模式的屬性。
f21對(duì)干一元聯(lián)系,按各種情處理，如下表格所示。
| 二元關(guān)系 | ER圖 | 轉(zhuǎn)換成的關(guān)系 | 聯(lián)系的處理 | 主鍵 | 外鍵 |
| 1：1 | 1->1 | (2個(gè)關(guān)系) 模式A 模式B |
(有兩種) 處理方式(1)： 把模式B的主鍵， 聯(lián)系的屬性加入模式A 處理方式(2)： 把模式A的主鍵， 聯(lián)系的屬性加入模式B |
(略) | (依據(jù)聯(lián)系的處理方式) 方式(1)： 模式B的主鍵為模式A外鍵 方式(2)： 表A的主鍵為表B的外鍵 |
| 1：N | 1->n | (2個(gè)關(guān)系) 模式A 模式B |
把模式A的主鍵，聯(lián) 系的屬性加入模式B |
(略) | 模式A的主鍵為模式B的外鍵 |
| M：N | m->n | (3個(gè)關(guān)系) 模式A 模式B 模式A-B |
聯(lián)系類型轉(zhuǎn)換 成關(guān)系模式A-B； 模式A-B的屬性： (a)聯(lián)系的屬性 (b)兩端實(shí)體類型的主鍵 |
兩端實(shí)體類型的 主鍵一起構(gòu)成模 式A-B主鍵 |
兩端實(shí)體類型的主 鍵分別為模式A-B的外鍵 |
此題為關(guān)系模式轉(zhuǎn)換為實(shí)體類型，因此采用實(shí)體→關(guān)系的逆向思維解題。從模式R1和R2可知，R1和R2為一對(duì)一關(guān)系，根據(jù)這兩個(gè)模式的拆分可以確定三個(gè)實(shí)體，此處將這三個(gè)實(shí)體分別命名為A、B和C。其中A、B和C分別一一對(duì)應(yīng)，且a和c分別是B和C的外鍵。從模式R1和R4可知，R1和R4為多對(duì)一關(guān)系，由此確定實(shí)體D。從模式R3和R4可知，R3和R4為多對(duì)一關(guān)系，由此可以確定出實(shí)體E。
【參考答案】
a、c為A的候選碼，可任選其一做主碼。可通過(guò)以下ER圖來(lái)表示：
2．【解題思路】
用戶定義的多語(yǔ)句表值函數(shù)的命令格式為：
CREATE FUNCTION[schema_name．]function name
([{@parameter_name[AS][type_schema_name．]parameter_data_type
[=default]}
[…．n]
]
)
RETURNS@return_variable TABLE
[WITH
[AS]
BEGIN
function_body
RETURN
END
[；]
({
J
[
)
題目要求返回指定類別下的每種商品在2012年的銷售總利潤(rùn)，并且將結(jié)果按照銷售總利潤(rùn)的降序輸出。采用復(fù)合SQL語(yǔ)句的格式，先查出指定類別的所有商品號(hào)：SELECT商品號(hào)FROM商品表WHERE類別=@lb，其中@lb為函數(shù)的傳人參數(shù)，然后在銷售表中用GROUP BY對(duì)商品號(hào)進(jìn)行分組，并采用SUM計(jì)算每個(gè)分組的總和。
【參考答案】
第一空：RETURNS
第一空：table
第三空：SELECT a．商品號(hào)，SUM(銷售數(shù)量*(銷售單價(jià)一進(jìn)貨單價(jià)))AS總利潤(rùn)FROM銷售表a JOIN商品表b ON a．商品號(hào)=b．商品號(hào)WHERE a．商品號(hào)IN(SELECT商品號(hào)FROM商品表WHERE類別=@lb)GROUP BY a．商品號(hào)0RDER BY總利潤(rùn)DESC
第四空：RETURN@ProfitTable
3．【解題思路】
數(shù)據(jù)庫(kù)性能優(yōu)化的基本原則就是通過(guò)盡可能少的磁盤訪問(wèn)獲得所需要的數(shù)據(jù)。SQL SERVER性能優(yōu)化一般從數(shù)據(jù)庫(kù)設(shè)計(jì)、應(yīng)用程序編碼、硬件優(yōu)化、數(shù)據(jù)庫(kù)索引、SQL語(yǔ)句、事務(wù)處理幾個(gè)方面人手考慮問(wèn)題。
(1)分析階段：在系統(tǒng)分析階段往往有太多需要關(guān)注的地方，系統(tǒng)各種功能性、可用性、可靠性、安全性需求吸引了我們大部分的注意力，但必須注意的是，性能往往是很重要的非功能性需求，必須根據(jù)系統(tǒng)的特點(diǎn)確定其實(shí)時(shí)性需求、響應(yīng)時(shí)間的需求、硬件的配置等。能有各種需求量化的指標(biāo)。
(2)設(shè)計(jì)階段：例如數(shù)據(jù)庫(kù)邏輯設(shè)計(jì)規(guī)范化；合理的冗余；主鍵的設(shè)計(jì)；外鍵的設(shè)計(jì)；字段的設(shè)計(jì)；數(shù)據(jù)庫(kù)物理存儲(chǔ)和環(huán)境的設(shè)計(jì)；數(shù)據(jù)庫(kù)的物理存儲(chǔ)、操作系統(tǒng)環(huán)境及網(wǎng)絡(luò)環(huán)境的設(shè)計(jì)，皆使得我們的系統(tǒng)在將來(lái)能適應(yīng)較多用戶的并發(fā)操作和較大的數(shù)據(jù)處理量。這里需要注意文件組的作用，適用文件組可以有效的把I／O操作分散到不同的物理硬盤，提高并發(fā)能力。
(3)系統(tǒng)設(shè)計(jì)：整個(gè)系統(tǒng)的設(shè)計(jì)，特別是系統(tǒng)結(jié)構(gòu)的設(shè)計(jì)對(duì)性能具有很大的影響。對(duì)于一般的OLTP系統(tǒng)，可以選擇C／S結(jié)構(gòu)、三層的C／S結(jié)構(gòu)等，不同的系統(tǒng)結(jié)構(gòu)其性能的關(guān)鍵也有所不同。系統(tǒng)設(shè)計(jì)階段應(yīng)歸納某些業(yè)務(wù)邏輯在數(shù)據(jù)庫(kù)編程階段實(shí)現(xiàn)，數(shù)據(jù)庫(kù)編程包括數(shù)據(jù)庫(kù)存儲(chǔ)過(guò)程、觸發(fā)器和函數(shù)。用數(shù)據(jù)庫(kù)編程實(shí)現(xiàn)業(yè)務(wù)邏輯的好處是減少網(wǎng)絡(luò)流量并能更充分利用數(shù)據(jù)庫(kù)的預(yù)編譯和緩存功能；索引設(shè)計(jì)階段可以根據(jù)功能和性能的需求進(jìn)行初步的索引設(shè)計(jì)，這里需要根據(jù)預(yù)計(jì)的數(shù)據(jù)量和查詢來(lái)設(shè)計(jì)索引，可能與將來(lái)實(shí)際使用時(shí)有所區(qū)別。
(4)編碼階段：編碼階段首先需要所有程序員具備優(yōu)化意識(shí)，也就是在實(shí)現(xiàn)功能的同時(shí)具備考慮優(yōu)化性能的思想。數(shù)據(jù)庫(kù)是能進(jìn)行集合運(yùn)算的工具，所謂集合運(yùn)算實(shí)際是批量運(yùn)算，即是盡量減少在客戶端進(jìn)行大數(shù)據(jù)量的循環(huán)操作，而用SQL語(yǔ)句或者存儲(chǔ)過(guò)程代替。這個(gè)階段主要是注意在SQL語(yǔ)句等方面的優(yōu)化，如：盡量少做重復(fù)的工作，用SELECT后跟需要的字段代替SELECT*語(yǔ)句，注意事務(wù)和鎖，注意I臨時(shí)表和表變量的用法，慎用游標(biāo)和觸發(fā)器，盡量使用索引等。
(5)硬件優(yōu)化：RAID(獨(dú)立磁盤冗余陣列)是由多個(gè)磁盤驅(qū)動(dòng)器(一個(gè)陣列)組成的磁盤系統(tǒng)。通過(guò)將磁盤陣列當(dāng)作一個(gè)磁盤來(lái)對(duì)待，基于硬件的RAID允許用戶管理多個(gè)磁盤。使用基于硬件的RAID與基于操作系統(tǒng)的RAID相比較可知，基于硬件的RAID能夠提供更佳的性能，如果使用基于操作系統(tǒng)的RAID，那么它將占據(jù)其他系統(tǒng)需求的CPU周期，通過(guò)使用基于硬件的RAID，用戶在不關(guān)閉系統(tǒng)的情況下能夠替換發(fā)生故障的驅(qū)動(dòng)器。利用數(shù)據(jù)庫(kù)分區(qū)技術(shù)，可均勻地把數(shù)據(jù)分布在系統(tǒng)的磁盤中，平衡I／0訪問(wèn)，避免I／0瓶頸等。
(6)事務(wù)處理調(diào)優(yōu)：數(shù)據(jù)庫(kù)的日常運(yùn)行過(guò)程中，可能面臨多個(gè)用戶同時(shí)對(duì)數(shù)據(jù)庫(kù)的并發(fā)操作而帶來(lái)的數(shù)據(jù)不一致的問(wèn)題，如：丟失更新、臟讀和不可重復(fù)讀等。并發(fā)控制的主要方法是封鎖，鎖的含義即是在一段時(shí)間內(nèi)禁止用戶做某些操作以避免產(chǎn)生數(shù)據(jù)不一致。對(duì)于事務(wù)性能的調(diào)優(yōu)，要考慮到事務(wù)使用的鎖的個(gè)數(shù)(在所有其他條件相同的情況下，使用的鎖個(gè)數(shù)越少，性能越好)、鎖的類型(讀鎖對(duì)性能更有利)以及事務(wù)持有鎖的時(shí)間長(zhǎng)短(持有時(shí)間越短，性能越好)等情形。
【參考答案】
根據(jù)SQL Server 2008數(shù)據(jù)庫(kù)的特性以及題目中的條件，綜合給出以下的調(diào)優(yōu)方案。
(1)表結(jié)構(gòu)優(yōu)化：重新優(yōu)化數(shù)據(jù)庫(kù)設(shè)計(jì)結(jié)構(gòu)，規(guī)范數(shù)據(jù)庫(kù)邏輯設(shè)計(jì)；設(shè)計(jì)主鍵和外鍵；設(shè)計(jì)合適大小的字段。
(2)硬件優(yōu)化：購(gòu)買一塊同樣大小的硬盤，將硬盤做成RAID5，用以提高數(shù)據(jù)庫(kù)讀寫(xiě)速度；增加服務(wù)器CPU個(gè)數(shù)；擴(kuò)大服務(wù)器的內(nèi)存。
(3)索引優(yōu)化：采用對(duì)經(jīng)常作為條件查詢的列設(shè)計(jì)索引，在查詢中經(jīng)常用到的列上建立非聚簇索引，在頻繁進(jìn)行范圍查詢、排序、分組的列上建立聚簇索引，對(duì)于有頻繁進(jìn)行刪除、插入操作的表不要建立過(guò)多的索引。
(4)采用視圖：合理使用視圖和分區(qū)視圖，在需要更新和刪除操作不多、查詢操作頻繁的表上建立索引視圖。
(5)SQL語(yǔ)句優(yōu)化：選擇運(yùn)算應(yīng)盡可能先做，并在對(duì)同一個(gè)表進(jìn)行多個(gè)選擇運(yùn)算時(shí)，選擇影響較大的語(yǔ)句放在前面，較弱的選擇條件寫(xiě)在后面，這樣就可以先根據(jù)較嚴(yán)格的條件得出數(shù)據(jù)較少的信息，再在這些信息中根據(jù)后面較弱的條件得到滿足條件的信息。應(yīng)避免使用相關(guān)子查詢，把子查詢轉(zhuǎn)換成聯(lián)結(jié)來(lái)實(shí)現(xiàn)。字段提取按照“需多少，提多少’’的原則，避免“SELECT*”，“SELECT*”需要數(shù)據(jù)庫(kù)返回相應(yīng)表的所有列信息，這對(duì)于一個(gè)列較多的表無(wú)疑是一項(xiàng)費(fèi)時(shí)的操作，采用存儲(chǔ)過(guò)程，使用存儲(chǔ)過(guò)程提高數(shù)據(jù)處理速度。