數(shù)據(jù)庫方案設(shè)計書：網(wǎng)上購物系統(tǒng)(初稿).docx

1、數(shù)據(jù)庫系統(tǒng)概論課程設(shè)計 網(wǎng)上購物系統(tǒng)的數(shù)據(jù)庫設(shè)計2012/6/14一、需求分析1.1 功能需求及數(shù)據(jù)需求分析1.1.1 用戶管理用戶：只允許瀏覽商品信息，可以注冊為普通用戶會員：擁有瀏覽商品和購買商品的權(quán)限，其屬性包括客戶號（唯一）、客戶名、 E-Mail 、密碼、姓名、性別、郵政編碼、地址（一個客戶可有幾個地址）、客戶所屬 VIP 級別、折扣優(yōu)惠。1.1.2 商品管理商品的增加。其中的屬性包含商品號（唯一）、商品分類、生產(chǎn)廠商、每個廠商的實際存貨量、規(guī)定的最低存貨量和商品其它描述商品的查詢，在只要輸入商品的任一屬性即可1.1.3 商品訂購管理注冊用戶即會員注冊后可以將相關(guān)商品放入購物車，最后

2、購物結(jié)束之后形成生成訂單，其中每個訂單屬性包含訂單號、客戶號、收貨地址、訂單日期、訂單金額、訂單明細（每個訂單都有幾個明細）內(nèi)容為商品號、單價、訂貨數(shù)量。 1.1.4 配送單管理默認屬性為客戶注冊時的基本信息，當然配送地址可由客戶修改為合適的收貨地址，支付方式也可根據(jù)提示由客戶自定。1.1.5 評論管理客戶可以給商品發(fā)表評論，相關(guān)屬性為評論號、客戶號、商品號、客戶郵箱、評論內(nèi)容、評論時間。1.2 業(yè)務(wù)規(guī)則分析1、所用用戶都有權(quán)限瀏覽商品信息，但只有注冊用戶才能訂購商品2、每位注冊用戶的編號都是唯一的。3、當普通客戶總的訂單金額達到 10000 元，即可升級為 VIP 客戶。 VIP 客戶一般分

3、為三個等級，對他們的優(yōu)惠策略是在普通客戶的價格上同時享有折扣優(yōu)惠。其中。三級會員，訂單金額在 10000，15000）元，享受折扣 9 折；二級會員，訂單金額在 15000， 25000）元，享受折扣 8.5 折；一級會員，訂單金額在 25000 及以上，享受折扣 7.5 折。4、每次商品的訂單號都是唯一的。5、客戶可以在前臺頁面查看訂單狀態(tài)，訂單狀態(tài)可以是 “ 0或”“ 1，”“ 1表”示訂單上的商品已發(fā)出， “0表”示訂單上的商品未發(fā)出。訂單的生成需用戶確定之后才能生效。1.3 業(yè)務(wù)需求及處理流程網(wǎng)上購物系統(tǒng)主要業(yè)務(wù)包括：商品信息的發(fā)布與查詢，商品的訂購，處理訂單，商品的配送。系統(tǒng)處理流程

4、：用戶注冊注冊用戶登錄選擇商品選購結(jié)束填寫個人信息與支付提交訂單訂單生成開始商家訂單處理審核信息通過生成訂單訂單有誤編號結(jié)束系統(tǒng)流程圖二、 概念結(jié)構(gòu)設(shè)計2.1 各子系統(tǒng)的局部E-R 如下：密碼客戶號姓名客戶密碼密碼密碼密碼圖 2.1.1：客戶實體會員等級會員折扣VIP會員表圖 2.1.2：VIP 客戶商品號商品號商品號商品商品號商品號商品號商品號圖 2.1.3：商品實體廠商號廠商名生產(chǎn)廠商地址電話圖 2.1.4：生產(chǎn)廠商實體類別號類別名稱商品類別圖 2.1.5：商品分類地址號地址名稱地址圖 2.1.6：地址實體訂單號客戶號發(fā)貨日期商品號訂單金額訂單收貨地址單價訂貨日期訂貨數(shù)量圖 2.1.7：訂

