《数据库原理》-药品销售管理系统

1、数据库原理数据库原理课程设计报告课程设计报告 设计题目： 药品销售管理系统 专 业： 信息管理与信息系统 计算机与数据科学学院 2019 年 01 月 10 日 目录 1 概述 .1 1.1 选题的背景与意义.1 1.2 相关技术分析.1 2 系统功能设计.3 2.1 系统总体结构设计图.3 2.2 系统功能模块.3 2.2.1 基本信息模块.3 2.2.2 销售查询模块.4 3 数据库设计 .5 3.1 需求分析设计.5 3.1.1 概述.5 3.1.2 需求阶段的目标及任务 .5 3.1.3 安全性和完整性要求.5 3.1.4 建立数据业务流程及数据字典.6 3.2 概念结构设计.8 3.

2、3 逻辑结构设计.9 3.3.1 逻辑结构实体转换成关系模式 .10 3.5 数据库实施.11 3.6 数据库运行与维护.18 4 总结 .23 5 参考文献 .24 1 概述 1.1 选题的背景与意义 医药作为民生的基本保障之一，是我们生活中不可缺少的部分。近年来，中 国医药行业迅速发展，药店企业多业态经营，仓储式大型超市、中等规模超市、 便利连锁综合发展。随着规模的扩大，传统的医药管理已经不能适应发展的需要， 很难在激烈的竞争中生存。越来越多的医药销售点的普及，规模各不一样。但总 的来说，免不了两个部分：进购药品和销售药品。为了实现这两个部分的功能和 要求，需设计出功能细致的完整系统。该系

3、统需包含对药品信息的管理、对财务 状况的管理等。医药管理是一项琐碎、复杂而又十分细致的工作。手工进行企业 日常的药品销售、出入库的工作，容易出现“开空单”的现象，且呆账、错账时 有发生，而且费时费力。本系统在设计中考虑和克服了上诉问题，实现了企业管 理工作的系统化、规范化和自动化。在本次课程设计中，基于对资料的调查了解 和自身的主观认识粗略设计出如下医药销售系统，以达到实现简单的医药销售的 各个功能的目的。 系统在现有人力和物力条件下，完全具备可以开发出来。作为阶段性产品， 日后的发展空间大，实现方法简单。本系统采用 SQL SERVER，所有数据信息的储 存都由数据库来完成。在结构上也具有很

4、大的灵活性，在安全上也可以得到充分 地保证。固本系统在技术上是完全可以实现的。 国内医药行业处于逢勃发展阶段，许多医药销售行业都要求使用计算机进行 管理，并且实际中也有系统的使用。所以本系统的使用市场非常的广阔，易于推 广和普及。固本系统在市场应用上是完全可行的。 1.2 相关技术分析 要想完成这个课题，需要对数据库相当了解，即对 SQL 语言以及 T-SQL 语言 有一定的了解，下面我们来介绍一下这两门语言。 SQL 语言是结构化查询语言，是一种数据库查询和程序设计语言，用于存取 数据以及查询、更新和管理关系数据库系统；SQL 语句用于取回和更新数据库中 的数据。SQL 可与数据库程序协同工

5、作，比如 MS Access、DB2、Informix、MS SQL Server、Oracle、Sybase 以及其他数据库系统。 存在着很多不同版本的 SQL 语言，但是为了与 ANSI 标准相兼容，它们必须以 相似的方式共同地来支持一些主要的关键词（比如 SELECT、UPDATE、DELETE、INSERT、WHERE 等等） 。 SQL 分类： DDL数据定义语言(CREATE，ALTER，DROP，DECLARE) DML数据操纵语言(SELECT，DELETE，UPDATE，INSERT) DCL数据控制语言(GRANT，REVOKE，COMMIT，ROLLBACK) SQL S

6、erver 用于操作数据库的编程语言，简称 T-SQL。T-SQL 与 PL/SQL 不同， 并没有固定的程序结构。 T-SQL 包括以下 4 个部分： DDL：定义和管理数据库及其对象，例如 create、alter 和 drop 等。 DML：实现对数据库表各对象的操作，例如 insert、update 等。 DCL：数据控制语言，实现对数据库进行安全管理和权限管理等控制，例如 grant、revoke、deny 等。 附加的语言元素。T-SQL 的附加语言元素，包括变量、运算符、函数、注释和流 程控制语句等。 在 T-SQL 中，命令和语句的书写是不区分大小写的。 要想完成好课题，必须要

