BORANG PENGESAHAN STATUS TESIS*

JIJDUL: DISTRIBUTED DATABASE PHARMACY MANAGEMENT SYSTEM

m SESI PENGAJIAN: 200712008

Saya NURSYAFEEKA BINTI ZAINUDDIN (HURUF BESAR)

mengaku membenarkan tesis (PSMISarjanalDoktor Falsafah) ini dishpan di Perpustakaan Fakulti Teknologi Maklumat clan Komunikasi dengan syarat-syarat kegunaan seperti berikut :

1. Tesis dan projek adalah hakmilik Universiti Teknikal Malaysia Melaka. 2. Perpustakaan Fakulti Teknologi Maklumat dan Komunikasi dibenarkan

membuat salinan untuk tujuan pengajian sahaja. 3. Perpustakaan Fakulti Teknologi Maklumat dan Komunikasi dibenarkan

membuat salinan tesis ini sebagai bahan pertukaran antara institusi pengajian tinggi.

4. ** Sila tandakan (/)

SULIT (Mengandungi maklurnat yang berdarjah keselamatan atau kepentingan Malaysia seperti yang termaktub di dalarn AKTA RAHSIA RASMI 1972)

TERHAD (Mengandungi maklumat TERHAD yang telah ditentukan oleh organisasihadan di mana penyelidikan dijalankan)

& a

(TANDATANGAN PENULIS)

Alamat tetap: Rumah Guru, SMK Senmarang, 83200 Senggaranp, Nama Penyelia Batu Pahat, Johor

Tarikh: 2 3 l o & 10% Tarikh: 33 /Q 6 YO8

CATATAN: * Tesis dimaksudkan sebagai Laporan Akhir Projek Sarjana Muda (PSM)

** Jika tesis ini SULIT atau TERHAD, sila lampirkan swat daripada pihak berkuasa.

DISTRIBUTED DATABASE PHARMACY MANAGEMENT SYSTEM (PMS)

NURSYAFEEKA BINTI ZAINUDDIN

This report is submitted in partial fulfillment of the requirements for the Bachelor of Computer Science (Database Management)

FACULTY OF INFORMATION AND COMMUNICATION TECHNOLOGY UNIVERSITI TEKNIKAI, MALAYSIA MELAKA

2008

DECLARATION

I hereby declare that this project report entitled

DISTRIBUTED DATABASE PHARMACY MANAGEMENT SYSTEM (PMS)

is written by me and is my own effort and that no part has been plagiarized

without citations.

STUDENT &?@ Date: 33 0 6 /06 (NURSYAFEE&4 BINTI ZAINUDDIN)

SUPERVISOR

DEDICATION

Thank you very much

to my beloved parents for their love,

to my supervisor for his advices

and to all my friends for their support from behind.

ACKNOWLEDGEMENTS

I would like to thank you very much to my supervisor, En. Muhammad

Suhaizan bin Sulong for helping and guiding me along this project. Furthermore, all

his advices are very worthwhile for me in finishing this project. Besides that, .I would

like to gratefully acknowledge the contribution of several people who helped me to

complete this thesis especially to all my friends who had given me 1 1 1 support from

behind. Moreover, thanks to my academic advisor, Miss Aina binti Md. Bohari who

has given many advices in developing Distributed Database Pharmacy Management

System (PMS). Thanks to Miss Nuridawati Binti Mustafa as my assessor for guiding

me before presentation for PSM 1.

Without any help from the lecturer in University Technical Malaysia Malacca,

(UTeM) I cannot deliver this report on time. Thanks to all lecturers in UTeM for

considering the students by giving time to finish this project. I am very grateful

because can be one of the students in UTeM who has supportive lecturer and always

sacrifices their time for finishing this project.

Last but not least, to all who might have involved directly or indirectly in developing

this system is much appreciated and a note of thanks from me.

ABSTRACT

The Distributed Database Pharmacy Management System (PMS) is developed especially for Pharmacy Murni, which is located at Batu Pahat, Johor. This application is developed to manage the medicine inventory and provide backup and recovery for security management of information in the pharmacy. It is an online system which can only be access within three main users; there are database administrator, pharmacist and pharmacist assistant. Only authorized user can login into the system and view the PMS application. The database administrator will maintain the backup and recovery and user privilege to view the system. Besides that, pharmacist's job is to manage the sales record of medicine besides using the medicine selector to choose the suitable medicine for specific symptom name. Furthermore, the pharmacist assistant job is to manage the order, payment and supplier details. On the other hand, this PMS application is focused more on database management of medicine besides maintaining the backup and recovery for the records in the database. The methodology of this system is System Development Life Cycle, SDLC and Database Life Cycle, DBLC. An analysis study has been done based on the current manual system and all the problems statements and requirements have been identified. Moreover, PMS is three tier architecture system which involves client tier, application tier or business tier and database management tier. The interfaces for PMS have been designed according to the requirement and needs of the current market. This Distributed Database Pharmacy Management System, (PMS) will help to improve the performance of current situation and overcome the problems that arise nowadays.

