MySQL数据库设计作业 ——《网上书店系统》数据库设计实验报告

数据库设计作业——《网上书店系统》数据库设计

一、功能需求

• 普通用户：可以进行最基础的登陆操作，可浏览图书、按类别查询图书、查看 图书的详细信息，还可以注册成为会员。
• 会员：需要填写详细信息（真实姓名、性别、手机号、地址、邮箱等），可浏览图书、按类别查询图书、查看图书的详细信息，在此基础上，还可以订购图书、生成订单、查看订单。
• 管理员：可以浏览图书、按类别查询图书、查看图书的详细信息，需要查看订单、处理订单进行发货处理，同时可以查看、修改个人信息，更新图书信息，如增加、删除图书、更新库存量、修改商品名称、增加图书的属性列等。
• 图书：需要管理员进行编排，按图书编号、书名、图书类型、作者、出版社、出版日期、定价、折扣、库存数量等信息分类。

二、数据库概念结构设计

1. 概念结构设计（E-R图）

2. 逻辑结构设计（数据库表、关系模式）

• 普通用户（账号 ，密码）
• 会员（账号，密码，会员名，注册时间，手机号，邮箱，地址，性别，真实姓名）
• 注册（账号） 外键：账号
• 管理员（管理员编号，管理员密码，管理员名称，邮箱，手机号）
• 订单（订单编号，会员账号，下单日期，订购数量，送货方式，支付方式，卖出 总价，收货地址，收货人联系方式，收货人姓名，发货日期，负责该订单的 管理员（编号））
• 订购（账号，订单编号） 外键：账号，订单编号
• 处理（管理员编号，订单编号） 外键：管理员编号，订单编号
• 图书（图书编号，书名，图书类型，作者，出版社，出版日期，定价，折扣，库 存数量，会员账号） 外键：会员账号
• 订单详情（订单编号，图书编号，订购数量，订购价格） 外键：订单编号，图书编号

规范化分析：

• 普通用户关系中账号为主键，其中函数依赖有：账号—>密码 因为在普通用户关系中不存在非主属性对码的部分函数依赖和传递函数依赖，所以普通用户关系是属于3NF。
• 会员关系中账号为主键，其中函数依赖有：账号—>密码，账号—>会员名，账号—>注册时间，账号—>手机号，账号—>邮箱，账号—>地址，账号—>性别，账号—>真实姓名 因为在会员关系中不存在非主属性对码的部分函数依赖和传递函数依赖，所以会员关系是属于3NF。
• 管理员关系中管理员编号为主键，其中函数依赖有：管理员编号—>管理员密码，管理员编号—>管理员名称，管理员编号—>邮箱，管理员编号—>手机号 因为在管理员关系中不存在非主属性对码的部分函数依赖和传递函数依赖，所以管理员关系是属于3NF。
• 订单关系中订单编号为主键，其中函数依赖有：订单编号—>会员账号，订单编号—>下单时间，订单编号—>订购总数，订单编号—>送货方式，订单编号—>支付方式，订单编号—>卖出总价，订单编号—>收货地址，订单编号—>收货人联系方式，订单编号—>收货人姓名，订单编号—>发货日期，订单编号—>负责该订单的管理员（编号） 因为在订单关系中不存在非主属性对码的部分函数依赖和传递函数依赖，所以订单关系是属于3NF。
• 图书关系中图书编号为主键，其中函数依赖有：图书编号—>书名，图书编号—>图书类型，图书编号—>作者，图书编号—>出版社，图书编号—>出版日期，图书编号—>定价，图书编号—>折扣，图书编号—>库存数量 因为在图书关系中不存在非主属性对码的部分函数依赖和传递函数依赖，所以图书关系是属于3NF。

3. 建立和管理基本表

表1 会员表

属性字段名数据类型是否为空/约束条件账号ACCOUNT_NUMBERCHAR(20)主键密码PASSWORDVARCHAR(50)非空地址ADDRESSVARCHAR(50)可以空会员名MEMBER_IDVARCHAR(50)可以空注册时间REGTIMEVARCHAR(50)可以空手机号PHONE_NUMBERVARCHAR(50)可以空邮箱POSTBOXVARCHAR(50)可以空性别SEXVARCHAR(8)可以空真实姓名NAMEVARCHAR(50)可以空

表2 管理员表

属性字段名数据类型是否为空/约束条件管理员编号ADMINISTRATOR_IDCHAR(20)主键管理员密码AD_CODECHAR(30)非空管理员名称AD_NAMEVARCHAR(20)非空邮箱E_MAILVARCHAR(50)可以空手机号TELEPHONEVARCHAR(20)可以空

表3 订单表

