Database Project - Pharmacy Data Management System PDF

Preview

Summary

Download Database Project - Pharmacy Data Management System PDF

Description

Pharmacy Data Management System Binh Du 301023706 LiPeng Zhang 300980170 Prince Student id Advanced Database Concepts COMP214 - Section 006

Pharmacy Data Management System

Table of Content Problem Domain Description ER Model and Business Rules User Interface Screens Tables Triggers, Functions, and Procedure Database Script

2 3 4 5 7 12

1

Pharmacy Data Management System

Problem Domain Description The purpose of this report is to create a data management system for a pharmacy. Generally, the information needed for the business includes patients, insurance companies, payments, medicines, and prescriptions. As a transaction is processed, the business procedures would be as following: External Procedure: A patient gives pharmacist prescription or asks to purchase some medicine. The pharmacist takes patient personal and insurance information, the requested medicines/drugs are processed to check out. While generating a payment bill, it is validated that if the drugs are covered by the insurance company. If yes, it shows how much is covered, then the patient will pay the rest that is not covered. Internal procedure: When pharmacist gets the medicines out of stock, the in-stock quantity is adjusted to show the available amount of the remain medicines. And based on the type of insurance that the patient held, medicine coverage is generated to calculate the amount covered by the insurance company. When the receipt is printed it displays both coverage amount that is paid by the insurance and remaining amount that the patient has to pay.

2

Pharmacy Data Management System

ER Model and Business Rules

Business Rules: - One patient may have zero or many prescriptions + One doctor can give 0 or many prescriptions to 1 patient + The patient can refill the given prescription 0 or many times + Each prescription has its own detailed description, but one prescription can have more than one medicines/drugs - Assuming that one patient can only have one insurance: + And the governmental insurance and the common insurance companies provide the pharmacy a medicine coverage list - One patient may make zero or many payments + One payment can be done under one or many methods

User Interface Screens

3

Pharmacy Data Management System

UI Home Page:

Drug List Page

4

Pharmacy Data Management System

Patient List Page

Tables The system has 7 tables: - Doctors: to contain the information about the doctors that provide the prescription to the patients - Drugs: to contain information about the drug such as drug name, drug description, inventory quantity, etc. - Insurances: to contain information about patients’ insurances - Insurance types: to contain information about insurance companies, such as their names, contact, address, etc. - Patients: to contain information about the patients - Prescription: to contain general information of one all prescription such as the ID of the patient that the prescription belongs to, ID of the doctor who provided the prescription, the date when the prescription is given, etc. - Prescription drugs: details of the drugs that are provided in the prescription such as drug ID, dosage, and instruction.

5

Pharmacy Data Management System

Patients table sample: (Sequence is applied to create patient ID)

Prescription table sample: (Sequence is applied to create prescription ID)

Doctor table sample: (Sequence is applied to create doctor ID)

Prescription drug table sample:

Drug table sample: (Sequence is applied to create drug ID, insurance_covering is

6

Pharmacy Data Management System

foreign key refers to the insurance type, Index is also applied to drug name and drug description in order to make the searching drug in the inventory more convenient)

Triggers, Functions, and Procedure Triggers: ● Trigger: when deleting the doctor ，this doctor‘s prescription will be deleted. CREATE OR REPLACE TRIGGER DELETE_PRESCRIPTION BEFORE DELETE ON PRESCRIPTION FOR EACH ROW BEGIN DELETE FROM doctors WHERE DOCTORS.doctor_id=:OLD.DOCTOR_ID; END;

7

Pharmacy Data Management System

Procedures: ● Procedures 1: CREATE OR REPLACE PROCEDURE BEGIN_INSURANCE (ARG_INSURANCE_ID IN VARCHAR2) AS BEGIN DECLARE CURR_START_DATE DATE; BEGIN SELECT ISSUE_DATE INTO CURR_START_DATE FROM INSURANCES WHERE INSURANCES.INSURANCE_ID = ARG_INSURANCE_ID; IF CURR_START_DATE = NULL THEN UPDATE INSURANCES SET ISSUE_DATE = CURRENT_DATE WHERE INSURANCES.INSURANCE_ID = ARG_INSURANCE_ID; DBMS_OUTPUT.PUT_LINE('INSURANCE ID: ' || ARG_INSURANCE_ID || ' already started at ' || TO_CHAR(CURRENT_DATE)); ELSE DBMS_OUTPUT.PUT_LINE('INSURANCE ID: ' || ARG_INSURANCE_ID || ' already started at ' || TO_CHAR(CURR_START_DATE)); END IF; END; END BEGIN_INSURANCE;