ABSTRAK

Distributed Database Pharmacy Management System (PMS) telah dibangunkan khasnya untuk Farmasi Murni di Batu Pahat, Johor. Sistem ini dibangunkan mtuk menguruskan inventori ubat-ubatan di dalam farmasi serta membekalkan backup and recovery untuk kawalan keselamatan maklumat di dalam fmas i . Sistem ini boleh diakses melalui internet dan hanya boleh digunakan untuk tiga pengguna iaitu pengurus pangkalan data, ahli farmasi dan pembantu ahli farmasi. Hanya pengguna yang sah sahaja boleh mengakses data melalui aplikasi PMS. Pengurus pangkalan data akan mengawal backup and recovery clan menguruskan hak keistimewaan pengguna untuk melihat aplikasi sistem tersebut. Selain itu, ahli f m a s i bertugas untuk menguruskan rekod jualan ubat-ubatan selain menggunakan pemilih ubat-ubatan yang sesuai untuk gejala penyakit tertentu. Tambahan lagi, tugas pembantu ahli farrnasi adalah untuk menguruskan tempahan, bayaran dan maklumat pembekal. Dengan kata lain, aplikasi PMS lebih terturnpu kepada pengurusan pangkalan data untuk ubat-ubatan selain menguruskan backup and recovery untuk setiap rekod &lam pangkalan data. Metodologi sistem ini ialah kitaran hidup pembangunan sistem dan kitaran pangkalan data. Kajian telah dibuat berdasarkan sistem semasa yang manual dan sernua kenyataan masalah clan keperluan sistern telah dikenal pasti. Tambahan lagi, PMS ialah tiga bahagian sistem senibina yang melibatkan bahagian pengguna, bahagian aplikasi atau bahagian pemiagaan dan bahagian pangkalan data. Ruang antara-muka untuk PMS telah di lakar berdasarkan keperluan pasaran. Distributed Database Pharmacy Management System, (PMS) aka. membantu memperbaiki kebolehan dari situasi semasa dan mengatasi masalah yang dihadapi pada masa kini.

TABLE OF CONTENTS

CHAPTER SUBJECT

DECLARATION

DEDICATION

ACKNOWLEDGEMENTS

ABSTRACT

ABSTRAK

TABLE OF CONTENTS

LIST OF TABLES

LIST OF FIGURES

LIST OF ABBREVIATIONS

LIST OF APPENDICES

LIST OF ATTACHMENTS

CHAPTER I INTRODUCTION

1.1 Project Background

1.2 Problem Statements

1.3 Objective

1.4 Scope

1.5 Project Significance

1.6 Expected Output

1.7 Conclusion

PAGE

ii

iii

iv

v

vi

vii

xi

xiii

XV

xvii

xviii

CHAPTER I1 LITERATURE REVIEW AND

PROJECT METHODOLOGY

2.1 Introduction

2.2 Facts and Findings

2.2.1 Domain

2.2.2 Existing System

2.2.3 Technique

2.3 Project Methodology

2.4 Project Requirements

2.4.1 Software Requirements

2.4.2 Hardware Requirements

2.4.3 Network Requirements

2.5 Project Schedule and Milestone

2.6 Conclusion

CHAPTER I11 ANALYSIS

3.1 Introduction

3.2 Problem analysis

3.3 Requirement Analysis

3 -3.1 Data Requirement

3.3.2 Functional Requirement

3.3.3 Non- Functional

Requirement

3.3.4 Others Requirement

3.4 Conclusion

CHAPTER IV DESIGN

4.1 Introduction

4.2 High-Level Design

4.2.1 System Architecture

viii

4.2.2 User Interface Design

4.2.2.1 Navigation Design

4.2.2.2 Input Design

4.2.2.3 Output Design

4.2.3 Conceptual and Logical

Database Design

4.3 System Architecture

4.3.1 Software Design

4.3.2 Physical Database Design

4.4. Conclusion

CHAPTER V IMPLEMENTATION

5.1 Introduction

5.2 Software Development

Environment Setup

5.3 Database Implementation

5.4 Software Configuration

Management

5.4.1 Configuration Environment

Setup

5.4.2 Version Control Procedure

5.5 Implementation Status

5.6 Conclusion

CHAPTER VI TESTING

6.1 Introduction

6.2 Test Plan

6.2.1 Test Organization

6.2.2 Test Environment

6.2.3 Test Schedule

6.3 Test Strategy

6.3.1 Classes of tests

