views from the hospital database

hospital_views.sql

-- AUTHOR: VIPUL
CREATE VIEW `patient_appointment_service` AS
(SELECT `patient`.`id` AS `patient_id`,
       `patient`.`name` AS `patient_name`,
       `appointment`.`reason` AS `appointment_reason`,
       `appointment`.`date_and_time` AS `appointment_date_and_time`,
       `service`.`name` AS `service_name`,
       `service`.`availed_date` AS `service_availed_date`
FROM `patient` LEFT JOIN `appointment` ON `patient`.`id` = `appointment`.`patient_id`
               LEFT JOIN `service` ON `patient`.`id` = `service`.`patient_id`);

-- If a patient enters hospital, then his motive can be to book an appointment or avail a service.
-- This view captures the motive of all the patients entering the hospital.
-- Statistics can be calculated according to the view about the number of people availing service or appointment or both.
-- Later this statistics can be used for the further development of hospital system.
-- Also, doctors can look at the past medical tests availed by a patient as a service, and recommend any future tests.

CREATE VIEW `billing_service_surgery` AS
(SELECT `billing`.`id` AS `billing_id`,
       `billing`.`patient_id` AS `patient_id`,
       `billing`.`amount` AS `billing_amount`,
       `billing`.`date` AS `billing_date`,
       `service`.`id` AS `service_id`,
       `service`.`name` AS `service_name`,
       `service`.`availed_date` AS `service_availed_date`,
       `surgery`.`id` AS `surgery_id`,
       `surgery`.`time_of_surgery` AS `time_of_surgery`
FROM `billing` LEFT JOIN `service` ON `billing`.`patient_id` = `service`.`patient_id`
               LEFT JOIN `surgery` ON `billing`.`patient_id` = `surgery`.`patient_id`);

-- Revenue is very important for a hospital management system. Also, the statistics involoved in the revenue generation.
-- This view helps to know from where the majority of the revenue is generated, from services offered or from surgeries performed.
-- At the same time an entire statistics of the revenue from service and surgery, provides insights to the hospital about the expenditure.
-- Every billing is made under patient id, so a patient can know the amount of money spent by him, split into categories.

CREATE VIEW `patient_epatient` AS
(SELECT `patient`.`id` AS `patient_id`,
       `patient`.`name` AS `patient_name`
FROM `patient`
UNION
SELECT `emergency_patient`.`id` AS `patient_id`,
       `emergency_patient`.`name`  AS `patient_name`
FROM `emergency_patient`);

-- There are two types of patients in the hospital, normal patients and emergency patients.
-- The management needs to know the entire list of patients who are getting served, or treated in the hospital.
-- This view will help to provide statistics of the total people who are treated in the hospital.
-- Take any actions, if the count of patients is decreasing and increase capacity of hospital,if the count of patients is increasing.

CREATE VIEW `employee` AS
(SELECT `doctor`.`id` AS `employee_id`,
       `doctor`.`name` AS `employee_name`,
       `doctor`.`working_hours` AS `working_hours`,
       `doctor`.`salary` AS `employee_salary`
FROM `doctor`
UNION
SELECT `nurse`.`id` AS `employee_id`,
       `nurse`.`name` AS `employee_name`,
       `nurse`.`working_hours` AS `employee_working_hours`,
       `nurse`.`salary` AS `employee_salary`
FROM nurse);

-- Management needs to keep track of all the employees, to provide them salaries.
-- This view helps to give salary to employees, and keep track of the employees working in the hospital.

-- AUTHOR: VINAY
CREATE VIEW `nurse_assign_surgery` AS
(SELECT `surgery`.`id`, `Surgery`.`patient_id`,
        `surgery`.`patient_name`, `nurse`.`name` AS `nurse_name`
FROM `assign` INNER JOIN `surgery` ON `assign`.`patient_id` = `surgery`.`patient_id`
              INNER JOIN `nurse` ON `assign`.`nurse_id` = `nurse`.`id`);

-- To backtrack to nurses in case of any mishappening in the surgery period
-- Also to keep track of the working period of nurses

