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

一、功能需求

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

二、数据库概念结构设计

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

mysql创建书库 mysql创建网上书店数据库_mysql创建书库

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

属性

字段名

数据类型

是否为空/约束条件

账号

ACCOUNT_NUMBER

CHAR(20)

主键

密码

PASSWORD

VARCHAR(50)

非空

地址

ADDRESS

VARCHAR(50)

可以空

会员名

MEMBER_ID

VARCHAR(50)

可以空

注册时间

REGTIME

VARCHAR(50)

可以空

手机号

PHONE_NUMBER

VARCHAR(50)

可以空

邮箱

POSTBOX

VARCHAR(50)

可以空

性别

SEX

VARCHAR(8)

可以空

真实姓名

NAME

VARCHAR(50)

可以空

表2 管理员表

属性

字段名

数据类型

是否为空/约束条件

管理员编号

ADMINISTRATOR_ID

CHAR(20)

主键

管理员密码

AD_CODE

CHAR(30)

非空

管理员名称

AD_NAME

VARCHAR(20)

非空

邮箱

E_MAIL

VARCHAR(50)

可以空

手机号

TELEPHONE

VARCHAR(20)

可以空

表3 订单表

属性

字段名

数据类型

是否为空/约束条件

订单编号

ORDER_ID

CHAR(20)

主键

会员账号

NUMBER

CHAR(30)

非空

下单日期

DATE

VARCHAR(20)

可以空

订购总数

SUM

INT

非空

送货方式

WAY

VARCHAR(20)

非空

支付方式

PAYMENT_WAY

VARCHAR(20)

非空

卖出总价

TOTAL_PRICE

FLOAT

可以空

收货地址

SHIPPING_ADD

VARCHAR(50)

非空

收货人联系方式

CONTACT

VARCHAR(20)

非空

收货人姓名

CONSIGNEE

VARCHAR(50)

非空

发货日期

ISSUANCE_DATE

VARCHAR(20)

可以空

负责该订单的管理员

ADMINISTRATOR_ID

VARCHAR(20)

非空

表4 图书表

属性

字段名

数据类型

是否为空/约束条件

图书编号

BOOK_ID

CHAR(20)

主键

书名

BOOK_NAME

CHAR(30)

非空

图书类型

TYPE

VARCHAR(20)

可以空

作者

AUTHOR

VARCHAR(20)

非空

出版社

PRESS

VARCHAR(50)

非空

出版日期

PRESS_DATE

VARCHAR(20)

非空

定价

PRICE

FLOAT

非空

折扣

DISCOUNT

FLOAT

非空

库存数量

STOCK_NUM

INT

可以空

会员账号

MEM_NUM

CHAR(20)

非空

4. 使用SQL语句建立基本表
CREATE TABLE MEMBER
(
	ACCOUNT_NUMBER CHAR(20) NOT NULL,
	PASSWORD VARCHAR(50) NOT NULL,
	ADDRESS	VARCHAR(50),
	MEMBER_ID VARCHAR(50),
	REGTIME	VARCHAR(50),
	PHONE_NUMBER VARCHAR(50),
	POSTBOX	VARCHAR(50),
	SEX	VARCHAR(8),
	NAME VARCHAR(50)
)

CREATE TABLE ADMINISTRATOR
(
	ADMINISTRATOR_ID CHAR(20) NOT NULL,
	AD_CODE	CHAR(30) NOT NULL,
	AD_NAME	VARCHAR(20) NOT NULL,
	E_MAIL VARCHAR(50),
	TELEPHONE VARCHAR(20)
)

CREATE TABLE ORDER_SHOW
(
	ORDER_ID CHAR(20) NOT NULL,
	NUMBER CHAR(30) NOT NULL,
	DATE VARCHAR(20),
	SUM	INT NOT NULL,
	WAY	VARCHAR(20) NOT NULL,
	PAYMENT_WAY	VARCHAR(20) NOT NULL,
	TOTAL_PRICE	VARCHAR(20),
	SHIPPING_ADD VARCHAR(50) NOT NULL,
	CONTACT	VARCHAR(20) NOT NULL,
	CONSIGNEE VARCHAR(50) NOT NULL,
	ISSUANCE_DATE VARCHAR(20),
	ADMINISTRATOR_ID VARCHAR(20) NOT NULL
)

