Suppose you have created tables in assignment 1. You can also use the sample cod

Question

Suppose you have created tables in assignment 1. You can also use the sample code included at the end of the document to create the tables.

Problem 1: Please create a PL/SQL procedure that given a patient's name, print out the start_time of the patient's appointment. Do not include canceled appointments. [50 points]

Problem 2: [50 points] Please create a PL/SQL procedure that checks availability of a doctor. The procedure has three input parameters: the doctor’s name, the start_time and length of a new appointment (the length is interval day to second type),and checks whether the doctor has any existing appointment that overlaps with new appointment. If there is no overlap, the procedure sets the output parameter to 1. Otherwise, the procedure sets the output to 0. Please do not count canceled appointments when checking overlap.

Please also write an anonymous PL/SQL program to call this procedure.

E.g., if the input is 'Dr. Rao', timestamp '2018-2-1 09:15:00.00', and interval '30' minute (i.e., some patient wants to see Dr. Rao from 9:15 am to 9:45 am on Feb 1, 2018). Since there are appointments from 9 to 9:30 and 9:30 to 9:40 for Dr. Rao, this procedure will set output parameter to 0 (there is overlap). If the input is 'Dr. Rao', timestamp '2018-2-1 10:00:00.00', and interval '30' minute, the procedure set the output to 1 because there is no conflict.

--- sample code to create the tables

drop table appointment_service cascade constraints;

drop table appointment cascade constraints;

drop table service cascade constraints;

drop table patient cascade constraints;

drop table doctor cascade constraints;

create table doctor

(did int,

dname varchar(50),

primary key(did));

insert into doctor values(1,'Dr. Rao');

insert into doctor values(2,'Dr. Adam');

insert into doctor values(3,'Dr. Smith');

create table patient

(pid int,

pname varchar(50),

pphone varchar(20),

primary key(pid));

insert into patient values(1,'Susan', '410-456-1122');

-- son of susan

insert into patient values(2,'Nathan','410-456-1122');

insert into patient values(3,'Ella', '410-456-8876');

insert into patient values(4,'Carl', '410-456-8876');

create table service

(sid int,

sdecription varchar(200),

primary key(sid));

insert into service values(1,'office visit');

insert into service values(2,'flu shot');

insert into service values(3,'physical exam');

create table appointment

(aid int,

pid int,

did int,

reason_for_visit varchar(200),

start_time timestamp, end_time timestamp,

status int, -- 1 booked, 2 happended, 3 canceled

primary key(aid),

foreign key (pid) references patient,

foreign key (did) references doctor);

-- done

insert into appointment values(1,1,1,'flu and low fever', timestamp '2018-2-1 9:00:00.00',

timestamp '2018-2-1 9:30:00.00',2);

-- done

insert into appointment values(2,2,1,'flu shot', timestamp '2018-2-1 9:30:00.00',

timestamp '2018-2-1 9:40:00.00',2);

-- canceled

insert into appointment values(3,3,2,'annual physical', timestamp '2018-2-1 9:00:00.00',

timestamp '2018-2-1 9:30:00.00',3);

-- booked

insert into appointment values(4,4,2,'annual physical', timestamp '2018-2-1 16:00:00.00',

timestamp '2018-2-1 16:30:00.00',1);

-- booked

insert into appointment values(5,1,1,'follow up', timestamp '2018-2-3 12:00:00.00',

timestamp '2018-2-3 12:30:00.00',1);

-- booked

insert into appointment values(6,3,2,'annual physical', timestamp '2018-2-3 9:00:00.00',

timestamp '2018-2-3 9:30:00.00',1);

insert into appointment values(7,3,2,'flu like symptom', timestamp '2018-2-3 11:00:00.00',

timestamp '2018-2-3 11:30:00.00',1);

-- these are fictitious appointment testing new appointment

insert into appointment values(8,2,1,'follow up', timestamp '2018-2-3 09:00:00.00',

timestamp '2018-2-3 12:00:00.00',1);

insert into appointment values(9,2,1,'follow up', timestamp '2018-2-3 12:30:00.00',

timestamp '2018-2-3 16:30:00.00',1);

create table appointment_service

(

aid int,

sid int,

primary key(aid, sid),

foreign key(aid) references appointment,

foreign key(sid) references service

);

-- 1: office visit, 2: flu shot, 3 physical

insert into appointment_service values(1, 1);

insert into appointment_service values(2, 2);

insert into appointment_service values(4, 2);

insert into appointment_service values(4, 3);

insert into appointment_service values(5, 1);

insert into appointment_service values(6, 2);

insert into appointment_service values(6, 3);

insert into appointment_service values(7, 1);

insert into appointment_service values(8, 1);

insert into appointment_service values(9, 1);

commit;

Explanation / Answer

Problem1.

create or replace procedure "appointment_start_time"

(patient_name IN VARCHAR) IS

BEGIN

select start_time from appointment where status != 3 and pid = (select pid from patient where pname= patient_name);

END;

Explanation:

Let's explain line by line what is happening

In line 1 we are creating a procedure named `appointment_start_time` if the procedure already exists it will repace the old procedure

In line 2 we are taking an Input parameter for patient name

In Line 3 this line marks the starting of the procedure.

In line 4 we are executing the actual query that will produce the answer for appointment start_time for patient name pname whose appointment is not cancelled.

In line 5 We end the procedure

To execute the procedure use this line

BEGIN

appointment_start_time("carl")

END;

parameter can be repalce with any name

Related Questions

Navigate

• Browse (All)
• Browse S
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.