Hospital Management System

SQL Project

22 min readDec 16, 2023

Executive Summary

While there have been successful and comprehensive healthcare systems to improve patient outcomes, a better HMS(Hospital Management System) can always enhance efficiency. This project is designed to gather examples of past attempts, develop a high-level representation of the extent of work, and provide an in-depth analysis of data utilized in the hospital management system. This representation will include various data models that consider representations of the data needed for appointment scheduling, hospital operations, and tracking finances.

In this report, we start with our dataset's logic design and modeling. First, we designed our ER/EER diagram, along with all underlying assumptions, which visually presents the entities and relationships between them. In the next section, we focus on the relational schema, where the logical diagram is transformed into a database diagram incorporating more detailed information. Then, we normalize the tables to ensure they all conform to 3NF. Lastly, we conclude the design with a short summary.

Problem Description

The conventional Hospital Management System has been facing challenges; information is difficult to retrieve. For instance, patients’ Identification numbers are unique throughout the system for each patient, and traditionally, this process is done manually. Therefore, errors occur in transaction processes, and it takes time and effort to handle and secure patient information and diagnosis data. This data model is made to improve the hospital management system, to register and maintain patient information for staff to access and update the information when needed. Simultaneous updates and changes are made and stored in the databases by administrators or receptionists.

1. Conceptual Design

Here is the EER diagram generated based on our project description and real-life experiences.

1.1 EER Diagram with all assumptions

Figure 1. EER Design for Hospital Management System Database

1.2 Crow Foot Notation for Relationship

This section examines the min-max notations used in the EER diagram to portray the relationships between entities. Crow Foot notations are elaborated upon in Table 1 to facilitate comprehension and interpretation of the relationships that exist in the relational database.

Explanation for Crow Foot Notation — cont.

2. Relational Schema

The diagram highlights the relationships between each table by showing how each foreign key is connected to the primary key of the parent table. It is a blueprint to show how information is correlated and retrieved in the database. For example, putting the Emp_ID in the Nurse and Doctor table allows their respective information to be retrieved when Emp_ID is called in a query.

2.1 Data Format for Every Relation

CREATE TABLE Room ( 
  Room_ID INT NOT NULL, 
  Room_Type VARCHAR(50) NOT NULL, 
  Patient_ID INT NOT NULL,
  Room_Cost DECIMAL(10,2),       
  PRIMARY KEY (Room_ID),               
  FOREIGN KEY (Patient_ID) REFERENCES Patient (Patient_ID) 
);

CREATE TABLE Lab_Screening ( 
  Lab_ID INT NOT NULL, 
  Patient_ID INT NOT NULL,
  Technician_ID INT NOT NULL, 
  Doctor_ID INT NOT NULL,   
  Test_Cost DECIMAL(10,2),
  Date_ DATE NOT NULL,   
  PRIMARY KEY (Lab_ID), 
  FOREIGN KEY (Patient_ID) REFERENCES Patient (Patient_ID), 
  FOREIGN KEY (Technician_ID) REFERENCES Patient (Patient_ID), 
  FOREIGN KEY (Doctor_ID) REFERENCES Doctor (Doctor_ID)
);

CREATE TABLE Bill ( 
  Bill_ID INT NOT NULL, 
  Date_ DATE NOT NULL, 
  Room_Cost Decimal(10,2), 
  Test_Cost DECIMAL(10,2), 
  Other_Charges DECIMAL(10,2), 
  M_Cost DECIMAL(10,2), 
  Total DECIMAL(10,2), 
  Patient_ID INT NOT NULL, 
  Remaining_Balance DECIMAL(10,2), 
  Policy_Number VARCHAR(20) NOT NULL,
  PRIMARY KEY (Payment_ID), 
  FOREIGN KEY (Room_Cost) REFERENCES Room (Room_Cost),
  FOREIGN KEY (Test_Cost) REFERENCES Lab_Screening (Test_Cost), 
  FOREIGN KEY (M_Cost) REFERENCES Medicine (M_Cost), 
  FOREIGN KEY (Patient_ID) REFERENCES Patient (Patient_ID), 
  FOREIGN KEY (Policy_Number) REFERENCES Insurance (Policy_Number) 
);

CREATE TABLE Insurancel ( 
  Policy_Number VARCHAR(20) NOT NULL, 
  Patient_ID INT NOT NULL, 
  Ins_Code VARCHAR(20) NOT NULL, 
  End_Date VARCHAR(10), 
  Provider VARCHAR(20), 
  Plan VARCHAR(20), 
  Co_Pay DECIMAL(10,2), 
  Coverage VARCHAR(20), 
  Maternity BOOLEAN, 
  Dental BOOLEAN, 
  Optical BOOLEAN, 
  PRIMARY KEY (Policy_Number), 
  FOREIGN KEY (Patient_ID) REFERENCES Patient (Patient_ID)
);

