CST363 Project 1

Small Pharma Data Network (SPDN)

May 16, 2023

Anthony Matricia
Haris Jilani
Introduction:
A drug store chain is an intricate web containing connections that may be directly or indirectly
linked. From the drug itself to the pharmaceutical companies that are responsible for it, there are
many different actors that play a part in the process of a drug’s prescription. The relational
database will serve to store essential data pertaining to this web of information, which includes,
but is not limited to, the pharmaceutical companies, pharmacies, patients, doctors, prescriptions,
and the drug itself in an organized and effective fashion. Without the database, specific
information desired by the user would take longer, as the program would have to iterate large
quantities of data one by one. The different actors that play a part when it comes to the process of
prescribing a drug also adds a complexity that would otherwise be extremely difficult to handle.

The actors that play a role in this intricate process are the following (in no particular order):
patients, doctors, pharmaceutical companies, pharmacies, prescriptions, contracts, and
supervisors. A patient is uniquely identified by an SSN, having a name, age, and address, all of
which are key in determining the patient. The patient can only have one doctor that serves as
their primary physician. A doctor is uniquely identified by their SSN, having a name, specialty,
and years of experience. However, a doctor is not required to have a specialty. The doctor is
licensed to prescribe drugs to any patient that is in need of a prescription. The patient can have
multiple prescriptions from several different doctors. The prescription itself is uniquely identified
by a RX number that is only applicable for one drug, written up by a doctor for a specific patient.
In addition, the prescription has a date, quantity, a generic name, and/or trade name.

A drug is classified by its formula name and its optional trade name. The drug is manufactured
by a pharmaceutical company, which is classified by its name and phone number. If the
prescription drug has a trade name, then the drug is specific to a pharmaceutical company.
Otherwise if the prescription has a generic name, then any drug with that name can be used from
the pharmaceutical company of choice. After manufacturing the drug, the pharmaceutical
company sells it to a pharmacy. The pharmacy has a name, address, and phone number, selling a
variety of drugs. If the prescription is filled, it must identify the pharmacy that filled it and on
what date. Filled prescriptions with generic names must use the pharmaceutical company instead
of the pharmacy.

The pharmaceutical company and the pharmacy are bound by a long-term contract that has a
start and end date, as well as the terms and conditions of the agreement. A pharmaceutical
company can be contractually binded to several different pharmacies and vice versa. The
contract requires a supervisor that is appointed by the pharmacy. The supervisor for a contract
can change, however the contract can only have one supervisor at a time. The supervisor is open
to supervising more than one contract, including more than one at a time.

—------------------------------------------------------------------------------------------------------------------
ER Model:
—------------------------------------------------------------------------------------------------------------------

Relational Schema (project1part1.sql):

CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET utf8 ;