5、單明細發(fā)票號碼訂單號發(fā)票圖 2.1.8：發(fā)票生產(chǎn)廠商廠商號供應(yīng)商品號商品圖 2.1.9：供應(yīng)關(guān)系商品分類類別名稱商品類別商品類別號圖 2.1.10：商品分類關(guān)系客戶評論號客戶郵箱客戶號評論評論內(nèi)容商品號評論時間商品圖 2.1.11：客戶評論關(guān)系2.2 視圖的集成地址n客戶類型所在訂單11mn屬于n客戶商品m 供應(yīng)n 生產(chǎn)廠商nnmn評論屬于分類11VIP會員商品類別三、 邏輯結(jié)構(gòu)設(shè)計3.1 E-R 圖向關(guān)系模型的轉(zhuǎn)換由于概念設(shè)計的結(jié)果是 ER 圖， DBMS 一般采用關(guān)系模型，因此數(shù)據(jù)庫的邏輯設(shè)計過程就是把 E-R 圖轉(zhuǎn)化為關(guān)系模式的過程。將該網(wǎng)上購物系統(tǒng)的總體概念結(jié)構(gòu)E-R 圖轉(zhuǎn)換為關(guān)系模型

6、：客戶（客戶號、密碼、姓名、性別、單位、電子信箱、身份證號）VIP 會員表（會員等級、會員折扣）商品（商品號、商品名稱、類別號、商品描述、單位、規(guī)格、單價）生產(chǎn)廠商表（廠商號，生產(chǎn)廠商名、地址、電話）地址（地址號、地址名稱）訂單（訂單號、客戶號、商品號、收貨地址、訂貨日期、訂貨數(shù)量、單價、訂單金額、發(fā)貨日期、訂單狀態(tài)）發(fā)票（發(fā)票號碼、訂單號）供應(yīng)（商品號、廠商號）商品分類（商品類別號、類別名稱）客戶類型（客戶號、客戶類型）評論（評論號、客戶號、商品號、客戶郵箱、評論內(nèi)容、評論時間）客戶地址表（客戶號、客戶地址）3.2 數(shù)據(jù)模型的優(yōu)化數(shù)據(jù)模數(shù)據(jù)模型的優(yōu)化是為了提高數(shù)據(jù)庫應(yīng)用系統(tǒng)的性能，根據(jù)應(yīng)用需

7、要適當?shù)匦薷?、調(diào)整關(guān)系模式。將轉(zhuǎn)化的關(guān)系模式進行優(yōu)化，最終達到第三范式。由以上關(guān)系可看出，這個關(guān)系模型都已經(jīng)達到第三范式，所以不需要進行優(yōu)化。型的優(yōu)化數(shù)據(jù)庫的結(jié)構(gòu)1.創(chuàng)建客戶表字段信息： 客戶號customerNo密碼password姓名customerName類型號typeNo性別sex單位company電子信箱email身份證號IDVIP 等級VIPCreate table customer（customerNochar(12)primanykey,passwordvarchar(18)NotNull ，customerNamevarchar（12） NotNull ，typeNochar

8、（8）NotNull ，sexchar（1）Check(sexIn( M， F )companyvarchar（20） NotNull ，emailvarchar（50） NotNull ，IDvarchar(18) NotNull,VIPchar(1)2.創(chuàng)建 VIP 會員表字段信息： 會員等級VIP會員折扣discountCreatetable VIP（VIPchar（ 1）primanykey,DiscountfloatNot Null,)3.創(chuàng)建商品表字段信息： 商品號productNo商品名稱productName類別號typeNo商品描述describe單位uints規(guī)格stand

9、ard單價priceCreatetable product（ProductNochar（ 12）primany key，ProductName varchar（18） Not Null,typeNochar(12)Not Null,describevarchar(50)Not Null,unitsvarchar(20)Not Null,standardchar(8)Not Null,pricenumericNot Null,)4.創(chuàng)建生產(chǎn)廠商表字段信息： 廠商號producerNo廠商名producerName地址address電話telephoneCreatetable producer（p

