網(wǎng)上購(gòu)物系統(tǒng)的數(shù)據(jù)庫(kù)設(shè)計(jì)

1、數(shù)據(jù)庫(kù)系統(tǒng)概論課程設(shè)計(jì)網(wǎng)上購(gòu)物系統(tǒng)的數(shù)據(jù)庫(kù)設(shè)計(jì)學(xué)院（系）： 專業(yè): 學(xué)號(hào)：姓名： 指導(dǎo)老師： 2012年6月23日目錄一、 需求分析1.1功能需求及數(shù)據(jù)需求分析-21.2業(yè)務(wù)規(guī)則分析-21.3業(yè)務(wù)需求及處理流程-2二、 概念結(jié)構(gòu)設(shè)計(jì)2.1各子系統(tǒng)的局部E-R-32.2視圖的集成-6三、 邏輯結(jié)構(gòu)設(shè)計(jì)3.1 E-R圖向關(guān)系模型的轉(zhuǎn)換-73.2數(shù)據(jù)模型的優(yōu)化-83.3數(shù)據(jù)庫(kù)的結(jié)構(gòu)-8四、 物理結(jié)構(gòu)設(shè)計(jì)4.1物理設(shè)計(jì)任務(wù)與目標(biāo)-124.2索引存取方法-12五、 數(shù)據(jù)庫(kù)的實(shí)施4.1數(shù)據(jù)庫(kù)的載入-12六、 總結(jié)6.1課程設(shè)計(jì)所負(fù)責(zé)部分-146.2課程設(shè)計(jì)的學(xué)習(xí)心得-14一、需求分析1.1功能需求及數(shù)據(jù)需

2、求分析用戶管理 用戶：只允許瀏覽商品信息，可以注冊(cè)為普通用戶 會(huì)員：擁有瀏覽商品和購(gòu)買商品的權(quán)限，其屬性包括客戶號(hào)（唯一）、客戶名、E-Mail、密碼、姓名、性別、郵政編碼、地址（一個(gè)客戶可有幾個(gè)地址）、客戶所屬VIP級(jí)別、折扣優(yōu)惠。 商品管理商品的增加。其中的屬性包含商品號(hào)（唯一）、商品分類、生產(chǎn)廠商、每個(gè)廠商的實(shí)際存貨量、規(guī)定的最低存貨量和商品其它描述商品的查詢，在只要輸入商品的任一屬性即可商品訂購(gòu)管理注冊(cè)用戶即會(huì)員注冊(cè)后可以將相關(guān)商品放入購(gòu)物車，最后購(gòu)物結(jié)束之后形成生成訂單，其中每個(gè)訂單屬性包含訂單號(hào)、客戶號(hào)、收貨地址、訂單日期、訂單金額、訂單明細(xì)（每個(gè)訂單都有幾個(gè)明細(xì)）內(nèi)容為商品號(hào)、單

3、價(jià)、訂貨數(shù)量。配送單管理默認(rèn)屬性為客戶注冊(cè)時(shí)的基本信息，當(dāng)然配送地址可由客戶修改為合適的收貨地址，支付方式也可根據(jù)提示由客戶自定。評(píng)論管理客戶可以給商品發(fā)表評(píng)論，相關(guān)屬性為評(píng)論號(hào)、客戶號(hào)、商品號(hào)、客戶郵箱、評(píng)論內(nèi)容、評(píng)論時(shí)間。1.2業(yè)務(wù)規(guī)則分析1、 所用用戶都有權(quán)限瀏覽商品信息，但只有注冊(cè)用戶才能訂購(gòu)商品2、 每位注冊(cè)用戶的編號(hào)都是唯一的。3、 當(dāng)普通客戶總的訂單金額達(dá)到10000元，即可升級(jí)為VIP客戶。VIP客戶一般分為三個(gè)等級(jí)，對(duì)他們的優(yōu)惠策略是在普通客戶的價(jià)格上同時(shí)享有折扣優(yōu)惠。其中。三級(jí)會(huì)員，訂單金額在10000，15000）元，享受折扣9折；二級(jí)會(huì)員，訂單金額在15000，2500

