Complete Database

Click Here

I am excited to showcase my SQL Database project, a comprehensive SQL database and query solution crafted from the ground up for an IV hydration clinic. This project represents a culmination of my expertise in database design, SQL querying, and healthcare management. With meticulous attention to detail , I created a robust database schema consisting of twelve intricately linked tables, each meticulously crafted to capture and organize essential clinic data. From patient records and medical history to inventory management and appointment scheduling, every aspect of the database was thoughtfully designed to streamline clinic operations and enhance patient care. Join me as I delve into the intricacies of database design and SQL querying, highlighting the innovative solutions and strategic insights that went into creating this indispensable tool for the IV hydration clinic.

Examples

Calculate Certain Statistics BEGIN -- Loop through each order to calculate statistics FOR order_rec IN order_cursor LOOP total_orders := total_orders + 1; total_order_quantity := total_order_quantity + order_rec.Order_qty; -- Check if current order quantity is maximum IF order_rec.Order_qty > max_order_quantity THEN max_order_quantity := order_rec.Order_qty; END IF; -- Check if current order quantity is minimum IF order_rec.Order_qty < min_order_quantity THEN min_order_quantity := order_rec.Order_qty; END IF; -- Count orders per employee total_orders_per_employee := total_orders_per_employee + 1; END LOOP; -- Calculate average order quantity IF total_orders > 0 THEN avg_order_quantity := total_order_quantity / total_orders; END IF; -- Output order statistics DBMS_OUTPUT.PUT_LINE('Total number of orders: ' || total_orders); DBMS_OUTPUT.PUT_LINE('Total order quantity: ' || total_order_quantity); DBMS_OUTPUT.PUT_LINE('Maximum order quantity: ' || max_order_quantity); DBMS_OUTPUT.PUT_LINE('Minimum order quantity: ' || min_order_quantity); DBMS_OUTPUT.PUT_LINE('Average order quantity: ' || avg_order_quantity);

find the total number of IV sessions for a specific solution CREATE OR REPLACE FUNCTION find_total_sessions_for_solution ( solution_id VARCHAR2 ) RETURN INT IS total_sessions INT; BEGIN SELECT COUNT(*) INTO total_sessions FROM IV_SESSIONS WHERE Solution_ID = solution_id; RETURN total_sessions; END; / -- find the average duration of IV sessions for a specific solution CREATE OR REPLACE FUNCTION find_avg_duration_for_solution ( solution_id VARCHAR2 ) RETURN DECIMAL IS avg_duration DECIMAL(10, 2); BEGIN SELECT AVG(Duration_minutes) INTO avg_duration FROM IV_SESSIONS WHERE Solution_ID = solution_id; RETURN avg_duration; END; / -- find the nurse who conducted the most sessions for a specific solution CREATE OR REPLACE FUNCTION find_nurse_with_most_sessions ( solution_id VARCHAR2 ) RETURN VARCHAR2 IS top_nurse_id VARCHAR2(10); BEGIN SELECT Nurse_ID INTO top_nurse_id FROM ( SELECT Nurse_ID, COUNT(*) AS session_count FROM IV_SESSIONS WHERE Solution_ID = solution_id GROUP BY Nurse_ID ORDER BY COUNT(*) DESC ) WHERE ROWNUM = 1; RETURN top_nurse_id; END; / DECLARE solution VARCHAR2(10) := 'IVS001'; -- Change this to the desired solution ID total_sessions INT; avg_duration DECIMAL; top_nurse_id VARCHAR2(10); BEGIN total_sessions := find_total_sessions_for_solution(solution); avg_duration := find_avg_duration_for_solution(solution); top_nurse_id := find_nurse_with_most_sessions(solution); DBMS_OUTPUT.PUT_LINE('Statistics for IV solution ' || solution); DBMS_OUTPUT.PUT_LINE('__________________________________________________'); DBMS_OUTPUT.PUT_LINE('Total number of sessions: ' || total_sessions); DBMS_OUTPUT.PUT_LINE('Average duration of sessions (minutes): ' || avg_duration); DBMS_OUTPUT.PUT_LINE('Nurse with the most sessions: ' || top_nurse_id); END; / CREATE OR REPLACE FUNCTION find_total_sessions_for_solution ( solution_id VARCHAR2 ) RETURN INT IS total_sessions INT; BEGIN SELECT COUNT(*) INTO total_sessions FROM IV_SESSIONS WHERE Solution_ID = solution_id; RETURN total_sessions; END; / -- find the average duration of IV sessions for a specific solution CREATE OR REPLACE FUNCTION find_avg_duration_for_solution ( solution_id VARCHAR2 ) RETURN DECIMAL IS avg_duration DECIMAL(10, 2); BEGIN SELECT AVG(Duration_minutes) INTO avg_duration FROM IV_SESSIONS WHERE Solution_ID = solution_id; RETURN avg_duration; END; /

Nurse and Patient Statistics DECLARE -- Variables v_max_avg_patients_per_nurse NUMBER := 0; v_min_avg_patients_per_nurse NUMBER := 999999; v_max_avg_nurse VARCHAR(50); v_min_avg_nurse VARCHAR(50); v_max_patients_list VARCHAR(1000) := ''; v_min_patients_list VARCHAR(1000) := ''; -- Variables for calculation v_total_nurses NUMBER := 0; v_total_patients NUMBER := 0; v_avg_patients_per_nurse NUMBER := 0; BEGIN -- Calculate total number of nurses and patients SELECT COUNT(*) INTO v_total_nurses FROM Employee; SELECT COUNT(*) INTO v_total_patients FROM IV_SESSIONS; -- Calculate average patients per nurse v_avg_patients_per_nurse := v_total_patients / v_total_nurses; -- Retrieve nurse information FOR nurse_rec IN (SELECT e.Firstname || ' ' || e.Lastname AS Nurse_Name, e.Emp# AS Nurse_ID, COUNT(ivs.Patient_ID) AS Num_Patients FROM Employee e JOIN IV_SESSIONS ivs ON e.Emp# = ivs.Nurse_ID GROUP BY e.Firstname || ' ' || e.Lastname, e.Emp#) LOOP -- Update max and min average patients per nurse IF nurse_rec.Num_Patients > v_max_avg_patients_per_nurse THEN v_max_avg_patients_per_nurse := nurse_rec.Num_Patients; v_max_avg_nurse := nurse_rec.Nurse_Name; END IF; IF nurse_rec.Num_Patients < v_min_avg_patients_per_nurse THEN v_min_avg_patients_per_nurse := nurse_rec.Num_Patients; v_min_avg_nurse := nurse_rec.Nurse_Name; END IF; END LOOP;

From patient demographics and treatment regimens to supplier information and billing details, every piece of information is carefully structured to facilitate easy access and retrieval. Through the implementation of advanced SQL querying techniques, I have empowered clinic staff to extract valuable insights and generate custom reports, enabling data-driven decision-making and optimizing patient care. This database stands as a testament to my expertise in database design and SQL querying, offering a robust and scalable solution to meet the demands of modern healthcare management.

Table

Name # of Attributes # of Values # of Foreign Keys
Drug Crafters 8 5 0
Empower 4 4 0
IV Bag Vitamins 12 4 2
Zipcode 10 3 0
Customer 22 9 3
Employee 11 8 3
Orders 11 3 1
Amazon 8 4 1
Medline 8 4 1
Discharge Packet 8 4 2
IV Solutions 10 4 3
IV Sessions 22 5 2