药品管理系统数据库设计：从数据字典到触发器实践

简介这是一份数据库课程设计报告以药品管理系统为背景基于SQL Server平台面向计算机、数据库相关专业学生及需要完成课程设计的开发者。资源为单个doc文档约1.07MB内容完整覆盖需求分析、概念设计、逻辑设计、物理设计到数据库实施等关键环节包含业务流程图、数据字典、分E-R图与全局E-R图等核心内容可直接作为课程设计报告撰写与数据库建模的参考范例。文档详细展示了从业务梳理到数据库表结构设计、索引策略及实施操作的完整流程帮助读者理解如何将数据库理论应用于实际管理系统开发。已有1037人学习使用适合正在筹备数据库课程设计或希望掌握药品信息管理项目设计思路的学习者。1. 从手工台账到 SQL Server药品管理系统为什么值得拆开看药品管理系统是数据库课程设计里的常客但大部分交上来的作业只讲建表很少讲清楚“为什么这么建”。这份报告的特殊之处在于它把整个数据库设计流程走了一遍从需求分析阶段的业务流程图和数据字典到概念设计阶段的 E-R 图再到逻辑设计、物理设计和实施阶段最后落在一组视图、触发器、存储过程上。业务本身不复杂——药品、制药商、买药人、柜台四类主数据加上订退、售退、存储三类流水核心就一句话所有进出货操作都要让库存表跟着变。适合第一次做完整数据库项目的学生对照流程也适合准备计算机三级数据库或数据库面试的人拿它当案例比对。如果你手头正好在做类似的进销存系统这份报告的 25 个数据项和 8 张表可以直接拿来做底稿。2. 需求分析与概念设计数据字典、业务流程图与 E-R 图建模2.1 业务流程图决定系统边界报告在需求分析阶段做了实地调查结论是当时药店还在用手工台账进货记录、售货记录、库存清点分开写在几个本子里药卖出去以后库存能不能对上全凭经验。这里第一个值得学习的地方是它没有直接开始画表而是先画业务流程图把系统边界定下来。三个核心业务分别是药品购进、药品出售、药品存储。购进业务里购药人员按需求单从制药商进货合格药品入库不合格的退回存储业务里库存管理员处理出库入库并修改库存信息缺货时给购药人员递缺货单售药业务里买药人拿取药单到售药处确认后售出或退回取药单再流转到库存管理员。这三条线下去了系统的处理对象就清楚了药品、制药商、买药人、柜台以及围绕它们发生的订退、售退、存储三类流水。2.2 数据字典25 个数据项如何收敛成 8 个数据结构数据字典是需求分析阶段的输出物报告里一共定义了 25 个数据项。初学者最容易在这里失控把字典写成一张超大字段表。这份报告的处理方式值得借鉴先按实体归类再给每个数据项标上存储结构、别名和取值约束。节选几个如下。数据项编号数据项名存储结构取值约束DI-1Dno 药品编号char(5)主键DI-6price1 药品进价float大于零DI-10Page 年龄int1-120DI-11Psex 性别char(2)男女DI-20Quantity 药品数量int大于零DI-22Supply 订退方式char(4)订购退订这个表的规律性很强编号类字段就是天然的主键候选数量、价格类字段要加 CHECK 约束方式类字段用定长字符存枚举值。把 25 个数据项按语义归拢后得到 8 个数据结构Drug、Maker、Patient、Storage 四张主表Order_Back订退、Buy_Back售退、Stored存储、User用户。主表记录“谁存在”流水表记录“发生了什么”这个划分直接决定了后面关系的粒度。这里有一个在数据库原理课程里反复强调但很容易被跳过的点数据结构不是把字段随便分堆而是按主键粒度分的。Drug 的主键是 DnoPatient 的主键是 Pno那它们之间发生的“买药”行为就不能塞进任何一张主表必须单独建一张以 (Pno, Dno) 为主键的中间表这就是后面 DBuy、DOrder 的由来。2.3 E-R 图到关系模式三组多对多关系的拆解概念设计阶段先从各子系统的分 E-R 图入手再合并成全局 E-R 图。报告给出了三个分 E-R 图药品存储、药品售出、药品退订。合并时要做属性冲突检测比如“处理时间 Time_SD”在售出、退回、订购、退订四个图里都出现但语义不同不能简单合并成一个属性而要落到各自的业务表里。药品和制药商是多对多一个制药商生产多种药品一种药品也可能由多个制药商供货药品和买药人是多对多药品和柜台也是多对多一个药品可以放在多个柜台一个柜台放多种药品。多对多关系在关系模式里必须拆成两个一对多所以 DOrder、OBack、DBuy、BBack、Stored 这些中间表是必然出现的。拆表的代价是查询多一次 JOIN换来的是订单、售退、库存各自独立成行不会出现“一条药品记录里塞多个制药商”这种反范式设计。3. 逻辑设计与物理设计主外键、CHECK 约束与复合索引的取舍3.1 建表 SQL先看三张代表性表逻辑设计阶段要把 E-R 图转成具体的表结构。报告原文里写的是 SQL Server 2000 语法部分细节直接抄会有点问题这里给出调整后的版本create database DrugStore; go use DrugStore; go create table Drug ( Dno char(5) primary key, Dname char(20) not null, Dclass char(8), Dguige char(10), Dbrand char(10), price1 decimal(10, 2) check (price1 0), price2 decimal(10, 2) check (price2 0) ); create table Maker ( Mno char(5) primary key, Mname char(30) not null, Mplace char(10) not null, Mphone char(20) not null ); create table DOrder ( Mno char(5) not null, Dno char(5) not null, Quantity int not null, Time_SD datetime, Supply char(4) not null, primary key (Mno, Dno), foreign key (Mno) references Maker(Mno), foreign key (Dno) references Drug(Dno), check (Quantity 0), check (Supply 订购) );代码说明原文把价格定义成 float这里换成 decimal(10,2)。float 是浮点存储0.10.2 这种运算在二进制下本身就是不精确的做进价售价这种货币计算一定要用定点数。另外原文用户表里 ID 用了 number(4)这是 Oracle 的写法SQL Server 里不认统一改成 int。char(5) 在 SQL Server 中是定长字符存 D001 实际会补成 D001 比较时数据库会忽略尾部空格但应用层取出字符串时可能带空格这个坑在对接 Java、C# 时经常出现作业里可以用真实项目建议直接用 varchar 或 nchar。3.2 CHECK 约束为什么订购和退订要拆成两张表原设计里 DOrder 和 OBack 结构几乎一样都是 (Mno, Dno, Quantity, Time_SD, Supply)但 DOrder 的 CHECK 约束是 Supply订购OBack 的 CHECK 是 Supply退订DBuy 和 BBack 也是这样拆的。这个设计是刻意的把业务方向用 CHECK 钉死在表上插入时如果传错值数据库直接拒绝不靠应用层判断。坏处是查“某个药品全年的订购加退订总量”时要 UNION 两张表查询语句变长但换来的是数据语义的强约束。这个取舍在课程设计评分时是加分项。很多人会把 Supply 设计成一张表里可取任意值再用 where 条件区分那样也不是不行但 CHECK 约束一拆数据字典里的“订退方式”就和表边界对齐了可读性更强。我一般会保留这种拆分同时在视图层把两张表 UNION 起来暴露给上层兼顾约束和查询便利。3.3 复合索引的顺序按药品查库存还是按柜台查库存物理设计阶段选择了索引存取方法关键看索引列顺序。Stored 表主键是 (Lno, Dno)这意味着默认聚簇索引按柜台编号排列同一个柜台的药品在物理上相邻适合“按柜台清点库存”的场景。而业务里更常见的是“查某个药品还剩多少”也就是按 Dno 走这时候主键索引帮不上忙。所以报告给 Stored 额外建了一个 (Dno, Lno) 的唯一索引这是对的。索引键列顺序覆盖的查询PK_Stored(Lno, Dno)按柜台查药品、柜台盘点DLno(Dno, Lno)按药品查库存、缺货统计这里要注意复合索引列顺序不能随便换。(Dno, Lno) 和 (Lno, Dno) 是两棵完全不同的 B 树前者先按药品编号排后者先按柜台编号排。数据库只能利用最左前缀所以建索引之前先想清楚查询条件里哪个列最常出现。另外一个被忽略的点是 Time_SD 没有建立索引报告里的 DBuy_Time_select 存储过程按处理时间查售退记录数据量大时就是全表扫描这是典型的“索引没跟上查询”补一个 (Time_SD) 或 (Time_SD, Dno) 索引就能解决。高频写入的场景下如果主键是随机 UUID插入时会造成索引页频繁分裂进而引发锁等待甚至死锁这份报告的复合主键是业务有序编号反而不容易出现这个问题。4. 数据库实施建库建表、视图隔离、触发器联动与存储过程封装4.1 建库建表注意版本差异报告附录第一句写的是 create datebase DrugStore明显是拼写错误正确是 create database。这种笔误在手写的报告里非常常见直接照着敲会报语法错误。建库之后就是建表前面已经给出了 Drug、Maker、DOrder 的建表 SQL这里补上库存表和售出表create table Stored ( Dno char(5) not null, Lno char(5) not null, Quantity int not null, primary key (Lno, Dno), foreign key (Lno) references Storage(Lno), foreign key (Dno) references Drug(Dno), check (Quantity 0) ); create table DBuy ( Pno char(5) not null, Dno char(5) not null, Time_SD datetime, Quantity int not null, Deal char(4) not null, primary key (Pno, Dno), foreign key (Pno) references Patient(Pno), foreign key (Dno) references Drug(Dno), check (Quantity 0), check (Deal 售出) );两张表的复合主键分别对应“哪个柜台的哪种药”和“哪个病人买了哪种药”。外键 Dno 都指向 Drug(Dno)保证流水表里不能出现药品主表中不存在的编号这就是参照完整性在实施阶段的落地。另外原报告里用户表直接命名为 useruser 是 SQL Server 的保留字建表会直接报错要么加方括号写成 [user]要么改成 sys_user 这类名字字段 Postword 也是明显的笔误应为 Password。保留字避让这类问题在作业里很容易被忽略但在实际 DDL 脚本里第一次执行就会暴露。4.2 视图用 with check option 隔离数据权限安全性设计用了视图加用户授权两层。给买药人看的视图只暴露药品名、规格、品牌、制药商和联系电话不给进价售价给管理员看的视图里才有价格和库存。两个视图的 SQL 如下-- 买药人视角只看到在售药品的公开信息 create view DM_P as select Dname as 药品名字, Dguige as 规格, Dbrand as 品牌, Mname as 制药商名称, Mplace as 产地, Mphone as 联系电话 from Drug, Maker, DOrder where Drug.Dno DOrder.Dno and Maker.Mno DOrder.Mno with check option; -- 管理员视角按柜台查库存带进价售价 create view DS_M as select Drug.Dno, Drug.Dname, price1, price2, Storage.Lname, Stored.Quantity from Drug, Stored, Storage where Drug.Dno Stored.Dno and Storage.Lno Stored.Lno with check option;这里要特别指出报告原稿的一个笔误原 DS_M 视图的连接条件写成了 Drug.Dno Stored.Lno把药品编号和柜台编号当成同一维度比较结果可想而知。正确应该是 Drug.Dno Stored.Dno 且 Stored.Lno Storage.Lno。如果你照着原报告敲代码发现查出来全是空或者列对不上第一个要查的就是连接条件里的字段归属。with check option 的作用是通过视图插入或更新的数据必须满足视图本身的 where 条件。比如基于 DM_P 视图做插入时如果插入的数据在 DOrder 里找不到对应关系操作会被拒绝这层保护在视图权限下放后尤其重要。4.3 触发器四张流水表与库存的联动库存不会自动变化要靠触发器把订购、退订、售出、退回四个动作翻译成 Stored 表的增减。报告里写了四个 after insert 触发器逻辑是对称的。以订购和售出为例create trigger DOrder_insert on DOrder after insert as begin update Stored set Stored.Quantity Stored.Quantity inserted.Quantity from Stored, inserted where Stored.Dno inserted.Dno; end; go create trigger DBuy_insert on DBuy after insert as begin update Stored set Stored.Quantity Stored.Quantity - inserted.Quantity from Stored, inserted where Stored.Dno inserted.Dno; end;inserted 是 SQL Server 在 after insert 触发器里自动生成的虚拟表里面保存本次插入的所有新行。触发器就是用 inserted 里的药品编号去匹配 Stored 表把对应药品的库存加或减掉。退订触发器和退回触发器分别是减和加方向与订购、售出相反四段代码其实只有运算符不同。这里的隐藏问题是update 语句并没有对 Quantity 做非负校验如果库存不够数量会变成负数。这个问题的处理放到最后一节。4.4 存储过程把增删改查封装成参数化接口存储过程这部分体现了“数据库增删改查接口化”的思路。业务层不直接对表 insert而是调用存储过程权限上可以只给应用账号执行存储过程的权限不给底层表的增删改权限SQL 上参数化输入避免拼接字符串带来的注入风险。create procedure Drug_insert drug_no char(5), drug_name char(20), drug_class char(8), drug_guige char(10), drug_brand char(10), drug_price1 decimal(10,2), drug_price2 decimal(10,2) as begin insert into Drug(Dno, Dname, Dclass, Dguige, Dbrand, price1, price2) values (drug_no, drug_name, drug_class, drug_guige, drug_brand, drug_price1, drug_price2); end; go create procedure DBuy_Time_select dbt_time datetime as begin select Pno, Dno, Quantity from DBuy where Time_SD dbt_time; end;存储过程的参数名统一用 开头和表字段名区分开不会有歧义。调用方式很简单exec Drug_insert D010, 感康, 感冒药, 10片, 仁和, 9.50, 15.00; exec DBuy_Time_select 2025-06-01;5. 缺货检查的触发器改进从 PRINT 提示到回滚事务5.1 原触发器的两个问题报告里 Stored_quohuo 触发器在库存低于 10 时只做了一件事print(药品货不足)。print 的结果只出现在 SSMS 的消息选项卡里应用层根本接收不到库存照样一路扣成负数。触发器是数据一致性的最后一道闸门正确的做法是在阈值被突破时直接回滚事务并抛出错误。5.2 改进版触发器create trigger TR_Stored_StockCheck on Stored after insert, update as begin if exists ( select 1 from Stored s join inserted i on s.Dno i.Dno and s.Lno i.Lno where s.Quantity 10 ) begin rollback; raiserror(药品库存低于阈值本次操作已回滚, 16, 1); end end;和原版相比改动有两点设计含义一是用 inserted 关联只检查本次更新涉及的药品库存避免历史存量数据低于阈值时误伤所有后续操作二是 rollback 会把同一批次里已经执行的库存修改全部撤销DBuy 或 DOrder 的插入也一起回滚保证数据一致。5.3 验证方法用两条语句验证即可select Dno, Lno, Quantity from Stored where Dno D001; go insert into DBuy(Pno, Dno, Time_SD, Quantity, Deal) values (P001, D001, getdate(), 95, 售出); go select Dno, Lno, Quantity from Stored where Dno D001;如果 D001 原库存是 100卖出 95 后理论上剩 5低于阈值插入语句会直接报错并回滚第二次查询库存仍然是 100DBuy 表中也没有这条记录。这套触发器的思路换到 MySQL 上同样成立只是语法上要用 BEFORE INSERT 触发器配合 SIGNAL SQLSTATE 45000 抛异常来替代 rollback校验逻辑本身没有区别。本文还有配套的精品资源点击获取