6.4 Test Design

6.4.1 Test Description

6.4.2 Test Data

6.5 Test Result and Analysis

6.6 Conclusion

CHAPTER VII PROJECT CONCLUSION

7.1 Observation on Weaknesses and

Strengths

7.2 Propositions for Improvement

7.3 Contribution

7.4 Conclusion

REFERENCES

BIBLIOGRAPHY

APPENDICES

LIST OF TABLES

TABLE TITLE PAGE

Summary for the Functions use in PSv2.0, PPMS, NRx,

Primecare and PMS (proposed system)

Hardware Requirements

Project Schedule

Data Requirement for Login

Data Requirement for Staff

Data Requirement for Medicine

Data Requirement for Symptom

Data Requirement for Orders

Data Requirement for Sales

Data Requirement for Supplier

Data Requirement for Payment

Software Requirement for PMS application

Hardware Specification

Network Requirement

List of All Medicine Output Design

DBMS for MySQL Database

Version Release List for Login Module

Version Release List for Medicine Management

Module

Version Release List for Backup Module

Version Release List for Recovery Module

Version Release List for Maintain Database

(Transaction Log) Module

Version Release List for Integration

Implementation Status Schedule for PMS application

User and Task for the Testing Phase

Test Environment

Test Schedule for PMS Testing Process

Test Cases Form for User Registration and Login

Module

Test Cases Form for Backup Module

Test Cases Form for Recovery Module

Test Cases Form for Medicine Management Module

Test Cases Form for Transaction Log Module

Test Data for Staff Management Process

LIST OF FIGURES

FIGURE TITLE PAGE

2.1 Website portal of Pharmacy System v2.0 (admin site)

2.2 Website portal of Pharmaserv Pharmacy Management

System (PPMS)

2.3 NRx Pharmacy Management Inventory's Screenshot

2.4 Primecare Pharmacy Management Patient Information's

Screenshot

The SSADM Stages 1 Modules

Sashimi Model

Modified Waterfall Model (National Instruments

Database Life Cycle, DBLC

Summary of Activities in the Database Initial Study

Context Diagram for Manual System

DFD level 0 for Manual System

DFD level 1 for Medicine Management Module

DFD level 1 for Order List Module

DFD level 1 for Selling Process Module

Context diagram for proposed system, PMS

DFD level 0 for proposed system, PMS

DFD level 1 for Registration and Login Module

DFD level 1 for Backup Module

DFD level 1 for Recovery Module

DFD level 1 for Medicine Management Module

DFD level 1 for Maintain Database (Transaction Log)

Module

Distributed Database Architecture

Navigation Design for PMS

Successful Login Message Box

Error Handling Dialog Box

Entity Relationship Diagram, ERD for PMS

User View for PMS application

System Architecture of PMS

Overview of Software Development and Hardware

Environment for Pharmacy Management System

Software Environment Development Setup for PMS

Setting Code to Start the Database Connection and

Services

Assigning admin login

Configuration Environment Setup

Flow to Version Control Procedure

LIST OF ABBREVIATIONS

PMS

DBMS

SSADM

PHP

DFD

DBLC

ERD

RAM

PSM

AJK

CSS

GUI

0s

NF

PK

FK

RDBMS

SQL

DDL

Pharmacy Management System

Database Management System

Structured Systems Analysis and Design Methodology

Personal Home Page

Data Flow Diagram

Database Life Cycle

Entity Relationship Diagram

Random Access Memory

Projek Sarjana Muda

Ahli Jawatan Kuasa

Cascade Style Sheet

Graphical User Interface

Operating System

Normal Form

Primary Key

Foreign Key

Relational Database Management System

Structured Query Language

Data Definition Language

DBA

DCL

FTP

TCP

IP

Database Administrator

Data Control Language

File Transfer Protocol

Transmission Control Protocol

Internet Protocol

LIST OF APPENDICES

APPENDIX TITLE

PRE - DESIGN

DATA DICTIONARY

NORMALIZATION

PSEUDO CODE OF PMS APPLICATION

DATA DEFINITION LANGUAGE (DDL)

CONFIGURATION ENVIRONMENT SETUP OF

PMS APPLICATION

TEST CASE

USER MANUAL

PAGE

LIST OF ATTACHMENTS

ATTACHMENT TITLE

1.1 LOG BOOK

1.2 PROPOSAL FORM

1.3 GANTT CHART

PAGE

CHAPTER I

INTRODUCTION

1.1 Project Background

Most of the pharmacies nowadays, like to use file system because it is a

traditional way and peoples are comfort with that way. But today, the technologies

has changes a lot and because of that almost all the application in the world prefer to

use computer as their stored information place. More recently, the pharmacy

management is focused on storing stock information such as types of medicines,

price, medication review and drug information. Currently, to keep track on sales

transaction such as stock and order information, manual recording system is used by

the pharmacist. This system has many disadvantages because the pharmacist always

busy with the sales transaction rather than managing the patient who comes to the

pharmacy.

In case of that, the Pharmacy Management System (PMS) will be developed

based on the manual system in the real situation in a pharmacy. Furthermore, it is a

web based application where user must open a web browser such as Internet Explorer

or Mozilla Firefox in their computer and type the address of this Pharmacy

Management System in the address bar of the web browser. This system has security

issues such as the validation for username and password to prevent the intruder from

entering the system. Moreover, this system has two levels of user, which are

pharmacist, pharmacist assistant and database administrator. The pharmacist can

view all the information in the pharmacy such as medicine information, staff

information, supplier and order information. The assistant pharmacist can view and

the medicine information, supplier and order information. Moreover,

database administrator's job is to handle the backup and recovery process besides

managing the staff information. This application will be using Hypertext

PreProcessor (PHI?), Apache HTTP Server and MySQL as the database.

1.2 Problem Statement

The problem statements are:-

a) The file system is less secure because the system has no user administration