10、roducerNochar（12）primany key,producerNamevarchar(18)Not Null,addressvarchar(50)Not Null,)telephonevarchar(12)Not Null5.創(chuàng)建商品類別表字段信息： 類別號protypeNo類別名稱protypeNameCreatetable protype（protypeNochar(12)protypeName varchar(18)primany key,Not Null)6. 創(chuàng)建地址表字段信息：地址號addressNo地址名稱addressNameCreatetable address(

11、addressNochar(12)primarykey,addressvarchar(20)Not Null,)7. 創(chuàng)建訂單明細表字段信息： 訂單號orderNo客戶號customerNo商品號productNo收貨地址address訂貨日期orderdate訂貨數(shù)量quantity單價price發(fā)票號碼billNo訂單金額ordersum發(fā)貨日期FhdateCreatetable order(訂單狀態(tài)orderstateorderNochar(12)primany key,customerNochar(12)Not Null,productNochar(12)Not Null,addres

12、svarchar(20)Not Null,orderdatedatetimeNot Null,quantitychar(4)Not Null,pricenumericNot Null,billNovarchar(12)primany key,FhdatedatetimeNot Null,Orderstatechar（8）Check （ orderstate IN（0，1），foreign key(customerNo) references customer(customerNo)foreign key(productNo) references product(productNo)8. 創(chuàng)建

13、發(fā)票表字段信息： 發(fā)票號碼billNo訂單號orderNoCreatetablebill （billNochar（ 12）primany key，orderNochar（12）NOT NULL)foreign key(orderNo) references product(orderNoNo)9.創(chuàng)建供應(yīng)表字段信息： 商品號productNoCreate廠商號tablesupport（productNochar（12）producerNochar（12）producerNoprimany key，primany key,foreign key(producerNo) references pr

14、oducer(producerNo)， foreign key(productNo) references product(productNo)10. 創(chuàng)建客戶類型表：字段信息： 客戶號customerNo客戶類型typeCreatetable type（CustomerNochar(12)primany key,Typevarchar(18)Not Null)11. 創(chuàng)建評論表字段信息： 評論號reviewNo客戶號customerNo商品號productNo客戶郵箱email評論內(nèi)容reviewContent評論時間reviewdateCreatetable review(reviewNo

15、char(12)primany key,customerNochar(12)Not Null,produtNochar(12)Not Null,emailvarchar(50)Not Null,reviewContent varchar(100)Not Null,reviewdatedatetimeNot Null,foreign key(customerNo) references customer(customerNo), foreign key(productNo) references product(productNo)12. 創(chuàng)建客戶地址表字段信息：客戶號客戶地址customerN

16、ocusaddressCreatetablecusaddress(customerNocusaddresschar(12)varchar(100)primany key,Not Null)12 創(chuàng)建客戶消費情況表字段信息 客戶號customerNo消費金額moneycreate table cost（customerNo varchar2(20 primary key),money number not null)這里我們保證了實體完整性，參照完整性和用戶自定義完整性。在各個基本表的定義中，我們使主碼值均不為空，表 orderitem 中 ORDER_ID 取值為表 myorder 中的主碼值

17、，而且我們還進行了用戶自定義的完整性約束，使表中某些值取值不為空。四、數(shù)據(jù)庫的實施4.1 數(shù)據(jù)庫的載入4.1.1 創(chuàng)建數(shù)據(jù)庫 shoponlinecreate database shoponlineon(Name= shoponline,Filename= f:shoponline.mdfSize=2,Maxsize=20,Filegrowth=1)Log on,(name= shoponlinelog,Filename=f；shoponlinelog.ldf,Size=2,Maxsize=5,Filegrowth=1)4.1.2 表的建立與數(shù)據(jù)的載入需要明確數(shù)據(jù)庫需要建立幾張表，以及每個表中

18、所要包括的屬性。在建立表的過程中。要對每個表進行字段屬性的設(shè)置。(創(chuàng)建表過程見三、數(shù)據(jù)庫的邏輯設(shè)計 )4.1.3 觸發(fā)器的設(shè)計在修改商品價格的時候，新的商品價格不能超過原來價格的成功，依此設(shè)計一個觸發(fā)器。觸發(fā)器設(shè)計如下：/* 創(chuàng)建觸發(fā)器 T1，實現(xiàn)注冊客戶VIP 自動升級 */Create Trigger T1 ON customerFor updateAs2 倍，否則修改不IF Update（ordersum）BeginDeclare price As numericSelect price=ordersum From insertedIf price=30000Update custome