7、把这些技术掌握好，这是基础，也是必要条件。 2 系统功能设计 2.1 系统总体结构设计图 图 2.1-1 总体结构设计图 2.2 系统功能模块 2.2.1 基本信息模块 图 2.2-1 基本信息模块 2.2.2 销售查询模块 图 2.2-2 销售查询模块 3 数据库设计 3.1 需求分析设计 3.1.1 概述 要求本系统能够投入实际的使用并且满足基本的功能要求。要求具有较高的可靠 性、安全性和易维护性，具有较高的可移植性。 本系统实现了以下的功能： 1）进行药物入库，对原有药物和新进药物进行登记 2）能够实现对药物的查询 3）能够统计当日销售量和当月销售量 3.1.2 需求阶段的目标及任务 需

8、求分析的目标及任务就是为了提取有效的信息，概念模型的抽象化，转化 为计算机系统能够识别的信息。则通过需求分析所得的信息如下： 1）处理对象 药品信息：药品编号、药品名称、药品类别、售价、进价、库存量、供应商、有 效期 员工信息：员工号、姓名、用户名、密码、职位、权限 顾客信息：顾客编号、会员号 供应商:供应商编号、供应商名称、联系人、联系方式 2）处理功能及要求 本次数据库设计需要做到的功能有: 1.药品信息的查询. 2.顾客信息的查询 3.药品销售的查询 4.日销售、月销售额查询 3.1.3 安全性和完整性要求 1）安全性要求 系统安全性要求体现在数据库安全性、信息安全性和系统平台的安全性等

9、方 面。安全性先通过视图机制，不同的用户只能访问系统授权的视图，这样可提供 系统数据一定程度上的安全性，再通过分配权限、设置权限级别来区别对待不同 操作者对数据库的操作来提高数据库的安全性；系统平台的安全性体现在操作系 统的安全性、计算机系统的安全性和网络体系的安全性等方面。 2）完整性要求 系统完整性要求系统中数据的正确性以及相容性。可通过建立主、外键，使 用 check 约束，或者通过使用触发器和级联更新。 在系统进行设计时，一定根据第七章所学知识对数据的安全性及完整性进行 设计。以保证数据库更为完善。 3.1.4 建立数据业务流程及数据字典 系统需求分析主要是通过对药店销售员工的咨询、请

10、教，了解药店的管理规 则和运行机制，并通过上网搜索有关药品管理系统的知识，了解到了药品管理的 现状，以及在管理中的一些问题，然后通过所了解的文字信息，理解数据业务流 程及数据字典，为后期数据库设计提供概念基础。 1.数据流图 数据流图 2.数据字典 （1）数据项：系统涉及的数据项有 44 项 数据项编号数据项编号数据项名数据项名数据项含义数据项含义与其它数据项的关系与其它数据项的关系存储结构存储结构别名别名 DI-1药品编号药品的编号 varchar（10 ） DI-2药品名称药品的名称varchar(50) DI-3所属类别药品的类别varchar(10) DI-4供应商编号供应商的编号va

11、rchar(10) DI-5生产批号生产的批号varchar(10) DI-6生产日期药品的生产日期datetime DI-7有效期药品的有效期限datetime DI-8产地药品的生产产地varchar(10) DI-9进价药品的进价money DI-10单价药品的售价money DI-11库存药品的库存量int DI-12会员折扣会员的折扣nvarchar(10) DI-13员工编号员工的编号varchar(10) DI-14顾客编号顾客的购买号varchar(10) DI-15销售数量药品销售的数量Numeric(18,0) DI-16销售金额药品销售的金额money DI-17销售日期

12、药品销售的日期datetime DI-18姓名员工的姓名varchar(10) 数据项编号数据项编号数据项名数据项名数据项含义数据项含义与其它数据项的关系与其它数据项的关系存储结构存储结构别名别名 DI-19职位员工的职位varchar(10) DI-20供应商名称药品供应商的名称varchar(20) DI-21联系人药品供应商联系人varchar(10) DI-22联系方式药品供应商的电话numeric(11,0) 表 3.1-1 数据项列表 （2）数据结构： 数据结数据结 构编号构编号 数据结构名数据结构名 数据结构数据结构 含义含义 组成组成 DS-1药品信息药店药品信息 药品编号,药