CREATE TABLE IF NOT EXISTS `doctor` (

`doctorid` INT NOT NULL AUTO_INCREMENT,
`name` VARCHAR(45) NOT NULL,
`startdate` DATETIME NOT NULL,
`specialty` VARCHAR(45) NULL,
PRIMARY KEY (`doctorid`))
ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS `patient` (

`patientid` INT NOT NULL AUTO_INCREMENT,
`name` VARCHAR(45) NOT NULL,
`birthday` DATE NOT NULL,
`address` VARCHAR(45) NOT NULL,
`doctorid` INT NOT NULL,
PRIMARY KEY (`patientid`),
INDEX `fk_patient_doctor1_idx` (`doctorid` ASC) VISIBLE,
CONSTRAINT `fk_patient_doctor1`
FOREIGN KEY (`doctorid`)
REFERENCES `doctor` (`doctorid`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS `pharmaceutical_company` (

`pharmaceuticalName` VARCHAR(45) NOT NULL,
`phone` VARCHAR(45) NOT NULL,
`price` DECIMAL(10,2) NOT NULL,
PRIMARY KEY (`pharmaceuticalName`))
ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS `drug` (

`formula` VARCHAR(45) NOT NULL,
`tradename` VARCHAR(45) NULL,
 PRIMARY KEY (`formula`))
ENGINE = InnoDB;
CREATE TABLE IF NOT EXISTS `pharmacy` (
`pharmacyName` VARCHAR(45) NOT NULL,
`address` VARCHAR(45) NOT NULL,
`phone` VARCHAR(45) NOT NULL,
`price` DECIMAL(10,2) NOT NULL,
PRIMARY KEY (`pharmacyName`))
ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS `prescription` (

`prescriptionid` VARCHAR(45) NOT NULL,
`pharmacyName` VARCHAR(45) NOT NULL,
`date` DATETIME NOT NULL,
`quantity` INT NOT NULL,
`doctorid` INT NOT NULL,
`patientid` INT NOT NULL,
`formula` VARCHAR(45) NOT NULL,
PRIMARY KEY (`prescriptionid`),
INDEX `fk_prescription_doctor1_idx` (`doctorid` ASC) VISIBLE,
INDEX `fk_prescription_patient1_idx` (`patientid` ASC) VISIBLE,
INDEX `fk_prescription_drug1_idx` (`formula` ASC) VISIBLE,
CONSTRAINT `fk_prescription_doctor1`
FOREIGN KEY (`doctorid`)
REFERENCES `doctor` (`doctorid`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_prescription_patient1`
FOREIGN KEY (`patientid`)
REFERENCES `patient` (`patientid`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_prescription_drug1`
FOREIGN KEY (`formula`)
REFERENCES `drug` (`formula`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS `supervisor` (

 `supervisorid` INT NOT NULL,
`name` VARCHAR(45) NOT NULL,
`phone` VARCHAR(45) NOT NULL,
PRIMARY KEY (`supervisorid`))
ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS `contract` (

`contractid` INT NOT NULL,
`start` DATE NOT NULL,
`end` DATE NOT NULL,
, NOT NULL,
`text` VARCHAR(500) NOT NULL,
`pharmaceuticalName` VARCHAR(45) NOT NULL,
`pharmacyName` VARCHAR(45) NOT NULL,
`supervisorid` INT NOT NULL,
PRIMARY KEY (`contractid`),
INDEX `fk_pharmaceutical_company_has_pharmacy_pharmacy1_idx` (`pharmacyName`
ASC) VISIBLE,
INDEX `fk_pharmaceutical_company_has_pharmacy_pharmaceutical_compa_idx`
(`pharmaceuticalName` ASC) VISIBLE,
INDEX `fk_contract_supervisor1_idx` (`supervisorid` ASC) VISIBLE,
CONSTRAINT `fk_pharmaceutical_company_has_pharmacy_pharmaceutical_company1`
FOREIGN KEY (`pharmaceuticalName`)
REFERENCES `pharmaceutical_company` (`pharmaceuticalName`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_pharmaceutical_company_has_pharmacy_pharmacy1`
FOREIGN KEY (`pharmacyName`)
REFERENCES `pharmacy` (`pharmacyName`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_contract_supervisor1`
FOREIGN KEY (`supervisorid`)
REFERENCES `supervisor` (`supervisorid`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS `drug_has_pharmacy` (

`formula` VARCHAR(45) NOT NULL,
 `pharmacyName` VARCHAR(45) NOT NULL,
`price` DECIMAL(10,2) NOT NULL,
`quantity` INT NOT NULL,
PRIMARY KEY (`formula`, `pharmacyName`),
INDEX `fk_drug_has_pharmacy_pharmacy1_idx` (`pharmacyName` ASC) VISIBLE,
INDEX `fk_drug_has_pharmacy_drug1_idx` (`formula` ASC) VISIBLE,
CONSTRAINT `fk_drug_has_pharmacy_drug1`
FOREIGN KEY (`formula`)
REFERENCES `drug` (`formula`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_drug_has_pharmacy_pharmacy1`
FOREIGN KEY (`pharmacyName`)
REFERENCES `pharmacy` (`pharmacyName`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS `drug_has_pharmaceutical` (

`formula` VARCHAR(45) NOT NULL,
`pharmaceuticalName` VARCHAR(45) NOT NULL,
`price` DECIMAL(10,2) NOT NULL,
`quantity` INT NOT NULL,
PRIMARY KEY (`formula`, `pharmaceuticalName`),
INDEX `fk_drug_has_pharmaceutical_company_pharmaceutical_company1_idx`
(`pharmaceuticalName` ASC) VISIBLE,
INDEX `fk_drug_has_pharmaceutical_company_drug1_idx` (`formula` ASC) VISIBLE,
CONSTRAINT `fk_drug_has_pharmaceutical_company_drug1`
FOREIGN KEY (`formula`)
REFERENCES `drug` (`formula`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_drug_has_pharmaceutical_company_pharmaceutical_company1`
FOREIGN KEY (`pharmaceuticalName`)
REFERENCES `pharmaceutical_company` (`pharmaceuticalName`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS `filled_prescription` (

 `filled_prescriptionid` INT NOT NULL,
`pharmaceuticalName` VARCHAR(45) NOT NULL,
`prescriptionid` VARCHAR(45) NOT NULL,
PRIMARY KEY (`filled_prescriptionid`),
INDEX `fk_filled_prescription_pharmaceutical_company1_idx` (`pharmaceuticalName` ASC)
VISIBLE,
INDEX `fk_filled_prescription_prescription1_idx` (`prescriptionid` ASC) VISIBLE,
CONSTRAINT `fk_filled_prescription_pharmaceutical_company1`
FOREIGN KEY (`pharmaceuticalName`)
REFERENCES `pharmaceutical_company` (`pharmaceuticalName`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_filled_prescription_prescription1`
FOREIGN KEY (`prescriptionid`)
REFERENCES `prescription` (`prescriptionid`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;

select p.name Patient, sum(pr.quantity) TotalPrescriptions from patient p, prescription pr where

p.patientid = pr.patientid group by p.name having sum(pr.quantity) > (select
sum(quantity)/count(quantity) from prescription);

select dhphc.pharmaceuticalName, dhphc.formula, dhphc.price, dhphc.quantity from

drug_has_pharmaceutical dhphc where dhphc.quantity in (select max(quantity) from
drug_has_pharmaceutical group by pharmaceuticalName) order by pharmaceuticalName;

select fpr.filled_prescriptionid, p.name Patient, pr.formula, pr.date DateFilled, pr.pharmacyName

Pharmacy, d.name Doctor, min(dhph.price) from filled_prescription fpr, prescription pr, doctor d,
patient p, drug_has_pharmacy dhph where pr.prescriptionid = fpr.prescriptionid && d.doctorid
= pr.doctorid && p.patientid = pr.patientid && dhph.formula = pr.formula group by p.name,
fpr.filled_prescriptionid order by fpr.filled_prescriptionid;

select p.name, max(pr.formula) mostPrescribedDrug, p.birthday birthDate from patient p,

prescription pr where pr.patientid = p.patientid && p.birthday >= '2000-01-01' group by
p.name, p.birthday order by p.name;

select pharmacyName, round(sum(price * quantity)/count(*), 2) averagePrice, min(price)

minPrice, max(price) maxPrice from drug_has_pharmacy group by pharmacyName;
—------------------------------------------------------------------------------------------------------------------

Check Constraints Documentation:

The general usages of check constraints in this code may include:

1. Data Validation: Check constraints can be used to ensure that the data being stored in a column
meets certain criteria or follows specific rules.

2. Referential Integrity: Check constraints can be used to enforce referential integrity between
tables. It can ensure that a foreign key column references an existing primary key value in
another table.

The specific implementations of check constraints in this code may include:

1. doctor Table:
Constraint on the startdate column to ensure that the date is not in the future.
Constraint on the specialty column to restrict the allowed values to a predefined list of
specialties.

2. patient Table:
Constraint on the birthday column to ensure that the date is not in the future.

3. pharmaceutical_company Table:
Constraint on the phone column to enforce a specific format or validation rule for phone
numbers.
Constraint on the price column to ensure that the price is a positive decimal number greater than
0.00.

4. drug Table:
Constraint on the formula column to enforce a specific format and/or validation rule.

5. pharmacy Table:
Constraint on the phone column to enforce a specific format or validation rule for phone
numbers.
Constraint on the price column to ensure that the price is a positive decimal number greater than
0.00.

6. prescription Table:
Constraint on the date column to ensure that the date is not in the future.
7. contract Table:
Constraint on the start and end columns to ensure that the start date is earlier than the end date.
Constraint on the start column to ensure it is not in the future.
Constraint on the end column to ensure it is not in the past.

—------------------------------------------------------------------------------------------------------------------
Normalization Documentation:
I believe the project is currently normalized up to at least 2NF-3NF. An analysis of each table is
below:

1. doctor table:
The table has a primary key doctorid, which is appropriate.

2. patient table:
The table has a primary key patientid, which is appropriate.
The doctorid column references the primary key of the doctor table, establishing a foreign key
relationship.
This relationship ensures referential integrity between the patient and doctor tables.

3. pharmaceutical_company table:
The table has a primary key pharmaceuticalName, which is appropriate.

4. drug table:
The table has a primary key formula, which is appropriate.

5. pharmacy table:
The table has a primary key pharmacyName, which is appropriate.

6. prescription table:
The table has primary key prescriptionid, which is appropriate.
The doctorid, patientid, and formula columns reference the primary keys of the doctor, patient,
and drug tables, respectively, establishing foreign key relationships.
These relationships ensure referential integrity between the prescription, doctor, patient, and drug
tables.

7. supervisor table:
The table has a primary key supervisorid, which is appropriate.

8. contract table:
The table has a primary key contractid, which is appropriate.
The pharmaceuticalName, pharmacyName, and supervisorid columns reference the primary keys
of the pharmaceutical_company, pharmacy, and supervisor tables, respectively, establishing
foreign key relationships.
These relationships ensure referential integrity between the contract, pharmaceutical_company,
pharmacy, and supervisor tables.

9. drug_has_pharmacy table:
The table has a composite primary key consisting of formula and pharmacyName, which is
appropriate for a many-to-many relationship.
The formula and pharmacyName columns reference the primary keys of the drug and pharmacy
tables, respectively, establishing foreign key relationships.
These relationships ensure referential integrity between the drug_has_pharmacy, drug, and
pharmacy tables.

10. drug_has_pharmaceutical table:

The table has a composite primary key consisting of formula and pharmaceuticalName, which is
appropriate for a many-to-many relationship.
The formula and pharmaceuticalName columns reference the primary keys of the drug and
pharmaceutical_company tables, respectively, establishing foreign key relationships.
These relationships ensure referential integrity between the drug_has_pharmaceutical, drug, and
pharmaceutical_company tables.

11. filled_prescription table:

The pharmaceuticalName and prescriptionid columns reference the primary keys of the
pharmaceutical_company and prescription tables, respectively, establishing foreign key
relationships.
These relationships ensure referential integrity between the filled_prescription,
pharmaceutical_company, and prescription tables.

—------------------------------------------------------------------------------------------------------------------
Conclusion:
Haris: After multiple readings of the directions, I was able to translate client requirements into
database terms that aided in the creation of the ER diagram. In turn, I was able to reword and
restate the client requirements having translated those requirements into terms where I could
piece everything together. I learned how collaboration, communication, and planning are
necessary behind the design and ultimately the creation of a database. The relationships between
multiple entities in the ER diagram signified how complex a database can be, as well as
highlighting the importance of an ER diagram in planning a database.
Anthony: Throughout part 1 of this project, we were able to create an Entity Relationship
diagram, Relational Schema, and all the necessary components for the foundation of our
preliminary report. The entity relationship diagram was a great way for me to visualize how the
data works. By sorting attributes into tables, and creating 1:1, 1:n, and n:n relationships, I was
able to see how all of the data works together. It was very beneficial to learn the process of
creating an ERD as well. Being able to visualize everything gave me a clear understanding of
database structure and how different entities are interconnected. Then, through forward
engineering, we created various tables representing different entities such as doctor, patient,
pharmaceutical_company, drug, pharmacy, and more. We defined relationships between these
tables using primary keys and foreign keys, capturing the associations between entities. In this
schema, we utilized data types such as INT, VARCHAR, DECIMAL, DATE, and DATETIME to
represent different attributes. We also incorporated constraints such as NOT NULL and foreign
key constraints to enforce data validity and maintain referential integrity. Lastly, the schema
demonstrated the use of constraints, such as primary key constraints to ensure uniqueness,
foreign key constraints to maintain referential integrity, and check constraints to enforce value
limits.

We also learned a lot through building, changing, and improving upon our database materials.
For example, changing the 'age' and 'years of experience' attributes from #'s of years to dates was
an important change to make because a number of years needs to be updated once per year, but a
date remains constant throughout all of time. Another change we made was making n:n
relationships between the drug entity and the pharmacy & pharmaceutical_company entities. By
doing this, we created an association table that is able to first check if the
pharmacy/pharmaceutical company offers the drug, and then it can also store the price and
quantity of the drug as well. This is important because it functions as a "middle man" that can
handle the varying prices and quantities of the drugs. If we stored price and quantity in the drug
or pharmacy entity, then we would not be able to handle different pharmacies/pharmaceutical
companies having different prices for the same drug or different drugs being priced differently at
the same pharmacy/pharmaceutical company.

Overall, the process of forward engineering the schema and creating an ERD has enhanced our
understanding of database design principles, relationships between entities, and the importance
of data integrity through data types and constraints. This hands-on experience contributes to our
growth and proficiency in SQL.