SQL Statements
Statements for Proposed Database
Below are the statements for the database (DB) designed for substance management on hospital grounds.
— Student Name
— Course Number
— Date
— This is my own work.
.open HospitalDB
.header on
.mode column
CREATE TABLE groups
(
gr_id INT NOT NULL,
gr_name CHAR(24) NOT NULL,
gr_descript VARCHAR(255) NOT NULL,
PRIMARY KEY (gr_id)
);
CREATE TABLE department
(
dept_id INT NOT NULL,
dept_name VARCHAR(24) NOT NULL,
dept_head VARCHAR(24) NOT NULL,
PRIMARY KEY (dept_id)
);
CREATE TABLE substance
(
sub_id INT NOT NULL,
sub_name VARCHAR(24) NOT NULL,
sub_mng VARCHAR(255) NOT NULL,
sub_therind VARCHAR(255) NOT NULL,
sub_ddi VARCHAR(255) NOT NULL,
sub_adm VARCHAR(255) NOT NULL,
sub_cntind VARCHAR(255) NOT NULL,
sub_overd VARCHAR(255) NOT NULL,
sub_storeq VARCHAR(255) NOT NULL,
gr_id INT NOT NULL,
PRIMARY KEY (sub_id),
FOREIGN KEY (gr_id) REFERENCES groups(gr_id)
);
CREATE TABLE employee
(
emp_id INT NOT NULL,
emp_fname VARCHAR(24) NOT NULL,
emp_lname VARCHAR(24) NOT NULL,
emp_midinit CHAR(1) NOT NULL,
dept_id INT NOT NULL,
PRIMARY KEY (emp_id),
FOREIGN KEY (dept_id) REFERENCES department(dept_id)
);
CREATE TABLE storage
(
sto_id INT NOT NULL,
sto_date DATE NOT NULL,
sto_usedate DATE NOT NULL,
sto_util yesno,
sub_id INT NOT NULL,
emp_id INT,
PRIMARY KEY (sto_id),
FOREIGN KEY (sub_id) REFERENCES substance(sub_id),
FOREIGN KEY (emp_id) REFERENCES epmloyee(emp_id)
);
Below are the statements for DB population with fictional data with screenshots of SELECT * FROM queries.
INSERT INTO groups ( gr_id, gr_name, gr_descript) VALUES ( ‘1’, ‘Antidepressants’, ‘Help to manage stress’ ) ;
INSERT INTO groups ( gr_id, gr_name, gr_descript) VALUES ( ‘2’, ‘Antibiotics’, ‘Kill bacteria’ ) ;
INSERT INTO groups ( gr_id, gr_name, gr_descript) VALUES ( ‘3’, ‘Antiallergy’, ‘Help to manage allergies’ ) ;
INSERT INTO groups ( gr_id, gr_name, gr_descript) VALUES ( ‘4’, ‘Vitamins’, ‘Improve vitamins counts’ ) ;
INSERT INTO department (dept_id, dept_name, dept_head) VALUES (‘1’, ‘Surgery’, ‘John Brown’ ) ;
INSERT INTO department (dept_id, dept_name, dept_head) VALUES (‘2’, ‘Nursery’, ‘Peter Black’ ) ;
INSERT INTO department (dept_id, dept_name, dept_head) VALUES (‘3’, ‘Pharmacy’, ‘Helen Red’ ) ;
INSERT INTO department (dept_id, dept_name, dept_head) VALUES (‘4’, ‘Therapy’, ‘Paul Yellow’ ) ;
INSERT INTO substance (sub_id, sub_name, sub_mng, sub_therind, sub_ddi, sub_adm, sub_cntind, sub_overd, sub_storeq, gr_id) VALUES (‘1’, ‘Antieviline’, ‘Manage with caution’, ‘Being evil’, ‘Antigoodine’, ‘Orally, twice a day, 1 tablet’, ‘Being good’, ‘Becomes too good. Stop treatment’, ‘6 months, temp between 0 and 5 degrees Celsius’, ‘1’ ) ;
INSERT INTO substance (sub_id, sub_name, sub_mng, sub_therind, sub_ddi, sub_adm, sub_cntind, sub_overd, sub_storeq, gr_id) VALUES (‘2’, ‘Antigoodine’, ‘Manage with caution’, ‘Being good’, ‘Antieviline’, ‘Orally, twice a day, 1 tablet’, ‘Being evil’, ‘Becomes too evil. Stop treatment’, ‘6 months, temp between 0 and 5 degrees Celsius’, ‘1’ ) ;
INSERT INTO substance (sub_id, sub_name, sub_mng, sub_therind, sub_ddi, sub_adm, sub_cntind, sub_overd, sub_storeq, gr_id) VALUES (‘3’, ‘Antisadine’, ‘Manage with caution’, ‘Being sad’, ‘Antihappyine’, ‘Orally, once a day, 1 tablet’, ‘Being happy’, ‘Becomes too happy. Stop treatment’, ‘6 months, temp between 0 and 5 degrees Celsius’, ‘1’ ) ;
INSERT INTO substance (sub_id, sub_name, sub_mng, sub_therind, sub_ddi, sub_adm, sub_cntind, sub_overd, sub_storeq, gr_id) VALUES (‘4’, ‘Antihappyine’, ‘Manage with caution’, ‘Being happy’, ‘Antisadine’, ‘Orally, once a day, 1 tablet’, ‘Being sad’, ‘Becomes too sad. Stop treatment’, ‘6 months, temp between 0 and 5 degrees Celsius’, ‘1’ ) ;
INSERT INTO employee (emp_id, emp_fname, emp_lname, emp_midinit, dept_id) VALUES (‘1’, ‘Harry’, ‘Potter’, ‘A’, ‘4’) ;
INSERT INTO employee (emp_id, emp_fname, emp_lname, emp_midinit, dept_id) VALUES (‘2’, ‘Hermione’, ‘Granger’, ‘B’, ‘3’) ;
INSERT INTO employee (emp_id, emp_fname, emp_lname, emp_midinit, dept_id) VALUES (‘3’, ‘Ron’, ‘Weasley’, ‘C’, ‘2’) ;
INSERT INTO employee (emp_id, emp_fname, emp_lname, emp_midinit, dept_id) VALUES (‘4’, ‘Tom’, ‘Riddle’, ‘D’, ‘1’) ;
INSERT INTO storage (sto_id, sto_date, sto_usedate, sto_util, sub_id, emp_id) VALUES (‘1’, ‘10-02-2019’, ’10-08-2019’, ‘yes’, ‘1’, ‘1’ ) ;
INSERT INTO storage (sto_id, sto_date, sto_usedate, sto_util, sub_id, emp_id) VALUES (‘2’, ‘10-02-2019’, ’10-08-2019’, ‘yes’, ‘2’, ‘1’ ) ;
INSERT INTO storage (sto_id, sto_date, sto_usedate, sto_util, sub_id, emp_id) VALUES (‘3’, ‘10-02-2019’, ’10-08-2019’, ‘yes’, ‘3’, ‘1’ ) ;
INSERT INTO storage (sto_id, sto_date, sto_usedate, sto_util, sub_id, emp_id) VALUES (‘4’, ‘10-02-2019’, ’10-08-2019’, ‘yes’, ‘4’, ‘2’ ) ;
Addition of Deleted Date Attribute
Over time, there may appear a need to delete rows of data from a DB for various reasons. The primary concern is not removing a row and using a soft deletion instead is safety. There is always a possibility of human error and data can be deleted by accident. Another advantage of using a marker column is preserving historical data for future analysis (Coronel & Morris, 2019). However, there are also tradeoffs for using this method of deletion, which includes privacy concerns (Ben-Assuli, 2015). Moreover, a DB using soft removal can become too sizeable, and there may appear problems with storage. In short, while there are clear benefits of using a marker-column for deletion, as opposed to deleting a row from a table, a DB administrator should also be aware of the tradeoffs of the method.
As the DB described above is not designed for soft deletion, there is a need to introduce a new attribute to the tables where data is most likely to be deleted. I chose to modify the employee table to acknowledge employee turnover. Additionally, I decided to change the substance table, as new drugs may appear on the market. I added emp_deletedate and sub_deletedate attributes respectively to the two entities. Below are the SQL queries for the procedure with a screenshot of .schema command.
ALTER TABLE substance ADD COLUMN sub_deletedate date ;
ALTER TABLE employee ADD COLUMN emp_deletedate date ;
SQL Queries
Database Schema
SQL Statements
— Student Name
— Course Number
— Date
— This is my own work.
.open Assignment2
.header on
.mode column
CREATE TABLE Patient
(
PatientID INT NOT NULL,
Name VARCHAR(50) NOT NULL,
DOB DATE NOT NULL,
PRIMARY KEY (PatientID)
);
CREATE TABLE Doctor
(
DoctorID INT NOT NULL,
Name VARCHAR(50) NOT NULL,
MedLicenseNumber INT NOT NULL,
Phone VARCHAR(20) NOT NULL,
PRIMARY KEY (DoctorID)
);
CREATE TABLE Medication
(
MedicationID INT NOT NULL,
BrandName VARCHAR(24) NOT NULL,
GenericName VARCHAR(24) NOT NULL,
PRIMARY KEY (MedicationID)
);
CREATE TABLE Prescription
(
PrescriptionID INT NOT NULL,
Date DATE NOT NULL,
MedicationID INT NOT NULL,
PatientID INT NOT NULL,
DoctorID INT NOT NULL,
FOREIGN KEY (MedicationID) REFERENCES Medication(MedicationID),
FOREIGN KEY (PatientID) REFERENCES PatientID(PatientID),
FOREIGN KEY (DoctorID) REFERENCES Doctor(DoctorID)
);
DB Population
INSERT INTO Patient (PatientID, Name, DOB) VALUES (‘1’, ‘Severus Snape’, date(‘1965-02-20’) ) ;
INSERT INTO Patient (PatientID, Name, DOB) VALUES (‘2’, ‘Albus Dumbledore’, date(‘1901-01-01’ ) ) ;
INSERT INTO Patient (PatientID, Name, DOB) VALUES (‘3’, ‘Dobby Elf’, date(‘1995-03-07’) ) ;
INSERT INTO Patient (PatientID, Name, DOB) VALUES (‘4’, ‘Albus-Severus Potter’, date(‘2010-03-12’) ) ;
INSERT INTO Doctor (DoctorID, Name, MedLicenseNumber, Phone) VALUES (‘1’, ‘Harry Potter’, ‘1234567’, ‘7707894561’ ) ;
INSERT INTO Doctor (DoctorID, Name, MedLicenseNumber, Phone) VALUES (‘2’, ‘Hermione Granger’, ‘7654321’, ‘7701234567’ ) ;
INSERT INTO Doctor (DoctorID, Name, MedLicenseNumber, Phone) VALUES (‘3’, ‘Ron Weasley’, ‘2345678’, ‘7707654321’ ) ;
INSERT INTO Doctor (DoctorID, Name, MedLicenseNumber, Phone) VALUES (‘4’, ‘Tom Riddle’, ‘3456789’, ‘7702345678’ ) ;
INSERT INTO Medication (MedicationID, BrandName, GenericName) VALUES (‘1’, ‘Antieviline’, ‘Goodness’ ) ;
INSERT INTO Medication (MedicationID, BrandName, GenericName) VALUES (‘2’, ‘Antigoodine’, ‘Evil’ ) ;
INSERT INTO Medication (MedicationID, BrandName, GenericName) VALUES (‘3’, ‘Antisadine’, ‘Happiness’ ) ;
INSERT INTO Medication (MedicationID, BrandName, GenericName) VALUES (‘4’, ‘Antihappine’, ‘Sadness’ ) ;
INSERT INTO Prescription (PrescriptionID, Date, MedicationID, PatientID, DoctorID) VALUES (‘1’, ‘2019-02-15’, ‘1’, ‘1’, ‘2’ ) ;
INSERT INTO Prescription (PrescriptionID, Date, MedicationID, PatientID, DoctorID) VALUES (‘2’, ‘2019-02-16’, ‘1’, ‘2’, ‘3’ ) ;
INSERT INTO Prescription (PrescriptionID, Date, MedicationID, PatientID, DoctorID) VALUES (‘3’, ‘2019-02-17’, ‘3’, ‘3’, ‘4’ ) ;
INSERT INTO Prescription (PrescriptionID, Date, MedicationID, PatientID, DoctorID) VALUES (‘4’, ‘2019-02-16’, ‘3’, ‘4’, ‘2’ ) ;
SQL Queries
Below is the query to retrieve a list of all doctors:
SELECT Name FROM Doctor ;
Below is the query to retrieve the number of patients who were born before 01/01/1994.
SELECT count (DOB)
FROM Patient
WHERE DOB > ‘1994-01-01’ ;
Below is the query to retrieve the patient information along with the ID of any prescribing doctor for each patient.
SELECT Name, DOB, DoctorID FROM Patient, Prescription
WHERE Patient.PatientID=Prescription.PatientID ;
Below is the query to retrieve the prescription information along with the patient name, DOB, medication brand name, and prescribing doctor name sorted by most recent date.
SELECT PrescriptionID, Date, Prescription.MedicationID, Prescription.PatientID, Prescription.DoctorID, Patient.Name, DOB, BrandName, Doctor.Name
FROM Prescription, Patient, Medication, Doctor
WHERE Patient.PatientID=Prescription.PatientID
AND Medication.MedicationID=Prescription.MedicationID
AND Prescription.DoctorID=Doctor.DoctorID
ORDER BY Date DESC ;
Below is the query to retrieve the most prescribed generic medication name.
SELECT GenericName,
COUNT(Prescription.MedicationID) AS value_occurrence
FROM Medication, Prescription
WHERE Medication.MedicationID=Prescription.MedicationID
GROUP BY Prescription.MedicationID
ORDER BY value_occurrence DESC
LIMIT 1;
References
Ben-Assuli, O. (2015). Electronic health records, adoption, quality of care, legal and privacy issues, and their implementation in emergency departments. Health Policy, 119(3), 287-297. Web.
Coronel, C., & Morris, S. (2019). Database systems: Design, implementation, and management. (13th ed.). Web.