8

Pharmacy Data Management System

● Procedures 2: CREATE OR REPLACE PROCEDURE END_INSURANCE (ARG_INSURANCE_ID IN VARCHAR2) AS BEGIN DECLARE CURR_END_DATE DATE; BEGIN SELECT EXPIRE_DATE INTO CURR_END_DATE FROM INSURANCES WHERE INSURANCES.INSURANCE_ID = ARG_INSURANCE_ID; IF CURR_END_DATE = NULL THEN UPDATE INSURANCES SET EXPIRE_DATE = CURRENT_DATE WHERE INSURANCES.INSURANCE_ID = ARG_INSURANCE_ID; DBMS_OUTPUT.PUT_LINE('INSURANCE ID: ' || ARG_INSURANCE_ID || ' ended at ' || TO_CHAR(CURRENT_DATE)); ELSE DBMS_OUTPUT.PUT_LINE('INSURANCE ID: ' || ARG_INSURANCE_ID || ' already ended at ' || TO_CHAR(CURR_END_DATE)); END IF; END; END END_INSURANCE;

9

Pharmacy Data Management System

Functions: ● Function 1: compute the doctor total from doctors table CREATE OR REPLACE FUNCTION totalDoctor return number is total number(3):=0; begin select count(*) into total from doctors; return total; End;

● Function 2: compute the drug cost CREATE OR REPLACE FUNCTION calc_drug_subtotal ( 10

Pharmacy Data Management System

insurance_type in out VARCHAR2, drug_id in out NUMBER, price in out Number, quantity in out number ) RETURN NUMBER as subtotal NUMBER; BEGIN IF insurance_type = 'OH' or insurance_type = 'GM' THEN subtotal:= 0; ELSIF insurance_type IS NULL THEN subtotal := price * quantity; END IF; return subtotal; END calc_drug_subtotal;

11

Pharmacy Data Management System

Database Script DROP SEQUENCE patients_seq; DROP SEQUENCE prescriptionNum_seq; DROP SEQUENCE doctorID_seq; DROP SEQUENCE drugID_seq; DROP TABLE patients; DROP TABLE insurance_types; DROP TABLE insurances; DROP TABLE invoices; DROP TABLE doctors; DROP TABLE prescription; --Create Sequence that populate the patient ID CREATE SEQUENCE patients_seq 12

Pharmacy Data Management System

START WITH 100001 INCREMENT BY 1 MINVALUE 100000; --Create patients table CREATE TABLE patients ( patient_id NUMBER(10) PRIMARY KEY, patient_name VARCHAR2(255) NOT NULL, phone_number NUMBER(10), address VARCHAR2(100), city VARCHAR2(30), province VARCHAR2(2), postal_code VARCHAR2(7), insurance_id VARCHAR2(15) UNIQUE ); INSERT INTO patients VALUES (patients_seq.nextval, 'Sam Gordon', 5195476854, '46 Finch Ave', 'Scarborough', 'ON', 'M1E 2L3', '1290348576'); INSERT INTO patients VALUES (patients_seq.nextval, 'Justine Grey', 6474328549, '578 Main St', 'Scarborough', 'ON', 'M0B 5L1', 'GVC58793203'); INSERT INTO patients VALUES (patients_seq.nextval, 'Dana Johnson', 6473235453, '90 Easter Rd', 'Eglinton', 'ON', 'M1V 4F1', '9246398350'); INSERT INTO patients VALUES (patients_seq.nextval, 'Mark Wong', 4675435343, '432 Younge St', 'Markham', 'ON', 'L1Z 3S2', 'GVC92924567'); INSERT INTO patients VALUES (patients_seq.nextval, 'Jeanne Mendes', 4674324322, '88 Eather St', 'Scarborough', 'ON', 'M1B 1R3', '4297539028'); INSERT INTO patients VALUES (patients_seq.nextval, 'Diana McKennedy', 4164365389, '77 Western Ave', 'Malvern', 'ON', 'M4G 8R3', '2348549355'); INSERT INTO patients VALUES (patients_seq.nextval, 'Joseph Miagar', 5197829827, '23 Sheppard Ave', 'Eglinton', 'ON', 'M6k 3P5', '8593358933'); Select * from patients; --Create insurance types table CREATE TABLE insurance_types ( 13

Pharmacy Data Management System

insurance_name VARCHAR2(225), abbreviate VARCHAR2(2) UNIQUE, address VARCHAR2(225), phone NUMBER(10) ); --Insert types of insurance into the table INSERT INTO insurance_types VALUES ('OHIP', 'OH', '119 King St W 10th Floor, Hamilton, ON L8P 4Y7',8665323161); INSERT INTO insurance_types VALUES ('Guard.me', 'GM','80 Allstate Parkway,Markham, Ontario,Canada L3R 6H3',9057526200); Select * from insurance_types; --Create Sequence that populate the Prescription number when new prescription is added to the system CREATE SEQUENCE prescriptionNum_seq START WITH 1001 INCREMENT BY 1 MINVALUE 1000; --Create prescription table CREATE TABLE prescription ( prescription_id NUMBER(10) PRIMARY KEY, prescription_date DATE, prescription_cost DECIMAL(10,2), patient_id NUMBER(10), CONSTRAINT prescription_patientID_fk FOREIGN KEY (patient_id) REFERENCES patients(patient_id),

); INSERT INTO prescription VALUES (prescriptionNum_seq.nextval,TO_DATE('06/11/19','DD/MM/YY'),31.36,100003); INSERT INTO prescription VALUES (prescriptionNum_seq.nextval,TO_DATE('07/11/19','DD/MM/YY'),3.79,100002); 14

