Chapter 1 Introduction

Chapter 2 System Analysis -- User’s Scenarios

Chapter 3 Requirements for System Design

Chapter 4 Database Design

Chapter 5 GUI Design and VB Implementation

Chapter 6 Source Code

In this project a Pharmacist Information System (PIS) is developed. PIS consists of an Microsoft (MS) Access database and an application program implemented in MS Visual Basic. With data stored in the Access database, the application program provides a convenient and efficient way to handle regular business operations of a pharmacy store. Visual Basic allows developing a friendly user interface for the application program. Characteristics of PIS are summarized as follows.

1. GUI and source code language: MS Visual Basic 5
2. Database implementation: MS Access 97
3. Subsystems and functions

• Pharmacist subsystem
• Login: Allows a user to login into the pharmacist subsystem if he/she is an authorized pharmacist or manager.
• Prescription:
• Input, load and update personal information of a patient.
• Fill a prescription, check drug interactions, provide a choice of generic or brand name drug, calculate drug price and automatically update patient drug history.
• Drug inventory: Check the in-stock quantity of some or all drugs.
• Patient drug history: Check the drug history of a patient according to prescriptions presented to this pharmacy store.
• Manager subsystem
• Login: Allows a user to login into the manager subsystem if he/she is an authorized manager.
• Drug inventory management: Query, add or update drug inventory.

This system has two types of users. One is pharmacist. The other is manager. These two types of users have different duties and have different requirements for the system.

A pharmacist should have a valid user name and password to use this system. The pharmacist can start to work after login into the system. When a patient comes to the pharmacy store with a prescription, the pharmacist checks if basic personal information of the patient is already in the system. If not, input the patient’s information into the system. Otherwise, simply load the patient’s information from the system. Then the pharmacist can take the prescription, check if required drugs are available in this store, make sure the drugs do not have negative interactions with other drugs the patient is taking or have taken, and eventually sell drugs to the patient according to the prescription. During this process the system should be able to check interactions between drugs, automatically calculate the price, and after the transaction is finished, automatically update drug inventory. The system should also provide the patient a choice to buy generic or brand name drugs.

Once in a while, the pharmacist may need to check the quantity of a drug in the store, or check a patient’s drug history. Therefore the system should be able to automatically create and update a drug history for a patient according to prescriptions presented to this pharmacy store. Accordingly the system should have functions for checking drug inventory and viewing patient drug history.

A manager can always login as a pharmacist and performs the functions available to a pharmacist. However, there are also duties that can be performed only by the manager. In this system the major duty of a manager is to maintain a drug inventory for daily operation of the pharmacy store. The manager should be able to check current drug inventory, update such information as the price of a drug when there is a change, delete drugs that are no longer for sale, and add new drugs when they are available. A manager also needs a user name and password to use this system.

Because no assumptions are made on the computer background of the two types of users of this system, the interface to this system must be easy to understand for people without any computer background. When appropriately designed, a menu-driven interface can have the property of self-evident and easy to use, and thus is appropriate for this system.

This system should be protected from unauthorized uses. Only authorized users can have access to it. And different user types should have different access level. For example, while a pharmacist automatically updates the appropriate tables in the database during a prescription transaction, only a manager can directly update drug inventory without a prescription transaction. Such security policies can be enforced through access control using a user name/password login system.

Several issues are involved in database design, including database integrity and appropriate decomposition of tables for efficient representation of information, etc. When designed properly, integrity constraints including domain constraints, referential integrity and functional dependencies can ensure database integrity, while pitfalls in relational database design such as repetition or loss of information and inability to represent certain information can also be avoided by following appropriate normal forms.

The system analysis in previous chapter outlines the functional requirements of the target system. To be more specific, the required functions are listed as follows:

• Pharmacist
• Login: Allows a user to login into the pharmacist subsystem if he/she is an authorized pharmacist or manager.
• Prescription:
• Input, load and update personal information of a patient.
• Fill a prescription, check drug interactions, provide a choice of generic or brand name drug, calculate drug price and automatically update patient drug history.
• Drug inventory: Check the in-stock quantity of some or all drugs.
• Patient drug history: Check the drug history of a patient according to prescriptions presented to this pharmacy store.
• Manager
• Login: Allows a user to login into the manager subsystem if he/she is an authorized manager.
• Drug inventory management: Query, add or update drug inventory.

