数据课程设计-网上书店管理系统

网上书店管理系统一、设计说明：本系统针对于使用支付宝平台的独立网上书店，非淘宝网店。二、实体与关系：实体：书籍、用户、供给商、月本钱记录、日营收记录、月营收记录、网店信息、邮费信息、省份分区。关系：订购、供货、浏览、评论等E-R图详见E-R.pdf三、E-R图转换为关系模式：所有实体转化为独立的表，供给关系、浏览关系、订购关系均为m：n，这些关系也转换为独立的表，以下为具体关系模式结构：1.书籍表结构：工程名列名数据类型可空默认值说明国际标准书号ISBNCHAR(13)否主键书籍名称BOOKNAMEVARCHAR(50)否作者AUTHORVARCHAR(20)否主类型MAINTYPEVARCHAR(20)否子类型SUBTYPEVARCHAR(20)否进价BUYINGPRICEDISCOUNT否大于等于0售价PRICEMONEY否大于等于0折扣DISCOUNTFLOAT可10.00～1.00封面COVERIMAGE否出版社PUBLISHERSVARCHAR(20)否出版时间PUBLICATIONTIMEDATETIME否页数PAGESINT否大于0#版次印次VERSIONPRINTINGNUMVARCHAR(20)否装帧APPAREACEVARCHAR(20)否语种LANGUAGEVARCHAR(20)可简体中文库存STOCKINT可0大于等于0评分SCOREFLOAT(2)可大于等于0，且小于等于102.消费者表结构：工程名列名数据类型可空默认值说明用户IDCUSTOMERIDCHAR(18)否主键，限制为18个0～9字符用户姓名CUSTOMERNAMEVARCHAR(20)否性别GENDERBIT是00：男1：女用户密码PASSWORDVARCHAR(20)否籍贯NATIVEPLACEVARCHAR(20)否消费者等级LEVELTINYINT是0大于等于0，且小于等于10成长值GROWTHVALUEINT是0大于等于0##联系方式TELVARCHAR(20)否3.进货记录表结构：工程名列名数据类型可空默认值说明国际标准书号ISBNCHAR(13)否主键日期DATEDATETIME否主键进价BUYINGPRICEMONEY否数量BUYINGAMOUNTINT否供给商编号SUPPLIERNO.CHAR(6)否主键4.进货单表结构：工程名列名数据类型可空默认值说明进货单编号RECEIPT-NOINT否主键，自增日期DATEDATETIME否条目ITEMTEXT否总本钱金额AMOUNTOFMONEYMONEY否大于0总数量TOTALQUANTITYINT否备注NOTEVARCHAR〔100〕是5,供给商表结构：工程名列名数据类型可空默认值说明供给商编号SUPPLIERNO.CHAR(6)否主键供给商名称SUPPLIERNAMEVARCHAR(20)否地址SUPPLIERADDRESSVARCHAR(20)否联系信息CONTACTINFOVARCHAR(20)否信用等级CREDITLEVELTINYINT是5大于等于0，且小于等于106.评论记录表结构：工程名列名数据类型可空默认值说明用户idCUSTOMERIDCHAR〔18〕否主键、外键国际标准书号ISBNCHAR〔13〕否主键时间TIMEDATETIME否评论COMMENTVARCHAR〔255〕否评分SCOREFLOAT(2)否大于等于0，且小于等于107.订购记录表结构：工程名列名数据类型可空默认值说明时间TIMEDATETIME否主键国际标准书号ISBNCHAR〔13〕否主键用户IDCUSTOMERIDCHAR〔18〕否主键、外键用户姓名CUSTOMERNAMEVARCHAR(20)否##联系方式TELVARCHAR(20)否进价BUYINGPRICEMONEY否大于0价格PRICEMONEY否大于0数目AMOUNTOFBOOKINT否大于08.订单表结构工程名列名数据类型可空默认值说明订单ORDERFORMNOINT否主键，自增时间TIMEDATETIME否条目ITEMTEXT否用户IDCUSTOMERIDCHAR〔18〕否用户姓名CUSTOMERNAMEVARCHAR(20)否##联系方式TELVARCHAR(20)否数目AMOUNTOFBOOKINT否大于0地址ADDRESSVARCHAR(20)否邮费POSTAGEMONEY否大于0金额AMOUNTOFMONEYMONEY否大于0折扣DISCOUNTFLOAT可10.00～1.00运送天数DAYSTINYINT可快递号EXPRESSNOVARCHAR〔15〕否状态STATETINYINT否大于等于0，且小于等于49.浏览记录表结构：工程名列名数据类型可空默认值说明国际标准书号ISBNCHAR(13)否时间TIMEDATETIME否用户IDCUSTOMER_IDCHAR(18)否游客10.日营收记录表结构：工程名列名数据类型可空默认值说明日期DATEDATETIME否主键日营业额DAILYTURNOVERMONEY是0大于等于0日总销量DAILYSALEAMOUNTINT是0大于等于0毛利润DAILYGROSSPROFITMONEY是0大于等于0实际到帐金额ACTUALINCOMEMONEY是0大于等于011.月营收记录表结构：工程名列名数据类型可空默认值说明月份MONTHDATETIME否主键月营业额MONTHLYTURNOVERMONEY是0大于等于0月总销量MONTHLYSALEAMOUNTINT是0大于等于0毛利润MONTHLYGROSSPROFITMONEY是0大于等于0净利润MONTHLYRETAINEDPROFITMONEY是0大于等于0实际到帐金额ACTUALINCOMEMONEY是0大于等于012.月本钱记录表结构：工程名列名数据类型可空默认值说明月份MONTHDATETIME否主键电费ELECTRICCHARGEMONEY是0大于等于0税款TAXPAYMENTMONEY是0大于等于0支付平台使用费CHARGEFORUSEMONEY是0大于等于0总运费TOTALFREIGHTMONEY是0大于等于0其它费用OTHER_PAYMENTMONEY是0大于等于013.省份分区表结构:工程名列名数据类型可空默认值说明省份PROVINCEVARCHAR(20)否分区PARTITIONTINYINT否主键，大于014.邮费信息表结构:工程名列名数据类型可空默认值说明类型TYPEVARCHAR(20)否主键分区PARTITIONTINYINT否主键，外键到货天数ARRIVALDAYSTINYINT否大于0首重重量FIRSTWEIGHTFLOAT(2)否大于0首重费用FWFEEMONEY否大于0续重重量ADDWEIGHTFLOAT(2)否大于0续重费用AWFEEMONEY否大于015.网店信息表结构:工程名列名数据类型可空默认值说明信息日期DATEDATETIME否主键网店简介INTRODUCTIONTEXT否相关图片RELATEDIMAGESIMAGE否店主STOREKEEPERVARCHAR(20)否实体店铺地址PHYSICLSTOREADDRESSVARCHAR(20)否联系TELVARCHAR(20)否电子邮箱EMAILVARCHAR(30)否四、视图：A：创立书籍视图,屏蔽进价等重要信息USEBOOKSTOREGOCREATEVIEWBOOK_VIEW AS SELECTISBN,BOOKNAME,AUTHOR,PRICE,COVER,PUBLISHERS,PUBLICATION_TIME,PAGES,VERSION_PRINTING_NUM,APPAREACE,LANGUAGE,STOCK,SCORE FROMBOOKGOB：创立书籍概览视图，便于用户浏览CREATEVIEWBOOK_VIEW_SIMPLE AS SELECTBOOKNAME,AUTHOR,PRICE,COVER,STOCK,SCORE FROMBOOKGOC：创立书籍分类视图CREATEVIEWBOOK_VIEW_IT AS SELECTBOOKNAME,AUTHOR,PRICE,COVER,STOCK,SCORE FROMBOOK WHERESUBTYPE='IT界'GOD：创立即时好评榜TOP10书籍视图，便于用户了解热门书籍CREATEVIEWTOP10_SCORE AS SELECTTOP10* FROMBOOK_VIEW WHERESCOREISNOTNULL ORDERBYSCOREDESCGOD：创立打折促销类书籍视图〔增加了显示浏览次数，多表视图〕 CREATEVIEW BOOK__PROMOTION AS SELECTTOP100BOOKNAMEAS'书名',AUTHORAS'作者',SCOREAS'评分',PRICEAS'价格',DISCOUNTAS'折扣',COUNT(BOOK.ISBN)AS'浏览次数' FROMBOOK,BROWSE_RECORD WHEREBOOK.ISBN=BROWSE_RECORD.ISBNANDDISCOUNT<1.0 GROUPBYSCORE,BOOK.ISBN,AUTHOR,BOOKNAME,PRICE,DISCOUNT ORDERBYSCOREDESC五、索引局部：A：在书籍表上创立索引，便于加快查找速度CREATEINDEXBOOK_INDEX ONBOOK(BOOKNAME) WITH(DROP_EXISTING=OFF)B：在月营收表上，创立唯一索引，加快查找速度。CREATEUNIQUECLUSTEREDINDEXMONTHLY_REVENUE_INDEX ONMONTHLY_REVENUE(MONTH)六、存储过程：1.按类别搜索/*搜索某类书的存储过程*/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'爱情'执行结果：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'史'执行结果：3.用存储过程查看某一用户的购物历史记录CREATEPROCEDURESHOPPING_RECORD@CUSTOMER_IDCHAR(18) AS SELECTTIME,ITEM,AMOUNT_OF_BOOK,AMOUNT_OF_MONEY FROMORDER_FORM WHERECUSTOMER_ID=@CUSTOMER_ID 执行结果：4.创立生成收货单存储过程,因为需要将结果集作为某一列的值,故将结果集先转化为字符串CREATEPROCEDUREMAKE_RECEIT@DATEDATETIME,@NOTEVARCHAR(255) AS /*将结果集先转化为字符串*/ 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' 执行结果：七、完整性约束主键、外键及check约束规那么如下所示：A：B：限制输入值，保证其为有效的身份证号。相关语句：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：相关语句：PRIMARYKEY(DATE,ISBN,SUPPLIER_NO)D：默认值约束八、自定义函数：A：用内嵌表值函数实现搜索某一作者的所有书籍。CREATEFUNCTIONSEARCH_BOOK_BYAUTHOR(@AUTHORVARCHAR(20))RETURNSTABLEASRETURN( SELECT* FROMBOOK_VIEW WHEREAUTHOR=@AUTHOR)SELECT*FROMBOOKSTORE.[DBO].SEARCH_BOOK_BYAUTHOR('汪远')执行结果：B：用户自定义函数，求某用户的成长值〔每消费10元，增加1点成长值〕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'成长值'执行结果：九、触发器：A：用户对书籍进行评分后，评论表中score列的值改变，引起书籍表中数据的改变，所以用触发器来处理这种关联。CREATETRIGGERUP_SCORE ONCOMMENTAFTERINSERT AS BEGIN DECLARE@OLD_VALUEFLOAT(2),@NEW_VALUEFLOAT(2),@ISBNCHAR(13) SELECT@NEW_VALUE=SCORE,@ISBN=ISBNFROMINSERTE