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

1、一、概述1.1需求背景伴著電子時(shí)代的迅猛發(fā)展和人民物質(zhì)生活的水平的提高，越來(lái)越多的電子購(gòu)物浪潮也洶涌而來(lái)。我們?nèi)萆碓谶@個(gè)信息化的大時(shí)代，網(wǎng)購(gòu)也就成了許多人生活中必不可少的一部分，足不出戶的便捷式購(gòu)物與傳統(tǒng)的購(gòu)物方式大相徑庭，人們?cè)谙硎艿椒奖?、?shí)惠的同時(shí)也不必?fù)?dān)憂安全的問(wèn)題，既方便了自身也推動(dòng)著國(guó)家經(jīng)濟(jì)的發(fā)展。電子商務(wù)網(wǎng)絡(luò)購(gòu)物平臺(tái)，無(wú)疑是這個(gè)時(shí)代的進(jìn)步。1.2編寫(xiě)目的數(shù)據(jù)庫(kù)設(shè)計(jì)說(shuō)明書(shū)是數(shù)據(jù)庫(kù)設(shè)計(jì)的必要部分，對(duì)設(shè)計(jì)中的數(shù)據(jù)庫(kù)的所有標(biāo)識(shí)、邏輯結(jié)構(gòu)和物理結(jié)構(gòu)作出具體的設(shè)計(jì)規(guī)定。本數(shù)據(jù)庫(kù)的設(shè)計(jì)說(shuō)明書(shū)編寫(xiě)的目的是對(duì)網(wǎng)上購(gòu)物系統(tǒng)設(shè)計(jì)的說(shuō)明，明確系統(tǒng)中的各項(xiàng)功能與非功能的需求，從而做出系統(tǒng)的數(shù)據(jù)流圖以及實(shí)體聯(lián)

2、系圖。作為系統(tǒng)的基準(zhǔn)文檔，為以后的開(kāi)發(fā)和維護(hù)提供依據(jù)。1.3軟件定義Myeclipse 10.0:一個(gè)非常優(yōu)秀的用于開(kāi)發(fā)Java、J2EE的Eclipse插件集合，Myeclipse功能非常強(qiáng)大，支持也十分廣泛，尤其對(duì)各種開(kāi)源產(chǎn)品的支持也不錯(cuò)。Apache Tomcat 6.0:是一個(gè)開(kāi)放源代碼、運(yùn)行servlet和JSP Web應(yīng)用軟件容器。Microsoft SQL Server 2005:Structured Query Language1.4開(kāi)發(fā)環(huán)境本電子商務(wù)網(wǎng)絡(luò)購(gòu)物平臺(tái)的開(kāi)發(fā)環(huán)境是Windows 7、Myeclipse10.0、Apache Tomcat6.0，數(shù)據(jù)庫(kù)環(huán)境是Micro

3、soft SQL Server 2005。二、需求分析2.1問(wèn)題陳述設(shè)計(jì)網(wǎng)絡(luò)購(gòu)物系統(tǒng)的數(shù)據(jù)庫(kù)。2.1需完成的功能客戶功能：(1) 游客可以查看商品信息，瀏覽網(wǎng)站信息，經(jīng)過(guò)注冊(cè)可以成為注冊(cè)客戶。(2) 注冊(cè)客戶：注冊(cè)、客戶信息查看和修改?？蛻舻卿洝⒋_認(rèn)客戶信息，顯示客戶信息。商品信息瀏覽、購(gòu)物車管理、商品查找、訂單查詢以及商品評(píng)論。結(jié)賬、確認(rèn)訂單、訂單狀態(tài)查詢、歷史訂單查詢。商家功能：商品的增刪改。訂單處理、訂單配送?？蛻糇?cè)后，登錄到電子商務(wù)網(wǎng)站，進(jìn)入購(gòu)物流程?？蛻粼跒g覽所有商品信息后，可以查看每個(gè)商品的詳細(xì)信息，選擇購(gòu)買或加入購(gòu)物車，購(gòu)物車內(nèi)可以隨意增刪商品，修改商品數(shù)量，并同時(shí)統(tǒng)計(jì)購(gòu)物車內(nèi)

