醫(yī)院住院病人管理數(shù)據庫設計

1、_大學軟件學院 實驗報告課程： 數(shù)據庫原理與實用技術實驗 學期： 2012-2013學年 第二學期 任課教師： 薛 專業(yè)： 網絡工程 學號： 姓名： 成績： 期末大作業(yè)：醫(yī)院住院病人管理數(shù)據庫設計一、實驗目的（1）掌握數(shù)據庫設計的基本方法（2）掌握各種數(shù)據庫對象的設計方法二、實驗內容1.數(shù)據庫概要設計：用文字簡要描述實體之間的聯(lián)系，畫出E-R圖（標出各聯(lián)系中實體的基數(shù)）。 E-R圖提供了表示實體型、屬性和聯(lián)系的方法。 1） 實體型：用矩形表示，矩形框內寫明實體名； 2） 屬性：用橢圓形表示，并用無向邊將其與相應的實體連接起來； 3） 聯(lián)系：用菱形表示，菱形框內寫明聯(lián)系名，并用無向邊分別與有關實

2、體型連接起來，同時在無向邊旁標上聯(lián)系的類型。 注意：如果一個聯(lián)系也具有屬性，則這些屬性也要用無向邊與該聯(lián)系連接起來。 下面將根據要設計的住院數(shù)據庫對其中涉及到的實體、實體屬性和實體間的聯(lián)系進行分析和設計。 （1） 實體及其屬性分析根據本次課程設計所給出的初始條件，目前有以下實體：病人實體、病房實體、病床實體、醫(yī)生實體、科室信息實體和住院登記實體。對于這些實體，它們的實體及屬性圖如圖4-7所示：住院數(shù)據庫實體及其屬性如下圖：編號姓名病房病床情況收費標準所屬科室編號性別醫(yī)生年齡職稱電話號碼 病案號部門姓名科室編號性別科室名病人年齡科室信息電話電話號碼主管醫(yī)生姓名病房編號主治醫(yī)師（2） 分E-R圖設

3、計在本次設計中，根據任務書所提供的業(yè)務規(guī)則，實體和實體之間可以有以下幾種聯(lián)系： 1） 醫(yī)生與病人之間的聯(lián)系，它們之間的聯(lián)系圖如圖1所示； 2） 病人與病房以及病房與病床之間的聯(lián)系，它們之間的聯(lián)系圖如圖2所示； 3) 醫(yī)生與科室信息之間的聯(lián)系,它們之間的聯(lián)系圖如圖3所示；1：病人與醫(yī)生聯(lián)系圖n人n醫(yī)生病人治療2：病人與病房及病房與病床聯(lián)系圖n人1人擁有病房病床病房n人1人住在病人3: 醫(yī)生與科室信息之間的聯(lián)系醫(yī)生科室名辦公11 (一般來說，一個醫(yī)生擁有一個辦公科室)（3） 基本E-R圖設計 對于分E-R圖，它們之間往往存在一些不一致的地方，即沖突。合并時不能簡單的將上述的各個分E-R圖畫在一起，

4、必須要消除各個分E-R圖中的不一致，以形成一個能為全系統(tǒng)所有用戶所共同理解和接受的統(tǒng)一的概念模型。在上述分E-R圖上可以做出修改,最終形成的基本E-R圖如下圖所示：醫(yī)院住院數(shù)據庫基本E-R圖：1n病人治療醫(yī)生n1住在辦公1科室病房12、數(shù)據表設計：將E-R圖轉換為數(shù)據表，給出創(chuàng)建各表的SQL語句。一：關系模型設計 關系模型的邏輯結構是一組關系模式的集合。將E-R圖轉換為關系模型實際上就是將實體型、實體型的屬性和實體之間的聯(lián)系轉換為一組關系模式，這種轉換需要遵守以下原則：1. 一個實體型轉換為一個關系模式。實體的屬性就是關系的屬性，實體的碼就是關系的碼。2. 對于實體之間的聯(lián)系有以下幾種情況：（

