华中科技大学 数据库实验报告

数据库实验报告

题目：医院病历管理系统

院系：控制科学与工程系

班级：

姓名：

学号：

指导老师：

一．实验题目

《医院病历管理系统》

二．实验背景

医院病历管理系统使用PowerDesigner软件建立数据库db_bingli，并创建了三张表：

1.医生信息表

医生编号（D_ID）医生姓名（D_name）

医生性别（D_sex）所在医院（D_hospital）

所在科室（D_department）医生电话号码（D_phone）

2.病人信息表

病人编号（P_ID）病人姓名（P_name）

病人性别（P_sex）病人血型（P_bloodType）

病人地址（P_address）病人电话号码（P_phone）

3.病历记录表

病历编号（R_ID）病历记录日期（data）

诊断（diagnosis）

五．PDM图

六．表的属性与数据类型的设计、约束的设计1.医生信息表

2.病人信息表

3.病历记录表

七．创建数据库的SQL语句

/*=========================================================== ===*/

/* DBMS name: Microsoft SQL Server 2000 */

/* Created on: 2010-11-29 22:21:34 */

/*=========================================================== ===*/

if exists (select 1

from dbo.sysreferences r join dbo.sysobjects o on (o.id = r.constid and o.type = 'F')

where r.fkeyid = object_id('RECORD') and https://www.wendangku.net/doc/166339677.html, =

'FK_RECORD_RELATIONS_PA TIENTS')

alter table RECORD

drop constraint FK_RECORD_RELATIONS_PA TIENTS

go

if exists (select 1

from dbo.sysreferences r join dbo.sysobjects o on (o.id = r.constid and o.type = 'F')

where r.fkeyid = object_id('RECORD') and https://www.wendangku.net/doc/166339677.html, =

'FK_RECORD_RELATIONS_DOCTOR')

alter table RECORD

drop constraint FK_RECORD_RELATIONS_DOCTOR go

if exists (select 1

from sysobjects

where id = object_id('DOCTOR')

and type = 'U')

drop table DOCTOR

go

if exists (select 1

from sysobjects

where id = object_id('PA TIENTS')

and type = 'U')

drop table PA TIENTS

go

if exists (select 1

from sysindexes

where id = object_id('RECORD')

and name = 'Relationship_2_FK'

and indid > 0

and indid < 255)

drop index RECORD.Relationship_2_FK

go

if exists (select 1

from sysindexes

where id = object_id('RECORD')

and name = 'Relationship_1_FK'

and indid > 0

and indid < 255)

drop index RECORD.Relationship_1_FK

go

if exists (select 1

from sysobjects

where id = object_id('RECORD')

and type = 'U')

drop table RECORD

go

/*=========================================================== ===*/

/* Table: DOCTOR */

/*=========================================================== ===*/

create table DOCTOR (

D_ID varchar(20) not null,

R_ID varchar(20) null,

D_name varchar(20) null,

D_sex varchar(2) null,

D_hospital varchar(20) null,

D_department varchar(20) null,

D_phone varchar(15) null,

constraint PK_DOCTOR primary key nonclustered (D_ID)

)

go

/*=========================================================== ===*/

/* Table: PATIENTS */

/*=========================================================== ===*/

create table PA TIENTS (

P_ID varchar(20) not null,

R_ID varchar(20) null,

P_name varchar(20) null,

P_sex varchar(2) null,

P_bloodType varchar(2) null,

P_address varchar(50) null,

P_phone varchar(15) null,

constraint PK_PATIENTS primary key nonclustered (P_ID)

)

go

/*=========================================================== ===*/

/* Table: RECORD */

/*=========================================================== ===*/

create table RECORD (

R_ID varchar(20) not null,

P_ID varchar(20) null,

D_ID varchar(20) null,

data datetime null,

diagnosis varchar(50) null,

constraint PK_RECORD primary key (R_ID)

)

go

/*=========================================================== ===*/

/* Index: Relationship_1_FK */

/*=========================================================== ===*/

create index Relationship_1_FK on RECORD (

P_ID ASC

)

go

/*=========================================================== ===*/

/* Index: Relationship_2_FK */

/*=========================================================== ===*/

create index Relationship_2_FK on RECORD (

D_ID ASC

)

go

alter table RECORD

add constraint FK_RECORD_RELA TIONS_PA TIENTS foreign key (P_ID) references PATIENTS (P_ID)

go

alter table RECORD

add constraint FK_RECORD_RELA TIONS_DOCTOR foreign key (D_ID) references DOCTOR (D_ID)

go

八．相关SQL操作

1.插入

INSERT INTO PA TIENTS

V ALUES('005','0006','xty','男','O','武汉理工大学','135********')

2．删除

DELETE FROM DOCTOR

WHERE(D_ID='00005')

3．修改

UPDA TE DOCTOR

SET D_name ='zjb'

WHERE (D_ID = '00004')

4. 查询

（1）查询记录号为0002的病历的诊断时间、诊断记录SELECT data,diagnosis

FROM RECORD

WHERE (R_ID = '0002')

（2）查询地址在华中科技大学的病人姓名、电话号码

SELECT P_name,P_phone

FROM PA TIENTS

WHERE (P_address = '华中科技大学')

九．实验体会与收获

略