According to above system analysis, the following data sets are input to, checked or generated by the system during its operation:

• Drug information: Drug name, type, price, usage and interactions etc.
• Patient information: Patient ID, name, phone number and address etc.
• Prescription information: Patient ID, prescription date, drugs, price etc.
• Patient drug history: Patient ID, drugs, prescription date etc.
• Authorization information: User name and password of authorized users.

Data and functions of PIS as described above can be modeled using the E-R model shown in Fig 3.1. These entities are related through 4 relations.

• A patient can submit one or many prescriptions while a prescription can be submitted by only one patient.
• One or more drugs can be subscribed in a prescription and one drug can be subscribed in more than one prescription.
• The drug entity has relationships with itself through drug interaction and brand-generic name correspondence. One drug may interact with several other drugs.
• A generic drug name may have several brand drug name. But a brand drug name can have only one generic drug name.
• The authorization entity does not have relationship with the other two entities. The only purpose of this entity is to give permission to a user to login into this system when the given user name and password are in this table.

CREATE TABLE drugs (

DRUG_NAME char (20) not null,

TYPE char (10) not null,

PRICE float,

USES char (10) not null,

QUENTITY int,

PRIMARY KEY (DRUG_NAME));

CREATE TABLE generic_brand (

G_NAME char (20) not null,

B_NAME char (20) not null,

PRIMARY KEY (G_NAME, B_NAME),

FOREIGN KEY (G_NAME) FEFERENCES drugs,

FOREIGN KEY (B_NAME) REFERENCES drugs);

CREATE TABLE history (

HISTORY_NUM AutoNumber,

P_SSN char (20) not null,

PRES_NUM int,

PRES_DATE Date/Time,

PRIMARY KEY (HISTORY_NUM));

CREATE TABLE interaction (

DRUG_NAME char (20) not null,

LEVEL char (50) not null

PRIMARY KEY (DRUG_NAME, INTERACTIVE_NAME),

FOREIGN KEY (DRUG_NAME) FEFERENCES drugs,

FOREIGN KEY (INTERACTIVE_NAME) REFERENCES drugs);

CREATE TABLE pass_word_pharmacist (

USER_ID int,

USER_NAME char (20) not null,

USER_LEVEL int

PRIMARY KEY (USER_ID));

CREATE TABLE patient (

P_SSN int,

P_NAME char (20) not null,

P_PHONE char (20) not null,

P_ADDRESS char (20) not null,

INSURANCE_NO char (20) not null

PRIMARY KEY (P_SSN));

CREATE TABLE prescribe (

PRESCRIBE_NUM AutoNumber,

PRES_NUM int,

DRUGS char (20) not null

PRIMARY KEY (PRES_NAME, DRUGS),

FOREIGN KEY (DRUG_NAME) FEFERENCES drugs,

FOREIGN KEY (PRES_NO) REFERENCES prescription);

CREATE TABLE prescription (

PRES_NO int,

PRES_DATE char (20) not null,

TOTAL float

PRIMARY KEY (PRES_NO));

CREATE TABLE submit (

PATIENT_SSN char (20) not null,

PRES_NUM int

PRIMARY KEY (PATIENT_SSN, PRES_NUM));

FOREIGN KEY (P_SSN) FEFERENCES patient,

FOREIGN KEY (PRES_NO) REFERENCES prescription);

1. Login window for the pharmacist subsystem

3. The prescription interface

The prescription interface consists of two major parts. The first part allows to load or input patient information. Simply input the patient’s social security number and click "load patient", If the patient is in the database, the information will be loaded as below. This interface allows for modification and updating of patient information.

If the patient is not in the database, the system will prompt to input patient information using the following message box followed by above interface:

Once the patient information is correct, the pharmacist can then accept a prescription, input drug name and quantities in the appropriate boxes. The system will automatically load interaction information. After all drugs are input, click the "Total" button will calculate the total price. When the transaction is accepted by clicking "OK", all related tables are automatically updated properly.

4. The drug inventory interface

5. The patient drug history interface

The major functions of this subsystem include loading, adding and updating drug information. These functions allow a manager to check current drug inventory, add new drugs and update drug prices and quantities.