網(wǎng)上購物系統(tǒng)的數(shù)據(jù)庫設計

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

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

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

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

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

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

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

8、tomerNo 密碼password 姓名customerName類型號typeNo性別sex單位company電子信箱email身份證號IDVIP等級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會員表字段信息： 會員等級VIP會員折扣discountCreatetableVIP（VIPchar（1）primarykey,Discount floatNot Null)3. 創(chuàng)建商品表字段信息： 商品號productNo商品名稱productName類別號typeNo商品描述describe單位uints規(guī)格standard單價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)廠商表字段信息： 廠商號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、息： 類別號protypeNo類別名稱protypeNameCreatetable protype（protypeNochar(12)primany key,protypeNamevarchar(18)Not Null)6. 創(chuàng)建地址表字段信息：地址號addressNo地址名稱addressNameCrestetable address(addressNochar(12)primarykey,addressvarchar(20)Not Null)7. 創(chuàng)建訂單明細表字段信息： 訂單號orderNo客戶號customerNo商品號productNo收貨地址address訂貨日期orderdate

12、訂貨數(shù)量quantity單價price發(fā)票號碼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ā)票號碼billNo訂單號orderNoCreatetablebill（billNochar（12）primany key，orderNochar（12）N

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

15、 客戶號customerNo客戶類型typeCreatetabletype（CustomerNochar(12)primary key,Typevarchar(18)Not Null)11. 創(chuàng)建評論表字段信息： 評論號reviewNo客戶號customerNo商品號productNo客戶郵箱email評論內(nèi)容reviewContent評論時間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)建客戶地址表字段信息：客戶號customerNo客戶地址cusaddressCreatetablecusaddress(customerNochar(12)primary key,cusaddressvarchar(100)Not Null

17、)這里我們保證了實體完整性，參照完整性和用戶自定義完整性。在各個基本表的定義中，我們使主碼值均不為空，而且我們還進行了用戶自定義的完整性約束，使表中某些值取值不為空。四、物理結構設計4.1物理設計任務與目標為上一階段的關系模型選取一個最適合的應用要求的物理結構，即確定存取方法和存儲結構。4.2索引存取方法為了提高讓會員搜索商品的速度，對商品建立聚簇索引。create nonclustered index productNo on product五、數(shù)據(jù)庫的實施4.1數(shù)據(jù)庫的載入創(chuàng)建數(shù)據(jù)庫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ā)器的設計在修改商品價格的時候，新的商品價格不能超過原來價格的2倍，否則修改不成功，依此設計一個觸發(fā)器。觸發(fā)器設計如下：/*創(chuàng)建觸發(fā)器T1，實現(xiàn)注冊客戶VIP自動升級*/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)建一個觸發(fā)器，只允許注冊會員在網(wǎng)上提交訂單*/Create Trigger T2 on orderFor insertAsIf Not Exists(select * F

20、rom insertedWhere customerNo in(select customer.customerNo from customer)BeginRaiserror(提交訂單前請先注冊！，16,1)Rollback TransactionEnd/*創(chuàng)建一個觸發(fā)器T3，統(tǒng)計訂單金額*/*創(chuàng)建一個更新觸發(fā)器，及時更新訂單*/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