全國計算機等級考試歷年真題

08年四月

二、簡答題

1、數據庫應用系統功能設計包括哪兩個方面？每個方面主要由哪聯

設計步驟組成？（10分）

答案：數據庫應用系統功能設計包括數據庫事務設計和應用程序

設計。與數據庫應用系統概念設計、邏輯設計、物理設計相對應，

應用程序（軟件）設計分為總體設計、概要設計和詳細設計，數據庫

事務設計則由事務概要設計和事務詳細設計組成

2、數據庫系統處理死鎖?般采用哪兩類方法？簡述每類方法的基本

原理（10分）

答案：解決死鎖主要采用死鎖預防和死鎖檢測與恢復兩類方法。a.死

鎖預防利用死鎖預防協議，通過破壞死鎖產生的必要條件來避免系統

進入死鎖狀態，防止死鎖發生。b.死鎖檢測與恢復則是允許系統進

入死鎖狀態，才旦定期檢查系統是否發生死鎖。當發現系統發生死鎖

后，采取相應的恢發機制使系統擺脫死鎖狀態.

3、請按要求解答卜列問題：

①在設計數據即應用系統時.需要對系統所需的存儲空間進行規劃。

規劃時需要考慮多種因素，請列出其中的4種。（4分）

②設有商品的售系統，該系統每天最多產生I力條梅品徜化汜錄.

用商品銷售數據表保存銷售記錄.系統需要保存3年的銷售記錄.

每年按365大計算。商品銷售數據我包含的屬性及各屬性占用的字

節分別為：肉品號（10個字節）、銷售數量（8個字節）、價格（8個

字節）、金額（8個字節）、收銀價編號（4個字節）、銷售員編號（4

個字節）、銷售時間（8個字節）.保存該衣的數據庫管理系統的數

據存儲單位是數據頁，每頁大小為8KB（共8192個字節）.該數據庫

笆理系統規定數據頁空間全部用丁保存銷售數據.并“存儲精售數

據時單條記錄不允許跨數據頁存放。請計算在數據昨中保存商品銷售

數據表需要的數據頁數H,要求寫出計算過程。(6分)

答案：①1.單條數據的長度：2.每年、每天、每小時等數據所增長的

大小；3.備份和冗余備份空間：4.系統文件的大小：5.占用內存空間

的大小等等。

②單條商品銷售記錄占用字節：10+8+8+844+4+8=50字節。單數據

頁可存放銷售記錄條數：163條

(163*50=8,150字節：164*50：8,120字節)。保存三年的銷售總

記錄數

：3*365*10,000=10,950,000條。需要的數據頁數目

：67.178(67,178*163=10,950,014；67.177*163=10.949.851).

三、應用與設計題

1、設有高校選課系統，需要對學校的系信息、教師信息、課程信息、

學生信息、學生選課信息進行管理.己知系(DEPT)信息包括系編

號(DeptNO),系名稱(DeptName),教師(Teacher)信息包拈教師

號(TNO)、教師名CTname),課程(Course)信息包括課程號(CNO)、

課程名(CName)、課程學分(Credit),學生(Student)信息包括學

號(SNO)、學生姓名(Sname)、學生性別(Sex)。

選課系統的管理規則如下：

I、個系可聘用多名教師.?個教師只受聘于個系：

【I、個系可有多名學生，個學生只屬于個系;

川、名教師可講授多門課程，門課程可被多個學生選修；

IV、?名學生可選修多門課程，?門課程可被多名學生選修；

V、學生選修完課程后，可獲得相應課程的成績。

針對以上描述，完成下列設計內容：

（I）構建選修課系統的ER圖。（要求圖中的實體集名用試卷中給出

的英文名，聯系名用聯系所關聯的實體集名的苜字字母間用“一”

或“”連接，大小寫不限）（10分）

（2）根據所構建的ER圖，設計滿足3NF的關系模式，并標出每個

關系模式的主碼和外碼。（要求關系模式名同實體集名或聯系名，屬

性名用試卷中給出的英文名，大小寫不限）（10分）

現有如下兩個關系模式：

Employees（Eid.Name.DeptNO）

Departments（DeptNO,DeptName.TotalNumber）

Employees關系模式描述了職工編號、姓名和所在部門編號：

Departments關系模式描述了部門編號、名稱和職工總人數。

請按SQLServer所采用的Tansact-SQL語法格式編寫實現具有功能

的后觸發型觸發器：每當在Employees表中插入一?行數據時，相應部

門的職工總人數就加I。（10分）

答案：createtriggersqltrionemployeesforinsert

as

declare@eidvarchar(II)declare@namevarehar(20)declare@

deptnovarchar(II)declareyjycursorfor

select*

frominsertedopenyjy

fetchnextfromyjyinto@uid,@name,@deptnowhile@@

fetchstatus=O

begin

updatedepartments

settotalnumber-totalnuniber-i-1

