Electronic Medical Record

COP 4710 Database Systems - Final Report

Group 14: Brandon Berk, James Cary, Andrew Watson, Kyle McCleary

Contents
1

Background and Real-World Scenario .................................................................................................. 2

Modeling the Electronic Medical Record (EMR) ................................................................................... 2

2.1

Business Rules ............................................................................................................................... 3

2.2

Entity Relationship Diagram (ERD) ................................................................................................ 4

2.3

Domains ........................................................................................................................................ 4

2.4

Relational Tables ........................................................................................................................... 5

The EMR Database ................................................................................................................................ 6

3.1

Database Scripts............................................................................................................................ 7

3.2

Database Usage........................................................................................................................... 10

Feedback and Commentary ................................................................................................................ 14

1 Background and Real-World Scenario

There exists a need for an Electronic Medical Record (EMR) solution at a real hospital in Melbourne, FL,
but unfortunately existing EMR software is expensive and very general in application. Doctor and office
staff need to track patient medical information like patient name and contact info, primary physician, and
various records of individual visits. These medical records contain qualitative information like doctors
notes and quantitative information like patient vitals.
In order to combat this need, the EMR system created by Group 14 features a Java GUI application with a
database back-end. The tailored application is able to model everyday relations that the hospital staff
encountersenabling them to easily keep track of their patients and referrals across visits. The Java GUI
is shown below in Figure 1.

Figure 1 - EMR Java Graphical User Interface (GUI)

2 Modeling the Electronic Medical Record (EMR)

In modeling the real-world scenario discussed above, it is pertinent to consider the logically related
entities described below in Table 1 - Conceptual Entities.
Entity
User
Patient

Description
Medical staff that directly uses the EMR such as Doctor, Secretary, or Medical Assistant.
The client being treated, tested, or otherwise handled by the User(s) during a Visit.

Visit

A single-instance patient visit to the hospital, either by appointment or walk-in. Information

regarding the patient Visit is logged by a single User in a single Record.

Record

Digitally recorded information regarding a single patient Visit. Includes notes and metrics like
patient vitals.

Referral

An optional component of a given record. If needed, the Doctor may refer the patient to an
outside business for a therapy, lab, x-ray, or other medical service.

Table 1 - Conceptual Entities

2.1 Business Rules

In modeling the EMR, a conversion of high-level descriptions of the conceptual entities to formal business
rules proves useful. Listed below are the various interactions and roles for each entity, made as thorough
as possible as an attempt is made to convert these real-world ideas and interactions to a database
implementation.

2.1.1 User

A user can be uniquely identified by its id

A user has a composite name attribute (last, first)
A user has a password
A user can be a Medical Assistant, Medical Doctor, or Secretary
A single user may document many visits daily

2.1.2 Visit

A visit can be uniquely identified by its id

A visit has a date/time
A visit may or may not be a scheduled appointment
A visit references the appropriate staff, patient, and record

2.1.3 Patient

A patient can be uniquely identified by its id

A patient has a composite name attribute (last, first)
A patient has a Date of Birth
A patient has contact phone numbers (home and cell)
A patient references the last visit and primary doctor

2.1.4 Record

A record can be uniquely identified by his or her id

A record contains the reason for the days visit
A record contains the physicians notes
A record contains a set of vitals taken from the patient during the visit
A record references a visit and logs optional many referrals
A record has vital attributes: weight, height, BMI, heart rate, blood pressure, pulse oxidation,
temperature

2.1.5 Referral

A referral can be uniquely identified by its id

A referral is for a target business
A referral contains the physicians notes

A referral contains a set of vitals taken from the patient during the visit
A referral references a record
A referral can be (disjoint) types: Lab, X-Ray, or Therapy

2.2 Entity Relationship Diagram (ERD)

In transitioning from a conceptual model to a database-implementable model the EMR, the creation of
an Entity Relation Diagram (ERD) was helpful and relevant. The ERD produced is shown below as Figure 2
- EMR Entity Relationship Diagram (ERD). Note that the subclass sections boxed in gray are
unimplemented in the final database, as they are just placeholders for future functionality.

