三、設(shè)計(jì)與應(yīng)用題

　　45某書店采用了SQL Server 2008數(shù)據(jù)庫(kù)管理系統(tǒng)，該書店有一個(gè)需求，需要統(tǒng)計(jì)指定年份中每一本書的銷售總額，例如：查詢2012年所有書的銷售總額。

　　已知圖書結(jié)構(gòu)如下：

　　圖書表(書號(hào)BOOK_ID，書名BOOK_NAME，單價(jià)BOOK_PRICE)

　　銷售表(書號(hào)BOOK_ID，銷售時(shí)間SALE_TIME，銷售數(shù)量SALE_NUM)。

　　假設(shè)單價(jià)和銷售數(shù)量均為int型，書號(hào)和書名均為varchar(50)類型，銷售時(shí)問(wèn)為datetime型。請(qǐng)給出滿足如下要求的多語(yǔ)句表值函數(shù)，該函數(shù)統(tǒng)計(jì)指定年份中每本書的銷售總額。(10分)設(shè)函數(shù)名為：BOOK_PROFIT(@year　int)，函數(shù)的返回結(jié)果格式如下：

　　書號(hào)銷售總額

　　B001　60000

　　A004　50000

　　參考解析：

　　【解題思路】

　　采用JOIN聯(lián)合查詢，先用WHERE條件查出符合銷售時(shí)間=@year的記錄，再將找出的記錄和圖書表合并，并采用單價(jià)*銷售數(shù)量計(jì)算出聯(lián)合查詢的表數(shù)據(jù)，最后根據(jù)GROUP BY統(tǒng)計(jì)每種書的銷售價(jià)格總和。

　　【參考答案】

　　CREATE FUNCTION BOOK_PROFIT(@year int)

　　RETURNS@f_BOOK_PROFIT table(

　　書號(hào)varchar(50)，

　　銷售總額int)

　　AS

　　BEGIN

　　INSERT INTO@f_BOOK_PROFIT

　　SELECT a書號(hào)，SUM(a單價(jià)*b銷售數(shù)量)

　　FROM圖書表a JOIN銷售表b ON a書號(hào)=b書號(hào)

　　WHERE year(b銷售時(shí)間)=@year

　　GROUP BY a.書號(hào)

　　RETURN

　　END

　　或者

　　CREATE FUNCTION BOOK_PROFIT(@year int)

　　RETURNS@f_BOOK_PROFlT table(

　　BOOK_ID varchar(50)，

　　PROFIT int)

　　AS

　　BEGIN

　　INSERT INTO@f_BOOK_PROFIT

　　SELECT a.BOOK_ID，SUM(a.BOOK_PRICE*b,SALE_NUM)

　　FROM BOOK a JOIN SALE b ON a.BOOK ID=b.B00K ID

　　WHERE year(bSALE_TIME)=@year

　　GROUP BY a.BOOK_ID

　　RETURN

　　END

　　46某商場(chǎng)商品經(jīng)營(yíng)管理系統(tǒng)使用SQL Server 2008數(shù)據(jù)庫(kù)管理系統(tǒng)，此系統(tǒng)上線運(yùn)行1年后，業(yè)務(wù)人員使用某統(tǒng)計(jì)功能(此功能每月使用一次)時(shí)發(fā)現(xiàn)速度很慢。該統(tǒng)計(jì)功能主要執(zhí)行的SQL語(yǔ)句如下：

　　SELECT商品號(hào)，SUM(銷售數(shù)量*銷售價(jià)格)銷售額

　　FROM銷售明細(xì)

　　GROUP BY商品號(hào);

　　該銷售明細(xì)表的建表語(yǔ)句如下：

　　CREATE TABLE銷售明細(xì)(

　　序列號(hào)intIDENTITY(1，1)NOT NULL，

　　商品號(hào)intNOT NULL，

　　銷售日期datetime NULL，

　　銷售數(shù)量intNOT NULL，

　　銷售價(jià)格intNOT NULL

　　);

　　并在銷售明細(xì)表上建有如下索引：

　　CREATE index ix_銷售明細(xì)_商品號(hào)on銷售明細(xì)(商品號(hào));

　　某技術(shù)人員提出通過(guò)執(zhí)行下述語(yǔ)句以提高此查詢的運(yùn)行效率：

　　CREATE VIEW商品銷售額視圖

　　WITH SCHEMABINDING

　　AS

　　SELECT商品號(hào)，SUM(銷售數(shù)量*銷售價(jià)格)銷售額，

　　COUNT_BIG(*)cnt

　　FROM db0.銷售明細(xì)

　　GROUP BY商品號(hào);

　　CREATE UNIQUE CLUSTERED INDEX ix_商品銷售額

　　ON商品銷售額視圖(商品號(hào));

　　(1)請(qǐng)分析該技術(shù)人員給出的語(yǔ)句功能以及對(duì)原有查詢語(yǔ)句的性能影響，并給出原因。

　　(2)此商場(chǎng)的銷售量很大，每天有大量數(shù)據(jù)插入到銷售明細(xì)表中。請(qǐng)從數(shù)據(jù)庫(kù)整體性能角度分析，此技術(shù)人員提出的優(yōu)化方法是否合適，并給出原因。

　　參考解析：

　　(1)【解題思路】

