Set-5

1) ER Diagram for Hospital Management System:

Entities: Patient, Doctor, Nurse, Department, Appointment, Prescription, Procedure
Relationships:
Doctor treats Patient (many-to-many)
Nurse cares for Patient (many-to-many)
Department employs Doctor/Nurse (one-to-many)
Appointment scheduled between Doctor and Patient (one-to-many)


2) PL/SQL Function Program to Use IN, OUT:

//CREATE OR REPLACE FUNCTION 
CREATE OR REPLACE FUNCTION calculate_area_length_width(
    length_val IN NUMBER,
    width_val IN NUMBER,
    area_val OUT NUMBER
) RETURN NUMBER
IS
BEGIN
    area_val := length_val * width_val;
    RETURN area_val;
END;
/

3) SQL Queries:
A)
DESCRIBE nurse;
DESCRIBE physician;
B) SELECT name FROM physician WHERE position = 'Senior Attending';
C) SELECT name FROM physician WHERE department_id IS NULL;
D) SELECT name FROM physician WHERE position LIKE 'Surgical%';
E)
SELECT P.name 
FROM physician P 
JOIN nurse N ON P.position = N.position 
WHERE N.name = 'Carla Espinosa';
F) ALTER TABLE physician MODIFY ssn VARCHAR2(20);
G) DELETE FROM department WHERE departmentid = 1;
H)
CREATE VIEW AttendingPhysicians AS
SELECT name, ssn 
FROM physician 
WHERE position = 'Attending Physician';