GRADUATE SCHOOL OF BUSINESS

INTERNATIONAL ISLAMIC UNIVERSITY ISLAMABAD

PROJECT ON DATA BASE

MANAGEMENT

PREPARED BY:
AAMIR HAYAT
& CLASS MATES

STUDENTS OF INTERNATIONAL ISLAMIC

UNIVERSITY ISLAMABAD
DATABASE OF SUNNY TRUST, PAKISTAN.

Database Management System.

Department of Management Sciences,

International Islamic University, Islamabad.
2
 Contents:
Chapter Page No.
Chapter 1:
Introduction:
1.1 Introduction to Organization: 3
1.2 Locations: 4
1.3 Departments: 4
1.4 Entity Classes: 5
1.5 Enterprise Data Model: 6
1.5.1 BASIC ERD. 6
1.5.2 PLANNING MATRIX: 7
Chapter 2:
Existing System:
2.1 Existing System: 8
2.1.1 Components Of Existing System: 8
2.1.2 Drawbacks: 8

Chapter 3:
Proposed System:
3.1 Proposed System: 9
Chapter 4:
Design:
4.1 System Design: 10
4.1.1 Organizational Chart Of Hospital: 10
4.1.2 Business Functions: 11
4.1.2.1 Hospital Management. 11
4.1.3 Data Flow Diagram: 11
4.2 Database Design: (Conceptual Data Model) 12
4.2.1 Entities: 12
4.2.2 Attributes: 12
4.2.3 Keys: 12
4.2.4 Relationships 13
4.2.5 Cardinalities 13
4.2.6 Business Rules 13
4.2.6.1 Entity Integrity 13
4.2.6.2 Domain 13
4.2.6.3 Triggering Operations 14
4.3 Logical Data Model: 15
4.3.1 Representing Entities 15
4.3.2 Representing Relationship 15
4.3.3 Data Dictionary 18
4.3.4 Normalization 19
Chapter 5:
Implementation. 20

3
Appendix A: 22
Detailed ERD.
Appendix B: 24
Data Flow Diagram.
Appendix C: 26
Screen Shots.
Appendix D: 32
Reports Printouts.

4
CHAPTER 1

INTRODUCTION:

1.1 INTRODUCTION TO ORGANIZATION:

The prevailing drug abuse problem in Pakistan is of colossal magnitude

(estimated 4.3 million addicts), very complex and varied. It was dramatized in the
early 1980s with the explosive spread of heroin culture among all social classes,
particularly the urban youth. The nature of problem has evolved with changing
supply-demand and availability of new types of drugs. Nonetheless, drug abuse
remains widespread and socially destructive since past twenty years. A single
addict usually affects lives of 15-25 extended family members, besides society at
large. Given ineffective enforcement measures, hence assured supply of various
Norco-drugs, drug abuse will tragically persist for the third decade into the 21st
century.

The prevailing drug abuse problem in Pakistan is not only colossal in

magnitude (estimated over 4 million addicts) but very complex, varied and
changing in nature. It was dramatized in the early 1980's with the rapid spread of
primarily heroin culture among practically all the social classes including, notably,
the student community and the urban youth.

The treatment and rehabilitation facilities for drug addicts in Pakistan

remain inadequate and mostly unsatisfactory. Thus high relapse rates often
promote the cynical view that drug addiction is incurable. Yet Sunny Trust
Pakistan (Regd.)/Addiction Treatment Centre stands out as a unique experiment in
the effective curing of drug addiction based on homeopathic medication and a
comprehensive system of self-discipline, spiritual training and socio-psychological
rehabilitation.

Founder Sunny Trust Pakistan Dr. Munawar Fiaz Sunny and his dedicated
team combine a decade long experience in curing over 4000 drug addicts
(including patients from foreign countries), based on the Sunny methodology. The
process of their cure involves reclaiming the addicts from the stranglehold of
drugs and then systematically harnessing of their inherent God-given attributes,
towards a positively transformed life. As Dr. Sunny has painstakingly
demonstrated, drug addiction is a convincingly curable condition with selective
homeopathic medicinal interventions as part of a more comprehensive healing
process of the body, mind and soul.

5
1.2 LOCATIONS:

1.2.1 ISLAMABAD OFFICE MURREE ROAD, BARA KAHU

ISLAMABAD.

The 50 bed Islamabad Centre, established in 1995, remains

operative to full capacity. It provides an open, clean and
healthy environment for drug abuse treatment and
rehabilitation.