　　該技術(shù)人員使用了帶有索引的視圖，將所關(guān)心的數(shù)據(jù)(商品號(hào)，銷售額，該商品號(hào)在表中出現(xiàn)的次數(shù))從銷售明細(xì)表中提取出來(lái)建立視圖，并對(duì)該視圖建立按商品號(hào)排序的聚簇索引，這樣大大減少了在搜索不同商品的銷售額時(shí)調(diào)用的數(shù)據(jù)表的規(guī)模，從而提高了查詢效率。由于表的數(shù)據(jù)規(guī)模很大，建立該視圖后，同一種商品不會(huì)多次出現(xiàn)在表中，而是通過(guò)一個(gè)計(jì)數(shù)變量cnt表示，即在檢索時(shí)大大減少了檢索規(guī)模。創(chuàng)建索引時(shí)，UNIQUE關(guān)鍵字表明此索引的每一個(gè)索引值只對(duì)應(yīng)唯一的數(shù)據(jù)記錄。CLUSTER表示要建立的索引是聚簇索引(所謂聚簇索引是指索引項(xiàng)的順序與表中記錄的物理順序一致的索引組織)。

　　【參考答案】

　　語(yǔ)句功能：建立包含所關(guān)心數(shù)據(jù)(商品號(hào)，銷售額，該商品號(hào)在表中出現(xiàn)的次數(shù))的帶索引的視圖，并建立按商品號(hào)對(duì)應(yīng)銷售額UNIQUE聚簇排序的索引，從而大大縮小了查詢語(yǔ)句的查詢范圍，提高了查詢效率。

　　原因：視圖問(wèn)接相關(guān)的屬性列(序列號(hào)，銷售日期，商品號(hào)，銷售數(shù)量，銷售價(jià)格)轉(zhuǎn)換成了目標(biāo)屬性列，減少了搜索空問(wèn)，同時(shí)建立UNIQUE CLUSTERED索引，使查詢商品號(hào)的數(shù)據(jù)記錄唯一，因此降低了搜索范圍，提高了搜索效率。

　　(2)【解題思路】

　　由于視圖是不實(shí)際存儲(chǔ)數(shù)據(jù)的虛表，因此對(duì)視圖的更新最終要轉(zhuǎn)換為對(duì)基本表的更新。而用戶通過(guò)視圖對(duì)數(shù)據(jù)進(jìn)行增加、刪除、修改時(shí)，有意或無(wú)意地對(duì)不屬于視圖范圍內(nèi)的基本表數(shù)據(jù)進(jìn)行操作，會(huì)破壞數(shù)據(jù)的一致性。而且視圖中的數(shù)據(jù)本身就是冗余的，每次對(duì)表進(jìn)行修改時(shí)，同時(shí)也要對(duì)相應(yīng)的視圖進(jìn)行修改，這大大增加了系統(tǒng)的負(fù)擔(dān)。

　　【參考答案】不合適，每天大量的插入操作使得在修改表的同時(shí)也要對(duì)視圖進(jìn)行修改，增加了系統(tǒng)的負(fù)擔(dān)，然而該統(tǒng)計(jì)功能一個(gè)月才用一次，這樣導(dǎo)致系統(tǒng)的利用率也較為低下。

　　47設(shè)有圖書管理數(shù)據(jù)庫(kù)，包含三張表：

　　圖書明細(xì)表(圖書編號(hào)，圖書類別，圖書名稱，作者，出版社，出版日期，定價(jià));

　　讀者表(借書證號(hào)，姓名，系別，辦證日期);

　　借出信息表(借出編號(hào)，借書證號(hào)，圖書編號(hào)，借書日期);

　　完成下列操作：

　　定義一個(gè)多語(yǔ)句表值函數(shù)，用于查詢學(xué)生借書情況，只需提供參數(shù)：借書證號(hào)，就可以通過(guò)調(diào)用函數(shù)返回此學(xué)生的借書情況，若有借書籍，則返回所借書籍的編號(hào)、書籍名稱、定價(jià)和借書日期;若沒(méi)有在借書籍，則返回記錄為空。設(shè)函數(shù)名為：f_BorrowBook(@jszh char(20))。

　　參考解析：

　　【解題思路】

　　SQL Server 2008多語(yǔ)句表值函數(shù)的格式為：

　　CREATE FUNCTION[schema_name.]function_name

　　([{@parameter_name[As][type_schema_name.]parameter data_type

　　[=default]}

　　[….n]

　　]

　　)

　　RETURNS@return_variable TABLE

　　[AS]

　　BEGIN

　　function_body

　　RETURN

　　END

　　[;]

　　：：=

　　({I}

　　[table_constraint][，...n])

　　采用三表聯(lián)合查詢，通過(guò)傳入的借書證號(hào)查出借出信息表中的圖書編號(hào)，然后查出圖書明細(xì)表的信息。

　　【參考答案】

　　CREATE FUNCTION BorrowBook(@jszh char(20))

　　RETURNS@jsqkb TABLE(書籍編號(hào)char(20)，書籍名稱char(50)，定價(jià)float，借書日期datetime)

　　AS

　　BEGIN

　　INSERT@jsqkb

　　SELECT圖書明細(xì)表.圖書編號(hào)，圖書名稱，定價(jià)，借出信息表.借書日期FROM借出信息表，圖書明細(xì)表

　　WHERE借出信息表.圖書編號(hào)=圖書明細(xì)表.圖書編號(hào)AND借出信息表.借書證號(hào)=@jszh

　　RETURN

　　END