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

1、據(jù)庫(kù)系統(tǒng)概論課程設(shè)計(jì)網(wǎng)上購(gòu)物系統(tǒng)的數(shù)據(jù)庫(kù)設(shè)計(jì)學(xué)院（系）：專業(yè)：學(xué)號(hào)：ia指導(dǎo)老師:2012年6月23日目錄需求分析1. 1功能需求及數(shù)據(jù)需求分析212業(yè)務(wù)規(guī)則分析21. 3業(yè)務(wù)需求及處理流程2概念結(jié)構(gòu)設(shè)計(jì)2.1各子系統(tǒng)的局部e-r32. 2視圖的集成6邏輯結(jié)構(gòu)設(shè)計(jì)3.1 er圖向關(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ù)需求分析1.1.1用戶管理 用戶：只允許瀏覽商品信息，可以注冊(cè)

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

3、.1.4配送單管理默認(rèn)屬性為客戶注冊(cè)吋的基本信息，當(dāng)然配送地址可由客戶修改為合適的收貨地 址，支付方式也可根據(jù)提示由客戶自定。1.1.5評(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à)格上同吋享有折扌】1優(yōu)惠。 其屮。三級(jí)會(huì)員，訂單金額在10000, 15000）元，享受折扣9折；二級(jí)會(huì)員， 訂單金額在15

4、000, 25000）元,享受折扣8.5折；一級(jí)會(huì)員，訂單金額在25000 及以上，享受折扣7.5折。4、每次商品的訂單號(hào)都是唯一的。5、客戶可以在前臺(tái)頁(yè)面查看訂單狀態(tài)，訂單狀態(tài)可以是“（t或-r表示訂單 上的商品已發(fā)出，“（f表示訂單上的商品未發(fā)出。訂單的生成需用戶確定之后才能生效。1. 3業(yè)務(wù)需求及處理流程網(wǎng)上購(gòu)物系統(tǒng)主要業(yè)務(wù)包括：商品信息的發(fā)布與查詢，商品的訂購(gòu)，處理訂單, 商品的配送。系統(tǒng)處理流程:注刖川戶住錄透擇俯品加人儘邑與支付握交訂單訂單:牛.成牛.戚訂單.編號(hào)系統(tǒng)流程圖2.1各子系統(tǒng)的局部e-r如下:訂單處陀審核信息通過概念結(jié)構(gòu)設(shè)計(jì)訂單有愎圖2.1.2： vip客戶商品號(hào)類別號(hào)

5、圖2.1.3：商品實(shí)體廠繭號(hào)地址圖2.1.5：商品分類發(fā)塔® 2.1.8：發(fā)票生產(chǎn)廠商圖2.1.9：供應(yīng)關(guān)系圖2.1.10：商品分類關(guān)系客戶圖2.1.11：客戶評(píng)論關(guān)系2. 2視圖的集成方式視圖的集成通俗的理解就是把各個(gè)子e-r圖集成合并。視圖集成可以 有兩種：1）多個(gè)分e-r圖一次集成；2）逐步集成，用累加的方式一次集成兩個(gè)分e-r圖。第一種方法比較復(fù)雜，做起來難度較大；第二種方法每次只集成兩個(gè)分 e-r圖，可以降低復(fù)雜度。無論采用哪種方式，每次集成局部e-r圖時(shí)都需要分兩步走：1）合并。 解決各分e-r圖合并起來生成初步e-r圖；2）修改和重構(gòu)。消除不必要的冗 余，生成基本e-r

6、圖。冗余數(shù)據(jù)和冗余聯(lián)系容易破壞數(shù)據(jù)庫(kù)的完整性，給數(shù) 據(jù)庫(kù)維護(hù)增加困難，應(yīng)當(dāng)予以消除。消除了冗余后的初步e-r圖稱為基本e-r 圖。消除冗余主要采用的方法有：1）用分析方法消除兀余；2）用規(guī)范化理論 消除冗余。對(duì)該網(wǎng)上購(gòu)物系統(tǒng)各子e-r圖的集成一一即該網(wǎng)上購(gòu)物系統(tǒng)的總體概念結(jié)構(gòu)e-r圖如下圖所示:峯戶類里浙在商胡 hm 供“產(chǎn)廠商三、邏輯結(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ì)過程就是把e-r圖轉(zhuǎn)化為關(guān)系模式的過程。將該網(wǎng)上購(gòu)物系統(tǒng)的總體概念結(jié)構(gòu)e-r圖轉(zhuǎn)換為關(guān)系模型： 客戶（客戶號(hào)、密碼、姓名、性別、單位、電子信箱、身

7、份證號(hào)） vip會(huì)員表（會(huì)員等級(jí)、會(huì)員折扌ii）商品（商品號(hào)、商品名稱、類別號(hào)、商品描述、單位、規(guī)格、單價(jià)） 生產(chǎn)廠商表（廠商號(hào),生產(chǎn)廠商名、地址、電話）地址（地址號(hào)、地址名稱）訂單（訂單號(hào)、客戶號(hào)、商品號(hào)、收貨地址、訂貨日期、訂貨數(shù)量、單價(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)論吋間） 客戶地址表（客戶號(hào)、客戶地址）3.1.2視圖的設(shè)計(jì)為會(huì)員查看商品建立視圖:32數(shù)據(jù)模型的優(yōu)化數(shù)據(jù)模數(shù)據(jù)模型的優(yōu)化是為了提高數(shù)據(jù)庫(kù)應(yīng)用系統(tǒng)的性能，根據(jù)應(yīng)