CREATE TABLE Medicine( 
  Medicine_ID INT NOT NULL, 
  M_Name VARCHAR(20) NOT NULL,
  M_Quantity INT NOT NULL, 
  M_Cost Decimal(10,2), 
  PRIMARY KEY (Medicine_ID) 
);

CREATE TABLE Prescription ( 
  Prescription_ID INT NOT NULL, 
  Patient_ID INT NOT NULL,
  Medicine_ID INT NOT NULL, 
  Date_ DATE, 
  Dosage INT, 
  Doctor_ID INT NOT NULL, 
  PRIMARY KEY (Prescription_ID), 
  FOREIGN KEY (Patient_ID) REFERENCES Patient (Patient_ID),
  FOREIGN KEY (Doctor_ID) REFERENCES Doctor (Doctor_ID), 
  FOREIGN KEY (Medicine_ID) REFERENCES Medicine (Medicine_ID)
);

CREATE TABLE Patient ( 
  Patient_ID INT NOT NULL, 
  Patient_FName VARCHAR(20) NOT NULL,
  Patient_LName VARCHAR(20) NOT NULL, 
  Phone VARCHAR(12) NOT NULL,
  Blood_Type VARCHAR(5) NOT NULL, 
  Email VARCHAR(50), 
  Gender VARCHAR(10),
  Condition_ VARCHAR(30),
  Admission_Date DATE,
  Discharge_Date DATE, 
  PRIMARY KEY (Patient_ID)
);

CREATE TABLE Medical History ( 
  Record_ID INT NOT NULL, 
  Patient_ID INT NOT NULL,
  Allergies VARCHAR(50), 
  Pre_Conditions VARCHAR(50), 
  PRIMARY KEY (Record_ID), 
  FOREIGN KEY (Patient_ID) REFERENCES Patient (Patient_ID)
);

CREATE TABLE Emergency Contact( 
  Contact_ID INT NOT NULL,
  Contact_Name VARCHAR(20) NOT NULL, 
  Phone VARCHAR(12) NOT NULL, 
  Relation VARCHAR(20) NOT NULL, 
  Patient_ID INT NOT NULL, 
  PRIMARY KEY (Contact_ID),
  FOREIGN KEY (Patient_ID) REFERENCES Patient (Patient_ID) 
);

CREATE TABLE Appointment ( 
  Appt_ID INT NOT NULL, 
  Scheduled_On DATETIME NOT NULL,
  Date_ DATE,
  Time_ TIME,
  Doctor_ID INT NOT NULL, 
  Patient_ID INT NOT NULL, 
  PRIMARY KEY (Appt_ID), 
  FOREIGN KEY (Doctor_ID) REFERENCES Doctor (Doctor_ID), 
  FOREIGN KEY (Patient_ID) REFERENCES Patient (Patient_ID) 
);

CREATE TABLE Nurse ( 
  Nurse_ID INT NOT NULL,
  Patient_ID INT NOT NULL,
  Emp_ID INT NOT NULL, 
  Dept_ID INT NOT NULL, 
  PRIMARY KEY(Nurse_ID), 
  FOREIGN KEY (Patient_ID) REFERENCES Patient (Patient_ID), 
  FOREIGN KEY (Emp_ID) REFERENCES Staff (Emp_ID), 
  FOREIGN KEY (Dept_ID) REFERENCES Department (Dept_ID) 
);

CREATE TABLE Staff ( 
  Emp_ID INT NOT NULL, 
  Emp_FName VARCHAR(20) NOT NULL, 
  Emp_LName VARCHAR(20) NOT NULL,
  Date_Joining DATE, 
  Date_Seperation DATE, 
  Emp_Type VARCHAR(15) NOT NULL, 
  Email VARCHAR(50), 
  Address VARCHAR(50) NOT NULL,
  Dept_ID INT NOT NULL,
  SSN INT NOT NULL, 
  PRIMARY KEY (Emp_ID), 
  FOREIGN KEY (Dept_ID) REFERENCES Department (Dept_ID) 
);

CREATE TABLE Doctor ( 
  Doctor_ID INT NOT NULL, 
  Qualifications VARCHAR(15) NOT NULL,
  Emp_ID INT NOT NULL, 
  Specialization VARCHAR(20) NOT NULL, 
  Dept_ID INT NOT NULL, 
  PRIMARY KEY (Doctor_ID), 
  FOREIGN KEY (Emp_ID) REFERENCES Staff (Emp_ID), 
  FOREIGN KEY (Dept_ID) REFERENCES Department (Dept_ID) 
);

