數(shù)據(jù)課程設(shè)計(jì)-網(wǎng)上書店管理系統(tǒng)

網(wǎng)上書店管理系統(tǒng)一、設(shè)計(jì)說明：本系統(tǒng)針對于使用支付寶平臺(tái)的獨(dú)立網(wǎng)上書店，非淘寶網(wǎng)店。二、實(shí)體與關(guān)系：實(shí)體：書籍、用戶、供給商、月本錢記錄、日營收記錄、月營收記錄、網(wǎng)店信息、郵費(fèi)信息、省份分區(qū)。關(guān)系：訂購、供貨、瀏覽、評論等E-R圖詳見E-R.pdf三、E-R圖轉(zhuǎn)換為關(guān)系模式：所有實(shí)體轉(zhuǎn)化為獨(dú)立的表，供給關(guān)系、瀏覽關(guān)系、訂購關(guān)系均為m：n，這些關(guān)系也轉(zhuǎn)換為獨(dú)立的表，以下為具體關(guān)系模式結(jié)構(gòu)：1.書籍表結(jié)構(gòu)：工程名列名數(shù)據(jù)類型可空默認(rèn)值說明國際標(biāo)準(zhǔn)書號ISBNCHAR(13)否主鍵書籍名稱BOOKNAMEVARCHAR(50)否作者AUTHORVARCHAR(20)否主類型MAINTYPEVARCHAR(20)否子類型SUBTYPEVARCHAR(20)否進(jìn)價(jià)BUYINGPRICEDISCOUNT否大于等于0售價(jià)PRICEMONEY否大于等于0折扣DISCOUNTFLOAT可10.00～1.00封面COVERIMAGE否出版社PUBLISHERSVARCHAR(20)否出版時(shí)間PUBLICATIONTIMEDATETIME否頁數(shù)PAGESINT否大于0#版次印次VERSIONPRINTINGNUMVARCHAR(20)否裝幀APPAREACEVARCHAR(20)否語種LANGUAGEVARCHAR(20)可簡體中文庫存STOCKINT可0大于等于0評分SCOREFLOAT(2)可大于等于0，且小于等于102.消費(fèi)者表結(jié)構(gòu)：工程名列名數(shù)據(jù)類型可空默認(rèn)值說明用戶IDCUSTOMERIDCHAR(18)否主鍵，限制為18個(gè)0～9字符用戶姓名CUSTOMERNAMEVARCHAR(20)否性別GENDERBIT是00：男1：女用戶密碼PASSWORDVARCHAR(20)否籍貫NATIVEPLACEVARCHAR(20)否消費(fèi)者等級LEVELTINYINT是0大于等于0，且小于等于10成長值GROWTHVALUEINT是0大于等于0##聯(lián)系方式TELVARCHAR(20)否3.進(jìn)貨記錄表結(jié)構(gòu)：工程名列名數(shù)據(jù)類型可空默認(rèn)值說明國際標(biāo)準(zhǔn)書號ISBNCHAR(13)否主鍵日期DATEDATETIME否主鍵進(jìn)價(jià)BUYINGPRICEMONEY否數(shù)量BUYINGAMOUNTINT否供給商編號SUPPLIERNO.CHAR(6)否主鍵4.進(jìn)貨單表結(jié)構(gòu)：工程名列名數(shù)據(jù)類型可空默認(rèn)值說明進(jìn)貨單編號RECEIPT-NOINT否主鍵，自增日期DATEDATETIME否條目ITEMTEXT否總本錢金額AMOUNTOFMONEYMONEY否大于0總數(shù)量TOTALQUANTITYINT否備注NOTEVARCHAR〔100〕是5,供給商表結(jié)構(gòu)：工程名列名數(shù)據(jù)類型可空默認(rèn)值說明供給商編號SUPPLIERNO.CHAR(6)否主鍵供給商名稱SUPPLIERNAMEVARCHAR(20)否地址SUPPLIERADDRESSVARCHAR(20)否聯(lián)系信息CONTACTINFOVARCHAR(20)否信用等級CREDITLEVELTINYINT是5大于等于0，且小于等于106.評論記錄表結(jié)構(gòu)：工程名列名數(shù)據(jù)類型可空默認(rèn)值說明用戶idCUSTOMERIDCHAR〔18〕否主鍵、外鍵國際標(biāo)準(zhǔn)書號ISBNCHAR〔13〕否主鍵時(shí)間TIMEDATETIME否評論COMMENTVARCHAR〔255〕否評分SCOREFLOAT(2)否大于等于0，且小于等于107.訂購記錄表結(jié)構(gòu)：工程名列名數(shù)據(jù)類型可空默認(rèn)值說明時(shí)間TIMEDATETIME否主鍵國際標(biāo)準(zhǔn)書號ISBNCHAR〔13〕否主鍵用戶IDCUSTOMERIDCHAR〔18〕否主鍵、外鍵用戶姓名CUSTOMERNAMEVARCHAR(20)否##聯(lián)系方式TELVARCHAR(20)否進(jìn)價(jià)BUYINGPRICEMONEY否大于0價(jià)格PRICEMONEY否大于0數(shù)目AMOUNTOFBOOKINT否大于08.訂單表結(jié)構(gòu)工程名列名數(shù)據(jù)類型可空默認(rèn)值說明訂單ORDERFORMNOINT否主鍵，自增時(shí)間TIMEDATETIME否條目ITEMTEXT否用戶IDCUSTOMERIDCHAR〔18〕否用戶姓名CUSTOMERNAMEVARCHAR(20)否##聯(lián)系方式TELVARCHAR(20)否數(shù)目AMOUNTOFBOOKINT否大于0地址ADDRESSVARCHAR(20)否郵費(fèi)POSTAGEMONEY否大于0金額AMOUNTOFMONEYMONEY否大于0折扣DISCOUNTFLOAT可10.00～1.00運(yùn)送天數(shù)DAYSTINYINT可快遞號EXPRESSNOVARCHAR〔15〕否狀態(tài)STATETINYINT否大于等于0，且小于等于49.瀏覽記錄表結(jié)構(gòu)：工程名列名數(shù)據(jù)類型可空默認(rèn)值說明國際標(biāo)準(zhǔn)書號ISBNCHAR(13)否時(shí)間TIMEDATETIME否用戶IDCUSTOMER_IDCHAR(18)否游客10.日營收記錄表結(jié)構(gòu)：工程名列名數(shù)據(jù)類型可空默認(rèn)值說明日期DATEDATETIME否主鍵日營業(yè)額DAILYTURNOVERMONEY是0大于等于0日總銷量DAILYSALEAMOUNTINT是0大于等于0毛利潤DAILYGROSSPROFITMONEY是0大于等于0實(shí)際到帳金額ACTUALINCOMEMONEY是0大于等于011.月營收記錄表結(jié)構(gòu)：工程名列名數(shù)據(jù)類型可空默認(rèn)值說明月份MONTHDATETIME否主鍵月營業(yè)額MONTHLYTURNOVERMONEY是0大于等于0月總銷量MONTHLYSALEAMOUNTINT是0大于等于0毛利潤MONTHLYGROSSPROFITMONEY是0大于等于0凈利潤MONTHLYRETAINEDPROFITMONEY是0大于等于0實(shí)際到帳金額ACTUALINCOMEMONEY是0大于等于012.月本錢記錄表結(jié)構(gòu)：工程名列名數(shù)據(jù)類型可空默認(rèn)值說明月份MONTHDATETIME否主鍵電費(fèi)ELECTRICCHARGEMONEY是0大于等于0稅款TAXPAYMENTMONEY是0大于等于0支付平臺(tái)使用費(fèi)CHARGEFORUSEMONEY是0大于等于0總運(yùn)費(fèi)TOTALFREIGHTMONEY是0大于等于0其它費(fèi)用OTHER_PAYMENTMONEY是0大于等于013.省份分區(qū)表結(jié)構(gòu):工程名列名數(shù)據(jù)類型可空默認(rèn)值說明省份PROVINCEVARCHAR(20)否分區(qū)PARTITIONTINYINT否主鍵，大于014.郵費(fèi)信息表結(jié)構(gòu):工程名列名數(shù)據(jù)類型可空默認(rèn)值說明類型TYPEVARCHAR(20)否主鍵分區(qū)PARTITIONTINYINT否主鍵，外鍵到貨天數(shù)ARRIVALDAYSTINYINT否大于0首重重量FIRSTWEIGHTFLOAT(2)否大于0首重費(fèi)用FWFEEMONEY否大于0續(xù)重重量ADDWEIGHTFLOAT(2)否大于0續(xù)重費(fèi)用AWFEEMONEY否大于015.網(wǎng)店信息表結(jié)構(gòu):工程名列名數(shù)據(jù)類型可空默認(rèn)值說明信息日期DATEDATETIME否主鍵網(wǎng)店簡介INTRODUCTIONTEXT否相關(guān)圖片RELATEDIMAGESIMAGE否店主STOREKEEPERVARCHAR(20)否實(shí)體店鋪地址PHYSICLSTOREADDRESSVARCHAR(20)否聯(lián)系TELVARCHAR(20)否電子郵箱EMAILVARCHAR(30)否四、視圖：A：創(chuàng)立書籍視圖,屏蔽進(jìn)價(jià)等重要信息USEBOOKSTOREGOCREATEVIEWBOOK_VIEW AS SELECTISBN,BOOKNAME,AUTHOR,PRICE,COVER,PUBLISHERS,PUBLICATION_TIME,PAGES,VERSION_PRINTING_NUM,APPAREACE,LANGUAGE,STOCK,SCORE FROMBOOKGOB：創(chuàng)立書籍概覽視圖，便于用戶瀏覽CREATEVIEWBOOK_VIEW_SIMPLE AS SELECTBOOKNAME,AUTHOR,PRICE,COVER,STOCK,SCORE FROMBOOKGOC：創(chuàng)立書籍分類視圖CREATEVIEWBOOK_VIEW_IT AS SELECTBOOKNAME,AUTHOR,PRICE,COVER,STOCK,SCORE FROMBOOK WHERESUBTYPE='IT界'GOD：創(chuàng)立即時(shí)好評榜TOP10書籍視圖，便于用戶了解熱門書籍CREATEVIEWTOP10_SCORE AS SELECTTOP10* FROMBOOK_VIEW WHERESCOREISNOTNULL ORDERBYSCOREDESCGOD：創(chuàng)立打折促銷類書籍視圖〔增加了顯示瀏覽次數(shù)，多表視圖〕 CREATEVIEW BOOK__PROMOTION AS SELECTTOP100BOOKNAMEAS'書名',AUTHORAS'作者',SCOREAS'評分',PRICEAS'價(jià)格',DISCOUNTAS'折扣',COUNT(BOOK.ISBN)AS'瀏覽次數(shù)' FROMBOOK,BROWSE_RECORD WHEREBOOK.ISBN=BROWSE_RECORD.ISBNANDDISCOUNT<1.0 GROUPBYSCORE,BOOK.ISBN,AUTHOR,BOOKNAME,PRICE,DISCOUNT ORDERBYSCOREDESC五、索引局部：A：在書籍表上創(chuàng)立索引，便于加快查找速度CREATEINDEXBOOK_INDEX ONBOOK(BOOKNAME) WITH(DROP_EXISTING=OFF)B：在月營收表上，創(chuàng)立唯一索引，加快查找速度。CREATEUNIQUECLUSTEREDINDEXMONTHLY_REVENUE_INDEX ONMONTHLY_REVENUE(MONTH)六、存儲(chǔ)過程：1.按類別搜索/*搜索某類書的存儲(chǔ)過程*/CREATEPROCEDURESEARCH_BOOK_BYTYPE@TYPEVARCHAR(20) AS SELECTISBN,BOOKNAME,AUTHOR,PRICE,COVER,PUBLISHERS,PUBLICATION_TIME,PAGES,VERSION_PRINTING_NUM,APPAREACE,LANGUAGE,STOCK,SCORE FROMBOOK WHEREMAINTYPE=@TYPEORSUBTYPE=@TYPEEXECUTESEARCH_BOOK_BYTYPE'愛情'執(zhí)行結(jié)果：2.按書名模糊匹配/*書名模糊搜索*/CREATEPROCEDURESEARCH_BOOK@BOOKNAMEVARCHAR(20) AS SELECTISBN,BOOKNAME,AUTHOR,PRICE,COVER,PUBLISHERS,PUBLICATION_TIME,PAGES,VERSION_PRINTING_NUM,APPAREACE,LANGUAGE,STOCK,SCORE FROMBOOK WHEREBOOKNAMELIKE'%'+@BOOKNAME+'%'GOEXECUTESEARCH_BOOK'史'執(zhí)行結(jié)果：3.用存儲(chǔ)過程查看某一用戶的購物歷史記錄CREATEPROCEDURESHOPPING_RECORD@CUSTOMER_IDCHAR(18) AS SELECTTIME,ITEM,AMOUNT_OF_BOOK,AMOUNT_OF_MONEY FROMORDER_FORM WHERECUSTOMER_ID=@CUSTOMER_ID 執(zhí)行結(jié)果：4.創(chuàng)立生成收貨單存儲(chǔ)過程,因?yàn)樾枰獙⒔Y(jié)果集作為某一列的值,故將結(jié)果集先轉(zhuǎn)化為字符串CREATEPROCEDUREMAKE_RECEIT@DATEDATETIME,@NOTEVARCHAR(255) AS /*將結(jié)果集先轉(zhuǎn)化為字符串*/ DECLARE@REC_CURSORCURSOR SET@REC_CURSOR=CURSORFORWARD_ONLYSTATIC FOR SELECTBOOKNAME FROMSUPPLY_RECORD WHERECONVERT(VARCHAR(10),DATE,120)=CONVERT(VARCHAR(10),@DATE,120) OPEN@REC_CURSOR DECLARE@TMPVARCHAR(20),@ITEMVARCHAR(20),@TOTAL_QUANTITYINT,@AMOUNT_OF_MONEYMONEY SET@ITEM='' FETCHNEXTFROM@REC_CURSORINTO@TMP WHILE(@@FETCH_STATUS=0) BEGIN SET@ITEM=@ITEM+@TMP FETCHNEXTFROM@REC_CURSORINTO@TMP END /**/ SELECT@AMOUNT_OF_MONEY=SUM(BUYING_PRICE*BUYING_AMOUNT),@TOTAL_QUANTITY=SUM(BUYING_AMOUNT) FROMSUPPLY_RECORD WHERECONVERT(VARCHAR(10),DATE,120)=CONVERT(VARCHAR(10),@DATE,120) INSERTINTORECEIPT(DATE,ITEM,AMOUNT_OF_MONEY,TOTAL_QUANTITY,NOTE) VALUES(@DATE,@ITEM,@AMOUNT_OF_MONEY,@TOTAL_QUANTITY,@NOTE) CLOSE@REC_CURSOR DEALLOCATE@REC_CURSOR EXECUTEMAKE_RECEIT'2011-11-23','GSGSH' 執(zhí)行結(jié)果：七、完整性約束主鍵、外鍵及check約束規(guī)那么如下所示：A：B：限制輸入值，保證其為有效的身份證號。相關(guān)語句：CUSTOMER_IDCHAR(18)NOTNULLPRIMARYKEYCHECK(CUSTOMER_IDLIKE'[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]')C：相關(guān)語句：PRIMARYKEY(DATE,ISBN,SUPPLIER_NO)D：默認(rèn)值約束八、自定義函數(shù)：A：用內(nèi)嵌表值函數(shù)實(shí)現(xiàn)搜索某一作者的所有書籍。CREATEFUNCTIONSEARCH_BOOK_BYAUTHOR(@AUTHORVARCHAR(20))RETURNSTABLEASRETURN( SELECT* FROMBOOK_VIEW WHEREAUTHOR=@AUTHOR)SELECT*FROMBOOKSTORE.[DBO].SEARCH_BOOK_BYAUTHOR('汪遠(yuǎn)')執(zhí)行結(jié)果：B：用戶自定義函數(shù)，求某用戶的成長值〔每消費(fèi)10元，增加1點(diǎn)成長值〕CREATEFUNCTIONCOMPUTE_GROWTH_VALUE(@CUSTOMER_IDCHAR(18))RETURNSINT AS BEGIN DECLARE@GROWTH_VALUEINT SELECT@GROWTH_VALUE=SUM(AMOUNT_OF_MONEY)/10 FROMORDER_FORM RETURN@GROWTH_VALUE ENDDECLARE@V1INTSELECT@V1AS'成長值'執(zhí)行結(jié)果：九、觸發(fā)器：A：用戶對書籍進(jìn)行評分后，評論表中score列的值改變，引起書籍表中數(shù)據(jù)的改變，所以用觸發(fā)器來處理這種關(guān)聯(lián)。CREATETRIGGERUP_SCORE ONCOMMENTAFTERINSERT AS BEGIN DECLARE@OLD_VALUEFLOAT(2),@NEW_VALUEFLOAT(2),@ISBNCHAR(13) SELECT@NEW_VALUE=SCORE,@ISBN=ISBNFROMINSERTE