四級(jí)數(shù)據(jù)庫(kù)歷年簡(jiǎn)答題設(shè)計(jì)應(yīng)用題

1、2008年4月二、 簡(jiǎn)答題（1） 數(shù)據(jù)庫(kù)應(yīng)用系統(tǒng)功能設(shè)計(jì)包括哪兩個(gè)方面？每個(gè)方面主要由哪些設(shè)計(jì)步驟組成？（10分）（2） 數(shù)據(jù)庫(kù)系統(tǒng)處理死鎖一般采用哪兩類方法？簡(jiǎn)述每類方法的基本原理（10分）（3） 請(qǐng)按要求解答下列問(wèn)題： 在設(shè)計(jì)數(shù)據(jù)庫(kù)應(yīng)用系統(tǒng)時(shí)，需要對(duì)系統(tǒng)所需的存儲(chǔ)空間進(jìn)行規(guī)劃。規(guī)劃時(shí)需要考慮多種因素，請(qǐng)列出其中的4種 （4分） 設(shè)有商品銷售系統(tǒng)，該系統(tǒng)每天最多產(chǎn)生1萬(wàn)條商品銷售記錄，并用商品銷售數(shù)據(jù)表保存銷售記錄。系統(tǒng)需要保存3年的銷售記錄，每年按365天計(jì)算。商品銷售數(shù)據(jù)表包含的屬性及各屬性占用的字節(jié)分別為：商品號(hào)（10各字節(jié)）、銷售數(shù)量（8各字節(jié)）、價(jià)格（8各字節(jié)）、金額（8各字節(jié)）、

2、收銀臺(tái)編號(hào)（4各字節(jié)）、銷售員編號(hào)（4各字節(jié)）、銷售時(shí)間（8各字節(jié)）。保存該表的數(shù)據(jù)庫(kù)管理系統(tǒng)的數(shù)據(jù)存儲(chǔ)單位是數(shù)據(jù)頁(yè)，每頁(yè)大小為8kb（共8192個(gè)字節(jié)）。該數(shù)據(jù)庫(kù)管理系統(tǒng)規(guī)定數(shù)據(jù)頁(yè)空間全部用于保存銷售數(shù)據(jù)，并且存儲(chǔ)銷售數(shù)據(jù)時(shí)單條記錄不允許跨數(shù)據(jù)頁(yè)存放。請(qǐng)計(jì)算在數(shù)據(jù)庫(kù)中保存商品銷售數(shù)據(jù)表需要的數(shù)據(jù)頁(yè)數(shù)目，要求寫(xiě)出計(jì)算過(guò)程。（6分）三、 設(shè)計(jì)題（1） 設(shè)有高校選課系統(tǒng)，需要對(duì)學(xué)校的系信息、教師信息、課程信息、學(xué)生信息、學(xué)生選課信息進(jìn)行管理。已知系（dept）信息包括系編號(hào)（deptno）、系名稱（deptname），教師（teacher）信息包括教師號(hào)（tno）、教師名（tname），課程（co

3、urse）信息包括課程號(hào)（cno）、課程名（cname）、課程學(xué)分（credit），學(xué)生（student）信息包括學(xué)號(hào)（sno）、學(xué)生姓名（sname）、學(xué)生性別（sex）。選課系統(tǒng)的管理規(guī)則如下：、一個(gè)系可聘用多名教師，一個(gè)教師只受聘于一個(gè)系；、一個(gè)系可有多名學(xué)生，一個(gè)學(xué)生只屬于一個(gè)系；、一名教師可講授多門(mén)課程，一門(mén)課程可被多個(gè)學(xué)生選修；、一名學(xué)生可選修多門(mén)課程，一門(mén)課程可被多名學(xué)生選修；、學(xué)生選修完課程后，可獲得相應(yīng)課程的成績(jī)。針對(duì)以上描述，完成下列設(shè)計(jì)內(nèi)容： 構(gòu)建選修課系統(tǒng)的er圖。（要求圖中的實(shí)體集名用試卷中給出的英文名，聯(lián)系名用聯(lián)系所關(guān)聯(lián)的實(shí)體集名的首字母，字母間用“”或“_”連接，