CREATE TABLE Department ( 
  Dept_ID INT NOT NULL, 
  Dept_Head VARCHAR(20) NOT NULL,
  Dept_Name VARCHAR(15) NOT NULL, 
  Emp_Count INT, PRIMARY KEY (Dept_ID) 
);

CREATE TABLE Payroll ( 
  Account_No VARCHAR(25) NOT NULL, 
  Salary DECIMAL(10,2) NOT NULL, 
  Bonus DECIMAL(10,2), 
  Emp_ID INT NOT NULL, 
  IBAN VARCHAR(25), 
  PRIMARY KEY (Account_No), 
  FOREIGN KEY (Emp_ID) REFERENCES Staff (Emp_ID) 
);

3. Normalization

In this part, we apply the principles of normalization to ensure all the tables conform to 3NF.

I. Patient Table

1NF Compliance: The table is in the First Normal Form (1NF) since it contains only atomic (indivisible) values in each column, and there are no repeating groups.

2NF Compliance: It is also in the Second Normal Form (2NF) because it has a primary key, “patient_id,” which uniquely identifies each row, and all non-key attributes are fully functionally dependent on the primary key.

3NF Compliance: The table is in the Third Normal Form (3NF) because it has no transitive dependencies. All non-key attributes are directly dependent on the primary key “patient_id.”

Functional Dependencies:

patient_id → patient_fname, patient_lname, Phone_no, blood_group, email, gender, condition, admission_date, discharge_date

This set of functional dependencies implies that each patient’s name, phone number, blood group, email, gender, condition, admission date, and discharge date are directly determined by their unique patient ID. As a result, the “Patient” table is in 3NF.

II. Lab Screening Table

1NF Compliance: The table is in the First Normal Form (1NF) as it contains atomic values in each column, and there are no repeating groups.

2NF Compliance: It is in the Second Normal Form (2NF) because it has a composite primary key consisting of “Lab_id” and “patient_id,” which uniquely identifies each row, and all non-key attributes are fully functionally dependent on this composite key.

3NF Compliance: The table is also in the Third Normal Form (3NF) because it has no transitive dependencies. All non-key attributes are directly dependent on the composite primary key, “Lab_id” and “patient_id.”

Functional Dependencies:

(Lab_id, patient_id) → technician_id, doctor_id, test_cost, date

This set of functional dependencies indicates that for each specific laboratory screening instance, which is identified by the combination of “Lab_id” and “patient_id,” the attributes “technician_id,” “doctor_id,” “test_cost,” and “date” are directly determined. This structure promotes data integrity and efficiency in managing lab screening records. As a result, the table is in 3NF.

III. Medicine Table

1NF Compliance: All attributes must be atomic, which means that they cannot be further divided. In this table, the attributes appear atomic, so it satisfies 1NF.

2NF Compliance: For a table to be in 2NF, it must first be in 1NF. Then, it must have no partial dependencies. A partial dependency occurs when an attribute depends on only a part of the candidate key.

Assuming that medicine_id is the candidate key, the Patient_id attribute appears to depend on the candidate key (medicine_id), indicating that it associates a patient with a specific medicine. This might be a partial dependency issue.

The presence of Patient_id in the “Medicine” table suggests that it might have a functional dependency on the patient for whom the medicine is prescribed. This indicates a potential partial dependency, which would mean that the table is not in 2NF. To bring the table to 2NF and resolve this issue, we created a separate table for the prescription or association between patients and medicines.

Functional Dependencies:

prescription_id -> medicine_id, patient_id, date, dosage, doctor_id medicine_id -> name, medicine_cost, quantity
medicine_id -> name, medicine_cost, quantity

3NF Compliance: Both tables are in the Third Normal Form (3NF) because they meet the requirements of 1NF (atomic attributes), 2NF (no partial dependencies), and 3NF (no transitive dependencies). In these tables, attributes depend directly on the candidate keys, and there are no indirect dependencies.

IV. Emergency Contact Table

1NF Compliance: The table is in the First Normal Form (1NF) as it contains atomic values in each column, and there are no repeating groups.

2NF Compliance: It is also in the Second Normal Form (2NF) because it has a primary key, “contact_id,” which uniquely identifies each row, and all non-key attributes are fully functionally dependent on the primary key.

3NF Compliance: The table satisfies the Third Normal Form (3NF) because it has no transitive dependencies. All non-key attributes directly depend on the primary key, “contact_id,” and “patient_id.”

Functional Dependencies:

contact_id → contact_name, number, relation, patient_id

