三、設(shè)計(jì)與應(yīng)用題(共30分)
1．設(shè)某連鎖商店數(shù)據(jù)庫(kù)中有關(guān)系模式R：
R(商店編號(hào)，商品編號(hào)，庫(kù)存數(shù)量，部門編號(hào)，負(fù)責(zé)人)
如果規(guī)定：每個(gè)商店的每種商品只在一個(gè)部門銷售，每個(gè)商店的每個(gè)部門只有一個(gè)負(fù)責(zé)人，每個(gè)商店的每種商品只有一個(gè)庫(kù)存數(shù)量。(10分)
(1)請(qǐng)根據(jù)上述規(guī)定，寫出關(guān)系模式R的函數(shù)依賴集；
(2)請(qǐng)給出關(guān)系模式R的候選碼；
(3)請(qǐng)說(shuō)明關(guān)系模式R屬于第幾范式，并給出理由；
(4)請(qǐng)將R分解成滿足3NF的關(guān)系模式。
2．設(shè)在SQL Server 2008某數(shù)據(jù)庫(kù)中，已建立了四個(gè)文件組：fg1、f92、f93和f94，以及一個(gè)分區(qū)函數(shù)RangePF1。
RangePF1的定義代碼如下：
CREATE PARTITION FUNCTION RangePFl(int)
AS RANGE LEFT FOR VALUES(100，200，300)
(1)請(qǐng)基于RangePFl創(chuàng)建一個(gè)分區(qū)方案RangePSl，每個(gè)分區(qū)對(duì)應(yīng)一個(gè)文件組。(5分)
(2)請(qǐng)使用RangePSl創(chuàng)建一個(gè)分區(qū)表PartitionT(Gid，GoodName，Price)，該表基于Gid列創(chuàng)建分區(qū)。其中Gid：int類型，取值大于等于1；GoodName：統(tǒng)一字符編碼可變長(zhǎng)類型，最多可存儲(chǔ)20個(gè)漢字；Price：定點(diǎn)小數(shù)類型，小數(shù)部分1位，整數(shù)部分到千位。(5分)
3．某商場(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))；(10分)
(1)請(qǐng)分析該技術(shù)人員給出的語(yǔ)句功能以及對(duì)原有查詢語(yǔ)句的性能影響，并給出原因。
(2)此商場(chǎng)的銷售量很大，每天有大量數(shù)據(jù)插入到銷售明細(xì)表中。請(qǐng)從數(shù)據(jù)庫(kù)整體性能角度分析，此技術(shù)人員提出的優(yōu)化方法是否合適，并給出原因。
三、設(shè)計(jì)與應(yīng)用題
1．(1)【解題思路】
函數(shù)依賴定義：設(shè)R(u)是屬性集U上的關(guān)系模式，X，Y是U的子集，若對(duì)于R(U)的任意一個(gè)可能的關(guān)系r,r中不可能存在兩個(gè)元組在X上的屬性值相等，在Y上的屬性值不等，則稱X函數(shù)確定Y或Y函數(shù)依賴X，記作X->Y。函數(shù)依賴是指關(guān)系R的一切關(guān)系均要滿足的約束條件。
【參考答案】
(商店編號(hào)，商品編號(hào))->部門編號(hào)，(商店編號(hào)，部門編號(hào))->負(fù)責(zé)人，(商店編號(hào)，商品編號(hào))->庫(kù)存量。
(2)【解題思路】
設(shè)K為R中的屬性或?qū)傩越M合，若u完全依賴于K，則K為R的候選碼。
【參考答案】
(商店編號(hào)，商品編號(hào))
(3)【解題思路】
關(guān)系數(shù)據(jù)庫(kù)是要滿足一定要求的。滿足最低要求的叫第一范式，在第一范式中滿足進(jìn)一步要求的為第二范式，其余以此類推。顯然該關(guān)系模式滿足第一范式，接下來(lái)檢查其是否滿足第二范式。在第二范式中，要求關(guān)系模式中不存在部分依賴，每一個(gè)非主屬性完全依賴于碼，而根據(jù)第一空可得如下依賴關(guān)系：(部門編號(hào)，商店編號(hào))->負(fù)責(zé)人，所以屬于第二范式。它的非主屬性(不包含在任何候選碼中的屬性)有3個(gè)：部門編號(hào)、負(fù)責(zé)人和庫(kù)存量，并皆完全函數(shù)依賴于主碼。將(商店編號(hào)、商品編號(hào))記作X，(商店編號(hào)、部門編號(hào))記作Y，負(fù)責(zé)人記作Z，即x→Y，Y→Z。由此可以看出，存在傳遞依賴，故不屬于第三范式。
【參考答案】
第二范式
(4)【解題思路】
第三范式中要求每一個(gè)屬性既不部分依賴于碼也不傳遞依賴于碼。
【參考答案】
R1(商店編號(hào)、商品編號(hào)、部門編號(hào)、庫(kù)存量)；B2(商店編號(hào)、部門編號(hào)、負(fù)責(zé)人)。
2．(1)【解題思路】
數(shù)據(jù)實(shí)際上是依附于表而存在，我們將表放入到文件組中，而文件組是一個(gè)邏輯的概念，其實(shí)體是輔助數(shù)據(jù)庫(kù)文件(ndf)，所以就等于將我們指定的數(shù)據(jù)放入到了指定的輔助數(shù)據(jù)庫(kù)文件中，然后將這些輔助數(shù)據(jù)庫(kù)文件放人不同的磁盤分區(qū)中，就可以有針對(duì)性的對(duì)相應(yīng)的數(shù)據(jù)實(shí)現(xiàn)性能的優(yōu)化。
【參考答案】
create partition scheme RangePSl
as partition RangePFl
to(fgl,f91．fgl，f92)
(2)【解題思路】
創(chuàng)建分區(qū)表可通過(guò)以下幾個(gè)步驟實(shí)現(xiàn)：
①創(chuàng)建分區(qū)函數(shù)。
②創(chuàng)建分區(qū)方案。
③使用分區(qū)方案創(chuàng)建表。
【參考答案】
創(chuàng)建分區(qū)表：
create table orders
(
GID int identity(1，1)primary key，
GoodName varchar(40)，
Price float
)
on RangePSl(GID)
3．(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ǔ)句的查詢范圍，提高了查詢效率。
原因：視圖中將問接相關(guān)的屬性列(序列號(hào)，銷售日期，商品號(hào)，銷售數(shù)量，銷售價(jià)格)轉(zhuǎn)換成了目標(biāo)屬性列，減少了搜索空問，同時(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)的利用率也較為低下。