Question

Using the Specialty, Doctor, Patient, Appointment, Allergy, PatientAllergy, Medicine, and PatientMedicine relations created for Hospital System database in the Unit 5 Programming Assignment, populate them with data using the following information in the tables. If your relations have additional attributes that are not included in the following table list, then add appropriate values to populate your relations with data.

Assignment Instructions:

• Provide all of the SQL statements required to create the relations
• Populate the relations with data (using SQL insert statements) by using the information in below tables
• Issue a select statement for each relation to retrieve data in relations
• Include both the select statement and the output of the select statement (using a screenshot of your database’s output) that shows the contents of each relation

Specialty Relation

SpecialtyNumber	SpecialtyName
S1	Dermatology
S2	Psychiatry
S3	Oncology
S4	Cardiology
S5	Urology
S6	Pediatrics

Doctor Relation

DoctorID	Name	Phone	SpecialtyNumber	Supervisor
D1	Doctor Karen	555-1212	S6
D2	Doctor John	555-2934	S2	D1
D3	Doctor Robert	555-6723	S6	D1
D4	Doctor David	555-1745	S4	D1
D5	Doctor Mary	555-6565	S5	D1
D6	Doctor Linda	555-4889	S1	D1
D7	Doctor Susan	555-4581	S3	D1
D8	Doctor Zeynep	555-7891	S4	D1
D9	Doctor Mat	555-7791	S1	D1

Patient Relation

PatientID	DoctorID	Name	Phone	Email	Address	AddedDate
P1	D2	Patient Dana	444-1212	P1@email.com	123 Home St.	02/01/2019
P2	D7	Patient Harry	444-2934	P2@email.com	3435 Main St.	7/13/2011
P3	D6	Patient Karl	444-6723	P3@email.com	2176 Baker St.	5/10/2009
P4	D2	Patient Sid	444-1745	P4@email.com	176 Right St.	6/20/2010
P5	D8	Patient Marry	444-6565	P5@email.com	435 Main St.	5/18/2014
P6	D6	Patient Kim	444-4889	P6@email.com	34 Home St.	3/15/2018
P7	D4	Patient Susan	444-4581	P7@email.com	65 Water St.	9/07/2011
P8	D3	Patient Sam	444-7891	P8@email.com	23 Hill Drive	11/23/2010
P9	D5	Patient Peter	444-7791	P9@email.com	12 River St.	02/01/2008
P10	D7	Patient Nick	123-1212	P10@email.com	335 Bay St.	7/13/2011
P11	D9	Patient Kyle	123-2934	P11@email.com	216 Baker St.	5/10/2016
P12	D9	Patient Garcia	123-6723	P12@email.com	176 Right St.	6/20/2010
P13	D4	Patient Alicia	123-1745	P13@email.com	823 Left St.	5/18/2015
P14	D4	Patient Dan	123-6565	P14@email.com	534 High St.	3/15/2018

Appointment

AppointmentID	PatientID	DoctorID	AppointmentDate	BloodPressure	Weight	TreatmentNotes
A1	P1	D2	07/01/2019	80	65	Dream to success
A2	P13	D4	01/04/2019	77	88	Good heart rate
A3	P11	D9	03/22/2019	82	95	Many spots
A4	P7	D4	02/01/2020	85	74	Fast heart rate
A5	P9	D5	04/13/2019	75	56	Reports checked
A6	P3	D6	11/12/2019	81	96	Sun light spots
A7	P10	D7	01/29/2020	80	87	Early treatment
A8	P9	D5	08/12/2019	86	92	Much better
A9	P14	D4	05/18/2019	75	75	Good heart rate
A10	P8	D3	11/18/2019	76	79	New teeth
A11	P11	D9	06/22/2019	78	71	Much better
A12	P2	D7	02/21/2020	82	86	Early treatment
A13	P4	D2	08/17/2019	81	101	Bad dreams
A14	P6	D6	06/27/2019	79	49	Sun light spots
A15	P10	D7	07/29/2020	80	83	Early treatment
A16	P7	D4	08/01/2020	78	79	Good heart rate

Allergy Relation

AllergyID	AllergyName
AL1	Drug
AL2	Food
AL3	Skin
AL4	Asthma
AL5	Rhinitis

PatientAllergy Relation

AllergyID	PatientID
AL4	P1
AL2	P13
AL3	P11
AL4	P7
AL5	P9
AL1	P3

Medicine Relation

MedicineID	MedicineName
M1	Ativan
M2	Ibuprofen
M3	Omeprazole
M4	Metoprolol
M5	Azithromycin
M6	Codeine

PatientMedicine

AppointmentID	MedicineID
A15	M1
A2	M6
A8	M3
A6	M3
A15	M2
A10	M6
A10	M2
A4	M5
A3	M5
A1	M2

Answer 1

Speciality Relation :
create table Speciality(SpecialityNumber varchar(10) primary key, SpecialityName varchar(30));
insert into Speciality values("S1","Dermatology");
insert into Speciality values("S2","Psychiatry");
insert into Speciality values("S3","Oncology");
insert into Speciality values("S4","Cardiology");
insert into Speciality values("S5","Urology");
insert into Speciality values("S6","Pediatrics");