属性字段名数据类型是否为空/约束条件订单编号ORDER_IDCHAR(20)主键会员账号NUMBERCHAR(30)非空下单日期DATEVARCHAR(20)可以空订购总数SUMINT非空送货方式WAYVARCHAR(20)非空支付方式PAYMENT_WAYVARCHAR(20)非空卖出总价TOTAL_PRICEFLOAT可以空收货地址SHIPPING_ADDVARCHAR(50)非空收货人联系方式CONTACTVARCHAR(20)非空收货人姓名CONSIGNEEVARCHAR(50)非空发货日期ISSUANCE_DATEVARCHAR(20)可以空负责该订单的管理员ADMINISTRATOR_IDVARCHAR(20)非空

表4 图书表

属性字段名数据类型是否为空/约束条件图书编号BOOK_IDCHAR(20)主键书名BOOK_NAMECHAR(30)非空图书类型TYPEVARCHAR(20)可以空作者AUTHORVARCHAR(20)非空出版社PRESSVARCHAR(50)非空出版日期PRESS_DATEVARCHAR(20)非空定价PRICEFLOAT非空折扣DISCOUNTFLOAT非空库存数量STOCK_NUMINT可以空会员账号MEM_NUMCHAR(20)非空

4. 使用SQL语句建立基本表

CREATETABLE MEMBER
(
    ACCOUNT_NUMBER CHAR(20)NOTNULL,
    PASSWORD VARCHAR(50)NOTNULL,
    ADDRESS    VARCHAR(50),
    MEMBER_ID VARCHAR(50),
    REGTIME    VARCHAR(50),
    PHONE_NUMBER VARCHAR(50),
    POSTBOX    VARCHAR(50),
    SEX    VARCHAR(8),
    NAME VARCHAR(50))CREATETABLE ADMINISTRATOR
(
    ADMINISTRATOR_ID CHAR(20)NOTNULL,
    AD_CODE    CHAR(30)NOTNULL,
    AD_NAME    VARCHAR(20)NOTNULL,
    E_MAIL VARCHAR(50),
    TELEPHONE VARCHAR(20))CREATETABLE ORDER_SHOW
(
    ORDER_ID CHAR(20)NOTNULL,
    NUMBER CHAR(30)NOTNULL,DATEVARCHAR(20),
    SUM    INTNOTNULL,
    WAY    VARCHAR(20)NOTNULL,
    PAYMENT_WAY    VARCHAR(20)NOTNULL,
    TOTAL_PRICE    VARCHAR(20),
    SHIPPING_ADD VARCHAR(50)NOTNULL,
    CONTACT    VARCHAR(20)NOTNULL,
    CONSIGNEE VARCHAR(50)NOTNULL,
    ISSUANCE_DATE VARCHAR(20),
    ADMINISTRATOR_ID VARCHAR(20)NOTNULL)CREATETABLE BOOK
(
    BOOK_ID    CHAR(20)NOTNULL,
    BOOK_NAME CHAR(30)NOTNULL,TYPEVARCHAR(20),
    AUTHOR VARCHAR(20)NOTNULL,
    PRESS VARCHAR(50)NOTNULL,
    PRESS_DATE VARCHAR(20)NOTNULL,
    PRICE FLOATNOTNULL,
    DISCOUNT FLOATNOTNULL,
    STOCK_NUM INT,
    MEM_NUM CHAR(20)NOTNULL)

5. 管理基本表

--(1) 向图书表增加“页数”列，其数据类型是整数型ALTERTABLE BOOK ADD B_PAGE INT--(2) 将“图书表”中的“页数”数据类型改成短整型ALTERTABLE BOOK ALTERCOLUMN B_PAGE SMALLINT--(3) 增加“图书表”中“页数”取值必须大于0的约束条件ALTERTABLE BOOK ADDCONSTRAINT CK_PAGE CHECK(B_PAGE >0)--(4) 删除“图书表”中的页数列ALTERTABLE BOOK DROPCONSTRAINT CK_PAGE

--(5) 删除“图书表”DROPTABLE BOOK

6. 建立和管理视图

--建立一个“图书类别”为“计算机”的视图
GO
CREATEVIEW COMPUTER ASSELECT*FROM BOOK WHERETYPE='计算机'
GO

7. 建立和管理索引

--在会员表上为姓名建立索引CREATEUNIQUEINDEX MEMBER_NAME ON MEMBER(NAME)

8. 数据查询

