云南大學(xué)數(shù)據(jù)庫(kù)期末大作業(yè)數(shù)據(jù)庫(kù)設(shè)計(jì)

1、云南大學(xué)軟件學(xué)院實(shí)驗(yàn)報(bào)告課程： 數(shù)據(jù)庫(kù)原理與實(shí)用技術(shù)實(shí)驗(yàn) 學(xué)期: 任課教師： 專業(yè)： 學(xué)號(hào)： 姓名： 成績(jī)： 期末大作業(yè)： Electronic Ventor數(shù)據(jù)庫(kù)設(shè)計(jì)一、實(shí)驗(yàn)?zāi)康?1)掌握數(shù)據(jù)庫(kù)設(shè)計(jì)的基本方法(2)掌握各種數(shù)據(jù)庫(kù)對(duì)象的設(shè)計(jì)方法(3)熟練掌握DBA必須具備的技能二、實(shí)驗(yàn)內(nèi)容1、根據(jù)項(xiàng)目的應(yīng)用和項(xiàng)目的需求說明文檔，進(jìn)行詳細(xì)的需求分析，給出需求分析 的結(jié)果。(1)客戶可以在網(wǎng)站上注冊(cè)，注冊(cè)的客戶要提供客戶的姓名、電話、地址，以方便售 后和聯(lián)系，姓名即作為用戶名，和密碼一起用于注冊(cè)和登錄，客戶編號(hào)可唯一識(shí)別用戶， 卡號(hào)可網(wǎng)上支付。其中地址、電話以方便聯(lián)系和寄貨；(2)網(wǎng)站管理員可以登

2、記各種商品，供客戶查詢，訂購(gòu)。登記商品時(shí)要提供商品的名 稱、價(jià)格，商店中現(xiàn)有商品量，商品編號(hào)可唯一識(shí)別商品；(3)類別表示商品所屬類別，類別編號(hào)可唯一識(shí)別類別，其中包含了，商品類別名稱 和制造廠商，可以對(duì)商品進(jìn)行分類售賣；(4)客戶可以在網(wǎng)上下訂單，也可以到實(shí)體店購(gòu)物，其在訂單上所選擇的支付方式不 同(信用卡、借記卡、現(xiàn)金，現(xiàn)金代表實(shí)體店購(gòu)物)，網(wǎng)站管理員可以查看訂單，并及 時(shí)將訂單的處理情況更新(比如貨物已寄出的信息，訂單狀態(tài)：0:未處理，1:已處理, 2:已發(fā)貨)；訂單編號(hào)可唯一識(shí)別訂單，訂單中包含訂單產(chǎn)生時(shí)間，訂單狀態(tài)，支付方 式和支付總額；(5)實(shí)體商店有自己的店名，賣多種商品，每個(gè)商

3、店都有固定的地址，顧客可以到店 中買商品，(注：在實(shí)體店中購(gòu)買商品的顧客一律將顧客名默認(rèn)為佚名)，當(dāng)商店中的庫(kù) 存量小于10時(shí)會(huì)有提醒到倉(cāng)庫(kù)中拿貨；(6)配送單中包含查詢號(hào)可唯一識(shí)別配送單，配送人，聯(lián)系方式；(7)倉(cāng)庫(kù)中倉(cāng)庫(kù)編號(hào)可唯一識(shí)別倉(cāng)庫(kù)，其中每個(gè)倉(cāng)庫(kù)都有區(qū)號(hào)，代表其地址。(8)各實(shí)體向關(guān)系1) 一個(gè)客戶可以購(gòu)買多種商品，一種商品可以被多個(gè)客戶購(gòu)買；2) 一個(gè)商品屬于且僅屬于一種類別，一種類別的商品可以包含多個(gè)商品或沒有；3) 一種商品放在多個(gè)商店中銷售，一個(gè)商店至少銷售一種或銷售多種商品；4) 一個(gè)訂單對(duì)應(yīng)一個(gè)客戶，一個(gè)客戶對(duì)應(yīng)多個(gè)訂單；5) 一個(gè)訂單對(duì)應(yīng)至少有一件商品或多件，一個(gè)商品對(duì)

