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 Chapter 1: Introduction: 1.1 Introduction to Organization: 1.2 Locations: 1.3 Departments: 1.4 Entity Classes: 1.5 Enterprise Data Model: 1.5.1 BASIC ERD. 1.5.2 PLANNING MATRIX: Chapter 2: Existing System: 2.1 Existing System: 2.1.1 Components Of Existing System: 2.1.2 Drawbacks: Chapter 3: Proposed System: 3.1 Proposed System: Chapter 4: Design: 4.1 System Design: 4.1.1 Organizational Chart Of Hospital: 4.1.2 Business Functions: 4.1.2.1 Hospital Management. 4.1.3 Data Flow Diagram: 4.2 Database Design: (Conceptual Data Model) 4.2.1 Entities: 4.2.2 Attributes: 4.2.3 Keys: 4.2.4 Relationships 4.2.5 Cardinalities 4.2.6 Business Rules 4.2.6.1 Entity Integrity 4.2.6.2 Domain 4.2.6.3 Triggering Operations 4.3 Logical Data Model: 4.3.1 Representing Entities 4.3.2 Representing Relationship 4.3.3 Data Dictionary 4.3.4 Normalization Chapter 5: Implementation.
3
Page No. 3 4 4 5 6 6 7 8 8 8
9 10 10 11 11 11 12 12 12 12 13 13 13 13 13 14 15 15 15 18 19 20
Appendix A: Detailed ERD. Appendix B: Data Flow Diagram. Appendix C: Screen Shots. Appendix D: Reports Printouts.
22 24 26 32
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 HISTORY
BILL
LAB TEST
Has Pays
Has
PATIENT
Treats Registers
DOCTOR NURSE CLERCK
Gets
Gets Gets
BILL
SALARY Paid
PURCHASE R
MEDICINE
Purchase
VENDOR
Provides
FIG. 2 BASIC ERD OF SUNNY TRUST, PAKISTAN.
8
1.5.2
PLANNING MATRIX: P A T I E N T
HOSPITAL MANAGEMENT.
D O C T O R
N U R S E
C L E R C K
M E D I C I N E
V E N D O R
P U R C H A S E R
B I L L
P H I S T O 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
L A B T E S T
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 ACOUNTANT
RESIDENT 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. 2. 3. 4. 5. 6. 7.
PATIENT (P No, P Name, P Add, P Phone, P Relative) P HISTORY (P No, D. O. Add, D. O. Leaving, Room No, Disease) BILL (B No, Amount, Description) TEST (T ID, T Name) MEDICINE (M No, M Name) VENDOR (V No, V Name, V Add) 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 HISTORY BILL TEST MEDICINE VENDOR EMPLOYEE DOCTOR NURSE
P No P No, D. O. Add B No T ID M No V No E No E No E No 14
PURCHASER DAILY WAGES ACCOUNTANT
E No E No 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 • • • • •
4.2.6
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.
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 User Rule Event Entity Event Entity Conditions Action
TRIGGERING OPERATIONS
Time of arrival must be before departure. Departure of PATIENT. PATIENT. Prepare bill. BILL. If Arrival is after Departure. Reject.
16
4.3
LOGICAL DATA MODEL: 4.3.1 REPRESENTING ENTITIES 1. 2. 3. 4. 5. 6. 7.
PATIENT (P No, P Name, P Add, P Phone, P Relative) P HISTORY (P No, D. O. Add, D. O. Leaving, Room No, Disease) BILL (B No, Amount, Description) TEST (T ID, T Name) MEDICINE (M No, M Name) VENDOR (V No, V Name, V Add) 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
BILL B No
Amount
Description
B No
Amount
PAYMENT P No
17
P Relative
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
D. O. Ending
E No
MEDICINE M No
P No
M Name
MEDICATION M No
D. O. Starting
18
Relationship Between VENDOR and MEDICINE: VENDOR V No
V Name
V Add
MEDICINE M No
M Name
SUPPLY V No
M No
Date
19
Quantity
4.3.3 DATA DICTIONARY
Entity
Attributes
Data Type
Width
Primary Unique Format Key
PATIENT PATIENT PATIENT PATIENT PATIENT
P No. P Name P Add P Phone P Relative
Number Text Text Text Text
5 20 50 15 20
Y N N N N
Y N N N N
NNNNN
P HISTORY
P No.
Number
5
Y
Y
P HISTORY
D. O. Add
Date/Time
10
N
N
Date/Time
10
N
N
Number Text
2 50
N N
N N
NNNNN dd-mmyy dd-mmyy NN
P HISTORY P HISTORY
D. O. Leaving Room No. Disease
BILL BILL BILL
B. No Amount Description
Number Number Text
5 10 50
Y N N
Y N N
NNNNN
TEST TEST
T ID T Name
Number Text
5 20
Y N
Y N
NNNNN
MEDICINE MEDICINE
M No. M Name
Number Text
10 15
Y N
Y N
NNNNN
VENDOR VENDOR VENDOR
V No. V Name V Add
Number Text Text
10 20 50
Y N N
Y N N
NNNNN
EMPLOYEE EMPLOYEE EMPLOYEE
E No. E Name E Type
Text Text Text
5 20 10
Y N N
Y N N
NNN-X
DOCTOR DOCTOR DOCTOR
E No Salary Specialization
Text Number Text
10 5 20
Y N N
Y N N
NNN-X NNNN
DOCTOR
D. O. Joining
Date/Time
10
N
N
P HISTORY
20
dd-mmyy
hh-mmss
DOCTOR
Timing
Date/Time
10
N
N
NURSE NURSE
E No. Salary
Text Number
10 5
Y N
Y N
NURSE
Timing
Date/Time
10
N
N
NURSE
D. O. Joining
Data/Time
10
N
N
PURCHASER PURCHASER
E. No. Salary
Text Number
10 5
Y N
Y N
NNN-X
DAILY WAGES DAILY WAGES
E No. Rate
Text Number
10 5
Y N
Y N
NNN-X
ACCOUNTATNT ACCOUNTATNT
E No. Salary
Text Number
10 5
Y N
Y N
NNN-X
ACCOUNTATNT
D. O. Joining
Date/Time
10
N
N
4.3.4
NNN-X hh-mmss dd-mmyy
dd-mmyy
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
Admits in Hopital
0 Treatment Process
Patient Concern Data
PATIENT
Employee Concern Data
EMPLOYEE
25
Level One Patient History
Patient Related Data
PATIENT
Admits in Hospital
Patient Schedule
Doctor, Nurse Concern Daata
Patient Registration
Treatment Process
After Recovering
Patient Discharge
Payment of Bill
PATIENT ACCOUNTANT
26
APPENDIX C Screen Shots.
27
28
29
30
31
32
33