4、0）元，享受折扣8.5折；一級(jí)會(huì)員，訂單金額在25000及以上，享受折扣7.5折。4、 每次商品的訂單號(hào)都是唯一的。5、 客戶可以在前臺(tái)頁(yè)面查看訂單狀態(tài)，訂單狀態(tài)可以是“0”或“1”，“1”表示訂單上的商品已發(fā)出， “0”表示訂單上的商品未發(fā)出。訂單的生成需用戶確定之后才能生效。1.3業(yè)務(wù)需求及處理流程 網(wǎng)上購(gòu)物系統(tǒng)主要業(yè)務(wù)包括：商品信息的發(fā)布與查詢，商品的訂購(gòu)，處理訂單，商品的配送。系統(tǒng)處理流程：系統(tǒng)流程圖二、 概念結(jié)構(gòu)設(shè)計(jì)2.1各子系統(tǒng)的局部E-R如下：圖：客戶實(shí)體圖：VIP客戶圖：商品實(shí)體圖：生產(chǎn)廠商實(shí)體圖：商品分類圖：地址實(shí)體圖：訂單明細(xì)圖：發(fā)票圖：供應(yīng)關(guān)系圖：商品分類關(guān)系圖：客戶評(píng)論

5、關(guān)系2.2視圖的集成方式視圖的集成通俗的理解就是把各個(gè)子E-R圖集成合并。視圖集成可以有兩種： 1)多個(gè)分E-R圖一次集成；2)逐步集成，用累加的方式一次集成兩個(gè)分E-R圖。第一種方法比較復(fù)雜，做起來(lái)難度較大；第二種方法每次只集成兩個(gè)分E-R圖，可以降低復(fù)雜度。無(wú)論采用哪種方式，每次集成局部E-R圖時(shí)都需要分兩步走：1)合并。解決各分E-R圖合并起來(lái)生成初步E-R圖；2)修改和重構(gòu)。消除不必要的冗余，生成基本E-R圖。冗余數(shù)據(jù)和冗余聯(lián)系容易破壞數(shù)據(jù)庫(kù)的完整性，給數(shù)據(jù)庫(kù)維護(hù)增加困難，應(yīng)當(dāng)予以消除。消除了冗余后的初步E-R圖稱為基本E-R圖。消除冗余主要采用的方法有：1)用分析方法消除冗余；2)用

6、規(guī)范化理論消除冗余。對(duì)該網(wǎng)上購(gòu)物系統(tǒng)各子E-R圖的集成即該網(wǎng)上購(gòu)物系統(tǒng)的總體概念結(jié)構(gòu)E-R圖如下圖所示：三、 邏輯結(jié)構(gòu)設(shè)計(jì)3.1 E-R圖向關(guān)系模型的轉(zhuǎn)換由于概念設(shè)計(jì)的結(jié)果是ER圖，DBMS一般采用關(guān)系模型，因此數(shù)據(jù)庫(kù)的邏輯設(shè)計(jì)過(guò)程就是把E-R圖轉(zhuǎn)化為關(guān)系模式的過(guò)程。將該網(wǎng)上購(gòu)物系統(tǒng)的總體概念結(jié)構(gòu)E-R圖轉(zhuǎn)換為關(guān)系模型：客戶（客戶號(hào)、密碼、姓名、性別、單位、電子信箱、身份證號(hào)）VIP會(huì)員表（會(huì)員等級(jí)、會(huì)員折扣）商品（商品號(hào)、商品名稱、類別號(hào)、商品描述、單位、規(guī)格、單價(jià)）生產(chǎn)廠商表（廠商號(hào)，生產(chǎn)廠商名、地址、電話 ）地址（地址號(hào)、地址名稱）訂單（訂單號(hào)、客戶號(hào)、商品號(hào)、收貨地址、訂貨日期、訂貨數(shù)