wheredcptno=@dcptno

fetchnextfromyjyinto@eid?@namc,@dcptnoend

closeyjydeallocateyjy

【解析】此題考直了后觸發型觸發器的Transact-SQL語句。其語法

格式為：

CREATETRIGGER觸發器名稱

ON|表名|視圖名|

[WITHENCRYPTION]AS

SQL語句AFTER和FOR,指定觸發器只有在引發的SQL語句中指

定的操作都一成功執行，并旦所有的約束檢查也成功完成后，才執

行此觸發器。即后觸發型觸發器。

08年9月

二、簡答題（每題10分，共20分）

請使用藍、黑色鋼筆或網珠筆將答案寫在答題紙的相應位置上，否則

無效。

（I）設有商場經營管理系統，系統中的部分數據表和視圖的相關信息

如卜一所示

賽或艇訪襄軍MW

?Ti《有品標識.時值）?儲傅柜臺.長■,華馀.金我）10000財霞胸1嶗

?n（悲叢標國時（弧商店標諛?息段■,思金累）1000啦T1

?T3（■品標識.月份.商店標識.型簟?,招金B）5000次慶T2

100次慶T3

3000次慶n

表中T4的數據源是T3表明T4中的數據是根據T1的數據匯總計算

而來的，并假定各個表的數據都得到了及時計算，現需對系統進行優

化，以提高與視圖vl有關的查詢效率，設有如下優化方案：

I為T1建立主索引

II為T2建立主索引

m為T3建立主索引

IV重寫視圖VI,將其數據源調整為T4,并為T4建立主索引

V重寫視圖VI,將其數據源調整為T2,并為T2建立主索引

請針對每一個優化方案，說明該優化方案對實現目標是否有效,

并簡要說明原因.

（2）卜圖為關系表Students（專業,姓名,學號,年齡），在對應的數

據文件中的物理存儲結構小意圖：

專：ik姓名。學號。年齡0

電子張家力，D-002^13

計茸機,蘇永軍?、J-100^

自動化?鄧秀娟?二05“23"

計算機?葉浩偉”L2W加

①寫出"專業"屬性上建立聚集索引的SQL語句,索引值按升序排序

（索引名為ZY_ind）°

②假設索引按稠密索引方式組織數據，畫出在"專業"屬性上建立聚

集索引后該表對應的索引文件和數據文件的物理存儲結構示意圖（以

"專業"為查找碼，按查找為定義的首字母升自定義的

三、設計與應用題（40分）

請使用藍、黑色鋼筆或圓珠筆將答案寫在答題紙的相應位置匕否則

無效。

（1）有商品表（商品號，商品名，分類，單價）,請編寫個實現更改商

品單價的存儲過程（存儲過程名為pUpdate）,更改規則如下："電腦"

類商品降價10%，"電視"類商品降價6%,"冰箱"類商品降價3%,其

他商品不降價。以商品的分類作為輸入參數，假設"分類"為字符串類

型，長度最多為6個漢字。如果商品女中沒有用戶指定的分類，則用

輸記參數返IE字符申"指定的分類不存在"；如果用戶指定的分類存在，

則用輸出參數返回字符串"修改已成功"。（10分）

（2）現有某圖書銷售數據庫,其關系表結構如下:

圖書表（圖書編號，圖書名稱，出版社編號，出版社名稱，出版時間，

出版數量，版次）

圖書銷售表（圖書編號,銷售日期,銷售數量,書店編號,讀者編

號，讀者姓名，讀者電話）

書店表（書店編號，聯系電話,所在城市編號,城市名稱）

I.系統所涉及的數據存在如下約束

出版社可以出版多本圖書，一木圖書只能在一個出版社出版，在該系

統的記錄的圖書出版信息包括出版時間、版次及出版數量信息。

II.一個書店可以出售多本圖M給多個讀者，每位讀者可以從多個

回占購買多本圖書，本圖書可以通過多個書店出售給讀者，書店把

圖書出售給讀者后會在系統中記錄售書日期和售書數量信息：

HL每個書店只能位于個城市,?個城市可以有多個書店。

①請根據以上信息畫出合理的圖/銷售數據庫的概念模型（用ER

圖表示）。（8分）

②以圖書銷售衣為例說明原數據庫設計的不合理之處。（4分）

③給出該數據庫符合3NF要求的全部關系模式，并指出關系模式中

的全部主碼和外碼。（8分）

（3）事務Tl、T2和T3按如下調度方式并發地對數據項A,B,C進行

訪問，假設A,B,C的初位分別為A=20,B=30,C=40o

①在事務T2剛完成提交后，數據庫中A,B,C的值各是多少？（3

分）

②當事務T3結束后，給出該并發調度對應的FI志文件，并說明

此時A,B,C的值各是多少？（7分）

T1T2T3Q

Begin-trans(II)*1

Read(A)?

Begin-trans(T2)/

Read(B)**

Write

Conniw

