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

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

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

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

4、應多個訂單；6) 一個訂單可以有一個商品配送單7) 一個倉庫可以存放多種商品，一種商品可以存放在一個倉庫；皂運istz. 姓府一 SM客戶弱號8C.5疣空號 5一 心式二七人LEhtnE0工心茗苫茬；：6 /=二nu工: E，餐子運紀VttTL±bl ”hkKi*+7 ,.10）商苫三仁心x>萍松°2、數(shù)據(jù)庫邏輯設計：用文字簡要描述實體之間的聯(lián)系，畫出 E-R圖（標出各聯(lián)系中實體的基數(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;訂單-配送當：1-1;倉庫-商品：1-n3、數(shù)據(jù)庫物理設計：將E-R圖轉(zhuǎn)換為數(shù)據(jù)表。需要給出詳細的轉(zhuǎn)換規(guī)則，對應生成的表，屬性（主屬性、描述性屬性，多值屬性，符合屬性等）,主鍵，外鍵，約束（取值是否NULL等），索引（三種類型的索引至少每種出現(xiàn)一次）等。4A二工工'RicCl 1)vu ；hu '' 改二-吟三;_* *!西電地中我帛老可7ir:k*r(lC "洋壹 vir=larilO：.套.吉支古號B二皿；袁

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

7、數(shù)據(jù)庫中的腳本和截圖)。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

8、on 類別 (類別編號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),價格 float,constraint PK_ 商品 primary key (商品編號),constraint fk_ 倉庫編號 foreign key(倉庫編號 )ref

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

10、ferences 客戶);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);creat

11、e table 商店(商店號 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_訂購 pr

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

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

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

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

16、BI iui bikimI(4)創(chuàng)建觸發(fā)器，當刪除配送單中的數(shù)據(jù)時顯示出所刪信息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查徇號=，工810，盅結(jié)果育福查詢號訂單編號配送人聯(lián)系電話1 IfOoid

17、1 00011 泰信ibikiibiuibiuibiuibI(5)創(chuàng)建存儲過程，根據(jù)商品編號，查詢該商品的訂購量create proc sp_find_quantity商品編號char(5),sumq int outputasselect商品編號，sum(數(shù)量)from訂購group by商品編號having商品編號=商品編號 declare sumq intexec sp_find_quantity 商品編號='s0001',sumq=sumq output print 'the sum quantity is:'+ convert(

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

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

20、聚集函數(shù)；d) 分析中至少使用一次嵌套查詢；e) 分析中至少使用一次 UNION或INTERSECT運算；給出每一種語句執(zhí)行的結(jié)果。(1)計算并查找訂購單中相同訂單訂購超過5000的總額select訂單編號，sum(金額)as總額from訂購group by訂單編號 having sum(金額)>5000SQLQueryl.sql - YA.S (yanyanpc (54)* elecc訂單編號”uml金額且白豆凝mm訂購roup by 訂單編號L aving sw 金頷> >5。|nr口結(jié)果：消息 訂單編號總額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)查找即購買了商品編號為S0001又買了 S0002的客戶select姓名from客戶b,訂單o,訂購swhere s.商品編號='s0001' and b.客戶編號=o.客戶編號 and。.訂單編號=s.訂單編號 intersect select姓名 from客戶b,訂單o,訂購s where s.商品編號='s0002' and b.客戶編號=o.客戶

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

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

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

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

26、iBiHinMiHH'2 2014/7/12 736303 2014/71334007、（選做）完成數(shù)據(jù)庫的設計之后，根據(jù)自己所熟悉的編程語言（ C、C+、JAVA）,實現(xiàn)一個簡單的程序，能夠在程序中組裝SELECT語句，鏈接創(chuàng)建的數(shù)據(jù)庫，進行查詢，并顯示查詢結(jié)果。若完成，請?zhí)峁┰敿毜拇a清單（代碼作為附件，附在本報告的最后）。用JSP頁面編寫 實現(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、實驗小結(jié)。在本次實驗中一共建立了 9個表，完成了 6條Select語句，6條關于視圖、觸發(fā)器和存儲過程, 在語句實現(xiàn)過程中，因為涉及內(nèi)容比較廣，差不多把數(shù)據(jù)庫語句全部復習了一遍才開始做，所花的 時間較長，但也收益頗豐，對數(shù)據(jù)庫的設計和實現(xiàn)也有了總體了解。最后在做

29、選做時，選擇了通過 網(wǎng)頁的方式實現(xiàn)，因為在做專業(yè)實訓時也用了Java EE這項技術，所以實現(xiàn)起來頗為得心應手。附件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ū)動包driver = "com.microsoft.sqlserver.jdbc.SQLServerDriver"/ 連接到數(shù)據(jù)庫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;/ 日志文件存儲位置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