13、品名称,所属类别,供应商编号,生产 批号,生产日期,有效期,产地,进价,单价,库存,会 员折扣 DS-2销售信息药品销售信息 药品编号,员工编号,顾客编号,销售数量,销售金 额,销售日期 DS-3供应商药品供应商信息供应商编号,供应商名称,联系人,联系方式 DS-8员工信息药店员工信息员工编号,姓名,职位 表 3.1-2 数据结构列表 （3）处理逻辑描述 处理编号处理编号处理功能处理功能处理过程处理过程 PR-1 查询 药品信息模块、供应商信息模块、员工信息模块、顾客 信息模块、销售信息模块： 先确定查询所涉及的功能模块；然后，根据要查询的内 容，确定查询数据流向；最后显示查询结果。 表 3.

14、1.3 处理逻辑列表 （4）数据流： 数据流名称数据流名称含义含义来源来源去向去向组成组成 销售单据 顾客购买药品的 单据 员工顾客 药品编号,员工编号, 顾客编号,销售数量, 销售金额,销售日期 表 3.1.4 数据流列表 （5）数据存储： 数据存储名称数据存储名称含义说明含义说明输入数据流输入数据流输出数据流输出数据流 药品信息关于药品的有关信息药品基本信息 表 3.1.5 数据存储列表 3.2 概念结构设计 图 3.2-1 药品局部 E-R 图 图 3.2-2 员工局部 E-R 图 图 3.2-3 供应商局部 E-R 图 图 3.2-4 销售局部 E-R 图 图 3.2-5 全局 E-R

15、 图 3.3 逻辑结构设计 逻辑结构设计的主要目的是把 E-R 图转换为关系模式。 3.3.1 逻辑结构实体转换成关系模式 实体型转换为关系模式。实体的属性就是关系的属性，实体的码就是关系的 码。对于实体间的联系则有以下不同的情况： 一个 m：n 联系转换为一个关系模式。与该联系相连的各实体的码以及联系本 身的属性均转换为关系的属性，而关系的码为各实体码的组合。 一个 1：n 联系可以转换为一个独立的关系模式，也可以与 n 端对应的关系 模式合并。如果转换为一个独立的关系模式，则与该联系相连的各实体的码以及 联系本身的属性均转换为关系的属性，而关系的码为 n 端实体的码 一个 1：1 联系可以

16、转换为一个独立的关系模式，也可以与任意一端对应的 关系模式合并。三个或三个以上实体间的一个多元联系可以转换为一个关系模式。 与该多元联系相连的各实体的码以及联系本身的属性均转换为关系的属性，而关 系的码为各实体码的组合。具有相同码的关系模式可合并。 根据以上的概念设计，可以把以上的几个 E-R 图转化为以下六个关系模式： 供应商 （ 供应商编号，供应商名称，联系人，联系方式） 药品 （药品编号，供应商编号，药品名称，产地，生产批号，所属类别，进价， 单价，库存，会员折扣，生产日期，有效期） 员工 （员工编号，姓名，职位） 顾客（会员号，顾客编号） 销售 （药品编号，员工编号，顾客编号，销售数量

17、，销售金额，销售日期） 供应商：每个非主属性完全函数依赖于主关系键供应商编号 属于 2NF 且不存在 函数传递 所以为 3NF 销售：药品编号，员工编号，顾客编号 3 个属性组合成主关系键，剩余三个非主 属性完全函数依赖于主关系键且不存在函数传递 故为 3NF 顾客：会员号为主关系键，剩余非主属性完全函数依赖于主关系键且不存在函数 传递故为 3NF 员工：员工编号为主关系键，剩余每个非主属性完全函数依赖于主关系键且不存 在函数传递故为 3NF 药品：药品编号为主关系键，剩余每个非主属性完全函数依赖于主关系键且不存 在函数传递故为 3NF 3.4 物理结构设计 数据库的物理设计就是为逻辑数据模型

18、选取一个最合适应用要求的物理结构 的过程，在这个阶段中要完成两大任务： （1）确定数据库的物理结构，在关系数据库中主要是存取方法和存储结构； （2）对物理结构进行评价，评价的重点是时间和空间效率。 决定存储结构的主要因素包括存取时间、存储空间和维护代价三个方面。设 计时应当对这三个方面均衡考虑。数据库管理系统提供了聚集和索引两个选择。 聚集就是为了提高查询速度，把一个属性上具有相同值的元组集中地存放 到一个物理块中。如果放不下,可以存到相邻的物理块中.使用聚集后,聚集码相 同的元组集中在一起了，因而聚集值不必在每个元组中重复存储。聚集功能可以 大大提高聚集码进行查询的效率。再主码上建立索引可以

