Code that needs to be used: comment: write your own CSID after the word USE in the below line use /*rafan*/; drop table if exists visits; drop table if exists physicians; drop table if exists patients; CREATE TABLE physicians( ID int not null, name varchar(500) not null, # comment department varchar(50), office_phone varchar(15), seniority varchar(50), PRIMARY KEY(ID) ); ALTER TABLE physicians MODIFY ID int auto_increment; CREATE TABLE patients( ID int not null auto_increment PRIMARY KEY, name varchar(500) not null, family_physician varchar(200), date_of_birth date, main_phone varchar(15), home_address varchar(500) ); CREATE TABLE visits( ID int not null auto_increment PRIMARY KEY, patient_ID int not null, physician_ID int not null, visit_date date, primary_diagnoses varchar(1500), room_number varchar(5)); alter table visits add foreign key (patient_ID) references patients(ID); alter table visits add foreign key (physician_ID) references physicians(ID); #==================================== insert into physicians values (null, "Ahmed Allam", "Cardiology", "9024321654", "PhD"); insert into physicians values (null, "James Hanlon", "Pediatrics", "9029876545", "M.D"); insert into physicians values (null, "Andy Woodland", "Orthopedics", "9029874398", "BSc"); insert into physicians values (null, "Naifish Sivakumar", "General Medicine", "9021875367", "PhD"); insert into physicians values (null, "John Steven", "Physiotherapy", "90209868768", "M.D"); insert into patients values (null, "John Brown", null, '1970-08-23', "+19023334321", "1670 Qunipool St, Halifax, NS"); insert into patients values (null, "Jessy Clark", "Michael Gerges", '1980-01-15', "+19027652345", "20 Connaught Ave, Bedford, NS"); insert into patients values (null, "Hany Ahmed", null, '2005-09-13', "+19023334321", "188 Sheppards Run, Halifax, NS"); insert into patients values (null, "Shail Kumar", "Frnaklin Johs", '1989-12-25', "+19029876554", "299 Larry Uteck Blvd, Bedford, NS"); insert into patients values (null, "Jamie Collins", null, '2000-07-20', "+19020987656", "155 Nine Mile Dr, Halifax, NS"); insert into visits values (null, 1, 3, '2017-07-23', "Left Knee Fracture", "A32" ); insert into visits values (null, 2, 5, '2016-09-16', "Severe pain in the back muscles", "B4"); insert into visits values (null, 4, 5, '2016-08-10', "Pain in left arm muscles", "C16"); #=========================================================================== Select * from patients; Select * from physicians; select patients.name, physicians.name from patients, physicians, visits where patients.ID = visits.patient_ID and physicians.ID = visits.physician_ID; 

Question as follows:

Description: Students will practice how to insert into and select from a simple database.

Tasks:

A. Insert: (6 marks)

Hint: Insert valid data, and beware of column data types. Assume primary keys are auto incremented.

1. A new row in the Patients table. (2 marks) 2. A new row in the Doctors table. (2 marks)

3. A new row in the Visits table to record a new patient visit. Use data that exists in the other tables for the columns Patient ID and Doctor ID, and assume any other valid data in the remaining columns. (2 marks)

B. Select: (4 marks) 1. Doctors who work in the Cardiology department. (2 marks)

2. Patients who were previously treated by Dr. John Steven (Doctor_ID 5).

Questions (4 marks) Hint: only brief answers are required, but you are free to explain as much as you need.

C. What is the relationship type between Patients and Doctors tables? Briefly explain your answer. (2 marks) D. What is the purpose of the columns Patient ID in the Patients table and Doctor ID in the Doctors

table? And what is their purpose in the Visits table? (2 marks)

Bonus Question (2 marks)

In task B-2 above, assume that we do not know the ID of Dr. John Steven in the Doctors table, and rewrite the select statement accordingly.

(2 marks)

Your deliverable for this Question:

For Tasks and Bonus parts, submit your SQL code that you have run correctly and produced results, label each answer of yours by the task letter and number (e.g. A-2). TA will run the same code to get results. If you couldnt run the code correctly and received errors and therefore you have no results, submit your code and explain what you did. You will still get a half mark for each task for which you wrote the SQL code partially correct.

For the Questions part, submit your answers labeled by the question letter (e.g. C or D).

This needs to be done in MySQL. And step by step process needs to be shown.