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

—大学软件学院实验报告课程：数据库原理与实用技术实验学期：2012-2013学年第二学期任课教师：专业： 网络工程 学号：—姓名：成绩：期末大作业:艮院住院病人管理数据库设计实验目的（1） 掌握数据库设计的基本方法（2） 掌握各种数据库对象的设计方法二、实验内容1.［据库概要设计：用文字简要描述实体之间的联系，画出E-R图（标出各联系中实体的基数）E-R图提供了表示实体型、属性和联系的方法1） 实体型：用矩形表示，矩形框内写明实体名；2） 属性：用椭圆形表示，并用无向边将其与相应的实体连接起来；3） 联系：用菱形表示，菱形框内写明联系名，并用无向边分别与有关实体型连接起来，同 时在无向边旁标上联系的类型注意：如果一个联系也具有属性，则这些属性也要用无向边与该联系连接起来卜面将根据要设计的住院数据库对其中涉及到的实体、实体属性和实体间的联系进行分析和设 计1）实体及其属性分析根据本次课程设计所给出的初始条件，目前有以下实体：病人实体、病房实体、病床实体、医 生实体、科室信息实体和住院登记实体对于这些实体，它们的实体及属性图如图4-7所示：住院数据库实体及其属性如下图:（2） 分E-R图设计在本次设计中，根据任务书所提供的业务规则，实体和实体之间可以有以下几种联系:1） 医生与病人之间的联系，它们之间的联系图如图1所示；2） 病人与病房以及病房与病床之间的联系，它们之间的联系图如图2所示；3） 医生与科室信息之间的联系，它们之间的联系图如图3所示；1:病人与医生联系图3:医生与科室信息之间的联系(一般来说，一个医生拥有一个办公科室)(3) 基本E-R图设计对于分E-R图，它们之间往往存在一些不一致的地方，即冲突。

合并时不能简单的将上述的各 个分E-R图画在一起，必须要消除各个分E-R图中的不一致，以形成一个能为全系统所有用户所共 同理解和接受的统一的概念模型在上述分E-R图上可以做出修改，最终形成的基本E-R图如下图所示： 医院住院数据库基本E-R图:2、数据表设计：将E-R图转换为数据表，给出创建各表的SQL 语句一：关系模型设计关系模型的逻辑结构是一组关系模式的集合将E-R图转换为关系模型实际上就是将实体型、 实体型的属性和实体之间的联系转换为一组关系模式，这种转换需要遵守以下原则：1. 一个实体型转换为一个关系模式实体的属性就是关系的属性，实体的码就是关系的码2. 对于实体之间的联系有以下几种情况：（1） 一个1： 1的联系可以转换为一个独立的关系模式，也可以与任意一端对应的关系模式 合并2） 一个1： n的联系可以转换成为一个独立的关系模式，也可以与n端对应的关系模式合 并3） 一个m： n的联系转换为一个关系模式4） 3个或者3个以上的实体之间的一个多元联系可以转换为一个关系模式5） 具有相同码的关系模式可以合并按照上述的原则，根据设计好的E-R图，可以将其转换为以下一组关系模式，其中关系模式 的码用下横线标出。