--（1）查询“张玲”的会员信息SELECT*FROM MEMBER WHERE NAME ='张玲'--（2）查询图书“中国通史”的价格SELECT PRICE FROM BOOK WHERE BOOK_NAME ='中国通史'--（3）查询网上书店系统中每种数的库存数量，并按照库存数量由多到少排列SELECT STOCK_NUM FROM BOOK ORDERBY STOCK_NUM DESC--（4）查询“陈东光”订购图书的订单情况SELECT*FROM ORDER_SHOW WHERE CONSIGNEE ='陈东光'--（5）列出账号为“jtxia”的会员所订购的所有图书信息SELECT*FROM BOOK WHERE MEM_NUM ='jtxia'--（6）列出“英语初级听力（学生用书）”图书的订购情况，按照订购数量由大到小降序排列SELECT*FROM ORDER_SHOW,BOOK WHERE ORDER_SHOW.NUMBER = MEM_NUM AND BOOK_NAME ='英语初级听力（学生用书）'ORDERBY SUM DESC--（7）查询订单数位列前三的会员信息SELECT*FROM MEMBER WHERE NAME IN(SELECTTOP3 NAME FROM ORDER_SHOW,MEMBER WHERE ACCOUNT_NUMBER = NUMBER GROUPBY NAME ORDERBYSUM(ORDER_ID))--（8）查询订购过“英语初级听力”，或者“高等数学”的会员账号和姓名SELECT ACCOUNT_NUMBER,NAME FROM MEMBER,ORDER_SHOW,BOOK WHERE MEMBER.ACCOUNT_NUMBER = ORDER_SHOW.NUMBER AND ORDER_SHOW.NUMBER = BOOK.MEM_NUM AND BOOK_NAME ='英语初级听力（学生用书）'OR BOOK_NAME ='高等数学'--（9）统计会员所有订单的订购总价SELECTSUM(TOTAL_PRICE)FROM ORDER_SHOW

--（10）查询订购总价大于200的会员的账号SELECT NUMBER FROM ORDER_SHOW,MEMBER WHERE ACCOUNT_NUMBER = NUMBER GROUPBY NUMBER HAVINGSUM(TOTAL_PRICE)>200

9. 数据更新

INSERTINTO MEMBER VALUES('jtxia','123456','河北省廊坊市','@~@','2020-2-1','12345678912','[email protected]','女','张玲')--（1）书店新进一种图书，即向图书表中增加一种新的图书，ISBN号为9787302164784，书名为软件工程导论（第5版），作者为张海潘，出版社为清华大学出版社，定价为35，折扣为8，图书类别为计算机，图书概况为“本书可作为高等院校软件工程课程的教材或教学参考书”，库存量为50INSERTINTO BOOK VALUES('9787302164784','软件工程导论（第5版）','计算机','张海潘','清华大学出版社','2014-3-8',35.0,0.80,50,'jskdb')--（2）书店价格调整，将“软件工程导论（第5版）”图书的“折扣”更新为7.5折UPDATE BOOK SET DISCOUNT =0.75WHERE BOOK_NAME ='软件工程导论（第5版）'--（3）会员地址变更，将会员账号为“jtxia”的地址更新为“天津大学”UPDATE MEMBER SET ADDRESS ='天津大学'WHERE ACCOUNT_NUMBER ='jtxia'--（4）书店进货，将所有图书的库存量增加10UPDATE BOOK SET STOCK_NUM = STOCK_NUM +10--（5）活动促销，将“计算机”类图书的折扣全部减1（多打一折）UPDATE BOOK SET DISCOUNT = DISCOUNT -0.1WHERETYPE='计算机'--（6）由于图书下架，删除图书“软件工程导论（第5版）”DELETEFROM BOOK WHERE BOOK_NAME ='软件工程导论（第5版）'

10. 数据库维护

数据库备份

SQL Server 2008提供了4种不同的备份方式，分别是：①完整备份，备份整个数据库的所有内容，包括事务日志。②差异备份，它是完整备份的补充，差异备份只备份上次完整备份后更改的数据。③事务日志备份，事务日志备份只备份事务日志里的内容。④数据库文件和文件组备份，如果在创建数据库时，为数据库创建了多个数据库文件或文件组，可以使用该备份方法。
常见的数据库备份you两种方法：一种是利用Management Studio进行数据库备份；另一种时利用SQL语句进行备份。

• 利用Management Studio进行数据库备份 在备份数据库之前，首先需要新建设备以用于存储备份的数据库。 新建备份设备的步骤为：打开“对象资源管理器”，点击“服务器对象”，右击“备份设备”，选择“新建备份设备”，在打开的“备份设备”窗口中，输入备份设备的名称“网上书店系统_bak”，在“文件”路径处输入所属路径，单击确定，即可在左侧的对象资源管理器中看到新建的备份文件“网上书店系统_bal”。 备份数据库：打开“服务器对象”，右击“网上书店系统_bak”这个新建立的备份设备，单击“备份数据库”，在打开的备份数据库窗口中，选择“网上书店系统”数据库、备份类型为“完整”、备份集名称默认为“网上书店系统-完整 数据库 备份”，单击“确定”按钮即可看到备份数据库成功的提示对话框。
• 使用SQL语句备份数据库 1）在新建查询窗口中，输入下面的语句创建备份设备。 SP_ADDUMPDEVICE ‘disk’,’网上书店系统_bak’,’文件路径’ 2）在新建查询窗口中，输入下面的语句备份数据库。 BACKUP DATABASS 网上书店系统 TO DISK = ‘网上书店系统_bak’ 3）单击工具栏上的“！执行(X)”按钮，可以看到消息窗口提示备份成功的消息。