Consider the following relations:

Doctor(SSN, FirstName, LastName, Specialty, YearsOfExperience, PhoneNum)

Patient(SSN, FirstName, LastName, Address, DOB, PrimaryDoctor_SSN)

Medicine(TradeName, UnitPrice, GenericFlag)

Prescription(Id, Date, Doctor_SSN, Patient_SSN)

Prescription_Medicine(Id,Prescription_Id, TradeName, NumOfUnits)

• The Doctor relation has attributes Social Security Number (SSN), first and last names,specialty, the number of experience years, and phone number.
• The Patient relation has attributes SSN, first and last names, address, date of birth(DOB), and the SSN of the patientʼs primary doctor.
•  The Medicine relation has attributes of trade name, unit price, and whether or not themedicine is generic (True or False).
• The Prescription relation has attributed to the prescription id, the date on which the prescription is written, the SSN of the doctor who wrote the prescription, and the SSN of the patient to whom the prescription is written.
• The Prescription_Medicine relation stores the medicines written in each prescription along with their quantities (number of units).

1. Write SQL queries to create the above tables and also show the relationship for the above

entities.

The underline attribute is the primary key and the foreign key.

2. Write the SQL expression for the following query

a. Write SQL query to show the trade name of generic medicine with unit

price less than $50.

b. Write SQL query to show the first and last name of patients whose primary

doctor named ʻJohn Smithʼ.

c. Write the SSN of patients who have ʻAspirinʼ and ʻVitaminʼ trade names in

one prescription.

d. Write a SQL query to show all doctors information whose experience is

greater than 5 years and specialty is a neurologist.

Answer To the Question 01

CREATE DATABASE dbmslab_db;

CREATE TABLE doctor_tbl(

   doctor_ssn INT NOT NULL AUTO_INCREMENT,

   first_name VARCHAR(100) NOT NULL,

   last_name VARCHAR(100) NOT NULL,

   speciality VARCHAR(100) NOT NULL,

   yearsof_experience VARCHAR(40) NOT NULL,

   PhoneNum VARCHAR(40) NOT NULL,

   PRIMARY KEY(doctor_ssn)

);

CREATE TABLE patient_tbl(

   patient_ssn INT NOT NULL AUTO_INCREMENT,

   first_name VARCHAR(100) NOT NULL,

   last_name VARCHAR(100) NOT NULL,

   address VARCHAR(100) NOT NULL,

   DOB DATE NOT NULL,

   doctor_ssn INT(10),

   PRIMARY KEY(patient_ssn),

   FOREIGN KEY (doctor_ssn) REFERENCES doctor_tbl (doctor_ssn)

);

CREATE TABLE medicine_tbl(

   trade_name VARCHAR(100) NOT NULL,

   unit_price VARCHAR(100) NOT NULL,

   generic_flag VARCHAR(100) NOT NULL,

   PRIMARY KEY(trade_name)

);

CREATE TABLE prescription_tbl(

   prescription_id INT NOT NULL AUTO_INCREMENT,

   prescription_date DATE NOT NULL,

   doctor_ssn INT,

   patient_ssn INT,

   PRIMARY KEY(prescription_id),

   FOREIGN KEY doctor_ssn REFERENCES doctor_tbl(doctor_ssn),

   FOREIGN KEY patient_ssn REFERENCES patient_tbl(patient_ssn) 

);

CREATE TABLE prescription_medicine_tbl(

   prescription_medicine_id INT NOT NULL AUTO_INCREMENT,

   prescription_id INT, 

   trade_name VARCHAR(100) NOT NULL,

   num_of_units VARCHAR(100) NOT NULL,

   PRIMARY KEY(prescription_medicine_id),

   FOREIGN KEY(prescription_id) REFERENCES prescription_tbl(prescription_id), 

   FOREIGN KEY(trade_name) REFERENCES medicine_tbl(trade_name)

);

INSERT INTO medicine_tbl(trade_name, unit_price,generic_flag) VALUES ("square","15$", "safe"), ("ibne sina","20$", "effective"), ("Incepta","30$", "high quality"), ("Opsonin ","50$", "strength"), ("Renata","60$", "safe")

INSERT INTO prescription_tbl(prescription_date,doctor_ssn,patient_ssn) VALUES ("2022-2-6","1", "1"), ("2022-2-6","3", "2"), ("2022-2-6","5", "3"), ("2022-2-6","2", "4"), ("2022-2-6","4", "5")

INSERT INTO prescription_medicine_tbl(prescription_id,trade_name,num_of_units) VALUES ("1","square", "5"), ("2","ibne sina", "10"), ("3","Opsonin", "8"), ("4","square", "9"), ("5","ibne sina", "5")

Answer To question no 02

a. 

SELECT* FROM medicine_tbl WHERE unit_price <"50$";

b.

SELECT patient_tbl.first_name, patient_tbl.last_name

FROM patient_tbl

INNER JOIN doctor_tbl ON patient_tbl.doctor_ssn = doctor_tbl.doctor_ssn AND doctor_tbl.first_name = "jubair";

c.

SELECT prescription_tbl.patient_ssn FROM prescription_tbl INNER JOIN prescription_medicine_tbl ON prescription_tbl.prescription_id = prescription_medicine_tbl.prescription_id WHERE prescription_medicine_tbl.trade_name = 'square' OR prescription_medicine_tbl.trade_name = 'ibne sina';

d.

SELECT* FROM doctor_tbl WHERE yearsof_experience>5 AND speciality="neurologist";

 Relational Design