Figure 2 - EMR Entity Relationship Diagram (ERD)

2.3 Domains
The domains for every attribute in the EMR model are shown below, categorized by data type.
Establishing and adhering to well-defined attribute domains is crucial to a well-constructed database with
domain integrity.

2.3.1 Keys
All key attributes below were implemented at the database level using unique integer objects.
User: id, secretary_id [fk], ma_id [fk], doc_id [fk]

Visit/Appointment: id, staff [fk], patient [fk], record [fk]

Patient: id, lastVisit [fk], PrimaryDoctor [fk],
Record: id
Referral: id, record [fk], secretary_id [fk], doc_id [fk], ma_id [fk]

2.3.2 Decimals
All decimal attributes below were implemented at the database level using float objects.
Record: weight, height, BMI, heartRate, bloodPressure, pulseOxidation, temperature

2.3.3 Text
All text attributes below were implemented at the database level using varchar objects.
User: name (Last), name (First), password, userType
Patient: name (Last), name (First), phone (home), phone (cell)
Record: reason, physicianNotes
Referral: businessName, referralType

2.3.4 Date and Time

All date and time attributes below were implemented at the database level using datetime objects.
Patient: DOB
Visit/Appointment: dateTime
Referral: date

2.3.5 True/False Flags

All flag attributes below were implemented at the database level using boolean objects.
Visit/Appointment: isAppointment

2.4 Relational Tables

Given a well-constructed set of business rules and complimentary entity relationship diagram, the group
was able to construct a set of relational tables that apply to the real-world EMR scenario. The resultant
tables are shown below in Figure 3 - Relational Tables.

Figure 3 - Relational Tables

2.4.1 Normalization
All tables in Figure 3 satisfy 2NF in that every tables non-key attributes are fully functionally dependent
on the tables primary key. All tables satisfy 3NF in that they satisfy 2NF and contain no transitive
dependencies.

2.4.2 Integrity Constraints

In ensuring the quality of the relational tables in Figure 3, various integrity constraints must be considered.
All tables in Figure 3 satisfy Entity Integrity in that each has a unique, non-null primary key. All tables in
Figure 3 satisfy Referential Integrity in that all foreign keys are non-null, and reference the primary key of
another table. All tables in Figure 3 satisfy Domain Integrity in that that they adhere to the established
domains. There are no additional User-Defined Integrity rules.

3 The EMR Database

In constructing the database back-end for the EMR application, the group used mySQL Workbench to
model tables based on ER Diagram. Physical schemas were established in Workbench using the relational
schema and domains already established. Database creation scripts were generated using Workbench
while population, querying and update scripts were hand-coded.

3.1 Database Scripts

Copied below for reference and convenience are example database creation, population, and
querying/update scripts. The data used in these scripts is falsified, fictitious and manufactured for
example purposes only and does not represent any real people or medical data.

3.1.1 Database Creation Script