5、1） 一個1：1的聯(lián)系可以轉換為一個獨立的關系模式，也可以與任意一端對應的關系模式合并。（2） 一個1：n的聯(lián)系可以轉換成為一個獨立的關系模式，也可以與n端對應的關系模式合并。（3） 一個m: n的聯(lián)系轉換為一個關系模式。（4） 3個或者3個以上的實體之間的一個多元聯(lián)系可以轉換為一個關系模式。（5） 具有相同碼的關系模式可以合并。 按照上述的原則，根據設計好的E-R圖，可以將其轉換為以下一組關系模式，其中關系模式的碼用下橫線標出。1病人（病案號，姓名，性別，電話號碼，病房編號） 此為病人實體型所對應的關系模式。其中病案號為次關系模式的碼，而病房編號為該關系模式的外碼。 2病房（病房編號，收費標

6、準，所屬科室，病床號） 此為病房實體型所對應的關系模式。其中病房編號唯一確定一個病房，所以為該關系模式的碼。3醫(yī)生（醫(yī)生編號，姓名，性別，職稱，電話號碼，部門） 此為醫(yī)生實體型所對應的關系模式。其中醫(yī)生編號唯一確定一個醫(yī)生，所以為該關系模式的碼。 4科室信息（科室編號，科室名，電話，主治醫(yī)生姓名） 此為科室實體型所對應的關系模式。其中科室編號唯一確定一個科室，所以為該關系模式的碼。一：數(shù)據字典： 1病人信息的數(shù)據項描述：數(shù)據項名數(shù)據項含義類型長度允許空備注病案號病人的編號VARCHAR15 NOT NULL（主鍵）對應唯一一個病人姓名病人姓名VARCHAR20NULL性別病人性別CHAR2NU

7、LL只能取男或女年齡病人年齡char50NULL電話病人電話VARCHAR12NULL病房編號CHAR4NULL住院時由系統(tǒng)分配病床號int2NULL一個病房一般有1-3個床位主治醫(yī)師CHAR50NULL 2病房信息的數(shù)據項描述：數(shù)據項名數(shù)據項含義類型長度允許空備注病房編號病房編號CHAR50 NOT NULL（主鍵）病房編號唯一收費標準INT4NULL單位為（元/天）所屬科室VARCHAR50NULL病床情況char50NULL一個病房一般有1-3個床位3醫(yī)生信息的數(shù)據項描述：數(shù)據項名數(shù)據項含義類型長度允許空備注醫(yī)生編號醫(yī)生編號VARCHAR50 NOT NULL（主鍵）對應唯一一個醫(yī)生姓名

8、醫(yī)生姓名VARCHAR50NULL性別醫(yī)生性別CHAR2NULL只能取男或女年齡醫(yī)生年齡CHAR50職稱醫(yī)生職稱VARCHAR50NULL有可能有多個職稱電話醫(yī)生電話VARCHAR50NULL科室編號所屬部門VARCHAR50NULL 4科室信息的數(shù)據項描述：數(shù)據項名數(shù)據項含義類型長度允許空備注編號科室編號CHAR50 NOT NULL（主鍵）科室編號唯一科室名VARCHAR50NULL電話VARCHAR50NULL主治醫(yī)生姓名VARCHAR50NULL 關系圖如下： 表的詳細列名： 基本表的設計完成數(shù)據庫的邏輯和物理設計后，需要運用SQL語言對數(shù)據庫中所涉及的表進行定義，同時要考慮與表有關的

9、完整性約束條件。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,主治醫(yī)生姓名 CHAR(50),FOREIGN KEY (病房編號) REFERENCES 病房(病房編號); Foreign key (主治醫(yī)生姓名)references 醫(yī)生(醫(yī)生姓名),