7、量、單價(jià)、訂單金額、發(fā)貨日期、訂單狀態(tài)）發(fā)票（發(fā)票號(hào)碼、訂單號(hào)）供應(yīng)（商品號(hào)、廠商號(hào)）商品分類（商品類別號(hào)、類別名稱）客戶類型（客戶號(hào)、客戶類型）評(píng)論（評(píng)論號(hào)、客戶號(hào)、商品號(hào)、客戶郵箱、評(píng)論內(nèi)容、評(píng)論時(shí)間）客戶地址表（客戶號(hào)、客戶地址）視圖的設(shè)計(jì)為會(huì)員查看商品建立視圖：3.2數(shù)據(jù)模型的優(yōu)化數(shù)據(jù)模數(shù)據(jù)模型的優(yōu)化是為了提高數(shù)據(jù)庫(kù)應(yīng)用系統(tǒng)的性能，根據(jù)應(yīng)用需要適當(dāng)?shù)匦薷?、調(diào)整關(guān)系模式。將轉(zhuǎn)化的關(guān)系模式進(jìn)行優(yōu)化，最終達(dá)到第二范式。由以上關(guān)系可看出，以上表都以消除了非主屬性對(duì)候選碼的部分函數(shù)依賴，這個(gè)關(guān)系模型都已經(jīng)達(dá)到第二范式，所以不需要進(jìn)行優(yōu)化。3.3數(shù)據(jù)庫(kù)的結(jié)構(gòu)1. 創(chuàng)建客戶表字段信息： 客戶號(hào)cus

8、tomerNo 密碼password 姓名customerName類型號(hào)typeNo性別sex單位company電子信箱email身份證號(hào)IDVIP等級(jí)VIPCreate table customer（customerNochar(12)primarykey,passwordvarchar(18)NotNull，customerNamevarchar（12）NotNull，typeNochar（8）NotNull，sexchar（1）Check(sex In(M，F(xiàn)) )，companyvarchar（20），emailvarchar（50）NotNull，IDvarchar(18)NotNu

9、ll,VIPchar(1)2. 創(chuàng)建VIP會(huì)員表字段信息： 會(huì)員等級(jí)VIP會(huì)員折扣discountCreatetableVIP（VIPchar（1）primarykey,Discount floatNot Null)3. 創(chuàng)建商品表字段信息： 商品號(hào)productNo商品名稱productName類別號(hào)typeNo商品描述describe單位uints規(guī)格standard單價(jià)priceCreatetableproduct（ProductNochar（12）primary key，ProductNamevarchar（18）NotNull,typeNochar(12)NotNull,descri

10、bevarchar(50) NotNull,unitsvarchar(20)Not Null,standardchar(8),pricenumericNot Null)4. 創(chuàng)建生產(chǎn)廠商表字段信息： 廠商號(hào)producerNo廠商名producerName地址address電話telephoneCreatetable producer（producerNochar（12）primary key,producerName varchar(18)Not Null,addressvarchar(50)NotNull,telephonevarchar(12)Not Null)5. 創(chuàng)建商品類別表字段信

11、息： 類別號(hào)protypeNo類別名稱protypeNameCreatetable protype（protypeNochar(12)primany key,protypeNamevarchar(18)Not Null)6. 創(chuàng)建地址表字段信息：地址號(hào)addressNo地址名稱addressNameCrestetable address(addressNochar(12)primarykey,addressvarchar(20)Not Null)7. 創(chuàng)建訂單明細(xì)表字段信息： 訂單號(hào)orderNo客戶號(hào)customerNo商品號(hào)productNo收貨地址address訂貨日期orderdate

12、訂貨數(shù)量quantity單價(jià)price發(fā)票號(hào)碼billNo訂單金額ordersum發(fā)貨日期Fhdate訂單狀態(tài)orderstateCreatetable ordermaster(orderNochar(12)primary key,customerNochar(12)Not Null,productNochar(12)NotNull,addressvarchar(20)Not Null,orderdatedatetimeNot Null,quantitychar(4)Not Null,pricenumericNot Null,billNovarchar(12)Not Null,Fhdateda

13、tetimeNot Null,Orderstatechar（8）Check（orderstate IN（0，1），foreign key(customerNo) references customer(customerNo)foreign key(productNo) references product(productNo)，foreign key(billNo) references bill(billNo)8. 創(chuàng)建發(fā)票表字段信息： 發(fā)票號(hào)碼billNo訂單號(hào)orderNoCreatetablebill（billNochar（12）primany key，orderNochar（12）N

14、ot Null，foreign key(orderNo) references product(orderNoNo) )9. 創(chuàng)建供應(yīng)表字段信息： 商品號(hào)productNo廠商號(hào)producerNoCreatetablesupport（productNochar（12）primany key，producerNochar（12）NotNull,foreign key(producerNo) references producer(producerNo)，foreign key(productNo) references product(productNo) )10. 創(chuàng)建客戶類型表：字段信息：