-- EMR DB creation script
-- Group 14 - Brandon Berk, James Cary, Andrew Watson, Kyle McCleary
SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';
-- Main DB schema
DROP SCHEMA IF EXISTS `EMR` ;
CREATE SCHEMA IF NOT EXISTS `EMR` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci ;
USE `EMR` ;
-- Safely create User table
DROP TABLE IF EXISTS `EMR`.`User` ;
CREATE TABLE IF NOT EXISTS `EMR`.`User` (
`idUser` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`lName` VARCHAR(45) NULL,
`fName` VARCHAR(45) NULL,
`password` VARCHAR(45) NULL,
`userType` VARCHAR(45) NULL,
PRIMARY KEY (`idUser`),
UNIQUE INDEX `idUser_UNIQUE` (`idUser` ASC)
)
ENGINE = InnoDB;
-- Safely create Patient table
DROP TABLE IF EXISTS `EMR`.`Patient` ;
CREATE TABLE IF NOT EXISTS `EMR`.`Patient` (
`idPatient` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`lName` VARCHAR(45) NULL,
`fName` VARCHAR(45) NULL,
`dob` DATE NULL,
`phoneHome` VARCHAR(45) NULL,
`phoneCell` VARCHAR(45) NULL,
`fkLastVisit` INT UNSIGNED NULL,
`fkPrimaryDoc` INT UNSIGNED NULL,
PRIMARY KEY (`idPatient`),
UNIQUE INDEX `idpatient_UNIQUE` (`idPatient` ASC),
INDEX `lastVisit_idx` (`fkLastVisit` ASC),
CONSTRAINT `primaryDoc`
FOREIGN KEY (`fkPrimaryDoc`)
REFERENCES `EMR`.`User` (`idUser`)
ON DELETE CASCADE
ON UPDATE CASCADE,
CONSTRAINT `lastVisit`
FOREIGN KEY (`fkLastVisit`)
REFERENCES `EMR`.`Visit` (`idVisit`)
ON DELETE CASCADE
ON UPDATE CASCADE
)
ENGINE = InnoDB;
-- Safely create Record table
DROP TABLE IF EXISTS `EMR`.`Record` ;
CREATE TABLE IF NOT EXISTS `EMR`.`Record` (
`idRecord` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`reason` BLOB NULL,
`notes` BLOB NULL,
`weight` FLOAT NULL,
`height` FLOAT NULL,
`bmi` FLOAT NULL,
`hr` FLOAT NULL,
`bp` FLOAT NULL,
`pulseOx` FLOAT NULL,
`temp` FLOAT NULL,
`fkVisit` INT UNSIGNED NULL,
PRIMARY KEY (`idRecord`),
UNIQUE INDEX `idRecord_UNIQUE` (`idRecord` ASC),
CONSTRAINT `recordVisit`
FOREIGN KEY (`fkVisit`)
REFERENCES `EMR`.`Visit` (`idVisit`)
ON DELETE CASCADE
ON UPDATE CASCADE
)
ENGINE = InnoDB;
-- Safely create Visit table
DROP TABLE IF EXISTS `EMR`.`Visit`;
CREATE TABLE IF NOT EXISTS `EMR`.`Visit` (
`idVisit` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`dt` DATETIME NULL,
`isAppt` TINYINT(1) NULL,
`fkStaff` INT UNSIGNED NULL,

`fkPatient` INT UNSIGNED NULL,

`fkRecord` INT UNSIGNED NULL,
PRIMARY KEY (`idVisit`),
UNIQUE INDEX `idVisit_UNIQUE` (`idVisit` ASC),
CONSTRAINT `visitStaff`
FOREIGN KEY (`fkStaff`)
REFERENCES `EMR`.`User` (`idUser`)
ON DELETE CASCADE
ON UPDATE CASCADE,
CONSTRAINT `visitPatient`
FOREIGN KEY (`fkPatient`)
REFERENCES `EMR`.`Patient` (`idPatient`)
ON DELETE CASCADE
ON UPDATE CASCADE,
CONSTRAINT `visitRecord`
FOREIGN KEY (`fkRecord`)
REFERENCES `EMR`.`Record` (`idRecord`)
ON DELETE CASCADE
ON UPDATE CASCADE
)
ENGINE = InnoDB;
-- Safely create Referral table
DROP TABLE IF EXISTS `EMR`.`Referral` ;
CREATE TABLE IF NOT EXISTS `EMR`.`Referral` (
`idReferral` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`dt` DATETIME NULL,
`businessName` VARCHAR(45) NULL,
`referralType` VARCHAR(45) NULL,
`fkVisit` INT UNSIGNED NULL,
PRIMARY KEY (`idReferral`),
UNIQUE INDEX `idReferral_UNIQUE` (`idReferral` ASC),
INDEX `referralVisit_idx` (`fkVisit` ASC),
CONSTRAINT `referralVisit`
FOREIGN KEY (`fkVisit`)
REFERENCES `EMR`.`Visit` (`idVisit`)
ON DELETE CASCADE
ON UPDATE CASCADE
)
ENGINE = InnoDB;
SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