4、大小寫(xiě)不限）（10分）2008年9月二、簡(jiǎn)答題(1)設(shè)有商場(chǎng)經(jīng)營(yíng)管理系統(tǒng)，系統(tǒng)中的部分?jǐn)?shù)據(jù)表和視圖的相關(guān)信息如下所示表中t4的數(shù)據(jù)額是t3表明t4中的數(shù)據(jù)是根據(jù)t1的數(shù)據(jù)匯總計(jì)算而來(lái)的，并假定各個(gè)表的數(shù)據(jù)都得到了及時(shí)計(jì)算，現(xiàn)需對(duì)系統(tǒng)進(jìn)行優(yōu)化，以提高與視圖v1有關(guān)的查詢頻率，設(shè)有如下優(yōu)化方案： 為t1建立主索引 為t2建立主索引 為t3建立主索引 重寫(xiě)視圖v1，將其數(shù)據(jù)源調(diào)整為t4，并為t4建立主索引 重寫(xiě)視圖v1，將其數(shù)據(jù)源調(diào)整為t2，并為t2建立主索引請(qǐng)針對(duì)每一個(gè)優(yōu)化方案，說(shuō)明該優(yōu)化方案對(duì)實(shí)現(xiàn)目標(biāo)是否有效，并簡(jiǎn)要說(shuō)明原因。(2)下圖為關(guān)系表scodents(專業(yè)，姓名，學(xué)號(hào)，年齡)，在對(duì)應(yīng)的

5、數(shù)據(jù)文件中的物理存儲(chǔ)結(jié)構(gòu)示意圖寫(xiě)出“專業(yè)”屬性上建立聚集索引的sql語(yǔ)句，索引值按升序排序(索引名為zy_ind)假設(shè)索引按稠密索引方式組織數(shù)據(jù)，畫(huà)出在“專業(yè)”屬性上建立聚集索引后該表對(duì)應(yīng)的索引文件和數(shù)據(jù)文件的物理存儲(chǔ)結(jié)構(gòu)示意圖(以“專業(yè)”為查找碼，按查找自定義的首字母升序排序)三、設(shè)計(jì)與應(yīng)用題(共40分)(1)有商品表(商品號(hào)，商品名，分類，單價(jià))，請(qǐng)編寫(xiě)一個(gè)實(shí)現(xiàn)更改商品單價(jià)的存儲(chǔ)過(guò)程(存儲(chǔ)過(guò)程名為pupdate)，更改規(guī)則如下：“電腦”類商品降價(jià)10%，“電視”類商品降價(jià)6%，“冰箱”類商品降價(jià)3%，其他商品不降價(jià)。以商品的分類作為輸入?yún)?shù)，假設(shè)“分類”為字符串類型，長(zhǎng)度最多為6個(gè)漢字。如

6、果商品表中沒(méi)有用戶指定的分類，則用輸出參數(shù)返回字符串“指定的分類不存在”;如果用戶指定的分類存在，則用輸出參數(shù)返回字符串“修改已成功”。(10分)(2)現(xiàn)有某圖書(shū)銷售數(shù)據(jù)庫(kù)，其關(guān)系表結(jié)構(gòu)如下：圖書(shū)表(圖書(shū)編號(hào)，圖書(shū)名稱，出版社編號(hào)，出版名稱，出版時(shí)間，出版數(shù)量，版次)圖書(shū)銷售表(圖書(shū)編號(hào)，銷售日期，銷售數(shù)量，書(shū)店編號(hào)，讀者編號(hào)，讀者姓名，讀者電話)書(shū)店表(書(shū)店編號(hào)，聯(lián)系電話，所在城市編號(hào)，城市名稱).系統(tǒng)所涉及的數(shù)據(jù)存在如下約束出版社可以出版多本圖書(shū)，一本圖書(shū)只能在一個(gè)出版社出版，在該系統(tǒng)的記錄的圖書(shū)出版信息包括出版時(shí)間、版次及出版數(shù)量信息。.一個(gè)書(shū)店可以出售多本圖書(shū)給多個(gè)讀者，每位讀者可以從

7、多個(gè)書(shū)店購(gòu)買(mǎi)多本圖書(shū)，一本圖書(shū)可以通過(guò)多個(gè)書(shū)店出售給讀者，書(shū)店把圖書(shū)出售給讀者后會(huì)在系統(tǒng)中記錄售書(shū)日期和售書(shū)數(shù)量信息：.每個(gè)書(shū)店只能位于一個(gè)城市，一個(gè)城市可以有多個(gè)書(shū)店。 請(qǐng)根據(jù)以上信息畫(huà)出合理的圖書(shū)銷售數(shù)據(jù)庫(kù)的概念模型(用er圖表示)。(8分) 以圖書(shū)銷售表為例說(shuō)明原數(shù)據(jù)庫(kù)設(shè)計(jì)的不合理之處。(4分) 給出該數(shù)據(jù)庫(kù)符合3nf要求的全部關(guān)系模式，并指出關(guān)系模式中的全部主碼和外碼。(8分)(3)事務(wù)t1、t2和t3按如下調(diào)度方式并發(fā)地對(duì)數(shù)據(jù)項(xiàng)a、b、c進(jìn)行訪問(wèn)，假設(shè)a、b、c的初值分別為a=20，b=30，c=40。 在事務(wù)t2剛完成提交后，數(shù)據(jù)庫(kù)中a、b、c的值各是多少?(3分) 當(dāng)事務(wù)t3結(jié)束