1病人（病案号,姓名，性别，电话号码，病房编号）此为病人实体型所对应的关系模式其中病案号为次关系模式的码，而病房编号为该关系模式的外码2病房（病房编号,收费标准，所属科室，病床号）此为病房实体型所对应的关系模式其中病房编号唯一确定一个病房，所以为该关系模式的 码3医生（医生编号.姓名，性别，职称，电话号码，部门）此为医生实体型所对应的关系模式其中医生编号唯一确定一个医生，所以为该关系模式的 码4科室信息（科室编号，科室名，电话，主治医生姓名）此为科室实体型所对应的关系模式其中科室编号唯一确定一个科室，所以为该关系模式的码据字典：1病人信息的数据项描述:数据项名数据项含义类型长度允许空备注病案号病人的编号VARCHAR15NOT NULL（主键）对应唯一一个病人姓名病人姓名VARCHAR20NULL性别病人性别CHAR2NULL只能取'男'或'女’年龄病人年龄char50NULL电话病人电话VARCHAR12NULL病房编号CHAR4NULL住院时由系统分配病床号int2NULL一个病房一般有1-3个床位主治医师CHAR50NULL2病房信息的数据项描述:数据项名数据项含 义类型长度允许空备注病房编号病房编号CHAR50NOT NULL（主键）病房编号唯一收费标准INT1NULL单位为（元/天）所属科室VARCHAR50NULL病床情况char50NULL一个病房一般有1-3个床位3医生信息的数据项描述:数据项名数据项含 义类型长度允许空备注医生编号医生编号VARCHAR50NOT NULL（主键）对应唯一一个医生姓名医生姓名VARCHAR50NULL性别医生性别CHAR2NULL只能取'男'或'女’年龄医生年龄CHAR50职称医生职称VARCHAR50NULL有可能有多个职称电话医生电话VARCHAR50NULL科室编号所属部门VARCHAR50NULL4科室信息的数据项描述:数据项名数据项含 义类型长度允许空备注编号科室编号CHAR50NOT NULL（主键）科室编号唯一科室名VARCHAR50NULL电话VARCHAR50NULL主治医生 姓名VARCHAR50NULL关系图如下:1病人信息 病案号 姓名 性别 年龄 电话 病房编号 病床号 主治医生姓名2病房信息 里］病房编号 m收费标准 n所属科室 一T病床情况表的详细列名：枪表"1痂人信息,中漩，恒置是"映留电 侦斌〃上性别3医生信息 _」医生编号 口姓名 二性别 2^年龄 Z］职称 n电话 」科室编号4科室信息!］科室编号电话一主治医生姓名宿 表“2病房信息〃中的数据，位置是“医院管理”中、”(local)"上J病房编号 I收费标准 I所属科室 I病床情况-I收费标准I所属科室魅表“3医生信息〃中隰踞，位置是’医院管理〃中、Xlocalf上I医生境与 I姓名 I性别 I年龄 I胆祢 I电话 I料室编号髓表“4科室信息"中的数据，位置是〃医院管理”中、"(local)"上I科室编号 I电话 I主治医生姓 ―基本表的设计完成数据库的逻辑和物理设计后，需要运用SQL语言对数据库中所涉及的表进行定义，同时要考虑与表有关的完整性约束条件。

