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