数据库课程设计-购物系统（纯MySQL）

小白学生一枚，可能有部分不准确的地方，仅供参考

SQL代码见：Mo-HanQing/OnlineShopping-Public: 数据库课程设计-在线购物系统（纯MySQL实现） (github.com)

3  系统设计

3.1  概念模型描述

3.1.1  构成系统的实体

由前面的购物系统的数据流图和数据字典可以抽取出系统的主要实体，包括“店铺”、“消费者”、“商品”和“账单”这四个实体。

“店铺”实体的属性有“店铺编号”、“店铺名称”、“店铺电话”和“发货地址”。

图6  “店铺”实体及其属性描述E-R图

“消费者”实体的属性有“消费者编号”、“消费者名称”、“消费者电话”和“收货地址”。

图7  “消费者”实体及其属性E-R图

“商品”实体的属性有“商品编号”、“商品名称”、“商品价格”和“商品库存”。

图8  “商品”实体及其属性E-R图

“账单”实体的属性有“账单编号”和“账单状态”。

图9  “账单”实体及其属性E-R图

3.1.2  系统局部E-R图

“店铺”实体与“商品”实体之间存在“拥有”的联系，一个店铺可以拥有多个商品，一个商品只能被一个店铺拥有，所以它们之间存在一对多联系 ( 1 : n ) 。

图10  “店铺”与“商品”实体的局部E-R图

“店铺”实体与“账单”实体之间存在“处理”的联系，一个店铺可以处理多个账单，一个账单只能被一个店铺处理，所以它们之间存在一对多联系 ( 1 : n ) 。

图11  “店铺”与“商品”实体的局部E-R图

“消费者”实体与“账单”实体之间存在“产生”的联系，一个消费者可以产生多个账单，一个账单只能由一个消费者产生，所以它们之间存在一对多联系 ( 1 : n ) 。

图12  “消费者”与“账单”实体的局部E-R图

“账单”实体与“商品”实体之间存在“包含”的联系，一个账单可以包含多个商品，一个商品可以被多个账单包含，所以它们之间存在多对多联系 ( m : n ) 。

图13  “账单”与“商品”实体的局部E-R图

3.1.3  合成全局E-R图

图14  合成后的全局E-R图

3.2  系统的逻辑设计

3.2.1  转换为关系数据模型

首先从“店铺”实体和“商品”实体以及它们之间的联系来考虑，“店铺”实体与“商品”实体之间是一对多联系，需要在“商品”实体转换的关系模式中加入“店铺”实体的键。“消费者”实体与“账单”实体之间是一对多联系，需要在“账单”实体转换的关系模式中加入“消费者”实体的键。“店铺”实体与“账单”实体之间也是一对多联系，需要在“账单”实体转换的关系模式中加入“店铺”实体的键。“账单”实体与“商品”实体之间是多对多联系，需要将“包含”联系转换为一个独立的关系模式，其属性为“账单”实体和“商品”实体的键加上联系的属性，可以得到如下关系模式。

店铺（店铺编号，店铺名称，店铺电话，发货地址）

消费者（消费者编号，消费者昵称，消费者电话，收货地址）

商品（商品编号，店铺编号（虚线下划线），商品名称，商品价格，商品库存）

账单（账单编号，消费者编号（虚线下划线），店铺编号（虚线下划线），账单状态，下单时间）

包含（账单编号（+ 虚线下划线），商品编号（+ 虚线下划线），商品数量）

3.2.2  关系数据模型的优化与调整

在上面的关系中，非主键属性都直接依赖于各自的主键，没有通过另一个非主键属性间接依赖于主键的情况，符合第三范式的要求。而为了简化账单总价的计算，在“包含”关系模式中增加“交易价格”属性，其值来自于“商品”关系模式中的“商品价格”。并在“账单”关系模式中增加“账单总价”属性，其值由“包含”关系模式中的“商品数量”和“交易价格”计算得出。两个关系模式分别改为“包含”（账单编号（+ 虚线下划线），商品编号（+ 虚线下划线），商品数量，交易价格）和“账单”（账单编号，消费者编号（虚线下划线），店铺编号（虚线下划线），账单状态，下单时间，账单总价）。

