简介面向高校数据库课程设计与实践环节的完整设计文档以医院管理系统为案例覆盖从需求分析、E-R概念设计、关系模型转换、用户子模式与权限设置到物理存储设计、建库建表、索引/视图/存储过程/触发器创建以及后期维护更新的完整数据库开发流程。文档从信息、处理和安全完整性三方面梳理需求并通过分E-R与总E-R图展现实体联系再落实为关系模型与分级权限控制方案。资源包为单个doc格式文件共1个文件总体积722KB是典型的课程设计说明书格式内容集中、便于完整精读。目前已有144人学习适合需要完成医院管理课程设计或系统学习数据库全流程的学生参考。读者可从中学到各阶段设计思路与SQL应用示例理解完整性约束和权限控制在真实系统中的落地方法有助于撰写课设报告与答辩准备。1. 需求边界从权限矩阵反推信息要求比界面更重要很多医院管理系统的课程设计会把重心放在界面和增删改查上但这套设计把权限与数据一致性放在需求分析的第一位先限定行政领导、医生、收费员各自能看的视图再回头建表。真正撑起整套系统的其实是一组外键链医患关系把病人挂到正确的科室住院信息让病床与护士挂钩收费链路把药品、数量与金额自动串起来。这份资料适合做数据库课程设计、需要快速搭出完整业务闭环的学生也适合刚接触 SQL Server 触发器与视图、想搞清底层表结构如何承接角色权限的开发。数据库设计的成败在需求阶段就已经决定了后面的表、触发器、存储过程只是把边界落实成代码。2. 概念结构设计分 E-R 图合并出总 E-R 模型的步骤2.1 实体、属性与业务含义的核对概念结构设计阶段的核心产出是分 E-R 图。这套文档里拆出了行政人员、医生、护士、病人、检查及药品、收费人员、病房病床等分图再合成总 E-R 图。分图的价值不在于画得规整而在于把每个实体的属性先钉死后面逻辑结构设计时直接映射成字段。我一般会先把所有实体属性整理成一张核对表对照业务需求逐项检查属性是否完整、有没有把联系误当实体、有没有遗漏主键。下面是这套系统的主要实体清单。实体核心属性业务含义行政人员行政人员编号、姓名、性别、年龄、职务、联系方式系统内最高权限角色可查看全部信息医生医生编号、姓名、性别、年龄、所属科室、联系方式诊疗主体可查询病人及住院信息护士护士编号、姓名、性别、年龄、所属科室住院部的看护主体归属科室与病床区域对应病人病人编号、姓名、性别、年龄、就医科室、联系方式就诊主体挂靠在就医科室下检查及药品编号、名称、单价、检查地点或存放处收费链路中的计价单元收费人员收费人员编号、姓名、性别、年龄只负责收费信息权限被视图隔离病房病床病床编号、所属科室、是否住人住院管理的资源实体用标志位标记占用状态检查这张表时最容易漏的是“检查及药品”。它同时承担两种角色检查项目和药品共用一套编号体系通过 Dnum 区分单价字段在收费存储过程中会被取出来参与计算。如果这里少了一个属性后面的收费存储过程就得跟着改。2.2 把联系转成关系医患、住院、收费三张联系表的落法分 E-R 图里的联系在关系模型中不能只停留在连线上必须转成带外键的表。这套系统里最关键的三张联系表是医患关系医生编号 病人编号 看病时间、住院信息病床号 病人编号 医生编号 护士编号 入住时间、收费信息收费流水账号 收费人员编号 病人编号 药品或检查编号 数量 价格。转表规则其实很固定多对多联系要把两端实体的主键都拿进来做成复合主键或外键一对多联系在“多”的一端加外键。住院信息表比较特殊它同时引用了医生、护士、病人、病床四个实体本质上是一个多实体的交汇节点。原始设计把 Hbednumber 设为主键意味着一个病床只能有一条在住记录这隐含了“该表只保存当前住院状态、不留历史记录”的业务假设。如果想要住院历史可追溯就要把主键换成“病床号 入住时间”的复合主键这是做扩展时必须想清楚的取舍。检查联系表是否合理我会在建库之后跑一段 SQL把系统里的主键字段和实体清单对齐验证有没有漏建表或者主键漂移。SELECT t.name AS 表名, c.name AS 主键字段 FROM sys.tables t JOIN sys.indexes i ON t.object_id i.object_id AND i.is_primary_key 1 JOIN sys.index_columns ic ON i.object_id ic.object_id AND i.index_id ic.index_id JOIN sys.columns c ON ic.object_id c.object_id AND ic.column_id c.column_id WHERE t.type U ORDER BY t.name;上面这段查询利用 SQL Server 系统目录 sys.tables、sys.indexes、sys.columns把当前库所有用户表的主键字段列出来。跑完之后对照实体核对表逐行检查每个实体是否都有主键、联系表的主键是否落在了正确字段上。如果发现某张表的主键是自增列而业务上又要求“一个病人只能有一条在住记录”说明主键选型阶段就没想清楚。2.3 总 E-R 图合并时的冲突点分 E-R 图合成总 E-R 图时常见冲突有四种同名实体的属性不一致比如分图里“病人—医生”的医患关系与“病人—住院”里出现的病人属性前后重复同名联系在不同分图里基数不一样一对多联系的方向画反以及检查及药品这类“一物两用”的实体被拆成两个实体。这套文档里最典型的是“收费人员”和“收费信息”。收费人员作为独立实体在分 E-R 图里只有编号、姓名、性别、年龄四个属性但它与病人、药品之间通过收费信息表产生联系。设计子模式时收费人员只能看到收费信息视图看不到病人和药品的具体内容。这里的权限隔离不是靠界面控制而是靠视图字段裁剪实现的。合并时如果把收费联系画成收费人员直接连病人和药品逻辑关系反而混乱因为收费人员根本不接触诊疗数据。合并建议按三步走先合并实体把同名校验一遍属性再合并联系确认每一根连线两端的外键字段都能在对应表里找到最后补弱实体凡是“离开某个实体就没有独立存在意义”的比如医患关系里的看病时间都挂在联系表上不要单独成表。3. 关系模式与物理设计主外键、字段长度与约束取舍3.1 十张关系的边界划分逻辑结构设计阶段E-R 图被转换成关系模型。这套系统共十张表行政人员表、医生表、护士表、病人表、收费人员表、检查及药品表、病房病床表、医患关系表、住院信息表、收费信息表。前七张来自实体后三张是联系转换而来。表名来源主键外键Administor实体Ano无Doctor实体Dno无Nurse实体Nno无Patient实体Pno无Charger实体Cno无Drug实体Dnum无House实体Hbednumber无Doctor_Patient联系Dno PnoDno、PnoPHouse联系HbednumberDno、Pno、NnoCharge联系TnoCno、Pno、Dnum三张联系表的外键全部指向实体表主键没有出现外键指向非唯一列的情况这一点符合关系模型的引用完整性要求。设计子模式时文档把病人基本信息、住院管理查询、收费信息分别做成视图本质上是在关系模型之上叠加了一层“用户视角裁切”权限控制从这一步就开始介入了。3.2 主键选型业务编号为什么比自增列更合适这套系统的主键几乎全部采用 VARCHAR(10) 的业务编号比如医生编号 D001、收费流水账号 1111111111而不是 SQL Server 常见的 IDENTITY 自增列。这是课程设计场景里很理性的选择业务编号可以直接打印在挂号单和收费单上人工识别时就能看出数据来源联表查询时 where 条件写起来也直观。代价也很明确。业务编号需要应用层保证唯一性一旦输入重复会直接撞主键约束VARCHAR 主键比 INT 主键占用更多索引空间数据量大时叶子节点页数增加扫描开销上升。生产系统一般会采用“自增列做代理主键 业务编号列上加唯一约束”的双轨方案既保证业务可读性又维持索引紧凑。课程设计里直接拿业务编号当主键没问题但要清楚边界它不适用于高频写入、超大规模并发的核心交易表。3.3 字段类型与约束char、varchar、money 的选择逻辑物理结构设计里细节最多也最容易在答辩时被问住。病床编号 Hbednumber 用 CHAR(6) 而不是 VARCHAR因为病床号位数固定、无前导空格问题定长字段在等值查询时更快。检查及药品表的价格用 MONEY 类型SQL Server 的 MONEY 本质是 8 字节定点数精度到万分之一适合单价和总价计算但要注意 MONEY 在做除法或跨数据库迁移时表现不稳定生产环境更推荐 DECIMAL(10,2)迁移到 MySQL 或 Oracle 时不用改字段语义。电话号码字段用 VARCHAR(11) 而不是 BIGINT这是数据库设计里的经典陷阱。手机号如果按数值存前导零会被抹掉而且未来如果允许国际号码数值类型直接存不下。联系方式这类“看起来像数字、实际不参与运算”的数据一律按字符串存。建表前我会先跑一段约束检查脚本确认默认值、非空约束都到位了避免出现病床占用状态没有默认值导致后续触发器失灵的隐性故障。SELECT t.name AS 表名, c.name AS 字段名, dc.definition AS 默认值约束, c.is_nullable AS 是否允许空 FROM sys.default_constraints dc JOIN sys.columns c ON dc.parent_object_id c.object_id AND dc.parent_column_id c.column_id JOIN sys.tables t ON c.object_id t.object_id WHERE t.name IN (House, Charge) ORDER BY t.name;这段查询从 sys.default_constraints 和 sys.columns 两张系统视图里过滤出 House 和 Charge 表的默认值约束详情。跑完后核对两个关键点House.Hflag 这一“是否住人”标志位有没有 DEFAULT 0以及 Charge 表的 Tprice 价格字段是否是计算后写入、不需要默认值。检查通过再建表后面写触发器时才不会被空值干扰。4. 数据库实施建库、建表、索引与视图落地4.1 建库参数数据文件与日志文件的增长策略实施阶段第一步是建库。课程设计文档里给出的 CREATE DATABASE 语句带有明确的文件参数这些参数不是摆设。数据文件 SIZE 10MB、MAXSIZE 300MB、FILEGROWTH 10%日志文件 SIZE 5MB、MAXSIZE 200MB、FILEGROWTH 2MB这种配置对小规模管理系统合适初始空间不浪费磁盘数据库增长时按比例扩展。生产环境建议把数据文件固定为一个较大的初始值并提前规划容量避免频繁自动增长造成磁盘碎片。CREATE DATABASE hospitalsystem ON ( NAME hospital_data, FILENAME E:\DB\hospital_data.mdf, SIZE 10MB, MAXSIZE 300MB, FILEGROWTH 10% ) LOG ON ( NAME hospital_log, FILENAME E:\DB\hospital_log.ldf, SIZE 5MB, MAXSIZE 200MB, FILEGROWTH 2MB );FILENAME 中的 E:\DB 路径在自己机器上如果不存在CREATE DATABASE 会直接报错需要先建好目录或改成实际路径。数据文件按 10% 增长适合表数量多、增量不规律的情况日志文件按固定 2MB 增长是为了控制日志膨胀速度但因为每次只扩 2MB事务量大的时候会产生大量 VLFs反而拖慢写入这一点在后续维护时要留意。4.2 建表顺序先父表后子表外键才不会报错创建表的顺序直接决定了外键约束能否建立。必须先创建被引用的父表Doctor、Patient、Nurse、Charger、Drug、House再创建引用它们的联系表。如果顺序写反外键 REFERENCES 会提示找不到目标表。CREATE TABLE Doctor ( Dno VARCHAR(10) PRIMARY KEY, Dname VARCHAR(20), Dsex VARCHAR(2), Dage INT, Ddept VARCHAR(50), Dtel VARCHAR(11) ); CREATE TABLE Patient ( Pno VARCHAR(10) PRIMARY KEY, Pname VARCHAR(20), Psex VARCHAR(2), Page INT, Ptel VARCHAR(11), Pdept VARCHAR(50) ); CREATE TABLE Doctor_Patient ( Dno VARCHAR(10), Pno VARCHAR(10), DPTime DATE, PRIMARY KEY (Dno, Pno), FOREIGN KEY (Dno) REFERENCES Doctor(Dno), FOREIGN KEY (Pno) REFERENCES Patient(Pno) ); CREATE TABLE PHouse ( Pno VARCHAR(10), Dno VARCHAR(10), Nno VARCHAR(10), HTime DATE, Hbednumber CHAR(6) PRIMARY KEY, FOREIGN KEY (Dno) REFERENCES Doctor(Dno), FOREIGN KEY (Pno) REFERENCES Patient(Pno), FOREIGN KEY (Nno) REFERENCES Nurse(Nno) ); CREATE TABLE Charge ( Tno VARCHAR(10) PRIMARY KEY, Cno VARCHAR(10), Pno VARCHAR(10), Dnum VARCHAR(10), Tnumber INT, Tprice MONEY, FOREIGN KEY (Cno) REFERENCES Charger(Cno), FOREIGN KEY (Pno) REFERENCES Patient(Pno), FOREIGN KEY (Dnum) REFERENCES Drug(Dnum) );代码里两个细节值得单独说。第一Doctor_Patient 用 (Dno, Pno) 复合主键天然限定了“同一个医生和同一个病人只能产生一条医患关系”。如果业务允许同一对医患在不同时间多次挂号这里就应该把主键改成 (Dno, Pno, DPTime)。第二PHouse 的 Hbednumber 既是主键又是外键语义它只允许每个病床保留一条在住记录新病人入住时如果已经有记录INSERT 会撞主键。这种设计能让“病床是否已占用”的判断变得简单直接代价是彻底放弃住院历史。4.3 索引与视图别在已有主键索引的列上重复建索引课程设计文档里的 CREATE INDEX 语句存在一个常见误区对已经设置了 PRIMARY KEY 的列再创建独立索引。SQL Server 的主键默认生成唯一聚集索引重复创建非聚集索引只会增加写入开销查询时既不会更快还占额外空间。真正值得建索引的是外键列和高频过滤列比如 PHouse 表的 Nno按护士查在住病人、Patient 表的 Pdept按科室查病人。视图部分的设计反而是这套系统里最出彩的地方。病人信息视图把 Patient、Doctor、Doctor_Patient 三张表拼在一起暴露给前端的字段带上了中文别名业务人员直接看“主治医生”“就诊时间”不需要理解底层外键关系。CREATE VIEW 病人信息_VIEW AS SELECT Patient.Pno AS 病人编号, Patient.Pname AS 病人, Patient.Psex AS 性别, Patient.Page AS 年龄, Patient.Ptel AS 电话, Patient.Pdept AS 就诊科室, Doctor.Dno AS 主治医生编号, Doctor.Dname AS 主治医生, Doctor_Patient.DPTime AS 就诊时间 FROM Doctor_Patient JOIN Patient ON Patient.Pno Doctor_Patient.Pno JOIN Doctor ON Doctor.Dno Doctor_Patient.Dno;原文档里用的是 FROM Patient, Doctor, Doctor_Patient 加 WHERE 的旧式连接写法功能上没问题但 SQL Server 2008 之后更推荐显式 JOIN ON。显式写法把连接条件从 WHERE 里拆出来表多时不容易漏条件也方便后面加 LEFT JOIN 扩展。视图创建后查询语句可以直接用带别名的字段作为过滤条件。SELECT * FROM 病人信息_VIEW WHERE 病人编号 P001;这行查询里“病人编号”是视图内部 Pno 的中文别名SQL Server 允许在 WHERE 子句直接引用别名不需要知道原表字段名。这种做法落到权限控制上很有意义收费人员的信息视图只暴露收费编号、收费员编号、病人编号、药品编号、数量、价格六个字段即使底层表有更多列视图之外的内容也完全不可见。5. 触发器与存储过程医患匹配、病床占用与自动计费5.1 触发器一医患科室匹配的升级写法触发器一的作用是检查病人挂号时就医科室和医生所属科室是否一致不一致则回滚。原文档用 DECLARE 取 INSERTED 单行再逐字段比较这在单行插入时能工作但批量 INSERT 时只会检查到 INSERTED 的第一行其他行绕过校验。更稳妥的做法是放弃逐行取值直接用 EXISTS 判断整个 INSERTED 集合里是否存在不匹配的行。CREATE TRIGGER 病人医生_匹配检查 ON Doctor_Patient AFTER INSERT AS IF EXISTS ( SELECT 1 FROM INSERTED i JOIN Doctor d ON d.Dno i.Dno JOIN Patient p ON p.Pno i.Pno WHERE d.Ddept p.Pdept ) BEGIN ROLLBACK TRANSACTION; THROW 51000, 医生科室与病人就医科室不匹配, 1; END这段触发器把校验从“逐行对比”改成“集合对比”INSERTED 表里每一对新插入的数据都会和 Doctor、Patient 两张表做关联只要有一对科室不匹配整个事务就回滚。THROW 是 SQL Server 2012 引入的错误抛出语句2008 及更早版本要用 RAISERROR 替代函数签名不同迁移时注意版本差异。5.2 触发器二用条件 UPDATE 替代“先查询再判断”触发器二负责住院登记时的病床占用控制。原逻辑是先从 INSERTED 取病床号再到 House 表查 Hflag 标志位如果等于 1 说明病床占用直接回滚未被占用且科室匹配时把 Hflag 置 1。这套“先 SELECT 再 UPDATE”的流程在单用户环境下没有问题但并发时两个会话可能同时查到 Hflag 0然后都执行 UPDATE造成数据错乱。数据库死锁和丢失更新都容易在这种场景出现。把“查询 判断 修改”合并成一条条件 UPDATE可以利用受影响行数天然完成原子抢占UPDATE House SET Hflag 1 WHERE Hbednumber BEDNUM AND Hflag 0; IF ROWCOUNT 0 BEGIN ROLLBACK TRANSACTION; THROW 51001, 病房正在被使用无法登记, 1; ENDWHERE 条件里的 Hflag 0 是这道防线最关键的参数。两个并发事务同时执行 UPDATE 时只有先拿到的那个会成功第二个受影响行数为 0直接回滚。这比 SELECT 加 UPDATE 省了一次往返也消除了判断与修改之间的时间窗。事务隔离级别即使保持在默认的 Read Committed这种写法也不会出现双会话同时占用同一张病床的问题。5.3 收费存储过程把计价规则收拢到一处收费存储过程把“单价 × 数量 总价”的规则固化在数据库端。应用层每次调用只需传流水号、收费员编号、病人编号、药品或检查编号、数量五个参数总价由过程内部从 Drug 表取出单价计算从源头上保证了计费口径一致。CREATE PROCEDURE 收费 Tno VARCHAR(10), Cno VARCHAR(10), Pno VARCHAR(10), Dnum VARCHAR(10), Tnumber INT AS BEGIN DECLARE Tprice MONEY, Dprice MONEY; SELECT Dprice Dprice FROM Drug WHERE Dnum Dnum; SET Tprice Tnumber * Dprize; INSERT INTO Charge (Tno, Cno, Pno, Dnum, Tnumber, Tprice) VALUES (Tno, Cno, Pno, Dnum, Tnumber, Tprice); END实际执行时如果 Dnum 在 Drug 表里不存在Dprice 是 NULL计算后的 Tprice 也是 NULLINSERT 会把 NULL 写进 Tprice价格字段出现空洞。生产级写法需要先判断 Dprice 是否为 NULL再做插入。调用存储过程时参数顺序与定义顺序一致名称参数可读性更高EXEC 收费 Tno 1000000001, Cno C001, Pno P001, Dnum 000123, Tnumber 4;执行完后可以用 SELECT SUM(Tprice) FROM Charge WHERE Pno P001 来验证该病人的费用累计。把收费规则放进存储过程而不是写在应用层另一个收益是后续如果增加折扣规则或医保字段只需要改过程内部逻辑收费端的客户端代码完全不用动。6. SQL 验证技巧用元数据复查视图与权限隔离6.1 查出每个视图暴露的字段权限隔离做得对不对不能只靠界面点击验证。用系统视图可以直接看到每个视图对外暴露了哪些列和权限矩阵逐项对照比手工点页面快得多。SELECT v.name AS 视图名, c.name AS 暴露字段 FROM sys.views v JOIN sys.columns c ON v.object_id c.object_id WHERE v.name LIKE %_VIEW ORDER BY v.name;假设权限矩阵要求收费人员只能看收费信息_VIEW 中的收费编号、收费员编号、病人编号、药品编号、数量、价格六列那么上面查询结果里收费信息_VIEW 如果多出任何一列都意味着信息泄露需要回改视图定义。这套检查在提交课程设计前跑一遍能把“界面隐藏了但字段还在”的问题直接暴露出来。6.2 用 GRANT 建立最小权限视图只管字段裁剪真正限制用户访问还要靠数据库账号权限。给收费员账号只授收费信息_VIEW 的 SELECT 权限给医生账号授病人信息_VIEW 和医生信息_VIEW行政领导账号才有更大范围权限CREATE LOGIN CashierUser WITH PASSWORD Cashier2025; CREATE USER CashierUser FOR LOGIN CashierUser; GRANT SELECT ON 收费信息_VIEW TO CashierUser; DENY SELECT ON 病人信息_VIEW TO CashierUser;三个账号分别登录后用 SESSION_USER 查看当前身份再执行 SELECT * FROM 收费信息_VIEW 或尝试访问其他视图能直观看到拒绝访问的效果。答辩时可以把这个验证过程做成截图权限设计部分就落地了。6.3 把验收步骤做成脚本更省事的做法是把视图字段检查、用户权限查询、收费标准验证合并成一个验收脚本。查询 sys.database_permissions 可以看到当前库里所有授权记录与 sys.views 的字段清单一起输出形成一份“角色 - 可见视图 - 可见字段”的对照结果整个过程一键完成这也让数据库课程设计里的安全管理章节不再停留在文字描述上。本文还有配套的精品资源点击获取
医院管理系统数据库设计：从E-R图到触发器与存储过程
简介面向高校数据库课程设计与实践环节的完整设计文档以医院管理系统为案例覆盖从需求分析、E-R概念设计、关系模型转换、用户子模式与权限设置到物理存储设计、建库建表、索引/视图/存储过程/触发器创建以及后期维护更新的完整数据库开发流程。文档从信息、处理和安全完整性三方面梳理需求并通过分E-R与总E-R图展现实体联系再落实为关系模型与分级权限控制方案。资源包为单个doc格式文件共1个文件总体积722KB是典型的课程设计说明书格式内容集中、便于完整精读。目前已有144人学习适合需要完成医院管理课程设计或系统学习数据库全流程的学生参考。读者可从中学到各阶段设计思路与SQL应用示例理解完整性约束和权限控制在真实系统中的落地方法有助于撰写课设报告与答辩准备。1. 需求边界从权限矩阵反推信息要求比界面更重要很多医院管理系统的课程设计会把重心放在界面和增删改查上但这套设计把权限与数据一致性放在需求分析的第一位先限定行政领导、医生、收费员各自能看的视图再回头建表。真正撑起整套系统的其实是一组外键链医患关系把病人挂到正确的科室住院信息让病床与护士挂钩收费链路把药品、数量与金额自动串起来。这份资料适合做数据库课程设计、需要快速搭出完整业务闭环的学生也适合刚接触 SQL Server 触发器与视图、想搞清底层表结构如何承接角色权限的开发。数据库设计的成败在需求阶段就已经决定了后面的表、触发器、存储过程只是把边界落实成代码。2. 概念结构设计分 E-R 图合并出总 E-R 模型的步骤2.1 实体、属性与业务含义的核对概念结构设计阶段的核心产出是分 E-R 图。这套文档里拆出了行政人员、医生、护士、病人、检查及药品、收费人员、病房病床等分图再合成总 E-R 图。分图的价值不在于画得规整而在于把每个实体的属性先钉死后面逻辑结构设计时直接映射成字段。我一般会先把所有实体属性整理成一张核对表对照业务需求逐项检查属性是否完整、有没有把联系误当实体、有没有遗漏主键。下面是这套系统的主要实体清单。实体核心属性业务含义行政人员行政人员编号、姓名、性别、年龄、职务、联系方式系统内最高权限角色可查看全部信息医生医生编号、姓名、性别、年龄、所属科室、联系方式诊疗主体可查询病人及住院信息护士护士编号、姓名、性别、年龄、所属科室住院部的看护主体归属科室与病床区域对应病人病人编号、姓名、性别、年龄、就医科室、联系方式就诊主体挂靠在就医科室下检查及药品编号、名称、单价、检查地点或存放处收费链路中的计价单元收费人员收费人员编号、姓名、性别、年龄只负责收费信息权限被视图隔离病房病床病床编号、所属科室、是否住人住院管理的资源实体用标志位标记占用状态检查这张表时最容易漏的是“检查及药品”。它同时承担两种角色检查项目和药品共用一套编号体系通过 Dnum 区分单价字段在收费存储过程中会被取出来参与计算。如果这里少了一个属性后面的收费存储过程就得跟着改。2.2 把联系转成关系医患、住院、收费三张联系表的落法分 E-R 图里的联系在关系模型中不能只停留在连线上必须转成带外键的表。这套系统里最关键的三张联系表是医患关系医生编号 病人编号 看病时间、住院信息病床号 病人编号 医生编号 护士编号 入住时间、收费信息收费流水账号 收费人员编号 病人编号 药品或检查编号 数量 价格。转表规则其实很固定多对多联系要把两端实体的主键都拿进来做成复合主键或外键一对多联系在“多”的一端加外键。住院信息表比较特殊它同时引用了医生、护士、病人、病床四个实体本质上是一个多实体的交汇节点。原始设计把 Hbednumber 设为主键意味着一个病床只能有一条在住记录这隐含了“该表只保存当前住院状态、不留历史记录”的业务假设。如果想要住院历史可追溯就要把主键换成“病床号 入住时间”的复合主键这是做扩展时必须想清楚的取舍。检查联系表是否合理我会在建库之后跑一段 SQL把系统里的主键字段和实体清单对齐验证有没有漏建表或者主键漂移。SELECT t.name AS 表名, c.name AS 主键字段 FROM sys.tables t JOIN sys.indexes i ON t.object_id i.object_id AND i.is_primary_key 1 JOIN sys.index_columns ic ON i.object_id ic.object_id AND i.index_id ic.index_id JOIN sys.columns c ON ic.object_id c.object_id AND ic.column_id c.column_id WHERE t.type U ORDER BY t.name;上面这段查询利用 SQL Server 系统目录 sys.tables、sys.indexes、sys.columns把当前库所有用户表的主键字段列出来。跑完之后对照实体核对表逐行检查每个实体是否都有主键、联系表的主键是否落在了正确字段上。如果发现某张表的主键是自增列而业务上又要求“一个病人只能有一条在住记录”说明主键选型阶段就没想清楚。2.3 总 E-R 图合并时的冲突点分 E-R 图合成总 E-R 图时常见冲突有四种同名实体的属性不一致比如分图里“病人—医生”的医患关系与“病人—住院”里出现的病人属性前后重复同名联系在不同分图里基数不一样一对多联系的方向画反以及检查及药品这类“一物两用”的实体被拆成两个实体。这套文档里最典型的是“收费人员”和“收费信息”。收费人员作为独立实体在分 E-R 图里只有编号、姓名、性别、年龄四个属性但它与病人、药品之间通过收费信息表产生联系。设计子模式时收费人员只能看到收费信息视图看不到病人和药品的具体内容。这里的权限隔离不是靠界面控制而是靠视图字段裁剪实现的。合并时如果把收费联系画成收费人员直接连病人和药品逻辑关系反而混乱因为收费人员根本不接触诊疗数据。合并建议按三步走先合并实体把同名校验一遍属性再合并联系确认每一根连线两端的外键字段都能在对应表里找到最后补弱实体凡是“离开某个实体就没有独立存在意义”的比如医患关系里的看病时间都挂在联系表上不要单独成表。3. 关系模式与物理设计主外键、字段长度与约束取舍3.1 十张关系的边界划分逻辑结构设计阶段E-R 图被转换成关系模型。这套系统共十张表行政人员表、医生表、护士表、病人表、收费人员表、检查及药品表、病房病床表、医患关系表、住院信息表、收费信息表。前七张来自实体后三张是联系转换而来。表名来源主键外键Administor实体Ano无Doctor实体Dno无Nurse实体Nno无Patient实体Pno无Charger实体Cno无Drug实体Dnum无House实体Hbednumber无Doctor_Patient联系Dno PnoDno、PnoPHouse联系HbednumberDno、Pno、NnoCharge联系TnoCno、Pno、Dnum三张联系表的外键全部指向实体表主键没有出现外键指向非唯一列的情况这一点符合关系模型的引用完整性要求。设计子模式时文档把病人基本信息、住院管理查询、收费信息分别做成视图本质上是在关系模型之上叠加了一层“用户视角裁切”权限控制从这一步就开始介入了。3.2 主键选型业务编号为什么比自增列更合适这套系统的主键几乎全部采用 VARCHAR(10) 的业务编号比如医生编号 D001、收费流水账号 1111111111而不是 SQL Server 常见的 IDENTITY 自增列。这是课程设计场景里很理性的选择业务编号可以直接打印在挂号单和收费单上人工识别时就能看出数据来源联表查询时 where 条件写起来也直观。代价也很明确。业务编号需要应用层保证唯一性一旦输入重复会直接撞主键约束VARCHAR 主键比 INT 主键占用更多索引空间数据量大时叶子节点页数增加扫描开销上升。生产系统一般会采用“自增列做代理主键 业务编号列上加唯一约束”的双轨方案既保证业务可读性又维持索引紧凑。课程设计里直接拿业务编号当主键没问题但要清楚边界它不适用于高频写入、超大规模并发的核心交易表。3.3 字段类型与约束char、varchar、money 的选择逻辑物理结构设计里细节最多也最容易在答辩时被问住。病床编号 Hbednumber 用 CHAR(6) 而不是 VARCHAR因为病床号位数固定、无前导空格问题定长字段在等值查询时更快。检查及药品表的价格用 MONEY 类型SQL Server 的 MONEY 本质是 8 字节定点数精度到万分之一适合单价和总价计算但要注意 MONEY 在做除法或跨数据库迁移时表现不稳定生产环境更推荐 DECIMAL(10,2)迁移到 MySQL 或 Oracle 时不用改字段语义。电话号码字段用 VARCHAR(11) 而不是 BIGINT这是数据库设计里的经典陷阱。手机号如果按数值存前导零会被抹掉而且未来如果允许国际号码数值类型直接存不下。联系方式这类“看起来像数字、实际不参与运算”的数据一律按字符串存。建表前我会先跑一段约束检查脚本确认默认值、非空约束都到位了避免出现病床占用状态没有默认值导致后续触发器失灵的隐性故障。SELECT t.name AS 表名, c.name AS 字段名, dc.definition AS 默认值约束, c.is_nullable AS 是否允许空 FROM sys.default_constraints dc JOIN sys.columns c ON dc.parent_object_id c.object_id AND dc.parent_column_id c.column_id JOIN sys.tables t ON c.object_id t.object_id WHERE t.name IN (House, Charge) ORDER BY t.name;这段查询从 sys.default_constraints 和 sys.columns 两张系统视图里过滤出 House 和 Charge 表的默认值约束详情。跑完后核对两个关键点House.Hflag 这一“是否住人”标志位有没有 DEFAULT 0以及 Charge 表的 Tprice 价格字段是否是计算后写入、不需要默认值。检查通过再建表后面写触发器时才不会被空值干扰。4. 数据库实施建库、建表、索引与视图落地4.1 建库参数数据文件与日志文件的增长策略实施阶段第一步是建库。课程设计文档里给出的 CREATE DATABASE 语句带有明确的文件参数这些参数不是摆设。数据文件 SIZE 10MB、MAXSIZE 300MB、FILEGROWTH 10%日志文件 SIZE 5MB、MAXSIZE 200MB、FILEGROWTH 2MB这种配置对小规模管理系统合适初始空间不浪费磁盘数据库增长时按比例扩展。生产环境建议把数据文件固定为一个较大的初始值并提前规划容量避免频繁自动增长造成磁盘碎片。CREATE DATABASE hospitalsystem ON ( NAME hospital_data, FILENAME E:\DB\hospital_data.mdf, SIZE 10MB, MAXSIZE 300MB, FILEGROWTH 10% ) LOG ON ( NAME hospital_log, FILENAME E:\DB\hospital_log.ldf, SIZE 5MB, MAXSIZE 200MB, FILEGROWTH 2MB );FILENAME 中的 E:\DB 路径在自己机器上如果不存在CREATE DATABASE 会直接报错需要先建好目录或改成实际路径。数据文件按 10% 增长适合表数量多、增量不规律的情况日志文件按固定 2MB 增长是为了控制日志膨胀速度但因为每次只扩 2MB事务量大的时候会产生大量 VLFs反而拖慢写入这一点在后续维护时要留意。4.2 建表顺序先父表后子表外键才不会报错创建表的顺序直接决定了外键约束能否建立。必须先创建被引用的父表Doctor、Patient、Nurse、Charger、Drug、House再创建引用它们的联系表。如果顺序写反外键 REFERENCES 会提示找不到目标表。CREATE TABLE Doctor ( Dno VARCHAR(10) PRIMARY KEY, Dname VARCHAR(20), Dsex VARCHAR(2), Dage INT, Ddept VARCHAR(50), Dtel VARCHAR(11) ); CREATE TABLE Patient ( Pno VARCHAR(10) PRIMARY KEY, Pname VARCHAR(20), Psex VARCHAR(2), Page INT, Ptel VARCHAR(11), Pdept VARCHAR(50) ); CREATE TABLE Doctor_Patient ( Dno VARCHAR(10), Pno VARCHAR(10), DPTime DATE, PRIMARY KEY (Dno, Pno), FOREIGN KEY (Dno) REFERENCES Doctor(Dno), FOREIGN KEY (Pno) REFERENCES Patient(Pno) ); CREATE TABLE PHouse ( Pno VARCHAR(10), Dno VARCHAR(10), Nno VARCHAR(10), HTime DATE, Hbednumber CHAR(6) PRIMARY KEY, FOREIGN KEY (Dno) REFERENCES Doctor(Dno), FOREIGN KEY (Pno) REFERENCES Patient(Pno), FOREIGN KEY (Nno) REFERENCES Nurse(Nno) ); CREATE TABLE Charge ( Tno VARCHAR(10) PRIMARY KEY, Cno VARCHAR(10), Pno VARCHAR(10), Dnum VARCHAR(10), Tnumber INT, Tprice MONEY, FOREIGN KEY (Cno) REFERENCES Charger(Cno), FOREIGN KEY (Pno) REFERENCES Patient(Pno), FOREIGN KEY (Dnum) REFERENCES Drug(Dnum) );代码里两个细节值得单独说。第一Doctor_Patient 用 (Dno, Pno) 复合主键天然限定了“同一个医生和同一个病人只能产生一条医患关系”。如果业务允许同一对医患在不同时间多次挂号这里就应该把主键改成 (Dno, Pno, DPTime)。第二PHouse 的 Hbednumber 既是主键又是外键语义它只允许每个病床保留一条在住记录新病人入住时如果已经有记录INSERT 会撞主键。这种设计能让“病床是否已占用”的判断变得简单直接代价是彻底放弃住院历史。4.3 索引与视图别在已有主键索引的列上重复建索引课程设计文档里的 CREATE INDEX 语句存在一个常见误区对已经设置了 PRIMARY KEY 的列再创建独立索引。SQL Server 的主键默认生成唯一聚集索引重复创建非聚集索引只会增加写入开销查询时既不会更快还占额外空间。真正值得建索引的是外键列和高频过滤列比如 PHouse 表的 Nno按护士查在住病人、Patient 表的 Pdept按科室查病人。视图部分的设计反而是这套系统里最出彩的地方。病人信息视图把 Patient、Doctor、Doctor_Patient 三张表拼在一起暴露给前端的字段带上了中文别名业务人员直接看“主治医生”“就诊时间”不需要理解底层外键关系。CREATE VIEW 病人信息_VIEW AS SELECT Patient.Pno AS 病人编号, Patient.Pname AS 病人, Patient.Psex AS 性别, Patient.Page AS 年龄, Patient.Ptel AS 电话, Patient.Pdept AS 就诊科室, Doctor.Dno AS 主治医生编号, Doctor.Dname AS 主治医生, Doctor_Patient.DPTime AS 就诊时间 FROM Doctor_Patient JOIN Patient ON Patient.Pno Doctor_Patient.Pno JOIN Doctor ON Doctor.Dno Doctor_Patient.Dno;原文档里用的是 FROM Patient, Doctor, Doctor_Patient 加 WHERE 的旧式连接写法功能上没问题但 SQL Server 2008 之后更推荐显式 JOIN ON。显式写法把连接条件从 WHERE 里拆出来表多时不容易漏条件也方便后面加 LEFT JOIN 扩展。视图创建后查询语句可以直接用带别名的字段作为过滤条件。SELECT * FROM 病人信息_VIEW WHERE 病人编号 P001;这行查询里“病人编号”是视图内部 Pno 的中文别名SQL Server 允许在 WHERE 子句直接引用别名不需要知道原表字段名。这种做法落到权限控制上很有意义收费人员的信息视图只暴露收费编号、收费员编号、病人编号、药品编号、数量、价格六个字段即使底层表有更多列视图之外的内容也完全不可见。5. 触发器与存储过程医患匹配、病床占用与自动计费5.1 触发器一医患科室匹配的升级写法触发器一的作用是检查病人挂号时就医科室和医生所属科室是否一致不一致则回滚。原文档用 DECLARE 取 INSERTED 单行再逐字段比较这在单行插入时能工作但批量 INSERT 时只会检查到 INSERTED 的第一行其他行绕过校验。更稳妥的做法是放弃逐行取值直接用 EXISTS 判断整个 INSERTED 集合里是否存在不匹配的行。CREATE TRIGGER 病人医生_匹配检查 ON Doctor_Patient AFTER INSERT AS IF EXISTS ( SELECT 1 FROM INSERTED i JOIN Doctor d ON d.Dno i.Dno JOIN Patient p ON p.Pno i.Pno WHERE d.Ddept p.Pdept ) BEGIN ROLLBACK TRANSACTION; THROW 51000, 医生科室与病人就医科室不匹配, 1; END这段触发器把校验从“逐行对比”改成“集合对比”INSERTED 表里每一对新插入的数据都会和 Doctor、Patient 两张表做关联只要有一对科室不匹配整个事务就回滚。THROW 是 SQL Server 2012 引入的错误抛出语句2008 及更早版本要用 RAISERROR 替代函数签名不同迁移时注意版本差异。5.2 触发器二用条件 UPDATE 替代“先查询再判断”触发器二负责住院登记时的病床占用控制。原逻辑是先从 INSERTED 取病床号再到 House 表查 Hflag 标志位如果等于 1 说明病床占用直接回滚未被占用且科室匹配时把 Hflag 置 1。这套“先 SELECT 再 UPDATE”的流程在单用户环境下没有问题但并发时两个会话可能同时查到 Hflag 0然后都执行 UPDATE造成数据错乱。数据库死锁和丢失更新都容易在这种场景出现。把“查询 判断 修改”合并成一条条件 UPDATE可以利用受影响行数天然完成原子抢占UPDATE House SET Hflag 1 WHERE Hbednumber BEDNUM AND Hflag 0; IF ROWCOUNT 0 BEGIN ROLLBACK TRANSACTION; THROW 51001, 病房正在被使用无法登记, 1; ENDWHERE 条件里的 Hflag 0 是这道防线最关键的参数。两个并发事务同时执行 UPDATE 时只有先拿到的那个会成功第二个受影响行数为 0直接回滚。这比 SELECT 加 UPDATE 省了一次往返也消除了判断与修改之间的时间窗。事务隔离级别即使保持在默认的 Read Committed这种写法也不会出现双会话同时占用同一张病床的问题。5.3 收费存储过程把计价规则收拢到一处收费存储过程把“单价 × 数量 总价”的规则固化在数据库端。应用层每次调用只需传流水号、收费员编号、病人编号、药品或检查编号、数量五个参数总价由过程内部从 Drug 表取出单价计算从源头上保证了计费口径一致。CREATE PROCEDURE 收费 Tno VARCHAR(10), Cno VARCHAR(10), Pno VARCHAR(10), Dnum VARCHAR(10), Tnumber INT AS BEGIN DECLARE Tprice MONEY, Dprice MONEY; SELECT Dprice Dprice FROM Drug WHERE Dnum Dnum; SET Tprice Tnumber * Dprize; INSERT INTO Charge (Tno, Cno, Pno, Dnum, Tnumber, Tprice) VALUES (Tno, Cno, Pno, Dnum, Tnumber, Tprice); END实际执行时如果 Dnum 在 Drug 表里不存在Dprice 是 NULL计算后的 Tprice 也是 NULLINSERT 会把 NULL 写进 Tprice价格字段出现空洞。生产级写法需要先判断 Dprice 是否为 NULL再做插入。调用存储过程时参数顺序与定义顺序一致名称参数可读性更高EXEC 收费 Tno 1000000001, Cno C001, Pno P001, Dnum 000123, Tnumber 4;执行完后可以用 SELECT SUM(Tprice) FROM Charge WHERE Pno P001 来验证该病人的费用累计。把收费规则放进存储过程而不是写在应用层另一个收益是后续如果增加折扣规则或医保字段只需要改过程内部逻辑收费端的客户端代码完全不用动。6. SQL 验证技巧用元数据复查视图与权限隔离6.1 查出每个视图暴露的字段权限隔离做得对不对不能只靠界面点击验证。用系统视图可以直接看到每个视图对外暴露了哪些列和权限矩阵逐项对照比手工点页面快得多。SELECT v.name AS 视图名, c.name AS 暴露字段 FROM sys.views v JOIN sys.columns c ON v.object_id c.object_id WHERE v.name LIKE %_VIEW ORDER BY v.name;假设权限矩阵要求收费人员只能看收费信息_VIEW 中的收费编号、收费员编号、病人编号、药品编号、数量、价格六列那么上面查询结果里收费信息_VIEW 如果多出任何一列都意味着信息泄露需要回改视图定义。这套检查在提交课程设计前跑一遍能把“界面隐藏了但字段还在”的问题直接暴露出来。6.2 用 GRANT 建立最小权限视图只管字段裁剪真正限制用户访问还要靠数据库账号权限。给收费员账号只授收费信息_VIEW 的 SELECT 权限给医生账号授病人信息_VIEW 和医生信息_VIEW行政领导账号才有更大范围权限CREATE LOGIN CashierUser WITH PASSWORD Cashier2025; CREATE USER CashierUser FOR LOGIN CashierUser; GRANT SELECT ON 收费信息_VIEW TO CashierUser; DENY SELECT ON 病人信息_VIEW TO CashierUser;三个账号分别登录后用 SESSION_USER 查看当前身份再执行 SELECT * FROM 收费信息_VIEW 或尝试访问其他视图能直观看到拒绝访问的效果。答辩时可以把这个验证过程做成截图权限设计部分就落地了。6.3 把验收步骤做成脚本更省事的做法是把视图字段检查、用户权限查询、收费标准验证合并成一个验收脚本。查询 sys.database_permissions 可以看到当前库里所有授权记录与 sys.views 的字段清单一起输出形成一份“角色 - 可见视图 - 可见字段”的对照结果整个过程一键完成这也让数据库课程设计里的安全管理章节不再停留在文字描述上。本文还有配套的精品资源点击获取