CREATE VIEW `surgery_admit_admitRoom` AS
(SELECT `surgery`.`id`,
        `surgery`.`patient_id`,
        `surgery`.`patient_name`,
        `admit_room`.`id` AS `room_id`,
        `availability`
FROM `admit` INNER JOIN `surgery` ON `admit`.`patient_id` = `surgery`.`patient_id`
             RIGHT JOIN `admit_room` ON `admit`.`room_id` = `admit_room`.`id`);

-- To know which patients are assigned to which rooms and to know the rooms which are empty so that it will helpful to assign new surgery patients.

CREATE VIEW `surgery_occur_operationTheatre` AS
(SELECT `surgery`.`id`,
        `surgery`.`patient_id`,
        `surgery`.`patient_name`,
        `operation_theatre`.`id` AS `operation_theatre_id`,
        `availability`
FROM `occur` INNER JOIN `surgery` ON `occur`.`surgery_id` = `surgery`.`id`
             RIGHT JOIN `operation_theatre` ON `occur`.`theatre_id` = `operation_theatre`.`id`);

-- To know which patients are assigned to which operation theatres and to know the theatre rooms which are empty so that it will helpful to assign new surgery patients to do the operation.

-- AUTHOR: PAVAN
CREATE VIEW `nurse_assign_patient` AS 
(SELECT `nurse`.`id` AS `nurse_id`,
        `nurse`.`name` AS `nurse_name`,
        `patient`.`id` AS `patient_Id`,
        `patient`.`name` AS `patient_name`
FROM `patient` INNER JOIN `assign` ON `patient`.`id` = `assign`.`patient_id`
               INNER JOIN `nurse` ON `nurse`.`id` = `assign`.`nurse_id`);

-- This will be a very frequently used query to find the nurses assigned to different persons.
-- If we want to know the nurses assigned to a particular patient we can perform queries in this view.

CREATE VIEW `doctor_schedule_patient` AS
(SELECT `doctor`.`id` AS `doctor_id`,
       `doctor`.`name` AS `doctor_name`,
       `doctor`.`designation` AS `designation`,
       `patient`.`id` AS `patient_id`,
       `patient`.`name` AS `patient_name`
FROM `doctor` INNER JOIN `schedule` ON `doctor`.`id` = `schedule`.`doctor_id`
              INNER JOIN `Patient` on `patient`.`id` = `schedule`.`patient_id`);

-- This view shows the list of all the doctors treating the patients.
-- If we want to know the patients which are treated by a particular doctor.
-- We can perform queries in this view.

CREATE VIEW `visitor_visit_patient` AS
(SELECT `visitor`.id AS `vistor_id`,
        `visitor`.`name` AS `visitor_name`,
        `visitor`.`contact_number` AS `vistor_number`,
        `Patient`.`id` AS `patient_id`,
        `Patient`.`name` AS `patient_name`
FROM `visitor` INNER JOIN `visit` ON `visit`.`visitor_id` = `visitor`.`id`
               INNER JOIN `Patient` ON `patient`.`id` = `visit`.`patient_id`);

-- This view shows the list of visitors visting the patients admitted in the hospital.
-- It will be easier to know the visitor who visited a particular patient by performing queries in this view.
-- There are various cases where we may need to look up into the list of visitors to a particular patient, in which case we can use pateint_id in this view to access the list of viewers directly in a simple query.

CREATE VIEW `doctor_perform_surgery` AS
(SELECT `surgery`.`patient_id` AS `patient_id`,
        `surgery`.`patient_name` AS `patient_name`,
        `surgery`.`time_of_surgery` AS `time_of_surgery`
FROM `surgery` INNER JOIN `perform` ON `surgery`.`id` = `perform`.`surgery_id`
                                   AND `perform`.`doctor_id` = '121801051');

-- This view shows the patient name, id, time of surgery who are undergoing surgery under the doctor whose id is '121801051'
-- It will be easier to know the details of the patients undergoing surgery under this doctor from this view.

CREATE view doctor_perform_surgery AS (SELECT surgery.patient_id AS patient_id,
       surgery.patient_name AS patient_name,
       surgery.time_of_surgery AS time_of_surgery,
       doctor.id AS doctor_id,
       doctor.name AS doctor_name
FROM surgery inner JOIN perform ON surgery.id = perform.surgery_id inner JOIN doctor ON  perform.doctor_id = doctor.id);