4、應(yīng)多個(gè)訂單；6) 一個(gè)訂單可以有一個(gè)商品配送單7) 一個(gè)倉(cāng)庫(kù)可以存放多種商品，一種商品可以存放在一個(gè)倉(cāng)庫(kù)；皂運(yùn)istz. 姓府一 SM客戶弱號(hào)8C.5疣空號(hào) 5一 心式二七人LEhtnE0工心茗苫茬；：6 /=二nu工: E，餐子運(yùn)紀(jì)VttTL±bl ”hkKi*+7 ,.10）商苫三仁心x>萍松°2、數(shù)據(jù)庫(kù)邏輯設(shè)計(jì)：用文字簡(jiǎn)要描述實(shí)體之間的聯(lián)系，畫出 E-R圖（標(biāo)出各聯(lián)系中實(shí)體的基數(shù)）害戶zzs-atl e zjz.arazxEZ,S I上J iLUz-A£1&i>£. (15)aber (11)Vnriablt EuraEttrs

5、. (ZO) CbK-BCt&TE t 刈客戶-商品：n-n;商品-類別：n-1;商品-商店：n-n;客戶-訂單：1-n;訂單-商品：1-n;訂單-配送當(dāng)：1-1;倉(cāng)庫(kù)-商品：1-n3、數(shù)據(jù)庫(kù)物理設(shè)計(jì)：將E-R圖轉(zhuǎn)換為數(shù)據(jù)表。需要給出詳細(xì)的轉(zhuǎn)換規(guī)則，對(duì)應(yīng)生成的表，屬性（主屬性、描述性屬性，多值屬性，符合屬性等）,主鍵，外鍵，約束（取值是否NULL等），索引（三種類型的索引至少每種出現(xiàn)一次）等。4A二工工'RicCl 1)vu ；hu '' 改二-吟三;_* *!西電地中我帛老可7ir:k*r(lC "洋壹 vir=larilO：.套.吉支古號(hào)B二皿；袁

6、啟魔苫W Vi zhiy ：選七 vaifikur .2 Z'）去£虧rv- 堆.產(chǎn)寓購(gòu)付華惠 i-F宣S燈總(10Ja±ihir (3)匚2MH 1 =fl«t配翅盆美任三_:丁k，訂工編寫vir '<£< J忑運(yùn)人vir:h*jf'：）立屁/送二過在客戶，配送單，類別中建立索引，語(yǔ)句在 4題中如3中，有2個(gè)n-n關(guān)系，分生成訂購(gòu)表和銷售表4、 SQL 中創(chuàng)建語(yǔ)句的使用：根據(jù)第二步中的結(jié)果，將相應(yīng)的表，屬性，主鍵，外鍵，約束等使用標(biāo)準(zhǔn)的 SQL CREATE 語(yǔ)句實(shí)現(xiàn)。(要求給出創(chuàng)建的標(biāo)準(zhǔn)語(yǔ)句，以及創(chuàng)建之后在SQL

