DATABASE MANAGEMENT SYSTEM PROJECT ON POLICE STATION (GULSHAN RAVI) PRESENTED TO SIR ZEESHAN AHMER PRESENTED BY FATIMA JEHANGIR BUSHRA WAJID SABAHAT ZULFIQAR FARIHA SATTAR NAVEED KOUSAR
46 41 56 42 44
MBA (SEMESTER 2) 2003-2005
INSTITUTE OF BUSINESS ADMINISTRARTION, UNIVERSITY OF THE PUNJAB, QUAID-E-AZAM CAMPUS, LAHORE.
ACKNOWLEDGEMENTS All praise for Almighty Allah for guiding us in hardships and all respect for Holy Prophet (Peace Be Upon Him) for showing us the light of truth. We pay special gratitude to our respected parents who have an important impact on our thinking and their advice and insights contributed to numerous improvements. We are very thankful to Sir Haseeb and all other coordinators in the computer lab who helped us during the extensive work of this assignment with patience when asked. Finally, we would like to thank our Course Instructor, Sir Zeeshan Ahmer for giving us this opportunity to learn things practically and whose guidance is a constant source of inspiration for us, which enabled us to accomplish the assignment.
TABLE OF CONTENTS INTRODUCTION FLOW OF SYSTEM DATA DICTIONARY PROBLEM DEFINITION STUDY OF SYSTEM PROBLEMS AND SUGGESTIONS ORGANIZATIONAL CHART
LOGICAL MODEL ENTITIES ATTRIBUTES OF ENTITIES RELATIONSHIPS AMONG ENTITIES ENTITY RELATIONSHIP DIAGRAM PROPOSAL FOR NEW SYSTEM
IMPLEMENTATION ON MS-ACCESS TABLES IN MS-ACCESS FORMS IN MS-ACCESS ENTITY RELATIONSHIP DIAGRAM QUERIES REPORTS
INTRODUCTION
INTRODUCTION The safety and protection of human rights has been remained the most important issue for every country because until and unless the rights of human beings are not protected, no country can imagine of its existence. It is the responsibility of every country’s government to protect the freedom and rights of all human beings with no discrimination so that every individual can lead his life with his own choice without violating the rules and regulations set by the government of that country. Although it is the government responsibility to safeguard human rights but to take care of human rights was not convenient for the government. So in order to fulfill this task, government formed a special platform for that purpose known as Police Station. In this way, the responsibility to protect the human rights was handed over to the police officers. Since then, the police officers have been working to provide peaceful environment to the citizens where they can work according to their wish. The police officers are expected to work with great diligence under difficult circumstances. Whenever a person faces some problem he goes to police station for help, then it is the police headache to help that person coming out of that problem. Police officers perform every kind of duty to protect the citizens’rights but generally four main objectives come under their duty which are: Traffic safety Crime reduction Violence prevention Drug abuse education
All these objectives are carried through the most important aspect that is display of dare.
The police officers are taught during their training that they have to show their maximum boldness and dare while performing their duty. Because they have to deal with the criminals some of which may be very powerful or strong but a police officer’s duty is to fight with criminals at any cost even without thinking of their lives. In Pakistan, police is also performing these duties and objectives but our system requires many improvements. People here usually remain afraid of police due to many reasons. They believe that police is not sincere with its duty and they might be caught in any serious problem if they go against with police. Besides its negative points, police also provides safety to citizens. It always remains steady for arresting any criminal who is a threat for the safety of society. After registering the FIR from any citizen, police starts its work and on that basis it arrests the criminals if proofs are found against them. Once the criminals are caught then police starts its investigation from the criminals. Torture is also employed to obtain confessions or information from criminals about a case. After getting all the proofs against the criminal, it is the duty of the police to present all the proofs honestly to the court so that the right man can get right punishment. Along with police, it is also the duty of every citizen to fully cooperate with the police for the purpose of providing all the necessary information to the police when needed. The true and right information provided by the people to police will help a lot in arresting the criminals who try to spoil the peaceful environment of society. Although facilities are provided to police to lead a good life but still the remuneration and training of police officers require substantial improvements. They perform hard duty as compared to other professionals but their remuneration is very low. They can not afford all the facilities of modern age for their family. This may result in negligence of their duty.
Along with low salary scale, facilities of modern technology such as computerized system of keeping records are not provided to police department which causes low efficiency. As it is the age of computers and all the organizations today use computers in maintaining their records, so this facility should also be given to police department in order to increase their efficiency and to save their time.
DATA FLOW DIAGRAM OF POLICE STATION
FLOW OF THE SYSTEM (FIR AGAINST PROPERTY) 1. FIR Logged 2. Assigned to an investigation officer ( Case diary opened). 3. Arrest of a person If (Yes) Verification of the person If (No) •
Warrant
•
Proclain offender
•
Entry in register 4.
4. Entry in Daily Diary. 5. Remand •
•
Physical remand -
Go to police station.
-
Lock up
-
Entry in daily dairy.
Judicial remand -
court appearance.
6. Investigation officer will make a challan
7. The challan is then signed by S.H.O. 8. Entry is made in Road Certificate. 9. Despatch the challan to prosecution branch. 10. Receipt of the challan is returned by prosecution branch, to Mohrer (Clerk) of the station.
NOTE In case of recovery, recovered case property will be handed back to the owner on
SUPERDARY (responsibility) ordered by competent court. 11. Responsibilities of the station are closed. 12. Prosecution branch will scrutinize the challan according to police Rule 27I, if challan is ok, it is submitted to the court after entry in register, else it is returned to the station . 13. Trail in court begin. After decision conviction slip will be send back to the station.
DATA DICTIONARY
DATA DICTIONARY MOHARAR He is a person who is responsible of keeping all the data records in a police station
SHO (STATION HOUSE OFFICER) A high ranking officer (ASI or above) who is in charge of a police station
WARRANT A legal document issued by a competent court that empowers the police about some legal matter
ROAD CERTIFICATE A certificate signed by the SHO before a challan is sent to the Prosecution Branch A certificate issued after the decision of a trial regarding the conviction of an n accused
PROSECUTION BRANCH A specialized police branch that scrutinizes the challan sent by the police station and correct loopholes in that case.
ASI Assistant Sub Inspector
SI Sub Inspector
SP Superintendent of Police
DSP Depute Pretending of Police
DIG Deputy Inspector General Of Police
IG Inspector General of Police
COMPLAINANT: A person who is seeking legal help form police station or he has some problem or complaints regarding the matters under the jurisdiction of police.
INFORMANT A person who brings the news of some criminal incident. He may be a passer by or a person by himself or may be a professional tout.
CASE DIARY A file in which daily detail of a case are written down by the investigating officer
DAILY DIARY A register in which all the events happening in a police station is record. It is updated on per day bases and is of vital importance in a traditional “Thana” culture.
F.I.R. (First Investigation Report) FIR is launched by a complainant and is usually written down by the Mohrar. It includes the details about the complaint and the initial actions are taken to insure the law and order situation.
PROBLEM STATEMEMT
Policemen are facing problem in recording their major data manually and in its updation and retrieval.
STUDY OF EXISTING SYSTEM Data is recorded in two ways in a Police Station: Manual Recording All the general complaints and reports (Theft, Robbery, Murder, etc.) are managed manually. Computerized Recording: Whereas the complaints against the theft of registered-vehicles and data keeping of driving licences is organized with the help of computer system
PROBLEMS AND SUGGESTIONS
PROBLEMS Police order 2002 as replaced Police Act 1861. The inherited problems have been carried on in the new Police Order 2002. Some of the century-long problems faced by the police internally and public are as follows: Burking of registration of cases. Police shirk frequent registration of cases to show their efficiency viz-à-viz escape from wrath of senior officers. Long duty house of Police Forces: Police officers are 24hrs-employee and gazetted holidays are not admissible to them. Due to long duty hours they are tired and as not allowed due to rest they become spleeny effecting Public Relation Salaries:
They are low paid government employees. The salaries are too less as compared to their authoritative powers. Because of non- balance in income and authority, they are tempted to accept gratification. Police lacks training to tackle the problem. Due to the increase in education or media-corporation, criminals have adopted most modern techniques for the commission of crime.
The increase in the population The increase in the population has out-numbered Police Force. Non-corporation of Public: Non-corporation of public in arresting the crimes: Police evade to join any investigation because of police attitude and criminals’ threat.
Manual recording or registering Finally the major problem faced by the police officers is that they have to make their major recordings manually in the registers. This takes a lot of time in storage, retrieval and updating of data.
SUGGESTIONS Following are the suggestions to overcome these major problems faced by the Police Force: Police officers may not be held responsible for increase in registration of cases or crimes on account of free of registration. They be allowed a holiday once in a week. The duty hours must be curtailed to not more than 10 hrs a day. Their pay scales be revised and enhanced, be provided maximum facilities to counter any offers or consideration. Strength needs increase or improvement. Public be Educated to discharge their responsibilities as a good citizen. Their recording system should be computerized so that officers will not face any problem in storage, retrieval and updating of data. Also it will save their cost and time
ORGANIZATIONAL CHART Provisional Police Officers (I.G)
Deputy Inspector General Of Police (D.I.G)
Superintendent Of Police (S.P)
Deputy Superintendent Of Police (D.S.P)
Assistant Superintendent Of Police (A.S.P)
Inspector (Is)
Sub- Inspector (S.I)
Assistant Sub- Inspector (A.S.I)
Head constable
Constable
PROPOSAL FOR IMPROVED SYSTEM What we suggest to them is that they should store and maintain their data in computerized form .It is easy to store huge information in computers than manually which will save their time and cost both. Through computers it will be easy for them to update any information and its retrieval. They should create DATABASE MANAGEMENT SYSTEM in order to maintain their records and should be given proper training in this regard to everyone.
LOGICAL MODEL
ENTITIES Entities are the element (person , place, object, event)that make up an organizational system, about which the organization wishes to maintain data.
Entities of the System Entities engaged in the police department regarding this project are Accused Case Outcome Investigation Officer ID Section of Law Complainant
ATTRIBUTES OF THE ENTITIES The various attributes of the entities are as follows
Accused Attributes include accused-id, name, father name, nic, and case-id.
Case Attributes include case-id, investigation officer-id, and case-detail.
Outcome Attributes include outcome-id, case-id, complainant-id, accused-id ,outcome detail, and investigation officer-id, section-id.
Investigation-Officer Attributes include investigation officer-id, name and case-id.
Section of law Attributes include section-id, case-id, and section of law.
Complainant Attributes include Complainant-id, name, father’s name, nic, address, and case-id, date-of-FIR.
RELATIONSHIPS Relationship is an association that describe the interaction between the entities. Relationship are of three types One to One One to Many Many to Many
Relationship between entities and the type of relationship Following type of relationship exist between the entities of the system. 1. There is one to many relationship between investigation officer and case because we assumed that one investigation officer can handle many cases. 2. There is one to many relationship between case and accused because many accused can be involved in one case. 3. There is one to one relationship between accused and outcome because there is only one outcome for each accused. 4. There is one to one relationship between complainant and outcome because there will be one outcome for each complaint by complainant. 5. There is one to one relationship between section of law and outcome because one section of law has one outcome or result. 6. There is one – one relationship between case and outcome because one case has only one outcome.
ENTITY RELATIONSHIP DIAGRAM Accused-id
Name
Complainant-
NI Father-name
Accused
1
Investigatio n officer-id
H Case-
1
H
M
Case-id
Accused -id
Case-
Outcome
Outcom e detail
1
Investigati on officer-
Case
1
M
Case detail
Have
H Name
1
1 Case-
1
M
Case-id
Father’ s
Name
H ave 1
H
Investigatio n officer-id
Investigation officer-id
H
1
M
complainant
Section of law Addres Section of law
1
Case-
NIC
Complainantid
IMPLEMENTATION ON MS ACCESS
TABLES
FIELD NAMES AND DATA TYPES The field names of all the tables used and their data type are given below.
Accused Field Name
Data Type
Accused-id Name Father’s Name NIC Case-id
Number Text Text Text Number
•
The primary key used in the accused table is accused-id.
•
The foreign key in the accused table is case-id from case table.
•
Field property of field NIC has been changed according to requirement. There is a change in input mask i.e. 00000-0000000-0
Case Field Name
Data Type
Case-id Case-detail Investigating-officer-id
Number Text Number
•
The primary key used in the table case is case-id.
•
The foreign key involved in this table is investigating-officer-id
Complainant Field Name
Data Type
Complainant-id Name Father’s Name NIC Address Case-id Date-of-FIR
Number Text Text Text Text Number Date/time
•
The primary key in this table is complainant-id
•
The foreign key is case-id
•
The field properties of field NIC has been changed according to requirements. There is a change in input mask i.e. 00000-0000000-0
•
The field properties of date of FIR is 99/99/0000
Investigation-officer Field Name
Data Type
Investigating-officer-id Name Case-id
Number Text Number
•
The primary key used in the table is investigation officer-id.
•
The foreign key on the investigation officer-id is case-id.
Outcome Field Name Outcome-id
Data Type Number
Case-id Complainant-id Accused-id Outcome-detail Section-id
Number Number Number Text Number
•
The primary key in this table is Outcome-id
•
The foreign keys are complainant-id,case-id, section-id and accused-id
Section of law Field Name
Data Type
Section-id Case-id Section-of-law
Number Number Number
• •
The primary key used in this table is secton-id Foreign key is case-id.
FORMS
ACCUSED Accused accused-id
name
father-name
NIC
case-id
1 2 3 4
ali akbar malik faisal zeeshan ahmad zahid badar
wajid ali naveed shahid ijaz ahmad badar hussain
12546-1254785-7 25478-3698547-5 25896-4789653-3 14789-6985725-3
1 2 3 4
Accused accused-id
name
father-name
NIC
case-id
5
shahid bashir
bashir ahmad
25789-2589634-3
5
CASE Case case-id
case-detail 1 mobile stolen 2 crime against property 3 murder 4 smuggling of arms 5 teasing a women
Investigating-officerid 2 3 1 4 5
COMPLAINANT complainant complainantfathername NIC id name 1 javaid akbar M. akbar 968572514783-9 2 shahzad shah khan 25478mughal 3698527-8 3 saif ali sattar 25879saleem 3697412-5 4 shahid salman 25478salman khan 3698524-2 5 rauf khalid khalid 36984aziz 2589632-1
address 15-c samnabad lahore 50-g gulshan ravi lahore 6-d multan road lahore 75-b tauheed park lahore 85-e samnabad lahore
INVESTIGATION-OFFICER
case- Date of id FIR 1 7/15/2004 2 6/16/2004 3 5/13/2003 4 12/4/2004 5
5/3/2004
investigation-officer investigating-officer-id name 1 adnan ahmad 2 azim khan 3 bilal ali 4 asif ahmad 5 babar javaid
case-id 3 1 2 4 5
OUTCOME outcome outcome-id case-id complainant-id Outcome-detail Accused-id Section-id 1 1 1 1 month jail 1 1 2 2 2 fine 150000 2 2 3 3 3 to be hanged 3 3 4 4 4 10 years jail and fine 5 crore 4 4 5 5 5 2 months jail 5 5
SECTION-OF-LAW
section-id 1 2 3 4 5
section-of-law section-of-law 381 402 302 433 354
Case-id 1 2 3 4 5
QUERIES
QUERY 1: Suppose D.I.G. wants to know that which investigation officer is handling which case, following query will be used to show all information in the table investigation –officer.
SELECT * FROM [investigation-officer]; Query1 investigating-officer-id
name
case-id
1 adnan ahmad
3
2 azim khan
1
3 bilal ali
2
4 asif ahmad
4
5 babar javaid
5
QUERY 2: If the data regarding the accused person Zahid Badar is required then DBMS can help in providing an easy access to the data by using following query.
SELECT * FROM accused WHERE name like 'zahid badar'; Query2 accused-id
name 4 zahid badar
father-name badar hussain
NIC 14789-6985725-3
QUERY 3:
case-id 4
If anyone wants to know the details regarding murder case i.e. details of accused person, case and investigation officer the following query will be used.
SELECT * FROM [CASE] INNER JOIN accused ON case. [Caseid]=accused. [Case-id] WHERE [case-detail] ='murder';
Query3 CASE.case- caseid detail
investigating- accusedofficer-id id
3 murder
1
name
fathername
accused.caseid
NIC
3 zeeshan ijaz 25896ahmad ahmad 4789653-3
3
QUERY 4: If the information regarding case id no.1 and 3 is required (this may be any number) following query will be used.
SELECT *FROM complainant WHERE [case-id] in (1, 3); Query4 complainantid
name
fathername
NIC
address
casedate-of-FIR id
1 javaid akbar
M. akbar
968572514783-9
15-c samanabad lahore
1 7 /15/2004
3 saif ali
sattar saleem
258793697412-5
6-d multan road lahore
3 5 /13/2003
REPORTS
REPORT: 1 This report regarding the whole data about accused will be presented to the officer on demand which contains the information like Accused-id Name Father-name National Identity Card Number Case-id
REPORT: 2 This report shows various results against various cases on demand.
REPORT: 3 This report shows the results when D.I.G. wants to know that which investigation officer is handling which case.
REPORT: 4 If the data regarding the accused person Zahid Badar is required then DBMS can help in providing an easy access to the data by using the above report no. 4
REPORT: 5 If anyone wants to know the details regarding murder case i.e. details of accused person, case and investigation officer the above report no. 5 will be used.
REPORT: 6 If the information regarding case id no.1 and 3 is required (this may be any number) above report no. 6 will be used.