Begln-trans(T3)^,

Read(C)^

Write

C:-C*2P

ConnniW

Write(C)^

rollback?'

二、簡答題答案

（1）答案：略,詳見解析。

（2）答案：

①CREATECLUSTEREDINDEXZYindONStudents（專業）

②索引文件的物理存儲結構示意圖：

數據文件的物理存儲結構示意圖:

②圖書銷售表中，送者信息應該單獨新建?張表，否則就會造成大量

的數據冗余。

③出版社表（出版社編號，出版社名稱）主碼：出版社編號

出版表（出版社編號，圖竹編號，出版時間，出版數量，版次）

主碼：（出版社編號，圖書編號）

圖書表（圖書編號,圖書名稱）主碼:圖書編號

書店表（書店編號,聯系電話,所在城市編號）主碼：書店編號

城市表（所在城市編號,城市名稱）主碼：所在城市編號

讀者表（讀者編號，讀者姓名，讀者電話）主碼：讀書?編號

圖書銷售表（圖書編號,讀者編號,銷售日期,銷售數量，書店

編號）主碼:（圖書編號,讀者編號）

（3）

答案：①A=30；B-20；C-40；

②日志記錄：302080

A=30；B=20；C=40；

二、應用與設計題

1.分析：簡答題解析

⑴解析：

1：試圖不能建立索引,無效。

H：視圖VI建立在T3匕T2的索引優化對VI沒有影響。

III：可以在?定程度上提高VI的查詢效率，但是從T3的每行記錄

粒度為"日"查詢到粒度"季"，大約每次查詢涉及90行。而從T4的每

行記錄粒度為"月"行詢到粒度"季"，大約每次查詢只涉及3行，顯然

后者的修制效率更高。另外T2的訪問頻率相當高，若是VI建立在

T2上，顯然會更加增加其查詢頻率,降低查詢效率。

IV：為5種方案中最優方案。每次查泡涉及行數最少（3條），而H

T4的訪問頻率最低。

V：每次查詢牽涉記錄行數比方案HI要多，雖然T2查詢頻率較T3

少，但是總體性能不如方案H1.

<2）解析：本題考察的是聚集索引的創建及數據文件的物理存儲

結構。

2.分析：三、設計與應用題解析

3）解析：存儲過程的建立使用CREATEPROCEDURE,對存儲

過程中變量的命名要用到@變量名，

CreateprocedurepUpdale

@flaschar（6）,

@jgasvarchar（20）output

As

Ifnotcxists（Sclect*from商品表where分類=@fl）

BEGIN

If腦"Update商品表set單價=單價*0.9where分類="電腦

Else

BEGIN

"電視")Update商品表set單價=單價*0.94where分類="電

視“

Else

BEGIN

IK@fl="冰箱")Update商品表set單價=單價*0.97where分類="冰

箱”

END

END

Scljg="修改」成功"

END

Else

Setj歹”指定的分類不存在"

(2)解析：本題考察的主要是數據庫設計優化，關系數據模型轉

換及規范化處理。一木圖書只能在個出版社出版，即出版社與圖書

是對多的關系，個書店可以出售多木圖書給多個讀者，每位讀者

可以從多個書店購買多本圖書,一本圖書可通過多個書店出售讀者,

即有圖書，讀者與書店都是多對多的關系，它們之間通過出售實體來

聯系。圖書捎售表中存在太多的冗余，應該化為更高的范式來消除不

必要的冗余。

(3)解析：本題考察的是事務的調度與I可滾。T1,T2,T3的整個調

度次序是先是TI把A加h10,此時A還是初位20,直到T3把A

寫入，此時A的他為30.而T2只是讀了B,B的值未發生變化；之

后的Bcgin-trans（T3）,T2時B進行了改寫并寫入，所以B的值發生

變化，但是TI雖然改變了C的位，但是卻沒有寫入，事務在T2的

時候就已經提交，所以C的位未變。

09年3月

二、簡答題（每題10分，共20分）

請使用藍、黑色鋼筆或阿珠筆將答案寫在答題紙的相應位置上，否則

無效。

（1）有如下2個關系表

Emp（eid,ename.age,did.salary）,其各列含義為:職工號,

姓名，年齡，部門號，工資

Depl（did.dname,mgrid）,其各列含義為：部門號,部門名

稱，部門經理職工號

寫出條SQL語句，查詢工資大于10000,只與他所在部門的

經理年齡相同的職工姓名。（6分）

（2）有學生表〔學好，姓名，年齡，性別，系名，專業名，班號），

設個系可有多個專業，每個［業可有多個班，各班班號不重復，-

個學生只在?個班學習，現經常需要按"系名"和"班號"進行查詢，為

提高查詢效率，需要為"系名"和"班號"兩個列建立個非聚集索引，

建立此索弓I有下列兩種方法：

方法1:索引列順序為（系名，班號）

方法2:索引列順序為（班號,系名）

