医院住院病人管理数据库设计

1、_大学软件学院实验报告课程：数据库原理与实用技术实验学期：2012-2013 学年 第二学期 任课教师：薛专业：网络工程学号：姓名：成绩：期末大作业：医院住院病人管理数据库设计一、实验目的（1）掌握数据库设计的基本方法（2 ）掌握各种数据库对象的设计方法二、实验内容1.数据库概要设计：用文字简要描述实体之间的联系，画出E-R图（标出各联系中实体的基数）。E-R图提供了表示实体型、属性和联系的方法。1）实体型：用矩形表示，矩形框内写明实体名；2）属性：用椭圆形表示，并用无向边将其与相应的实体连接起来；3）联系：用菱形表示，菱形框内写明联系名，并用无向边分别与有关实体型连接起来，同时在无向边旁标上

2、联系的类型。注意：如果一个联系也具有属性，则这些属性也要用无向边与该联系连接起来。下面将根据要设计的住院数据库对其中涉及到的实体、实体属性和实体间的联系进行分析和设计。（1）实体及其属性分析根据本次课程设计所给出的初始条件，目前有以下实体：病人实体、病房实体、病床实体、医生实体、科室信息实体和住院登记实体。对于这些实体，它们的实体及属性图如图4-7所示：住院数据库实体及其属性如下图:主治医师（2）分E-R图设计在本次设计中，根据任务书所提供的业务规则，实体和实体之间可以有以下几种联系:1） 医生与病人之间的联系，它们之间的联系图如图1所示；2） 病人与病房以及病房与病床之间的联系，它们之间的联

3、系图如图2所示;3）医生与科室信息之间的联系 ，它们之间的联系图如图 3所示；1 ：病人与医生联系图2 :病人与病房及病房与病床联系图3:医生与科室信息之间的联系(一般来说，一个医生拥有一个办公科室)(3) 基本E-R图设计对于分E-R图，它们之间往往存在一些不一致的地方，即冲突。合并时不能简单的将上述的各个分E-R图画在一起，必须要消除各个分 E-R图中的不一致，以形成一个能为全系统所有用户所共 同理解和接受的统一的概念模型。在上述分E-R图上可以做出修改，最终形成的基本E-R图如下图所示：医院住院数据库基本 E-R图:11科室1病房2、数据表设计：将 E-R图转换为数据表，给出创建各表的S

4、QL语句。一：关系模型设计关系模型的逻辑结构是一组关系模式的集合。将E-R图转换为关系模型实际上就是将实体型、实体型的属性和实体之间的联系转换为一组关系模式，这种转换需要遵守以下原则：1. 一个实体型转换为一个关系模式。实体的属性就是关系的属性，实体的码就是关系的码。2. 对于实体之间的联系有以下几种情况：（1）一个1:1的联系可以转换为一个独立的关系模式，也可以与任意一端对应的关系模式合并。（2） 一个1 : n的联系可以转换成为一个独立的关系模式，也可以与n端对应的关系模式 合并。（3）一个m: n的联系转换为一个关系模式。（4）3个或者3个以上的实体之间的一个多元联系可以转换为一个关系模

5、式。（5）具有相同码的关系模式可以合并。按照上述的原则，根据设计好的 E-R图，可以将其转换为以下一组关系模式，其中关系模式 的码用下横线标出。1病人（病案号，姓名，性别，电话号码，病房编号）此为病人实体型所对应的关系模式。其中病案号为次关系模式的码，而病房编号为该关系模式的外码。2病房（病房编号，收费标准，所属科室，病床号）此为病房实体型所对应的关系模式。其中病房编号唯一确定一个病房，所以为该关系模式的码。3医生（医生编号，姓名，性别，职称，电话号码，部门）此为医生实体型所对应的关系模式。其中医生编号唯一确定一个医生，所以为该关系模式的码。4科室信息（科室编号，科室名，电话，主治医生姓名）此

6、为科室实体型所对应的关系模式。其中科室编号唯一确定一个科室，所以为该关系模式的码。:数据字典:1病人信息的数据项描述:数据项名数据项含义类型长度允许空备注病案号病人的编号VARCHAR15NOT NULL（主键）对应唯一一个病人姓名病人姓名VARCHAR20NULL性别病人性别CHAR2NULL只能取男或女年龄病人年龄char50NULL电话病人电话VARCHAR12NULL病房编号CHAR4NULL住院时由系统分配病床号int2NULL一个病房一般有1-3个床位主治医师CHAR50NULL2病房信息的数据项描述:数据项名数据项含义类型长度允许空备注病房编号病房编号CHAR50NOT NULL

7、（主键）病房编号唯一收费标准INT4NULL单位为（元/天）所属科室VARCHAR50NULL病床情况char50NULL一个病房一般有1-3个床位3医生信息的数据项描述:数据项名数据项含义类型长度允许空备注医生编号医生编号VARCHAR50NOT NULL（主键）对应唯一一个医牛姓名医生姓名VARCHAR50NULL性别医生性别CHAR2NULL只能取男或女年龄医生年龄CHAR50职称医生职称VARCHAR50NULL有可能有多个职称电话医生电话VARCHAR50NULL科至编号所属部门VARCHAR50NULL4科室信息的数据项描述:数据项名数据项含义类型长度允许空备注编号科至编号CHAR