-- AUTHOR: GURU
CREATE VIEW `doctor_epatient_nurse` AS
(SELECT `doctor`.`name` AS `doctor_name`, 
	    `doctor`.`id` AS `doctor_id`,
    	`emergency_patient`.`id` as `e_patient_id`,
	    `emergency_patient`.`name` as `e_patient_name`,
	    `nurse`.`id` as `nurse_id`,
    	`nurse`.`name` as `nurse_name`
FROM `doctor` JOIN 
		(
			`treat` JOIN 
			(
				`emergency_patient` JOIN 
				(
					`attend` JOIN `nurse` ON `attend`.`nurse_id` = `nurse`.`id`
				)
				ON `emergency_patient`.`id` = `attend`.`epatient_id`
			)
			ON (`emergency_patient`.`id` = `treat`.`epatient_id`)
		)
		ON (`doctor`.`id` = `treat`.`doctor_id`)
);

-- This is a useful view, just in case we wanna have a page landing at all the emergency patients list who have been admitted to the hospital along with the nurses and doctors who have taken up the case.

CREATE VIEW `doctor_surgery_nurse` AS 
(SELECT `doctor`.`id` AS `doctor_id`,
	    `doctor`.`name` AS `doctor_name`,
	    `surgery`.`id` AS `surgery_id`,
	    `surgery`.`patient_id` AS `patient_id`,
	    `surgery`.`patient_name` AS `patient_name`,
	    `nurse`.`id` AS `nurse_id`,
	    `nurse`.`name` AS `nurse_name`
FROM `doctor` JOIN 
	(
		`perform` JOIN 
		(
			`surgery` JOIN 
			(
				`assign` JOIN `nurse` ON `assign`.`nurse_id` = `nurse`.`id`
			)
			ON `assign`.`patient_id` = `surgery`.`patient_id`
		)
		ON `surgery`.`id` = `perform`.`surgery_id`
	)
	ON `doctor`.`id` = `perform`.`doctor_id`
);

-- Just like the previous case, we might need to look up on all the surgeries along with the doctors and nurses who were involved in the surgery as well.
-- Thus in that case a doctor_surgery_nurse view can be helpful.

CREATE VIEW `doctor_appointments` AS 
(SELECT `doctor`.`id` AS `doctor_id`,
	    `doctor`.`name` AS `doctor_name`,
	    `appointment`.`id` AS `appointment_id`,
    	`appointment`.`patient_name` AS `patient_name`,
	    `appointment`.`patient_id` AS `patient_id`,
	    `appointment`.`reason` AS `reason`
FROM `doctor` JOIN 
	(
		`schedule` JOIN `appointment` on `appointment`.`patient_id` = `schedule`.`patient_id`
	)
	on `doctor`.`id` = `schedule`.`doctor_id`
);

-- Suppose we want to display what are all the appointments scheduled to a particular doctor, life becomes easy with this view in such a case.

CREATE VIEW `patient_service` AS 
(SELECT `patient`.`id` AS `patient_id`,
	`patient`.`name` AS `patient_name`,
	`service`.`id` AS `service_id`,
	`service`.`name` AS `service_name`,
	`service`.`availed_date` AS `date_availed`
FROM `patient` JOIN 
	(
		`avail` JOIN `service` ON `avail`.`service_id` = `service`.`id`
	)
	ON `patient`.`id` = `avail`.`patient_id`
);

-- There are chances that we may need to look up all the services availed by some patient or data a particular service available by patient, in such a case, the patient_service view helps a lot to retrieve info in simple queries.


-- DELETING VIEWS
-- DROP VIEW `nurse_assign_patient`;
-- DROP VIEW `doctor_schedule_patient`;
-- DROP VIEW `visitor_visit_patient`;
-- DROP VIEW `doctor_perform_surgery`;
-- DROP VIEW `doctor_epatient_nurse`;
-- DROP VIEW `doctor_surgery_nurse`;
-- DROP VIEW `doctor_appointments`;
-- DROP VIEW `patient_service`;
-- DROP VIEW `nurse_assign_surgery`;
-- DROP VIEW `surgery_admit_admitRoom`;
-- DROP VIEW `surgery_occur_operationTheatre`;
-- DROP VIEW `patient_appointment_service`;
-- DROP VIEW `Billing_Service_Surgery`;
-- DROP VIEW `patient_epatient`;
-- DROP VIEW `employee`;