1.2.2 FAISALABAD OFFICE MAIN BAZAR, LIAQAT TOWN.

.The 30-bed Sunny Trust Faisalabad facility, established in

March 1992, is presently under reorganization.

1.3 DEPARTMENTS:

1.3.1 PATIENT MANAGEMENT:

This department manages the admissions, stay, and discharge

of patients in the hospital.

1.3.2 CLINICAL SERVICES:

This department is responsible for all the extra services

provided to the patients such as pharmacy, X-Rays,
Ultrasounds and psychiatric tests.

ACCOUNTS AND FINANCIAL MANAGEMENT:

This department manages the finances and accounts. It deals

with all the cash, receipts, payments etc.

INVENTORY MANAGEMENT:

This department contains all aspects about inventory such as

medicines, equipments, furniture etc.

PAYROLL MANAGEMENT:

it deals with the payment of salary of the entire staff of the

hospital.

6
1.4 ENTITY CLASSES:

1. PATIENT.
2. EMPLOYEE.
2.1. DOCTOR.
2.2. NURSE.
2.3. ACCOUNTANT.
2.4. PURCHASER.
2.5. DAILY WAGES.
3. BILL.
4. MEDICINE.
5. VENDOR.
6. PATIENT HISTORY.

7
1.5 ENTERPRISE DATA MODEL:
1.5.1 BASIC ERD.

PATIENT
BILL LAB TEST
HISTORY

Has

Pays Has

PATIENT

Treats
Registers

DOCTOR
NURSE

CLERCK

Gets Gets

Gets BILL

SALARY

Paid

PURCHASE
MEDICINE VENDOR
R

Purchase Provides

FIG. 2 BASIC ERD OF SUNNY TRUST, PAKISTAN.

8
 1.5.2 PLANNING MATRIX:

HOSPITAL P D N C M V P B P L
MANAGE- A O U L E E U I A
T C R E D N R L H B
MENT.
I T S R I D C L I
E O E C C O H S T
N R K I R A T E
T N S O S
E E R T
R Y

PATIENT
REGISTRATION

PATIENT STAY.

PATIENT
DISCHARG.

PHARMACY.

PATIENT
TREATING.

PATIENT
MONITORING.

GENERAL
ACCOUNTING.

BILL PATIENT.

INVENTORY
MANAGEMENT.

SALARY
PAYMENT.

FIG. 3 PLANNING MATRIX OF SUNNY TRUST, PAKISTAN.

9
CHAPTER 2

EXISTING SYSTEM:

2.1 EXISTING SYSTEM:

The existing system of Sunny Trust, Pakistan is maintained

manually on registers. This system contains all information about patients, clinical
services, accounts management, inventory management and payroll system.

2.1.1 COMPONENTS OF EXISTING SYSTEM:

 Registration of Patients.
 Patient Fee.
 Staff Record.
 Payments.
 Purchases.
 Medicine.
 Vendor Information.

2.1.2 DRAWBACKS:

 Low Efficiency.
 Low Security.
 Redundancy.
 Inconsistency.
 More Chances of Errors.
 Loss of Time.
 Wastage of Storage.
 Low Sharing.

10
CHAPTER 3

PROPOSED SYSTEM:

3.1 PROPOSED SYSTEM:

The system we are going to create will be computerized. All data

will be saved in computer files. It is hoped that the proposed system will
provide fast, accurate and automated data collection to all the departments.
Some general features of the proposed system are as follows:

 Efficiency.
 Controlled Redundancy.
 Error Free.
 Accuracy.
 Flexibility.
 User Friendly.
 Consistency.
 Full Sharing of Data.
 Controlled Security.

11
CHAPTER 4

DESIGN:

4.1 SYSTEM DESIGN:

4.1.1 ORGANIZATIONAL CHART OF HOSPITAL:

OWNER

GENERAL
MANAGER

HEAD RESIDENT
ACOUNTANT OFFICER

ACOUNTANT PATIENT

PURCHASER DOCTOR

INVENTORY NURSE

FIG. 1 ORGANIZATIONAL CHART OF SUNNY TRUST, PAKISTAN.

12
4.1.2 BUSINESS FUNCTIONS:

4.1.2.1HOSPITAL MANAGEMENT.

4.1.2.1.1 PATIENT MANAGEMENT.

4.1.2.1.1.1 PATIENT REGISTRATION.
4.1.2.1.1.2 PATIENT STAY.
4.1.2.1.1.3 PATIENT DISCHARG.

4.1.2.1.2 CLINICAL SERVICES.