3.1.2 Database Population Script

use EMR;
-- Insert one user of each userType
INSERT INTO User
(fName, lName, password, userType)
VALUES
("Phil", "McGraw", "LexiRoxMySox", "Doctor");
INSERT INTO User
(fName, lName, password, userType)
VALUES
("Snookie", "Snook", "Snooks", "Secretary");
INSERT INTO User
(fName, lName, password, userType)
VALUES
("Lexi", "Cox", "Skimpy", "MedicalAssistant");
-- Insert four patients
INSERT INTO Patient
(fName, lName, dob, phoneHome, phoneCell, fkPrimaryDoc)
VALUES
("Brandon", "Berk", '1992-11-02', "5556155538", "5558675309",
(SELECT idUser FROM User WHERE fName='Phil' AND lName='McGraw' AND userType='Doctor'));
INSERT INTO Patient
(fName, lName, dob, phoneHome, phoneCell, fkPrimaryDoc)
VALUES
("James", "Cary", '1991-09-09', "3213213215", "6546546545",
(SELECT idUser FROM User WHERE fName='Phil' AND lName='McGraw' AND userType='Doctor'));
INSERT INTO Patient
(fName, lName, dob, phoneHome, phoneCell, fkPrimaryDoc)
VALUES
("Andrew", "Watson", '1991-06-26', "9879879877", "7897897899",
(SELECT idUser FROM User WHERE fName='Phil' AND lName='McGraw' AND userType='Doctor'));
INSERT INTO Patient
(fName, lName, dob, phoneHome, phoneCell, fkPrimaryDoc)
VALUES
("Kyle", "McCleary", '1991-06-26', "1233211233", "4566546544",
(SELECT idUser FROM User WHERE fName='Phil' AND lName='McGraw' AND userType='Doctor'));
-- Insert at least one visit per patient. Kyle has two visits.
INSERT INTO Visit
(dt, isAppt, fkStaff, fkPatient)
VALUES
('2014-11-18 20:27:30', TRUE,
(SELECT idUser FROM User WHERE fName='Phil' AND lName='McGraw' AND userType='Doctor'),
(SELECT idPatient FROM Patient WHERE fName='Brandon' AND lName='Berk' AND dob='1992-11-02')
);
INSERT INTO Visit
(dt, isAppt, fkStaff, fkPatient)

