數(shù)據(jù)庫原理、技術(shù)及應(yīng)用習(xí)題匯編

..作業(yè)6：〔數(shù)據(jù)庫原理局部〕一、設(shè)某醫(yī)院住院管理數(shù)據(jù)庫包括病人、醫(yī)生、科室、病房以及護(hù)士等5個實體集，其中"病人〞包括病歷號、、性別、入院時間、病癥等屬性；"醫(yī)生〞包括醫(yī)生工號、、年齡、職稱等屬性；"科室〞包括科室編碼、名稱、辦公地點(diǎn)、科室主任等屬性；"病房〞包括病房號、位置、床位數(shù)等屬性；"護(hù)士〞包括護(hù)士工號、、年齡等屬性。醫(yī)院住院管理的根本語義如下：①一個醫(yī)生對應(yīng)治療多個病人，一個病人可有多個醫(yī)生治療；②一個科室有多個醫(yī)生和護(hù)士，一個醫(yī)生和護(hù)士只屬于一個科室；③一個病人對應(yīng)一個床位號，一個護(hù)士護(hù)理多個床位，一個床位有多個護(hù)士護(hù)理。試根據(jù)上述語義完成以下各項數(shù)據(jù)庫設(shè)計任務(wù)：①設(shè)計滿足上述要求的E-R圖。〔8分〕②將該E-R圖轉(zhuǎn)換成關(guān)系模式，并指出每一個關(guān)系的主碼和外碼?！?分〕③編寫創(chuàng)立"醫(yī)生〞關(guān)系模式對應(yīng)的根本表的T-SQL語句。〔4分〕④假設(shè)符合1NF的醫(yī)院數(shù)據(jù)庫的關(guān)系模式如下所示，其根本語義為同一病人同一入院時間只有一個主治醫(yī)生，試根據(jù)式理論將該關(guān)系模式分解成符合3NF的多個關(guān)系模式?！?分〕醫(yī)療〔病歷號，病人，病癥，床位號，病房編號，病房位置，床位數(shù)，醫(yī)生工號，醫(yī)生，醫(yī)生職稱，科室編碼，科室名稱，科室地點(diǎn)，科室主任〕二、試設(shè)計某科技園區(qū)用電管理系統(tǒng)關(guān)系數(shù)據(jù)庫，該系統(tǒng)根本信息需求如下：1〕園區(qū)有多幢建筑物〔即辦公樓〕，建筑物根本信息包括：建筑物編碼、建筑物名稱、建筑物樓對應(yīng)的經(jīng)度緯度坐標(biāo)、建筑物所在地址、最低樓層、最高樓層、樓層數(shù)〔計算得到〕等。2〕園區(qū)可以進(jìn)駐多個企業(yè)單位〔用戶〕，企業(yè)根本信息包括企業(yè)編碼、企業(yè)名稱、聯(lián)系人、聯(lián)系、進(jìn)駐時間等。每個企業(yè)進(jìn)駐園區(qū)后，其管理總部對應(yīng)園區(qū)建筑物的某個樓層和房間號。3〕建筑物的各個樓層中安裝有多個電表，電表根本信息包括：電表編碼、電表名稱、規(guī)格型號、供給商名稱、安裝位置〔包括經(jīng)度緯度坐標(biāo)、建筑物及樓層〕、電表所屬的企業(yè)用戶等。4〕園區(qū)物業(yè)管委會可以定期或隨時采集各個電表的當(dāng)前電表指示數(shù)據(jù)，存放在數(shù)據(jù)庫中，同一電表同一天最多只保存一次數(shù)據(jù)采集記錄。根據(jù)采集的數(shù)據(jù)和時間，系統(tǒng)可定期或?qū)崟r計算出每個電表的實際耗電量，進(jìn)而統(tǒng)計出每個企業(yè)的用電量。5〕為了更好節(jié)約和控制用電，每個企業(yè)單位每年都有一個分月度的用電方案。月末時，數(shù)據(jù)庫應(yīng)用系統(tǒng)可統(tǒng)計出每個企業(yè)的用電超標(biāo)情況。試根據(jù)上述語義，完成以下各題：1.設(shè)計滿足上述要求的數(shù)據(jù)庫E－R圖?！?分〕2.根據(jù)E-R圖，轉(zhuǎn)換得到各個關(guān)系模式，并指出每一個關(guān)系模式的主碼和外碼?！?分〕3．使用關(guān)系代數(shù)完成以下檢索：〔6分〕①檢索"信達(dá)〞〔企業(yè)名稱〕公司2014年5月10日在"科技創(chuàng)新樓〞〔建筑物名稱〕中的所有電表的采集數(shù)據(jù)。②檢索哪些建筑物中安裝有兩個或兩個以上不同單位的電表，列出這些建筑物的名稱。4〕假設(shè)一個關(guān)系模式如下，試運(yùn)用式理論和方法，從第一式開場將其逐步轉(zhuǎn)換成符合3NF的關(guān)系模式?！?分〕電能管理數(shù)據(jù)庫=〔電表編碼，電表名稱，電表所在樓層，企業(yè)編碼，企業(yè)名稱，建筑物編碼，建筑物名稱，電表采集時間、電表當(dāng)前指示數(shù)〕三、圖書出版和銷售管理的一個局部E—R圖及其各個實體的屬性如以下圖1所示，其語義如下：一本書可以由多個作者編寫，一個作者可以編寫多本書；一本書只能在一個出版，但一個可以出版多本書；一個書店可以銷售多本書，一本書可以在多個書店銷售；書店銷售容包括：銷售訂單號、銷售日期和銷售數(shù)量等信息；同一本書同一銷售訂單中只出現(xiàn)一次。①試將該E—R圖中的各種聯(lián)系〔包括聯(lián)系的類型和聯(lián)系的屬性〕標(biāo)注完整?！?分〕圖1.圖書出版與銷售管理的局部E—R圖②將上述E—R圖轉(zhuǎn)換成關(guān)系模型，導(dǎo)出各個關(guān)系模式，注明各個關(guān)系模式的主碼和外碼；根據(jù)式理論，分析上述各個關(guān)系模式是否屬于3NF。〔10分〕作業(yè)2：1.從產(chǎn)品表中檢索哪些產(chǎn)品名稱的前10個字符中包含en字符串。2.從產(chǎn)品表中檢索哪些產(chǎn)品名稱中出現(xiàn)兩個er字符串。3.從產(chǎn)品表中檢索哪些產(chǎn)品名稱中只包含一個er字符串。4.從產(chǎn)品表中檢索哪些產(chǎn)品名稱中包含單引號字符。5.從客戶表中檢索哪些客戶名稱所包含的字符個數(shù)最多。6.根據(jù)產(chǎn)品表和供給商表，檢索哪些產(chǎn)品是由美國的供給商提供的。7.從產(chǎn)品表中檢索第一大類產(chǎn)品中〔即categoryid值為1〕哪些產(chǎn)品的單價大于其同類產(chǎn)品的平均單價。8.從產(chǎn)品表中檢索第一大類產(chǎn)品中〔即categoryid值為1〕哪些產(chǎn)品的單價最低。9.從訂單明細(xì)表〔Orderitems〕中計算10號產(chǎn)品的銷售額和平均銷售單價。〔提示：使用sum函數(shù)〕10.根據(jù)產(chǎn)品表和訂單明細(xì)表，統(tǒng)計計算Tofu這個產(chǎn)品的銷售額〔提示：使用sum函數(shù)和IN〕。?作業(yè)3：1.統(tǒng)計檢索銷售額最大的前30%的客戶他們購置哪些產(chǎn)品的次數(shù)最多。2.統(tǒng)計檢索哪些客戶購置tofu這個產(chǎn)品的次數(shù)最多。3.統(tǒng)計計算銷售額最大的前30%的客戶中有哪些購置了tofu這個產(chǎn)品。4.分別從客戶表和產(chǎn)品表中隨機(jī)提取一個客戶和產(chǎn)品，統(tǒng)計計算這個客戶購置這個產(chǎn)品的銷售量和銷售額。5.單價最貴的前10個產(chǎn)品中2008年每個客戶購置這些產(chǎn)品的次數(shù)分別是多少。6.統(tǒng)計計算銷售額最大的前30%的客戶他們的銷售額占總銷售額的百分比。7.利用自連接，統(tǒng)計檢索哪些產(chǎn)品的單價是一樣的。8.統(tǒng)計檢索哪些產(chǎn)品的銷售額與tofu這個產(chǎn)品的銷售額相差最大。9.統(tǒng)計檢索哪些產(chǎn)品的銷售額與tofu這個產(chǎn)品的銷售額相差在10%之。10.統(tǒng)計檢索哪些產(chǎn)品2008年度每筆銷售的銷售單價都是大于本錢單價的1.1倍以上。11.統(tǒng)計檢索哪些美國的客戶從未買過美國供給商供給的產(chǎn)品。12.統(tǒng)計每類產(chǎn)品2008年每個月的銷售額。在此根底上，統(tǒng)計檢索哪幾類產(chǎn)品有兩個月以上的銷售額在同月中排名前3位。13.統(tǒng)計計算銷售額最大的30%客戶購置tofu這個產(chǎn)品的數(shù)量占總tofu這個產(chǎn)品銷量的百分比。14.統(tǒng)計2009年每一筆訂單的利潤和利潤率。利潤=銷售量*〔銷售單價-本錢單價〕，利潤率=100*利潤/銷售額。15.統(tǒng)計檢索銷售額最大的前30%客戶沒有購置過哪些產(chǎn)品。?作業(yè)4：1.從2008年銷售次數(shù)最多的前5個產(chǎn)品中隨機(jī)提取1個產(chǎn)品，求這個產(chǎn)品的銷售額在同類產(chǎn)品銷售額中的排名名次。2.利用相關(guān)子查詢，計算每個產(chǎn)品的銷售額在同類產(chǎn)品銷售額中的排名名次。3.隨機(jī)提取一個客戶，計算這個客戶2009年度每個月銷售額與上年同期銷售額的增長率。4.在2008年1月1日至2009年12月31日之間隨機(jī)產(chǎn)生一個日期區(qū)間，分別查詢在這個日期區(qū)間哪些客戶購置tofu這個產(chǎn)品的次數(shù)最多和最少。5.統(tǒng)計2008年哪些客戶購置了tofu這個產(chǎn)品但沒有購置longlifetofu這個產(chǎn)品。6.統(tǒng)計哪些產(chǎn)品同時出現(xiàn)在同一個訂單中的比例最高？注意：除數(shù)為每個產(chǎn)品的銷售次數(shù)。數(shù)據(jù)庫原理與應(yīng)用模擬卷〔作業(yè)5〕1．某公司需建立產(chǎn)品信息數(shù)據(jù)庫，其語義如下：①一種產(chǎn)品可以使用多個不同種類的部件，也可以使用多個不同種類的零件：一種部件至少被用在一種產(chǎn)品中；一種部件可以由多個不同種類的零件組成；一種零件至少被用在一種產(chǎn)品或一種部件中；②對于一種具體的部件，要記錄這種部件使用的各種零件的數(shù)量；③對于一種具體的產(chǎn)品，要記錄這種產(chǎn)品使用的各種部件數(shù)量和各種零件數(shù)量，但部件所使用的零件數(shù)量不計入該產(chǎn)品的零件數(shù)量；④一個供給商可供給多種零件，但一種零件只能由一個供給商供給。圖1.產(chǎn)品信息數(shù)據(jù)庫E—R圖在此E-R圖中，實體的屬性完整，試完成以下各題：①根據(jù)以上需求，在圖1中寫出各個實體〔型〕之間的聯(lián)系及其聯(lián)系的屬性?！?分〕②根據(jù)E-R圖導(dǎo)出該數(shù)據(jù)庫的各個關(guān)系模式，標(biāo)出每個關(guān)系模式的主碼和外碼，并根據(jù)式理論，分析上述各個關(guān)系模式是否屬于3NF?！?0分〕2．圖書出版和銷售管理的一個局部E—R圖及其各個實體的屬性如以下圖1所示，其語義如下：一本書可以由多個作者編寫，一個作者可以編寫多本書；一本書只能在一個出版，但一個可以出版多本書；一個書店可以銷售多本書，一本書可以在多個書店銷售；書店銷售容包括：銷售訂單號、銷售日期和銷售數(shù)量等信息；同一本書同一銷售訂單中只出現(xiàn)一次。①試將該E—R圖中的各種聯(lián)系〔包括聯(lián)系的類型和聯(lián)系的屬性〕標(biāo)注完整?！?分〕圖1.圖書出版與銷售管理的局部E—R圖②將上述E—R圖轉(zhuǎn)換成關(guān)系模型，導(dǎo)出各個關(guān)系模式，注明各個關(guān)系模式的主碼和外碼；根據(jù)式理論，分析上述各個關(guān)系模式是否屬于3NF?！?0分〕3．某數(shù)據(jù)庫包含學(xué)生〔Students〕、課程〔Courses〕、選課〔StudCourses〕等3個關(guān)系模式，其根本數(shù)據(jù)如表1所示，試解答以下各題。表1.關(guān)系數(shù)據(jù)庫實例〔a〕學(xué)生關(guān)系：Students〔b〕課程關(guān)系：Courses〔c〕選課關(guān)系：StudCourses學(xué)號SnoSname性別Gender所屬專業(yè)Major課程號o課程名ame類別Type學(xué)號Sno課程號o成績GradeS1雯女信息管理C1數(shù)據(jù)庫1S1C180S2高波男計算機(jī)科學(xué)C2C程序設(shè)計2S1C276S3遠(yuǎn)男電子商務(wù)C3數(shù)據(jù)構(gòu)造1S1C393S4訓(xùn)男信息管理C4Web技術(shù)2S2C280S5胡娟女計算機(jī)科學(xué)C5軟件工程3S2C346S2C478S3C262S4C152S4C379S4C456S5C486①關(guān)系代數(shù)Π2,4(Students(Π1,2(StuCourses)÷Π1(σ3=1(Courses))))的計算結(jié)果為：〔6分〕②檢索"信息管理〞專業(yè)中哪些學(xué)生〔要求列出〕每門課程的考試成績都及格，寫出其關(guān)系代數(shù)表達(dá)式?！?分〕③某數(shù)據(jù)檢索的關(guān)系代數(shù)表達(dá)式如下：((StudCourses))(StudCourses))Students)寫出與該語句對應(yīng)的T-SQL語句?！?分〕二、程序閱讀題〔閱讀以下T-SQL語句，寫出標(biāo)記處各條SELECT語句運(yùn)行的結(jié)果，此題共30分，每題6分，任選5小題〕數(shù)據(jù)表Test1、Test2的構(gòu)造定義如下，其原始數(shù)據(jù)分別如表2和表3所示。其中Test1表中的F7和F8、Test2表2中的C1列需要自己計算得到。寫出以下程序各個小題中SELECT語句的檢索結(jié)果。CREATETABLEtest1(F1intidentityprimarykey,F2varchar(10),F3varchar(10),F4tinyint,F5datetimedefault(getdate()),F6intdefault(0),F7ASLEFT(f2,1)+RIGHT(f3,1)+CAST(f1asvarchar))GOCREATETABLEtest2(C1intidentity(200,2),C2varchar(20),C3int,C4int)GO表2.Test1原始數(shù)據(jù)F1F2F3F4F5F6F7F81froggreen22011-03-1500:00:00.000152catgrey32011-06-1600:00:00.000163birdgreen22011-09-1300:00:00.000174mousegreen12011-10-3000:00:00.000195foxred32011-11-1000:00:00.000206sheepred12011-12-3000:00:00.000187tigerred22012-01-1800:00:00.000168pigblack22012-02-1100:00:00.000159wolfgrey32012-03-1200:00:00.00017表3.Test2原始數(shù)據(jù)C1C2C3C4C0128C0237C0316C0425C0534C0643C0732C0823C0914C1045①第1小題SELECTf1,f3+SPACE(1)+f2AS'Animal',f4,Convert(varchar(10),f5,120)as'f5'FROMtest1WHEREDATEDIFF(month,f5,getdate())>=6andf3like'%e[dy]%'ORDERBYf3,f2DESCGO②)第2小題SELECTf2,f3,CASEWHENf4=1THEN'R'WHENf4=2THEN'S'WHENf4=3THEN'T'ELSE'X'ENDasf4,f6,f7FROMtest1WHEREf6BETWEEN16AND18GO③第3小題CREATEPROCEDUREproc1(v1varchar(10))ASBEGINdeclaresvarchar(500)sets='SELECTsum(c4*f6)asamtFROMtest2asaJOINtest1asbONa.c3=b.f1'+space(1)sets=s+'WHEREa.c3='+ltrim(str(v1,4))execute(s)ENDGOEXECUTEproc12GO④第4小題DeclaretTABLE(h1int,h2varchar(10),h3varchar(10),h4int)INSERTINTOtSELECTc1,c2,f2,c4*f6FROMtest2aJOINtest1bONb.f1=a.c3WHEREb.f3='green'SELECT*FROMt⑤第5小題CREATEFUNCTIONfn1(v1int)RETURNSvarchar(20)ASBEGINDeclaresvarchar(20),nint,s1varchar(10)SELECTn=count(*)fromtest2wherec3=v1SELECTs1=f3+space(1)+f2fromtest1wheref1=v1IFn>0BEGINSets=cast(nasvarchar)+space(1)+s1IFn>1sets=s+'s'ENDELSEIFn=0sets='no