8、后，給出該并發(fā)調(diào)度對(duì)應(yīng)的日志文件，并說(shuō)明此時(shí)a、b、c的值各是多少?(7分)0903參考答案080408090903二、填空題（1） slect a.* from emp a, dept b, emp cwhere a.did=b.did and b.mgr_id=c.eidand a.salary>10000 and a.age=c.age（2） 方法2更加合理。理由有三：其一，更加有利于減少索引層次，提高查找效率。其二、更加符合使用習(xí)慣。其三、更加便于以（系、班）進(jìn)行統(tǒng)計(jì)。create index i_index_1 on 學(xué)生表（系號(hào)，班號(hào)）（3）不是等價(jià)沖突，因?yàn)閠1的write

9、（q）和t2的write（q）是沖突操作，不能交換。三、應(yīng)用題（1） creat table 車(chē)輛信息 （車(chē)輛號(hào) char（7） not null check 車(chē)輛號(hào) like（“京a-z 0-9 0-9 0-9 0-9 0-9”）車(chē)型 char（6） default “轎車(chē)”，發(fā)動(dòng)機(jī)號(hào) char（6） not null,行使里程 int check（行使里程 >= 0），車(chē)輛所有人 char（8） not null,聯(lián)系電話 char（13） unique,primsry key （車(chē)輛號(hào)）（2） 主機(jī)碼為：（教師號(hào)、課程號(hào)）第1范式，因?yàn)榇嬖诓糠忠蕾?。分布方法為：教師?（教師號(hào)，姓

10、名，職稱）課程表 （課程號(hào)，課程名，學(xué)分，教科書(shū)名）教師授課表 （教師號(hào)，課程號(hào)）（3） 占用空間量從大至小排序?yàn)椋翰呗?，策略2，策略1備份速度從快到慢排序?yàn)椋翰呗?，策略2，策略3恢復(fù)速度從快到慢排序?yàn)椋翰呗?，策略2，策略11009二、應(yīng)用題（每空2分，共30分）（1）【1】文件支持多種記錄類型的存儲(chǔ)，這種文件將來(lái)自多個(gè)關(guān)系表不同類型的記錄存儲(chǔ)在一起，可減少多表查詢時(shí)磁盤(pán)的訪問(wèn)量，提高多表查詢的效率。設(shè)有表t（c1,c2），c1的數(shù)據(jù)類型為char（2），要求其第i位的取值范圍為1,9,第2位的取值范圍為0, 9.實(shí)現(xiàn)此要求的約束表達(dá)式為check（c1 like“【2】”）。（3）設(shè)有

11、某種文件，當(dāng)向其中插入一條新記錄時(shí)，首先讀文件頭，找到最末磁盤(pán)塊的地址后將最末磁盤(pán)塊讀入內(nèi)存緩沖區(qū)，在緩沖區(qū)內(nèi)將新插入記錄寫(xiě)入該數(shù)據(jù)塊的末尾，然后將緩沖區(qū)中修改過(guò)的數(shù)據(jù)塊作為最末磁盤(pán)塊寫(xiě)回磁盤(pán)文件。以這種方式組織的文件被稱為【3】。（4）在保證數(shù)據(jù)一致性的加鎖協(xié)議中，【4】級(jí)加鎖協(xié)議要求事務(wù)在讀數(shù)據(jù)項(xiàng)之前必須先對(duì)該數(shù)據(jù)項(xiàng)加共享鎖，直到事務(wù)結(jié)束才釋放該鎖。（5）利用日志技術(shù)進(jìn)行數(shù)據(jù)庫(kù)恢復(fù)時(shí)，為了使恢復(fù)子系統(tǒng)避免對(duì)整個(gè)日志文件進(jìn)行檢查，需要在日志文件中增加【5】記錄。（6）數(shù)據(jù)庫(kù)邏輯設(shè)計(jì)的目標(biāo)是把概念設(shè)計(jì)階段得到的【6】模型轉(zhuǎn)換成所選定的數(shù)據(jù)庫(kù)管理系統(tǒng)支持的數(shù)據(jù)模型。在各種數(shù)據(jù)環(huán)境中，粒度用于描述

