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

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

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

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

4、辑结构是一组关系模式的集合 。将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科室信息的数据项描述数据项名数据项含义类型长度允许空备注编号科至编号CHAR50NOT NULL（主键）科室编号

8、唯一科室名VARCHAR50NULL电话VARCHAR50NULL主治医生姓名VARCHAR50NULL关系图如下1病人信息病秦号姓名电话病房编号病床号主治医生姓名戈病房信息=病房编号 收费标准 所属科室 病床慣况表的详细列名：年齣电蛍枱表俪躺T中衢据.強1戡轆中映上3医生信息号 号 上名别龄称话室 医姓性年职电科?科室编号电话主治医生姓名4科室信息信表 烷洌房倍息”中的數据,廣是“医席管理片中、rf(loca»)w上病房编号1收费标准所厲料室1病床情况览表乌医生信息“中的砸,位置是"医蹄管理"中、上k医生编号siBar年龄电话科室编号唱表”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) NOT NULL,

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

11、e table 病房(病房编号 CHAR(50) not null,收费标准 INT ,所属科室VARCHAR(50),病房情况CHAR(50),primary key(病房编号), ）；4 .简历科室表create table科室编号 VARCHAR(50) not null,科室名CHAR(10),主治医生姓名 CHAR(5),电话 CHAR(20),primary key( 科室编号),)；1插入病人信息insert into病人values('001','阿一','男','18','333333','

12、;101','1','陈一'）；insert into病人values('002','阿二','男','18','433333','102','1','陈二'）;insert into病人values('003','阿三','男','18','533333','103','1','陈三'）;insert

13、 into病人values('004','阿四','男','18','633333','104','1','陈四'）;insertintofpiAvaluesf,r IS r333333 R 101'/V,r ffi-insertirrto病人values(n 002? / P<Z?/? 18?/433333 ? 102F/f , ? Tinsertinto病人values (R 003J 阴三'J/ 16*/533333 ' 103&#

14、39;/T?陈三')insertinto病人values(n 804n / 阿四n/M1,n 18? / 6333331,n 1041.T / 阵四7（所戢响的行數为1行）C所黒响的行数为1行）（所恿响的行數为1行）（所豹响的行救为1行）插入病房基本信息外科','3床位'）内科','3床位'）儿科','3床位'）妇科','3床位'）insert into病房insert into病房insert into病房insert into病房values('101','300

15、','values('102','400','values('103','500','values('104','600','insert into 病房 values f 101 P 300P L 外茱 insert into 病詹 valumg（" 1 D2J 4UCT内未 insert into 病房 values （T L03 n 500? 3 7 JL科 insert into 翕勇 vainest'' 104' !

16、 6007 . L ,（所影响的行数为1行）（所影响的行数为1行）（所影响的行埶为1行）（所影响的行數为1行）插入医生基本信息insert into 医生values（'001','陈一','男','30','中级','1111','1'）;insert into 医生values（'002','陈二','女','50','高级','2111','2'）;insert in

17、to 医生values（'003','陈三','男','30','中级','3111','3'）;insert into 医生values（'004','陈四','女','40','高级','4111','4'）;ab中高中喜12 3 4 o o o Oinsert int o insert into insert into insert intovalues '

18、0017 J 陈一J 医至 values （p MT J 腺二.,K 医生 values " 003n/ 陈三， 医生 values ' D04','晦四','（所鞍响的行数拘1行）f所黒响的行数芮1 fr）（所尉响的行数芮1行）t所斃响的行载芮1行）插入科室基本信息in sert into科室 values（'101','外科','陈','1111'）;in sert into科室values（'102','内科','陈二',

19、9;2111'）;in sert into科室values（'103','儿科','陈三','3111'）;insert into 科室 values('104','妇科','陈四','4111');insert insert insert insertint.嚣度宀-i-1淫科科valuts(F JOJ valuesC 102R, values f 103 values (' 104h,J*JJ 一二一_呼12 3 4（所戦响的frftA 1行） t

20、所駅响的行数为l行）（所影响的行数対1行）（所彭响的行数为1行）3、完整性约束：根据需要给数据表添加数据完整性，并说明添加的理由建立完整性约束：保证医生的真实性，和医生编号的正确性create rule a_rule已将规则绑定网表的项命令已成功兗成。Ias(8医生编号 like 0-9 0-9 0-9pexec Sp bitidrule b rulej'痛人.病案即：建立完整性约束：保证病人的真实性，和病人编号的正确性create rule b_ruleas 施病尧号 like ? 0-9 0-9 0-9 命令已成功完虞*建立完整性约束：保证科室的真实性，和科室编号的正确性creat

21、 e rule cl_ruleas 倉科室歸号 like 7 Q-9 0-9 0-9 *exec Sp bindrule cl rule '科室科室偏号-4<II命令已成功完成。L已将规则绑定到裹的鬲Eh建立完整性约束：保证病房的真实性，create rule d_ruleas Wno like 'w0-9'exec Sp_bindrule d_rule,'病房.Wno：create rule c2 ruleas 建病馬徧号 like ' 0-9 0-9 0-9*4 |命令已戚功芫麻和病房编号的正确性exec Sp_bi.ndrule crule,

22、'病尿病馬编号'4nr2将规则绑定對夷的验证存入的信息Select + fro»病案号娃名性别年龄电话病房编号病床号主治医生姓名12 3 4 o o o O o o o O一二三四 昭阿阴阳333333 lfll533333 103533333 104一二三四 陈陈陈陈Select - from 病第病房编号收费标准所属科室病房情况床床位te位位Select * frwn 医生|4医生编号医生姓名性别年龄电话科室编号L001陈一30中观11111012002陈二50咼级211110230D330中憑3J11IQ34004陈四40吕级4111104Stl4Ct*froi

23、hfXf科室扁号科室容主治医生姓名电话O104科科科科外内儿妇1X - - 11 - Sr - - J 11 - J12 3 4二 mftinsert into 病人 values (* 005* / d'filf* /T B33333*, 104* / 2 J ):（所窮响的行救为1行）查看是否存入查找功能疣mm病人 氐" 病歸8号hTW4III病案号娃名性别年龄电话病房漏号病床号主治医生姓名1003 昭三男 18533333 1031 陈三4、存储过程、触发器和视图：根据需要给数据库添加几个实用 的存储过程、触发器和视图，并说明它们各自的功能。一:建立触发器：用企业管理器

24、为表病人创建一个级联更新触发器trrigger_c。要求：若修改病人表中一病人的编号，则表病房中与该病人相关的编号自动修改。1、 启动企业管理器，选择数据库-医院管理，将其展开，单击表”对象。2、 打开 触发器属性”对话框。在右窗格中，用鼠标右键单击表对象病人，在打开的快捷菜单中选 择所有任务管理触发器”选项，打开触发器属性”对话框。3、 创建SQL语句。在 文本”输入框内输入下面语句:create trigger trigger_c on 病人for update asif update(病房编号)begindeclare 病房编号 _new nvarchar(50), 病房编号 _old

25、nvarchar(50)select 病房编号_new=病房编号 from insertedselect 病房编号_old=病房编号 from deletedupdate 病房set病房编号=病房编号_new where 病房编号=病房编号_old end触发器雇性.建立存储过程存储过程是保存起来的可以接受和返回用户提供的参数的Transact-SQL语句的集合。可以创建一个过程供永久使用，或在一个会话中临时使用，或在所有会话中临时使用。也可以创建在Microsoft SQL Server启动时自动运行的存储过程。它可以实现模块化编程。具有对数据库立即访问的功能。可以加快程序的运行速度。可以减

26、少网络流量。可以提高数据库的安全性。自动执行存储过程。Create Proc 医生治疗的情况as select医生.医生编号 病人病案号from 医生inner join 病人 on医生.医生编号=病人病案号exec sp_depe nds 医生治疗的情况&总五£ Proc医生治疗的'惰凉-as select医生"医生漏粤扁人,病樂号from医生innrr小“病人on底生-医生隔号-病人-病案号|愉令已咸功芫威。Create Proc 病人情况as select*from 病人Returnexec sp_depe nds病人情况esec sp_dependsnametypeupdatedselectedcolumnCreacte Procas select*fro> 病人 Return命令已成功芫咸。1dbo病人 ufer ta