This set of functional dependencies indicates that for each unique emergency contact identified by “contact_id,” the attributes “contact_name,” “number,” “relation,” and “patient_id” are directly determined.

V. Room Table

1NF Compliance: The table is in the First Normal Form (1NF) because it contains atomic values in each column, and there are no repeating groups.

2NF Compliance: It is also in the Second Normal Form (2NF) since it has a primary key, “room_id,” which uniquely identifies each row, and all non-key attributes are fully functionally dependent on the primary key.

3NF Compliance: The table satisfies the Third Normal Form (3NF) because it does not contain any transitive dependencies. All non-key attributes are directly dependent on the primary key, “room_id.”

Functional Dependencies:

room_id → room_type, patient_id, room_cost

This set of functional dependencies indicates that for each individual room, identified by “room_id,” the attributes “room_type,” “patient_id,” and “room_cost” are directly determined. There are no transitive dependencies, ensuring the “Room” table is well-structured and adheres to 3NF principles.

VI. Employee Table

1NF Compliance: The table is in the First Normal Form (1NF) because it contains atomic values in each column, and there are no repeating groups.

2NF Compliance: It is in the Second Normal Form (2NF) since it has a primary key, “emp_id,” which uniquely identifies each row, and all non-key attributes are fully functionally dependent on the primary key.

3NF Compliance: The “Employee” table as provided does not fully satisfy the Third Normal Form (3NF) due to a transitive dependency between the “department_id” and “department_name” attributes. The “department_name” depends on “department_id,” which itself depends on the “emp_id,” violating the 3NF principles.

Functional Dependencies:

emp_id → emp_name, DoB, joining_date, emp_type, email, address, data_of_leaving, department_id, SSN department_id → department_name

To bring the table into 3NF, we separated the “department_id” and “department_name” into a separate table that links department details to employees

With this there are no transitive or partial dependencies in any of the 2 table, So they conform to the 3NF normal form.

VII. Doctor Table

1NF Compliance: Each column in the table contains atomic (indivisible) values, meeting the requirements of 1NF.

2NF Compliance: The table goes beyond 1NF by having a primary key, “doctor_id,” which uniquely identifies each row. All non-key attributes are fully functionally dependent on the primary key, adhering to 2NF principles.

Functional Dependencies:

doctor_id → qualifications, patient_id, specialization, employee_id

VIII. Nurse Table

1NF Compliance: The table is in the First Normal Form (1NF) as it contains atomic values in each column, and there are no repeating groups.

2NF Compliance: It is also in the Second Normal Form (2NF) because it has a primary key, “nurse_id,” which uniquely identifies each row, and all non-key attributes are fully functionally dependent on the primary key.

Functional Dependencies:

nurse_id → patient_id, emp_id

This set of functional dependencies indicates that for each individual nurse, identified by “nurse_id,” the attributes “patient_id” and “emp_id” are directly determined. There are no transitive dependencies, ensuring that the “Nurse” table is well-structured and adheres to 3NF principles.

IX. Bill Table

1NF Compliance: The table is in the First Normal Form (1NF) because it contains atomic values in each column, and there are no repeating groups.

2NF Compliance: It is also in the Second Normal Form (2NF) since it has a primary key, “payment_id,” which uniquely identifies each row, and all non-key attributes are fully functionally dependent on the primary key.

Functional Dependencies:

payment_id, p_id → date, room_cost, test_cost, othercharges, m_cost, total

This set of functional dependencies indicates that for each payment and associated patient (identified by “payment_id” and “p_id”), the attributes “date,” “room_cost,” “test_cost,” “othercharges,” “m_cost,” and “total” are directly determined. There are no transitive dependencies, ensuring that the “Bill” table is well-structured and adheres to 3NF principles.

X. Medical History Table

1NF Compliance: The table is in the First Normal Form (1NF) because it contains atomic values in each column, and there are no repeating groups.

2NF Compliance: It is also in the Second Normal Form (2NF) since it has a primary key, “patient_id,” which uniquely identifies each row, and all non-key attributes are fully functionally dependent on the primary key.

Functional Dependencies:

patient_id → allergies, pre_conditions

This set of functional dependencies indicates that for each patient, identified by “patient_id,” the attributes “allergies” and “pre_conditions” are directly determined. There are no transitive dependencies, ensuring that the “Medical History” table is well-structured and adheres to 3NF principles.

XI. Insurance Table

1NF Compliance: The table is in the First Normal Form (1NF) because it contains atomic values in each column, and there are no repeating groups.

2NF Compliance: It is also in the Second Normal Form (2NF) since it has a primary key, “p_id,” which uniquely identifies each row, and all non-key attributes are fully functionally dependent on the primary key.

