Relational Database Design and Modeling for Software Engineers

0% completed

Previous
Next
Designing a Hospital Management System

In this case study, we will design a database for a Hospital Management System. The objective is to model a system that can manage information about patients, doctors, treatments, room assignments, guardians, and medical tests. We’ll follow a structured approach, step-by-step, to create an ER diagram and map it to a relational schema.

Requirements Analysis

The first and most critical step in database modeling is understanding the requirements of the system. This involves interacting with stakeholders, analyzing existing processes, and defining the scope of the system.

For our case study, let's outline the key functionalities that the HMS should support:

  1. Patient Management: Register new patients, update patient information, and record patient visits.
  2. Doctor Management: Maintain doctor profiles, specialties, schedules, and availability.
  3. Appointment Scheduling: Schedule appointments between patients and doctors.
  4. Medical Records: Store and retrieve patient medical histories, treatments, and prescriptions.
  5. Billing: Generate bills for services rendered, manage payments and insurance details.
  6. Room Management: Assign rooms to patients, manage room availability and types.

Step-by-Step ER Diagram Creation

We will build the ER diagram for the Hospital Management System through the following four steps.

Step 1: Identify Entities

Based on the requirements, we can identify the following entities:

  1. Patient
  2. Doctor
  3. Appointment
  4. Medical_Record
  5. Prescription
  6. Bill
  7. Room
  8. Department
  9. Medicine
  10. Insurance
Image
Hospital Management System Entities

Step 2: Identify Attributes and Define Primary Keys

Next, let’s identify attributes for each entity and define primary keys:

1. Patient

  • Patient_ID (Primary Key)
  • Name (Composite)
  • First_Name
  • Last_Name
  • Date_of_Birth
  • Gender
  • Address
  • Phone_Number (Multi-valued)
  • Email

2. Doctor

  • Doctor_ID (Primary Key)
  • Name (Composite)
  • First_Name
  • Last_Name
  • Specialty
  • Phone_Number (Multi-valued)
  • Email

3. Appointment

  • Appointment_ID (Primary Key)
  • Appointment_Date
  • Appointment_Time
  • Patient_ID (Foreign Key)
  • Doctor_ID (Foreign Key)
  • Reason_For_Visit

4. Medical_Record

  • Record_ID (Primary Key)
  • Patient_ID (Foreign Key)
  • Diagnosis
  • Treatment_Details
  • Record_Date
  • Doctor_ID (Foreign Key)

5. Prescription

  • Prescription_ID (Primary Key)
  • Record_ID (Foreign Key)
  • Medicine_ID (Foreign Key)
  • Dosage
  • Frequency
  • Duration

6. Bill

  • Bill_ID (Primary Key)
  • Patient_ID (Foreign Key)
  • Bill_Date
  • Amount
  • Payment_Method

7. Room

  • Room_Number (Primary Key)
  • Room_Type
  • Availability_Status
  • Patient_ID (Foreign Key)

8. Department

  • Department_ID (Primary Key)
  • Department_Name
  • Location

9. Medicine

  • Medicine_ID (Primary Key)
  • Medicine_Name
  • Manufacturer
  • Price

10. Insurance

  • Insurance_ID (Primary Key)
  • Provider_Name
  • Policy_Number
  • Coverage_Details
Image
Adding attributes in ER diagram of HMS

Step 3: Establish Relationships Between Entities

Now, we will establish relationships between the entities based on the requirements:

Common Types of Relationships

  • One-to-One (1:1): Each instance of Entity A relates to one instance of Entity B.
  • One-to-Many (1:N): One instance of Entity A relates to multiple instances of Entity B.
  • Many-to-Many (M:N): Multiple instances of Entity A relate to multiple instances of Entity B.

Relationships in HMS

1. Patient and Appointment

  • A patient can have multiple appointments.
  • Each appointment is for one patient.
  • Type: One-to-Many (Patient to Appointment)

2. Doctor and Appointment

  • A doctor can have multiple appointments.
  • Each appointment is with one doctor.
  • Type: One-to-Many (Doctor to Appointment)

3. Patient and Medical_Record

  • A patient can have multiple medical records.
  • Each medical record belongs to one patient.
  • Type: One-to-Many (Patient to Medical_Record)