CREATE TABLE BOOK
(
	BOOK_ID	CHAR(20) NOT NULL,
	BOOK_NAME CHAR(30) NOT NULL,
	TYPE VARCHAR(20),
	AUTHOR VARCHAR(20) NOT NULL,
	PRESS VARCHAR(50) NOT NULL,
	PRESS_DATE VARCHAR(20) NOT NULL,
	PRICE FLOAT NOT NULL,
	DISCOUNT FLOAT NOT NULL,
	STOCK_NUM INT,
	MEM_NUM CHAR(20) NOT NULL
)
5. 管理基本表
--(1) 向图书表增加“页数”列，其数据类型是整数型
ALTER TABLE BOOK ADD B_PAGE INT

--(2) 将“图书表”中的“页数”数据类型改成短整型
ALTER TABLE BOOK ALTER COLUMN B_PAGE SMALLINT

--(3) 增加“图书表”中“页数”取值必须大于0的约束条件
ALTER TABLE BOOK ADD CONSTRAINT CK_PAGE CHECK(B_PAGE > 0)

--(4) 删除“图书表”中的页数列
ALTER TABLE BOOK DROP CONSTRAINT CK_PAGE

--(5) 删除“图书表”
DROP TABLE BOOK
6. 建立和管理视图
--建立一个“图书类别”为“计算机”的视图
GO
CREATE VIEW COMPUTER AS SELECT * FROM BOOK WHERE TYPE = '计算机'
GO
7. 建立和管理索引
--在会员表上为姓名建立索引
CREATE UNIQUE INDEX 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 ORDER BY 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 = '英语初级听力（学生用书）' ORDER BY SUM DESC

--（7）查询订单数位列前三的会员信息
SELECT * FROM MEMBER WHERE NAME IN (SELECT TOP 3 NAME FROM ORDER_SHOW,MEMBER WHERE ACCOUNT_NUMBER = NUMBER GROUP BY NAME ORDER BY SUM(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）统计会员所有订单的订购总价
SELECT SUM(TOTAL_PRICE) FROM ORDER_SHOW

--（10）查询订购总价大于200的会员的账号
SELECT NUMBER FROM ORDER_SHOW,MEMBER WHERE ACCOUNT_NUMBER = NUMBER GROUP BY NUMBER HAVING SUM(TOTAL_PRICE) > 200
9. 数据更新
INSERT INTO MEMBER VALUES ('jtxia','123456','河北省廊坊市','@~@','2020-2-1','12345678912','123@qq.com','女','张玲')

--（1）书店新进一种图书，即向图书表中增加一种新的图书，ISBN号为9787302164784，书名为软件工程导论（第5版），作者为张海潘，出版社为清华大学出版社，定价为35，折扣为8，图书类别为计算机，图书概况为“本书可作为高等院校软件工程课程的教材或教学参考书”，库存量为50
INSERT INTO BOOK VALUES ('9787302164784','软件工程导论（第5版）','计算机','张海潘','清华大学出版社','2014-3-8',35.0,0.80,50,'jskdb')

--（2）书店价格调整，将“软件工程导论（第5版）”图书的“折扣”更新为7.5折
UPDATE BOOK SET DISCOUNT = 0.75 WHERE BOOK_NAME = '软件工程导论（第5版）'

--（3）会员地址变更，将会员账号为“jtxia”的地址更新为“天津大学”
UPDATE MEMBER SET ADDRESS = '天津大学' WHERE ACCOUNT_NUMBER = 'jtxia'

--（4）书店进货，将所有图书的库存量增加10
UPDATE BOOK SET STOCK_NUM = STOCK_NUM + 10

--（5）活动促销，将“计算机”类图书的折扣全部减1（多打一折）
UPDATE BOOK SET DISCOUNT = DISCOUNT - 0.1 WHERE TYPE = '计算机'

--（6）由于图书下架，删除图书“软件工程导论（第5版）”
DELETE FROM 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)”按钮，可以看到消息窗口提示备份成功的消息。