Functional Dependencies:

p_id → policy_number, ins_code, expiry_date, ins_company, ins_plan, co_pay, med_coverage, maternity, dental, optical

This set of functional dependencies indicates that for each patient and their insurance information, identified by “p_id,” the attributes like “policy_number,” “ins_code,” “expiry_date,” “ins_company,” “ins_plan,” “co_pay,” “med_coverage,” “maternity,” “dental,” and “optical” are directly determined. There are no transitive dependencies, ensuring that the “Insurance” table is well-structured and adheres to 3NF principles.

XII. Payroll Table

1NF Compliance: The table is in the First Normal Form (1NF) because it contains atomic values in each column, and there are no repeating groups.

2NF Compliance: It is also in the Second Normal Form (2NF) since it has a primary key, “emp_id,” which uniquely identifies each row, and all non-key attributes are fully functionally dependent on the primary key.

Functional Dependencies:

emp_id → salary, bonus, account_no, IBAN

This set of functional dependencies indicates that for each employee and their payroll information, identified by “emp_id,” the attributes such as “salary,” “bonus,” “account_no,” and “IBAN” are directly determined. There are no transitive dependencies, ensuring the “Payroll” table is well-structured and adheres to 3NF principles.

XIII. Appointment Table

1NF Compliance: The table is in the First Normal Form (1NF) because it contains atomic values in each column, and there are no repeating groups.

2NF Compliance: It is also in the Second Normal Form (2NF) since it has a primary key, “Appt_id,” which uniquely identifies each row, and all non-key attributes are fully functionally dependent on the primary key.

Functional Dependencies:

Appt_id → Scheduled_on, Date, Time, Doctor_ID, Patient_id Doctor_ID → Doctor_name, Doctor_specialization Patient_id → Patient_name, Patient_date_of_birth

This set of functional dependencies indicates that for each appointment, identified by “Appt_id,” the attributes “Scheduled_on,” “Date,” “Time,” “Doctor_ID,” and “Patient_id” are directly determined. There are no transitive dependencies, ensuring that the “Appointment” table is well-structured and adheres to 3NF principles.

Note: All the tables used in the Section 3 and the Section 4 are first normalized and then used.

4. SQL Statements

4.1 Pre-Illumination

This report outlines the implementation phase of the database project, focusing on the creation of the database, table setup, data population, and SQL queries. Our project utilizes the MySQL database management system. Part 1 is the creation of the database, including tables, all other structures as well as constraints, data type and format, Part 2 is the query scenario design and implementation along with the results. Part 3 is our conclusion of what is included in this report.

4.2 Creation of Database with SQL Statements

4.2.1 Table Creation


CREATE TABLE Patient (
    Patient_ID INT NOT NULL,
    Patient_FName VARCHAR(20) NOT NULL,
    Patient_LName VARCHAR(20) NOT NULL,
    Phone VARCHAR(12) NOT NULL,
    Blood_Type  VARCHAR(5) NOT NULL,
    Email VARCHAR(50),
    Gender  VARCHAR(10),
    Condition_ VARCHAR(30),
    Admission_Date DATE,
    Discharge_Date DATE,
    PRIMARY KEY (Patient_ID) 
    );

CREATE TABLE Department (
    Dept_ID INT NOT NULL,
    Dept_Head VARCHAR(20) NOT NULL,
    Dept_Name VARCHAR(15) NOT NULL,
    Emp_Count INT,
    PRIMARY  KEY (Dept_ID) 
  );

CREATE TABLE Staff (
    Emp_ID INT  NOT NULL,
    Emp_FName  VARCHAR(20) NOT NULL,
    Emp_LName  VARCHAR(20) NOT NULL,
    Date_Joining  DATE,
    Date_Seperation DATE,
    Emp_Type VARCHAR(15) NOT NULL,
    Email  VARCHAR(50),
    Address  VARCHAR(50) NOT NULL,
    Dept_ID  INT NOT NULL,
    SSN  INT NOT NULL,
    PRIMARY KEY (Emp_ID),
    FOREIGN KEY (Dept_ID) REFERENCES Department  (Dept_ID)
);

CREATE TABLE Doctor (
    Doctor_ID INT NOT NULL,
    Qualifications VARCHAR(15) NOT NULL,
    Emp_ID INT NOT NULL,
    Specialization VARCHAR(20) NOT NULL,
    Dept_ID INT NOT NULL,
    PRIMARY KEY (Doctor_ID),
    FOREIGN KEY (Emp_ID) REFERENCES Staff (Emp_ID),
    FOREIGN KEY (Dept_ID) REFERENCES Department (Dept_ID)
);