Doctor relation :
create table Doctor(DoctorID varchar(10) primary key, DoctorName varchar(30), Phone varchar(30), SpecialityNumber varchar(30), Supervisor varchar(30),foreign key (SpecialityNumber) references Speciality(SpecialityNumber));
insert into Doctor values("D1","Doctor Karen","555-1212","S6","NULL");
insert into Doctor values("D2","Doctor John","555-2934","S2","D1");
insert into Doctor values("D3","Doctor Robert","555-6723","S6","D1");
insert into Doctor values("D4","Doctor David","555-1745","S4","D1");
insert into Doctor values("D5","Doctor Mary","555-6565","S5","D1");
insert into Doctor values("D6","Doctor Linda","555-4889","S1","D1");
insert into Doctor values("D7","Doctor Susan","555-4581","S3","D1");
insert into Doctor values("D8","Doctor Zeynep","555-7891","S4","D1");
insert into Doctor values("D9","Doctor Mat","555-7791","S1","D1");

Patient relation :
create table Patient(PatientID varchar(10) primary key, DoctorID varchar(30), PatientName varchar(30), Phone varchar(30), Email varchar(30),foreign key (DoctorID) references Doctor(DoctorID));
insert into Patient values("P1","D2","Patient Dana","444-1212","P1@email.com");
insert into Patient values("P2","D7","Patient Harry","444-2934","P2@email.com");
insert into Patient values("P3","D6","Patient Karl","444-6723","P3@email.com");
insert into Patient values("P4","D2","Patient Sid","444-1745","P4@email.com");
insert into Patient values("P5","D8","Patient Marry","444-6565","P5@email.com");
insert into Patient values("P6","D6","Patient Kim","444-4889","P6@email.com");
insert into Patient values("P7","D4","Patient Susan","444-4581","P7@email.com");
insert into Patient values("P8","D3","Patient Sam","444-7891","P8@email.com");
insert into Patient values("P9","D5","Patient Peter","444-7791","P9@email.com");
insert into Patient values("P10","D7","Patient Nick","123-1212","P10@email.com");
insert into Patient values("P11","D9","Patient Kyle","123-2934","P11@email.com");
insert into Patient values("P12","D9","Patient Garcia","123-6723","P12@email.com");
insert into Patient values("P13","D4","Patient Alicia","123-1745","P13@email.com");
insert into Patient values("P14","D4","Patient Dan","123-6565","P14@email.com");

Appointment Relation :
create table Appointment(AppointmentID varchar(10) primary key, PatientID varchar(30), DoctorID varchar(30), AppointmentDate varchar(30),foreign key (PatientID) references Patient(PatientID),foreign key (DoctorID) references Doctor(DoctorID));
insert into Appointment values("A1","P1","D2","07/01/2019");
insert into Appointment values("A2","P13","D4","01/04/2019");
insert into Appointment values("A3","P11","D9","03/22/2019");
insert into Appointment values("A4","P7","D4","02/01/2020");
insert into Appointment values("A5","P9","D5","04/13/2019");
insert into Appointment values("A6","P3","D6","11/12/2019");
insert into Appointment values("A7","P10","D7","01/29/2020");
insert into Appointment values("A8","P9","D5","08/12/2019");
insert into Appointment values("A9","P14","D4","05/18/2019");
insert into Appointment values("A10","P8","D3","11/18/2019");
insert into Appointment values("A11","P11","D9","06/22/2019");
insert into Appointment values("A12","P2","D7","02/21/2020");
insert into Appointment values("A13","P4","D2","08/17/2019");
insert into Appointment values("A14","P6","D6","06/27/2019");
insert into Appointment values("A15","P10","D7","07/29/2020");
insert into Appointment values("A16","P7","D4","08/01/2020");

Allergy Relation :
create table Allergy(AllergyID varchar(10) primary key, AllergyName varchar(30));
insert into Allergy values("AL1","Drug");
insert into Allergy values("AL2","Food");
insert into Allergy values("AL3","Skin");
insert into Allergy values("AL4","Asthma");
insert into Allergy values("AL5","Rhinitis");

PatientAllergy Relation :
create table PatientAllergy(AllergyID varchar(10), PatientID varchar(30),foreign key (AllergyID) references Allergy(AllergyID),foreign key (PatientID) references Patient(PatientID));
insert into PatientAllergy values("AL4","P1");
insert into PatientAllergy values("AL2","P13");
insert into PatientAllergy values("AL3","P11");
insert into PatientAllergy values("AL4","P7");
insert into PatientAllergy values("AL5","P9");
insert into PatientAllergy values("AL1","P3");

Medicine Relation :
create table Medicine(MedicineID varchar(10) primary key, MedicineName varchar(30));
insert into Medicine values("M1","Ativan");
insert into Medicine values("M2","Ibuprofen");
insert into Medicine values("M3","Omeprazole");
insert into Medicine values("M4","Metoprolol");
insert into Medicine values("M5","Azithromycin");

PatientMedicine Relation :
create table PatientMedicine(AppointmentID varchar(10), MedicineID varchar(30),foreign key (AppointmentID) references Appointment(AppointmentID),foreign key (MedicineID) references Medicine(MedicineID));
insert into PatientMedicine values("A15","M1");
insert into PatientMedicine values("A2","M6");
insert into PatientMedicine values("A8","M3");
insert into PatientMedicine values("A6","M3");
insert into PatientMedicine values("A15","M2");
insert into PatientMedicine values("A10","M6");
insert into PatientMedicine values("A10","M2");
insert into PatientMedicine values("A4","M5");
insert into PatientMedicine values("A3","M5");
insert into PatientMedicine values("A1","M2");