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

__大学软件学院 实验报告
课程： 数据库原理与实用技术实验 学期： 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：病人与医生联系图
n人
n
医生
病人
治疗

2：病人与病房及病房与病床联系图
n人
1人
拥有
病房
病床
病房
n人
1人
住在
病人

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

n

病人
治疗
医生

n

1

住在
办公

1

科室
病房
1

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病人信息的数据项描述：
数据项名
数据项含义
类型
长度
允许空
备注
病案号
病人的编号
VARCHAR
15
NOT NULL
（主键）对应唯一一个病人
姓名
病人姓名
VARCHAR
20
NULL

性别
病人性别
CHAR
2
NULL
只能取‘男’或‘女’
年龄
病人年龄
char
50
NULL

电话
病人电话
VARCHAR
12
NULL

病房编号

CHAR
4
NULL
住院时由系统分配
病床号

int
2
NULL
一个病房一般有1—3个床位
主治医师

CHAR
50
NULL

2病房信息的数据项描述：
数据项名
数据项含义
类型
长度
允许空
备注
病房编号
病房编号
CHAR
50
NOT NULL
（主键）病房编号唯一
收费标准

INT
4
NULL
单位为(元/天）
所属科室

VARCHAR
50
NULL

病床情况

char
50
NULL
一个病房一般有1-3个床位
3医生信息的数据项描述：
数据项名
数据项含义
类型
长度
允许空
备注
医生编号
医生编号
VARCHAR
50
NOT NULL
（主键）对应唯一一个医生
姓名
医生姓名
VARCHAR
50
NULL

性别
医生性别
CHAR
2
NULL
只能取‘男’或‘女’
年龄
医生年龄
CHAR
50

职称
医生职称
VARCHAR
50
NULL
有可能有多个职称
电话
医生电话
VARCHAR
50
NULL

科室编号
所属部门
VARCHAR
50
NULL

4科室信息的数据项描述：
数据项名
数据项含义
类型
长度
允许空
备注
编号
科室编号
CHAR
50
NOT NULL
(主键）科室编号唯一
科室名

VARCHAR
50
NULL

电话

VARCHAR
50
NULL

主治医生姓名

VARCHAR
50
NULL

关系图如下：
表的详细列名：
基本表的设计
完成数据库的逻辑和物理设计后,需要运用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，
病床号 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 (科室编号）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. 插入病人信息
insert into 病人 values('001'，’阿一',’男'，’18'，'333333'，'101’，'1',’陈一');
insert into 病人 values('002'，'阿二’，’男’，'18',’433333’，’102’，’1’,’陈二’）;
insert into 病人 values('003’,’阿三'，’男’，’18’，'533333'，’103’,’1’，'陈三’);
insert into 病人 values（’004','阿四'，'男'，'18’,’633333'，’104’，’1'，’陈四’）;
插入病房基本信息
insert into 病房 values（'101’,’300’,’外科'，’3床位')；
insert into 病房 values（’102','400’，'内科'，'3床位')；
insert into 病房 values('103',’500'，'儿科',’3床位’）；
insert into 病房 values('104',’600’,’妇科’,’3床位'）；
插入医生基本信息
insert into 医生 values（’001',’陈一','男'，’30'，'中级’，’1111','1’);
insert into 医生 values（’002’,'陈二'，’女'，'50','高级’,'2111’,'2');
insert into 医生 values（’003'，’陈三'，’男','30’，’中级’，'3111'，'3'）;
insert into 医生 values(’004'，’陈四’,’女',’40’，’高级'，’4111’，’4'）；
插入科室基本信息
insert into 科室 values（'101'，’外科',’陈一’,'1111')；
insert into 科室 values(’102’,’内科'，’陈二','2111'）；
insert into 科室 values（'103’,'儿科’,'陈三','3111’）；
insert into 科室 values（’104','妇科',’陈四’，’4111’）;
3、 完整性约束:根据需要给数据表添加数据完整性,并说明添加的理由。
建立完整性约束:保证医生的真实性,和医生编号的正确性
建立完整性约束：保证病人的真实性，和病人编号的正确性
建立完整性约束：保证科室的真实性，和科室编号的正确性
建立完整性约束：保证病房的真实性，和病房编号的正确性
create rule d_rule
as ＠Wno like '[w］[0-9］’；
exec Sp_bindrule d_rule, ’病房.Wno';
验证存入的信息：
插入:
查看是否存入：
查找功能 ：
4、存储过程、触发器和视图：根据需要给数据库添加几个实用的存储过程、触发器和视图，并说明它们各自的功能。
一：建立触发器：
用企业管理器为表病人创建一个级联更新触发器trrigger_c.要求:若修改病人表中一病人的编号，则表病房中与该病人相关的编号自动修改。
1、 启动企业管理器，选择数据库-—-医院管理 ,将其展开，单击“表"对象。
2、打开“触发器属性”对话框.在右窗格中，用鼠标右键单击表对象病人，在打开的快捷菜单中选择“所有任务”→“管理触发器”选项，打开“触发器属性”对话框。
3、创建SQL语句.在“文本”输入框内输入下面语句:
create trigger trigger_c on 病人
for update as
if update（病房编号） begin
declare @病房编号_new nvarchar（50），＠病房编号_old nvarchar(50)
select @病房编号_new=病房编号 from inserted
select ＠病房编号_old=病房编号 from deleted
update 病房set 病房编号=@病房编号_new where 病房编号=＠病房编号_old
end
二. 建立存储过程：
存储过程是保存起来的可以接受和返回用户提供的参数的 Transact-SQL 语句的集合。可以创建一个过程供永久使用,或在一个会话中临时使用，或在所有会话中临时使用.也可以创建在 Microsoft SQL Server启动时自动运行的存储过程。它可以实现模块化编程。具有对数据库立即访问的功能.可以加快程序的运行速度。可以减少网络流量.可以提高数据库的安全性。自动执行存储过程。

Create Proc 医生治疗的情况
as select 医生。医生编号，病人.病案号
from 医生 inner join 病人 on 医生。医生编号=病人.病案号
exec sp_depends 医生治疗的情况
Create Proc 病人情况
as select*from 病人
Return
exec sp_depends 病人情况
三。 创建视图:
视图是虚表,它就像一个窗口，透过它可以看到数据库中自己感兴趣的数据及其变化。创建视图显示医生与病人对应情况
create view 病人与医生
as select 病案号,姓名,医生姓名
from 医生 inner join 病人 on 医生.医生编号=病人。病案号
四。 数据库的备份
备份数据库 选择 医院病房管理 数据，右键选择“所有任务”， 然后选择“备份数据库”，弹出对话框，点击“添加", 弹出对话框，选择要保存备份文件的路径和指定文件名,点击“确定”完成备份工作。
5、实验小结.
在这些天中，我们学到了很多东西，包括建表，导入数据，查询，插入。最重要的是我们有机会用电脑自己进行实践，没接触的时候总是觉得它比较深奥或是不可接近的新型语言，尽管自己心理上有些陌生感。学习数据库就和我们平时的其它科目学习一样感觉它有永无止境的知识SQL Server数据库的实验学习使我对数据库的有了新的进步，以后再看到也就不至于什么也不懂,其实那么多数据库我觉得学好一门就行，只是他们的语言可能不大一样，学好一门后就可去认识其它的，这样应该有事半功倍的效果。因此，我感谢老师给了我有用的知识，以便我在以后学习或认识更多的内容能有新的方法和思维，也能更加有效和快速的去消化吸收新的东西。希望在今后中，SQL Server能给我更多帮助。
9