實(shí)驗(yàn)7 數(shù)據(jù)庫設(shè)計(jì).doc

實(shí)驗(yàn)7 數(shù)據(jù)庫設(shè)計(jì)（6學(xué)時(shí)） 實(shí)驗(yàn)?zāi)康呐c要求了解數(shù)據(jù)庫設(shè)計(jì)的重要性和數(shù)據(jù)庫設(shè)計(jì)在信息系統(tǒng)開發(fā)和建設(shè)中的核心地位。掌握數(shù)據(jù)庫設(shè)計(jì)方法和步驟。 實(shí)驗(yàn)任務(wù)在某個(gè)RDBMS產(chǎn)品上，選擇合適的應(yīng)用系統(tǒng)開發(fā)工具為某個(gè)部門或單位開發(fā)一個(gè)數(shù)據(jù)庫應(yīng)用系統(tǒng)。通過實(shí)踐，掌握本章介紹的數(shù)據(jù)庫設(shè)計(jì)方法，同時(shí)培養(yǎng)團(tuán)隊(duì)合作精神。要求56位同學(xué)組成一個(gè)開發(fā)小組，每位同學(xué)承擔(dān)不同角色（例如：項(xiàng)目管理員、DBA、系統(tǒng)分析員、系統(tǒng)設(shè)計(jì)員、系統(tǒng)開發(fā)員、系統(tǒng)測(cè)試員）。具體要求：給出數(shù)據(jù)庫設(shè)計(jì)各個(gè)階段的詳細(xì)設(shè)計(jì)報(bào)告；寫出系統(tǒng)的主要功能和使用說明；提交運(yùn)行的系統(tǒng)；寫出收獲和體會(huì)，包括已解決和尚未解決的問題，進(jìn)一步完善的設(shè)想與建議；每個(gè)小組進(jìn)行20分鐘的報(bào)告和答辯，講解設(shè)計(jì)方案，演示系統(tǒng)運(yùn)行，匯報(bào)分工與合作情況。網(wǎng)上書店數(shù)據(jù)庫設(shè)計(jì)一、設(shè)計(jì)意義伴隨著信息化的發(fā)展，計(jì)算機(jī)走進(jìn)了千家萬戶，人們的交流變得方便，方便了網(wǎng)民的生活！此外，伴隨著卓越、當(dāng)當(dāng)、淘寶等網(wǎng)絡(luò)購(gòu)物平臺(tái)的興起，網(wǎng)絡(luò)購(gòu)物也被網(wǎng)民認(rèn)可，首先，網(wǎng)上書店方便了網(wǎng)民的生活，更加便捷地、足不出戶便可買到心儀的書籍；其次，網(wǎng)上購(gòu)書減少了許多中間銷售代理，因此價(jià)格會(huì)比較低，對(duì)廣大網(wǎng)民的好處是非常明顯的；再者，網(wǎng)上書店是信息化購(gòu)物，基本上所有操作都是通過計(jì)算機(jī)程序自動(dòng)完成的，因此大大降低了勞力的消耗；最后，電子商務(wù)與出版發(fā)行業(yè)的結(jié)合使發(fā)展網(wǎng)上書店及研究網(wǎng)上書店具有雙重意義，充分結(jié)合互聯(lián)網(wǎng)銷售的特點(diǎn)探索圖書發(fā)行的新渠道和新模式，是對(duì)電子商務(wù)科學(xué)的充實(shí)和補(bǔ)充，更是對(duì)出版發(fā)行業(yè)發(fā)行渠道和市場(chǎng)的開拓和創(chuàng)新。數(shù)據(jù)庫對(duì)于一個(gè)系統(tǒng)的意義是相當(dāng)重要的，它關(guān)系到整個(gè)項(xiàng)目的整體流程，整個(gè)網(wǎng)站的后期維護(hù)便捷與否，從長(zhǎng)遠(yuǎn)利益來看，數(shù)據(jù)庫的設(shè)計(jì)是至關(guān)重要的。二、需求分析2.1系統(tǒng)功能分析本實(shí)驗(yàn)將設(shè)計(jì)出一個(gè)網(wǎng)上書店數(shù)據(jù)庫，其要求簡(jiǎn)要如下：（1）系統(tǒng)用戶由兩類組成：管理員和客戶。（2）管理員負(fù)責(zé)后臺(tái)管理： 圖書信息的維護(hù)（增、刪、改），圖書要進(jìn)行分類管理； 客戶信息的維護(hù)； 訂單的確認(rèn)和刪除。（3）讀者可以查詢圖書的基本信息，可以維護(hù)自己的信息，可以進(jìn)行網(wǎng)上訂書，可以查詢訂單處理的情況。2.2 數(shù)據(jù)需求系統(tǒng)共有七個(gè)實(shí)體：客戶、管理員、圖書類型、圖書、訂單、詳細(xì)訂單、圖書評(píng)價(jià)。（1）會(huì)員的屬性：自動(dòng)編號(hào)CustomerId、客戶名、客戶密碼、真實(shí)姓名、性別、客戶電話、E-mail、地址、注冊(cè)時(shí)間（2）管理員的屬性：自動(dòng)編號(hào)AdminId、管理員名稱、管理員郵箱、密碼（3）圖書類型的屬性：自動(dòng)編號(hào)BookTypeId、類別名稱（4）圖書的屬性：自動(dòng)編號(hào)BookId、圖書名稱、圖書類型、出版社、出版日期、圖書作者、圖書定價(jià)、圖書簡(jiǎn)介、圖書庫存量、圖書成交量（5）訂單的屬性：自動(dòng)編號(hào)OrderId、客戶編號(hào)、下單日期、總訂購(gòu)數(shù)量、送貨方式、支付方式、收貨人姓名、收貨地址、收貨人聯(lián)系方式、總賣出價(jià)（6）詳細(xì)訂單屬性：自動(dòng)編號(hào)Id、詳細(xì)訂單OrderDetailId、訂單號(hào)OrderId、圖書編號(hào)、訂購(gòu)數(shù)量、發(fā)貨狀態(tài)、收貨狀態(tài)、賣出總價(jià)（7）圖書評(píng)價(jià)的屬性：自動(dòng)編號(hào)CommentId、圖書編號(hào)、評(píng)論客戶編號(hào)、評(píng)論客戶名、評(píng)論時(shí)間、評(píng)論內(nèi)容、審核標(biāo)志詳細(xì)訂單是針對(duì)每個(gè)商品進(jìn)行下單，通過詳細(xì)訂單不僅可以產(chǎn)看商品信息而且可以看出每個(gè)商品的收發(fā)貨狀態(tài)，訂單包括多個(gè)詳細(xì)訂單此外，由以往的購(gòu)物經(jīng)驗(yàn)可知，一般買書的時(shí)候都會(huì)有一個(gè)臨時(shí)購(gòu)物車，客戶可以把自己想買的書本放入購(gòu)物車，但是購(gòu)物車中的商品只是放入了購(gòu)物車還沒有下單（8）臨時(shí)購(gòu)物車：客戶編號(hào)、圖書編號(hào)、訂購(gòu)數(shù)量、總價(jià)，是否付款實(shí)體間的聯(lián)系:通過以上分析可知，實(shí)體之間的聯(lián)系有：客戶與客戶評(píng)論之間是一對(duì)多關(guān)系（1：n）書籍類別與書籍之間是一對(duì)多關(guān)系（1：n）用戶與訂單之間是一對(duì)多關(guān)系（1：n）訂單與詳細(xì)訂單之間是一對(duì)多關(guān)系（1:n）客戶與訂單之間是一對(duì)多關(guān)系（1:n）三、 E-R圖3.1實(shí)體圖圖書類型圖書類別號(hào)類別名稱圖3-1 圖書類別信息管理員管理員編號(hào)管理員名稱管理員郵箱密碼圖3-2 管理員信息圖 書圖書編號(hào)圖書名稱圖書類型出版社出版日期圖書作者圖書定價(jià)圖書簡(jiǎn)介圖書庫存量圖書成交量圖3-3 圖書信息客 戶客戶編號(hào)客戶名客戶密碼真實(shí)姓名性別地址客戶電話注冊(cè)時(shí)間E-mail圖3-4 客戶信息購(gòu)物車臨時(shí)訂購(gòu)信息客戶編號(hào)圖書編號(hào)訂購(gòu)數(shù)量總價(jià)是否付款圖3-5 購(gòu)物車臨時(shí)訂購(gòu)信息訂 單訂單編號(hào)客戶編號(hào)下單日期總訂購(gòu)數(shù)量送貨方式支付方式收貨人姓名收貨地址收貨人聯(lián)系方式總賣出價(jià)圖3-6 訂單信息詳細(xì)訂單訂單詳細(xì)編號(hào)訂單號(hào)圖書編號(hào)訂購(gòu)數(shù)量發(fā)貨狀態(tài)收貨狀態(tài)賣出總價(jià)圖3-7 訂單詳細(xì)信息圖書評(píng)價(jià)評(píng)價(jià)編號(hào)圖書編號(hào)評(píng)價(jià)客戶編號(hào)評(píng)價(jià)客戶名審核標(biāo)志評(píng)價(jià)內(nèi)容評(píng)價(jià)時(shí)間圖3-8 圖書評(píng)論信息3.2 實(shí)體關(guān)系E-R圖圖書分類圖書參照參照參照評(píng)論詳細(xì)訂單訂單購(gòu)物車參照客戶參照參照1N1NN11NN1N11N圖3-9 網(wǎng)上書店頂層E-R圖四、邏輯結(jié)構(gòu)4.1 邏輯轉(zhuǎn)換 根據(jù)3.2節(jié)中的E-R圖可以將系統(tǒng)中的概念模型轉(zhuǎn)換為具體的表（即關(guān)系）結(jié)構(gòu)，共分為8個(gè)關(guān)系，詳細(xì)信息如下所示：客戶（會(huì)員）（客戶編號(hào)，客戶名，客戶密碼，真實(shí)姓名，性別，客戶電話，E-mail，地址，注冊(cè)時(shí)間）管理員（管理員編號(hào)，管理員名稱，管理員郵箱，密碼）圖書類型（圖書類型編號(hào)，類別名稱）圖書（圖書編號(hào)，圖書名稱，圖書類型，出版社，出版日期，圖書作者，圖書定價(jià)，圖書簡(jiǎn)介，圖書庫存量，圖書成交量）購(gòu)物車零時(shí)訂購(gòu)信息（客戶編號(hào)，圖書編號(hào)，訂購(gòu)數(shù)量，總價(jià)，是否付款）訂單（訂單號(hào)，客戶編號(hào)，下單日期，總訂購(gòu)數(shù)量，送貨方式，支付方式，收貨人姓名，收貨地址，收貨人聯(lián)系方式，總賣出價(jià)）詳細(xì)訂單（詳細(xì)訂單編號(hào)，訂單號(hào)，圖書編號(hào)，訂購(gòu)數(shù)量，發(fā)貨狀態(tài)，收貨狀態(tài)，賣出總價(jià)）圖書評(píng)價(jià)（圖書評(píng)價(jià)編號(hào)，圖書編號(hào)，評(píng)論客戶編號(hào)，評(píng)論客戶名，評(píng)論時(shí)間、評(píng)論內(nèi)容，審核標(biāo)志）4.2 細(xì)化表結(jié)構(gòu) 為方便，根據(jù)上述文字描述，用英文簡(jiǎn)寫為表和列取名，確定列的數(shù)據(jù)類型及必要的約束規(guī)則，給出如下所示數(shù)據(jù)庫表的基本結(jié)構(gòu)及說明：（1）客戶信息表tb_customerinfo列名說明數(shù)據(jù)類型約束CustomerId客戶編號(hào)int主碼（自增字段）CustomerName客戶名稱varchar(20)主碼not nullCustomerPwd客戶密碼varchar(20)not nullCustomertruename真實(shí)姓名varchar(20)not nullCustomerSex性別varchar(2)not null, 取“男”或“女”CustomerTel客戶電話varchar(20)not nullCustomerEmailE-mailvarchar(20)not nullCustomerAddr地址varchar(20)not nullCustomerRegTime注冊(cè)時(shí)間datetimenot null表1 客戶信息表（2）圖書類型信息表tb_booktypeinfo列名說明數(shù)據(jù)類型約束BookTypeId圖書類型編號(hào)int主碼（自增字段）BookTypeName類別名稱varchar(50)not null表2 圖書類型信息表（3）圖書信息表tb_bookinfo列名說明數(shù)據(jù)類型約束BookId圖書編號(hào)int主碼（自增字段）BookTypeId圖書類型intnot null, 引用tb_booktypeinfo的外碼BookName圖書名varchar(20)not nullBookPress出版社varchar(20)not nullBookPubDate出版日期datetimenot nullBookAuthor圖書作者varchar(10)not nullBookPrice圖書定價(jià)moneynot nullBookOutline圖書簡(jiǎn)介varchar(200)not nullBookDealmount成交量intnot nullBookStoremount圖書庫存量intnot null表3 圖書信息表（4）管理員信息表tb_manager列名說明數(shù)據(jù)類型約束AdminId管理員編號(hào)int主碼（自增字段）AdminName管理員名稱varchar(10)not null AdminPwd密碼varchar(20)not null表4 管理員信息表（5）購(gòu)物車臨時(shí)訂購(gòu)信息tb_shopbook列名說明數(shù)據(jù)類型約束CustomerId客戶編號(hào)int主碼（自增字段）BookId圖書編號(hào)int主碼, 引用tb_bookinfo的外碼ordermount訂購(gòu)數(shù)量intnot nullprice總價(jià)moneynot nullispay是否付款varchar(10)not null，默認(rèn)為未付款，取“未付款”或“已付款”表5 購(gòu)物車臨時(shí)訂購(gòu)信息表（6）訂單信息表tb_order列名說明數(shù)據(jù)類型約束OrderId訂單編號(hào)varchar(20)主碼CustomerId客戶編號(hào)intnot null, 引用tb_customerinfo的外碼Orderdate下單日期datetimenot nullOrdermount總訂購(gòu)數(shù)量intnot nullpostmethod送貨方式varchar(100)not nullpaymethod支付方式varchar(100)not nullrecevername收貨人姓名varchar(10)not nullreceveraddr收貨地址varchar(20)not nullrecevertel收貨人電話號(hào)碼varchar(10)not nulltotalprice總賣出價(jià)moneynot null表6 訂單信息表（7）訂單詳細(xì)信息表tb_orderdetail列名說明數(shù)據(jù)類型約束OrderDetailId詳細(xì)訂單編號(hào)int主碼OrderId訂單號(hào)varchar(20)not null, 引用tb_order的外碼BookId圖書編號(hào)intnot nullordermount訂購(gòu)數(shù)量intnot nullpoststatus發(fā)貨狀態(tài)varchar(10)not null，默認(rèn)為未發(fā)貨，取“未發(fā)貨”或“已發(fā)貨”Recevstatus收貨狀態(tài)varchar(10)not null，默認(rèn)為未收貨，取“未收貨”或“已收貨”sigletotalprice賣出總價(jià)moneynot null表7 訂單詳細(xì)信息表（8）圖書評(píng)價(jià)信息表tb_comment列名說明數(shù)據(jù)類型約束CommentId圖書評(píng)價(jià)編號(hào)int主碼（自增字段）BookId圖書編號(hào)intnot null,引用tb_bookinfo的外碼CustomerId評(píng)論客戶編號(hào)intnot null, 引用tb_customerinfo的外碼Customername評(píng)論客戶名varchar(20)not nullCommentdate評(píng)論時(shí)間datetimenot nullCommentcontent評(píng)論內(nèi)容varchar(100)not nullCommentflag審核標(biāo)志varchar(10)not null表8 圖書評(píng)價(jià)信息表五、數(shù)據(jù)庫實(shí)施本章節(jié)主要包含創(chuàng)建表、添加數(shù)據(jù)和創(chuàng)建必要的觸發(fā)器、存儲(chǔ)過程等內(nèi)容。5.1 創(chuàng)建約束條件外鍵關(guān)聯(lián)關(guān)系：(1)為圖書信息表添加圖書分類表的外鍵關(guān)聯(lián)關(guān)系alter table tb_bookinfo add constraint FK_TB_BOOKI_REFERENCE_TB_BOOKT foreign key (BookTypeId) references tb_booktypeinfo (BookTypeId) （）為圖書評(píng)論表添加圖書表的外鍵關(guān)聯(lián)關(guān)系alter table tb_comment add constraint FK_TB_COMME_REFERENCE_TB_BOOKI foreign key (BookId) references tb_bookinfo (BookId) （）為圖書評(píng)論表添加客戶表的外鍵關(guān)聯(lián)關(guān)系alter table tb_comment add constraint FK_TB_COMME_REFERENCE_TB_CUSTO foreign key (CustomerId) references tb_customerinfo (CustomerId) （）為訂單表添加用戶的外鍵關(guān)聯(lián)關(guān)系alter table tb_order add constraint FK_TB_ORDER_REFERENCE_TB_CUSTO foreign key (CustomerId) references tb_customerinfo (CustomerId) （）為詳細(xì)訂單表添加訂單的外鍵關(guān)聯(lián)關(guān)系alter table tb_orderdetail add constraint FK_TB_ORDER_REFERENCE_TB_ORDER foreign key (OrderId) references tb_order (OrderId) （）添加詳細(xì)訂單表與書本指尖的外鍵關(guān)聯(lián)關(guān)系 alter table tb_orderdetail add constraint FK_TB_ORDER_REFERENCE_TB_BOOKI foreign key (BookId) references tb_bookinfo (BookId) （）購(gòu)物車與書本制作間的外鍵關(guān)聯(lián)關(guān)系alter table tb_shopbook add constraint FK_TB_SHOPB_REFERENCE_TB_BOOKI foreign key (BookId) references tb_bookinfo (BookId) （）購(gòu)物車與用戶的外鍵關(guān)系 alter table tb_shopbook add constraint FK_TB_SHOPB_REFERENCE_TB_CUSTO foreign key (CustomerId) references tb_customerinfo (CustomerId) create table tb_reply ( ReplyId int not null, CustomerId int not null, ReplyType varchar(20) not null, Replytitle varchar(20) not null, Replycontent varchar(100) not null, Customername varchar(20) not null, Commentdate datetime not null, CustomerIP varchar(10) not null, constraint PK_TB_REPLY primary key (ReplyId)5.2 添加數(shù)據(jù)（注意添加的先后次序）對(duì)于存在外鍵關(guān)聯(lián)關(guān)系的表，注意添加數(shù)據(jù)的時(shí)候要先添加被關(guān)聯(lián)的表的信息5.3 創(chuàng)建視圖(需要經(jīng)常進(jìn)行的操作)1 定義一個(gè)視圖查看根據(jù)客戶的姓名查詢其購(gòu)物車的信息create view shopping_car asselect CustomerName,BookName,ordermount,price from tb_shopbook,tb_customerinfowhere tb_bookinfo.BookId = tb_shopbook.BookId and tb_shopbook.CustomerId = tb_customerinfo.CustomerId2 定義一個(gè)視圖客戶可以根據(jù)其姓名查詢所買到的圖書的詳細(xì)信息create view book_infoasselect CustomerName,BookName,BookTypeId,BookPress,BookPubDate,BookAuthor,BookPrice,BookOutline from tb_bookinfo ,tb_customerinfo where tb_customerinfo.CustomerId = tb_orderdetail.CustomerId andBookId = (select BookId from tb_orderdetail where Recevstatus=已收貨) insert into tb_manager(AdminName,AdminPwd, AdminFlag) values (admin,admin,1)insert into tb_customerinfot (CustomerName, CustomerPwd, Customertruename, CustomerSex, CustomerTel, CustomerEmail, CustomerAddr, CustomerRegTime, CustomerQues, CustomerAnswer, CustomerLogTime, CustomerLastLogT) values (toy,toy,李明,男,1231628368,,常熟理工,1,2010-12-22 11:00:11:111, 2010-12-22 11:00:11:111)insert into tb_booktypeinfo(BookTypeName) values (科技類)insert into tb_bookinfo values (, 1, php編程寶典 ,清華大學(xué)出版社, 2010-2-1, 16k,第二版,云峰,978-7-900-2131-8,56.00,100,合法哈客戶卡的合法,發(fā)的發(fā),發(fā)的發(fā),57.00,56.50,12,34,0.87,C:/1.jpg,90,2010-1-1 1:1:1:111,精品包裝)insert into tb_order values (, 20101202001, 1 , 2010-12-22 12:00:11:111, 2, ,申通快遞,建行,李明,常熟理工,12313212323,180)insert into tb_orderdetail values (, 2010120200101, 20101202001 ,1, 2, ,云峰,180)insert into tb_parameter values (風(fēng)云網(wǎng)上書店, 熱熔器額外人, 的發(fā)愛的 ,南京十字路口, 331231, 080-21312,copyright ljfl, C:/logo.jpg,,建行、工行,發(fā)的發(fā)放,合法哈客戶卡的合法,發(fā)的發(fā),發(fā)的發(fā),7.00,8:00-12:00,f得法,發(fā)的說法,0.87,的法定)5.4 創(chuàng)建必要觸發(fā)器(1)定義一個(gè)觸發(fā)器，其基本功能是在tb_bookinfo表中修改庫存數(shù)量時(shí)，檢查數(shù)量是否小于0，如果是提示庫存不足，庫存為空。其具體代碼如下所示：create trigger trig1 on tb_bookinfo for update as if update(BookStoremount) begin declare mount int select mount=BookStoremount from inserted if(mount0) begin print 庫存不足，庫存為空 rollback end end(2)定義一個(gè)觸發(fā)器，其基本功能是在填寫訂單和詳細(xì)訂單的時(shí)候相應(yīng)的總價(jià)被計(jì)算處理填入相應(yīng)的位置。其具體代碼如下所示：create trigger trig2 on tb_orderdetail for insert,update as begin declare mount int,price money,sigleprice money,totalprice money set mount=(select ordermount from inserted) set totalprice=(select totalprice from tb_order,inserted where tb_order.orderid=inserted.orderid) set price=(select bookprice from tb_bookinfo,inserted where tb_bookinfo.bookid=inserted.bookid) set sigleprice=mount*price update tb_orderdetail set sigletotalprice=sigleprice where orderdetailid=(select orderdetailid from inserted ) update tb_order set totalprice=(totalprice+sigleprice) where orderid=(select orderid from inserted) end create trigger trig2on tb_orderdetailfor insert,updateas begin declare mount int,price money,sigleprice money,totalprice money set mount=(select ordermount from inserted) set totalprice=(select totalprice from tb_order,inserted where tb_order.orderid=inserted.orderid) set price=(select bookprice from tb_bookinfo,inserted where tb_bookinfo.bookid=inserted.bookid) set sigleprice=mount*priceupdate tb_orderdetail set sigletotalprice=sigleprice where orderdetailid=(select orderdetailid from inserted )update tb_order set totalprice=(totalprice+sigleprice) where orderid=(select orderid from inserted)end go5.5 創(chuàng)建必要存儲(chǔ)過程1) 建立存儲(chǔ)過程customer_book，功能是客戶在網(wǎng)站前臺(tái)根據(jù)客戶名稱來查詢買到的圖書詳細(xì)情況，其實(shí)現(xiàn)代碼如下所示：create procedure customer_book customername varchar(20), bookname varchar(20) out, price money out,ordermount int out, totalprice money out, as select bookname=tb_bookinfo.BookName,price=tb_bookinfo.BookPrice, ordermount=tb_orderdetail.Ordermount,totalprice=tb_orderdetail.totalprice, from tb_bookinfo,tb_customerinfo,tb_orderdetail where tb_customerinfo.CustomerId =tb_order.CustomerId and tb_orderdetail.Recevstatus=已收貨 and tb_bookinfo.BookId=tb_orderdetail.BookId and tb_customerinfo.CustomerId =customername （2）根據(jù)客戶名查詢所買商品的發(fā)貨狀態(tài)create procedure book_poststatecustomername varchar(20),asselect BookName,ordermount,poststatus from tb_orderdetail,tb_bookinfowh