4、商品總額?？蛻舸_認(rèn)購(gòu)物車內(nèi)的商品，選中購(gòu)買，即可生成訂單?？梢圆榭从唵螤顟B(tài)，“0表示”商品未發(fā)出，“1”表示商品已發(fā)出。客戶分為普通客戶和VIP客戶，依據(jù)購(gòu)買商品的金額和數(shù)量，自動(dòng)升級(jí)。不同的VIP等級(jí)享有不同的折扣。2.1數(shù)據(jù)流圖網(wǎng)絡(luò)購(gòu)物系統(tǒng)總數(shù)據(jù)流圖如下圖1：網(wǎng)絡(luò)購(gòu)物系統(tǒng)總數(shù)據(jù)流圖2.1數(shù)據(jù)字典客戶表數(shù)據(jù)字典：字段中文描述數(shù)據(jù)類型是否為空備注UserID客戶IDBigIntNot null主鍵UserName客戶名Varchar(30)Not nullRealName客戶真實(shí)姓名Varchar(30)Not nullUserPassword客戶密碼Varchar(30)Not nullEm

5、ail客戶郵箱Varchar(30)Not nullPhone聯(lián)系電話Varchar(30)Not nullID身份證號(hào)Varchar(30)Not nullQuestion密碼問(wèn)題Varchar(30)NullResult答案Varchar(30)NullRegTime注冊(cè)時(shí)間DatetimeNot nullGradeID會(huì)員等級(jí)BigIntNot nullAddressID地址IntNot null表1：客戶表字段信息商品表數(shù)據(jù)字典：字段中文描述數(shù)據(jù)類型是否為空備注GoodsID商品號(hào)BigIntNot null主鍵GName商品名稱Varchar(30)Not nullGPrice商品價(jià)

6、格SmallmoneyNot nullGTypeID商品類別BigIntNot nullGIntro商品描述Varchar(200)NullGImage商品圖片路徑Varchar(100)NullGCount商品計(jì)數(shù)IntNot nullGSail商品銷量BigIntNot nullGWeight商品重量BigIntNullGDateTime上架日期DatetimeNot nullGSize商品規(guī)格Varchar(100)Not null表2：商品表字段信息商品類別表數(shù)據(jù)字典：字段中文描述數(shù)據(jù)類型是否為空備注TypeID商品類別號(hào)BigIntNot null主鍵TypeName類別描述Varc

7、har(50)Not null表3：商品類別表字段信息商家表數(shù)據(jù)字典：字段中文描述數(shù)據(jù)類型是否為空備注ProducerID商家號(hào)BigIntNot null主鍵ProName商家名Varchar(30)Not nullProAddress商家地址Varchar(50)Not nullProPhone商家電話Varchar(30)Not null表4：商家表字段信息訂單表數(shù)據(jù)字典：字段中文描述數(shù)據(jù)類型是否為空備注SubID訂單號(hào)Varchar(30)Not null主鍵UserID客戶號(hào)BigIntNot nullGoodsID商品號(hào)BigIntNot nullRAddress送貨地址Varch

8、ar(50)Not nullSpDate訂購(gòu)日期DatetimeNot nullSpCount訂購(gòu)數(shù)量IntNot nullGPrice單價(jià)SmallmoneyNot nullReceiptID發(fā)票號(hào)BigIntNot nullSpPrice訂單金額MoneyNot nullSendDate發(fā)貨時(shí)間DatetimeNot nullPayment付款方式Varchar(20)Not nullSendType運(yùn)送方式Varchar(20)Not nullSendPrice郵費(fèi)SmallmoneyNot nullState訂單狀態(tài)IntNot null表5：訂單表字段信息購(gòu)物車表數(shù)據(jù)字典：字段中文描