VALUES
('2014-11-19 20:27:30', TRUE,
(SELECT idUser FROM User WHERE fName='Phil' AND lName='McGraw' AND userType='Doctor'),
(SELECT idPatient FROM Patient WHERE fName='James' AND lName='Cary' AND dob='1991-09-09')
);
INSERT INTO Visit
(dt, isAppt, fkStaff, fkPatient)
VALUES
('2014-11-20 20:27:30', TRUE,
(SELECT idUser FROM User WHERE fName='Lexi' AND lName='Cox' AND userType='MedicalAssistant'),
(SELECT idPatient FROM Patient WHERE fName='Andrew' AND lName='Watson' AND dob='1991-06-26')
);
INSERT INTO Visit
(dt, isAppt, fkStaff, fkPatient)
VALUES
('2014-11-21 20:27:30', TRUE,
(SELECT idUser FROM User WHERE fName='Lexi' AND lName='Cox' AND userType='MedicalAssistant'),
(SELECT idPatient FROM Patient WHERE fName='Kyle' AND lName='McCleary' AND dob='1991-06-26')
);
-- Kyle's second appointment is a walkin (isAppt = false)
INSERT INTO Visit
(dt, isAppt, fkStaff, fkPatient)
VALUES
('2014-11-22 20:27:30', FALSE,
(SELECT idUser FROM User WHERE fName='Snookie' AND lName='Snook' AND userType='Secretary'),
(SELECT idPatient FROM Patient WHERE fName='Kyle' AND lName='McCleary' AND dob='1991-06-26')
);
-- Insert a record per visit
INSERT INTO Record
(reason, notes, weight, height, bmi, hr, bp, pulseOx, temp, fkVisit)
VALUES
("Standard checkup", "vitals good", 160.0, 6.0, 18.0, 60, 120, 0.97, 98.6,
(SELECT idVisit FROM Visit WHERE dt='2014-11-18 20:27:30')
);
INSERT INTO Record
(reason, notes, weight, height, bmi, hr, bp, pulseOx, temp, fkVisit)
VALUES
("Regular checkup", "patient healthy. advised to quit vaping.", 150.0, 5.9, 20, 70, 122, 0.98, 98.0,
(SELECT idVisit FROM Visit WHERE dt='2014-11-19 20:27:30')
);
INSERT INTO Record
(reason, notes, weight, height, bmi, hr, bp, pulseOx, temp, fkVisit)
VALUES
("knee pain", "recurring knee pain follow-up", 180.0, 6.0, 20, 65, 118, 0.98, 98.5,
(SELECT idVisit FROM Visit WHERE dt='2014-11-20 20:27:30')
);
INSERT INTO Record
(reason, notes, weight, height, bmi, hr, bp, pulseOx, temp, fkVisit)
VALUES
("6 month checkup", "patient complained of fever", 215, 6.3, 19.0, 85, 125, 0.99, 102.0,
(SELECT idVisit FROM Visit WHERE dt='2014-11-21 20:27:30')
);
INSERT INTO Record
(reason, notes, weight, height, bmi, hr, bp, pulseOx, temp, fkVisit)
VALUES
("fever follow-up", "Fever still high. Referring to Fever Specialists", 215, 6.3, 19.0, 85, 125, 0.99, 104,
(SELECT idVisit FROM Visit WHERE dt='2014-11-22 20:27:30')
);
-- Insert a referral for Kyle. He needs to see a specialist
INSERT INTO Referral
(dt, businessName, referralType, fkVisit)
VALUES
(NOW(), "Fever Specialists", "General",
(SELECT idVisit FROM Visit WHERE dt='2014-11-22 20:27:30')
);

3.1.3 Database Query/Update Script

----QUERIES----- Select three users by different means
select * from user where password = "LexiRoxMySox";
select * from user where userType = "Secretary";
select * from user where fName = "Lexi";
-- Select every patient by different means
select * from patient where lName = "Berk";
select * from patient where dob = '1991-06-26';
select * from patient where phoneHome = "3213213215";
-- Show all visits after 11-20-2014 00:00:00
select * from visit where dt > '2014-11-20';
-- Get the names of patients born on Jun 26 1991 who have had a visit.
select fName, lName from patient where dob = '1991-06-26' and idPatient in (select fkPatient from visit);
----UPDATES----- Snookie suddenly got a promotion!
update user
set lName="McGraw", userType="MedicalAssistant"
where lName="Snook" and fName="Snookie" and userType="Secretary";
-- aaaaand Lexi got demoted!
update user
set userType="Secretary"
where lName="Cox" and fName="Lexi" and userType="MedicalAssistant";
-- Lexi is upset. She quittin'
delete from user
where fName="Lexi" and lName="Cox" and userType="Secretary";

3.2 Database Usage

The sections below detail the example creation, population and usage of the EMR database. The data
used in these sections is falsified, fictitious and manufactured for example purposes only and does not
represent any real people or medical data.

3.2.1 Database Creation

The group used MySQL Workbench for the entirety of phase 3. Example screenshots detailing this
process are included below.
Figure 4 - Creation of Tables shows the creation a table per entity from the ERD.

Figure 4 - Creation of Tables

Figure 5 - Example Column Options shows the sort of options that are necessary when populating the
attributes of types INT, BOOL, and DATETIME. Other options included VARCHAR() and BLOB for text DATE
for dates, and FLOAT for various patient vitals.