7、數(shù)據(jù)庫(kù)中的腳本和截圖)。create table 客戶(客戶編號(hào)char(5) not null unique,姓名varchar(10),密碼varchar(15),電話numeric(11),地址varchar(20),卡號(hào)char(19),constraint PK_ 客戶 primary key (客戶編號(hào));create table 類別(類別編號(hào)char(5) not null unique,類別名稱varchar(10),制造商varchar(10) ,constraint PK_ 類別 primary key (類別編號(hào));create unique index 類別 _PK

8、on 類別 (類別編號(hào)ASC);create table 倉(cāng)庫(kù)(倉(cāng)庫(kù)編號(hào)char(5) not null unique,倉(cāng)庫(kù)區(qū)號(hào)varchar(5),constraint PK_ 倉(cāng)庫(kù) primary key (倉(cāng)庫(kù)編號(hào));create table 商品商品編號(hào)char(5) not null unique,倉(cāng)庫(kù)編號(hào)char(5) not null,類別編號(hào)char(5) not null,商品名稱varchar(50),價(jià)格 float,constraint PK_ 商品 primary key (商品編號(hào)),constraint fk_ 倉(cāng)庫(kù)編號(hào) foreign key(倉(cāng)庫(kù)編號(hào) )ref

9、erences 倉(cāng)庫(kù),constraint fk_ 類別編號(hào) foreign key(類別編號(hào)'references 類別);create unique clustered index 客戶 _PK on 客戶 (客戶編號(hào)ASC);create table 訂單(訂單編號(hào)char(5) not null,客戶編號(hào)char(5),訂購(gòu)時(shí)間varchar(10),支付方式varchar(10),訂單狀態(tài)numeric,總額 float ,constraint PK_ 訂單 primary key (訂單編號(hào) ),constraint fk_ 客戶編號(hào) foreign key(客戶編號(hào),re

10、ferences 客戶);create table 配送單(查詢號(hào) char(10) not null unique,訂單編號(hào)char(5) not null,配送人 varchar(10),聯(lián)系電話numeric(11),constraint PK_ 配送單 primary key (查詢號(hào)), constraint fk_ 訂單編號(hào) foreign key(訂單編號(hào) )references 訂單);create clustered index 配送 _FK on 配送單 (訂單編號(hào)ASC);create unique index 配送單 _PK on 配送單 (查詢號(hào) ASC);creat

11、e table 商店(商店號(hào) char(5) not null unique,商店名 char(10),地址varchar(20),constraint PK_ 商店 primary key (商店號(hào));create table 銷售(商店號(hào) char(5) not null,商品編號(hào)char(5) not null,庫(kù)存量 int,constraint PK_銷售 primary key(商店號(hào)，商品編號(hào));create table 訂購(gòu)(訂單編號(hào)char(5) not null,商品編號(hào)char(5) not null,數(shù)量 int,金額 float,constraint PK_訂購(gòu) pr

12、imary key (訂單編號(hào),商品編號(hào))；國(guó)口率毓表±) n dba倉(cāng)庫(kù)E二I dba訂單±)二dbd訂購(gòu)t J3 dbo.每戶S J dba類別3 3 dba配送單±.二 db。,商店S n db。,商品 ± dbd鎮(zhèn)警 1.h5、存儲(chǔ)過程、觸發(fā)器和視圖：根據(jù)需要給數(shù)據(jù)庫(kù)添加至少六個(gè)實(shí)用的存儲(chǔ)過程、觸發(fā)器和視圖,并說明它們各自的功能。(需要給出語(yǔ)句執(zhí)行的結(jié)果示意圖)創(chuàng)建視圖，查找商品名為蘋果的商品create view find_goodsasselect商品編號(hào)，商品名稱，價(jià)格from商品where商品名稱like '蘋果' * &

13、#187; . p * 一«-» )!» l j1 r3 create view find_gcQd3 iB3el品亡七 商品編號(hào),商品名稱,價(jià)格fmm商品Lwhere商品名稱工工讓.蘋果3消息命令已成功完成。+1 n題題圖A 區(qū) dbo.findgoods 日口列S)商品聚號(hào)(charCS), not null) 國(guó)商品名稱(varchjr(50), null) 國(guó)價(jià)格(float, null)(2)創(chuàng)建視圖，查找價(jià)格在3000-6000的商品create view price as select商品編號(hào)，商品名稱 from商品 where 價(jià)格 >300

14、0 and 價(jià)格 <6000|日 create view priceasselect商品編號(hào),商品名稱from商品Lwhere 價(jià)格>3。0。 and 價(jià)格<6QQO1)消息命令已成功完成。dboprice直日口列國(guó) 商品遍號(hào)(char(5), not null)H 商品名稱(varchair(5O), null)(3)創(chuàng)建觸發(fā)器，提醒店鋪中庫(kù)存量小于10的商品create trigger trigger_alarmon銷售after updateasif(select 庫(kù)存量 from 銷售 where 庫(kù)存量 <10)<10print 'trigger

15、 out:'select商品編號(hào)，庫(kù)存量from 銷售 where庫(kù)存量<10 returnSQLQueryS.sql - YA.s (yanyanpc (53)*觸發(fā)器sql =未由9 SQLQuEiftselect 庫(kù)存量f工皿 銷售庫(kù)存量COYl。 -print 1 trigger out:1select商品褊號(hào),庫(kù)存量from銷售where庫(kù)存量10L return緒果命令已成功完成。日update稍售 set庫(kù)存量XLwhes商品編號(hào)-國(guó)結(jié)果|。麗商品編罵庫(kù)存里UlUI-ll-：l_IMMIUlUI-IUUIb1 I sOM1 | 2 %IBIialBlfallB AI

16、BI iui bikimI(4)創(chuàng)建觸發(fā)器，當(dāng)刪除配送單中的數(shù)據(jù)時(shí)顯示出所刪信息create trigger trigger_delon配送單after deleteasprint 'delete'SQLQuery4.sql - YA.s (yanyanpc (54)* 5( create trigger triqger_d.elcm配送單after deleteASprint 1 delete'-select f rcio deleteiJ消息命令已成功完成。日delE七日 配送單Lwhere查徇號(hào)=，工810，盅結(jié)果育福查詢號(hào)訂單編號(hào)配送人聯(lián)系電話1 IfOoid

17、1 00011 泰信ibikiibiuibiuibiuibI(5)創(chuàng)建存儲(chǔ)過程，根據(jù)商品編號(hào)，查詢?cè)撋唐返挠嗁?gòu)量create proc sp_find_quantity商品編號(hào)char(5),sumq int outputasselect商品編號(hào)，sum(數(shù)量)from訂購(gòu)group by商品編號(hào)having商品編號(hào)=商品編號(hào) declare sumq intexec sp_find_quantity 商品編號(hào)='s0001',sumq=sumq output print 'the sum quantity is:'+ convert(

18、varchar(5),sumqVAN¥AN.bu5ine55 dbd商品 SQLQuery4.sql 臼 declare 口 int©商品編號(hào)=T。工-03umq=suir.q outputA print * the 5uic quantity 工耳L convertvarchar 5 ,g月umq)二I結(jié)果消息商品編號(hào)優(yōu)列名)1 r'sbobi 4(6)創(chuàng)建存儲(chǔ)過程，通過商品名稱尋找商品信息create proc sp_find_pricename char(10)asselect *from商品where 商品名稱 like name + '%'

19、exec sp_find_pricename='戴爾n結(jié)果出消息1商品編號(hào)倉(cāng)庫(kù)編號(hào)類別編號(hào)商品名稱價(jià)格:s(XK)3a(K)C3I0003戴爾(DELL)kis14L-2523B3400250009a0003I0003戴爾(DELL)hsl50Rl44SS5990330010a0003I0003戴爾(DELL)lns14UR 152SRR41006、分析常見的業(yè)務(wù)流程，列舉出至少五種SQL SELECT語(yǔ)句。實(shí)現(xiàn)的語(yǔ)句要滿足如下要求:a) 在五種語(yǔ)句中的查詢能反映正常的業(yè)務(wù)需求；b) 分析中至少要分別出現(xiàn)一次ORDER BY、GROUP BYHAVING 子句；c) 分析中至少使用一次

20、聚集函數(shù)；d) 分析中至少使用一次嵌套查詢；e) 分析中至少使用一次 UNION或INTERSECT運(yùn)算；給出每一種語(yǔ)句執(zhí)行的結(jié)果。(1)計(jì)算并查找訂購(gòu)單中相同訂單訂購(gòu)超過5000的總額select訂單編號(hào)，sum(金額)as總額from訂購(gòu)group by訂單編號(hào) having sum(金額)>5000SQLQueryl.sql - YA.S (yanyanpc (54)* elecc訂單編號(hào)”uml金額且白豆凝mm訂購(gòu)roup by 訂單編號(hào)L aving sw 金頷> >5。|nr口結(jié)果：消息 訂單編號(hào)總額piBii-irainriiBii-troieii-iiBin1

21、 j 0DM1 | 21&00J i-s r-1 s-i r, r r r_1 mm l i2000021220030000422SD040000685M5 ODDOS7sM6 0001021700(2)查找即購(gòu)買了商品編號(hào)為S0001又買了 S0002的客戶select姓名from客戶b,訂單o,訂購(gòu)swhere s.商品編號(hào)='s0001' and b.客戶編號(hào)=o.客戶編號(hào) and。.訂單編號(hào)=s.訂單編號(hào) intersect select姓名 from客戶b,訂單o,訂購(gòu)s where s.商品編號(hào)='s0002' and b.客戶編號(hào)=o.客戶

22、編號(hào) and。.訂單編號(hào)=s.訂單編號(hào)SQLQueryl.Eql - VA-s (yanyanpc (54)* select 姓名Emm客戶b,訂單露訂購(gòu)swhere m.商品編自h aOOOl' and “客戶編號(hào)y.客戶編號(hào) and 0.訂單編號(hào)F.訂單編號(hào) intersecc select. 姓名from客戶0訂單d訂購(gòu)sLwheru曰商品編號(hào)30002 R and 七客戶編號(hào)=?？蛻艟幪?hào) and Ox訂單編號(hào)7 .訂單編號(hào)T L"1I日結(jié)果4消息姓名1 :房靈j(3)按照顧客c0002消費(fèi)的總額大小順序排訂單編號(hào)select訂單編號(hào)，總額 from訂單where 客戶

23、編號(hào)='c0001'order by 總額 desc_ YANYAN.business - dba訂單 SQLQUfiryl Fs selec?fem訂單where客巧編號(hào)口口“-order by，總、額 desc0結(jié)果心消息訂單編號(hào)總額jisimemiirm mriim mi nn1 j 00004 j 及 3002000021220030000878004000113400(4)查詢訂單編號(hào)為 00003的配送信息select。.訂單編號(hào)，配送人，聯(lián)系電話 from 訂單 o,配送單 s where。.訂單編號(hào)=s.訂單編號(hào) and s訂單編號(hào)='00003'

24、;LI一 'YANYAN.business - dboS丁單 SQLQueryLsql - YA_s (yanyanpc (54)T select 口.外革輯與配送人聯(lián)系電話from訂單d配送單aI-where O .訂單編號(hào)訂單編號(hào)d4訂單編號(hào)?？?。3r*in目結(jié)果7感癡訂單編號(hào)配缸聯(lián)系電話1 rS»D3 5光信 1128373832查找同樣買了商品編號(hào)為 s0002的客戶信息select客戶編號(hào)，姓名，電話from客戶where客戶編號(hào)in(select客戶編號(hào) from 訂單，訂購(gòu)where訂單.訂單編號(hào)=訂購(gòu).訂單編號(hào) and訂購(gòu).商品編號(hào)='s0002

25、9;）日巴史工曰5客戶編號(hào),姓名,電話from客戶where客戶編號(hào)select客戶編號(hào)from訂單，仃購(gòu)-wnere 訂單.訂單編號(hào),丁購(gòu).訂單編號(hào)and訂購(gòu).商品編號(hào)=匕。21客戶編號(hào)姓名電話1 f cCWI房靈 1533需&91832 %旃 ijft 18293a32332（6）查詢當(dāng)日銷售總額select訂購(gòu)時(shí)間，sum（總額）as營(yíng)業(yè)額from訂單group by訂購(gòu)時(shí)間 h * rI134曰3訂購(gòu)時(shí)間1師（總額I as營(yíng)業(yè)額 from訂單by訂購(gòu)時(shí)間|S結(jié)果消息訂購(gòu)時(shí)間營(yíng)北額 £（ B4M1 j 201477/11 | 37100 hainraiHinramMiB

26、iBiHinMiHH'2 2014/7/12 736303 2014/71334007、（選做）完成數(shù)據(jù)庫(kù)的設(shè)計(jì)之后，根據(jù)自己所熟悉的編程語(yǔ)言（ C、C+、JAVA）,實(shí)現(xiàn)一個(gè)簡(jiǎn)單的程序，能夠在程序中組裝SELECT語(yǔ)句，鏈接創(chuàng)建的數(shù)據(jù)庫(kù)，進(jìn)行查詢，并顯示查詢結(jié)果。若完成，請(qǐng)?zhí)峁┰敿?xì)的代碼清單（代碼作為附件，附在本報(bào)告的最后）。用JSP頁(yè)面編寫 實(shí)現(xiàn)如下:Z business& JAX-WS Web Service?la Deployment Descriptor： busj 陋 Java Resourcesj 四 src一田 co im.yn j. my Business.(

27、|J BuildConnectionJ;|j- CcnnecticnBroker|jj ConnecticnFactor)咄 Libraries就 JavaScript Resources|> & build三J B WebContenti> 6 META-1NF0 & WEB-INF busines5.jspJ 聚 business充 JAX-WS Web Servicesil Deployment Descriptor： bus j / Java Resources/0 src& 出 com.ynu.my Busin ess.( |J| BuildCon

28、necticn.j5 j CcnnectionBroker |j ConnecticnFactor) W LibrariesBl JaviaScript Resources> & buildJ © WebContenti> B META-INF0 & WEB-INF . bj5ines5.jsp8、實(shí)驗(yàn)小結(jié)。在本次實(shí)驗(yàn)中一共建立了 9個(gè)表，完成了 6條Select語(yǔ)句，6條關(guān)于視圖、觸發(fā)器和存儲(chǔ)過程, 在語(yǔ)句實(shí)現(xiàn)過程中，因?yàn)樯婕皟?nèi)容比較廣，差不多把數(shù)據(jù)庫(kù)語(yǔ)句全部復(fù)習(xí)了一遍才開始做，所花的 時(shí)間較長(zhǎng)，但也收益頗豐，對(duì)數(shù)據(jù)庫(kù)的設(shè)計(jì)和實(shí)現(xiàn)也有了總體了解。最后在做