9、述數(shù)據(jù)類型是否為空備注CarID購(gòu)物車IDBigIntNot null主鍵UserID客戶IDVarchar(10)Not nullGoodsID商品IDVarchar(20)Not nullOrderTime訂購(gòu)時(shí)間DatetimeNot nullOrderCount訂購(gòu)數(shù)量IntNot nullGoodsPrice單價(jià)SmallmoneyNot nullUserCheck用戶確認(rèn)IntNot nullOrderPrice購(gòu)物總額MoneyNot null表6：購(gòu)物車表字段信息會(huì)員等級(jí)表數(shù)據(jù)字典：字段中文描述數(shù)據(jù)類型是否為空備注GradeID會(huì)員等級(jí)IDBigIntNot null主鍵Gra

10、deType等級(jí)分類IntNot null表7：會(huì)員等級(jí)表字段信息評(píng)論表數(shù)據(jù)字典：字段中文描述數(shù)據(jù)類型是否為空備注CommentID評(píng)論IDBigIntNot null主鍵Content1內(nèi)容Varchar(200)Not nullTitle標(biāo)題Varchar(20)Not nullUserID客戶IDBigIntNot nullProducerID商家IDBigIntNot nullCommentTime評(píng)論時(shí)間DatetimeNot null表8：評(píng)論表字段信息供應(yīng)表數(shù)據(jù)字典：字段中文描述數(shù)據(jù)類型是否為空備注SupplyID供應(yīng)IDBigIntNot null主鍵RealStock實(shí)際存貨

11、IntNot nullMinStock最低存貨IntNot nullGoodsID商品IDBigIntNot nullProducerID商家IDBigIntNot null表9：供應(yīng)表字段信息發(fā)票表數(shù)據(jù)字典：字段中文描述數(shù)據(jù)類型是否為空備注ReceiptID發(fā)票IDBigIntNot null主鍵SubID訂單號(hào)BigIntNot null表10：發(fā)票表字段信息地址表數(shù)據(jù)字典：字段中文描述數(shù)據(jù)類型是否為空備注AddressID地址IDIntNot null主鍵Address地址Varchar(50)Not nullUserID客戶IDBigIntNot null表11：發(fā)票表字段信息三、數(shù)據(jù)

12、庫(kù)概念結(jié)構(gòu)設(shè)計(jì)3.1 E-R模型圖的設(shè)計(jì)（1）尋找實(shí)體集客戶VIP會(huì)員商品商家商品類別地址（2）尋找實(shí)體間聯(lián)系客戶與商品之間多對(duì)多聯(lián)系：訂單，評(píng)論1> 客戶與VIP會(huì)員之間多對(duì)一聯(lián)系：屬于2> 會(huì)員等級(jí)與訂單細(xì)節(jié)一對(duì)多：參照13> 訂單細(xì)節(jié)與商品多對(duì)一：參照24> 訂單細(xì)節(jié)預(yù)訂單多對(duì)一：組成客戶與商品之間一對(duì)多聯(lián)系：購(gòu)物車1> 客戶與購(gòu)物車一對(duì)一：擁有2> 購(gòu)物車與商品一對(duì)多：存放3> 購(gòu)物車與訂單細(xì)節(jié)一對(duì)多：結(jié)算商家與商品之間多對(duì)多聯(lián)系：供應(yīng)商品與商品類別之間多對(duì)一聯(lián)系：分類客戶與地址之間一對(duì)多聯(lián)系：所在（3）確定實(shí)體的屬性客戶：客戶ID 客戶名 客

13、戶真實(shí)姓名 客戶密碼 客戶郵箱 聯(lián)系電話身份證號(hào) 密碼問(wèn)題 答案 注冊(cè)時(shí)間 會(huì)員等級(jí)IDVIP會(huì)員：會(huì)員等級(jí)ID 等級(jí)分類商品：商品號(hào) 商品名稱 商品價(jià)格 商品類別 商品描述 商品圖片路徑 商品計(jì)數(shù) 商品銷量 商品重量 上架日期 商品規(guī)格商家：商家號(hào) 商家名 商家地址 商家電話地址：地址號(hào) 地址名稱商品類別：商品類別號(hào) 類別描述（3）確定聯(lián)系的屬性訂單：訂單號(hào) 客戶號(hào) 商品號(hào) 送貨地址 訂購(gòu)日期 訂購(gòu)數(shù)量 單價(jià)發(fā)票號(hào) 訂單金額 發(fā)貨時(shí)間 付款方式 運(yùn)送方式 郵費(fèi) 訂單狀態(tài)購(gòu)物車：購(gòu)物車ID 客戶ID 商品ID 訂購(gòu)時(shí)間 訂購(gòu)數(shù)量 單價(jià) 用戶確認(rèn)供應(yīng)：供應(yīng)ID 實(shí)際存貨 最低存貨 商品ID 商家

