Electronic Health Record

Andrew Bundy, Brian James, Jason Contreras, Robert Meis

California State University Monterey Bay

CST 363: Introduction to Database Systems

February 28, 2020

 Millipede Networks 2

Introduction

A client has asked Millipede Networks to develop a relational database to store

all relevant information for its drug store chain. The proposed database is named
“Electronic Health Record,” or “EHR” for short. After collecting information, the following
has been taken into consideration for the design of the database. For patients, the client
wants to store personally identifying information, such as their Social Security Number,
name, and age, along with their address and primary care physician. Doctors will have
their personally identifying information and relevant clinical information stored. This
includes their name, identifying Social Security Number, medical specialty, number of
patients, and their years of experience. Doctors prescribe medicine to patients using
prescriptions, which are associated with a quantity, date filled, and number of refills
when prescribed to a patient more than once. Every drug is identified by a trade name
and generic name by their corresponding pharmaceutical company. Pharmaceutical
companies will be identified by all necessary contact information including a name,
current address, and a phone number. Similar to pharmaceutical companies,
pharmacies will have contact information stored, including an address, name and phone
number. Pharmaceutical companies have long-term contracts with pharmacies. These
contracts possess text associated with the settlement, a start date, date of expiration,
and an appointed supervisor.

Major Findings (Database):

As our team began to create the conceptualized database, one of the first
problems that emerged was determining where to hold information in the instructions.
While several of the tables were clearly outlined in the instructions, it was determined
that it would be necessary to include additional tables in order to minimize repetition in
the data. The guiding principle in creating the database was simplicity, trying to
implement the cleanest, more compact version of the database to avoid redundancy
and improve performance. However, the initial design included more tables than were
necessary, leading to several columns appearing in multiple tables. It was interesting to
find the right balance between including more tables to hold data and avoiding repeating
columns unnecessarily. Having more tables minimized the number of columns in each
one but created repeat data that could be removed by merging tables together. Upon
reevaluation, a couple of the tables were removed, and their columns redistributed to
other tables, which cut down on having the same columns appear unnecessarily in
multiple tables.
In the process of developing the actual ER diagram using the MySQL
Workbench, our team learned about identifying and non-identifying relationships, a topic
not covered by Murach’s MySQL book. It was necessary to research this topic to ensure
 Millipede Networks 3
the relational lines in the diagram accurately represented the relationships between
tables.

Major Findings (Servlets):

A substantial amount of learning took place while building the servlets, and some
interesting issues were encountered at each step. For example, one servlet involves
building a page where a patient requests a refill using a prescription_id, and then is
taken to another HTML page where the price of the prescription is displayed. From
there, the patient either chooses to fill the order, at which point the database is updated,
or cancel the order.
There were two main issues encountered for this servlet. First, more than one
action needs to occur within this servlet path. This was eventually solved by creating
multiple servlets to handle each HTML form action depending on the user’s choice.
The second issue that was encountered was with how to get the prescription_id
data from one servlet to another. Several options were tried, including attaching it to an
HTML form and exploring Java ActionListeners. Eventually, a solution was found using
an HTTP Session and the setAttribute() and getAttribute() methods to send the variable
data between servlets.
The problem statement for the servlet displaying the list of patient prescriptions
described a real-life situation and application for this servlet, a JOIN was needed to
include the trade name of the drug along with all the other patient prescription information
that should be included. The result set will yield a table of prescriptions that is printed to
a table and is displayed on the user’s screen in HTML results.
After the user gets the view of their meds, then the next option left for them should
be to decide whether to refill a prescription. Additional research was done to explore
more techniques for passing data between servlets and html pages. Several attempts
were made to do this, including trying to send an integer to the prescription refill
form. Eventually, a way to create a refill button on the results page was realized. After
the results table is rendered, two out.println statements that included the tags for HTML
form action and a submit-type button were added. When clicked, that button sends the
user to the Prescription_refill_form. We decided to allow for the user to see multiple
prices of the drug from their prescription from different pharmacies to help them save
money, as prices could be much higher in between pharmacies for the same drug.
The fourth servlet is where a pharmacy manager requests a report containing the
name and quantity of drugs used within the last month. This was done by having the
pharmacy manager request the report by entering the corresponding pharmacy id.
When building this servlet there had been one minor issue. When attempting to
print out query results of SUM(quantity), “quantity” was solely used in the print statement
and the output was coming out empty. Changing “quantity” to “SUM(quantity)” fixed this
issue.” This a simple but important reminder that when printing query results that the
portion in quotations must mirror exactly what the query statement says when doing
calculations.
 Millipede Networks 4