①這兩種方法哪個更合理？請簡要說明原因。（4分）

②針對你認為合理的方法，寫出創建該索引的SQL語句。（3分）

(3)考慮定義在事務集(T1,T2,T3)卜一的調度S1和S2,SI與S2

是否是沖突等價的？為什么？(7分)

S3S2/

TP3T3PTIPT2PT3一

read(P)read(Q)>?砂

read(Q)?>read(P)?府

write(Q)*write(Q)??o

write(Q)“?Jownte(Q)o

write(Q)“dwrite(P)?J

Pwrite(P)??wnte(Q)>

write(P)-uine(P)?

read<P)?lead(P)~

三、設計與應用題(40分)

請使用藍、黑色鋼筆或圓珠筆將答案寫在答題紙的相應位置上，否則

無效。

(1)某汽車維修公司需建立?個汽車維修數據庫，該數據庫中需要

存儲和管理下列信息：

車輛信息:車牌號，車型,發動機號,行駛里程，車輛所有人,

聯系電話

維修項II：項目號，項目名稱，維修費

汽車備件：備件號，備件名稱，備件單價，庫存數量

以上數據之間存在下列約束:

可以對個車輛進行多個維修項目，每個維修項目可用于多個車

輛，維修項II完成后要在數據庫中記錄維修時間

種備件可用于多個維修項目，每個維修項口最多只使用種備

件，但每種備件的數量可以是多個。

①根據以上需求構建該數據庫的概念模型［畫ER圖）。（8分）

②假設車輛信息有如下約束

車牌號：標識屬性,取值形式為:第1個字符是"京"，第2個字

符為"A"到"Z"的字母，第3到第7個字符均是"0"到"9"的數字。此列

采用普通編碼定長字符型，非空：

車型：普通編碼定長字符型,長度為6,默認值為"轎車";

發動機號：普遍編碼定長字符型：長度為6,非空；

行駛里程：整型，取值大于等于0:

車輛所有人：普通編碼定長字符型，長度為8,非空；

聯系電話：普通編碼定長字符型，長度為13,取值唯。

寫出創建滿足上述要求的車輛信息表的SQL語句。（注：表名、

列名均用題中給出的中文名，SQL語句中的大、小寫字母均可。）

（8分）

（2）現有關系模式:

教師授課（教師號，姓名，職稱，課程號，課程名，學分，教科

書名）

其函數依賴集為：

｛教師號一姓名，教師號一職稱，課程號一課程名，課程號一學

分，課程號一教科書名｝

①指出這個關系模式的主碼。（2分）

②這個關系模式是第兒范式，為什么？（4分）

③將其分解為滿足3NF要求的關系模式（分解后的關系模式名

自定）（6分）

(3)為在某種程度上保證某數據庫系統可靠地運行，在SQLServer

2000環境中，對其制定有如下三種備份策略。假設對此數據庫進行

?次完全備份需要4小時，差異備份需要2小時，R志備份需要1小

時。設所有備份都是從凌晨1:00開始。

策略I:每周周日進行?次完全備份，每周周至周六每天進行

次日志備份：

策略2:每周周日進行次完全備份，每周周一至周六每天進行

次差異備份：

策略3:每天進行次完全備份。

①假設需要保留一年的備份數據，比較三種備份策略所占用的

空間。

②比較每種備份策略的備份和恢復速度。

二、簡答題答案

答案：

(1)selecta.*fromEmpa,Deptb,Empcwherea.did=b.didand

b.mgrid-c.cidanda.salary>10000anda.agc=c.agc

(2)①方法I更加合理，理由如下：更加有利于減少索引層次，提高

查詢效率：更加符合使用習慣：更加便于(系、班)進行統計。

②createindexindex1on學生表(系名，班號)

(3)不是等價沖突，因為T1的write(Q)和T2的write(Q)是沖

突的，不可交換。三、設計與應用題答案

答案：

(1)①概念模型如下:

②Createtable車輛信息(

車牌號char(7)NotNULLCHECK車牌號like("京[A-Z][0-9][0-9]

[0-9][0-9]")

車型char(6)default"轎車"，

發動機號char(6)NOTNULL,

行駛里程intCHECK(行駛里程＞=0),

車輛所有人char(8)NOTNULL,

聯系電話char(13)UNIQUE,

PRIVATEKEY(車牌號)

)

（2）①主碼為：（教師號、課程號）

②笫1范式,因為存在部分依賴。

③分布方法為：

教師表（教師號，姓名，職稱）

課程表（課程號，課程名，學分,教科書名）

教師授課表（教師號，課程號）

（3）①占用空間量從大到小排序為：策略3,策略2,策略1

②備份速度從快到慢排序為：策略1,策略2,策略3

恢復速度從快到慢排序為：策略3,策略2,策略I

三、答案解析

一、選擇題

1.分析：技術可行性是根據用戶提出的系統功能、性能及實現系

統的各項的約束條件，對系統軟件、系統硬件、技術方案做出評

估和選擇建議，它屬于規劃與分析階段的可行性分析。

二、應用與設計題

1.分析：簡答題解析

解析：沖突等價調度是指一個調度S,通過調換S中的不

沖突操作次序所得到的新調度稱為S的沖突等價調度。不沖突操

作有兩類:一是對同一數據均為讀操作,即Ri(x),Rj(x);對不同

數據對象的?組操作中可以有寫的操作，即Ri(x),Wj(y)或

Wi(x)町(y).顯然TI和T2對數據對象Q進行了相同的寫操作,所

以是沖突的，所以S1和S2不是沖突等價的.

2.分析：設計與應用題解析

解析：⑴由題一個車輛可進行多個項目的維修，一個維修可

用于多車輛，可知實體車輛信息與維修項目是多對多的關系；

種配件可用于多個維修項目，但?個維修項目只能最多用?種配

件,可知配件與維修項目是一對多的關系。車牌號的取值形式限

定可用CHECK來表示。

(2)由給定的依賴關系可知，教師號和課程號能夠決定所有的屬

性，即教師號和課程號是主碼。由于每一個非主屬性都部分的依

賴于碼，所以不滿足2NF,所以它為1NF

(3)完全備份是備份全部選中的文件夾，并不依賴于文件的存檔

屬性來決定備份I哪寫文件；

差異備份是針對完全備份的，它是備份上-?次完全備份后發生變

化的所方文件，恢復時需要完全數據庫備份和最近的差異數據庫

備份

H志備份需要有完全數據庫備份；備份自上次backuplog語句到

當前H志尾之間的所有數據庫修改；截斷事務H志這樣備份的數

據量少，只備份上次備份到現在的修改：恢復時需要完全數據庫

備份和所有的日志備份

09年9月

三、設計與應用題(30分)

請使用藍、黑色鋼筆或圓珠筆將答案寫在:答題紙的相應位置上，否則

無效。

<1)石兩張關系表：

Students(SID.SName.Sex.Dept)

Prizing(SID.PName,Type.PDate)

Students及描述的是學生基木信息,Prizing我描述的是學生獲獎信息。

①若在應用程序中經常使用如下查詢語句：

SELECTS.SID,SName,PNamc,TypeFROMStudentsASSJOIN

PrizingASPONS.SID=P.SID

請問能否使用聚集文件提高此直向的性能？并給出原因。

②為了提高查詢性能，數據庫管理員在Students表的SName列上建

立了個非聚集索引SNameJnd.如果應用程序使用如下語句查詢數

據：

SELECT*FROMStudentsWHERESNamelike'%華'

請問SNameind索引是否能夠提高語句的查詢速度,并給出原因。

(2)某公司需建立產品信.息數據庫，經調查其業務要求如下：

I、種產品可以使用多個不同種類的部件，也可以使用多個不同種

類的零件；一種部件至少被用在一種產品中；一種部件可以由多個不

同種類的零件組成；一種零件至少被用在種產品或?種部件中；

II、對于種具體的部件，要記錄這種部件使用的各種零件的數疥：

III、對于種具體的產品.要記錄這種產品使用的各種部件數量和各

種零件數量，但部件所使用的零件數量不計入該種產品的零件數量：

IV、一個供應商可供應多種零件，但一種零件只能由一個供應商供應。

根據以上業務要求，某建模人員構建了如下ER圖：

NUP團山的7?侏M山枇之做E7:伏曲左以1號葉左/:不企礎》〃卜

①請)新構建合理的ER圖,使之符合業務要求，且信息完整。

②根據重構的ER圖給出符合3NF的關系模式，并標出每個關系

模式的主碼和外碼。

(3)在SQLServer2000中，石教師表Teachers(TeacherlD,Name?

LeaderlD),其中TeacherlD是主碼，類型是長度為4的普通編碼定長

字符出，旦每位是0-9的數字字符；Name的類型是長度為10的普通

編碼可變長字符串；LeaderlD是每個教師的領導的TeacherlD。

①寫出創建Teachers及的SQL語句，要求語句中包含所有的約束。

②現要查詢TcachcrID為"1234"的教師的領導的領導的TcachcrID,

請給出相應的SQL語句，要求只使用條SQL語句實現，」L此語句

中不允許包含了查詢。

三、設計與應用題答案

（1）答案：①可以用聚集文件提高此查詢性能。因為聚集文件本身

存儲不同類型表的聯系，能夠減少表間的連接操作，提高查詢性能。

②此索引不能提高該語句的查詢速度。因為該索引是建立在SNAME

上的非聚集索引，以SNAME首字母排序，而查詢語句卻要求查詢名

字最后一個是"華"的SNAME,還是需要全部掃描students表,因此

不能提高該語句的看詢速度。

（2）答案：①重構ER圖如下:

②符合3NF的關系模式：（帶卜劃線的為主:碼屬件）

產品（產品號（PK）,產品名稱，型號，裝配圖號）

部件（部件號（PK）,部件名稱）

零件（零件號（PK）,零件名稱，供應商號（FK））

產品_部件（產品號（FK）,部件號（FK）,部件數量）

產品—零件（產品號（FK）,零件號（FK）,零件數量）

部件零件（部件號（FK）,零件號（FK）,零件數量）

供應商（供應商號（PK）,供應商名稱，聯系電話）

(3)答案：

?createtableTeachers(

TeacherlDchar(4)check(TcachcrlDlike"[0-9][0-9][0-9]

Namevarcharf10),

LeaderlDchar(4),

primarykeyTeacherlD,

foreignkeyLeaderlDreferencesTeachcrs(TcachcrlD)

)

②selectT2.LeaderIDfromTeachersasTljoinTeachersasT2on

T1.LeaderlD=T2.TeacherlDwhereT1.TeacherID=,1234,

2.分析：三、設計與應用題解析

（1）解析：本題考查的是優化查詢速度的方法等。聚集文件木

身存儲不同類型表的聯系，能夠減少表間的連接操作，提高查詢

性能。而非聚集索引，對于以SWME首字母排序時有效。

（2）解析：本題考查的是ER圖和關系模式的知識。實體設計

中存在明顯的不合理之處，比如供應商應該是一個獨立的實體。

此外，對于產品和零件I'L接的聯系未能正確表現出來。?種產品

可以使用多個不同種類的部件，也可以使用多個不同種類的零件。

說明產品與零件是直接有關系的，必須用個實體來把它們之間

的關系表示出來，否則，設計出來的關系模式存在有大量的數據

冗余。

（3）解析：創建表格使用CREATE語句。對于查詢表中領導的

領導信息可以通過對該表進行自我連接，1231的條件約束可以通

過在WHERE廣句中來實現。

10年3月

三、設計與應用題（共30分）

1.設有某簡化的網上購物系統，其ER圖如卜.:

該系統數據之間存在卜列約束:

I.個客戶（編號唯一）可以擁有多個訂單,每個訂單僅屬于個客

戶；

II.一個訂單（編號唯）可以包含多個訂購細目，每個訂購細目只屬

于一個訂單：

in.一種商品可以出現在多個訂購細目中，一個訂購細目只包含?利

商品；

IV.個商品類別可以包含多種商品，種商品只屬于個商品類別。

①請根據以上約束，標出ER圖中各實體集之間聯系的基數。(2分)

②請將ER圖轉換成符合3NF的關系模式，并給出各關系模式的土碼

和外碼。(4分)

③請修改所設計的關系模式使之支持下列功能(僅列出發生修改的關

系模式)。(4分)

I.使客戶能方便了儲每個訂購細目的處理狀態和到貨時間；

H.使客戶能方便查詢商品供應商的編號、名稱、所在城市、聯系電

話和信譽。(限定一個供應商可以供應多種商品，一種商品只選自一

個供應商)

答案：②uscrInfb(uscrlD,username,uscrTcl,uscrEmail,ordcr【D)主碼

userID,外碼orderlD:

orderInfb(orderID,orderDate,itemID)主碼orderlD.外碼itemID：

itemInfb(ilemID,itemNum,itemSum)主碼itemID；

commodityInfb(comID,comName,comPrice,comDate,typelD,itemID)

主碼comlD,外碼typelD,itemlD：

comType(type【D,typeName,com【D)主:碼typelD.外碼comlD；

③在訂購細目中添加發貨時間和到貨時間;添加供應商關系模式，修

改商品關系模式

businessInfb(businessID,businessName,businessCity,businessTel,busines

sCredil)主碼

businessID

comniodityInfb(comID,comNamc,coniPricc,comDatc,typeID,itcmID,busi

nesslD)主

碼comID.外碼typeID,itemID,businessID；

2.某學校的學籍管理系統實現了對學生學籍信息的管理，其中學生

表結構為：學生表（學號

，姓名,性別,系號,是否有學籍），此表中除學號列有唯一索引外,

其它列均無索引。

①設學籍管理系統中經常執行如下形式的操作：

SELECT*FROM學生表

WHERE姓名=，張三，AND系號=*10'AND是否有學籍=，有'

系統運行中發現這類操作的效率比較低，系統工程師建議：由于查詢

條件中使用了“系號”、

“姓名”和“是否有學籍”三列，建議在這三個列上分別建立索引，

以提高查詢效率.

請問其建議建立的三個索弓I是否都能提高查詢效率?請簡要說明原

因。（6分）

②設學生表中有下列數據：（見下表）

學號姓名性別系號是否有學籍

2008337工二;?;'二另一有

生」-

20083381席彬彬10冒

20JS339馬宏偉Ka21020100襟

2008556王斌男20有

2008558張偉男20.有丁五代P西