10、2.建立醫(yī)生表：create table 醫(yī)生 ( 醫(yī)生編號 VARCHAR(50) not null,醫(yī)生姓名 CHAR(10) , 性別 CHAR(5) , 年齡 CHAR(20), 職稱 VARCHAR(50),電話 VARCHAR(50),科室編號 VARCHAR(50),primary key(醫(yī)生編號),Foreign key (科室編號)references 科室(科室編號),);3.建立病房表：create table 病房 ( 病房編號 CHAR(50) not null,收費標準 INT , 所屬科室 VARCHAR(50) , 病房情況 CHAR(50), primary

11、 key(病房編號),);4 .簡歷科室表create table 科室 ( 編號 VARCHAR(50) not null,科室名 CHAR(10) , 主治醫(yī)生姓名 CHAR(5) , 電話CHAR(20), primary key(科室編號),);1. 插入病人信息insert into 病人 values('001','阿一','男','18','333333','101','1','陳一');insert into 病人 values('002

12、9;,'阿二','男','18','433333','102','1','陳二');insert into 病人 values('003','阿三','男','18','533333','103','1','陳三');insert into 病人 values('004','阿四','男','18

13、9;,'633333','104','1','陳四');插入病房基本信息insert into 病房 values('101','300','外科','3床位');insert into 病房 values('102','400','內科','3床位');insert into 病房 values('103','500','兒科','3床位'

14、);insert into 病房 values('104','600','婦科','3床位');插入醫(yī)生基本信息insert into 醫(yī)生 values('001','陳一','男','30','中級','1111','1');insert into 醫(yī)生 values('002','陳二','女','50','高級','2111&

15、#39;,'2');insert into 醫(yī)生 values('003','陳三','男','30','中級','3111','3');insert into 醫(yī)生 values('004','陳四','女','40','高級','4111','4');插入科室基本信息insert into 科室 values('101','外科&

16、#39;,'陳一','1111');insert into 科室 values('102','內科','陳二','2111');insert into 科室 values('103','兒科','陳三','3111');insert into 科室 values('104','婦科','陳四','4111');3、 完整性約束：根據需要給數(shù)據表添加數(shù)據完整性，并說明添加的理

17、由。建立完整性約束：保證醫(yī)生的真實性，和醫(yī)生編號的正確性 建立完整性約束：保證病人的真實性，和病人編號的正確性 建立完整性約束：保證科室的真實性，和科室編號的正確性建立完整性約束：保證病房的真實性，和病房編號的正確性create rule d_rule as Wno like 'w0-9'exec Sp_bindrule d_rule, '病房.Wno'驗證存入的信息：插入：查看是否存入：查找功能 ：4、存儲過程、觸發(fā)器和視圖：根據需要給數(shù)據庫添加幾個實用的存儲過程、觸發(fā)器和視圖，并說明它們各自的功能。 一：建立觸發(fā)器： 用企業(yè)管理器為表病人創(chuàng)建一個級聯(lián)更新觸發(fā)

18、器trrigger_c。要求：若修改病人表中一病人的編號，則表病房中與該病人相關的編號自動修改。 1、 啟動企業(yè)管理器，選擇數(shù)據庫-醫(yī)院管理 ，將其展開，單擊“表”對象。 2、打開“觸發(fā)器屬性”對話框。在右窗格中，用鼠標右鍵單擊表對象病人，在打開的快捷菜單中選擇“所有任務”“管理觸發(fā)器”選項，打開“觸發(fā)器屬性”對話框。3、創(chuàng)建SQL語句。在“文本”輸入框內輸入下面語句：create trigger trigger_c on 病人 for update as if update(病房編號) begin declare 病房編號_new nvarchar(50),病房編號_old nvarchar

19、(50) select 病房編號_new=病房編號 from inserted select 病房編號_old=病房編號 from deleted update 病房set 病房編號=病房編號_new where 病房編號=病房編號_old end 二. 建立存儲過程：存儲過程是保存起來的可以接受和返回用戶提供的參數(shù)的 Transact-SQL 語句的集合?？梢詣?chuàng)建一個過程供永久使用，或在一個會話中臨時使用，或在所有會話中臨時使用。也可以創(chuàng)建在 Microsoft SQL Server啟動時自動運行的存儲過程。它可以實現(xiàn)模塊化編程。具有對數(shù)據庫立即訪問的功能。可以加快程序的運行速度。可以減少網絡流量?？梢蕴岣邤?shù)據庫的安全性。自動執(zhí)行存儲過程。Create Proc 醫(yī)生治療的情況 as select 醫(yī)生.醫(yī)生編號,病人.病案號 from 醫(yī)生 inner join 病人 on 醫(yī)生.醫(yī)生編號=病人.病案號exec sp_depends 醫(yī)生