19、r set VIP= 4ElseIf price=20000Update customer set VIP= 3ElseIf price=10000Update customer set VIP= 2ElseIf price=0Update customer set VIP= 1End/* 創(chuàng)建一個觸發(fā)器，只允許注冊會員在網(wǎng)上提交訂單 Create Trigger T2 on order*/For insertAsIf Not Exists(select * From insertedWhere customerNo in(select customer.customerNo from cus

20、tomer)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)BeginDeclare orderNo int,productNo char(12)Declare cur_order Cursor ForSelect orderNo，productNo From Delet

21、edOpen cur_orderBegin TransactionFetch cur_order into orderNo,productNoWhile (fetch_status=0)BeginUpdate orderSetordersum=ordersum-D.quantity*D.price+I.quantity*I.priceFrom inserted I,deleted DWhere order.orderNo=I.orderNo And I.orderNo=D.orderNoAndorder.orderNo=orderNoAndI.productNo=D.productNoAnd

22、I.productNo=productNoFetch cur_order into orderNo,productNoEndCommit teanClose cur_orderDeallocate cur_orderEnd4.2 數(shù)據(jù)庫的運行附錄：源程序IF Not exists (select * from syslogins where name=Exec sp_addlogin u1,u1 u1)GoIF Not exists (select * from sysdatabases where name= shoponline )Drop database shoponlineGocre

23、ate database shoponlineon(Name= shoponline ,Filename= f:shoponline.mdf ,Size=2,Maxsize=20,Filegrowth=1)Log on(name= shoponlinelog ,Filename= f；shoponlinelog.ldf,Size=2,Maxsize=5,Filegrowth=1)Use shoponlineGoExec sp_addlogin u1,u1GoCreate table customer（customerNochar(12)primanykey,passwordvarchar(18

24、)NotNull，customerNamevarchar（ 12）NotNull，typeNochar（ 8）NotNull，sexchar（ 1）Check(sex In( ，MF )companyvarchar（ 20）NotNull，emailvarchar（ 50）NotNull，IDvarchar(18)NotNull,VIPchar(1)Create tableVIP （VIPchar（ 1） primanykey,)DiscountfloatNot Null,Create tableproduct（ProductNochar（ 12）primany key，ProductName

25、varchar（ 18）Not Null,typeNochar(12)Not Null,describevarchar(50)Not Null,unitsvarchar(20)Not Null,standardchar(8)Not Null,)pricenumericNot Null,Create table producer（producerNochar（ 12）primany key,producerNamevarchar(18)Not Null,addressvarchar(50)Not Null,)telephonevarchar(12)Not NullCreate table pro

26、type（protypeNochar(12)primany key,protypeName varchar(18)Not Null)Creste table address(addressNochar(12)primanykey,addressvarchar(20)Not Null,)Create table order(orderNochar(12)primany key,customerNochar(12)Not Null,productNochar(12)Not Null,addressvarchar(20)Not Null,orderdatedatetimeNot Null,quant

27、itychar(4)Not Null,pricenumericNot Null,billNovarchar(12)primany key,FhdatedatetimeNot Null,Orderstatechar（ 8）Check（ orderstate IN（0，1），foreign key(customerNo) references customer(customerNo)foreign key(productNo) references product(productNo)Create tablebill（billNochar（ 12）primany key，orderNochar（

28、12）primany key,foreign key(orderNo) references product(orderNoNo)Create tablesupport（productNochar（ 12）primany key，producerNochar（ 12）primany key,foreign key(producerNo) references producer(producerNo)，foreign key(productNo) references product(productNo)Create tabletype（CustomerNochar(12)primany key

29、,)Typevarchar(18)Not NullCreate table review(reviewNochar(12)primany key,customerNochar(12)Not Null,produtNochar(12)Not Null,emailvarchar(50)Not Null,reviewContentvarchar(100) Not Null,reviewdatedatetimeNot Null,foreign key(customerNo) references customer(customerNo), foreign key(productNo) references product(productNo)Create tablecusaddress( customerNo cusaddresschar(12)varchar(100)primany key,Not Null)/* 創(chuàng)建觸發(fā)器 T1，實現(xiàn)注冊客戶VIP 自動升級 */Create Trigger T1 ON customerFor updateAsIF Update（ ordersum）BeginDeclare price As numericSelect price=ordersum From insertedIf price=30000Update cu