12、數(shù)據(jù)的綜合程度。設(shè)某系統(tǒng)中有商品銷售表（商品號(hào)，銷售量，單價(jià)，日期，時(shí)間）和商品周銷售表（商品號(hào)，周銷售量，周），則商品銷售表的粒度級(jí)要【7】于商品周銷售表的粒度級(jí)。若兩個(gè)表的時(shí)間跨度相同，則商品銷售表中存放的數(shù)據(jù)量要【8】于商品周銷售表的數(shù)據(jù)量。（8）設(shè)有商品表（商品號(hào)，商品名，單價(jià)）和銷售表（商品號(hào)，銷售時(shí)間，銷售數(shù)量）?，F(xiàn)要查詢單價(jià)高于100元的全部商品的銷售總次數(shù)和總數(shù)量，包括沒(méi)有被賣(mài)出過(guò)的商品（沒(méi)有被賣(mài)出過(guò)的商品的銷售總次數(shù)和總數(shù)量置為0）。請(qǐng)補(bǔ)全如下語(yǔ)句：select商品表。商品號(hào)，銷售總次數(shù)=【9】，銷售總數(shù)量=sum（銷售數(shù)量）from商品表【10】銷售表0n商品表。商品號(hào)=銷

13、售表。商品號(hào)where單價(jià)>100group by商品表。商品號(hào)（9）在sql server 2000中，設(shè)u1是某數(shù)據(jù)庫(kù)中的用戶，若要使u1在該數(shù)據(jù)庫(kù)中只具有查詢?nèi)坑脩舯淼臋?quán)限。請(qǐng)補(bǔ)全如下語(yǔ)句：exec sp_addrolemember“【11】”,“【12】”（10）sql server 2000內(nèi)置的系統(tǒng)管理員賬戶是【13】（11）設(shè)在sql server 2000平臺(tái)一民對(duì)數(shù)據(jù)庫(kù)salesdb進(jìn)行了如下圖所示的各份操作。己知這三次備份操作均備份在設(shè)備bk上，且該設(shè)備只包含這三個(gè)備份操作的結(jié)果。在數(shù)據(jù)庫(kù)出現(xiàn)故障后，為盡可能減少數(shù)據(jù)丟失，需要恢復(fù)所有的備份。請(qǐng)補(bǔ)全如下用于恢復(fù)日志備

14、份1的sql語(yǔ)句：restore log salesdb from bkwith【14】【15】三、設(shè)計(jì)與應(yīng)用題（共30分）（1）已知某教務(wù)管理系統(tǒng)有下列關(guān)系模式（帶下劃線的屬性是主碼）：系（系號(hào)，系名）學(xué)生（系號(hào)，姓名，性別，入學(xué)日期，正常畢業(yè)日期，所在系號(hào)）課程（課程號(hào)，課程名，學(xué)分，開(kāi)課系號(hào)，教師號(hào)）選課（學(xué)號(hào)，課程號(hào)，成績(jī)）教師（教師號(hào)，姓名，職稱，所在系號(hào)）請(qǐng)根據(jù)給出的關(guān)系模式畫(huà)出該系統(tǒng)的er圖，圖中忽略實(shí)體集的屬性，但如果實(shí)體集之間的聯(lián)系有屬性則需要給出聯(lián)系的屬性。（6分）隨著教學(xué)改革的需要，該系統(tǒng)需要滿足下列需求：i.需為課程增加先修課信息（一門(mén)課程可有多門(mén)先修課程）；ii.一門(mén)

15、課程可由多名教師講授，一名教師可以講授多門(mén)課程。請(qǐng)根據(jù)以上需求修改關(guān)系模式，僅列出有改動(dòng)的關(guān)系模式，并使每個(gè)關(guān)系模式滿足3nf。（4分）（2）在sqlserver2000的某數(shù)據(jù)庫(kù)中有如下兩張關(guān)系表：學(xué)生表（學(xué)號(hào)，姓名，性別，系號(hào)），學(xué)號(hào)為主碼系表（系號(hào)，系名，系簡(jiǎn)稱），系號(hào)為主碼在數(shù)據(jù)庫(kù)中執(zhí)行如下t-sql代碼：declare deptid varchar（10）declare cnt intset cnt=odeclare cursor1 cursor for seleet系號(hào)from系表where系名like'%電%'open cursorlfetch next from

16、 cursor1 into deptidwhile fetch_status=obegindeclare temp_cnt intselect temp_cnt=count（*）from學(xué)生表where系號(hào)=deptidset cnt=cnt+temp_cntfetch next from cursor1 into odeptidendclose cursor1deallocate  cursor1select cnt執(zhí)行過(guò)程中發(fā)現(xiàn)速度比較慢，為了解決性能問(wèn)題，需在功能不變的情況下，將此t-sql代碼改為一條sql語(yǔ)句，請(qǐng)寫(xiě)出此sql語(yǔ)句（語(yǔ)句中不能含有子查詢）并說(shuō)明為什么此種修改可以提高性能。（5分）設(shè)在學(xué)生表的（姓名，系號(hào)）列上建有一個(gè)復(fù)合索引，該索引是否有助于提高下列兩個(gè)語(yǔ)句的查詢效率？并說(shuō)明原因。（5分）select*from學(xué)生表 where系號(hào)='