3.2.3  数据库表结构

表1  数据信息表

表2  店铺信息表(shop)

表3  消费者信息表(customer)

表4  商品信息表(commodity)

表5  账单信息表(bill)

表6  账单-商品信息表(bill_commodity)

3.3  数据库的物理设计

3.3.1  视图设计

创建了五个视图：shop_info_view、customer_info_view、shop_comm_view、cust_comm_view和bill_view。

shop_info_view是店铺信息视图，该视图限制每个店铺只能查看自己店铺的信息。

customer_info_view是消费者商品信息视图，该视图限制每个消费者只能查看自己的消费者信息。

shop_comm_view是店铺的商品视图，该视图限制了每个店铺只能查看自己店铺的商品信息。

cust_comm_view是消费者的商品视图，消费者可以查看所有店铺的商品，但消费者无法直接查看商品的库存量。

bill_view是账单视图，该视图限制了每个店铺和每个消费者只能查看自己的账单。

3.3.2  索引设计

除系统自动建立的唯一索引外，手动创建了五个索引：commodity_shop_id_idx、commodity_price_idx、bill_cust_id_idx、bill_shop_id_idx和bill_time_idx。

通过在commodity表的shop_id和price字段上创建索引，加快基于店铺编号和商品价格的查询速度，这对于执行涉及特定店铺的商品查询或价格范围查询非常有用。

在bill表的cust_id、shop_id和time字段上创建索引，提高基于消费者编号、店铺编号和下单时间的查询性能。

3.3.3  存储过程设计

创建了五个存储过程：add_customer_pr、add_shop_pr、add_commodity_pr、create_bill_pr和manage_bill_pr。

add_customer_pr包含两个参数：input_cust_phone和input_cust_address，含义是消费者电话号码和收货地址。该存储过程用于添加消费者信息和更新消费者信息。

add_shop_pr包含两个参数：input_shop_phone和input_shop_address，含义是店铺电话号码和发货地址。该存储过程用于添加店铺信息和更新店铺信息。

add_commodity_pr包含三个参数：input_comm_name、input_price和input_remain，含义是商品名称、商品价格和商品库存。该存储过程用于添加商品信息和更新商品信息。

create_bill_pr包含八个参数：input_cust_id、input_shop_id、input_comm_id_1、input_count_1、input_comm_id_2、input_count_2、input_comm_id_3和input_count_3，含义是消费者编号、店铺编号，以及三个商品的商品编号和需求量。该存储过程用于创建账单，消费者一次性最多购买三种商品。

manage_bill_pr包含两个参数：input_bill_id和input_bill_status，含义是账单编号和账单状态。该存储过程用于店铺对账单进行处理。

3.3.4  存储函数设计

创建了三个存储函数：phone_exist_fu、check_bill_fu和total_price_fu。

phone_exist_fu包含两个参数：kind和input_phone，含义是类型和电话号码。函数的返回值类型是布尔类型，该函数用于判断输入的电话号码是否存在。当kind的值为0时，表示对店铺电话号码进行判断；当kind的值为1时，表示对消费者电话号码进行判断。如果函数的返回值为TRUE，说明输入的电话号码已存在；如果函数的返回值为FALSE，说明输入的电话号码不存在。

check_bill_fu包含六个参数：input_comm_id_1、input_count_1、input_comm_id_2、input_count_2、input_comm_id_3和input_count_3，含义是商品编号和商品需求量。函数的返回值类型是布尔类型，该函数用于初步判断账单是否可以被创建。如果函数的返回值为TRUE，说明账单暂时可以被创建；如果函数的返回值为FALSE，说明账单不可以被创建。

total_price_fu包含一个参数：input_bill_id，含义是账单编号。函数的返回值为DECIMAL(10, 2)。该函数用于计算账单总价。

3.3.5  触发器设计

创建了两个触发器：update_remain_tr和before_bill_delete_tr。

update_remain_tr用于在bill_commodity表中插入商品信息后，减少对应商品的库存。

before_bill_delete_tr用于维护bill表与bill_commodity表的数据一致性，在删除bill表中的某个订单之前，删除此订单包含的商品。