Hospital Management System Project BY Oracle SQL
Hospital management system designed by Oracle sql language by using Oracle sql developer software to
write sql queries for stored procedures, functions, sequence and triggers.
The structural design by using Oracle sql modeler software, to give the whole view of tables in the hospital
management system.
The system can be used by:
Receptionist.
Nurse.
Doctor.
Accountant.
System Admin.
UML use case diagram:
In the following UML (Unified modeling Language) Diagram is designed on online.visual-paradigm.com web
application, it will summarize the functions of the System with the external users (Actors).\
UML Logical structure:
The structure is designed in web app app.diagrams.net it contains entities, attributes and relationships
between entities.
The entity represents the table name in our database , each entity has attributes which will represent
columns in the table.
The majority of relationships in our database are many to many relationships.
UML Logical Structure
Relational Structure By SQL Data Modeler:
In sql data modeler we use more details in database modeling like data types, primary key, not null value,
forign key values. With addition to attributes, entities and relationships.
What makes data modeler special is that it can engineer the model and convert it to sql script.
In above structure Prescribed_Med has only one attribute because Patient_Report table and Medicine table
has many to many relationship, Prescribed_Med is there to break many to many relationship.
The reason we made a separate table for the hospital Contact because hospitals have multiple phone
numbers and email.
Payroll table will be accessed only by an accountant to secure the information.
SQL Programing:
After converting the structure we will use sql scripts for database modifications.
In the patient table we create a sequence function to store patient_id automatically and start with 10000001
and end with 19999999.
CREATE SEQUENCE seq_patient_id
MINVALUE 10000001
START WITH 10000001
MAXVALUE 19999999
CACHE 10;
Then create the trigger tr_patient to add seq_patient_id on the primary key patient_id
CREATE OR REPLACE TRIGGER tr_patient BEFORE INSERT ON patient
FOR EACH ROW BEGIN
SELECT
LPAD(RTRIM(RTRIM(TO_CHAR(seq_patient_id.NEXTVAL))),10,'0') INTO :NEW.patient_id FROM DUAL;
END;
To make sure the sequence function and the trigger created refresh the left panel check on the trigger and
sequence menu
Add patient to the table with the following query
INSERT INTO PATIENT(first_name, last_name, nationality, gender, address, dob, phone, email)
VALUES('garey', 'lee', 'canada', 'male', 'A999 - R191 - V700', '22-oct-95', 22698817, '[email protected]');
Stored Procedure:
Stored procedure is simply a stored queries that is used for a specific function and can be reused whenever
we recall, it always begins with the header that specifies the name of the procedure.
In our database we will use a stored procedure to insert the values in the patient table. The patient_id will be
added automatically, so we will not mention it in the query.
CREATE OR REPLACE PROCEDURE AddPatient(
first_name IN VARCHAR2 ,
last_name IN VARCHAR2,
nationality IN VARCHAR2,
gender IN VARCHAR2,
address IN VARCHAR2,
dob IN DATE,
phone IN NUMBER,
email IN VARCHAR2
) AS
BEGIN
INSERT INTO patient(first_name, last_name, nationality, gender, address, dob, phone, email)
VALUES (first_name, last_name, nationality, gender, address, dob, phone, email);
COMMIT;
END;
/
To execute AddPatient procedure we will use the following query to add elements to the table without typing
the full script.
EXECUTE AddPatient('lebron', 'james', 'usa', 'male', 'A999 - R192 - V207', '30-dec-84', 88542021,
'[email protected]');
Notice: the elements in the execution must be similar as the one in the store procedure
In employee_id we used the following sequence
CREATE SEQUENCE seq_employee_master
MINVALUE 90001
MAXVALUE 99999
CACHE 10;
The trigger is the following
CREATE OR REPLACE TRIGGER tr_payroll_id BEFORE INSERT ON payroll FOR EACH ROW BEGIN
SELECT
LPAD(RTRIM(RTRIM(TO_CHAR(seq_payroll_id.NEXTVAL))),19,'0') INTO :NEW.payroll_id FROM DUAL;
END;
Minimum Salary:
To find out the minimum salary in payroll table
SELECT MIN(SALARY) FROM PAYROLL;
The output
Maximum Salary:
To find out the maximum salary in payroll table
SELECT MAX(SALARY) FROM PAYROLL;
Total salary:
To find out the total salary in payroll table
SELECT SUM(SALARY) FROM PAYROLL;
Third maximum salary:
SELECT employee_id, salary
FROM payroll p1
WHERE 3-1 = (SELECT COUNT(DISTINCT salary) FROM payroll p2
WHERE p2.salary > p1.salary);
Joining Tables;
Joining two tables, employee_master and payroll table, to display salary and employee full name
SELECT em.employee_id, first_name || ' ' || last_name AS full_name, salary
FROM employee_master em
JOIN payroll p ON em.employee_id = p.employee_id;