Figure 5 - Example Column Options

Figure 6 - Example Foreign Keys Options shows the mapping of foreign keys with cascading options.

Figure 6 - Example Foreign Keys Options

Figure 7 - Example Indexes Options shows the creation of necessary indices for mapping foreign keys to
primary keys.

Figure 7 - Example Indexes Options

3.2.2 Database Population

Figure 8 - Database Population shows the populated relations within the EMR. The group fashioned false
data with a sense of humor for 3 user types, 4 patients, 5 visits and matching records, and 1 referral.

Figure 8 - Database Population

3.2.3 Database Queries

Figure 9 User Selection shows the selection of users by password, userType and firstName.

Figure 9 User Selection

Figure 10 - Patient Selection shows selection of patients by lName, dob (2 results), and phoneHome.

Figure 10 - Patient Selection

Figure 11 - Visit Selection shows the visits that occurred later than 2014-11-20. Note that the first result
has a timestamp later than the assumed 00:00:00.

Figure 11 - Visit Selection

Figure 12 - Advanced Patient/Visit interaction shows the retrieval of the full name of patients whose DOB
is 1991-06-26 and have had at least one visit.

Figure 12 - Advanced Patient/Visit interaction

3.2.4 Database Updates

Figure 13 - Love Triangle Part 1 shows the marriage of Snookie Snook to Phil McGraw. This results her in
her taking his last name and being promoted to Medical Assistant.

Figure 13 - Love Triangle Part 1

Figure 14 - Love Triangle Part 2 shows the epic conclusion of our story. Lexi, having rocked Phils socks just
days before his marriage to Snookie, quits in outrage after her demotion to secretary.

Figure 14 - Love Triangle Part 2

4 Feedback and Commentary

At the beginning of the project certain decisions were made regarding the EMR model without
any real database implementation experience. Going into the creation phase of the project led the group
to make a few adjustments to the initial EMR model. The first major change was the elimination of the
vitals entity. After discussion about the end use for the database it made little sense to discretize the
record and vital entities as they were inherently reliant on one another. Thus there would never be a case
where records existed without vitals. Upon the merging of these entities it was also discovered that the
domains of some of the attributes for the vitals had to change in order for the database to be successfully
integrated into the application. For example the height attribute which was initially planned to be two
attributes (feet and inches) were merged into a single floating point attribute in the database where the
decimal denoted the change from feet to inches. Similar modifications were also made to the heart rate
and blood pressure attributes. It was also decided that the group would leave out the referral subtypes
from the database as those entities would never contain any actual attributes. These entities were left in
the database schema however as an option for future development. These modifications have been
denoted in our final ERD diagram in Figure 2 - EMR Entity Relationship Diagram (ERD) with an
unimplemented highlight.
As a group we felt that the MySQL DBMS software performed exceptionally well and appears to
be a sufficient tool for many different types of databases. With many of the group members having no
experience with the software at all it was very simple to install and load on all of our machines without
the need to even investigate tips for setting up the software. Following the prompts and wizards were

enough to get us to the point of being able to create our schemas. Workbench proved to be a very handy
tool for us as it aided in the creation of not only our schema but also the population and query scripts to
verify proper database functionality. After the scripts were created and the database populated we then
were able to go back and create ERD diagrams with the software to ensure everything was implemented
correctly and that we had followed all of our project guidelines and business rules.
One of the most frustrating parts of the project, as is true with most group projects, was the
coordination of the group members in order to meet project deadlines. However this was a very small
price to pay for the valuable knowledge we gained in learning about MySQL and the workbench software.
Going through all the database creation steps from schema modeling to front-end application
development has helped all of us in understanding of the material learned in the course, and should help
even more outside the classroom. Had we started the process back in September we probably would have
started using the MySQL software from the very first phase of the project. The software can be an
immensely powerful tool in the modeling of a database as much as it is in its creation.