4. Doctor and Medical_Record

  • A doctor can write multiple medical records.
  • Each medical record is written by one doctor.
  • Type: One-to-Many (Doctor to Medical_Record)

5. Medical_Record and Prescription

  • A medical record can have multiple prescriptions.
  • Each prescription is associated with one medical record.
  • Type: One-to-Many (Medical_Record to Prescription)

6. Prescription and Medicine

  • A prescription can include multiple medicines.
  • A medicine can be prescribed in multiple prescriptions.
  • Type: Many-to-Many (Resolved via an associative entity)

7. Patient and Bill

  • A patient can have multiple bills.
  • Each bill is for one patient.
  • Type: One-to-Many (Patient to Bill)

8. Patient and Room

  • A room can be assigned to one patient at a time.
  • A patient can be assigned to one room at a time.
  • Over time, rooms are assigned to different patients.
  • Type: One-to-One

9. Patient and Department

  • A department can be assigned to multiple patients at a time.
  • A patient can be assigned to one department at a time.
  • Type: Many-to-One (Patient to Department)

10. Department and Doctor

  • A department can have multiple doctors at a time.
  • A doctor can have only one department at a time.
  • Type: One-to-Many (Department to Doctor)

11. Patient and Insurance

  • A patient can have zero or multiple insurances.
  • Each insurance with a unique policy number is for one patient.
  • Type: One-to-Many (Patient to Insurance)

Here is the final ER diagram.

Image
Hospital Management System ER Diagram

Mapping the ER Diagram to a Relational Schema

To create a relational schema from an ER diagram, we follow a structured approach that includes identifying entities, converting relationships, and handling attributes (especially primary and foreign keys). This ensures a well-designed relational database that preserves the relationships and dependencies specified in the ER model.

Step 1: Convert Entities to Tables

Each entity in the ER diagram becomes a table in the relational model. Here’s how to approach this:

  1. Identify each entity: In this case, the entities are Doctor, Patient, etc.
  2. Convert each entity to a table: Each entity box in the ER diagram will directly translate to a table with the same name.
  3. Assign primary keys: Each table should have a primary key based on the unique attribute of each entity (e.g., Doctor_ID for Doctor, Patient_ID for Patient).

Step 2: Convert Attributes for Each Entity

For each table, we add columns based on the attributes shown in the ER diagram. Here’s how to handle attributes:

  1. Direct Attributes: Each attribute in the ER diagram becomes a column in the corresponding table.
  2. Primary Key Identification: Ensure that each table’s primary key is clearly marked to uniquely identify each record.
  3. Handle Composite Attribute: Remove the composite attribute name and add its subfields first_name and last_name in the table.
  4. Handle Multi-valued Attribute: For each multi-values attributes like Phone_Number create a separate table.

Step 3: Map Relationships to Tables

  • Map relationships between entities by adding foreign keys or creating junction tables based on the type of relationship.

a. One-to-One (1:1)

  • Add the primary key of one entity as a foreign key in the related entity’s table.

Example:

  • Relationship: Patient ↔ Room
  • Tables:
    • Room Table with Assigned_Patient_ID (FK)

b. One-to-Many (1:N)

  • Add the primary key of the "one" side as a foreign key in the "many" side table.

Example:

  • Relationship: Doctor ↔ Appointment
  • Tables:
    • Doctor Table
    • Appointment Table with Doctor_ID (FK)

c. Many-to-Many (M:N)

  • Create a junction table with the primary keys of both entities as foreign keys, along with any attributes specific to the relationship.

Example:

  • Relationship: Prescription ↔ Medicine
  • Junction Table: Prescription_Medicine with columns:
    • Prescription_ID (FK)
    • Medicine_ID (FK)

Here is the final relational model for the Hospital Management System.

Image
Relational Model for Hospital Management System

Step 4: Create Relational Schema

Now, we'll translate the ER diagram into a relational database schema.

1. Patient

CREATE TABLE Patient (
    Patient_ID INT PRIMARY KEY,
    First_Name VARCHAR(256),
    Last_Name VARCHAR(256),
    Gender CHAR(10),
    Address VARCHAR(512),
    Email VARCHAR(128),
    Date_of_Birth DATE,
    Department_ID INT
    FOREIGN KEY (Department_ID) REFERENCES Department(Department_ID)
);