CREATE TABLE Nurse (
    Nurse_ID INT  NOT NULL,
    Patient_ID  INT  NOT NULL,
    Emp_ID  INT NOT NULL,
    Dept_ID INT NOT NULL,
    PRIMARY KEY(Nurse_ID),
    FOREIGN KEY (Patient_ID) REFERENCES Patient (Patient_ID),
    FOREIGN KEY (Emp_ID) REFERENCES Staff  (Emp_ID),
    FOREIGN KEY (Dept_ID) REFERENCES Department (Dept_ID)
);

CREATE TABLE Emergency_Contact(
    Contact_ID INT  NOT NULL,
    Contact_Name VARCHAR(20) NOT NULL,
    Phone VARCHAR(12) NOT NULL,
    Relation VARCHAR(20) NOT NULL,
    Patient_ID  INT NOT NULL,
    PRIMARY KEY (Contact_ID),
    FOREIGN KEY (Patient_ID) REFERENCES Patient (Patient_ID)
);

  CREATE TABLE Payroll (
    Account_No VARCHAR(25) NOT NULL,
    Salary DECIMAL(10,2) NOT NULL,
    Bonus DECIMAL(10,2),
    Emp_ID INT NOT NULL,
    IBAN VARCHAR(25),
    PRIMARY KEY (Account_No),
    FOREIGN KEY (Emp_ID) REFERENCES Staff (Emp_ID)
   );

 CREATE TABLE  Lab_Screening (
    Lab_ID INT NOT NULL,
    Patient_ID  INT  NOT NULL,
    Technician_ID  INT  NOT NULL,
    Doctor_ID  INT NOT NULL,
    Test_Cost  DECIMAL(10,2),
    Date  DATE  NOT NULL,
    PRIMARY KEY (Lab_ID),
    FOREIGN KEY (Patient_ID) REFERENCES Patient (Patient_ID),
    FOREIGN KEY (Doctor_ID) REFERENCES Doctor (Doctor_ID)
);

CREATE TABLE Insurance (
    Policy_Number VARCHAR(20) NOT NULL,
    Patient_ID INT NOT NULL,
    Ins_Code VARCHAR(20) NOT NULL,
    End_Date VARCHAR(10),
    Provider VARCHAR(20),
    Plan VARCHAR(20),
    Co_Pay  DECIMAL(10,2),
    Coverage VARCHAR(20),
    Maternity  BOOLEAN,
    Dental  BOOLEAN,
    Optical BOOLEAN,
    PRIMARY  KEY (Policy_Number),
    FOREIGN KEY (Patient_ID) REFERENCES Patient (Patient_ID)
);


CREATE TABLE Medicine (
    Medicine_ID INT  NOT NULL,
    M_Name VARCHAR(20) NOT NULL,
    M_Quantity INT NOT NULL,
    M_Cost  Decimal(10,2),
   PRIMARY KEY (Medicine_ID)
    );

CREATE TABLE Prescription (
    Prescription_ID INT  NOT NULL,
    Patient_ID  INT  NOT NULL,
    Medicine_ID  INT  NOT NULL,
    Date  DATE,
    Dosage  INT,
    Doctor_ID INT NOT NULL,
    PRIMARY KEY (Prescription_ID),
    FOREIGN KEY (Patient_ID) REFERENCES Patient (Patient_ID),
    FOREIGN KEY (Doctor_ID) REFERENCES Doctor (Doctor_ID),
    FOREIGN KEY (Medicine_ID) REFERENCES Medicine (Medicine_ID)
);

CREATE TABLE Medical_History (
    Record_ID  INT NOT NULL,
    Patient_ID  INT NOT NULL,
    Allergies VARCHAR(50),
    Pre_Conditions VARCHAR(50),
    PRIMARY KEY (Record_ID),
    FOREIGN KEY (Patient_ID) REFERENCES Patient (Patient_ID)
);

CREATE TABLE Appointment (
    Appt_ID INT  NOT NULL,
    Scheduled_On  DATETIME NOT NULL,
    Date  DATE,
    Time TIME,
    Doctor_ID INT NOT NULL,
    Patient_ID  INT NOT NULL,
    PRIMARY KEY (Appt_ID),
    FOREIGN KEY (Doctor_ID) REFERENCES Doctor (Doctor_ID), 
    FOREIGN KEY (Patient_ID) REFERENCES Patient (Patient_ID)
);

 CREATE TABLE  Room (
    Room_ID INT NOT NULL,
    Room_Type VARCHAR(50) NOT NULL,
    Patient_ID  INT  NOT NULL,
    Room_Cost  DECIMAL(10,2),
    PRIMARY KEY (Room_ID),
    FOREIGN KEY (Patient_ID) REFERENCES Patient (Patient_ID)
    );

