Question: Draw E-R diagram for Hospital management System.
2

Convert E-R diagram into tables.

*Mumbai University > Computer Engineering > Sem 4 > Database Management System *

Marks: 10 M

Year: Dec 2015

ADD COMMENTlink
modified 3.1 years ago  • written 3.1 years ago by gravatar for Juilee Juilee2.3k
2

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 COMMENTlink
written 3.1 years ago by gravatar for Juilee Juilee2.3k
Please log in to add an answer.