【正文】 notnull,7. Customernamevarchar(20)notnull,8. Commentdatedatetimenotnull,9. CustomerIPvarchar(10)notnull,10. constraintPK_TB_REPLYprimarykey(ReplyId)11. ) alter table tb_bookinfo add constraint FK_TB_BOOKI_REFERENCE_TB_BOOKT foreign key (BookTypeId) references tb_booktypeinfo (BookTypeId) go alter table tb_ment add constraint FK_TB_COMME_REFERENCE_TB_BOOKI foreign key (BookId) references tb_bookinfo (BookId) go alter table tb_ment add constraint FK_TB_COMME_REFERENCE_TB_CUSTO foreign key (CustomerId) references tb_customerinfo (CustomerId) go alter table tb_order add constraint FK_TB_ORDER_REFERENCE_TB_CUSTO foreign key (CustomerId) references tb_customerinfo (CustomerId) go alter table tb_orderdetail add constraint FK_TB_ORDER_REFERENCE_TB_ORDER foreign key (OrderId) references tb_order (OrderId) go alter table tb_orderdetail add constraint FK_TB_ORDER_REFERENCE_TB_BOOKI foreign key (BookId) references tb_bookinfo (BookId) go alter table tb_reply add constraint FK_TB_REPLY_REFERENCE_TB_CUSTO foreign key (CustomerId) references tb_customerinfo (CustomerId) go alter table tb_shopbook add constraint FK_TB_SHOPB_REFERENCE_TB_BOOKI foreign key (BookId) references tb_bookinfo (BookId) go alter table tb_shopbook add constraint FK_TB_SHOPB_REFERENCE_TB_CUSTO foreign key (CustomerId) references tb_customerinfo (CustomerId) go 1. insertintotb_manager(AdminName,AdminPwd,AdminFlag)values(’admin’,’admin’,1)2. insertintotb_customerinfot(CustomerName,CustomerPwd,Customertruename,CustomerSex,CustomerTel,CustomerEmail,CustomerAddr,CustomerRegTime,CustomerQues,CustomerAnswer,CustomerLogTime,CustomerLastLogT)values(‘toy’,’toy’,’李明’,’男’,’1231628368’,’a122390@’,’常熟理工’,’1’,’’,’’,’2010122211:00:11:111’,’2010122211:00:11:111’)3. insertintotb_booktypeinfo(BookTypeName)values(’科技類’)4. insertintotb_bookinfovalues(‘’,’1’,’php編程寶典’,’清華大學(xué)出版社’,’201021’,’16k’,’第二版’,’云峰’,’’,’978790021318’,’’,’100’,’合法哈客戶卡的合法’,’發(fā)的發(fā)’,’發(fā)的發(fā)’,’’,’’,’12’,’34’,’’,’C://’,’90’,’2010111:1:1:111’,’精品包裝’)5. insertintotb_ordervalues(‘’,’20101202001’,’1’,’2010122212:00:11:111’,’2’,’’,’申通快遞’,’建行’,’’,’李明’,’常熟理工’,’12313212323’,’’,’180’)6. insertintotb_orderdetailvalues(‘’,’2010120200101’,’20101202001’,’1’,’2’,’’,’’,’云峰’,’’,’180’)7. insertintotb_parametervalues(‘風(fēng)云網(wǎng)上書店’,’熱熔器額外人’,’的發(fā)愛的’,’南京十字路口’,’331231’,’08021312’,’@copyrightljfl’,C://’,’’,’建行、工行’,’發(fā)的發(fā)放’,’合法哈客戶卡的合法’,’發(fā)的發(fā)’,’發(fā)的發(fā)’,’’,’8:0012:00’,’f得法’,’發(fā)的說法’,’’,’的法定’) 定義一個觸發(fā)器，其基本功能是在tb_bookinfo表中修改庫存數(shù)量時，檢查數(shù)量是否小于0，如果是提示庫存不足，庫存為空。其具體代碼如下所示： create trigger trig1 on tb_bookinfo for update as if(update(BookStoremount)) begin declare @mount int set @mount=(select BookStoremount from deleted) if(@mount0) begin print 39。庫存不足，庫存為空39。 rollback end end ，其基本功能是在填寫訂單和詳細(xì)訂單的時候相應(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 =) set @price=(select bookprice from tb_bookinfo,inserted where =) 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 go ，功能是客戶在網(wǎng)站前臺根據(jù)客戶名稱來查詢買到的圖書詳細(xì)情況，其實現(xiàn)代碼如下所示： create procedure customer_book @customername varchar(20), @orderdate datetime out, @bookname varchar(20) out, @price money out,@orsermount int out, @sigletotalprice money out, @totalprice money out, @pstatus varchar(10) out, @rstatus varchar(10) out as select @orderdate=orderdate,@bookname=bookname,@price=bookprice, @sigletotalprice=sigletotalprice,@totalprice=totalprice, @pstatus=poststatus,@rstatus=recevstatus from tb_bookinfo,tb_customerinfo,tb_order,tb_orderdetail where = and = and =@customername ，功能是管理員在網(wǎng)站后臺根據(jù)客戶名稱來查詢買到的圖書詳細(xì)情況，其實現(xiàn)代碼如下所示： create procedure admin_book @customername varchar(20), @orderdate datetime out, @bookname varchar(20) out, @message varchar(100) out, @memo varchar(100) out, @recevername varchar(10)out, @receveraddr varchar(20) out, @recevertel varchar(10) out, @price money out, @orsermount int out, @sigletotalprice money out, @totalprice money out, @pstatus varchar(10) out, @rstatus varchar(10) out as select @orderdate=orderdate,@bookname=bookname,@message=message, @memo=memo,@recevername=recevername,@receveraddr=receveraddr, @recevertel=recevertel,@price=bookprice,@sigletotalprice=sigletotalprice, @totalprice=totalprice,@pstatus=poststatus,@rstatus=recevstatus from tb_bookinfo,tb_customerinfo,tb_order,tb_orderdetail where = and = and =@customername 5 總結(jié)這次網(wǎng)上書店管理系統(tǒng)的設(shè)計花了我不少的時間，在沒有設(shè)計這個系統(tǒng)前，我一直覺得編碼很難，但是經(jīng)過這次系統(tǒng)設(shè)計，我覺得系統(tǒng)分析階和設(shè)計階段是最難的。在一開始做這個系統(tǒng)設(shè)計前，由于缺乏設(shè)計經(jīng)驗，我并不知道該怎么設(shè)計一個系統(tǒng)，從哪里入手設(shè)計一個系統(tǒng)，所以心里很浮躁。后來，我去圖書館借了幾本有關(guān)系統(tǒng)分析和設(shè)計方面的書，又在網(wǎng)上下載了一些資料，經(jīng)過我對書本上的知識的學(xué)習(xí)和對網(wǎng)上資料的研究，我終于弄清楚了該怎么設(shè)計分析設(shè)計一個系統(tǒng)通過本次實驗使我對數(shù)據(jù)庫有了一個更加全面，更加深入的了解，也可以說是對本學(xué)期所學(xué)的數(shù)據(jù)庫知識的一個總結(jié)，這次實驗不像以前一樣，只是對數(shù)據(jù)庫某一部分知識的練習(xí)鞏固，不僅僅是設(shè)計幾張表，設(shè)計幾個存儲過程，觸發(fā)器，而是對這些知識的一個整合，是一次綜合的練習(xí)，使我對設(shè)計一個數(shù)據(jù)庫的流程有了一個了解和掌握。攀枝花學(xué)院本科課程設(shè)計報告（論文） 參 考 文 獻(xiàn)[1] 王珊，薩師煊. 數(shù)據(jù)庫系統(tǒng)概論[M].北京:（2010重?。?[2] 白尚旺，[M].北京:.[3] SQL server 2008數(shù)據(jù)倉庫與Analysis Services. Bain [4] 王珊，數(shù)據(jù)庫技術(shù)與聯(lián)機(jī)分析處理. 北京科學(xué)出版社 1998[5]姚永一，SQL Server數(shù)據(jù)庫實用教程，北京：電子工業(yè)出版社,2010. [6]高云，崔艷春，SQL Server 2008數(shù)據(jù)庫技術(shù)實用教程，北京：清華大學(xué)出版社，2011 [7]何玉潔，梁琦，數(shù)據(jù)庫原理與應(yīng)用（第二版），北京：機(jī)械工業(yè)出版社，2011 [8]壯志劍，數(shù)據(jù)庫原理與SQL Server，北京：高等教育出版社，2008 攀枝花學(xué)院本科課程設(shè)計報告（論文） 67