8、50NOT NULL（主键）科至编号唯一科室名VARCHAR50NULL电话VARCHAR50NULL主治医生姓名VARCHAR50NULL关系图如下:1病人信息戈病房信息=病房编号 收费标准 所属料室 病床慣况表的详细列名:性别年齣电蛍%表丁献信息"中的數据 是住谯魏中側測"上4科室信息 卫|科室编号 电话主治医生姓名沱1表“2病房信息"中的敎据，位置是“医院管理"中、u(local)w上病房编号1收费标准所属科室病床情况一览表马医生信息“中的砸位置是"医碗管理"中、叩*初“上匡生编号姓名性别年龄职称电话科室编号r坨表“4科室信割中

9、的数据，位置是"医院管理"中、"(local)"上 科室编号丨电话I主治医生姓名基本表的设计完成数据库的逻辑和物理设计后，需要运用SQL语言对数据库中所涉及的表进行定义，同时要考虑与表有关的完整性约束条件。1.建立病人表：CREATE TABLE 病人(病案号 VARCHAR(15) PRIMARY KEY,姓名 VARCHAR(20) NOT NULL,性别 CHAR(2) CHECK(性别 IN('男','女'),年龄 VARCHAR(100) NOT NULL,电话 VARCHAR(12), 病房编号 CHAR(50

10、) NOT NULL, 病床号 INT, 主治医生姓名 CHAR(50),FOREIGN KEY ( 病房编号 ) REFERENCES 病房(病房编号 );Foreign key ( 主治医生姓名 )references 医生 ( 医生姓名 ),2.建立医生表： create table 医生 (医生编号 VARCHAR(50) not null, 医生姓名 CHAR(10) , 性别CHAR(5) ,年龄CHAR(20),职称VARCHAR(50),电话VARCHAR(50),科室编号 VARCHAR(50), primary key( 医生编号 ),Foreign key ( 科室编号

11、)references 科室 (科室编号 ), );VARCHAR(50) ,3. 建立病房表： create table 病房病房编号CHAR(50) not null,收费标准INT ,(所属科室病房情况 CHAR(50), primary key( 病房编号 ), );4 . 简历科室表create table 科室(编号 VARCHAR(50) not null, 科室名 CHAR(10) , 主治医生姓名 CHAR(5) , 电话 CHAR(20), primary key( 科室编号 ), );1.插入病人信息insert into病人values('001',&#

12、39;阿一 ',' 男','18','333333','101','1','陈一');insert into病人values('002','阿二 ',' 男','18','433333','102','1','陈二');insert into病人values('003','阿三 ',' 男','18',

13、'533333','103','1','陈三');insert into病人values('004','阿四 ',' 男','18','633333','104','1','陈四');insert into fpiA values Q01F/ P?-F/F 1/333333*,r 101'/ lh,F ffi-insert irrto 病人 vainest1 002? / P<? 18?/4

14、3333 ? 102F/ 1 ?陈二：insert into 病人 values (p M3'J 阴三-'/ lC*/533333 ' 103'/ T, h 陈三'： insert into 病人 values(n 104nn18 / 6333331, 1041. T / 阵四（所助向的行數対1行）C所黒响的行数为1行）（所影响的行敕为1行）（所影响的行救再1行插入病房基本信息insert into病房values('101','300','外科','3床位'）；insert into病房v

15、alues('102','400','内科','3床位'）；insert into病房values('103','500','儿科','3床位'）；insert into病房values('104','600','妇科','3床位'）；床味床床3 3 3 3insert into 病房 values IO!1 p 300 1 外卑 insert into 翕度 valumg (T D2J 4UCT内* i

16、nsert into 病房 values (T L03 n 500? j7 JL insert into 翕勇 vainest'' 104''6007 、4（所影响的行数为1行）（所影响的行数为1行）（所影响的行埶为1行）（所影响的行數为1行）插入医生基本信息insert into 医生 values（'001','陈一','男','30','中级','1111','1'）;insert into 医生 values（'002',

17、9;陈二','女','50','高级','2111','2'）;insert into 医生 values('003','陈三','男','30','中级','3111',3);insert into 医生 values('004','陈四','女','40','高级','4111','4');ab中高

18、中葛12 3 4 o o o Ointo values ' OOT, PS insert into ET values(' 002p,1,insert into 医生 valuesf 003?1SS三， itiiart into 医生 values ' D04p ,'四"，（所鞍响的行数拘1行） f所黒响的行数芮1 fr）（所尉响的行数芮1行）插入科室基本信息insert into科室values('101','外科','陈一','1111');insert into科室values(&#

19、39;102','内科','陈二','2111');insert into科室values('103','儿科','陈三','3111');in sert into科室values('104','妇科','陈四','4111');values( values (' values C values Ct所斃响的行载芮1行）101 室室室室102103（所影响的行数曲1行） t所駅响的行數为1行）（所劭响的行

20、数为1行）（所劭响的行数为1行）3、完整性约束：根据需要给数据表添加数据完整性，并说明添加的理由。建立完整性约束：保证医生的真实性，和医生编号的正确性命令已成功呢咸。I< HI已将规则绑走到表的列上®create rule a_ruleexec Sp bindrule a rule,'医生.医生偏丐as(8医生编号 like ' 0-9 0-9 0-9p建立完整性约束：保证病人的真实性，和病人编号的正确性ere art e rule b_ruleas 蘇案号 like ' 0-9 0-9 0-9n |命令已成功完成。exec Sp_bindrule b.

21、rule, F:已将规则绑定到表的列上卩建立完整性约束：保证科室的真实性，和科室编号的正确性create rule cl_ruleas 倉科室编号 like ' 109 0_9 0_9 *ex&c Sp bindrule?科室.科室编号-4ni命令已咸功完成。L已将规则绑定到裹的萸Eh建立完整性约束：保证病房的真实性，和病房编号的正确性create rule d_ruleas Wno like 'w0-9'exec Sp_bindrule d_rule,' 病房.Wno：验证存入的信息:Select * frQM 病人病案号|輕名性别年靜|电话 病房编号

22、病床号主治医生姓容12 3 4 o o o O o o o O_Inr mTJcnr- nsTCDK Q?k一二三四 昭阀阴阳333333 101533333 103533333 104一二三四 陈陈陈陈Select - from 病居病房编号收费标准所属科室病房情;兄科科科科外内儿妇位僮位位医生编号医生姓名性别年龄班称电话科室编号12 3 4 o _u o O 0 0-00胃女胃女舉级级级中高中高12 3-4o _u o- O11 .4 11Select * fru 科室I科室扁号科室名主治医生姓容:III2312 3 4 o o O料科科科 外内儿妃用话uh211131114111二 mf

23、tinsert irrt 口 病人 values005,'小新，男，亍 J 533333T,? 104? , ' 2， 笔一（所鞍响的行數为1行）查看是否存入:Select * fra* 病 |-L* l l12病案号姓容性别年龄电话病房编号病床号主治區生姓名001阿一男18333333 L01002間二 舅18 433333 1021陈一1陈二345DD3 阿三盟 18 533333 103 0Q4 阿四胃 18 633333 LD4 005 小新 舆 & 533333 104三四笔1A 11 _2查找功能selwct病人病房01 号=TD3病案号姓名性別年龄电话病房

24、塢号主洽医生姓客100反三男185333331031陈三4、存储过程、触发器和视图：根据需要给数据库添加几个实用的 存储过程、触发器和视图，并说明它们各自的功能。一：建立触发器：用企业管理器为表病人创建一个级联更新触发器trrigger_c 。要求：若修改病人表中一病人的编号,则表病房中与该病人相关的编号自动修改。1、启动企业管理器，选择数据库 -医院管理，将其展开，单击“表”对象。2、打开“触发器属性”对话框。在右窗格中，用鼠标右键单击表对象病人，在打开的快捷菜单中选择“所有任务”7“管理触发器”选项，打开“触发器属性”对话框。3、创建SQL语句。在“文本”输入框内输入下面语句：create

25、 trigger trigger_c on 病人for update asif update( 病房编号)begindeclare 病房编号 _newnvarchar(50),病房编号 _oldnvarchar(50)select 病房编号_new=病房编号from insertedselect 病房编号_old=病房编号 from deletedupdate 病房set病房编号=病房编号_new where病房编号=病房编号_oldend触发器雇哇建立存储过程:存储过程是保存起来的可以接受和返回用户提供的参数的Transact-SQL语句的集合。可以创建一个过程供永久使用，或在一个会话中临时

26、使用， 或在所有会话中临时使用。 也可以创建在 Microsoft SQL Server启动时自动运行的存储过程。它可以实现模块化编程。具有对数据库立即访问的功能。可以加快程序的运行速度。可以减少网络流量。可以提高数据库的安全性。自动执行存储过程。Create Proc医生治疗的情况as select 医生.医生编号 病人.病案号from 医生inner join 病人 on医生.医生编号=病人.病案号exec sp_depe nds医生治疗的情况汽 W&I 丄乂.m日丄uwyi乂*Create Procas select医生"医生漏粤扁人,病栗号from医生inn”小“病人m匿生-医生隔号-病人-病案号|命宛成功完感。exec sp depertdsnametypeupdat rdselectedcdIwiutldbo sp_depends stored procedurenono©objnauiedbo.病人user tablnono病黑号dbo.匿牛user tablenono医生嵋号Create Proc 病人情况as select*from 病人Returnexec sp_depe nds病人情况esec sp_dependsnainm|±yp苦 updat亡己盘已肃c七已己colsnCreacte Pro