4.1.2.1.2.1 PHARMACY.
4.1.2.1.2.2 PATIENT TREATING.
4.1.2.1.2.3 PATIENT MONITORING.

4.1.2.1.3 ACCOUNTS AND FINANCE.

4.1.2.1.3.1 BILL PATIENT.
4.1.2.1.3.2 GENERAL ACCOUNTING.

4.1.3 DATA FLOW DIAGRAM:

Please consult Appendix B Page No.

13
4.2 DATABSE DESIGN: (CONCEPTUAL DATA MODEL)

The conceptual data model prepared for the project has the following features.

4.2.1 ENTITIES:

We have the following entities in our project:

PATIENT, P HISTORY, BILL, MEDICINE, VENDOR, EMPLOYEE,
DOCTOR, NURSE, VENDOR, ACCOUNTANT, DAILY WAGES,
PURCHASER

4.2.2 ATTRIBUTES:

Following are the attributes of the entities:

1. PATIENT (P No, P Name, P Add, P Phone, P Relative)

2. P HISTORY (P No, D. O. Add, D. O. Leaving, Room No, Disease)
3. BILL (B No, Amount, Description)
4. TEST (T ID, T Name)
5. MEDICINE (M No, M Name)
6. VENDOR (V No, V Name, V Add)
7. EMPLOYEE (E No, E Name, E Type)
a. DOCTOR (E No, Salary, Specialization, D. O. Joining,
Timing)
b. NURSE (E No, Salary, Timing, D. O. Joining)
c. PURCHASER (E No, Salary)
d. DAILY WAGES (E No, Rate)
e. ACCOUNTANT (E No, Salary, D. O. Joining)

4.2.3 KEYS:

ENTITIES PRIMARY KEYS

PATIENT P No
P HISTORY P No, D. O. Add
BILL B No
TEST T ID
MEDICINE M No
VENDOR V No
EMPLOYEE E No
DOCTOR E No
NURSE E No

14
 PURCHASER E No
DAILY WAGES E No
ACCOUNTANT E No

4.2.4 RELATIONSHIPS

 PATIENT has a relationship with P HISTORY, BILL, TEST, MEDICINE,

DOCTOR.
 P HISTORY has a relationship with the PATIENT.
 BILL has a relationship with PATIENT.
 TEST has a relationship with PATIENT.
 MEDICINE have a relationship with VENDOR.
 EMPLOYEE provides services.
o DOCTOR
o NURSE
o PURCHASER
o DAILY WAGES.
o ACCOUNTANT

4.2.5 CARDINALITIES

• One PATIENT can has only one BILL,DOCTOR and one PATIENT can has
one or more P HISTORY as well as one PATIENT can has no or more TEST,
MEDICINE.
• There can be one or more P HISTORY against a PATEINT.
• One BILL can have no or more PATIENTS and one PATIENT can have only
and only one type of BILL
• TEST has a zero to many relationship with the PATIENT and PATEINT has
also zero to many relationship with TEST.
• MEDICINE has a zero to many relationship with the VENDOR and
VENDOR has also zero to many relationship with MEDICINE.

4.2.6 BUSINESS RULES

4.2.6.1 ENTITY INTEGRITY

Each entity has a separate primary key satisfying the entity

integrity.

15
4.2.6.2 DOMAIN

PATIENT Patient has only one type and they are Resident Patient.
BILL Three types of bill. Rich bill, middle bill and poor bill.
EMPLOYEE doctor, nurse, purchaser, daily wages, accountant.

4.2.6.3 TRIGGERING OPERATIONS

User Rule  Time of arrival must be before departure.

Event  Departure of PATIENT.
Entity  PATIENT.
Event  Prepare bill.
Entity  BILL.
Conditions  If Arrival is after Departure.
Action  Reject.

16
4.3 LOGICAL DATA MODEL:

4.3.1 REPRESENTING ENTITIES

1. PATIENT (P No, P Name, P Add, P Phone, P Relative)

2. P HISTORY (P No, D. O. Add, D. O. Leaving, Room No, Disease)
3. BILL (B No, Amount, Description)
4. TEST (T ID, T Name)
5. MEDICINE (M No, M Name)
6. VENDOR (V No, V Name, V Add)
7. EMPLOYEE (E No, E Name, E Type)
f. DOCTOR (E No, Salary, Specialization, D. O. Joining,
Timing)
g. NURSE (E No, Salary, Timing, D. O. Joining)
h. PURCHASER (E No, Salary)
i. DAILY WAGES (E No, Rate)
j. ACCOUNTANT (E No, Salary, D. O. Joining)

