Problem 1. Please create the following tables with appropriate primary keys & fo

Question

Problem 1. Please create the following tables with appropriate primary keys & foreign keys. [20 points]

A patient management database that stores information about doctors, patients, appointments:

Doctor table with did (doctor id), dname (doctor name)

Patient table with pid (patient id), pname (patient name), phone (phone# of patient)

Service table: sid (service id), description (description of the service, e.g., 'flu shot')

Appointment table: aid (appointment id), pid (patient id), did (doctor id), reason_for_visit (patient provided detail for visit, e.g., Annual physical), start_time (appointment start time, please use timestamp data type), end_time (estimated end_time), status (integer type with 1=booked, 2=happened, 3=canceled)

Appointment_service table: aid (appointment id), sid (service id, the service provided in that appointment)

Problem 2. Insert at least three rows of data to each table created in Problem 1. Make sure you keep the primary key and foreign key constraints. [20 points]

Problem 3. Please write ONE SQL statement for each of the following tasks using tables created in Problem 1. You can ONLY use conditions listed in the task description. [60 points, 10 points each]

Task 1: return patient id and start time of all appointments that are not canceled and with did=2 and start time on Feb 1 2018. To check the condition start time is on Feb 1 2018, you can use trunc(start_time) to get the date of start_time.

Task 2: return patient id and start time of all appointments that are not canceled and with Dr. Adam and start time on Feb 1 2018.

Task 3: return the doctor id and the total number of not canceled appointments with start time on Feb 1 2018 for each doctor.

Task 4: return the id of doctor who has at least two not canceled appointments on Feb 1 2018.

Task 5: return appointment id, appointment start_time and description of services provided in all not canceled appointments for Ella (Ella is a patient).

Task 6: return the names of doctors who have at least 2 not canceled appointments on Feb 1 2018.

Explanation / Answer

create table Doctor
(did int primary key,
dname varchar(40));

create table Patient
(pid int primary key,
pname varchar(50),
phone varchar(15));

create table Service
(sid int primary key,
description varchar(30));

create table Appointment
(aid int primary key,
pid int,
did int,
reason_for_visit varchar(50),
start_time timestamp,
end_time datetime,
status integer CHECK (status between 1 and 3),
FOREIGN KEY (pid) REFERENCES Patient(pid)
FOREIGN KEY (did) REFERENCES Doctor(did));

create table Appointment_service
(aid int primary key,
sid int,
foreign key(aid) references Appointment(aid)
foreign key(sid) references Service(sid));

insert into Doctor values(1001, "Smith");
insert into Doctor values(1002, "RON");
insert into Doctor values(1003, "Raj");

insert into Patient values(2001, "Shannon","992101020");
insert into Patient values(2002, "Shalom","03032002");
insert into Patient values(2003, "Augusth","23823200");

insert into Service values(3001, "flu shot");
insert into Service values(3002, "DPT vaccine");
insert into Service values(3003, "OPD");

insert into Appointment values(40001, 2001,1001,"Flu shot", '2018-05-13 08:15:30','8:30',1);
insert into Appointment values(40002, 2002,1002,"Viral check", '2018-06-22 09:15:30','9:30',3);
insert into Appointment values(40003, 2001,1003,"general check", '2018-05-12 10:20:10','10:30',2);

insert into Appointment_service values(40001,3001);
insert into Appointment_service values(40002,3002);
insert into Appointment_service values(40003,3003);

select pid, start_time from appointment where status<> 3 and TRUNC(start_time)='2018-02-01';

select pid,start_time from appointment,doctor d where status<>3 and d.dname="Adam" and TRUNC(start_time)='2018-02-01';

select did, count(status) from appointment where status<> 3 and TRUNC(start_time)='2018-02-01' group by did;

select did from appointment where status<> 3 and TRUNC(start_time)='2018-02-01' ;

select A.aid, start_time, p.pname, S.description from appointment A,Service S,Appointment_service A1, Patient p where A.status<> 3 and p.pname="Ella" and A.aid=A1.aid and a1.sid=s.sid

select dname from doctor d, appointment a where status<>3 and d.did=a.did and TRUNC(start_time)

Related Questions

Navigate

• Browse (All)
• Browse P
• Subjects

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