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.