CREATE TABLE Bill (
    Bill_ID INT NOT NULL,
    Date  DATE,
    Room_Cost Decimal(10,2),
    Test_Cost  DECIMAL(10,2),
    Other_Charges  DECIMAL(10,2),
    M_Cost DECIMAL(10,2),
    Total  DECIMAL(10,2),
    Patient_ID INT NOT NULL,
    Remaining_Balance DECIMAL(10,2),
    Policy_Number VARCHAR(20) NOT NULL,
    PRIMARY KEY (Bill_ID),
    FOREIGN KEY (Patient_ID) REFERENCES Patient (Patient_ID),
    FOREIGN KEY (Policy_Number) REFERENCES Insurance (Policy_Number)
);

4.2.2 A Database State

To ensure the database is populated for testing and development purposes, sample dummy data was inserted into each table. The following records were added to each table, maintaining data consistency and validity. Only a part of data will be shown here since there are multiple rows for each table.

Insertion of Table “Patient”

Insertion of Table “Department”

Insertion of Table “Staff”

Insertion of Table “Doctor”

Insertion of Table “Nurse”

Insertion of Table “Emergency_Contact”

Insertion of Table “Payroll”

Insertion of Table “Lab_Screening”

Insertion of Table “Insurance”

Insertion of Table “Medicine”

Insertion of Table “Prescription”

Insertion of Table “Medical_History”

Insertion of Table “Appointment”

Insertion of Table “Room”

Insertion of Table “Bill”

5. Query Scenario Design

Query 01: The hospital management wants to calculate the total revenue generated by the hospital, including room charges, lab screening charges, and other miscellaneous charges, for a specific date range.

SELECT DATE_FORMAT(b.Date, '%Y-%m-%d') AS Billing_Date,     
SUM(b.Room_Cost + b.Test_Cost + b.Other_Charges + b.M_Cost) AS Total_Revenue 
FROM bill b
WHERE b.Date BETWEEN '2023-12-01' AND '2023-12-31' 
GROUP BY DATE_FORMAT(b.Date, '%Y-%m-%d')
ORDER BY DATE_FORMAT(b.Date, '%Y-%m-%d');

Result for Query 01:

Query 02: A hospital administrator wants to retrieve information about a specific patient, including their personal details, insurance information, room history, lab screening details, and billing history. This comprehensive query will provide a detailed overview of the patient’s interactions with the hospital. (Retrieve Patient Information and Associated Bills)

SELECT
p.Patient_ID, p.Patient_FName, p.Patient_LName, p.Gender, p.Phone, i.Policy_Number, i.Ins_Code, i.Plan, i.Co_Pay, i.Coverage, r.Room_ID, r.Room_Type, r.Room_Cost, l.Lab_ID, l.Test_Cost, l.Date AS Lab_Screening_Date, b.Bill_ID, b.Date AS Billing_Date, b.Room_Cost AS Billing_Room_Cost, b.Test_Cost AS Billing_Test_Cost, b.Other_Charges, b.M_Cost, b.Total, b.Remaining_Balance
FROM patient p
JOIN insurance i ON p.Patient_ID = i.Patient_ID
LEFT JOIN room r ON p.Patient_ID = r.Patient_ID
LEFT JOIN lab_screening l ON p.Patient_ID = l.Patient_ID
LEFT JOIN bill b ON p.Patient_ID = b.Patient_ID
WHERE
p.Patient_ID = 1;  -- Replace with the desired Patient_ID

Result for Query 02:

Query 03: Retrieve Patient Information for those who are currently prescribed Aspirin.

SELECT DISTINCT P.Patient_ID, P.Patient_FName, P.Patient_LName, Pre.Dosage
FROM Patient P
INNER JOIN Prescription Pre ON P.Patient_ID = Pre.Patient_ID
INNER JOIN Medicine M ON Pre.Medicine_ID = M.Medicine_ID
WHERE M.M_Name = 'Aspirin';

Result for Query 03:

Query 04: Retrieve doctors and their associated departments.

SELECT D.Doctor_ID, D.Qualifications, D.Specialization, D.Dept_ID, Dep.Dept_Name
FROM Doctor D
JOIN Department Dep ON D.Dept_ID = Dep.Dept_ID;

Result for Query 04:

Query 05: Retrieve the total number of appointments for each doctor by date.

SELECT A.Date, A.Doctor_ID, COUNT(*) AS TotalAppointments
FROM Appointment A
GROUP BY A.Date, A.Doctor_ID
ORDER BY A.Date, A.Doctor_ID;

Result for Query 05:

Query 06: Retrieve emergency contact details along with patient information.

SELECT EC.Contact_ID, EC.Contact_Name, EC.Phone, EC.Relation, P.Patient_FName, P.Patient_LName
FROM Emergency_Contact EC
JOIN Patient P ON EC.Patient_ID = P.Patient_ID;

Result for Query 06:

Query 07: Retrieve the names and contact information of patients with a medical history related to allergies.

SELECT Patient_FName,Patient_LName,Phone
FROM Patient
JOIN Medical_History ON Patient.Patient_ID = Medical_History.Patient_ID
WHERE Medical_History.Allergies IS NOT NULL;

Result for Query 07:

Query 08: Find the total bill amount paid by patients with insurance coverage.

SELECT Patient_FName, COALESCE(SUM(Bill.Total), 0) AS TotalBillPaid
FROM Patient
LEFT JOIN Bill ON Patient.Patient_ID = Bill.Patient_ID
LEFT JOIN Insurance ON Patient.Patient_ID = Insurance.Patient_ID
GROUP BY Patient.Patient_FName;

Result for Query 08:

Query 09: Retrieve the prescription details along with patient information.

SELECT Prescription.Prescription_ID, Prescription.Date, Patient.Patient_ID, Patient.Patient_FName, Patient.Patient_LName, Medicine.Medicine_ID, Medicine.M_Name, Prescription.Dosage
FROM Prescription
JOIN Patient ON Prescription.Patient_ID = Patient.Patient_ID
JOIN Medicine ON Prescription.Medicine_ID = Medicine.Medicine_ID;

Result for Query 09:

Query 10: Calculate the total cost of prescriptions for each patient.

SELECT Patient.Patient_ID, Patient.Patient_FName, Patient.Patient_LName, SUM(Medicine.M_Cost * Prescription.Dosage) AS Total_Prescription_Cost
FROM Patient
JOIN Prescription ON Patient.Patient_ID = Prescription.Patient_ID
JOIN Medicine ON Prescription.Medicine_ID = Medicine.Medicine_ID
GROUP BY Patient.Patient_ID, Patient.Patient_FName, Patient.Patient_LName;

Result for Query 10:

Query 11: Retrieve the information of the patients who have an outstanding bill balance to notify them.

SELECT distinct pt.Patient_ID, pt.Patient_FName, pt.Patient_LName, pt.phone, pt.email, bill.Remaining_Balance
FROM patient pt JOIN appointment apt ON pt.patient_id = apt.Patient_ID
JOIN bill ON bill.Patient_ID = pt.patient_id
WHERE bill.Remaining_Balance > 0;

Results for Query 11:

Query 12: Retrieve the phone number of the male patients who are O+ and are free from allergies for future blood donation.

SELECT pt.Patient_ID, pt.Patient_FName, pt.Patient_LName, pt.phone 
FROM patient pt 
JOIN medical_history mh ON pt.Patient_ID = mh.Patient_ID
WHERE Gender ='Male'
AND allergies = 'None'
AND Blood_Type = 'O+';

Result for Query 12:

6. Conclusion

In conclusion, this data model was made to help hospital staff maintain information and improve access, making the retrieval process easier. The Hospital database management must be improved or upgraded to meet any situation. In this report, we discuss and design the relational schema of the Hospital Management System Database. Our EER diagram and its associated relational schema show the conceptual and logical designs of the system. We also defined data types, assumptions and constraints for each attribute in the relations. The next step is to implement this database and change the design accordingly. The developed system and its evaluation should be carried out to improve the database system and management processes in hospitals. It is capable of storing a variety and large volume of databases. More so, the software has been designed to include program modules to handle the Medical Centre information such as patients’ data, supply management, patients bill etc. Thus, this software contains the database files of patients, doctors, nurses, and departments of a hospital and should provide the necessary information which will be compatible, accurate, flexible, secured and efficient for the desired purpose it is to serve.

Hospital Management System

SQL Project

Executive Summary

Problem Description

Contents:

1. Conceptual Design

1.1 EER Diagram with all assumptions

1.2 Crow Foot Notation for Relationship

2. Relational Schema

2.1 Data Format for Every Relation

3. Normalization

I. Patient Table

II. Lab Screening Table

III. Medicine Table

IV. Emergency Contact Table

V. Room Table

VI. Employee Table

VII. Doctor Table

VIII. Nurse Table

IX. Bill Table

X. Medical History Table

XI. Insurance Table

XII. Payroll Table

XIII. Appointment Table

4. SQL Statements

4.1 Pre-Illumination

4.2 Creation of Database with SQL Statements

5. Query Scenario Design

6. Conclusion

Written by Apoorvchowdhry