Pharmacy Data Management System

INSERT INTO prescription VALUES (prescriptionNum_seq.nextval,TO_DATE('10/11/19','DD/MM/YY'),9.17,100007); INSERT INTO prescription VALUES (prescriptionNum_seq.nextval,TO_DATE(10/11/19','DD/MM/YY'),3.15,100001); INSERT INTO prescription VALUES (prescriptionNum_seq.nextval,TO_DATE('12/10/19','DD/MM/YY'),10.14,100002); INSERT INTO prescription VALUES (prescriptionNum_seq.nextval,TO_DATE(08/11/19','DD/MM/YY'),7.75,100003); INSERT INTO prescription VALUES (prescriptionNum_seq.nextval,TO_DATE(06/12/19','DD/MM/YY'),1.44,100007); INSERT INTO prescription VALUES (prescriptionNum_seq.nextval,TO_DATE('04/10/19','DD/MM/YY'),4.33,100006); Select * from prescription; CREATE TABLE PRESC_DRUGS ( prescription_id NUMBER(10) NOT NULL, Drug_id number(10), prescription_dosage NUMBER(20), prescription_instructions VARCHAR2(50), CONSTRAINT FK_presc FOREIGN KEY(prescription_id)REFERENCES prescription(prescription_id), CONSTRAINT FK_drug FOREIGN KEY(drug_id)REFERENCES drugs(Drug_id) ); INSERT INTO PRESC_DRUGS VALUES (1005,9,15,'MORNING, AFTERNOON, EVENING'); INSERT INTO PRESC_DRUGS VALUES (1008,2,20,'MORNING'); INSERT INTO PRESC_DRUGS VALUES (1007,5, 10,'MORNING, EVENING'); INSERT INTO PRESC_DRUGS VALUES (1006,7,10,'AFTERNOON, EVENING'); INSERT INTO PRESC_DRUGS VALUES (1005,4,20,'MORNING, AFTERNOON, EVENING'); 15

Pharmacy Data Management System

INSERT INTO PRESC_DRUGS VALUES (1007,4,20,'MORNING, AFTERNOON, EVENING'); Select * from PRESC_DRUGS ORDER BY prescription_id;

--Create insurance table CREATE TABLE insurances ( patient_id NUMBER(10), insurance_id VARCHAR2(15), issue_date DATE, expire_date DATE, insurance_type VARCHAR2(2), CONSTRAINT insurances_patient_id_fk FOREIGN KEY (patient_id) REFERENCES patients(patient_id), CONSTRAINT insurances_insurance_id_fk FOREIGN KEY (insurance_id) REFERENCES patients(insurance_id), CONSTRAINT insurances_insurance_type_fk FOREIGN KEY (insurance_type) REFERENCES insurance_types(abbreviate) ); --Insert values into insurances tables INSERT INTO insurances VALUES (100001, 1290348576, TO_DATE('01/01/15','DD/MM/YY'),TO_DATE('01/01/20','DD/MM/YY'),'OH'); INSERT INTO insurances VALUES (100002, 'GVC58793203', TO_DATE('01/09/19','DD/MM/YY'),TO_DATE('01/09/20','DD/MM/YY'),'GM'); INSERT INTO insurances VALUES (100003, 9246398350, TO_DATE('01/08/17','DD/MM/YY'),TO_DATE('01/08/22','DD/MM/YY'),'OH'); INSERT INTO insurances VALUES (100004, 'GVC92924567', TO_DATE('01/09/19','DD/MM/YY'),TO_DATE('01/09/19','DD/MM/YY'),'GM'); INSERT INTO insurances VALUES (100005, 4297539028, TO_DATE('01/01/16','DD/MM/YY'),TO_DATE('01/01/21','DD/MM/YY'),'OH'); 16

Pharmacy Data Management System

INSERT INTO insurances VALUES (100006, 2348549355, TO_DATE('01/09/17','DD/MM/YY'),TO_DATE('01/09/22','DD/MM/YY'),'OH'); INSERT INTO insurances VALUES (100007, 8593358933, TO_DATE('01/03/19','DD/MM/YY'),TO_DATE('01/03/24','DD/MM/YY'),'OH'); Select * from insurances; --Create sequence for doctor ID CREATE SEQUENCE doctorID_seq START WITH 1 INCREMENT BY 1 MINVALUE 1; --Create Doctor table CREATE TABLE doctors ( doctor_id NUMBER(4) PRIMARY KEY, doctor_name VARCHAR(80), doctor_contact NUMBER(10), doctor_type VARCHAR(225) ); --Insert values for doctors' table INSERT INTO doctors VALUES (doctorID_seq.nextval, 'Alan Rogers', 4165438989, 'Cardiologist'); INSERT INTO doctors VALUES (doctorID_seq.nextval, 'Samantha Rande', 4374336849, 'Dermatologist'); INSERT INTO doctors VALUES (doctorID_seq.nextval, 'Verora Vince', 6474358238, 'Allergist'); INSERT INTO doctors VALUES (doctorID_seq.nextval, 'Jean Smith', 6473258939, 'Gastroenterologist'); INSERT INTO doctors VALUES (doctorID_seq.nextval, 'Bob Wang', 4674398934, 'Endocrinologist'); Select * from doctors; --Create sequence for drug ID CREATE SEQUENCE drugID_seq START WITH 1 INCREMENT BY 1 MINVALUE 1; --Create drug table CREATE TABLE drugs 17

Pharmacy Data Management System

( Drug_id Number(10) primary key, Drug_name VARCHAR2(225), Drug_description VARCHAR2(225), Drug_type VARCHAR2(225), Inventory_quantity NUMBER(7), Price NUMBER(5,2), Insurance_covering VARCHAR2(2), CONSTRAINT drug_covered_by_insurance_fk FOREIGN KEY (insurance_covering) REFERENCES insurance_types(abbreviate) ); --Insert values --BO stands for the drug is covered by both insurance INSERT INTO drugs VALUES (drugID_seq.nextval, 'Coumadin', '15mg', 'tablet', 150, 31.36, 'OH'); INSERT INTO drugs VALUES (drugID_seq.nextval, 'Lipitor', '20mg', 'tablet', 300, 3.79, 'OH'); INSERT INTO drugs VALUES (drugID_seq.nextval, 'Keflex', '500mg', 'capsule',350, 9.17, null); INSERT INTO drugs VALUES (drugID_seq.nextval, 'Mobic', '7.5mg', 'tablet', 200, 5.87, 'GM'); INSERT INTO drugs VALUES (drugID_seq.nextval, 'Nabumetone', '750mg', 'tablet', 3500, 1.37, 'OH'); INSERT INTO drugs VALUES (drugID_seq.nextval, 'Acetaminophmen', '160mg', 'tablet', 400, 4.19, 'OH'); INSERT INTO drugs VALUES (drugID_seq.nextval, 'Beonzonatate', '100mg', 'capsule', 600, 6.78, 'GM'); INSERT INTO drugs VALUES (drugID_seq.nextval, 'Dexilant', '60mg', 'tablet', 60, 304, 'OH'); INSERT INTO drugs VALUES (drugID_seq.nextval, 'Naloxone', '0.4mg', 'injectable', 20, 20.40, null); INSERT INTO drugs VALUES (drugID_seq.nextval, 'Prednisolone', '15mg', 'tablet', 40, 24.30, 'GM'); select * from drugs; --Create an index to search for drugs: CREATE INDEX idx_drug ON drugs (drug_name, drug_description);

18...