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

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

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

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

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

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

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

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

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,主治醫生姓名 CHAR(50),FOREIGN KEY (病房編號) REFERENCES 病房(病房編號); Foreign key (主治醫生姓名)references 醫生(醫生姓名),

10、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

11、 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

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床位');插入醫生基本信息insert into 醫生 values('001','陳一','男','30','中級','1111','1');insert into 醫生 values('002','陳二','女','50','高級','2111&

15、#39;,'2');insert into 醫生 values('003','陳三','男','30','中級','3111','3');insert into 醫生 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、 完整性約束：根據需要給數據表添加數據完整性，并說明添加的理

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

18、器trrigger_c。要求：若修改病人表中一病人的編號，則表病房中與該病人相關的編號自動修改。 1、 啟動企業管理器，選擇數據庫-醫院管理 ，將其展開，單擊“表”對象。 2、打開“觸發器屬性”對話框。在右窗格中，用鼠標右鍵單擊表對象病人，在打開的快捷菜單中選擇“所有任務”“管理觸發器”選項，打開“觸發器屬性”對話框。3、創建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 二. 建立存儲過程：存儲過程是保存起來的可以接受和返回用戶提供的參數的 Transact-SQL 語句的集合?？梢詣摻ㄒ粋€過程供永久使用，或在一個會話中臨時使用，或在所有會話中臨時使用。也可以創建在 Microsoft SQL Server啟動時自動運行的存儲過程。它可以實現模塊化編程。具有對數據庫立即訪問的功能?？梢约涌斐绦虻倪\行速度?？梢詼p少網絡流量?？梢蕴岣邤祿斓陌踩?。自動執行存儲過程。Create Proc 醫生治療的情況 as select 醫生.醫生編號,病人.病案號 from 醫生 inner join 病人 on 醫生.醫生編號=病人.病案號exec sp_depends