19、提高查询速度，还能避 免重复值录入，确保完整性。对于那些检索非常频繁的非主属性建立索引，从逻 辑上改变了记录的存储位置，从而改变了访问数据的入口点。 在本次药品销售管理系统设计中，我采用了索引的方式，建立多个索引可以 缩短存取时间。以下是我针对具体情况建立的索引： 1.create unique index sci on 供应商(供应商编号) 2.create unique index yg on 员工信息(员工编号) 3.create unique index yp on 药品信息(药品编号,供应商编号) 4.create unique index xs on 销售信息(药品编号,顾客编号,

20、员工编号) 5.create unique index gk on 顾客信息(会员号) 3.5 数据库实施 （一）建立药品销售管理系统数据库 create database 药品销售管理系统 （二）建立各个数据表 1.药品信息 Create table 药品信息 (药品编号 varchar(10) primary key, 药品名称 varchar(50) not null, 所属类别 varchar(10) not null, 供应商编号 varchar(10)foreign key references 供应商(供应商编号), 生产批号 varchar(10) not null, 生产日期

21、 datetime not null, 有效期 datetime not null, 产地 varchar(10) not null, 进价 money not null, 单价 money not null, 库存 int not null, 会员折扣 nvarchar(10), ) 2.供应商 Create table 供应商 (供应商编号 varchar(10) primary key, 供应商名称 varchar (20) not null, 联系人 varchar(10) not null, 联系方式 numeric(11,0) not null, ) 3.销售信息 Create t

22、able 销售信息 (药品编号 varchar(10)foreign key references 药品信息(药品编号) , 员工编号 varchar(10) foreign key references 员工信息(员工编号), 顾客编号 varchar(10) not null, 销售数量 numeric(18,0) not null, 销售金额 money not null, 销售日期 datetime not null ) 4.员工信息 Create table 员工信息 (员工编号 varchar(10) primary key, 姓名 varchar(10) not null, 职位

23、 varchar(10) not null, ) 5.顾客信息 Create table 顾客信息 (会员号 varchar(10) primary key, 顾客编号 varchar(10) not null ) （三）建立视图 （1）用于查询药品基本信息的视图定义如下： create view 药品_view(药品编号, 药品名称,生产日期, 进价, 单价, 库存) as select 药品编号, 药品名称,生产日期, 进价, 单价, 库存 from 药品信息 （2）用于查询员工基本信息的视图定义如下： create view 员工_view(员工编号,姓名) as select 员工编号

24、,姓名 from 员工信息 （3）用于查询药品销售基本信息的视图定义如下： create view 销售_view as select 药品编号, 销售数量,销售日期,sum(销售金额*销售数量) as 销售总 额 from 销售信息 group by 药品编号,销售数量,销售日期 （4）用于查询供应商基本信息的视图定义如下： create view 供应商_view(供应商编号,供应商名称,联系人,联系方式) as select 供应商编号,供应商名称,联系人,联系方式 from 供应商 （5）用于查询顾客基本信息的视图定义如下： create view 顾客_view(会员号,顾客编号)

25、as select 会员号,顾客编号 from 顾客信息 （6）用于查询药品日销售基本信息的视图定义如下： create view 日销售_view as select 销售日期,sum(销售总额) as 日销售总额 from 销售_view group by 销售日期 （7）用于查询药品月销售基本信息的视图定义如下： create view 月_销售_view as select month (销售日期) as 日期,sum(日销售总额) as 月销售总额 from 日销售_view group by 销售日期 （四）建立存储过程 （1）用于添加药品供应商信息的存储过程如下： use 药品销

26、售管理系统 go create procedure 添加供应商信息 (供应商编号 varchar(10), 供应商名称 varchar(20), 联系人 varchar(10), 联系方式 numeric(10,0) ) as insert into 供应商 values (供应商编号,供应商名称,联系人,联系方 式) （2）用于添加药品信息的存储过程如下： use 药品销售管理系统 go create procedure 添加药品信息 (药品编号 varchar(10), 药品名称 varchar(50), 所属类别 varchar(10), 供应商编号 varchar(10), 生产批号

27、varchar(10), 生产日期 datetime, 有效期 datetime, 产地 varchar(10), 进价 money, 单价 money, 库存 int, 会员折扣 nvarchar(10) ) as insert into 药品信息 values (药品编号,药品名称,所属类别,供应商 编号,生产批号, 生产日期,有效期,产地,进价,单价,库 存,会员折扣) （3）用于添加药品销售信息的存储过程如下： use 药品销售管理系统 go create procedure 添加销售信息 (药品编号 varchar(10), 员工编号 varchar(10), 顾客编号 varcha

28、r(10), 销售数量 numeric(18,0), 销售金额 money, 销售日期 datetime ) as insert into 销售信息 values (药品编号,员工编号,顾客编号, 销售数量,销售金额,销售日期) （4）用于添加顾客信息的存储过程如下： use 药品销售管理系统 go create procedure 添加顾客信息 (会员号 varchar(10), 顾客编号 varchar(10) ) as insert into 顾客信息 values (会员号,顾客编号) （5）用于添加员工信息的存储过程如下： use 药品销售管理系统 go create procedu