29、選做時(shí)，選擇了通過 網(wǎng)頁(yè)的方式實(shí)現(xiàn)，因?yàn)樵谧鰧I(yè)實(shí)訓(xùn)時(shí)也用了Java EE這項(xiàng)技術(shù)，所以實(shí)現(xiàn)起來頗為得心應(yīng)手。附件package com.ynu.myBusiness.db;import java.sql.Connection;import java.sql.SQLException;import java.sql.ResultSet;import java.sql.Statement;import com.devdaily.opensource.database.DDConnectionBroker;public class ConnectionBroker implements Build

30、Connection private String driver = null;private String url = null;private String username = null;private String password = null;private int minConnections = 0;private int maxConnections = 0;private long timeout = 0;private long leaseTime = 0；private String logFile = null;private DDConnectionBroker b

31、roker = null;void setUp() / 驅(qū)動(dòng)包driver = "com.microsoft.sqlserver.jdbc.SQLServerDriver"/ 連接到數(shù)據(jù)庫(kù)businessurl = "jdbc:sqlserver:/:1433;DatabaseName=business"username = "sa"password = "yym"minConnections = 3;maxConnections = 6;timeout = 100;leaseTime = 600

32、00;logFile = "C:/D/DDConnectionBroker.log"broker = null;/ 日志文件存儲(chǔ)位置public Connection getConnection() throws SQLException try / construct (建立)the broker (中間人)broker = new DDConnectionBroker(driver, url, username, password, minConnections, maxConnections, timeout, leaseTime, logFile);catch (S

33、QLException se) / could not get a broker; not much reason to go onSystem.out.println(se.getMessage();System.out.println("Could not construct a broker, quitting.");/ 建立 broker 成功return broker.getConnection();public void freeConnection(Connection conn) throws SQLException try broker.freeConn

34、ection(conn);catch (Exception e) System.out.println("Threw an exception trying to free my Connection: "+ e.getMessage();public int getNumberConnections() throws SQLException if (broker != null)return broker.getNumberConnections();elsereturn -1;public ConnectionBroker() super();setUp();<

35、% page language="java" contentType="text/html; charset=GB2312" pageEncoding="GB2312"%><%pageimport="com.ynu.myBusiness.db.*,java.sql.Connection,java.sql.PreparedStatement,java.s ql.ResultSet,java.sql.Statement" %><!DOCTYPE html PUBLIC "-/W3C/DTD HTML 4.01 Transitional/EN""/TR/html4/loose.dtd"><html><head><meta http-equiv="Content-Type" content=&qu