数据库技术是信息系统的一个核心技术

目录 1. 摘要和需求分析……………………………………………………3 2. 数据库结构 2.1 概念设计 全局/整体E-R图……………………………………………7 2.2 逻辑设计 2.2.1建立关系模式……………………………………………9 2.2.2用户子模式建立…………………………………………9 2.2.3关系模式逻辑结构定义…………………………………10 3. 数据库物理设计 3.1 索引…………………………………………………………10 3.2 触发器………………………………………………………10 3.3 存储过程……………………………………………………11 4.数据库入库与测试 4.1数据入库……………………………………………………12 4.2 数据库测试…………………………………………………12 参考文献………………………………………………………………12 附录 SQL语句 1 基本………………………………………………………13 2 视图…………………………………………………………14 3 索引…………………………………………………………15 4 触发器………………………………………………………15 5 存储过程……………………………………………………18 药品进销存管理系统数据库设计 摘要： 随着计算机技术、通信技术和网络技术的迅猛发展，人类社会已经进入了信息化时代。信息资源成为最重要、最宝贵的资源之一，数据库技术已经成为信息社会中对大量数据进行组织与管理的信息系统核心技术和网络信息化管理系统的重要基础。是一种计算机辅助管理数据的方法，它研究如何组织和存储数据，如何高效地获取和处理数据。是通过研究数据库的结构、存储、设计、管理以及应用的基本理论和实现方法，并利用这些理论来实现对数据库中的数据进行处理、分析和理解的技术。 目前中小型的药店营业规模不断扩大，药品的数量和种类不断增加，人工管理的难度也越来越大，如果开发出药品进销存管理系统，通过使用计算机对物质信息进行管理，可以很大程度上方便管理人员对药品的管理，实现管理的高效化和统一化。从而给工作带来了巨大的方便，给药店带来了巨大的经济利益。 1. 需求分析 需求分析阶段就是分析用户的需求，这也是设计数据库的第一步。药品进销存管理系统是中小型药店管理人员方便药店的各个流程的工作为目的的。需求分析这个阶段，主要确定包括业务流程，数据流程，以及要实现的流程和目标，通过数据的分析，可以得到市场的信息，便于管理人员提高，优化管理水平。让销售更加方便，节约成本，带来经济效益。 1.1.业务流图包括药品的采购，库存管理和销售，主要是阐述整个药品的进存的流水作业过程。 如图1-1所示： 1.2 .数据流程图是反映 药店作业过程的数据去向和流向。通过数据流程图，抽象现实的数据到药品进销存的物理模型。再根据这个物理模型抽象出信息流，将物理模型转化成逻辑模型，反映信息在系统中的流动，处理，存储情况。流程图分为顶层数据流图，第一层数据流图，第二层数据流图。 图1-2 顶层数据流程图 图1-3 第一层数据流程图 图1-4第二层数据流程图 （销售） 图1-5 第二层数据流图 （库存管理） 数据库结构设计 主要分为概念设计和逻辑设计两个部分。 2.1 概念设计 1. E-R图设计如下： 属性如下所示： 供应厂家（供应厂编号，供应厂名称，供应厂厂址，供应厂电话，供应厂网站） 药品（药品编码，药品商品名，通用名，药品分类，药品剂型，生产厂家，单价，生产日期，保质期） 仓库（仓库编号，仓库类别，仓库地址） 职员（职工编号，姓名，性别，年龄） 客户（客户编号，名称，性别，电话） 供应（供应量） 购买（购买量） 退货（退货原因，退货数量） 2.2逻辑设计 逻辑设计的任务是把概念结构设计好的基本E-R图转为与选用DBMS产品所支持数据模型相符合的逻辑结构。药品管理系统的设计采用关系模型。将E-R图中的实体的属性和实体型之间的联系转化为关系模式。 对于实体型间的联系有以下不同的情况： （1） 一个1：1联系可以转换为一个独立的关系模式，也可以以任一端对应的模式合并。如果转换为一个独立的关系模式，则与该联系相连的各实体的码以及联系的本身属性都转换为关系的属性。 （2） 一个1：n 联系可以转换为一个独立的关系模式，也可以与任一端对应的模式合并。如果转换为一个独立的关系模式，则与该联系相连的各实体的码以及联系的本身属性都转换为关系的属性。 （3） 一个m:n联系转换为一个关系模式，与该联系相连的个实体的码以及联系本身的属性转换为关系属性，各实体的码组成关系的码或关系的码的一部分。 药品管理系统涉及的关系模式的几种情况主要来自以下几种联系 ：供应厂家和药品信息的关系（m:n），药品和仓库的关系(m:1)，药品通过销售部门和客户建立的联系（m: n） 。 2.2.1 建立关系模式 药品进销存系统涉及的关系主要有： 供应厂家的药品为m:n（多对多）的关系，将它们之间的联系转化为独立的关系模式。仓库和药品的关系为1:n（一对多），将其之间的联系与n端实体合并。职工和仓库的关系为1:n（一对多）的关系，也将其之间的联系与n端实体合并。客户和药品之间的联系为m:n（多对多）的关系，将它们之间的联系转换为独立的关系模式。 具体的转换如下图所示，主键或外键已标注： 供应厂家（供应厂编号，供应厂名称，供应厂厂址，供应厂电话，供应厂网站） 药品(药品编码，药品商品名，通用名，药品分类，药品剂型，单价，生产日期，保质期，库存量，报损量) 仓库（仓库编号，仓库类别，仓库管理人员） 管理（职工号，仓库号） 客户（客户编号，名称，性别，电话） 供应（供应厂家编号，药品编号，供应量） 销售（药品编号，客户编号，销售量） 退货（药品编号，客户编号，退货原因，退货数量） 2.2.2 用户子模式建立 根据需求分析研究建立满足不同需求的用户子模式，各个子模式的建立在更大程度上方便数据操作。用户子模式的定义如表所示。具体实现过程见附录3-2 用户子模式定义 编号 用户子模式（VIEW） 作用 V-1 vi supplier 方便查询药品供应商信息 V-2 vi medicine 方便查询各类药品的信息 V-3 vi storehouse 方便查询仓库基本信息 V-4 vi storage 方便查询库存信息 V-5 vi sale 方便查询药品销售信息 2.2.3关系模式逻辑结构定义 药品进销存管理系统涉及的8张表，分别是 Supplier, Medicine, Storehouse, Customer, Buy, Storage, Sale, Back。如下表所示：具体定义见对其对应附录。 编号 基本表定义 安全性和完整性 T-1 Supplier 见附录 T-2 Medicine 见附录 T-3 Storehouse 见附录 T-4 Customer 见附录 T-5 Buy 见附录 T-6 Storage 见附录 T-7 Sale 见附录 T-8 Back 见附录 3.数据库物理设计 数据库在物理设备上的存储结构与存取方法就是数据库的物理结构，它依赖与选定的数据库管理系统。为给一个给定的逻辑数据模型选取一个最合适的应用环境的物理结构的过程，就是数据库的物理设计。这一阶段任务是确定数据库的物理结构，并不断的进行优化处理，主要建立索引，触发器，存储过程。 3.1 索引 建立索引是加快查询的有效手段。建立的索引如下表所示。 编号 名称 定义 备注 1-1 In SupName 见附录 1-2 In MedNo 见附录 1-3 In CusName 见附录 3.2触发器 触发器是用户定义在关系表上的一类由事件驱动的过程。触发器的定义可以更加便捷的实现数据的操作，增加数据操作的灵活性，对数据有更大的控制能力。如进货量，药品的入库保值期的检测。建立的触发器见下表。 编号 触发器名称 定义 作用 T-1 tri med1 见附录 限制更新药品保质期大于6个月 T-2 tri med2 见附录 限制更新药品进价不小于1元 T-3 tri med Date 见附录 限制过期药品不能插入 T-4 tri cus1 见附录 限制将Sale表的购买量小于10的改为10 T-5 tri acc 见附录 限制某一药品仓库存量不大于400 T-6 tri cus2 见附录 限制删除C-4020客户的退货记录 T-7 tri buy1 见附录 限制采购量更新量不小于100 3.3存储过程 存储过程可以实现数据库操作的增删改功能，在执行时需要调用。药品的进存销过程部分存储过程如下所示。 编号 存储过程名称 定义 作用\ P-1 pro sel sup 见附录 查询供货商信息 P-2 pro sel med1 见附录 查询某一药品生产日期 P-3 pro sel med2 见附录 查询呼吸系统药物 P-4 pro upd med3 见附录 更新五官科药品保质期 P-5 pro sel sale1 见附录 查询中草药的销售量 P-6 pro sel back1 见附录 查询某种退货药品的退货量 P-7 pro add sup 见附录 插入一条供应商信息 P-8 pro add med 见附录 插入一条药品信息 P-9 pro add cus 见附录 插入一条客户信息 P-10 pro del med 见附录 删除一条药品记录 P-11 pro del back见附录 删除一条退货信息 P-12 pro order sale见附录 按药品销量排序 4.数据入库与测试 4.1.1 数据入库 各个主要数据表如药品信息，供应厂家信息记录在20条以上，经过整理收集在EXCEL中，设置所需格式用批量导入的方式进行导入到数据库中。先导入基本表，再导入关系表，成功证明导入后的数据表可以打开，导入失败要检验是否符合完整性和逻辑。进行修改重新导入。 4.1.2 数据库测试 数据库测试是对建立的数据库及数据库对象进行测试。设计的测试案例主要有基本信息表数据的查询，插入，删除和更新操作。插入不满足约束条件的数据以检测触发器触发，基本操作都是以存储过程的形式实现代码的封装，执行时调用存储过程。 参考文献 【1】 萨师煊 王珊 《数据库系统概论（第四版）》 北京 高教出版社 【2】 附录 sql 语句 1 基本表 Create database Drugstroe Create table Supplier ( Supno char(10) primary key, Supname char(40) not null, Supaddr char(10), Suptel char(15), Supnet char(20) ）； Create table medicine ( Medno char(10) primary key, Medname char(20) not null, Medtong char(20)not null, Medkind char(10), Medtype char(10), Medpri float, Meddate char(10), Medexp char(10) ); Create table customer ( Cusno char(10) primary key, Cusname char(10) not null, Cussex char(2) check(cussex in(‘man’, ‘woman’))， Custel char(15) not null); Create table buy ( Supno char(10), Medno char(10), Buyqua smallint, Primary key(supno, medno), Foreign key(supno) references supplier(supno), Foreign key(medno)references medicine(medno) ); Create table storage ( Medno char(10), Stono char(10), Stoqua int, Lostqua int, Primary key (stono,medno), Foreign key (stono) references storehouse (stono), Foreign key(medno) reference medicine (medno) ); Create table sale ( Medno char(10)， Cusno char(10), Salqua int, Foreign key(medno) references medicine (medno), Foreign key(medno) reference customer (cusno) ); Create table back ( Medno char(10), Cusno char(10), Backsake char(40), Backqua int, Foreign key(medno) references medicine (medno), Foreign key(cusno) references customer (cusno) ); 2.视图 ---建立视图 Create view vi_supplier as Select supno From supplier create view vi_medicine as select medno from medicine Create view vi_stroehouse as Select stono From storehouse Create view vi_storage as select stono from storage create view vi_sale as select medname 药名，Salequa销售量 from medicine, sale where medicine.medno = sale.medno order by salequa desc 3.索引 Create nonclustered index in_supname On supplier (supname) Create nonclustered index in_medno On medicine (medno) Create nonclustered index in_cusname On customer(cusname) 2. 触发器 触发器的建立 --创建限制插入药品保质期大于6个月 Create triger tri_med1 On medicine After insert ,updata As If exists ( Select medexp From medicine Where medexp < 6 ) begin print ‘保质期必须大于6个月’ rollback end 限制更新的药品进价不小于1元，小于一元的改为1元 create trigger tri_med2 on medicine after insert ,update as declare @medno char(10)， @medipri float Select @medno=medno, @medipri=medipri from inserted if @medipri<1 Update medicine Set medipri=1 Where medno=@medno ---限制过期药品不能完成插入 Create trigger tri_med_data On medicine For insert As If exists ( Select From medicine Where datadiff (mm, meddata()) > medexp ) Begin Print’药品已过期’ Rollback end ---限制将表的购买量小于10的改为10 Create trigger tri_cus1 On sale For update As If update as if update (salequa) if exists （ Select* From inserted join deleted on inserted.medno = deleted.medno Where inserted.salequa >= 10 And deleted.salequa < 10 ) Rollback --- 限制某一药品仓库存储量不大于400 Create trigger tri_acc On storage after insert , update as declare @stono char(10), @accqua int Select @stono=stono, @accqua=accqua From inserted If @ accqua > 400 Print ‘存储量大于400’ ----限制删除C-4020客户的退货记录 Create trigger tri_cus2 On back After delete As if Exists ( select* From deleted Where cusno = ‘C-4020’ ） begin print’删除记录’ end --限制采购量更新量不小于100 Create trigger tri_buy1 On buy For insert, update as if exist ( select* from inserted where buyqua < 100 ) begin print ‘采购量小于100’ end 5存储过程 ---建立存储过程 ---查询供货商信息 Create procedure pro_sel_sup as select * from supplier ---测试执行结果 Execute pro_sel_sup ---查询药品信息 -create pro pro_med As Select *medicine --查询某一药品生产日期 Create pro pro_sel_medl @medname char(20) As Select medname药品名, meddate 生产日期 From medicine Where medname = @ medname 测试执行结果（带参数） Exec pro_sel_med1’护彤颗粒’ ---查询呼吸系统药 Create proc pro_sel_med2 As Select medno编号, medname 药品名称, medtong 通用名 from medicine Where medkind=’呼吸系统药’ ---测试执行结果 Exec pro_sel_med2 ---更新五官科药品保质期 Create proc pro_upd_med3 As Update medicine Set medexp = medexp + 12 Where medkind = ‘五官科药’ ---测试执行结果 Exec pro_upd_med3 ---查询中成药的销售量 Create proc pro_sel_sale As Select medicine药名, salequa 销量 From medicine, sale Where medicine.medno = sale.medno and medkind =’中成药’ ---测试执行结果 Exec pro_ sel_sale ---查询某种原因的药品的退货量 Create proc pro_sel_back @backsake char(40) As Select medname 药名,backqua 退货量 From medicine, back Where medicine.medno = back.medno and @backsake ---测试执行结果 Exec pro_sel_back ’过期’ Exec pro_sel_back ‘数量有误’ ----插入一条药品信息 Create proc pro_add_med @medno char(10), @medname char（20）, @medtong char (20), @medkind char (10), @medtype char (10), @medipri float, @meddate datetime, @medexp int As Insert into medicine Values(@medno, @medname,@medtong,@medking,@medtype,@medipri,@meddate,@medexp) ---测试执行结果 Exec pro_add_med ‘m-2030’, ‘好娃娃’, ‘ 小儿氨酚烷胺颗粒’, ‘呼吸系统药’, ‘ 颗粒剂’, 12, ‘2010-2-3’, 24 Exec pro_med ----插入一条客户信息 Create proc pro_add_cus @cusno char(10), @cusname char(20), @cussex char(2), @custel char(15) As Insert into customer Values (@cusno, @cusname, @cussex,@custel) ---测试执行结果 Exec pro_add_cus’C-4030’, ‘ 张小铭’, ‘ 男’, ‘15255364267’ ----删除一条退货信息 Create proc pro_del_back @medno char(10) As delete From back Where medno =@ medno ---测试执行结果 Exec pro_del_back ‘m-2001’ ---删除一条药品记录 Create proc pro_del_med @medicine char(20) As delete From medicine Where medname =@ medname Exec pro_del_med ‘好娃娃’ ---按销售量排序 Create proc pro_order_sale As Select medname 药品名, saleque 销售量 From medicine, sale Where medicine, medno = sale.medno Order by salequa desc Exec pro_order_sale 存单 提货 提货单 发货单 发货 提货单 缺货 查看 销售部 客户 订货 订货单 提货单 提货 存档 库存单 退货单 退货 仓库 采购员 采购 缺货单 缺货 供应厂家 发货 发货单 验货 入库 药店采购 退货单 退货 入库单 入库 E3 库房 S1 库存台帐 E2 客户 P2 销售 P3 报损 P4 盘存 P1 进货 E1 供应厂家 S1 库存台帐 E2 客户 P6 进销存 E1 供应厂家 E2 客户 P2.1 销售 S1 库存合账 P2.2 确认退货 P2.3 退货 S2 收支帐务 库房 E3 P3.1 库房报损 S1 库存台帐 P2 销售 P3.2 销售报损 供应厂家 供应 药品 存储 仓库 购买 退货 客户