29、re 添加员工信息 (员工编号 varchar(10), 姓名 varchar(10), 职位 varchar(10) ) as insert into 员工信息 values (员工编号,姓名,职位) （五）设计触发器 删除修改供应商信息的触发器如下： use 药品销售管理系统 go create trigger 删除修改供应商信息 on 供应商 after delete,update as delete from 药品信息 where 药品信息.供应商编号 in(select 供应商编号 from deleted) Go （六）向表中添加数据 1.药品信息添加数据 insert into

30、药品信息(药品编号,药品名称,所属类别,供应商编号,生产批号,生 产日期,有效期,产地,进价,单价,库存,会员折扣) values(blg001,板蓝根,感冒药,ooo111,2018-11-12,2020-11- 12,深圳,10,20,3000,0.85), (blg002,长效西林,抗生素,ooo112,2018-12-12,2020-12-12, 上海,18,40,2000,0.85), (blg003,阿司匹林,感冒药,ooo111,2018-11-12,2020-11-12, 深圳,10,20,3000,0.85), (blg004,心痛定,止痛药,ooo123,2018-12-2

31、3,2020-12-23,天 津,8,30,3000,0.85), (blg005,安定,止痛药,ooo114,2018-11-12,2020-11-12,深圳 ,8,25,3000,0.85), (blg006,阿莫西林,感冒药,ooo135,2018-11-12,2020-11-12, 哈尔滨,10,21,3000,0.85) 2.供应商添加数据 insert into 供应商(供应商编号,供应商名称,联系人,联系方式) values(ooo111,药都制药公司,王先生,132*), (ooo112,上海制药公司,李女士,153*), (ooo114,中国中药公司,张先生,130*), (

32、ooo123,中国医药公司,易先生,132*), (ooo135,广州药品集团,张小姐,1341*) 3.销售信息添加数据 insert into 销售信息(药品编号,员工编号,顾客编号,销售数量,销售金额,销售 日期) values(blg001,0001,01,2,20,2018-12-21), (blg003,0001,02,1,20,2018-12-23), (blg004,0002,01,1,30,2019-01-02), (blg005,0001,03,1,25,2018-01-02), (blg005,0002,03,1,25,2018-12-22), (blg001,0001,

33、01,2,20,2018-12-21) 4.员工信息添加数据 insert into 员工信息(员工编号,姓名,职位) values(0001,张三,员工), (0002,李平,员工), (0003,张王,员工), (0004,李四,员工), (0005,易烊,经理) 5.顾客信息添加数据 insert into 顾客信息(会员号,顾客编号) values(0001,01), (0002,02), (0003,03), (0004,04), (0005,05) （七）设置权限 创建登录名： exec sp_addlogin 张三,1234,药品销售管理系统,null exec sp_addlo

34、gin 张王,1234,药品销售管理系统,null exec sp_addlogin 李平,1234,药品销售管理系统,null exec sp_addlogin 李四,1234,药品销售管理系统,null exec sp_addlogin 易烊,1234,药品销售管理系统,null 创建用户名： exec sp_adduser 张三,张三,public exec sp_adduser 李平,李平,public exec sp_adduser 李四,李四,public exec sp_adduser 张王,张王,public exec sp_adduser 易烊,易烊,db_owner 权限设

35、置： grant select on 药品信息 to 张三 grant select on 销售信息 to 张三 grant select on 顾客信息 to 张三 grant select on 药品信息 to 李四 grant select on 销售信息 to 李四 grant select on 顾客信息 to 李四 grant select on 药品信息 to 李平 grant select on 销售信息 to 李平 grant select on 顾客信息 to 李平 grant select on 销售信息 to 张王 grant select on 药品信息 to 张王 grant select on 顾客信息 to 张王 grant select on 销售信息 to 易烊 grant select on 药品信息 to