2008559丁二女20兆ucityc「

并“己經在學生表的姓名列上建立了個非聚集索弓1（按姓名拼音升

序排序）。請給出查找碼“姓名”對應的稠密索引示意圖。（4分）

答案：①不能。使用索引是需要付出代價的，增加了系統維護時間和

占用了系統時間。學籍、系

號這兩個字段重復值比較多，因此在這兩個字段上添加索引不會顯著

的增加杳詢速度，減少用戶

的響應時間。相反，因為需要占用空間，反而會降低數據庫的整體性

能。

3.在SQLServer2000中,設有教師表（教帥號,姓名，所在部門號,

職稱）和部門表（部門號

，部門名，高級職稱人數）。請編寫滿足下列要求的后觸發型觸發器（設

觸發器名字為lri_zc）。

（10分）

每當在教師表中插入一名具有高級職稱（“教授”或“副教授”）的教

師時，或者將洋高級職稱教師的職稱更改為高級職稱時，均修改部門

表中相應部門的高級職稱人數。（假設?次操作只插入或更改名教

師的職稱）

答案：CREATETRIGGERtric_zc

ON教師表

AFTERINSERT,UPDATE

AS

BEGIN

DECLARE@zcvarchar(10),@deptvarchar(30)

SET@zc=(SELECT職稱FROMinserted)

SET@dept-(SELECT所在部門號FROMinserted)

IF@zc='教授'OR'副教授'

UPDATE部門表

SET高級職稱人數-高級職稱人數+1

WHERE部門號-@depl

END

10年9月

三、設計與應用題(共30分)

（1）已知某教務管理系統有下列關系模式（帶下劃線的屬性是

主碼）:

系（系號,系名）

學生（系號，姓名，性別，入學FI期，正常畢業日期，所在系號）

課程（課程號，課程名，學分，開課系號，教師號）

選課（學號，課程號，成績）

教師（教師號，姓名,職稱，所在系號）

①請根據給出的關系模式畫出該系統的ER圖，圖中忽略實體集

的屬性，但如果實體集之間的聯系有屬性則需要給出聯系的屬性。（6

分）

②隨著教學改革的需要，該系統需要滿足下列需求：

I.需為課程增加先修課信息（門課程可有多門先修課程）：

IL?門課程可由多名教師講授，一名教師可以講授多門課程。

請根據以上需求修改關系模式，僅列出有改動的關系模式，并使

每個關系模式滿足3NF.（4分）

（2）在SQLServer2000的某數據庫中右如下兩張關系表：

學生表（學號，姓名，性別，系號），學號為主碼

系表（系號，系名，系簡稱）.系號為主碼

①在數據庫中執行如下T-SQL代碼：

DECLARE@DePtIDvarchar（10）

DECLARE@cntint

set@cnt=O

DECLAREcursor1cursorFORSELEET系號FROM系表

WHERE系名LIKE%電％，

OPENcursorl

FETCHNEXTFROMcursorlINTO@DePtID

WHILE@@FETCHSTATUS-O

BEGIN

DECLARE@tcmpentint

SELECT@temp_cnt=COUNT（*）FROM學生表WHERE系號

=@DeptID

set@Cnt=@cnt+@lempent

FETCHNEXTFROMcursor1INTOODePtiD

END

CLOSEcursor1

DEALLOCATEcursor1

SELECT@cnt

執行過程中發現速度比較慢，為了解決性能問題，需在功能不變

的情況下，將此T-SQL代碼改為一條SQL語句，請寫出此SQL語

句（語句中不能含有子查詢）并說明為什么此種修改可以提高性能。

（5分）

②設在學生表的（姓名，系號）列上建有一?個復合索引,該索引

是否有助于提高卜冽兩個語句的查詢效率？并說明原因。（5分）

SELECT*FROM學生表WHERE系號=T;

SELEET*FRoM學生表WHERE姓名=，張三、

（3）在SQLserver2000中,設有圖書表（書號，書名，類別,

單價）和圖書卬刷表（書號，期，印刷數量，印次），每個書

號代表種圖書。書號、書名和類別的數據類型均為varehar（20）,

單價、印刷數量和印次的數據類型均為int,卬刷n期的數據類型大

smalldatetime.現需查詢指定類別的圖書在指定年份后(含指定年份)

累計印刷總數量。請寫出實現該功能的標量函數。(10分)

答案

①

②修改“課程”關系模式為：課程(課程號，課程名，學分，開課系號，教師號，先修課號)，主碼為

“課程號。

新增“授課”關系模式：授課(教師號，課程號)，主碼為“教師號”、“課程號。

①SELECTCOUNT(*)FROM學生表WHERE系名LIKE電%，

②第一句不能，笫二句能，系名的重良率太高，姓名的重復率相當低C

3.

CREATEFUNCTIONfun一定義函數名為fun

(@classVARCHAR(20),?orderyearCHAR(4))一聲明參數