2. Patient_Phone_Number

CREATE TABLE Patient_Phone_Number (
    Patient_ID INT,
    Phone_Number VARCHAR(13),
    PRIMARY KEY (Patient_ID, Phone_Number),
    FOREIGN KEY (Patient_ID) REFERENCES Patient(Patient_ID)
);

3. Doctor

CREATE TABLE Doctor (
    Doctor_ID INT PRIMARY KEY,
    First_Name VARCHAR(256),
    Last_Name VARCHAR(256),
    Specialty VARCHAR(16),
    Email VARCHAR(128),
    Department_ID INT
    FOREIGN KEY (Department_ID) REFERENCES Department(Department_ID)
);

4. Doctor_Phone_Number

CREATE TABLE Doctor_Phone_Number (
    Doctor_ID INT,
    Phone_Number VARCHAR(13),
    PRIMARY KEY (Doctor_ID, Phone_Number),
    FOREIGN KEY (Doctor_ID) REFERENCES Doctor(Doctor_ID)
);

5. Appointment

CREATE TABLE Appointment (
    Appointment_ID INT PRIMARY KEY,
    Appointment_Date DATE,
    Appointment_Time TIME,
    Patient_ID INT,
    Doctor_ID INT,
    Reason_For_Visit VARCHAR(1028),
    FOREIGN KEY (Patient_ID) REFERENCES Patient(Patient_ID),
    FOREIGN KEY (Doctor_ID) REFERENCES Doctor(Doctor_ID)
);

6. Medical_Record

CREATE TABLE Medical_Record (
    Record_ID INT PRIMARY KEY,
    Patient_ID INT,
    Diagnosis VARCHAR(512),
    Treatment_Details TEXT,
    Record_Date DATE,
    Doctor_ID INT,
    FOREIGN KEY (Patient_ID) REFERENCES Patient(Patient_ID),
    FOREIGN KEY (Doctor_ID) REFERENCES Doctor(Doctor_ID)
);

7. Prescription

CREATE TABLE Prescription (
    Prescription_ID INT PRIMARY KEY,
    Dosage INT,
    Frequency INT,
    Duration INT,
    Record_ID INT,
    FOREIGN KEY (Record_ID) REFERENCES Medical_Record(Record_ID)
);

8. Prescription_Medicine

CREATE TABLE Prescription_Detail (
    Prescription_ID INT,
    Medicine_ID INT,
    PRIMARY KEY (Prescription_ID, Medicine_ID),
    FOREIGN KEY (Prescription_ID) REFERENCES Prescription(Prescription_ID),
    FOREIGN KEY (Medicine_ID) REFERENCES Medicine(Medicine_ID)
);

9. Medicine

CREATE TABLE Medicine (
    Medicine_ID INT PRIMARY KEY,
    Medicine_Name VARCHAR(256),
    Manufacturer VARCHAR(256),
    Price DECIMAL(10, 2)
);

10. Bill

CREATE TABLE Bill (
    Bill_ID INT PRIMARY KEY,
    Patient_ID INT,
    Bill_Date DATE,
    Amount DECIMAL(10, 2),
    Payment_Method VARCHAR(18),
    Insurance_Coverage Boolean,
    FOREIGN KEY (Patient_ID) REFERENCES Patient(Patient_ID)
);

11. Room

CREATE TABLE Room (
    Room_Number INT PRIMARY KEY,
    Room_Type VARCHAR(50),
    Availability_Status Boolean,
    Assigned_Patient_ID INT,
    FOREIGN KEY (Assigned_Patient_ID) REFERENCES Patient(Patient_ID)
);

12. Department

CREATE TABLE Department (
    Department_ID INT PRIMARY KEY,
    Department_Name VARCHAR(256),
    Location VARCHAR(256)
);

13. Insurance

CREATE TABLE Insurance (
    Insurance_ID INT PRIMARY KEY,
    Provider_Name VARCHAR(256),
    Policy_Number INT,
    Coverage_Details TEXT
);

.....

.....

.....

Like the course? Get enrolled and start learning!
Previous
Next