Draw E-R diagram for Hospital management System.

28kviews

written 9.4 years ago by

teamques10 ★ 70k

Step 1: E-R Diagram

enter image description here

Step 2: Converting the E-R Diagram into Tables

b. Converting entity to table and attribute to columns

Hospital

Hosp-id	Primary Key
HCity
HAddress
Hos-Name
Pat-id	Foreign key references to Pat-id of Patient table
Doc-id	Foreign key references to Doc-id of Doctor table

Patient

Pat-id	Primary Key
PName
PAddress
PDiagnosis
Record-id	Foreign key references to Record-id of Medical Record table
Hosp-id	Foreign key references to Hosp-id of Hospital table

Medical Record

Record-id	Primary Key
Problem
Date_of_examination
Pat-id	Foreign key references to Pat-id of Patient table

Doctor

Doc-id	Primary Key
DName
Qualification
Salary
Hosp-id	Foreign key references to Hosp-id of Hospital table

Step 3: Mapping of Attributes

Simple Attributes

Simple Attributes which can not be divided into subparts.

Example: Salary of Doctor

enter image description here

Composite Attributes

Composite Attributes which can be divided into subparts.

Example: Patient Name, Doctor Name

enter image description here

Step 4: Mapping of Relationships

b. Foreign Key approach

Hosp_patient

Pat-id	Hospital table makes foreign key references to Pat-id of Patient table
Hosp-id	Patient table makes foreign key references to Hosp-id of Hospital table

Hosp_Doctor

Hosp-id	Doctor table makes foreign key references to Hosp-id of Hospital table
Doc-id	Hospital table makes foreign key references to Doc-id of Doctor table

PatiPPatient_MedicalRecord

Pat-id	Medical Record table makes foreign key references to Pat-id of Patient table
Record-id	Patient table makes foreign key references to Record-id of Medical Record table

Step 5: Identifying the relationships

a. Hospital has a set of patients.

Therefore the relations is 1……..N.

b. Hospital has a set of doctors.

Therefore the relations is 1……..N.

c. Doctor are associated with each patient.

Therefore the relations is N……..1.

d. Each patient has record of various test and examination conducted.

Therefore the relations is 1……..N.

ADD COMMENT EDIT