14、ID分類：商品號(hào) 商品分類客戶類型屬于：客戶號(hào) 客戶類型評(píng)論：評(píng)論ID 內(nèi)容 標(biāo)題 客戶ID 商家ID 評(píng)論時(shí)間客戶地址所在:客戶號(hào) 客戶地址（4）畫(huà)出E-R圖圖2：客戶與商品實(shí)體間的詳細(xì)聯(lián)系E-R圖圖3：網(wǎng)絡(luò)購(gòu)物系統(tǒng)E-R圖四、數(shù)據(jù)庫(kù)邏輯結(jié)構(gòu)設(shè)計(jì)4.1 E-R模型圖轉(zhuǎn)化為關(guān)系表（1）實(shí)體轉(zhuǎn)化為單獨(dú)的一張表客戶VIP會(huì)員商品商家商品類別地址（2）多對(duì)多聯(lián)系轉(zhuǎn)化為單獨(dú)的一張關(guān)系表購(gòu)物車 訂單 評(píng)論 供應(yīng)（3）其他一對(duì)多的聯(lián)系通過(guò)在多的一方增加屬性來(lái)實(shí)現(xiàn)客戶表與會(huì)員等級(jí)：在客戶表中增加會(huì)員等級(jí)ID商品與商品類型：在商品中增加類型號(hào)4.2 描述基本表的關(guān)系E-R圖轉(zhuǎn)化為關(guān)系模型，關(guān)系的碼用下劃線標(biāo)出

15、。此為客戶實(shí)體對(duì)應(yīng)的關(guān)系模式：客戶(客戶號(hào)，密碼，會(huì)員等級(jí)號(hào)，)此為VIP會(huì)員實(shí)體對(duì)應(yīng)的關(guān)系模式：VIP會(huì)員(會(huì)員等級(jí)號(hào)，等級(jí)類型)此為商品實(shí)體對(duì)應(yīng)的關(guān)系模式：商品(商品號(hào)，描述，商品分類，商品名，)此為商家實(shí)體對(duì)應(yīng)的關(guān)系模式：商家(商家號(hào)，商家名，商家電話，)此為商品類別實(shí)體對(duì)應(yīng)的關(guān)系模式：商品類別(商品類別號(hào)，商品類別)此為聯(lián)系“購(gòu)物車”所對(duì)應(yīng)的關(guān)系模式：購(gòu)物車(購(gòu)物車號(hào)，客戶號(hào)，商品號(hào)，)此為聯(lián)系“訂單”所對(duì)應(yīng)的關(guān)系模式：訂單(訂單號(hào)，客戶號(hào)，商品號(hào)，)此為聯(lián)系“評(píng)論”所對(duì)應(yīng)的關(guān)系模式：評(píng)論(評(píng)論號(hào)，內(nèi)容，客戶號(hào)，)此為聯(lián)系“供應(yīng)”所對(duì)應(yīng)的關(guān)系模式：供應(yīng)(供應(yīng)號(hào)，實(shí)際存貨，最少存貨，)4

16、.3 所有關(guān)系表達(dá)到BCNF范式4.4 定義視圖（1）訂單表中只關(guān)聯(lián)客戶、商品的ID，而最終客戶核對(duì)的應(yīng)該是包含客戶真實(shí)姓名在內(nèi)的客戶必要信息以及包含商品名稱的商品信息，分別建立兩個(gè)視圖：1> 參照商品的訂單細(xì)節(jié)create view View_goodsas select * from tb_goods where GoodsID='201201'2>參照客戶信息的訂單細(xì)節(jié)create view View_useras select * from tb_user where UserID='201'4.5 定義索引在經(jīng)常查詢的列上（主鍵）建立聚簇索