level and is an unauthorized system where anyone can change the information

without notice of everybody.

b) The file system does not efficient enough for the pharmacist to do their work

because wasting of time to search the desired information such as the medicine

information.

c) Besides that, the data is difficult to search and sometimes it creates duplication

because redundant data in some part of information such as in supplier

information.

d) Moreover, the existing system cannot recover data to prevent lost of data.

1.3 Objective

Objectives outline the actions that will be taken to solve the problems that are

stated in the previous section. The following are the objectives for this project:

a) To develop a web based application with a high security level for the pharmacy

with user authentication and authorization.

b) To ease searching capability for retrieval of data.

c) To apply distributed database in web based application.

d) To increase the time performance in managing the medicine inventory.

1.4 Scope

The focus of this paper is on Pharmacy Management System, PMS which is an

online system that manages the order and medicine information. In this section, the

swpe will be explained in terms of users, function and technologies.

1.4.1 Scope of System Users

There are three users in these systems which are pharmacist, pharmacist

assistant and database administrator.

a) Pharmacist

The pharmacist will be responsible on handling the manipulation aspects of

approving the suppliers and can only view the medicine, stock and sales records.

b) Assistant pharmacist

The assistant pharmacist will be responsible on managing the data in order

details, medicine information and stock's records.

c) Database administrator

The database administrator's job is to make sure that all data is safe by

handling the backup and recovery for all information transferred in the PMS.

1.4.2 Scope of Functions

This project will include five modules which consist of login module, backup

module, recovery module, medicine management module and maintain the database

(transaction log).

a) Login Module

Only an authorized user can login into the system by providing their correct

username and password to access the system.

b) Backup Module

This backup will be maintained by database administrator. Furthermore, this

module is used to make sure that all data in the tables is copied to other locations.

The database administrator only has to click on backup button then all the data will

be backups automatically.

c) Recovery Module

This recovery module involves in restoring the backup tables or database into

a specific location.

d) Medicine Management Module

This module provided medicine information, such as medicine id, name, price

per unit and expiry date. The system can add, update or delete the medicine

information.

e) Maintain the database (transaction log)

The database is maintained by database administrator (DBA) and the user

privilege will be controlled by the DBA. The data that is inserted or updated will be

recorded automatically in the tables of database by using trigger.

1.4.3 Scope of Technologies

Given that PMS is an online system, so the technologies used will be web-

based tools such as:

a) Apache Web Server Version 2.2.4

b) Hypertext Preprocessor (PHP) Version 5.2.3 as server side scripting language

a) MySQL Database Version 5.0.45

b) phpMyAdmin Database Manager Version 2.10.2

c) JavaScript as client side scripting language

d) AppServ Version 2.5.9 for Windows

1.5 Project Significance

For project significance, it will be explain based on user level such as database

administrator and pharmacist in the pharmacy. PMS provides backup and recovery to

ease the work of database administrator. Besides that, this project will help to ease

the staff works in the pharmacy and increase the work performance of the staff in

case of PMS's usability. The pharmacies can spend more time in serving the

customer who comes to the pharmacy and because of that the pharmacy will get

more attention to the public.

In addition, this project is better compared to manual system which using files

system to keep the sales record. It is because of the security issues that is

implemented in this system will help to increase security level in maintaining the

records information. Besides that, in terms of accessibility the data will be remains

save because of the different level of users had been applied in this project.

Moreover, this project is a dynamic and interactive web-based because it has

faster access through database to get the desired information. In case of that, the user

can get their desired information within a seconds or minutes rather than waiting for

a long time period. Additionally, the web page that will be created has a beautiful

interface and always following the standard documentation.