三、設計與應用題(共30分)
1．設有某商業(yè)單位需要建立商務數(shù)據(jù)庫用以處理銷售記賬，它記錄的數(shù)據(jù)包括：顧客姓名，所在單位及電話號碼；商品名稱，型號，產(chǎn)地及單價；某顧客購買某商品的數(shù)量及日期。假定無同名顧客，無同型號商品，由話公用靦客可存不同日期買同一商品。
(1)請畫出該單位的商務ER圖模型并優(yōu)化。(6分)
(2)將ER模型換成關系模型并規(guī)范化到3NF。(4分)
2．設在采用SQLServer 2008數(shù)據(jù)庫的圖書館應用系統(tǒng)中有三個基本表，表結構如下所示，請用SQL語句完成下列兩個查詢：
BORROWER：
借書證號姓名系名班級
12011106蔣輝文計算機系12-1
12011107王麗計算機系12-1
12012113范園園信息系12-2
LOANS：
借書證號圖書館登記號借書日期
1201 1 106 T0010012012．01．02
120121 13 T0010262013．02．O6
BOOKS：
索書號書名作者圖書登記號出版社價格
TP311．1數(shù)據(jù)庫系統(tǒng)李明T001001科學19．00
TP311．2二級C語言王珊T001026人民32．00
(1)檢索至少借了5本書的同學的借書證號、姓名、系名和借書數(shù)量。(5分)
(2)檢索借書和王麗同學所借圖書中的任意一本相同的學生姓名、系名、書名和借書日期。(5分)
3．在SQL Server 2008中，設有教師表(教師號，姓名，所在部門號，職稱)和部門表(部門號，部門名，高級職稱人數(shù))。請編寫滿足下列要求的后觸發(fā)型觸發(fā)器(設觸發(fā)器名字為tri_zc)。
每當在教師表中插入一名具有高級職稱(“教授”或“副教授”)的教師時，或者將非高級職稱教師的職稱更改為高級職稱時，均修改部門表中相應部門的高級職稱人數(shù)。(假設一次操作只插入或更改一名教師的職稱)(10分)
三、設計與應用題
1．【解題思路】
(1)ER圖的畫法
建立相應的ER圖的過程如下：
第一，確定實體類型。本題有兩個實體類型，即顧客實體和商品實體。
第二，確定聯(lián)系類型。該題中只有顧客一商品模型。
第三，把實體類型和聯(lián)系類型組合成ER圖。
第四，確定實體類型和聯(lián)系類型的屬性。
顧客實體集屬性：姓名、單位、電話號碼
商品實體集屬性：型號、名稱、產(chǎn)地、單價由顧客和商品兩個實體和一個顧客一商品購買關系，并且根據(jù)實體的屬性可以畫出相應ER圖。
(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)歷下面兩個步驟：
1)標識ER模型中的聯(lián)系。
2)依次轉(zhuǎn)換與每個聯(lián)系相關聯(lián)的實體集及聯(lián)系。
本題中從ER模型轉(zhuǎn)換成關系模型，由兩個實體和一個關系分別可以轉(zhuǎn)換成三個關系模式。
【參考答案】
(1)ER模型如下所示：
(2)將ER模型轉(zhuǎn)換為關系模型，并規(guī)范到3NF：
顧客(姓名，單位，電話號碼)，主鍵：姓名
商品(型號，名稱，單價，產(chǎn)地)，主鍵：型號
購買(姓名，型號，數(shù)量，日期)，主鍵：姓名+型號
2．【解題思路】
(1)采用兩表聯(lián)合查詢。以兩表“借書證號”為相等條件，在結果集中用GROUP BY按照“借書證號”來分類，并且用HAVING關鍵字統(tǒng)計出符合條件的記錄數(shù)。
(2)采用IN關鍵字進行兩表聯(lián)合查詢，在BORROWER和LOANS聯(lián)合的結果集中查找滿足第三個表指定的條件。
【參考答案】
(1)SELECT LOANS．借書證號，姓名，系名，COUNT(*)As借書數(shù)量
FROM BORROWER，LOANS
WHERE BORROWER．借書證號=LOANS．借書證號
GROUP BY LOANS．借書證號
HAVING COUNT(*)>=5；
(2)SELECT姓名，系名，書名，借書日期
FROM BORROWER，LOANS，BOOKS
WHERE
BORROWER．借書證號=LOANS．借書證號AND LOANS．圖書登記號=BOOKS．圖書登記號
AND索書號IN
(
SELECT索書號FROM BORROWER，LOANS，BOOKS
WHERE BORROW．借書證號=LOANS．借書證號
AND LOANS．圖書館登記號=BOOKS．圖書登記號
AND姓名=”王麗”
)
3．【解題思路】
創(chuàng)建觸發(fā)器的SQL語句為：CREATE TRIGGER，其語法格式為：
CREATE TRIGGER[schema_name．]trigger_name
ON{tableIview}
{FOR l AlZl'ER I INSTEAD OF}
{[INSERT][，][DELETE][，][UPDATE]}
AS{sql_statement}
[；]
其中AFFER指定觸發(fā)器為后觸發(fā)型觸發(fā)器，INSERT，UPDATE和DELETE為指定引發(fā)觸發(fā)器執(zhí)行的操作。根據(jù)原題要求，insert觸發(fā)器會在inserted表中添加一條剛插入的記錄，update觸發(fā)器會在更新數(shù)據(jù)后將更新前的數(shù)據(jù)保存在deleted表中，更新后的數(shù)據(jù)保存在inserted表中。在教師表中插入或者更新的時候，都會在inserted表中增加一條記錄，所以只需在觸發(fā)器查詢inserted表中查詢有沒有“教授”或者“副教授”的記錄，如果有，則觸發(fā)修改相應部門的高級職稱人數(shù)即可。
【參考答案】
CREATE TRIGGER tri_ZC
ON教師表
AL TER INSERT，UPDATE
AS
BEGIN
DECLATE@ZC varchar(10)，@dept varchar(30)
SELECT@dept=所在部門號，@2c=職稱FROM inserted
IF@ZC=’教授’0r’副教授7
Update部門表
SET高級職稱人數(shù)=高級職稱人數(shù)+1
Where部門號=@dept
End