Lab 19 Web App SQL

Juan Quintana, Arielle Lauper, Vance Thrasher

CST363: Intro to Database Systems

June 4, 2024
ER diagram:

Introduction:

This database was designed to facilitate the tracking of patient prescriptions for
pharmacies. Along with prescription data, the database also manages doctor and
patient information. This design efficiently manages validation of doctor, patient, and
drug information. The database also tracks prescription fills and refills as well as
variations in pricing from different pharmacies.

Requirements:

● All doctors who have registered with the application must be tracked.

A Doctor table is created in the database to hold all of the information pertaining to the
doctors registered on the application.

● All patients who have registered with the application must be tracked.

A Patient table is created in the database to hold all of the information pertaining to the
patients registered on the application.

● The database must keep track of all of the prescriptions.

A Prescription table is created in the database to hold all of the information pertaining to
the prescriptions that registered doctors have written for registered patients.

● The database must have a list of drugs that can be prescribed.

A Drug table is created in the database to hold a list of all of the prescription drugs
offered.

● The cost of a drug may fluctuate depending on what pharmacy it is purchased

from. The correct drug cost must be tracked.

The Drug_Cost table holds the information for the cost of a drug at a given pharmacy.
The Drug_Cost table connects the Pharmacy and Drug tables. The cost attribute is
assigned the price that the particular pharmacy charges for the drug, and the
unit_amount attribute holds the number of tablets sold at the given price.

● Each time a prescription is filled, the database must track the pharmacy that
filled the prescription, the total cost, and the date it was filled.

The Prescription_Fill table tracks the pharmacy that filled the prescription, the total cost,
and the date the prescription was filled. The total cost is calculated with the cost
attribute in the Drug_Cost table and the quantity of the drug in the Prescription table.

Below is a breakdown of the diagram:

Name: doctor

Description: A doctor is any doctor that has been registered on the web application.
Each doctor has an auto generated primary key for identification. The table records the
doctor’s unique social security number (ssn), first name and last name. Doctors may
also specify a specialty, and the date when they first started practicing. Each doctor may
have many patients and may write many prescriptions.

Name: patient

Description: A patient is a person registered under the care of a doctor. Each patient
has an auto generated primary key for identification. The table records the unique social
security number (ssn), first name, last name, birth date, and the street, city, state, and
zip code of the patient. The state is recorded as a two character abbreviation. Every
patient must have one primary doctor and may have many prescriptions.
Name: prescription

Description: A prescription is an authorization from a registered doctor which approves

the sale of a particular drug to a patient. Each prescription has an auto generated
primary key for identification. The quantity included in each refill, the number of times
the prescription can be refilled, and the date the prescription was created is tracked.
Each Prescription must correspond to a drug that is in the database. Each prescription
must be associated with a registered doctor and one patient. Each prescription may be
refilled up to the number of refills on the prescription.

Name: drug

Description: A drug represents any drug tracked in the database. Each drug has a
name. Each drug can be prescribed multiple times.

Name: pharmacy

Description: A pharmacy represents any pharmacy tracked in the database. Each

pharmacy has an auto generated primary key for identification. The name, address, and
phone number of the pharmacy is recorded. The phone number may include
non-numeric characters such as parentheses and dashes. Each pharmacy may fill
many prescriptions.

Name: drug_cost

Description: The Drug_Cost table tracks the cost each pharmacy charges for a set
amount of a drug. It records the cost that a particular pharmacy charges, and unit
amount of the drug sold at that price.

Name: prescription_fill

Description: The Prescription_Fill table connects the Pharmacy and Prescription tables.
It also records the cost to the patient, and the date the prescription was filled.
SQL schema:

-- MySQL Workbench Forward Engineering

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='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DA
TE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTI
ON';

-- -----------------------------------------------------

-- Schema prescription

-- -----------------------------------------------------

-- -----------------------------------------------------

-- Schema prescription

-- -----------------------------------------------------

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

USE `prescription` ;

-- -----------------------------------------------------

-- Table `prescription`.`doctor`

-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `prescription`.`doctor` (

`id` INT NOT NULL AUTO_INCREMENT,

`ssn` CHAR(9) NOT NULL,

`first_name` VARCHAR(50) NOT NULL,

`last_name` VARCHAR(50) NOT NULL,

`specialty` VARCHAR(50) NULL,

 `practice_since` INT NULL,

PRIMARY KEY (`id`),

UNIQUE INDEX `ssn_UNIQUE` (`ssn` ASC) VISIBLE)

ENGINE = InnoDB;

-- -----------------------------------------------------

-- Table `prescription`.`patient`

-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `prescription`.`patient` (

`id` INT NOT NULL AUTO_INCREMENT,

`doctor_id` INT NOT NULL,

`ssn` CHAR(9) NOT NULL,

`first_name` VARCHAR(50) NOT NULL,

`last_name` VARCHAR(50) NOT NULL,

`birth_date` DATE NOT NULL,

`street` VARCHAR(50) NOT NULL,

`city` VARCHAR(50) NOT NULL,

`state` CHAR(2) NOT NULL,

`zip` INT NOT NULL,

PRIMARY KEY (`id`),

UNIQUE INDEX `ssn_UNIQUE` (`ssn` ASC) VISIBLE,

INDEX `fk_Patient_Doctor_idx` (`doctor_id` ASC) VISIBLE,

CONSTRAINT `fk_Patient_Doctor`

FOREIGN KEY (`doctor_id`)

REFERENCES `prescription`.`doctor` (`id`)

ON DELETE NO ACTION
 ON UPDATE NO ACTION)

ENGINE = InnoDB;

