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

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

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

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

4、應(yīng)多個(gè)訂單；6) 一個(gè)訂單可以有一個(gè)商品配送單7) 一個(gè)倉庫可以存放多種商品，一種商品可以存放在一個(gè)倉庫；2、數(shù)據(jù)庫邏輯設(shè)計(jì)：用文字簡要描述實(shí)體之間的聯(lián)系，畫出 E-R圖（標(biāo)出各聯(lián)系中實(shí)體的基數(shù)）羯:號 dVar j kb 】£ cJmr勝彥 1: ar b (51S碼博址號-一士_-.三Vuiablt Ehurtcttri 5?."g*"iEB，tle, zlkars.ciers 1 15 'Nhi± Sx (11JYaz-iatle z.ha±Szt*Tfi .It ?ZkuractiTi (20)窖口黃號<pi>魯直卷

5、關(guān)汜尸?£:r-.Hr-二.一支訂.-Hr t .工"二 -：zDute & Tim=V&riable thu之 魚(5Float客戶-商品：n-n;商品-類別：n-1;商品-商店：n-n;客戶-訂單：1-n;訂單-商品：1-n;訂單-配送當(dāng)：1-1;倉庫-商品：1-n3、數(shù)據(jù)庫物理設(shè)計(jì)：將E-R圖轉(zhuǎn)換為數(shù)據(jù)表。需要給出詳細(xì)的轉(zhuǎn)換規(guī)則， 對應(yīng)生成的表，屬性（主 屬性、描述性屬性，多值屬性，符合屬性等） ，主鍵，外鍵，約束（取值是否 NULL等），索引（三種類型的索引至少每種出現(xiàn)一次）等。三一一 7«一三一5.-vurc ur i5)VBTGMT 1

6、5"1 ni3ie-?iE 11 DEd1ax 附eka±- 2 白宜岳符號金惠inteccT float宜* 號 vueur 10- 訂用材W iiatf t 二4 史利若式EFcJlU 斤三根春niznrriz 感里 floa-t在客戶，配送單，類別中建立索引,有店金鵬靖號v1mkacOJW 啊區(qū)號 vckar(5)語句在 4題中如3中，有2個(gè)n-n關(guān)系，分生成訂購表和銷售表4、 SQL 中創(chuàng)建語句的使用：根據(jù)第二步中的結(jié)果，將相應(yīng)的表，屬性，主鍵，外鍵，約束等使用標(biāo)準(zhǔn)的 SQL CREATE 語句實(shí)現(xiàn)。(要求給出創(chuàng)建的標(biāo)準(zhǔn)語句，以及創(chuàng)建之后在SQL 數(shù)據(jù)庫中的腳本和截

7、圖)。create table 客戶(客戶編號char(5) not null unique,姓名varchar(10),密碼varchar(15),電話numeric(11),地址varchar(20),卡號char(19),constraint PK_ 客戶 primary key (客戶編號);create table 類別(類別編號char(5) not null unique,類別名稱varchar(10),制造商varchar(10) ,constraint PK_ 類別 primary key (類別編號);create unique index 類別 _PK on 類別 (類別

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

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

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

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

12、 （訂單編號,商品編號）；0 口E 素統(tǒng)裴® n dba合面 十三 d be,訂單 0 口 dbm訂購 + n d bo,客戶 國 dbd蝴 + 3 dba配送單 ® J db。商店 工_ dBd商品 Edb0檔售<I ->> ?! JtiI5、存儲過程、觸發(fā)器和視圖：根據(jù)需要給數(shù)據(jù)庫添加至少六個(gè)實(shí)用的存儲過程、觸發(fā)器和視圖,并說明它們各自的功能。（需要給出語句執(zhí)行的結(jié)果示意圖）創(chuàng)建視圖，查找商品名為蘋果的商品create view find_goods asselect商品編號，商品名稱，價(jià)格from商品where商品名稱like '蘋果'

13、; create view finci_oad3百國selecl: 商品編號f商品名稱,僑格 fxorr,商品Lwhem商品名稱，蘋果為1|_j消息命令已成功完而日二J視圜國 Lj MMH- I dbo.find.goods日U列ijl 商品（char（5）, not nullH 商界名稱（varcharfSOl null） 囿價(jià)格田口或null）(2)創(chuàng)建視圖，查找價(jià)格在3000-6000的商品create view price as select商品編號，商品名稱 from商品 where 價(jià)格 >3000 and 價(jià)格 <6000日 create view price商品編號

14、.商品名稱工匚口皿商品Lwiiere 價(jià)格3000 -:.：!價(jià)格-.60。命叁已成功完成，,dbc.prKe日口列國 商品編號(ehar(5X net null)S商品名稱 tvarcbsn(50)F null)(3)創(chuàng)建觸發(fā)器，提醒店鋪中庫存量小于10的商品create trigger trigger_alarmon銷售after updateasif(select 庫存量 from 銷售 where 庫存量 <10)<10print 'trigger out:'select商品編號，庫存量from 銷售 where庫存量<10 returnSQLQuur

15、y3，§ql -(y號ny口 n'pc ($&)* BM、-觸發(fā)露與ql -未連播 SQLQu” if t3型Im三/庫存量 fxorn 鞘售.庫存量二工口 <10-rtinr 1 trigger otic 1商品編號.庫存量上工所 銷售庫存量L recurn_J|結(jié)果第令已成功竟成。B update"亡庫存量安-wh.ere 商品編號i 與口匚，口二口緒果直不息就品編記庫石里1 i sWQI 2(4)創(chuàng)建觸發(fā)器，當(dāng)刪除配送單中的數(shù)據(jù)時(shí)顯示出所刪信息create trigger trigger_delon配送單after deleteasprint &

16、#39;delete'SQLQuery4.sql - YAs (yanyanpu (54)* SC- create trigger trig-gerelI m配送單口 £匚:工委工個(gè)喧33 print 'delere1- select * frern de Letdl命令已成功完成E delete配送單1-where 查詢號f £0013，IS3結(jié)果島j肖息一直回號一訂單編號配送人聯(lián)系電話1 I f001C -i D0011 張信 1312B373B32、! r nur(5)創(chuàng)建存儲過程，根據(jù)商品編號，查詢該商品的訂購量create proc sp_find

17、_quantity商品編號char(5),sumq int outputasselect商品編號，sum(數(shù)量)from訂購group by商品編號having商品編號=商品編號declare sumq intexec sp_find_quantity商品編號='s0001',sumq=sumq outputprint 'the sum quantity is:'+convert(varchar(5),sumqAM.business - dhci,號品 EQLQueryAfiql日 deGlare 33nMq Inz百 exec sp find Quantity

18、 國商品編號-Mgll- ?與uirq=gmuir.q outputa print 1 the sum quantity is:1 +L convert tvarchai Si f Sbljt.q3給果I青而商品墻號/列名)- 311 lllli Mil 一“ 41ll，|1 j 3OT01I _ j 4(6)創(chuàng)建存儲過程，通過商品名稱尋找商品信息create proc sp_find_pricename char(10)as select * from商品 where 商品名稱 like name + '% exec sp_find_price name='戴爾阿品褊罵倉庫堀

19、號類別洞號商品名郡口而丁二!戴爾(DELL) hs14L252SBs'ociSia 000310003戴爾(DELL) lns15BR-1WSsOOPaOOT3IM03戴爾(DELL hs14U1523RRJ籍果/葫息價(jià)格MM 5990 +1006、分析常見的業(yè)務(wù)流程，列舉出至少五種SQL SELECT語句。實(shí)現(xiàn)的語句要滿足如下要求:a) 在五種語句中的查詢能反映正常的業(yè)務(wù)需求；b) 分析中至少要分別出現(xiàn)一次ORDER BY、GROUP BYHAVING 子句;c) 分析中至少使用一次聚集函數(shù)；d) 分析中至少使用一次嵌套查詢；e) 分析中至少使用一次 UNION或INTERSECT運(yùn)

20、算；給出每一種語句執(zhí)行的結(jié)果。(1)計(jì)算并查找訂購單中相同訂單訂購超過5000的總額select訂單編號，sum(金額)as總額from訂購 group by訂單編號 having sum(金額)>5000S-QLQueryl sql V/O (yanyanpc (54)”訂單編號金額as總額工口m訂購roup by訂單編號-3中工ng金額 -5DOO|結(jié)果D消息訂單墉號總的1! 0WD100002! 21 1220023(NXID422SOT4(N006SW0SODODS730060001021700(2)查找即購買了商品編號為S0001又買了 S0002的客戶select姓名from

21、客戶b,訂單o,訂購swhere s.商品編號='s0001' and b.客戶編號=o.客戶編號 and。.訂單編號=s.訂單編號intersectselect姓名from客戶b,訂單o,訂購swhere s.商品編號='s0002' and b.客戶編號=o.客戶編號 and。.訂單編號=s.訂單編號£QLQueryl.£ql - YA.S (yanyanpc (54)*0 select 姓名IE工皿 客戶已,訂單 露訂購"where s.商品編號=20門口1，a" b.客戶編號”.客戶編號andg.訂單編號=3.訂單

22、編號intersectSelect姓名fmm客戶端訂單明訂購3where 小商品編號=港?？?1一a b.客戶編號=口.客戶編號and 0.訂單編號七.訂單編號(3)按照顧客c0002消費(fèi)的總額大小順序排訂單編號 select訂單編號，總額 from訂單where 客戶編號='c0001'order by 總額 descVAN/AN.business - dbO.訂單 SQLQueryl| 章童冢ftE訂單where 客戶編號='E(M)L-order toy，總，額 desc也結(jié)果上消息一衛(wèi)里隔號總額1；D0OO41228004ii m nii-Miiiiiiia!

23、mri2DOOQ21220D30037800d000113400(4)查詢訂單編號為 00003的配送信息select。.訂單編號，配送人，聯(lián)系電話 from 訂單 o,配送單 s where。.訂單編號=s.訂單編號 and s訂單編號='00003'YANVAN.business - dbd訂單 SQLQutryXfql - YA-J (yaHyaHpe (54)*一£1二D二訂單裝百曲送入F聯(lián)系電話from訂單口，配送單a-where ci.訂單編號道.訂單編號and 4訂單編號二，0。口 口丁rri查找同樣買了商品編號為s0002的客戶信息select客戶編號

24、，姓名，電話 from客戶 where客戶編號 in (select客戶編號where訂單.訂單編號=訂購.訂單編號 and訂購.商品編號='s0002'）（6）查詢當(dāng)日銷售總額select訂購時(shí)間，sum（總額）as營業(yè)額from訂單group by訂購時(shí)間臼亨789訂購時(shí)間口皿1總額 營業(yè)額 from訂單-iroup by訂購時(shí)間|fnr3結(jié)果11消息訂解宿營it頡 "ilia-im-iHiiafUHiii ni«4i|12014/7/11J371002201477/12-7363032014/71334007、（選做）完成數(shù)據(jù)庫的設(shè)計(jì)之后，根據(jù)自己所熟

25、悉的編程語言（ C、C+、JAVA）,實(shí)現(xiàn)一個(gè)簡單的程序，能夠在程序中組裝SELECT語句，鏈接創(chuàng)建的數(shù)據(jù)庫，進(jìn)行查詢，并顯示查詢結(jié)果。若完成，請?zhí)峁┰敿?xì)的代碼清單（代碼作為附件，附在本報(bào)告的最后） 用JSP頁面編寫 實(shí)現(xiàn)如下:. 27 business> & JAX-WS Web Services由 Deployment Desqriptpr; bu5 j 急 Java Resources中 arcj t iom.ynG.myBusin&ss.; 團(tuán) BuildConnertionje | J | Connection Bro leer J Conn e-ctionFa

26、ctory 片.LibrariesJavaScript Resource? & buildj 2 WebContent0 M ETA-INF£ WEB-LNF, busine5s.jsp/4 business及 JAX-WS Web Services血 De-ployment Descriptor; bu$學(xué) Java Resources港srej 出 土匕而，yHU，myButiri«d Ti BjildConnectionjE |j Conn e ctionBroke-r p Coinne-ctionFactori> Libraries瞰 JavaScri

27、pt Resources:> £> build已 Web Contentl> B META-INFb WEBrCNF卜 buine3sjsp8、實(shí)驗(yàn)小結(jié)。在本次實(shí)驗(yàn)中一共建立了 9個(gè)表，完成了 6條Select語句，6條關(guān)于視圖、觸發(fā)器和存儲過程, 在語句實(shí)現(xiàn)過程中，因?yàn)樯婕皟?nèi)容比較廣，差不多把數(shù)據(jù)庫語句全部復(fù)習(xí)了一遍才開始做，所花的 時(shí)間較長，但也收益頗豐，對數(shù)據(jù)庫的設(shè)計(jì)和實(shí)現(xiàn)也有了總體了解。最后在做選做時(shí)，選擇了通過 網(wǎng)頁的方式實(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

28、.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 BuildConnection private String driver = null;private String url = null;private String us

29、ername = 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 broker = null;void setUp() / 驅(qū)動包driver = "com.microsoft.sqlserver.jdbc.SQLServe

30、rDriver"/ 連接到數(shù)據(jù)庫businessurl = "jdbc:sqlserver:/:1433;DatabaseName=business"username = "sa"password = "yym"minConnections = 3;maxConnections = 6;timeout = 100;leaseTime = 60000;/ 日志文件存儲位置logFile = "C:/D/DDConnectionBroker.log"broker = null;public

31、Connection getConnection() throws SQLException try / construct (建立)the broker (中間人)broker = new DDConnectionBroker(driver, url, username, password, minConnections, maxConnections, timeout, leaseTime, logFile);catch (SQLException se) / could not get a broker; not much reason to go onSystem.out.printl

32、n(se.getMessage();System.out.println("Could not construct a broker, quitting.");/ 建立 broker 成功return broker.getConnection();public void freeConnection(Connection conn) throws SQLException try broker.freeConnection(conn);catch (Exception e) System.out.println("Threw an exception trying

33、 to free my Connection: "+ e.getMessage();public int getNumberConnections() throws SQLException if (broker != null)return broker.getNumberConnections();elsereturn -1;public ConnectionBroker() super();setUp();<% page language="java" contentType="text/html; charset=GB2312"

34、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="text/html; charset=UTF-8">&l