Law Enforcement Computer Applications in Crime Analysis Relational Databases in Everyday Life
Resources
Read/review the following resources for this activity:
Bruce, C. W., & Stallo, M. A. (2009). Better policing with Microsoft Office 2007. Southlake, TX: A.C.T. Now. ISBN 1439253285.
• Bruce & Stallo: Chapter 1 (pp. 15–57)
• Lecture
Introduction: Relational Databases in Everyday Life
Police databases have tables specific to police data, and most of us rarely encounter them, but we do encounter databases every day in our work, personal life, and entertainment options. This discussion will help us understand relational databases through the lens of the databases we use constantly.
Initial Post Instructions
Choose a database that you commonly use and see what you can figure out about the database: how many tables is it likely to have, and how are they related? What are the primary keys?
Law Enforcement Computer Applications in Crime Analysis
Lecture
________________________________________
Relational Databases and Database Design
Microsoft Access is a powerful relational database development tool. It is the most widely used desktop database application in the world. Now in its tenth version (Access 2013), Access was first released in 1992. Though somewhat limited in its capabilities when compared to recent versions, Access 1.0 revolutionized data management for many businesses and public agencies.
The power of Access derives from its integration of fairly easy-to-use database development and application development tools. Any database application, such as your records management or CAD system, requires two components: a database in which to store information and an application with which to enter, query, and search that data. Commercial systems are created with some combination of these two components—for instance, an Oracle database accessed through a C++ application (or “front end”); or a SQL Server database underneath an application written in Visual Basic.
Access is not designed to compete with either these more powerful database servers or these more robust programming languages. It instead offers a more simple solution for the professional who is trained and experienced in neither database server management nor application programming. With Access, the average person can develop a database and create forms, queries, and reports to enter, search, and display information. Many crime analysts use Microsoft Access to track crimes, offenders, and other information; or to create a “front end” for their antiquated, complicated, or otherwise inefficient records management systems.
Access has two primary uses for police agencies:
• To track and manage data about anything that for which the agency does not already have an application, or for which the agency has an inadequate application. If you do not have a full records management system (RMS), you may need an Access database to track arrests, citations, traffic accidents, known criminals, firearms licenses, sex offenders, Neighborhood Watch members, grants, department inventory, known graffiti tags, and a host of other things.
• To query and report on large datasets received from other agencies or systems. Agencies may want to use Access to ask more flexible questions about data in their RMSes, to make sense of a large parole file received from the state’s Department of Corrections, to organize data in a warrant file, to translate and merge data from incompatible RMSes or computer-aided dispatch (CAD) systems, and many other things that would take pages to itemize.
Crime analysts generally blend these two approaches to create a complete data management and querying solution for their day-to-day data interactions.
Relational Databases
A relational database contains multiple tables, related to each other through common fields. It is contrasted to a “flat file” system in which all data appears in a single table.
Imagine storing something complex, like all the data associated with a crime, in a single table. The table would have to consist of hundreds of columns for data about the crime, the offenders, the victims, the items of property stolen, the involved vehicles, and so forth. Moreover, there would have to be multiple columns with the same data to account for more than one offender, more than one victim, more than one vehicle, and so on. Most of the time, these “extra” columns would remain blank, and asking questions about the data in them would be a nightmare.
A relational database solves these problems by storing data in multiple tables organized into logical topics. Each table contains only the data relevant to its topic. A typical crime database would have separate tables for the main incident information (date, time, location), the crimes committed, the people involved, the property stolen. Each table contains a primary key, such as the incident number, which may appear in the other tables as foreign keys. This allows tables to be linked. The book contains more examples of relational databases and how they work.