1.建立病人表：CREATE TABLE 病人病案号 VARCHAR(15) PRIMARY KEY,姓名 VARCHAR(20) NOT NULL,性别 CHAR(2) CHECK(性别 IN('男'，'女'))，年龄 VARCHAR(IOO) NOT NULL,电话 VARCHAR(12),病房编号 CHAR(50) 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 (科室编,4)references科室(科室编号)，)；3. 建立病房表：create 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.插入病人信息insertinsertinsertinsertintointointointo病人病人病人病人values （' 001','阿一'，'男'，'18', ' 333333',' 101', T','陈一'） values （' 002','阿二'，'男'，’18', ' 433333',' 102,, ' 1','陈二'） values （' 003','阿三’，'男'，’18', ' 533333',' 103', ' 1','陈三'） values （'004','阿四’，'男’，’18',' 633333',' 104', T','陈四’）ntntntntsss S e e e e u u u u 1111 ag ，a V V V V A AAA 病病病病12 3 4 o o o O o o o Oy‘ 2 , 一二三四 可rsp可 PDPDPDP8 8 8 8 1J 4J u U, , , , 一二三四陈陈陈陈12 3 4 o o o O（斯影响的行数为1行）（所影响的行独为\行）（斯影响的行数为1行）（斯影响的行数为1行）插入病房基本信息insertinto 病房 values （'101'300''夕卜科'，'3床位'）insertinsertinto 病房 values C 102',' 400','内科'，'3 床位'）into 病房 values （' 103',' 500','儿科'，'3 床位'）insertinto 病房 values C 104',' 600''妇科'，’3床位'）房房房房 病病病病 o o o O ntntntnt 1111values( 101 , 300 了 values ('102' / 400"与 values 103, / 500^ values C 104?,'600",科科科科外内儿妇> > > > 位位位位 床床.床床3 3 3 3（所影响的行数为1行）（所影响的行数为1行）（斯影响的行数为1行）〈M影响的行数为1行） 插入医生基本信息insert into 医生 values C OOf , ,陈一'，’男','30','中级',T111',T'); insert into 医生 values (' 002','陈二'，'女'，'50','高级'，'2111' ,' 2'); insert into 医生 values (' 003','陈三'，'男'，'30','中级','3111' , ' 3'); insert into 医生 values C OO f ,J 陈四'，'女','40','高级','411T ,' 4');insert inser-f insertintoin+ointovalues( 001 , values 。

2’, values (* 003*, values004?.o o o O 3 5 3 4 1 ，3 A 3 ■ > > > > 男女勇女 1 ， J J J J 一二三四 睡陈陈陈12 3 4 o o o O2 3 4J J J , 级级级级 中S中高（所影响的行教为行）（所影响的行教为1行）（所甥响的行数为1行）（所影响的行教为1行）插入科室基本信息insert into 科室 values C 101','外科'，'陈一','111T ） insert into 科室 values C 102','内科'，'陈二'，'211T ） insert into 科室 values C 103','儿科'，'陈三','311T ） insert into 科室 values （'104','妇科'，'陈四','4111'）室室室室 科科科科 o o o O ntntntnt ■1 i 1 ivalues( 101 , values (' 102' 3 values C 103', values? (' 104',外内儿妇一二三四陈陈陈阵12 3（所影响的行数为1行）（所影响的行数为1行）〈断影响的行教为1行）（所影响的行数为1行）3、完整性约束：根据需要给数据表添加数据完整性，并说明添加的理由。

建立完整性约束：保证医生的真实性，和医生编号的正确性create ruleas 伺医生编号 like ' [0-9] [0-9] [0-9]'exec Sp_bindmle ° 医生.医生端号'：已将规则绑定到表的列上命令已成功完成I建立完整性约束：保证病人的真实性，和病人编号的正确性create rule b_ruleas 色病案号 like ' [0-9] [0-9] [0-9],|exec Sp_bindrule b__rule,'病人-病案号[；命令已成功完成已将规则绑定到表的列上建立完整性约束：保证科室的真实性，和科室编号的正确性create rule cl_ruleas ⑥料室编号 like ' [0-9] [0-9] [0-9]，exec Sp_bindrule cl__rule,'科室.科室编号'in|命兮已成功完成建立完整性约束：保证病房的真实性，和病房编号的正确性［已将规贝慨定到表的列上create rule d_ruleas @Wno like ' [w][0-9]1 ;exec Sp bindrule d rule,'病房・Wn';exec Sp_bindrule c2-ruleJ '病房.病房漏号'create rule c2_tuLeas @病房编号 like [0-9] [0-9] [0-9]命今已成功完成，z m3将规则绑定到表的列上。

验证存入的信息:Select * from 病人病条号姓名性别年龄电话 病房编号病床号主治医生姓名I001网一男18333333101陈一2002网二R18433333102陈二3003网三里18533333103陈三4004网四R18633333104陈四Select * from 医生]医生编号医生姓名性别年龄职称电话科室扁号12 3 4 o o o O o o o O一二三四 陈陈陈陈12 3 4 o o o O 1A 41 1A 1A 1A 41 1A 1A 1A 41 1A 1A 1A 41 1A 1A 12 3 4 级级级级 中高中高Select * from 科室|科室编号科室名主治医生姓名12 3 4 o o o O 1A 1A 1A 1A外内儿妇科科科科一二三四 陈陈陈陈电话 1111 2111 3111 4111插入:insert into 病人 values ('005','小新'，'财，’5',' 533333',' 14',' 2« '螃笔')：（所影响的行数为L行）查看是否存入:select ：*ftom 病入 where 病房漏号='13'4 川I病案号姓名性别年龄电话 病房编号病侏号主治医生姓名1 003 阿三男 18 533333 103 1 陈三4、存储过程、触发器和视图：根据需要给数据库添加几个实用的存储过程、触发器和视图，并说明它们各自的功能。

一：建立触发器：用企业管理器为表病人创建一个级联更新触发器trrigger.co要求：若修改病人表中一病人的编 号，则表病房中与该病人相关的编号自动修改1、 启动企业管理器，选择数据库一-医院管理，将其展开，单击“表”对象2、 打开“触发器属性”对话框在右窗格中，用鼠标右键单击表对象病人，在打开的快捷菜单中 选择“所有任务”一“管理触发器”选项，打开“触发器属性”对话框3、 创建SQL语句在“文本”输入框内输入下面语句：create trigger trigger_c on 病人for update asif update (病房编号)begindeclare @病房编号_new nvarchar (50), @病房编号_old nvarchar (50)select 6病房编 '；_new二病房编号 from insertedselect 6病房编号—Old二病房编号from deletedupdate病房set病房编号二@病房编号_new where病房编号二@病房编号_old end触发器属性二g存储过程：存储过程是保存起来的可•以接受和返回用户提供的参数的Transact-SQL语句的集合。

可以创建一 个过程供永久使用，或在一个会话中临时使用，或在所有会话中临时使用也可以创建在Microsoft SQL Server启动时自动运行的存储过程它可以实现模块化编程具有对数据库立即访问的功能 可以加快程序的运行速度可以减少网络流量可以提高数据库的安全性自动执行存储过程Create Proc医生治疗的情况as select医生.医生编号，病人.病案号from医生inner join病人on医生.医生编号二病人.病案号 exec sp_depends医生治疗的情况F m可—MLz-zuizuoiuv^r^.masier.Mc-zuizuoiuv^r^xCreate Proc医生治疗的情况as select医生.辰生编号,，箱人.病案号from医生inner join庶人on医生.医生编号-病人.病索号|exec sp_depends医生治疗的情况4nametypeupdatedselectedcolujnn12 3dbo. sp_dependsdbo.病入dbo.医生stored procedure user table user tablenonononononoffiob jniame 病案号 医生编号Create Proc病人情况 as select*from 布j 人 Return exec sp_depends 病人情况exec sp_depends 病人情;兄Create Pr。

病人情况 as select-+-from.病人 Return命令已成功芫成12345678dbo.病人 user dbo.病人 user dbo.病人 user dbo.病人 user dbo.病人 user dbo.病人 user dbo.病人 user dbo.病人 usertable no table no table no table no table no table no table no table noyesyesyesyesyesyesyesyes号医 编号 味治龄话房案名别 病主年电病病姓性typeupdatedselectedcoluiimname三.创建视图:视图是虚表，它就像一个窗口，透过它可以看到数据库中自己感兴趣的数据及其变化创建视图 显示医生与病人对应情况 create view 病人与医生as select病案号，姓名，医生姓名from医生inner join病人on医生.医生编号二病人.病案号create viev病人.与医生as select局案号，姓名，医生编号from医生inner join病人on医生.医生漏号二病人病案号命令已成公完成四.数据库的备份备份数据库选择医院病房管理数据，右键选择“所有任务”，然后选择“备份数据库”，弹出对 话框，点击“添加”，弹出对话框，选择要保存备份文件的路径和指定文件名，点击“确定”完成 备份工作。

SQL Server 备份 _ master ■那常知I迭顶I数据库(□)： [master 三]L 名称(N): | master副分C重与现有媒体(W)调度r 调度(U)： I |确定 | ♦消 | ■朗 |5、实验小结在这些天中，我们学到了很多东西，包括建表，导入数据，查询，插入最重要的是我们有机 会用电脑自己进行实践，没接触的时候总是觉得它比较深奥或是不可接近的新型语言，尽管自己心 理上有些陌生感学习数据库就和我们平时的其它科目学习一样感觉它有永无止境的知识 SQL Server数据库的实验学习使我对数据库的有了新的进步，以后再看到也就不至于什么也不憧, 其实那么多数据库我觉得学好一门就行，只是他们的语言可能不大一样，学好一门后就可去认识其 它的，这样应该有事半功倍的效果因此，我感谢老师给了我有用的知识，以便我在以后学习或认 识更多的内容能有新的方法和思维，也能更加有效和快速的去消化吸收新的东西希望在今后中， SQL Server能给我更多帮助。