三、設計與應用題(共30分)
1．已知某教務管理系統(tǒng)的設計人員在需求分析階段收集到下列原始數據表格：
教師
|
教師號 |
教師名 |
職稱 |
工資 |
上級領導教師號 |
|
9868 |
王文華 |
教授 |
8000 |
null |
|
9983 |
李一斌 |
副教授 |
6000 |
9868 |
|
9985 |
丁一 |
講師 |
刪 |
9868 |
|
0783 |
王潤澤 |
講師 |
刪 |
9868 |
|
0899 |
歐陽丹妮 |
講師 |
刪 |
9868 |
課程
|
課程號 |
課程名 |
學分 |
教材號 |
教材名 |
出版社名 |
任課教師號 |
|
C2006 |
計算機原理 |
3 |
11 |
計算機原理 |
清華大學出版社 |
9868 |
|
C2006 |
計算機原理 |
3 |
12 |
計算機原理與應用 |
高等教育出版社 |
9868 |
|
C2004 |
數據結構 |
3 |
13 |
數據結構 |
清華大學出版社 |
9868 |
|
C2010 |
數據庫原理 |
13 |
14 |
數據庫原理 |
清華大學出版社 |
9868 |
|
C2010 |
數據庫原理 |
3 |
15 |
數據庫原理與技術 |
高等教育出版社 |
9868 |
|
S3001 |
音樂欣賞 |
2 |
16 |
音樂欣賞 |
清華大學出版社 |
9983 |
已知該業(yè)務系統(tǒng)存在如下規(guī)則：
Ⅰ．每個教師有的教師號，每個教師號對應的一名教師；
Ⅱ．每門課程有的課程號，每個課程號對應的一門課程；
Ⅲ．每本教材有的教材號，每個教材號對應的一本教材；
Ⅳ．每個教師多只有一個上級領導，也可以沒有上級領導；
V．一門課程僅由一名教師講授；
Ⅵ．一本教材僅用于一門課程。(12分)
(1)請根據原始數據表及業(yè)務規(guī)則，給出該系統(tǒng)的關系模式，保證每個關系模式滿足3NF，并說明每個關系模式的主碼和外碼。
(2)畫出該系統(tǒng)的ER圖，ER圖中需給出每個實體集的屬性，主碼屬性用下劃線標識。
2．在SQL Server 2008中，設某數據庫中有商品表(商品號，商品名，進貨價格)，商品號為主碼；銷售表(商品號，銷售時間，銷售數量，銷售價格，本次利潤)，商品號和銷售時間為主碼，銷售價格為本次銷售商品的單價?，F要求每當在銷售表中插入前4列數據時(假設只插入一行數據)，系統(tǒng)自動計算本次銷售產生的利潤，并將該利潤賦給銷售表的第5列“本次利潤”。請編寫實現上述功能的后觸發(fā)型觸發(fā)器代碼。(10分)
3．在進行某學校教務管理系統(tǒng)的數據庫設計時，數據庫設計人員設計了如下幾個關系模式：
系(系號，系名)，系號為主碼
學生(學號，姓名，所在系號)，學號為主碼
課程(課程號，課程名，開課系號)，課程號為主碼
選課(學號，課程號，選課時間)，學號和課程號為主碼
開發(fā)人員在將關系模式實施到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
)
在執(zhí)行如下查詢語句時發(fā)現執(zhí)行效率很低：
SELECT *FROM選課JOIN學生0N學生．學號=選課．學號
JOIN系ON系．系號=學生．所在系號
JOIN課程0N課程．課程號=選課．課程號
WHERE系．系號=ˊ012 ˊ
AND convert(vvarchar(10)，選課時間，120)>=ˊ2010-01-01ˊ
(1)在查找原因時發(fā)現建表語句有問題。請指出問題并說明該問題是否會影響此查詢語句的執(zhí)行效率。(4分)
(2)設已在“選課”表的“選課時間”列及“學生”表的“所在系號”列上建立了索引。請問這兩個索引是否能夠提高該查詢語句的執(zhí)行效率?如果不能，請說明原因。(4分)
三、設計與應用題
1．(1)【解題思路】
關系的描述稱為關系模式，它可以形式化地表示為R(U，D，DOM，F)。要想使轉換生成的關系模式滿足3NF，則必須滿足關系模式中每一個非主屬性既不部分依賴于碼也不傳遞依賴于碼。根據題目中的表中的數據和業(yè)務系統(tǒng)的規(guī)則可知，共有四個實體存在，分別為教師、課程、教材、職稱，職稱作為實體而不是教師的屬性是因為職稱與工資掛鉤，考慮到其有進一步描述的特性，所以把職稱作為一個關系而不是教師的一個屬性，而且教師號、職稱、工資之間存在傳遞依賴，不滿足3NF。
【參考答案】
教師(教師號，教師名，上級領導教師號，職稱)
主碼：教師號；外碼：上級領導教師號、職稱
職稱(職稱，工資)
主碼：職稱；外碼：無
課程(課程號，課程名，學分，教材號，任課教師號)
主碼：課程號；外碼：教材號，任課教師號
教材(教材號，教材名，出版社)
主碼：教材號；外碼：無
(2)【解題思路】
E-R圖也稱實體一聯(lián)系圖，提供了表示實體類型、屬性和聯(lián)系的方法，用來描述現實世界的概念模型。為了簡化E-R圖的處置，現實世界的事物能作為屬性對待的，盡量作為屬性對待。實體與屬性的劃分給出如下兩條規(guī)則：①作為“屬性”，不能再具有需要描述的性質，“屬性”必須是不可分的數據項，不能包含其它屬性。②“屬性”不能與其它實體有聯(lián)系，即E-R圖中所表示的聯(lián)系是實體之間的聯(lián)系。題目中教師與課程有講授關系，課程和教材有使用關系，教師與職稱有聘用關系。
【參考答案】
2．【解題思路】
觸發(fā)器是用戶定義在關系表上的一類由事件驅動的特殊過程。一旦定義，任何用戶對表的增、刪、改操作均由服務器自動激活相應的觸發(fā)器，在DBMS核心層進行集中的完整性控制。觸發(fā)器類似于約束，但比約束更加靈活，可以實施比FOREIGN KEY約束、CHECK約束更為復雜的檢查和操作，具有更精細和強大的數據控制能力。
觸發(fā)器的創(chuàng)建格式：
CREATE TRIGGER[schema_name．]trigger_name
ON{tablelview}
{FORlAFFERIINSTEAD OF}
{[INSERT][，][UPDATE][，][DELETE]}
AS{Sql_statement}
[；]
對應于本題中，觸發(fā)事件為在銷售表中插入前四列，條件為無條件執(zhí)行，觸發(fā)體動作為計算本次利潤并插入表中。
【參考答案】
CREATE TRIGGER calcu_product
AFTER INSERT ON銷售表
FOR EACH ROW
AS BEGIN
DECLARE@PurchasePrise float／*對應商品的進價的參數*／
SELECT@PurchasePrise=進貨價格FROM商品表WHERE商品號=new．商品號
UPDATE銷售表SET本次利潤=new．銷售數量*(new．銷售價格-@PurchasePrise)WHERE商
品號=new．商品號AND銷售時間=New．銷售時間
／*因為是行級觸發(fā)器，所以可以使用更新后的新值，用new*／
END
3．(1)【解題思路】
本題中查詢語句的功能是得到12系全體學生在2010年1月1日后的選課情況的匯總表。在每個數據表的定義時都必須嚴格定義表中的完整性約束條件，包括主鍵的設置，否則之后會出現主鍵有相同值的情況，破壞了數據的完整性。
【參考答案】
建表時沒有設置主鍵，也沒有說明外鍵，但不會影響此查詢語句的執(zhí)行效率。
(2)【解題思路】
“選課”表的“選課時問”列上建立了索引，從而能夠提高執(zhí)行效率。經常出現在Where子句中的字段，特別是大表的字段，應該建立索引。索引的作用就類似于書的目錄，即會按照章節(jié)的順序排列。因此如果在一本數百頁的書里面查找某個章節(jié)位置的時候，就可以只掃描書的目錄。掃描的范圍縮了n倍，查詢的效率自然就會提高。另外，在SQL Server內存夠用的情況下，索引會被放到內存中，在內存中查找自然又會提高效率，所以必須合理利用索引。
【參考答案】
“選課”表的“選課時問”可以建立索引，從而提高了查詢效率，而“學生”表的“所在系號”建立索引不會提高查詢效率。索引的意義就是將記錄按目標關鍵字順序排列，這樣查找某個目標關鍵字的對應值的位置就縮小了查找范圍?！斑x課時間”的重復率低，所以可以作為索引，而學生“所在系號”的重復率太高，則不會提高查詢效率。