三、設計與應用題(共30分)
1．設有高校選課系統(tǒng)，需要對學校的系信息、教師信息、課程信息、學生信息、學生選課信息進行管理。已知系(DEPT)信息包括系編號(DeptNO)、系名稱(DeptName)；教師(Teacher)信息包括教師號(TNO)、教師名(Tname)；課程(Course)信息包括課程號(CNO)、課程名(CName)、課程學分(Credit)；學生(Student)信息包括學號(SNO)、學生姓名(Sname)、學生性別(Sex)。
選課系統(tǒng)的管理規(guī)則如下：
Ⅰ．一個系可聘用多名教師，一個教師只受聘于一個系；
Ⅱ．一個系可有多名學生，一個學生只屬于一個系；
Ⅲ．一名教師可講授多門課程，一門課程可由多名教師講授；
Ⅳ．一名學生可選修多門課程，一門課程可被多名學生選修；
V．學生選修完課程后，可獲得相應課程的成績。
針對以上描述，完成下列設計內(nèi)容：
(1)構(gòu)建選修課系統(tǒng)的ER圖。(要求圖中的實體集名用試卷中給出的英文名，聯(lián)系所關聯(lián)的實體集名的首字母，字母問用“一”或“_”連接，大小寫不限)。(6分)
(2)根據(jù)所構(gòu)建的ER圖，設計滿足3NF的關系模式，并標出每個關系模式的主碼和外碼。(要求關系模式名同實體集名或聯(lián)系名，屬性名用試卷中給出的英文名，大小寫不限)(4分)
2．某書店采用了SQL Server 2008數(shù)據(jù)庫管理系統(tǒng)，該書店有一個需求，需要統(tǒng)計指定年份中每一本書的銷售總額，例如：查詢2012年所有書的銷售總額。
已知圖書結(jié)構(gòu)如下：
圖書表(書號BOOK_ID，書名BOOK_NAME，單價BOOK_PRICE)
銷售表(書號BOOK_ID，銷售時間SALE_TIME，銷售數(shù)量SALE_NUM)。
假設單價和銷售數(shù)量均為int型，書號和書名均為varchar(50)類型，銷售時問為datetime型。請給出滿足如下要求的多語句表值函數(shù)，該函數(shù)統(tǒng)計指定年份中每本書的銷售總額。(10分)設函數(shù)名為：BOOK_PROFIT(@year int)，函數(shù)的返回結(jié)果格式如下：
書號銷售總額
B001 60000
A004 50000
3．某網(wǎng)上商城因為業(yè)務發(fā)展，原有的系統(tǒng)不能很好的滿足需要，因此采用了一套新的經(jīng)營管理系統(tǒng)，此系統(tǒng)使用SQL Server 2008數(shù)據(jù)庫管理系統(tǒng)。此系統(tǒng)上線運行前，需將商場原有的數(shù)據(jù)導入到新系統(tǒng)中。原有系統(tǒng)使用SQL Server 2000，數(shù)據(jù)結(jié)構(gòu)與新系統(tǒng)不完全一致。因此需要把數(shù)據(jù)從SQL Server 2000導入到SQL Server 2008中，為了保證數(shù)據(jù)一致性，數(shù)據(jù)導入過程中要求暫停業(yè)務且必須在3小時內(nèi)完成。
(1)在原有數(shù)據(jù)導入新系統(tǒng)的過程中，實施人員發(fā)現(xiàn)原有數(shù)據(jù)量很大，導人數(shù)據(jù)需要四小時，業(yè)務無法接受。經(jīng)分析某工程師認為，數(shù)據(jù)導入過程中的數(shù)據(jù)庫I／O很高，但導人數(shù)據(jù)的程序本身對系統(tǒng)資源占用率很低。該工程師建議將數(shù)據(jù)導入過程中的數(shù)據(jù)恢復模式從“完整”模式改為“簡單”模式以提高數(shù)據(jù)導人速度；而另一位工程師則認為此方法未必能提高數(shù)據(jù)導入速度，而且還可能導致數(shù)據(jù)丟失，不建議使用此方法。
請分析此方法是否能夠提高數(shù)據(jù)導入速度并給出理由，同時分析此操作的數(shù)據(jù)丟失風險。(5分)
(2)在成功導入歷史數(shù)據(jù)后，此系統(tǒng)順利上線運行。在上線運行的第一周，發(fā)現(xiàn)數(shù)據(jù)庫服務器的CPU使用率很高，達到近90％，高峰期間達到100％，且系統(tǒng)內(nèi)存占用率達到90％，但系統(tǒng)I／O很輕。業(yè)務人員反應系統(tǒng)操作速度很慢。為了提高系統(tǒng)運行速度。在不修改應用程序的前提下，兩位工程師提出了不同的解決辦法：
Ⅰ．為服務器增加2顆CPU，緩解CPU使用率很高的問題；
Ⅱ．為服務器增加一倍內(nèi)存，緩解內(nèi)存使用率很高的問題。
考慮成本，現(xiàn)階段只能按照一種方案實施。請指出在現(xiàn)有情況下，哪種方案更合理并給出理由。(5分)
三、設計與應用題
1．【解題思路】
(1)ER圖的畫法
建立相應的ER圖的過程如下：
第一，確定實體類型。本題有四個實體類型，即Teacher實體，Course實體，Student實體和DEPT實體。
第二，確定聯(lián)系類型。Teacher實體與Course實體之間是多對多關系，即T-C關系，Student實體與Course實體之間是多對多關系，即S-C關系。DEPT實體與Teacher實體之間是一對多關系，即D-T關系。DEPT實體與Student實體之間是一對多關系，即D-S關系。
第三，把實體類型和聯(lián)系類型組合成ER圖。
第四，確定實體類型和聯(lián)系類型的屬性。
Teacher實體集屬性：教師號、教師名
Course實體集屬性：課程號、課程名、課程學分
Student實體集屬性：學號、學生姓名、學生性別
DEPT實體集屬性：系編號、系名稱
(2)ER模型轉(zhuǎn)換為關系模式的規(guī)則
①把ER模型中的每一個實體集轉(zhuǎn)換為同名的關系，實體集的屬性就是關系的屬性，實體集的碼就是關系的碼。
②把ER模型中的每一個聯(lián)系轉(zhuǎn)換成一個關系，與該聯(lián)系相連的各實體集的碼以及聯(lián)系的屬性轉(zhuǎn)換為關系的屬性。關系的碼根據(jù)下列情況確定。
若聯(lián)系為1：1，則每個實體集碼均是該關系的候選碼。
若聯(lián)系為1：n，則關系的碼為n端實體集的碼。
若聯(lián)系為m：n，則為各實體集碼的組合或其中一部分實體集碼的組合。
③合并具有相同碼的關系。根據(jù)規(guī)則，把一個ER模型轉(zhuǎn)換為關系模式，一般經(jīng)歷下面兩個步驟：
第一，標識ER模型中的聯(lián)系。
第二，依次轉(zhuǎn)換與每個聯(lián)系相關聯(lián)的實體集及聯(lián)系。
【參考答案】
ER圖如下所示：
設計3NF，如下：
DEFY(DeptN0，DeptName)，主碼：DeptN0，無外碼
Teacher(TN0，TName，DeptNO)，主碼TN0，外碼DeptNO
Student(SN0，SName，Sex，DeptNO)，主碼SN0，外碼DeptNO
Course(CN0，CName，Credit)，主碼CN0，無外碼
T-C(TN0，CNO)，主碼(TN0，CNO)，外碼TN0，CNO
S-C(SN0，CN0，成績)，主碼(SN0，CNO)，外碼SN0，CNO
2．【解題思路】
采用JOIN聯(lián)合查詢，先用WHERE條件查出符合銷售時間=@year的記錄，再將找出的記錄和圖書表合并，并采用單價*銷售數(shù)量計算出聯(lián)合查詢的表數(shù)據(jù)，后根據(jù)GROUP BY統(tǒng)計每種書的銷售價格總和。
【參考答案】
CREATE FUNCTION BOOK_PROFIT(@year int)
RETURNS@f_BOOK_PROFIT table(
書號varchar(50)，
銷售總額int)
AS
BEGIN
INSERT INTO@f_BOOK_PROFIT
SELECT a書號，SUM(a單價*b銷售數(shù)量)
FROM圖書表a JOIN銷售表b ON a書號=b書號
WHERE year(b銷售時間)=@year
GROUP BY a．書號
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
3．【解題思路】
(1)SQL Server 2008的數(shù)據(jù)恢復模式有三種：
①簡單恢復模式，此方法可以大幅度減少事務13志的管理開銷，因為恢復模式不備份事務日志。但是如果數(shù)據(jù)庫損壞，則簡單恢復模式將面臨極大的數(shù)據(jù)丟失風險。在這種恢復模式下，數(shù)據(jù)只能恢復到新備份狀態(tài)。因此對于用戶數(shù)據(jù)庫，簡單恢復模式只適用于測試和開發(fā)數(shù)據(jù)庫，或用于主要包含只讀數(shù)據(jù)的數(shù)據(jù)庫。
②完整恢復模式，此方法可以完整的記錄所有事務，并將事務13志記錄保留到對其備份完畢為止。此方法相對簡單恢復模式來說，更占用時間。
③大容量日志恢復模式，此方法只對大容量操作進行小記錄，使事務日志不會被大容量加載操作所填充。
在數(shù)據(jù)導入過程中，所有的業(yè)務都是暫停的，因此可以采用簡單恢復模式提高數(shù)據(jù)導入速度。且只在數(shù)據(jù)導入的過程中暫時的修改恢復模式，因此數(shù)據(jù)并不會丟失。
(2)提高數(shù)據(jù)庫性能的方法一般是從外部環(huán)境、調(diào)整內(nèi)存分配、調(diào)整磁盤I／0、調(diào)整競爭資源等幾方面著手來改變數(shù)據(jù)庫的參數(shù)。SQL Server 2008采用將數(shù)據(jù)緩沖在內(nèi)存的方式，因此在數(shù)據(jù)庫系統(tǒng)運行的過程中會占用一定的內(nèi)存，又因為I／O并不存在問題，說明內(nèi)存尚滿足需求。CPU使用率很高，表明CPU的計算能力不足，應該增加CPU的數(shù)量。
【參考答案】
(1)此方法能夠提高數(shù)據(jù)導入速度。原因：此系統(tǒng)I／O很高，修改恢復模式后，系統(tǒng)大限度減少日志開銷，可提高導入速度。由于僅在數(shù)據(jù)導入過程中修改恢復模式，所以并無數(shù)據(jù)丟失風險。
(2)第一種方案比較合理。原因：SQL Server 2008采用將數(shù)據(jù)緩沖在內(nèi)存的方式，因此內(nèi)存的使用率比較高是正常情況，且現(xiàn)階段I／O并不存在問題，表明內(nèi)存滿足需求。此階段CPU使用率很高，表明CPU計算資源不足，因此增加CPU數(shù)量對解決問題有效。