4.3.2 REPRESENTING RELATIONSHIP

Relationship Between PATIENT and BILL:

PATIENT
P No P Name P Add P Phone P Relative

BILL
B No Amount Description

PAYMENT
P No B No Amount

17
 Relationship Between PATIENT and TEST:

PATIENT
P No P Name P Add P Phone P Relative

TEST
T ID T Name

RESULT
P No T No Date Result

Relationship Between PATIENT, DOCTOR and MEDICINE:

PATIENT
P No P Name P Add P Phone P Relative

MEDICINE
M No M Name

MEDICATION
P No M No D. O. Starting D. O. Ending E No

18
 Relationship Between VENDOR and MEDICINE:

VENDOR
V No V Name V Add

MEDICINE
M No M Name

SUPPLY
V No M No Date Quantity

19
4.3.3 DATA DICTIONARY

Data Primary
Entity Attributes Width Unique Format
Type Key
PATIENT P No. Number 5 Y Y NNNNN
PATIENT P Name Text 20 N N
PATIENT P Add Text 50 N N
PATIENT P Phone Text 15 N N
PATIENT P Relative Text 20 N N

P HISTORY P No. Number 5 Y Y NNNNN

dd-mm-
P HISTORY D. O. Add Date/Time 10 N N
yy
D. O. dd-mm-
P HISTORY Date/Time 10 N N
Leaving yy
P HISTORY Room No. Number 2 N N NN
P HISTORY Disease Text 50 N N

BILL B. No Number 5 Y Y NNNNN

BILL Amount Number 10 N N
BILL Description Text 50 N N

TEST T ID Number 5 Y Y NNNNN

TEST T Name Text 20 N N

MEDICINE M No. Number 10 Y Y NNNNN

MEDICINE M Name Text 15 N N

VENDOR V No. Number 10 Y Y NNNNN

VENDOR V Name Text 20 N N
VENDOR V Add Text 50 N N

EMPLOYEE E No. Text 5 Y Y NNN-X

EMPLOYEE E Name Text 20 N N
EMPLOYEE E Type Text 10 N N

DOCTOR E No Text 10 Y Y NNN-X

DOCTOR Salary Number 5 N N NNNN
DOCTOR Specialization Text 20 N N
dd-mm-
DOCTOR D. O. Joining Date/Time 10 N N
yy

20
 hh-mm-
DOCTOR Timing Date/Time 10 N N
ss

NURSE E No. Text 10 Y Y NNN-X

NURSE Salary Number 5 N N
hh-mm-
NURSE Timing Date/Time 10 N N
ss
dd-mm-
NURSE D. O. Joining Data/Time 10 N N
yy

PURCHASER E. No. Text 10 Y Y NNN-X

PURCHASER Salary Number 5 N N

DAILY WAGES E No. Text 10 Y Y NNN-X

DAILY WAGES Rate Number 5 N N

ACCOUNTATNT E No. Text 10 Y Y NNN-X

ACCOUNTATNT Salary Number 5 N N
dd-mm-
ACCOUNTATNT D. O. Joining Date/Time 10 N N
yy

4.3.4 NORMALIZATION

1NF
All the tables are already in 1NF as:
• There were repeating groups in PATIENT table TEST and BILL.
• There are no Multi-valued attributes.

2NF
All the tables are already in 2NF as :
• There is no Partial Functional Dependency in any table.
• There are composite primary keys in Patient History , Result ,
Payment , Medication , Supply.
• In these tables no non-key attribute depends on a part of composite
Primary key.

3NF
All the tables are already in 3NF as :
• There is no Transitive Dependency in any table.
• No two Non-key attributes depends on each other but on primary
keys.

21
CHAPTER 5

IMPLIMENTATION:
We implemented this software using the MS Access tool. The soft copy of the
software has been attached.

22
APPENDIX A

23
24
APPENDIX B

Level Zero.

PATIENT

0
Admits in Hopital Treatment Patient Concern Data
Process

PATIENT

Employee Concern Data

EMPLOYEE

25
Level One
Patient Related Data Patient
PATIENT History

Admits in Hospital Patient

Doctor, Nurse Concern Daata
Schedule

Patient Registration

Treatment
Process After Recovering

Patient Payment of Bill

Discharge

PATIENT
ACCOUNTANT

26
APPENDIX C

Screen Shots.

27
28
29
30
31
32
33