實驗7 數(shù)據(jù)庫設(shè)計.doc

實驗7 數(shù)據(jù)庫設(shè)計（6學時） 實驗?zāi)康呐c要求了解數(shù)據(jù)庫設(shè)計的重要性和數(shù)據(jù)庫設(shè)計在信息系統(tǒng)開發(fā)和建設(shè)中的核心地位。掌握數(shù)據(jù)庫設(shè)計方法和步驟。 實驗任務(wù)在某個RDBMS產(chǎn)品上，選擇合適的應(yīng)用系統(tǒng)開發(fā)工具為某個部門或單位開發(fā)一個數(shù)據(jù)庫應(yīng)用系統(tǒng)。通過實踐，掌握本章介紹的數(shù)據(jù)庫設(shè)計方法，同時培養(yǎng)團隊合作精神。要求56位同學組成一個開發(fā)小組，每位同學承擔不同角色（例如：項目管理員、DBA、系統(tǒng)分析員、系統(tǒng)設(shè)計員、系統(tǒng)開發(fā)員、系統(tǒng)測試員）。具體要求：給出數(shù)據(jù)庫設(shè)計各個階段的詳細設(shè)計報告；寫出系統(tǒng)的主要功能和使用說明；提交運行的系統(tǒng)；寫出收獲和體會，包括已解決和尚未解決的問題，進一步完善的設(shè)想與建議；每個小組進行20分鐘的報告和答辯，講解設(shè)計方案，演示系統(tǒng)運行，匯報分工與合作情況。網(wǎng)上書店數(shù)據(jù)庫設(shè)計一、設(shè)計意義伴隨著信息化的發(fā)展，計算機走進了千家萬戶，人們的交流變得方便，方便了網(wǎng)民的生活！此外，伴隨著卓越、當當、淘寶等網(wǎng)絡(luò)購物平臺的興起，網(wǎng)絡(luò)購物也被網(wǎng)民認可，首先，網(wǎng)上書店方便了網(wǎng)民的生活，更加便捷地、足不出戶便可買到心儀的書籍；其次，網(wǎng)上購書減少了許多中間銷售代理，因此價格會比較低，對廣大網(wǎng)民的好處是非常明顯的；再者，網(wǎng)上書店是信息化購物，基本上所有操作都是通過計算機程序自動完成的，因此大大降低了勞力的消耗；最后，電子商務(wù)與出版發(fā)行業(yè)的結(jié)合使發(fā)展網(wǎng)上書店及研究網(wǎng)上書店具有雙重意義，充分結(jié)合互聯(lián)網(wǎng)銷售的特點探索圖書發(fā)行的新渠道和新模式，是對電子商務(wù)科學的充實和補充，更是對出版發(fā)行業(yè)發(fā)行渠道和市場的開拓和創(chuàng)新。數(shù)據(jù)庫對于一個系統(tǒng)的意義是相當重要的，它關(guān)系到整個項目的整體流程，整個網(wǎng)站的后期維護便捷與否，從長遠利益來看，數(shù)據(jù)庫的設(shè)計是至關(guān)重要的。二、需求分析2.1系統(tǒng)功能分析本實驗將設(shè)計出一個網(wǎng)上書店數(shù)據(jù)庫，其要求簡要如下：（1）系統(tǒng)用戶由兩類組成：管理員和客戶。（2）管理員負責后臺管理： 圖書信息的維護（增、刪、改），圖書要進行分類管理； 客戶信息的維護； 訂單的確認和刪除。（3）讀者可以查詢圖書的基本信息，可以維護自己的信息，可以進行網(wǎng)上訂書，可以查詢訂單處理的情況。2.2 數(shù)據(jù)需求系統(tǒng)共有七個實體：客戶、管理員、圖書類型、圖書、訂單、詳細訂單、圖書評價。（1）會員的屬性：自動編號CustomerId、客戶名、客戶密碼、真實姓名、性別、客戶電話、E-mail、地址、注冊時間（2）管理員的屬性：自動編號AdminId、管理員名稱、管理員郵箱、密碼（3）圖書類型的屬性：自動編號BookTypeId、類別名稱（4）圖書的屬性：自動編號BookId、圖書名稱、圖書類型、出版社、出版日期、圖書作者、圖書定價、圖書簡介、圖書庫存量、圖書成交量（5）訂單的屬性：自動編號OrderId、客戶編號、下單日期、總訂購數(shù)量、送貨方式、支付方式、收貨人姓名、收貨地址、收貨人聯(lián)系方式、總賣出價（6）詳細訂單屬性：自動編號Id、詳細訂單OrderDetailId、訂單號OrderId、圖書編號、訂購數(shù)量、發(fā)貨狀態(tài)、收貨狀態(tài)、賣出總價（7）圖書評價的屬性：自動編號CommentId、圖書編號、評論客戶編號、評論客戶名、評論時間、評論內(nèi)容、審核標志詳細訂單是針對每個商品進行下單，通過詳細訂單不僅可以產(chǎn)看商品信息而且可以看出每個商品的收發(fā)貨狀態(tài)，訂單包括多個詳細訂單此外，由以往的購物經(jīng)驗可知，一般買書的時候都會有一個臨時購物車，客戶可以把自己想買的書本放入購物車，但是購物車中的商品只是放入了購物車還沒有下單（8）臨時購物車：客戶編號、圖書編號、訂購數(shù)量、總價，是否付款實體間的聯(lián)系:通過以上分析可知，實體之間的聯(lián)系有：客戶與客戶評論之間是一對多關(guān)系（1：n）書籍類別與書籍之間是一對多關(guān)系（1：n）用戶與訂單之間是一對多關(guān)系（1：n）訂單與詳細訂單之間是一對多關(guān)系（1:n）客戶與訂單之間是一對多關(guān)系（1:n）三、 E-R圖3.1實體圖圖書類型圖書類別號類別名稱圖3-1 圖書類別信息管理員管理員編號管理員名稱管理員郵箱密碼圖3-2 管理員信息圖 書圖書編號圖書名稱圖書類型出版社出版日期圖書作者圖書定價圖書簡介圖書庫存量圖書成交量圖3-3 圖書信息客 戶客戶編號客戶名客戶密碼真實姓名性別地址客戶電話注冊時間E-mail圖3-4 客戶信息購物車臨時訂購信息客戶編號圖書編號訂購數(shù)量總價是否付款圖3-5 購物車臨時訂購信息訂 單訂單編號客戶編號下單日期總訂購數(shù)量送貨方式支付方式收貨人姓名收貨地址收貨人聯(lián)系方式總賣出價圖3-6 訂單信息詳細訂單訂單詳細編號訂單號圖書編號訂購數(shù)量發(fā)貨狀態(tài)收貨狀態(tài)賣出總價圖3-7 訂單詳細信息圖書評價評價編號圖書編號評價客戶編號評價客戶名審核標志評價內(nèi)容評價時間圖3-8 圖書評論信息3.2 實體關(guān)系E-R圖圖書分類圖書參照參照參照評論詳細訂單訂單購物車參照客戶參照參照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個關(guān)系，詳細信息如下所示：客戶（會員）（客戶編號，客戶名，客戶密碼，真實姓名，性別，客戶電話，E-mail，地址，注冊時間）管理員（管理員編號，管理員名稱，管理員郵箱，密碼）圖書類型（圖書類型編號，類別名稱）圖書（圖書編號，圖書名稱，圖書類型，出版社，出版日期，圖書作者，圖書定價，圖書簡介，圖書庫存量，圖書成交量）購物車零時訂購信息（客戶編號，圖書編號，訂購數(shù)量，總價，是否付款）訂單（訂單號，客戶編號，下單日期，總訂購數(shù)量，送貨方式，支付方式，收貨人姓名，收貨地址，收貨人聯(lián)系方式，總賣出價）詳細訂單（詳細訂單編號，訂單號，圖書編號，訂購數(shù)量，發(fā)貨狀態(tài)，收貨狀態(tài)，賣出總價）圖書評價（圖書評價編號，圖書編號，評論客戶編號，評論客戶名，評論時間、評論內(nèi)容，審核標志）4.2 細化表結(jié)構(gòu) 為方便，根據(jù)上述文字描述，用英文簡寫為表和列取名，確定列的數(shù)據(jù)類型及必要的約束規(guī)則，給出如下所示數(shù)據(jù)庫表的基本結(jié)構(gòu)及說明：（1）客戶信息表tb_customerinfo列名說明數(shù)據(jù)類型約束CustomerId客戶編號int主碼（自增字段）CustomerName客戶名稱varchar(20)主碼not nullCustomerPwd客戶密碼varchar(20)not nullCustomertruename真實姓名varchar(20)not nullCustomerSex性別varchar(2)not null, 取“男”或“女”CustomerTel客戶電話varchar(20)not nullCustomerEmailE-mailvarchar(20)not nullCustomerAddr地址varchar(20)not nullCustomerRegTime注冊時間datetimenot null表1 客戶信息表（2）圖書類型信息表tb_booktypeinfo列名說明數(shù)據(jù)類型約束BookTypeId圖書類型編號int主碼（自增字段）BookTypeName類別名稱varchar(50)not null表2 圖書類型信息表（3）圖書信息表tb_bookinfo列名說明數(shù)據(jù)類型約束BookId圖書編號int主碼（自增字段）BookTypeId圖書類型intnot null, 引用tb_booktypeinfo的外碼BookName圖書名varchar(20)not nullBookPress出版社varchar(20)not nullBookPubDate出版日期datetimenot nullBookAuthor圖書作者varchar(10)not nullBookPrice圖書定價moneynot nullBookOutline圖書簡介varchar(200)not nullBookDealmount成交量intnot nullBookStoremount圖書庫存量intnot null表3 圖書信息表（4）管理員信息表tb_manager列名說明數(shù)據(jù)類型約束AdminId管理員編號int主碼（自增字段）AdminName管理員名稱varchar(10)not null AdminPwd密碼varchar(20)not null表4 管理員信息表（5）購物車臨時訂購信息tb_shopbook列名說明數(shù)據(jù)類型約束CustomerId客戶編號int主碼（自增字段）BookId圖書編號int主碼, 引用tb_bookinfo的外碼ordermount訂購數(shù)量intnot nullprice總價moneynot nullispay是否付款varchar(10)not null，默認為未付款，取“未付款”或“已付款”表5 購物車臨時訂購信息表（6）訂單信息表tb_order列名說明數(shù)據(jù)類型約束OrderId訂單編號varchar(20)主碼CustomerId客戶編號intnot null, 引用tb_customerinfo的外碼Orderdate下單日期datetimenot nullOrdermount總訂購數(shù)量intnot nullpostmethod送貨方式varchar(100)not nullpaymethod支付方式varchar(100)not nullrecevername收貨人姓名varchar(10)not nullreceveraddr收貨地址varchar(20)not nullrecevertel收貨人電話號碼varchar(10)not nulltotalprice總賣出價moneynot null表6 訂單信息表（7）訂單詳細信息表tb_orderdetail列名說明數(shù)據(jù)類型約束OrderDetailId詳細訂單編號int主碼OrderId訂單號varchar(20)not null, 引用tb_order的外碼BookId圖書編號intnot nullordermount訂購數(shù)量intnot nullpoststatus發(fā)貨狀態(tài)varchar(10)not null，默認為未發(fā)貨，取“未發(fā)貨”或“已發(fā)貨”Recevstatus收貨狀態(tài)varchar(10)not null，默認為未收貨，取“未收貨”或“已收貨”sigletotalprice賣出總價moneynot null表7 訂單詳細信息表（8）圖書評價信息表tb_comment列名說明數(shù)據(jù)類型約束CommentId圖書評價編號int主碼（自增字段）BookId圖書編號intnot null,引用tb_bookinfo的外碼CustomerId評論客戶編號intnot null, 引用tb_customerinfo的外碼Customername評論客戶名varchar(20)not nullCommentdate評論時間datetimenot nullCommentcontent評論內(nèi)容varchar(100)not nullCommentflag審核標志varchar(10)not null表8 圖書評價信息表五、數(shù)據(jù)庫實施本章節(jié)主要包含創(chuàng)建表、添加數(shù)據(jù)和創(chuàng)建必要的觸發(fā)器、存儲過程等內(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) （）為圖書評論表添加圖書表的外鍵關(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) （）為圖書評論表添加客戶表的外鍵關(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) （）為詳細訂單表添加訂單的外鍵關(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) （）添加詳細訂單表與書本指尖的外鍵關(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) （）購物車與書本制作間的外鍵關(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) （）購物車與用戶的外鍵關(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ù)（注意添加的先后次序）對于存在外鍵關(guān)聯(lián)關(guān)系的表，注意添加數(shù)據(jù)的時候要先添加被關(guān)聯(lián)的表的信息5.3 創(chuàng)建視圖(需要經(jīng)常進行的操作)1 定義一個視圖查看根據(jù)客戶的姓名查詢其購物車的信息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 定義一個視圖客戶可以根據(jù)其姓名查詢所買到的圖書的詳細信息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編程寶典 ,清華大學出版社, 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 (風云網(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)定義一個觸發(fā)器，其基本功能是在tb_bookinfo表中修改庫存數(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)定義一個觸發(fā)器，其基本功能是在填寫訂單和詳細訂單的時候相應(yīng)的總價被計算處理填入相應(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)建必要存儲過程1) 建立存儲過程customer_book，功能是客戶在網(wǎng)站前臺根據(jù)客戶名稱來查詢買到的圖書詳細情況，其實現(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