-- ----------------------------------------------------

-- Table `prescription`.`drug`

-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `prescription`.`drug` (

`drug_name` VARCHAR(100) NOT NULL,

PRIMARY KEY (`drug_name`))

ENGINE = InnoDB;

-- -----------------------------------------------------

-- Table `prescription`.`prescription`

-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `prescription`.`prescription` (

`RXID` INT NOT NULL AUTO_INCREMENT,

`doctor_id` INT NOT NULL,

`patient_id` INT NOT NULL,

`drug_name` VARCHAR(50) NOT NULL,

`quantity` INT NOT NULL,

`number_of_refills` INT NOT NULL,

`date_created` DATE NOT NULL,

PRIMARY KEY (`RXID`),

INDEX `fk_Prescription_Doctor1_idx` (`doctor_id` ASC) VISIBLE,

INDEX `fk_Prescription_Patient1_idx` (`patient_id` ASC) VISIBLE,

INDEX `fk_Prescription_Drug1_idx` (`drug_name` ASC) VISIBLE,

CONSTRAINT `fk_Prescription_Doctor1`
 FOREIGN KEY (`doctor_id`)

REFERENCES `prescription`.`doctor` (`id`)

ON DELETE NO ACTION

ON UPDATE NO ACTION,

CONSTRAINT `fk_Prescription_Patient1`

FOREIGN KEY (`patient_id`)

REFERENCES `prescription`.`patient` (`id`)

ON DELETE NO ACTION

ON UPDATE NO ACTION,

CONSTRAINT `fk_Prescription_Drug1`

FOREIGN KEY (`drug_name`)

REFERENCES `prescription`.`drug` (`drug_name`)

ON DELETE NO ACTION

ON UPDATE NO ACTION)

ENGINE = InnoDB;

-- -----------------------------------------------------

-- Table `prescription`.`pharmacy`

-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `prescription`.`pharmacy` (

`pharmacy_id` INT NOT NULL AUTO_INCREMENT,

`name` VARCHAR(50) NOT NULL,

`address` VARCHAR(100) NOT NULL,

`phone_number` VARCHAR(20) NOT NULL,

PRIMARY KEY (`pharmacy_id`))

ENGINE = InnoDB;
-- -----------------------------------------------------

-- Table `prescription`.`drug_cost`

-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `prescription`.`drug_cost` (

`pharmacy_id` INT NOT NULL,

`drug_name` VARCHAR(50) NOT NULL,

`cost` DECIMAL(8,2) NOT NULL,

`unit_amount` INT NOT NULL,

PRIMARY KEY (`pharmacy_id`, `drug_name`),

INDEX `fk_Pharmacy_has_Drug_Drug1_idx` (`drug_name` ASC) VISIBLE,

INDEX `fk_Pharmacy_has_Drug_Pharmacy1_idx` (`pharmacy_id` ASC) VISIBLE,

CONSTRAINT `fk_Pharmacy_has_Drug_Pharmacy1`

FOREIGN KEY (`pharmacy_id`)

REFERENCES `prescription`.`pharmacy` (`pharmacy_id`)

ON DELETE NO ACTION

ON UPDATE NO ACTION,

CONSTRAINT `fk_Pharmacy_has_Drug_Drug1`

FOREIGN KEY (`drug_name`)

REFERENCES `prescription`.`drug` (`drug_name`)

ON DELETE NO ACTION

ON UPDATE NO ACTION)

ENGINE = InnoDB;

-- -----------------------------------------------------

-- Table `prescription`.`prescription_fill`

-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `prescription`.`prescription_fill` (

`fill_number` INT NOT NULL AUTO_INCREMENT,

`RXID` INT NOT NULL,

`pharmacy_id` INT NOT NULL,

`cost` DECIMAL(8,2) NOT NULL,

`date` DATE NOT NULL,

PRIMARY KEY (`fill_number`, `RXID`),

INDEX `fk_table1_Prescription1_idx` (`RXID` ASC) VISIBLE,

INDEX `fk_table1_Pharmacy1_idx` (`pharmacy_id` ASC) VISIBLE,

CONSTRAINT `fk_table1_Prescription1`

FOREIGN KEY (`RXID`)

REFERENCES `prescription`.`prescription` (`RXID`)

ON DELETE NO ACTION

ON UPDATE NO ACTION,

CONSTRAINT `fk_table1_Pharmacy1`

FOREIGN KEY (`pharmacy_id`)

REFERENCES `prescription`.`pharmacy` (`pharmacy_id`)

ON DELETE NO ACTION

ON UPDATE NO ACTION)

ENGINE = InnoDB;

SET SQL_MODE=@OLD_SQL_MODE;

SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;

SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
Screenshots:

Register as a new patient with last name “Simpson”, city “Rockville”, zip code 62701
and a doctor with name “Spock”. Show a successful registration.

Attempt to register as a new patient with last name “Burns” but with a doctor name that
does not exist. Show a screenshot of the patient register form with the error message.
Create a prescription for the patient “Simpson” and doctor “Spock” for a drug “lisinopril”
and quantity 90. Show the screen with the success message and prescription display.

Attempt to create a prescription with an invalid drug name. Show a screen with the
create prescription form and error message.
Attempt to fill a prescription with an invalid pharmacy name.

Attempt to fill a prescription with an invalid rxid.

Fill the prescription with success.

Get the profile for patient “Simpson” and edit the patient record for “Simpson” and
change city to Springfield and zip code to 61705. Show the web page of the successful
update.
Edit the patient record for “Simpson”. Attempt to change the doctor’s name to a doctor
that does not exist. Show the error message and edit patient form.