Programming Assign. Unit 7
docx
keyboard_arrow_up
School
University of the People *
*We aren’t endorsed by this school
Course
2203
Subject
Medicine
Date
Feb 20, 2024
Type
docx
Pages
9
Uploaded by EarlRockAlpaca7
Programming Assign. Unit 7
Assignment Instructions:
Provide all of the SQL statements required to create the relations
Populate the relations with data (using SQL insert statements) by using 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
SQL statements required to create the relations
CREATE TABLE Specialty (
SpecialtyNumber VARCHAR(10) PRIMARY KEY,
SpecialtyName VARCHAR(50)
);
CREATE TABLE Doctor (
DoctorID VARCHAR(10) PRIMARY KEY,
Name VARCHAR(50),
Phone VARCHAR(15),
SpecialtyNumber VARCHAR(10),
Supervisor VARCHAR(10),
FOREIGN KEY (SpecialtyNumber) REFERENCES Specialty(SpecialtyNumber),
FOREIGN KEY (Supervisor) REFERENCES Doctor(DoctorID)
);
CREATE TABLE Patient (
PatientID VARCHAR(10) PRIMARY KEY,
DoctorID VARCHAR(10),
Name VARCHAR(50),
Phone VARCHAR(15),
Email VARCHAR(50),
Programming Assign. Unit 7
Address VARCHAR(100),
AddedDate DATE,
FOREIGN KEY (DoctorID) REFERENCES Doctor(DoctorID)
);
CREATE TABLE Appointment (
AppointmentID VARCHAR(10) PRIMARY KEY,
PatientID VARCHAR(10),
DoctorID VARCHAR(10),
AppointmentDate DATE,
BloodPressure INT,
Weight INT,
TreatmentNotes VARCHAR(100),
FOREIGN KEY (PatientID) REFERENCES Patient(PatientID),
FOREIGN KEY (DoctorID) REFERENCES Doctor(DoctorID)
);
CREATE TABLE Allergy (
AllergyID VARCHAR(10) PRIMARY KEY,
AllergyName VARCHAR(50)
);
CREATE TABLE PatientAllergy (
PatientID VARCHAR(10),
AllergyID VARCHAR(10),
FOREIGN KEY (PatientID) REFERENCES Patient(PatientID),
FOREIGN KEY (AllergyID) REFERENCES Allergy(AllergyID)
);
Programming Assign. Unit 7
CREATE TABLE Medicine (
MedicineID VARCHAR(10) PRIMARY KEY,
MedicineName VARCHAR(50)
);
CREATE TABLE PatientMedicine (
PatientID VARCHAR(10),
MedicineID VARCHAR(10),
FOREIGN KEY (PatientID) REFERENCES Patient(PatientID),
FOREIGN KEY (MedicineID) REFERENCES Medicine(MedicineID)
);
Insert statments
-- Specialty Relation
INSERT INTO Specialty (SpecialtyNumber, SpecialtyName) VALUES
('S1', 'Dermatology'),
('S2', 'Psychiatry'),
('S3', 'Oncology'),
('S4', 'Cardiology'),
('S5', 'Urology'),
('S6', 'Pediatrics');
-- Doctor Relation
INSERT INTO Doctor (DoctorID, Name, Phone, SpecialtyNumber, Supervisor) VALUES
('D1', 'Doctor Karen', '555-1212', 'S6', NULL),
('D2', 'Doctor John', '555-2934', 'S2', 'D1'),
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
Programming Assign. Unit 7
('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
INSERT INTO Patient (PatientID, DoctorID, Name, Phone, Email, Address, AddedDate) VALUES
('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 Relation
INSERT INTO Appointment (AppointmentID, PatientID, DoctorID, AppointmentDate, BloodPressure, Weight, TreatmentNotes) VALUES
('A1', 'P1', 'D2', '07/01/2019', 80, 65, 'Dream to success'),
Programming Assign. Unit 7
('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
INSERT INTO Allergy (AllergyID, AllergyName) VALUES ('AL1', 'Drug'),
('AL2', 'Food'),
('AL3', 'Skin'),
('AL4', 'Asthma'),
('AL5', 'Rhinitis');
-- PatientAllergy Relation
INSERT INTO PatientAllergy (AllergyID, PatientID) VALUES
('AL4', 'P1'),
('AL2', 'P13'),
Programming Assign. Unit 7
('AL3', 'P11'),
('AL4', 'P7'),
('AL5', 'P9'),
('AL1', 'P3');
-- Medicine Relation
INSERT INTO Medicine (MedicineID, MedicineName)
VALUES
('M1', 'Ativan'),
('M2', 'Ibuprofen'),
('M3', 'Omeprazole'),
('M4', 'Metoprolol'),
('M5', 'Azithromycin'),
('M6', 'Codeine');
INSERT INTO PatientMedicine (AppointmentID, MedicineID) VALUES
('A15', 'M1'),
('A2', 'M6'),
('A8', 'M3'),
('A6', 'M3'),
('A15', 'M2'),
('A10', 'M6'),
('A10', 'M2'),
('A4', 'M5'),
('A3', 'M5'),
('A1', 'M2');
• Issue a select statement for each relation to retrieve data in relations
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
Programming Assign. Unit 7
-- Select statement for Specialty relation
SELECT * FROM Specialty;
-- Select statement for Doctor relation
SELECT * FROM Doctor;
-- Select statement for Patient relation
SELECT * FROM Patient;
Programming Assign. Unit 7
-- Select statement for Appointment relation
SELECT * FROM Appointment;
-- Select statement for Allergy relation
SELECT * FROM Allergy;
-- Select statement for PatientAllergy relation
SELECT * FROM PatientAllergy;
Programming Assign. Unit 7
-- Select statement for Medicine relation
SELECT * FROM Medicine;
-- Select statement for PatientMedicine relation
SELECT * FROM PatientMedicine;
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help