The instructions for the fifth servlet asked to show all of the drugs each doctor
prescribed over a six-month period. In order to accomplish this, a multi-table JOIN
between doctor, drug, and prescription tables was used in order to get the requisite
information: the doctor’s name, the name of the drug, and the amount of the drug the
doctor prescribed over that period of time. This required using SUM(quantity) for the
prescription, and grouping the prescribed amount of medication by doctor and drug
name. This led to repetition of doctors’ names, since doctors prescribed multiple types of
medication. Since the FDA officials using this servlet appear more interested in the
amount of drugs, rather than the people prescribing them, the data was sorted by
quantity prescribed to show which doctors and medication combinations prescribe d the
most drugs.
Similar to servlet 4, a problem occurred in servlet 5 when trying to display the
results of the servlet’s SQL query. Originally, the results were ordered by “quantity”
instead of “SUM(quantity).” This issue was quickly fixed to show the correct result. This
problem was sorted out in MySQL Workbench prior to including the SQL query in the
servlet. Creating, testing, verifying, and editing the query in MySQL Workbench helped to
ensure that the servlet query would be correct even before it was used in the servlet.
This made it easier to work on the servlet, as trying to work out problems with the servlet
code on top of a faulty SQL query would make locating the bugs even more difficult than
trying to debug the program with the correct query.
 Millipede Networks 5
Java Servlet Images

Patient Portal Home Page 1:

Patient Portal Home Page 2:

 Millipede Networks 6
Physician Prescription Interface 1:

Physician Prescription Interface 2:

 Millipede Networks 7
Fill Prescription 1:

Fill Prescription 2:
 Millipede Networks 8
Order Filled:

Order Canceled:
 Millipede Networks 9
Monthly Report 1:

Monthly Report 2:

FDA Page:
 Millipede Networks 10
FDA Page 2:
 Millipede Networks 11
ER Diagram
 Millipede Networks 12
ER Diagram Description

This EHR database was designed using entity relationship modelling to represent
the data tables and the cardinality between them. The problem specification sheet
provided gave an indication of which data types to use and where to place them in the
design. Once the data elements were identified, the information was then subdivided
into smaller elements and placed into appropriate table columns on the graphical model.
Tables were used to represent the different entities described by the database,
including Patient, Doctor, Prescription, Drug, Pharmacy, Pharmacy_Inventory,
Pharmaceutical_Company, and Contract. The relationships between the tables of the
conceptual model are varied. If a many-to-many relationship was discovered between
entities, then a linking table was created between them. Most tables use a one-to-many
relationship. The doctor table has two one-to-many relationships with patient and
prescription tables. The prescription table is a linking table that has four one-to-many
relationships running through it. The contract table and pharmacy inventory both have a
many-to-many relationship with respect to their connected tables.
Several tables do not use foreign keys: Doctor, Pharmacy, and Pharmaceutical
company. These tables all use their primary keys as indexes. The rest of the tables use
both foreign keys and indexes. The Patient table uses primary_physician_ssn
(doctor_ssn from the Doctor table) as a non-identifying foreign key and its primary key is
patient_ssn. The Prescription table uses a composite key that consists of the identifying
foreign keys patient_ssn (Patient) and doctor_ssn (Doctor) in conjunction with a non-
unique primary key. In addition, drug_id (Drug) and pharmacy_id (Pharmacy) are non-
identifying foreign keys in the Prescription table. The Drug table uses drug_id as its
primary key and pharma_comp_id (Pharmaceutical_Company) is an identifying foreign
key. The Drug_Price table uses drug_id (Drug) as an identifying foreign key that forms a
composite key with its other foreign key, pharmacy_id (Pharmacy). The last table,
Contract, uses pharmacy_id (Pharmacy) and pharma_comp_id
(Pharmaceutical_Company) as identifying foreign keys, in conjunction with its own non-
unique primary key, contract_id to form a composite key.
Most of the design issues for the database took the form of determining the right
balance of tables and columns to minimize repetition and improve performance.
Extraneous tables were added to simplify and reduce the number of columns in each
table. However, this led to issues with redundancy that were rectified by merging
information from tables. For example, a Price_Table was initially used to hold the prices
of each drug in Pharmacy_Inventory. This led to the repetition of several columns,
including pharmacy_id, drug_id, and inventory_id. By merging the prices from
Price_Table into Pharmacy_Inventory, those columns were no longer repeated
unnecessarily and the database was further streamlined. After Pharmacy_Inventory was
created, it was determined an even simpler version of the table could be created,
Drug_Price. It uses only the pharmacy_id, drug_id, and drug price to store information
 Millipede Networks 13
about the cost of each drug at different pharmacies. Several of the other tables were
simplified to comply with the instructions and remove unnecessary information, such as
a second address.