RETURNSINT-聲明返回值類型

AS

BEGIN

--定義一個SMALLDATN1ME類型的中間變量,用于存放轉換后的年份

DECLARE@lcmSMALLDATATIME

??定義一個INT類型的變量，用于存放卬刷總書目

DECLARE@cntINT

一把傳進來的CHAR(4)類型的年份轉換成SMALLDATATIME類型，并存入中間變量

SET@iem=CONVERT(SMALLDATETIME,@ordcTyear)

-SQL

SELECT@cnt=SUM(印刷數目)

FROM圖書表JOIN圖書卬刷表

ON圖書表.書號=圖書卬刷表.書號

WHERE類別=@classAND日期＞=@tem

RETURN@cnt-返回把印刷總數量

END

11年3月

三、設計與應用題（（D題20分，（2）題10分，共30分。）

1.已知某教務管理系統的設計人員在需求分析階段收集到卜列原始

數據表格：

答案：（I）①教師（教師號.教師名，職稱名，上級領導教師號）

職稱（職稱號，工資）

課程（課程號，課程名，學分，任課教師號）

教材（教材號，教材名，出版社名，課程號）

②ER圖

2.在SQLServer2000中?設某數據庫中有商品表（商品號，商品名,

進貨價格），商品號為主碼；銷售表（商品號，銷售時間，銷售數量，

銷售價格，本次利潤），商品號和銷售時間為主碼，銷售價格為本次

銷售商品的單價。現要求每當在銷售表中插入前4列數據時（假設?次

只插入?行數據），系統自動計算木次銷售產生的利潤，并將該利潤

賦給銷售表的第5列“本次利潤:請編寫實現上述功能的后觸發型觸

發器代碼。（10分）

答案：CREATETRIGGERupdatc_salc

ON銷售衣FORUPDATE

FOREACHROW

ASBEGIN

UPDATETablesaleSET木次利潤=銷售價格-(SELECT進貨價格

FROM產品表)

END

CREATETRIGGERInsertsale

ON銷售發FORINSERT

FOREACHROW

ASBEGIN

INSERTINTO銷伐表VALUES(new.商品號,new.銷售時間，new.銷售

數量,new.銷售價格)

END

3.在進行某學校教務管理系統的數據庫設計時，數據庫設計人員設

計了如下兒個關系模式：

系(系號，系名)，系號為主碼

學生(學號，姓名，所在系號)，學號為主碼

課程(課程號，課程名，開課系號)，課程號為主碼

選課(學號，課程號，選課時間)，學號和課程號為主碼

開發人員在將關系模式實施到SQLServer2000的“教務”數據庫時，使

用了如下表結構定義語句：

CREATETABLE系(系號varchar(10)NOTNULL,系名varchar(lOO))

CREATETABLE學生(學號varchar(50)NOTNULL.姓名varchar(50),

所在系號varchar(10))

CREATETABLE課程(課程號varchar(50)NOTNULL,課程名

varchar(100),開課系varchar(10))

CREATETABLE選課（學號varchar（50）NOTNULL,課程號

varchar（50）NOTNULL,選datetime）

在執行如下查制語句時發現執行效率很低：

SELECT*FROM選課JOIN學生ON學生.學號=選課.學號

JOIN系ON系.系號=學生.所在系號

JOIN課程ON課程.課程號=選課.課程號

WHERE系.系號一012

ANDconvcrt（varchar（10）.選課時間,120）＞=2010-01-01

①在杏找原因時發現建表語句有問題。請指出問題并說明該問題是否

會影響此查詢語句的執行效率。（4分）

②設已在“選課”表的“選課時間”列及“學生”表的“所在系號”列上建立

了索引。請問這兩個索引是否能夠提高該告制語句的執行效率?如果

不能，請說明原因。（4分）

三、設計與應用題（（1）題2（）分，（2）題10分,共30分。）

1.已知某教務管理系統的設計人員在需求分析階段收集到下列原始數據表格：

答案：（1）①教師（教師號，教師名，職稱名，上級領導教師號）

職稱（職稱號，工資）

課程（課程號，課程名，學分，任課教師號）

教材（教材號，教材名，出版社名，課程號）

②ER圖

2.在SQLServer2000中，設某數據庫中有商品表（商品號，商品名，進貨價格），

商品號為主碼：銷售表（商品號，銷售時間，銷售數量，銷售價格，本次利潤），

商品號和銷售時間為主碼，銷售價格為本次銷售商品的單價。現要求每當在銷售

表中插入前4列數據時（假設一次只插入?行數據），系統自動計算本次銷售產生然后針

對各個數據的利泗，并將該利澗賦給銷售表的第5列“本次利潤”。請編寫實現上述功

能的

后觸發型觸發揣代碼.(10分)

答案：CREATETRIGGERupdatesale

ON銷售表FORUPDATE

FOREACHROW

ASBEG1N

UPDATETable_saleSET