15、 客戶號(hào)customerNo客戶類型typeCreatetabletype（CustomerNochar(12)primary key,Typevarchar(18)Not Null)11. 創(chuàng)建評(píng)論表字段信息： 評(píng)論號(hào)reviewNo客戶號(hào)customerNo商品號(hào)productNo客戶郵箱email評(píng)論內(nèi)容reviewContent評(píng)論時(shí)間reviewdateCreatetablereview(reviewNochar(12)primany key,customerNochar(12)Not Null,produtNochar(12)Not Null,emailvarchar(50)Not

16、 Null,reviewContentvarchar(100)Not Null,reviewdatedatetimeNot Null,foreign key(customerNo) references customer(customerNo),foreign key(productNo) references product(productNo)12. 創(chuàng)建客戶地址表字段信息：客戶號(hào)customerNo客戶地址cusaddressCreatetablecusaddress(customerNochar(12)primary key,cusaddressvarchar(100)Not Null

17、)這里我們保證了實(shí)體完整性，參照完整性和用戶自定義完整性。在各個(gè)基本表的定義中，我們使主碼值均不為空，而且我們還進(jìn)行了用戶自定義的完整性約束，使表中某些值取值不為空。四、物理結(jié)構(gòu)設(shè)計(jì)4.1物理設(shè)計(jì)任務(wù)與目標(biāo)為上一階段的關(guān)系模型選取一個(gè)最適合的應(yīng)用要求的物理結(jié)構(gòu)，即確定存取方法和存儲(chǔ)結(jié)構(gòu)。4.2索引存取方法為了提高讓會(huì)員搜索商品的速度，對(duì)商品建立聚簇索引。create nonclustered index productNo on product五、數(shù)據(jù)庫(kù)的實(shí)施4.1數(shù)據(jù)庫(kù)的載入創(chuàng)建數(shù)據(jù)庫(kù)shoponlinecreate database shoponlineon(Name=shopo

18、nline, Filename=f:SQLshoponline.mdf, Size=2, Maxsize=20, Filegrowth=1)Log on(name=shoponlinelog, Filename=f；SQLshoponlinelog.ldf, Size=2, Maxsize=5, Filegrowth=1)觸發(fā)器的設(shè)計(jì)在修改商品價(jià)格的時(shí)候，新的商品價(jià)格不能超過(guò)原來(lái)價(jià)格的2倍，否則修改不成功，依此設(shè)計(jì)一個(gè)觸發(fā)器。觸發(fā)器設(shè)計(jì)如下：/*創(chuàng)建觸發(fā)器T1，實(shí)現(xiàn)注冊(cè)客戶VIP自動(dòng)升級(jí)*/Create Trigger T1 ON customerFor updateAsIF Update（o

19、rdersum）BeginDeclare price As numericSelect price=ordersum From insertedIf price>=30000Update customer set VIP=3ElseIf price>=20000Update customer set VIP=2ElseIf price>=10000Update customer set VIP=1End/*創(chuàng)建一個(gè)觸發(fā)器，只允許注冊(cè)會(huì)員在網(wǎng)上提交訂單*/Create Trigger T2 on orderFor insertAsIf Not Exists(select * F

20、rom insertedWhere customerNo in(select customer.customerNo from customer)BeginRaiserror(提交訂單前請(qǐng)先注冊(cè)！，16,1)Rollback TransactionEnd/*創(chuàng)建一個(gè)觸發(fā)器T3，統(tǒng)計(jì)訂單金額*/*創(chuàng)建一個(gè)更新觸發(fā)器，及時(shí)更新訂單*/Create Trigger updatesaleitem On order For Update AsIf Update (quantity) Or Update (price)Begin Declare orderNo int,productNo char(12)

21、Declare cur_order Cursor ForSelect orderNo，productNo From DeletedOpen cur_orderBegin Transaction Fetch cur_order into orderNo,productNoWhile (fetch_status=0)Begin Update orderSet ordersum=ordersum-D.quantity*D.price+I.quantity*I.priceFrom inserted I,deleted DWhere order.orderNo=I.orderNo And I.orderNo=D.orderNoAnd order.orderNo=orderNo And I.productNo=D.productNoAnd I.pro