8、用需要適當(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) 號(hào) 信證 戶碼名型別位子份 客密姓類性單電身vip等級(jí)create table customer (customerno password customername typeno sexcompany emailidvipcustomernochar( 12)primary key,passwordvarchar(18)notnull,customern

9、amevarchar (12)notnull,typenochar (8)notnull,sexchar (1)check(sex in(4m <f),companyvarchar (20),emailvarchar (50)notnull,idvarchar(18)notnull,vipchar( 1)2. 創(chuàng)建vip會(huì)員表字段信息：會(huì)員等級(jí)vip會(huì)員折扣discountcreate table vip (vip char (1) primary key, discount float not null)3. 創(chuàng)建商品表字段信息：商品號(hào)productno商品名稱productname類

10、別號(hào)typeno商品描述describe單位uints規(guī)格standard單價(jià)pricecreate table product (productnochar (12)primary key,productnamevarchar (18)notnulltypenochar( 12)notnulldescribevarchar(50)notnullunitsvarchar(20)notnullstandardchar(8),price)numericnotnull4. 創(chuàng)建生產(chǎn)廠商表字段信息：廠商號(hào)producerno廠商名producername地址address電話telephonecrea

11、te table producer (producernochar (12)primary key,producernamevarchar(18)notnull,addressvarchar(50)notnull,telephonevarchar( 12)notnull)5. 創(chuàng)建商品類別表字段信息：類別號(hào)類別名稱protypeno protypenamecreate table protype (primany key,not nullprotypenochar( 12)protypenamevarchar( 18)6. 創(chuàng)建地址表字段信息：地址號(hào) addressno 地址名稱 addres

12、snamecreste table address(addressno char( 12) address varchar(20)primary key,not null7.創(chuàng)建訂單明細(xì)表字段信息：訂單號(hào)orderno客戶號(hào)customerno商品號(hào)productno收貨地址address訂貨日期orderdate訂貨數(shù)量quantity單價(jià)price發(fā)票號(hào)碼billno訂單金額ordersum發(fā)貨日期fhdate訂單狀態(tài)orderstatecreate table ordermaster(ordernochar(12)primary key,customernochar(12)notnull

13、,productnochar( 12)notnull,addressvarchar(20)notnull,orderdatedatetimenotnull,quantitychar(4)notnull,pricenumericnotnull,billnovarchar( 12)notnull,fhdatedatetimenotnull,orderstatechar (8)check(orderstate in ('o',t),foreign key(customerno) references customer(customerno) foreign key(productno

14、) references product(productno), foreign key(billno) references bill(billno)8.創(chuàng)建發(fā)票表字段信息：發(fā)票號(hào)碼 訂單號(hào)create table billno ordernobill (char (12)char (12)billnoordernoprimany key,not null,foreign key(orderno) references product(ordernono) )9.創(chuàng)建供應(yīng)表字段信息：商品號(hào)productno廠商號(hào)producernocreate table support ( product

15、nochar (12)primany key,producernochar (12)not null,foreign key(producerno) references producer(producerno), foreign key(productno) references product(productno)10.創(chuàng)建客戶類型表：字段信息：客戶號(hào) customerno 客戶類型typecreate table type ( customernochar( 12)primary key,typevarchar( 18)not null)11創(chuàng)建評(píng)論表字段信息：評(píng)論號(hào)reviewno客戶

16、號(hào)customerno商品號(hào)productno客戶郵箱email評(píng)論內(nèi)容reviewcontent評(píng)論時(shí)間reviewdatecreate tablereview(reviewnochar(12)primany key,customernochar(12)notnull,produtnochar(12)notnull,emailvarchar(50)notnull,reviewcontent varchar( 100)notnull,reviewdatedatetimenotnull,foreign key(customerno) references customer(customerno)

17、, foreign key(productno) references product(productno)12.創(chuàng)建客戶地址表字段信息：客戶號(hào) customerno客戶地址cusaddresscreate table cusaddress(customernochar( 12)primary key,cusaddressvarchar( 100) not null)這里我們保證了實(shí)體完整性，參照完整性和用戶自定義完整性。在各個(gè)基本表的 定義中，我們使主碼值均不為空，而且我們述進(jìn)行了用戶自定義的完整性約束， 使表中某些值取值不為空。四、物理結(jié)構(gòu)設(shè)計(jì)4.1物理設(shè)計(jì)任務(wù)與目標(biāo)為上一階段的關(guān)系模型選

18、取一個(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ù)的載入4.1.1創(chuàng)建數(shù)據(jù)庫(kù)shoponlinecreate database shoponlineon(name=' shoponline；filename=, f:sqlshoponline.mdf,size=2,maxsize=20,filegrowth=l)log on(name=5 shoponlinelogfilename二&#

19、39;f； sqlshoponlinelog.ldf,size=2,maxsize=5,filegrowth=l)4.1.2觸發(fā)器的設(shè)計(jì)在修改商品價(jià)格的時(shí)候，新的商品價(jià)格不能超過原來價(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 (ordersum)begindeclare price as numericselect price=ordersum from insertedif price>=3ooooupdate cu

20、stomer set vip二3elseif price>=20000update customer set vip=2elseif price>= 10000 update customer set vip=' vend/*創(chuàng)建一個(gè)觸發(fā)器，只允許注冊(cè)會(huì)員在網(wǎng)上提交訂單*/create trigger t2 on orderfor insertasif not exists(select * from insertedwhere customerno in(select custome匚customerino from customer) beginraiserror(6提

21、交訂單前請(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)begindeclare orderno int,productno char(12)declare cur_order cursor forselect orderno, productno from deletedopen cur_orderbegin transactionfetch cur_order into orderno, productnowhile (fetch_status=o)beginupdate orderset ordersum=ordersud.quantity*d.price+lquantity*i.pricefrom inserted i5deleted dwhere order.orderno=i.orderno and lorderno=d.ordernoand ord er. ord er n o= ord erno and i.productno=d.productno