17、引如：在商品信息表上建立索引create cluster index Index_ GoodsIDon tb_goods(GoodsID)4.6 定義觸發(fā)器（1）注冊(cè)客戶自動(dòng)升級(jí)VIP-創(chuàng)建觸發(fā)器tr_grade，實(shí)現(xiàn)注冊(cè)客戶VIP自動(dòng)升級(jí)Create Trigger tr_grade ON tb_subFor updateAsIF Update(SpPrice)BeginDeclare price moneySelect price=SpPrice From insertedIf price>=30000Update tb_user set GradeID=4ElseIf price&

18、gt;=20000Update tb_user set GradeID=3ElseIf price>=10000Update tb_user set GradeID=2ElseIf price>=0Update tb_user set GradeID=1End（2）更新訂單，訂單金額是商品價(jià)格總和-創(chuàng)建一個(gè)更新觸發(fā)器，及時(shí)更新訂單Create Trigger tr_update On tb_sub For Update AsIf Update (SpCount) Or Update (GPrice)Begin Declare orderNo Bigint,productNo Big

19、intDeclare cur_order Cursor ForSelect SubID,GoodsID From DeletedOpen cur_orderBegin Transaction Fetch cur_order into orderNo,productNoWhile (fetch_status=0)Begin Update tb_subSet tb_sub.SpPrice=tb_sub.SpPrice-D.SpCount*D.GPrice+I.SpCount*I.GPriceFrom inserted I,deleted DWhere tb_sub.subID=I.subID An

20、d I.subID=D.subIDAnd tb_sub.subID=orderNo And I.GoodsID=D.GoodsIDAnd I.GoodsID=productNoFetch cur_order into orderNo,productNoEndCommit tranClose cur_orderDeallocate cur_orderEnd（3）更新購(gòu)物車，選中商品更新商品總額-更新購(gòu)物車，選中商品更新商品總額Create Trigger tr_car On tb_car For Update AsIf Update (OrderCount) Or Update (GoodsPr

21、ice)Begin Declare CarID Bigint,GoodsID BigintDeclare cur_car Cursor ForSelect CarID,GoodsID From DeletedOpen cur_carBegin Transaction Fetch cur_car into CarID,GoodsIDWhile (fetch_status=0)Begin Update tb_carSet tb_car.OrderPrice=tb_car.OrderPrice-D.OrderCount*D.GoodsPrice+I.OrderCount*I.GoodsPriceFr

22、om inserted I,deleted DWhere tb_car.CarID=I.CarID And I.CarID=D.CarIDAnd tb_car.CarID=CarID And I.GoodsID=D.GoodsIDAnd I.GoodsID=GoodsIDFetch cur_car into CarID,GoodsIDEndCommit tranClose cur_carDeallocate cur_carEnd（4）創(chuàng)建觸發(fā)器只允許注冊(cè)會(huì)員在網(wǎng)上下訂單-創(chuàng)建一個(gè)觸發(fā)器tr_sub，只允許注冊(cè)會(huì)員在網(wǎng)上提交訂單Create Trigger tr_sub on tb_subFor

23、 insertAsIf Not Exists(select * From insertedWhere UserID in(select tb_user.UserID from tb_user)BeginRaiserror('提交訂單前請(qǐng)先注冊(cè)！',16,1)Rollback TransactionEnd4.7 定義約束（1）確定實(shí)體完整性約束主鍵約束（2）參照完整性約束-為tb_user表添加參照性約束alter table tb_userwith nocheckadd constraint fk_user foreign key(GradeID)references tb_g

24、rade(GradeID)-為tb_goods表添加參照性約束alter table tb_goodswith nocheckadd constraint fk_goods foreign key(GTypeID)references tb_type(TypeID)-為tb_address增加外鍵約束alter table tb_addresswith nocheckadd constraint fk_address foreign key(UserID)references tb_user(UserID)（3）用戶自定義完整性約束1>客戶表密碼不能少于6位，且不能與客戶號(hào)相同Alter table tb_userAdd constraint Ck_pass Check (6<=len(Userpassword) and Userpassword<>UserID)2> 電子信箱格式為格式Alter table tb_userAdd constraint Ck