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

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

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

3、計(jì)在本次設(shè)計(jì)中，根據(jù)任務(wù)書所提供的業(yè)務(wù)規(guī)則，實(shí)體和實(shí)體之間可以有以下幾種聯(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 (一般來說，一個(gè)醫(yī)生擁有一個(gè)辦公科室)（3） 基本E-R圖設(shè)計(jì) 對(duì)于分E-R圖，它們之間往往存在一些不一致的地方，即沖突。合并時(shí)不能簡(jiǎn)單的將上述的各個(gè)分E-R圖畫在一起，

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

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

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

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

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

9、完整性約束條件。1. 建立病人表：CREATE TABLE 病人(病案號(hào) VARCHAR(15) PRIMARY KEY,姓名 VARCHAR(20) NOT NULL,性別 CHAR(2) CHECK(性別 IN('男','女'),年齡 VARCHAR(100) NOT NULL,電話 VARCHAR(12),病房編號(hào) CHAR(50) NOT NULL,病床號(hào) INT,主治醫(yī)生姓名 CHAR(50),FOREIGN KEY (病房編號(hào)) REFERENCES 病房(病房編號(hào)); Foreign key (主治醫(yī)生姓名)references 醫(yī)生(醫(yī)生姓名),

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

11、 key(病房編號(hào)),);4 .簡(jiǎn)歷科室表create table 科室 ( 編號(hào) VARCHAR(50) not null,科室名 CHAR(10) , 主治醫(yī)生姓名 CHAR(5) , 電話CHAR(20), primary key(科室編號(hào)),);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','內(nèi)科','3床位');insert into 病房 values('103','500','兒科','3床位'

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

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

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

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

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

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