|
|
摘 要
- 进入人类社会的各个领域并发挥着越来越重要的作用。为了应对激烈的市场竞争,各商家推出了各种措施吸引顾客.其中相当普遍的方式就是利用会员制度。
- 采用选择功能强大的 Microsoft SQL Server 为开发工具，利用软件工程思想和方法，开发该系统的所需要的功能。
该数据库设计从需求分析、概念结构、逻辑结构、物理结构、数据库实施等方面着手，分成了五大模块，分别为：等级管理、前台界面、收银台、仓库管理、用户界面。另外，还使用视图、触发器、事务、主键、外键等维护数据库。
- 它为超市提供了系统性的会员管理平台解决方案，可以实现超市的电子化管理，是一套比较完备的管理工具，提高了超市管理效率。在很大限度上帮助超市的掌握与管理。
- 本超市会员管理系统设计思路较为肤浅，但在一定程度上实现了超市会员管理系统的实用功能。初次设计数据库，其中肯定会有不足之处，还望读者谅解！
- 【关键词】Microsoft SQL Server、五大模块、CRUD
Abstract
Enter into various fields of human society and play an increasingly important role.In response to the fierce market competition, businesses have launched various measures to attract customers. The fairly common way is to use the membership system.
Choose the powerful Microsoft SQL Server as the development tool, using software engineering ideas and methods, to develop the functions needed for the system.
The database design starts from demand analysis, concept structure, logical structure, physical structure, database implementation, and is divided into five modules:
level management, front desk interface, cashier, warehouse management and user interface.In addition, the database uses views, triggers, transactions, primary, external keys, etc.
It provides a systematic member management platform solution for supermarkets, which can realize the electronic management of supermarkets, is a relatively complete management tools, and improves the efficiency of supermarket management.Help supermarkets to master and manage to a large extent.
The design idea of the supermarket member management system is relatively shallow, but to some extent, it realizes the practical function of the member management system of the supermarket.The first design of the database, which will certainly have shortcomings, but also hope the readers to understand.【Keywords】Microsoft SQL Server、Five modules、CRUD
目 录
该系统分为五大模块，让不同模块各自完成系统的各个需求，就能使得效率最大化，提高超市的盈利水平。其系统功能结构如图 1 所示。
各模块的功能如下：
- 管理员：
- 对会员等级的管理。
- 前台：
- 负责会员信息管理、会员卡的管理。
- 仓库人员：
- 对超市商品信息的管理。
- 收银台：
- 对会员的消费记录进行管理。
- 会员客户端：
- 会员将通过网络等渠道，自行连接数据库，能够对当前会员自己信息的查询与修改，查询当前会员卡与当前用户的消费记录。以此完成会员对会员信息的查询、修改。
数据流图（Data Flow Diagram）：简称 DFD，它从数据和加工角度，以图形方式来表达系统的逻辑功能、在系统内部的逻辑流向和逻辑变换过程，是的主要表达工具及用于表示软件的一种方法。
在小组经过严谨的研究与讨论之后，我们认为系统的数据流图如图 2 所示。
数据字典是指对数据的数据项、数据结构、、数据存储、处理逻辑等进行定义和描述，其目的是对中的各个元素做出详细的说明
- 数据项：
- 数据项是数据记录中最基本的、不可分的有名数据单位，是具有独立含义的最小标识单位。
- 经过实际的调查与分析，认为系统所需要的数据项如表 1 所示。
| 属性名 | 含义 | 取值范围 | 数据类型 | 主码 | 外码 | 完整性要求 | 默认值 | | 会员信息表（tb_imformation） | 会员信息表（tb_imformation） | 会员信息表（tb_imformation） | 会员信息表（tb_imformation） | 会员信息表（tb_imformation） | 会员信息表（tb_imformation） | 会员信息表（tb_imformation） | 会员信息表（tb_imformation） | | Ino | 会员卡号 | int | 是 | NOT NULL | 自增长 | | Iname | 姓名 | char(8) | NOT NULL 唯一性 | | Isex | 性别 | 男、女 | char(2) | NOT NULL | | Iage | 年龄 | [0,100] | int | NULL | | iPhone | 电话号码 | char(12) | NULL | | 会员等级表（tb_grade） | 会员等级表（tb_grade） | 会员等级表（tb_grade） | 会员等级表（tb_grade） | 会员等级表（tb_grade） | 会员等级表（tb_grade） | 会员等级表（tb_grade） | 会员等级表（tb_grade） | | Gno | 等级编号 | int | 是 | NOT NULL | | Gname | 名称 | char(20) | NULL | | Gdiscounts | 优惠 | [0,∞] | float | NOT NULL | | 会员卡表（tb_card） | 会员卡表（tb_card） | 会员卡表（tb_card） | 会员卡表（tb_card） | 会员卡表（tb_card） | 会员卡表（tb_card） | 会员卡表（tb_card） | 会员卡表（tb_card） | | Ino | 会员卡号 | int | 是 | NOT NULL | | Gno | 等级编号 | int | 是 | NOT NULL | | Ctime | 注册时间 | datetime | NULL | 当前时间 | | 商品信息表（tb_products） | 商品信息表（tb_products） | 商品信息表（tb_products） | 商品信息表（tb_products） | 商品信息表（tb_products） | 商品信息表（tb_products） | 商品信息表（tb_products） | 商品信息表（tb_products） | | Pno | 商品编号 | int | 是 | NOT NULL | | Pprice | 单价 | [0,∞] | float | NOT NULL | | Pname | 名称 | char(20) | NULL | | Pcount | 库存数量 | [0,∞] | int | NOT NULL | 50 | | 会员订单表（tb_orders） | 会员订单表（tb_orders） | 会员订单表（tb_orders） | 会员订单表（tb_orders） | 会员订单表（tb_orders） | 会员订单表（tb_orders） | 会员订单表（tb_orders） | 会员订单表（tb_orders） | | Ino | 会员卡号 | int | 是 | NOT NULL | | Pno | 商品编号 | int | 是 | NOT NULL | | Ocount | 购买数量 | （0,∞] | int | NOT NULL | | Otime | 购买时间 | datetime | NULL | 当前时间 | | Omoney | 订单金额 | float | NOT NULL |
- 数据结构：
- 数据结构是数据之间的组合关系。一个数据结构由若干个数据项组成，也可以由若干数据结构组成，或者由若干个数据项和数据结构混合而成。
- 经过严谨的讨论与分析，我们认为的数据结构如表 2 所示。
| 数据结构 | 数据结构 | | 名称 | 涉及的数据项 | | 等级信息 | 等级编号 + 名称 + 优惠 | | 级别信息 | 会员卡号 + 等级编号 | | 会员信息 | 会员卡号 + 姓名 + 性别 + 年龄 + 电话号码 | | 订单信息 | 会员卡号 + 商品编号 + 购买数量 + 订单金额 | | 商品信息 | 商品编号 + 单价 + 名称 + 库存数量 |
数据流指的是数据结构在系统内传输的路径。
经过严谨的讨论与分析，我们认为的数据流如表 3 所示。
| 数据流 | 数据流 | 数据流 | 数据流 | | 模块 | 名称 | 含义 | 数据结构 | | 等级管理 | 改动等级单 | 会员级别与对应优惠（CUD） | 等级信息 | | 等级管理 | 查询等级流 | 会员级别与对应优惠（R） | 等级信息 | | 前台界面 用户界面 | 改动级别单 | 会员级别（CUD） | 级别信息 | | 前台界面 用户界面 | 查询级别流 | 会员级别（R） | 级别信息 | | 前台界面 用户界面 | 改动会员单 | 会员的各种信息（CUD） | 会员信息 | | 前台界面 用户界面 | 查询会员流 | 会员的各种信息（R） | 会员信息 | | 收银台 用户界面 | 改动订单单 | 会员订单的各种信息（CUD） | 订单信息 | | 收银台 用户界面 | 查询订单流 | 会员订单的各种信息（R） | 订单信息 | | 仓库管理 | 改动商品单 | 商品信息的各种信息（CUD） | 商品信息 | | 仓库管理 | 查询商品流 | 商品信息的各种信息（R） | 商品信息 |
- 数据存储：
- 数据存储是数据结构停留或着保存的地方。
经过严谨的讨论与分析，我们认为的数据存储如表 4 所示。
| 数据存储 | 数据存储 | 数据存储 | 数据存储 | | 编号 | 名称 | 简述 | 数据结构 | | D1 | 会员等级表 | 存储会员级别与对应优惠 | 等级信息 | | D2 | 会员卡表 | 存储会员级别 | 级别信息 | | D3 | 会员信息表 | 存储会员的各种信息 | 会员信息 | | D4 | 会员订单表 | 存储会员订单的各种信息 | 订单信息 | | D5 | 商品信息表 | 存储商品信息的各种信息 | 商品信息 |
数据是有很多无用重复的数据的，如果将这些数据直接分析或者存储的话，将影响系统效率，此时需要对数据进行加工。
经过严谨的讨论与分析，我们认为的数据存储如表 5 所示。
| 处理逻辑 | 处理逻辑 | 处理逻辑 | 处理逻辑 | 处理逻辑 | | 编号 | 名称 | 简述 | 数据流 | 数据存储 | | P1.1 | 查询等级；界面 | 处理查询等级；的相关输入 | 查询等级 | 会员等级表 | | P1.2 | 修改等级；界面 | 处理修改等级；的相关输入 | 改动等级 | 会员等级表 | | P2.1 | 查询会员；界面 | 处理查询会员；的相关输入 | 查询级别、；查询会员 | 会员卡表、；会员信息表 | | P2.2 | 修改会员；界面 | 处理修改会员；的相关输入 | 改动级别、；改动会员 | 会员卡表、；会员信息表 | | P3.1 | 查询订单；界面 | 处理查询订单；的相关输入 | 查询订单 | 会员订单表 | | P3.2 | 修改订单；界面 | 处理修改订单；的相关输入 | 改动订单 | 会员订单表 | | P4.1 | 查询商品；界面 | 处理查询商品；的相关输入 | 查询商品 | 商品信息表 | | P4.2 | 修改商品；界面 | 处理修改商品；的相关输入 | 改动商品 | 商品信息表 |
- 数据库结构设计
- 概念结构设计
- E-R 图提供表示实体类型、属性和联系的方法，其组成成分有：
- 矩形框：表示实体，在框中记入实体名。
- 框：表示联系，在框中记入联系名。
- 椭圆形框：表示实体或联系的属性，将属性名记入框中。
- 名，则在其名称下划一下划线。
- 连线：实体与属性、实体与联系、联系与属性之间用直线相连。
- 一对一联系，要在两个实体连线方向各写 1。
- 一对多联系，要在一的一方写 1，多的一方写 N。
- ，则要在两个实体连线方向各写 N、M。
- 局部 E-R 图
- 当前的超市会员管理系统的五大模块的局部 E-R 图如图 1 所示。
- 全局 E-R 图
当前的超市会员管理系统的 E-R 图如图 2 所示。
- 逻辑结构设计
- 建立关系模型
- 关系模式是指关系的，现实世界随着时间在不断地变化，因而在不同的时刻，关系模式的关系也会有所变化。
- 但是，现实世界的许多己有事实限定了关系模式所有可能的关系必须满足一定的完整性约束条件。这些约束或者通过对属性取值范围的限定，或者通过间的相互关联反映出来。关系模式应当刻画出这些完整性约束条件。
- 下面将 E-R 图转换为关系模式，关系的码使用下划线标出。
- 会员信息表：
（会员卡号、姓名、性别、年龄、电话号码）
- 会员等级表：
- （等级编号、名称、优惠）
- 会员卡表：
- （会员卡号、等级编号、注册时间）
- 会员订单表：
- （商品编号、单价、名称、库存数量）
- 商品信息表：
- （会员卡号、商品编号、购买数量、购买时间、订单金额）
- 关系模式的优化与规范化
- 数据库设计三范式要求：
第一范式：
所有表的设计都必须有主键、每一个字段都是原子性不可再分。
第二范式：
在第一范式的基础之上，要求所有非主键字段必须完全依赖主键。
第三范式：
在第二范式的基础之上，要求所有非主键字段必须直接依赖主键。
- 针对以上要求，我们小组专门对关系模式进行了优化处理，保证了关系模式符合以下要求：
- 每张表具有主键或者外键。
- 如：在最初，我们原本不打算会员信息表使用主键，而是作为被引用的表，由其他外键进行引用。但是，我们却发现，会员信息表中的数据可能发生重复，且 SQL Server 报错,原因是其无法区分表中的每一条记录。
- 每一列属性都是不可再分的属性值，即保证每一列的具有原子性。
- 如：设计会员信息表的“电话号码”属性时，原本设定的是“联系方式”，但是，我们发现这过于空泛，每个人都有不同的联系方式。最后，我们决定设置为“电话号码”。
- 两列的属性相近或相似或一样，尽量合并属性一样的列，确保不产生冗余数据。
- 如：设计商品信息表的“库存数量”属性时，原本还设定了“剩余数量”属性。但是，这两个变量太过于相似，我们发现“剩余数量”是可以通过查询语句的算数计算得到，并不需要专门设置。
- 每一行的数据只能与其中一列相关，即一行数据只做一件事。只要数据列中出现数据重复，就要把表拆分开来。
- 如：设计会员订单表的“优惠”属性时，我们原本打算会员信息表与会员订单表合并，即每一条品订单都有“姓名”、“性别”等。但是我们发现会员可以购买多个商品，会导致多条记录是重复的。因此，才拆分了会员订单表和会员信息表，使用会员信息表的卡号作为外键来区分每一条订单。
- 表中数据不存在传递关系，使得每个属性都跟主键有直接关系而不是间接关系。
- 如：设计会员等级表时，是打算会员信息表与会员等级表合并，不单独多设置一张表。但是，我们发现它与主键“卡号”与等级的“名称”是间接关系，需要“等级”作为中间的传递依赖，这是不符合第三范式的。因此，决定拆分表，将会员信息表与会员等级表拆分开来。
总结为：
- 一对一：一对一，外键唯一！！！
- 一对多：一对多，两张表，多的表加外键！！！
- 多对多：多对多，三张表，关系表两个外键！！！
- 使用 EA 数据库模型设计软件设计实体关系图
- 在这个大数据盛行的时代，和数据打交道变的必不可少了，如果有工具来规范我们的数据库会更加方便我们的生活。
- 而 EA 数据库模型设计(Enterprise Architect)工具是十分强大，不仅能创建 UML 图，还可以根据 E-R 图进行数据库设计，自动创建并导出数据库脚本。在这次的课程设计中，我们可以利用 EA 设计实体关系图。
- 当前的 EA 设计的超市会员管理系统的实体关系图如图 3 所示。
- 数据库物理设计
- 存取路径
注意：
由于数据会慢慢变大、避免重装系统数据丢失等问题，最好手动设置路径。在还没有创建当前用户的数据库时，更改服务器的数据库默认存储路径，更改的方式与路径如图 1 与图标 2 所示。
数据库文件一般分一般有两种，分别是：
- 主数据文件，后缀名为“mdf”。
- 记录日志的文件。后缀名为“ldf”。
创建数据库，其名为：“Membership_Management_System_of_Supermarket”。
在"选择页"的"文件"选项的右侧里面可以看到数据库文件的详细信息。数据的存放位置如图 3 所示。
索引的分类有：
唯一索引、主键索引、聚集索引、非聚集索引。
字段添加索引的条件：
- 数据量庞大。
- 该字段经常出现在 where 后。
- 该字段很少进行 DML 操作。
添加索引的注意事项：
- 建议不要随意添加索引，因为索引也是需要维护的，太多的话反而会降低系统的性能。
- 索引建议通过主键、unique 约束的字段进行查询，效率是比较高的。
索引失效的情况：
- 第 1 种情况：模糊匹配当中以“%”开头。
- 第 2 种情况：使用 or 的时候会失效。
- 第 3 种情况：使用复合索引的时候，没有使用左侧的列查找。
- 第 4 种情况：在 where 当中索引列参加了运算、函数。
- ……
当前数据库系统的表共有 5 张，由于在字段上添加约束会自动创建索引，因此，无需再次另外创建索引。当前数据库的索引如表 1 所示。
| 索引表 | 索引表 | 索引表 | 索引表 | 索引表 | | 名称 | 类型 | 位置 | 字段 | 用途 | | PK__tb_grade__C51FB5AD0519C6AF | 主键、聚集 | tb_grade | Gno | 获取当前系统的等级种类 | | PK__tb_imfor__C497F0FF7F60ED59 | 主键、聚集 | tb_imformation | Ino | 快速检索会员 | | PK__tb_produ__DD37C1490DAF0CB0 | 主键、聚集 | tb_products | Pno | 快速检索商品 | | UQ__tb_imfor__999265D7023D5A04 | 唯一、非聚集 | tb_imformation | Iname | 保证名字唯一 |
exec sp_helpindex 表名在查询的过程中，我们将尽可能的使用索引进行 CRUD 操作，并且避免索引失效的情况，来以此提高语句的查询效率。
- 视图
- 视图：站在不同的角度去看待同一份数据。
- 可以把非常复杂、需要在不同的位置上反复使用的 SQL 语句以视图对象的形式新建。在需要编写这条 SQL 语句的位置直接使用视图对象，可以大大简化开发。
- 并且利于后期的维护，因为修改的时候也只需要修改一个位置就行，只需要修改视图对象所映射的 SQL 语句。
- 我们以后面向视图开发的时候，使用视图的时候可以像使用表一样使用视图。
- 视图的注意事项：
- 必须有权限，删除基表并不删除视图。
- 建议采用与表明显不同的名字命名规则。
- 对由多表连接成的视图修改数据时，不能同时影响一个以上的基表，也不能删除视图中的数据。
- 对视图上的计算列，内置函数列和行集合函数列不能修改。
- 对具有 NOT NULL 的列进行修改时可能会出错。
- 如果某些列因为规则或者约束的限制不能接受从视图插入的数据时，则插入数据可能会失败。
- 当前数据库的视图如表 2 所示：
| 视图 | 视图 | 视图 | | 名称 | 作用 | 涉及的表 | | V_Membership | 查看、修改会员相关的信息与等级 | tb_imformation、tb_card、tb_grade |
- 触发器
- 触发器：加强数据的完整性约束和业务规则。
- 当前数据库的触发器如表 3 所示：
表 3-3：触发器
| 触发器 | 触发器 | 触发器 | | 名称 | 作用 | 位置 | | T_Membership_imformation | 保证录入会员信息表时，自动创建对应的会员卡 | tb_imformation | | T_Membership_orders | 录入订单信息时，自动计算其订单金额并写入当前订单，再减去对应的商品库存 | tb_orders |
事务：让批量的 DML 语句同时成功，或者同时失败！！！
- 一个事务其实就是一个完整的业务逻辑。是一个最小的工作单元。不可再分。事务的特征：原子性、一致性、隔离性、持久性。
- 事务和事务之间的隔离级别有 4 个级别：
- 读未提交：read uncommitted（最低的隔离级别）
没有提交就读到了。
缺点：脏读现象。
- 读已提交：read committed
- 提交之后才能读到。
- 缺点：不可重复读取数据。
- 可重复读：repeatable read
- 提交之后也读不到，永远读取的都是刚开启事务时的数据。
- 缺点：幻影读。
- 序列化/串行化：serializable（最高的隔离级别）
- 事务排队，不能并发！
- 缺点：效率最低。但是解决了所有的问题。
在后续的数据库实施与设计中，一旦需要涉及多个 DML 语句，我们将使用事务机制保证其同时成功或同时失败，以保证数据的完整性。
SQL 语句为：
CREATE DATABASE [Membership_Management_System_of_Supermarket] ON PRIMARY
( NAME = N'Membership_Management_System_of_Supermarket', FILENAME = N'D:\BC\Microsoft SQL Server\Probject\MSSQL10.MSSQLSERVER\MSSQL\DATA\Membership_Management_System_of_Supermarket.mdf', SIZE = 3072KB, FILEGROWTH = 1024KB )
LOG ON
( NAME = N'Membership_Management_System_of_Supermarket_log', FILENAME = N'D:\BC\Microsoft SQL Server\Probject\MSSQL10.MSSQLSERVER\MSSQL\DATA\Membership_Management_System_of_Supermarket_log.ldf', SIZE = 1024KB, FILEGROWTH = 10%)
效果如图 1 所示：
--选定数据库
use [Membership_Management_System_of_Supermarket]
GO
--会员信息表
create table tb_imformation(
Ino int identity(1,1) primary key not null,
Iname char(8) not null unique,
Isex char(2) check(Isex='男' or Isex='女') not null,
Iage int check (Iage>0 and Iage<100),
Iphone char(12)
)
--会员等级表
create table tb_grade (
Gno int primary key,
Gname char(20),
Gdiscounts float check (Gdiscounts>=0) not null
)
--会员卡表
create table tb_card(
Ino int foreign key(Ino) references tb_imformation(Ino),
Gno int foreign key(Gno) references tb_grade(Gno),
Ctime datetime default getdate()
)
--商品信息表
create table tb_products(
pno int primary key,
Pprice float check (Pprice>=0) not null,
Pname char(20),
Pcount int check (Pcount>=0) default 50
)
--会员订单表
create table tb_orders(
Ino int foreign key(Ino) references tb_imformation(Ino),
Pno int foreign key(Pno) references tb_products(Pno),
Ocount int check(Ocount>0) not null,
Otime datetime default getdate(),
Omoney float
)
效果如图 2 所示：
- 数据库的操纵
- 录入等级、商品数据（DML 数据操作语言）
- SQL 语句的语法格式：
insert语句一次插入多条记录：
insert into 表名(字段名1,字段名2)
values(记录1),(记录2),(记录3),(记录4);管理员通过等级管理界面录入等级：
- 需求：
- 增加等级为 1，名称为“钻石”,优惠程度为“50%”。
- 增加等级为 2：名称为“铂金”,优惠程度为“20%”。
- 增加等级为 3：名称为“黄金”,优惠程度为“10%”
- SQL 语句为：
USE [Membership_Management_System_of_Supermarket]
GO
INSERT INTO [tb_grade]
VALUES
(1,&#39;钻石&#39;,0.5),(2,&#39;铂金&#39;,0.8),(3,&#39;黄金&#39;,0.9)效果如图所示：
注意：在实际开发中，使用编程语言编写或拼接SQL语句时，需要避免使用“*”来查询字段，因为其不仅效率低，且可读性差。仓库员通过仓库管理界面录入商品信息：
- 需求：
- 面包 3.5 元 100 箱子
- 辣条 3 元 11 包
- 电动牙刷 320 元 1 把
- 冬衣 400 元 50 件
- 模型玩具 100 元 0 个
- 书本 2.3 元 5 本
- SQL 语句为：
USE [Membership_Management_System_of_Supermarket]
GO
INSERT INTO [tb_products](pno,Pname,Pprice,Pcount)
VALUES
(10010,&#39;面包&#39;,3.5,100),(10086,&#39;辣条&#39;,3,11),(12580,&#39;电动牙刷&#39;,320,1),(10000,&#39;冬衣&#39;,400,50),(00544,&#39;模型玩具&#39;,100,0),(44944,&#39;书本&#39;,2.3,5)效果如图 4 所示：
SQL 语句的语法格式：
创建事务：
begin tran
begin try
SQL语句
end try
begin catch
if @@trancount>0
rollback tran
end catch
if @@trancount>0
commit tran
创建视图对象：
create view 视图名 as DQL语句
创建触发器：
create trigger触发器名
on 表或视图
ter|after|instead of
[DELETE][,INSERT][,UPDATE]
as
T-SQL语句组前台员通过前台管理界面录入会员：
- 需求：
- 小明 男 1371234567 黄金会员
- 李明 男 17 岁 黄金会员
- 公叔春兰 男 36 岁 110120119 黄金会员
- 王先生 男 21 岁 铂金会员
- 文林 女 铂金会员
- 汲春晓 女 63 岁 钻石会员
难点：
- 创建会员信息的同时，需要同时建立对应的会员卡。
- 不通过卡号，指定新创建的会员对应的会员等级
- 当创建会员信息记录成功，而创建会员卡记录失败时，需要将数据库恢复到创建会员信息记录之前的状态。（防止会员信息与会员卡分离）
- SQL 语句为：
- 第一步：创建触发器
USE [Membership_Management_System_of_Supermarket]
GO
-- 保证录入会员信息表时，自动创建对应的会员卡
CREATE TRIGGER
[T_Membership_imformation]
ON
[tb_imformation]
AFTER
INSERT
AS
IF @@rowcount = 0 RETURN
DECLARE @Ino INT,@Iname CHAR(8)
SELECT @Iname=Iname FROM INSERTED
IF EXISTS(SELECT Ino FROM tb_imformation WHERE Iname = @Iname)
BEGIN
SELECT @Ino=Ino FROM tb_imformation WHERE Iname = @Iname
INSERT INTO [tb_card](Ino) VALUES (@Ino)
END
USE [Membership_Management_System_of_Supermarket]
GO
-- 创建会员视图，查看、修改会员相关的信息与等级
CREATE VIEW
[V_Membership]
AS
SELECT
tb_imformation.Ino,Iname,Isex,Iage,
tb_card.Gno,Ctime,Gname,Gdiscounts
FROM
tb_imformation
LEFT JOIN
tb_card
ON
tb_imformation.Ino = tb_card.Ino
LEFT JOIN
tb_grade
ON
tb_grade.Gno = tb_card.Gno
USE [Membership_Management_System_of_Supermarket]
GO
--使用事务机制，保证同时成功或者同时失败
begin tran
begin try
INSERT INTO [tb_imformation](Iname,Isex,Iage,Iphone) VALUES(&#39;小明&#39;,&#39;男&#39;,null,1371234567)
UPDATE [V_Membership] SET Gno=3 WHERE Iname = &#39;小明&#39;
INSERT INTO [tb_imformation](Iname,Isex,Iage,Iphone) VALUES(&#39;李明&#39;,&#39;男&#39;,17,null)
UPDATE [V_Membership] SET Gno=3 WHERE Iname = &#39;李明&#39;
INSERT INTO [tb_imformation](Iname,Isex,Iage,Iphone) VALUES(&#39;公叔春兰&#39;,&#39;男&#39;,39,110120119)
UPDATE [V_Membership] SET Gno=3 WHERE Iname = &#39;公叔春兰&#39;
INSERT INTO [tb_imformation](Iname,Isex,Iage,Iphone) VALUES(&#39;王先生&#39;,&#39;男&#39;,21,null)
UPDATE [V_Membership] SET Gno=2 WHERE Iname = &#39;王先生&#39;
INSERT INTO [tb_imformation](Iname,Isex,Iage,Iphone) VALUES(&#39;文林&#39;,&#39;女&#39;,null,null)
UPDATE [V_Membership] SET Gno=2 WHERE Iname = &#39;文林&#39;
INSERT INTO [tb_imformation](Iname,Isex,Iage,Iphone) VALUES(&#39;汲春晓&#39;,&#39;女&#39;,63,null)
UPDATE [V_Membership] SET Gno=1 WHERE Iname = &#39;汲春晓&#39;
end try
begin catch
if @@trancount>0
rollback tran
end catch
if @@trancount>0
commit tran效果如图 5 所示：
错误操作：
USE [Membership_Management_System_of_Supermarket]
GO
CREATE VIEW
[V_Membership]
AS
SELECT
Iname,Isex,Iage,
Gno,Ctime
FROM
tb_imformation
LEFT JOIN
tb_card
ON
tb_imformation.Ino = tb_card.Ino
INSERT INTO [V_Membership] VALUES(&#39;小明&#39;,&#39;男&#39;,null,null,3,1371234567)效果如图 6 所示：
原因：
- 对由多表连接成的视图修改数据时，不能同时影响一个以上的基表，也不能删除视图中的数据。此时应该分别更新或使用触发器。
收银员通过收银台界面录入订单：
- 需求：
- 1 号 尝试购买 10086 4 个
- 2 号 尝试购买 12580 1 个
- 4 号 尝试购买 44944 3 个
- 6 号 尝试购买 10010 20 个
- 1 号 尝试购买 544 1 个
- 难点：
- 触发器需要自动计算订单金额，并将其结果自动写入表中对应的记录中。
- 触发器需要将对应的商品库存需要减去，库存不足时，插入终止，需要撤销之前的操作，将数据库恢复到插入订单前的状态。
- 触发器需要保证计算的订单金额是与其订单对应的，当相同顾客购买多个不同商品时，触发器计算的金额不能错误写入到其他订单。
- SQL 语句为：
- 第一步：创建触发器
USE [Membership_Management_System_of_Supermarket]
GO
-- 保证录入会员订单时，自动将订单金额写入表中，库存自动减去
CREATE TRIGGER
[T_Membership_orders]
ON
[tb_orders]
AFTER
INSERT
AS
IF @@rowcount = 0 RETURN
DECLARE @Ino int,@Pno int,@Ocount int,@Gdiscounts float,@Omoney float
SELECT @Ino=Ino FROM INSERTED
SELECT @Pno=Pno FROM INSERTED
SELECT @Ocount=Ocount FROM INSERTED
SELECT @Gdiscounts=Gdiscounts FROM [V_Membership] WHERE Ino = @Ino
SELECT @Omoney=Pprice*@Gdiscounts*@Ocount FROM tb_products WHERE pno = @pno
IF EXISTS(SELECT Omoney FROM tb_orders WHERE Ino = @Ino)
BEGIN
-- 截取最后一条记录，保证数据不重复
UPDATE tb_orders SET Omoney = @Omoney WHERE Otime = (SELECT TOP 1 Otime FROM tb_orders ORDER BY Otime desc) AND Pno = @Pno AND Ino = @Ino
-- 库存减去
DECLARE @Pcount int
SELECT @Pcount = Pcount-@Ocount FROM tb_products WHERE Pno = @Pno
UPDATE tb_products SET Pcount = @Pcount WHERE Pno = @Pno
END
USE [Membership_Management_System_of_Supermarket]
GO
--使用事务机制，保证同时成功或者同时失败
begin tran
begin try
INSERT INTO [tb_orders](Ino,pno,Ocount) VALUES(1,10086,4)
INSERT INTO [tb_orders](Ino,pno,Ocount) VALUES(2,12580,1)
INSERT INTO [tb_orders](Ino,pno,Ocount) VALUES(4,44944,3)
INSERT INTO [tb_orders](Ino,pno,Ocount) VALUES(6,10010,20)
-- 错误语句
--INSERT INTO [tb_orders](Ino,pno,Ocount) VALUES(1,544,1)
end try
begin catch
if @@trancount>0
PRINT &#39;出现错误，开始回滚&#39;
rollback tran
end catch
if @@trancount>0
commit tran效果如图 7 所示：
错误操作：
号的商品的库存为“0”，因此写入订单的时候，触发器使得库存减去 1 小于 0，不符合约束。IF 分支侦测到语句异常，通过事务进行回滚，还原表之前的状态。
因此，“INSERT INTO tb_orders VALUES(1,544,1)”的语句是非法的，需要删除。未删除该语句的效果如图 8 所示。
- 查询等级、商品、会员、订单数据（DQL 数据查询语言）
- SQL 语句的语法格式：
查询字段：
select 字段名 from 表名
给查询的列起别名：
使用as关键字起别名。
select 字段 as 别名 from 表名
条件查询：
select字段1,字段2,字段3.... from 表名where条件管理员通过等级管理界面查询等级：
USE [Membership_Management_System_of_Supermarket]
GO
SELECT Gno AS &#39;等级编号&#39;,Gname AS &#39;名称&#39;,Gdiscounts*100 AS &#39;优惠程度&#39; FROM tb_grade
效果如图 9 所示：
仓库员通过仓库管理界面查询商品信息：
USE [Membership_Management_System_of_Supermarket]
GO
SELECT Pno AS &#39;商品编号&#39;,Pprice AS &#39;单价&#39;,Pname AS &#39;商品名称&#39;,Pcount AS &#39;库存数量&#39; FROM tb_products
效果如图 10 所示：
前台员通过前台管理界面查询会员：
需求：查询当前系统的会员信息与会员等级
USE [Membership_Management_System_of_Supermarket]
GO
SELECT Ino AS &#39;卡号&#39;,Iname AS &#39;姓名&#39;,Isex AS &#39;性别&#39;,Iage AS &#39;年龄&#39;, Gname AS &#39;等级&#39;,Ctime AS &#39;注册时间&#39; FROM V_Membership
效果如图 11 所示：
收银员通过收银台界面查询订单：
需求：
- 查询当前数据库中的所有消费记录
- 查询卡号为 4 的所有消费记录
- SQL 语句为：
USE [Membership_Management_System_of_Supermarket]
GO
SELECT
Iname AS &#39;姓名&#39;, tb_products.Pname AS &#39;商品&#39;,Ocount AS &#39;数量&#39;, Omoney AS &#39;总金额&#39;, Otime AS &#39;购买时间&#39;
FROM
tb_orders
LEFT JOIN
tb_imformation
ON
tb_imformation.Ino = tb_orders.Ino
LEFT JOIN
tb_products
ON
tb_products.pno = tb_orders.Pno
2.
SELECT
Iname AS &#39;姓名&#39;, tb_products.Pname AS &#39;商品&#39;,Ocount AS &#39;数量&#39;, Omoney AS &#39;总金额&#39;, Otime AS &#39;购买时间&#39;
FROM
tb_orders
LEFT JOIN
tb_imformation
ON
tb_imformation.Ino = tb_orders.Ino
LEFT JOIN
tb_products
ON
tb_products.pno = tb_orders.Pno
WHERE
tb_orders.Ino = 4
效果如图 12、图 13 所示：
用户通过用户界面查询当前用户的会员与订单：
需求：
- 查询卡号为 6 的会员信息与等级
- 查询卡号为 6 的所有消费记录
- SQL 语句为：
USE [Membership_Management_System_of_Supermarket]
GO
SELECT
Ino AS &#39;卡号&#39;,Iname AS &#39;姓名&#39;,Isex AS &#39;性别&#39;,Iage AS &#39;年龄&#39;, Gname AS &#39;等级&#39;,Ctime AS &#39;注册时间&#39;
FROM
V_Membership
WHERE
Ino = 6
2.
SELECT
Iname AS &#39;姓名&#39;, tb_products.Pname AS &#39;商品&#39;,Ocount AS &#39;数量&#39;, Omoney AS &#39;总金额&#39;, Otime AS &#39;购买时间&#39;
FROM
tb_orders
LEFT JOIN
tb_imformation
ON
tb_imformation.Ino = tb_orders.Ino
LEFT JOIN
tb_products
ON
tb_products.pno = tb_orders.Pno
WHERE
tb_orders.Ino = 6效果如图 14、图 15 所示：
- 改动等级、商品、会员、订单数据（DML 数据操作语言）
- SQL 语句的语法格式：
修改表中的记录：
update 表名 set 字段名1=值1,字段名2=值2,字段名3=值3... where 条件
删除表中的记录：
delete from 表名 where 条件管理员通过等级管理界面修改等级：
需求：
- 修改“铂金”等级，优惠程度为“15%”。
- 增加等级为 4，名称为“至尊”，优惠程度为“75%”。
- 增加等级为 5，名称为“青铜”，优惠程度为“5%”。
- 修改“至尊”等级，名称为“SVIP”。
- 删除“青铜”等级
- SQL 语句为：
USE [Membership_Management_System_of_Supermarket]
GO
--使用事务机制，保证同时成功或者同时失败
begin tran
begin try
UPDATE tb_grade SET Gdiscounts=0.85 WHERE Gname = &#39;铂金&#39;
INSERT INTO tb_grade(Gno,Gname,Gdiscounts) VALUES(4,&#39;至尊&#39;,0.25),(5,&#39;青铜&#39;,0.05)
UPDATE tb_grade SET Gname=&#39;SVIP&#39; WHERE Gname = &#39;至尊&#39;
DELETE FROM tb_grade WHERE Gname = &#39;青铜&#39;
end try
begin catch
if @@trancount>0
rollback tran
end catch
if @@trancount>0
commit tran效果如图 16 所示：
仓库员通过仓库管理界面修改商品信息：
需求：
- 删除商品“模型玩具”。
- 修改“书本”的价格为“3.5”
- 修改“电动牙刷”的库存为“10”
- SQL 语句为：
USE [Membership_Management_System_of_Supermarket]
GO
--使用事务机制，保证同时成功或者同时失败
begin tran
begin try
DELETE FROM tb_products WHERE Pname = &#39;模型玩具&#39;
UPDATE tb_products SET Pprice=3.5 WHERE Pname = &#39;书本&#39;
UPDATE tb_products SET Pcount=10 WHERE Pname = &#39;电动牙刷&#39;
end try
begin catch
if @@trancount>0
rollback tran
end catch
if @@trancount>0
commit tran
效果如图 17 所示：
前台员通过前台管理界面修改会员：
需求：
- 将“小明”的等级升级成“SVIP”。
- 删除“汲春晓”该会员
- 修改“文林”的年龄为“20”
- SQL 语句为：
USE [Membership_Management_System_of_Supermarket]
GO
--使用事务机制，保证同时成功或者同时失败
begin tran
begin try
UPDATE
tb_card
SET
Gno = (SELECT Gno FROM tb_grade WHERE Gname = &#39;SVIP&#39;)
WHERE
Ino = (SELECT Ino FROM tb_imformation WHERE Iname = &#39;小明&#39;)
-- 删除被引用的记录，需要删除引用的记录，再删除被引用的记录
DELETE FROM tb_orders WHERE Ino = (SELECT Ino FROM tb_imformation WHERE Iname = &#39;汲春晓&#39;)
DELETE FROM tb_card WHERE Ino = (SELECT Ino FROM tb_imformation WHERE Iname = &#39;汲春晓&#39;)
DELETE FROM tb_imformation WHERE Iname = &#39;汲春晓&#39;
UPDATE tb_imformation SET Iage=20 WHERE Iname = &#39;文林&#39;
end try
begin catch
if @@trancount>0
rollback tran
end catch
if @@trancount>0
commit tran
收银员通过收银台界面修改订单：
需求：将“小明”的订单全部删除。
USE [Membership_Management_System_of_Supermarket]
GO
--使用事务机制，保证同时成功或者同时失败
begin tran
begin try
DELETE FROM tb_orders WHERE Ino = (SELECT Ino FROM tb_imformation WHERE Iname = &#39;小明&#39;)
end try
begin catch
if @@trancount>0
rollback tran
end catch
if @@trancount>0
commit tran
- 效果如图 19 所示：
- 数据库的删除
- 删除父表、子表的语法格式：
父表：被引用的表，如：t_class表
子表：引用的表，如：t_student表
删除顺序：先删子，再删父。
创建表的顺序：先创建父，再创建子。
删除数据的顺序：先删子，再删父。
插入数据的顺序：先插入父，再插入子。
drop：不再需要该表。
truncate：仍要保留该表结构，但删除所有记录。
delete：删除表的部分记录。
需求：删除数据库中所有的表
USE [Membership_Management_System_of_Supermarket]
GO
--使用事务机制，保证同时成功或者同时失败
begin tran
begin try
DROP TABLE tb_orders
DROP TABLE tb_products
DROP TABLE tb_card
DROP TABLE tb_grade
DROP TABLE tb_imformation
end try
begin catch
if @@trancount>0
rollback tran
end catch
if @@trancount>0
commit tran
需求：删除数据库
USE master
GO
DROP DATABASE [Membership_Management_System_of_Supermarket]
错误操作：
原因：
- 没有“USE master GO”的语句。
- 我们不能删除当前正在使用的数据库。所以要先使用 master 数据库，这样就能解除要删除的当前数据库的正在使用的状态。
- 参考文献
- 明日科技．SQL Server 从入门到精通[M]. 清华大学出版社,第二版, 2017,9,1
- 刘征海．数据库原理与应用 SQLServer2012 基于计算思维[M]. 上海交通大学出版出版社,第一版,2018,1
- 彭林,余艳．SQLServer2000 金典教程[M]. 人民邮电出版社,第一版,2001,8
- 王珊 萨师煊．数据库系统概论[M]．高等教育出版社,第五版,2014.9
- Ben Forta．MySQL 必知必会[M]．人民邮电出版社,第一版,2009.1
- 胡致杰,胡羽沫,李代平．数据库系统原理及应用课程设计与实验指导[M]．清华大学出版社,第一版,2018.9
- 刘金岭,冯万利．数据库系统及应用教程 SQLServer2008[M]．清华大学出版社,第一版,2013.9
- 陈贻品,贾蓓,和晓军．SQL 从入门到精通[M]．中国水利水电出版社,第一版,2020.1
- 贾铁军．数据库原理及应用 SQL Server 2016 [M]．机械工业出版社,第一版,2017.9
- 赵正文. 现代数据库技术[M]. 电子科技大学出版社, 2013.
- 祁新安, 侯清江. SQL Server 数据库的运用研究[J]. 制造业自动化, 2010(A7):3.
- 秦亚丛. 浅谈 SQL Server 数据库的特点和基本功能[J]. 数码世界, 2017(7):1.
- 张海艳. SQL Server 数据库的运用研究[J]. 中国新技术新产品, 2015(19):1.
- 张文雯. SQL Server 视图概述[J]. 市场调查信息：综合版, 2020(10):1.
- 石坤泉. 基于 MySQL 数据库的图书管理系统安全稳定性研究[J]. 电子技术与软件工程, 2016(1):1.
- 致 谢
这次课程设计也激发了我们今后努力学习的兴趣，我们想这将对以后的学习产生深远的影响。让我们充分认识到团队合作的重要性只有分工协作才能保证整个项目的有条不絮。另外在课程设计的过程中当我们碰到不明白的问题时指导老师总是耐心的讲解给我们的设计以极大的帮忙使我们获益匪浅。因此十分感谢老师的教导。透过这次设计我们懂得了学习的重要性了解到理论知识与实践相结合的重要好处学会了坚持、耐心和努力这将为自我今后的学习和工作做出了最好的榜样。我们认为作为一名软件工程专业的学生这次课程设计是很有好处的。更重要的是如何把平时所学的东西应用到实际中。虽然对于这门课懂的并不多很多基础的东西都还没有很好的掌握，觉得很难，也没有很有效的办法透过自身去理解，但是靠着这一个多礼拜的“学习”在小组同学的互帮互助和讲解下渐渐对这门课逐渐产生了些许的兴趣，自我开始主动学习并逐步从基础慢慢开始弄懂它。
首先我们要感谢我的老师在课程设计上给予我的指导、提供给我的支持和帮助，这是我能顺利完成这次设计的主要原因，更重要的是老师帮我解决了许多技术上的难题，让我能把系统做得更加完善。在程序完善的过程中，我们也遇到了这样或那样的问题，但经过自己的不懈努力及查阅大量的资料，最终都得到了基本满意的答案。在此期间，我不仅学到了许多新的知识，而且也开阔了视野，提高了自己的设计能力。
同时，在课程设计中,老师严谨的治学态度、丰富渊博的知识、敏锐的学术思维、精益求精的工作态度以及侮人不倦的师者风范是我终生学习的楷模，老师们的高深精湛的造诣与严谨求实的治学精神，将永远激励着我。在我写报告期间，导师渊博的学识、严谨求实的科学精神、一丝不苟的治学态度和高尚的品格，深深的感染了我和每一个同学。报告的每次改动都离不开老师的辛勤工作，从各个方面来说，审查的工作往往比编写任务更复杂。正是导师百忙中不辞劳苦的帮助，才使我能够顺利完成这个报告，在大学中有一起“共患难”的同学们，对我们有深远影响的老师们，你们是我在今后的宝贵的财富。在此，谨向老师和同学们们致以衷心的感谢和崇高的敬意！
在这次课程设计的撰写中，我们得到了许多人的帮助。首先我们要感谢我的老师在课程设计上给予我的指导、提供给我们支持和帮助，这是我能顺利完成这次设计的主要原因，更重要的是老师帮我解决了许多技术上的难题，让我们能把系统做得更加完善。在此期间，我不仅学到了许多新的知识，而且也开阔了视野，提高了自己的设计能力。其次，我要感谢帮助过我的同学，他们也为我解决了不少我不太明白的设计上的难题。
另外，感谢校方给予我这样一次机会，让我能够独立地完成一次课程设计，并在这个过程当中，给予我们各种方便，使我们在这学期快要结课的时候，能够将学到的知识应用到实践中，增强了我们实践操作和动手应用能力，提高了独立思考的能力。课程设计实现的过程让我们看到了我们自己理论知识上的不足，已掌握的知识也在这次的课程设计中有了质的飞跃，知识能够应用那才是真正掌握了，也希望学校多给我们一些这样的机会。
感谢所有任课老师和所有同学给自己的指导和帮助，本设计的完成是在我们的导师老师的细心指导下进行的。在本次设计遇到问题时老师不辞辛苦的讲解才使得我的设计顺利的进行。从设计的选题到资料的搜集直至最后设计的修改的整个过程中，花费曲老师很多的宝贵时间和精力，在此向老师表示衷心地感谢！还要感谢和我同一班级的同学，是你们在我平时设计中和我一起探讨问题，并指出我设计上的误区，使我能及时的发现问题把设计顺利的进行下去，没有你们的帮助我不可能这样顺利地结稿，是他们教会了我们专业知识，教会了我们如何学习。正是由于他们，我们才能在课程设计方面取得卓越的进展，在此表示深深的谢意。再次感谢老师长期以来悉心的指导和在设计过程提供的大量资料和修改意见，为日后的工作和更进一步的学习打下了坚实的基础，也积累了许多宝贵的设计经验。 |
|
发表于 2022-12-31 17:56:40