A Database of Crime Stats in the USA

Contents

Chapter 1

Chapter 2
Entity-Relationship Diagram
Relational Constructs
Entity Tables

Chapter 3

Chapter 4
Code Components

Chapter 5
Graphical User Interface – Search Parameters Screen
Graphical User Interface – File Choosing Screen
Graphical User Interface – Query Result Screen
Graphical User Interface – Exceptions

Chapter 6

Chapter 7

Appendix

Related Topic Areas and Readings

REFERENCES

Foreword

This database was constructed by ten students at the University of West Alabama who participated in a Database Management class project. The data was extracted from the FBI’s Uniform Crime Reporting Statistics database and provided aggregated annual crime quantities between the years of 1960 and 2014. It is our hope that this database can be used to increase awareness of crime rates in the United States. The authors of this project include: Christina Brown, James Akin, Gerald Germane Jr., Takari Nelson, Kathleen Jackson, Vontrez Tolliver, Paul Johnson, Zackary Wyatt, Diamond Roberts, and Timothy Watson.

Abbreviations

1. DBMS – Database Management System

2. CS-472-01 is the official course identity of the Database Management Class at the University of West Alabama.

3. FBI- Federal Bureau of Investigation

4. GUI- A Graphical User Interface allows a user to interact with an electronic device such as a computer, a hand-held device, and other appliances that have the downloaded interface.

5. ER-Diagram – An Entity-Relationship Diagram is a data modeling technique used in software engineering to produce a conceptual data model of an information system. It uses different shapes and arrows to delineate and show every relationship within a database.

Chapter 1

Considering the increasing numbers of mass shootings and inhumane criminal acts, it is important to be aware of one’s surroundings, acquaintances, and friendships. Anyone can become a victim at any given time and one’s safety cannot be promised. The previous issue is what fostered the construction of this uniform crime rate database. The purpose of this project is to increase awareness of crime rates in the United States by providing the public with a free and easily accessible database linked to a visually attractive and self-explanatory graphical user interface. All queries can be performed through the interface to access information on criminal information in the United States.

Before constructing a query, it is important to know what information is collected in the database. The data sets used in this project were extracted from an official 2016 United States FBI crime report of all fifty states, including Washington D.C., between the years of 1960 and 2014. There are nine different crimes of two categories. Category one consisted of the following violent crimes: murder and nonnegligent manslaughter, legacy rape, revised rape, robbery, and aggravated assault. Category two consisted of the following property crimes: burglary, larceny-theft, motor vehicle theft. The data sets included the total count of cases per crime and the rate of each crime per 100,000 population.

The scope and limitations of our project were straightforward. Our data base will have the U.S map as a user interface. Included in this interface will be a list box, for selections and searches. The map will be interactive as it will allow users to make a general selection. The map will change color upon “hovering” over the specified state. If a user clicks a state the interface will display crime statistics for the most current year, for that specific state.

All searches done by our interface will have multiple fields, so that the user can parse the database in a functional manner. Multiple states, and years need to be “Selectable”. Also, we will have a predetermined selection for them, that will categorize the data by region, and by time. Our database needs easy navigation functionality, so that any user can competently preform inquiries. The user interface needs to feel innate, and natural. Lastly the data needs to be displayed in a meaningful way. Uniform Crime Rate Database will adhere to all requirements and limitations.

Chapter 2

This section contains the relational constructs necessary for establishing relationships among tables. Table descriptions were provided in the preceding section.

Entity-Relationship Diagram

This project incorporated several entity tables. The ER-Diagram for this project is shown below.

Figure 1 – Entity Relationship Diagram for the Project

Abbildung in dieser Leseprobe nicht enthalten

Relational Constructs

The basic constructs comprising the ER Diagram are highlighted as follows:

Population Table

Table Name: 00_Population

Description: This table holds the annual population count of each state per year

Attributes: State,Year, Population

Attribute Description: State is 2 VARCHAR Characters

Year is 4 Integer Characters

Population is 10 Integer Characters

Key: Pop_ID-Population

Key Description: This field is unique for every state per year.

Visiting Key: None

Visiting Key Description: None

Related Tables: Related to all Tables by the Pop_ID field

Cardinality: Many to Many

Murder Table

Table Name: 01_Murder

Description: This table holds the total murder cases and the rate per 100,000 population per state per year

Attributes: Murder_ID , Count, Rate, Pop_Id

Attribute Description: Murder ID is 8 VARCHAR characters

Count data type is 10 INT characters

Rate is FLOAT of 10 possible characters

Pop_ID is 8 VARCHAR characters

Key: Murder_ID

Key Description: is unique for all murder cases per state and year.

Visiting Key: Pop_ID

Visiting Key Description: This key field holds the population for all states per year

Related Tables: The murder table is related to all tables by the Pop_ID

Cardinality: Many to Many

Legacy Rape Table

Table Name: 02_LegacyRape

Description: This table holds the Annual Legacy Rape count and rate per 100,000 population during each provided year.

Attributes: LR_ID, Count, Rate, Pop_ID

Attribute Description: LR_ID is 8 VARCHAR Characters

Count is 10 INT Characters

Rate is 10 FLOAT Characters

Pop_id is 8 VARCHAR Characters

Key: LR_ID

Key Description: This field is unique for Legacy Rape cases in all states per year

Visiting Key: Pop_ID

Visiting Key Description: This key field holds the population for all states per year

Related Tables: The Legacy Rape table is related to all tables by the Pop_ID

Cardinality : Many to Many

Revised Rape Entities

Table Name:03_RevisedRape

Description: This table holds the count of Revised Rape cases and the rate of this crime per 100,000 population per state and year.

Attributes: RR_ID, Count, Rate, Pop_ID

Attribute description: RR_ID is 8 VARCHAR Characters

Count is 10 INT

Rate is 10 FLOAT Characters

Pop_ID is 8 VARCHAR Characters

Key: RR_ID

Key Description: This field is unique for Revised Rape cases in all states per year

Visiting Key: POP_ID

Visiting Key Description: This key field holds the population for all states per year

Related Tables: The Revised Rape table is related to all tables by the Pop_ID

Cardinality: Many to Many

Robbery Table

Table Name: 04_Robbery

Description: This table holds the count of Robbery cases and the rate of this crime per 100,000 population per state and year.

Attributes: Rob_ID, Count, Ratio, Pop_ID

Attribute Description: Rob_ID is 8 VARCHAR Characters

Count is 10 INT Characters

Ratio is 10 FLOAT Characters

Pop_ID is 8 VARCHAR Characters

Key: Rob_ID

Key Description: This field is unique for Robbery cases in all states per year

Visiting Key: POP_ID

Visiting Key Description: This key field holds the population for all states per year

Related Tables: The Robbery table is related to all tables by the Pop_ID

Cardinality: Many to Many

Assault Table

Table Name: 05_Assault

Description: This table holds the count of Assault cases and the rate per 100,000 population per state and year.

Attributes: Aslt_ID, Count, Rate, Pop_ID

Attribute Description: Aslt_ID is 8 VARCHAR Characters

Count is up to 10 INT Characters

Rate is up to 10 FLOAT Characters

Pop_ID is 8 VARCHAR Characters

Key: Aslt_ID

Key Description: This field is unique for Assault cases in all states per year

Visiting Key: Pop_ID

Visiting Key Description: This key field holds the population for all states per year

Related Tables: The Assault table is related to all tables by the Pop_ID

Cardinality: Many to Many

Burglary Table

Table Name: 06_Burglary

Description: This table holds the count of Burglary cases and the rate of this crime per 100,000 population per state and year.

Attributes: Brg_ID, Count, Rate, Pop_ID

Attribute Description: Brg_ID is 8 VARCHAR Characters

Count is 10 INT Characters

Rate is up to 10 FLOAT Characters

Pop_ID is 8 VARCHAR Characters

Key: Brg_ID

Key Description: This field is unique for Burglary cases in all states per year

Visiting Key: Pop_ID

Visiting Key Description: This key field holds the population for all states per year

Related Tables: The Burglary table is related to all tables by the Pop_ID

Cardinality: Many to Many

Larceny Table

Table Name: 07_Larceny

Description: This table holds the count of Burglary cases and the rate of this crime per 100,000 population per state and year.

Attributes: Lar_ID, Count, Rate, Pop_ID

Attribute Description: Lar_ID is 8 VARCHAR Characters

Count is up to 10 INT Characters

Rate is up to 10 FLOAT Characters

Pop_ID is 8 VARCHAR Characters

Key: Lar_ID

Key Description: This field is unique for Larceny cases in all states per year

Visiting Key: Pop_ID

Visiting Key Description: This key field holds the population for all states per year

Related Tables: The Larceny table is related to all tables by the Pop_ID

Cardinality: Many to Many

Motor Vehicle Theft Table

Table Name: 08_Motor_Vehicle_Theft

Description: This table holds the count of Motor Vehicle Theft cases and the rate of this crime per 100,000 population per state and year.

Attributes: MVT_ID, Count, Rate, Pop_ID

Attribute Description: MVT_ID is 8 VARCHAR Character

Count is up to 10 INT Characters

Rate is up to 10 FLOAT Characters

Pop_ID is 8 VARCHAR Characters

Key: MVT_ID

Key Description: This field is unique for Motor Vehicle Theft cases in all states per year

Visiting Key: Pop_ID

Visiting Key Description: This key field holds the population for all states per year

Related Tables: The Motor Vehicle Theft table is related to all tables by the Pop_ID

Cardinality: Many to Many

Violent Crime Table

Table Name: 09_Violent_ Crime_Total

Description: This table holds the count of Violent Crime cases and the rate of this crime per 100,000 population per state and year.

Attributes: VC_ID, Count, Rate, Pop_ID

Attribute Description: VC_ID is 8 VARCHAR Characters

Count is up to 10 INT Characters

Rate is up to 10 FLOAT Characters

Pop_ID is 8 VARCHAR Characters

Key: VC_ID

Key Description: This field is unique for Violent Crime cases in all states per year

Visiting Key: Pop_ID

Visiting Key Description: This key field holds the population for all states per year

Related Tables: The Violent Crime table is related to all tables by the Pop_ID

Cardinality: Many to Many

Property Crime Table

Table Name: 10_Property_Crime_Total

Description: This table holds the count of Property Crime cases and the rate of this crime per 100,000 population per state and year.

Attributes: PC_ID, Count, Rate, Pop_ID

Attribute Description: PC_ID is 8 VARCHAR Characters

Count is 10 INT Characters

Rate is up to 10 FLOAT Characters

Pop_ID is 8 VARCHAR Characters

Key: PC_ID

Key Description: This field is unique for Property Crime cases in all states per year

Visiting Key: Pop_ID

Visiting Key Description: This key field holds the population for all states per year

Related Tables: The Property Crime table is related to all tables by the Pop_ID

Cardinality: Many to Many

Entity Tables

This section contains database table listings. These items are explained as follows:

- Assault - Delineates aggregated annual quantities of reported assault for the period spanning the years 1960-2014.
- Burglary - Delineates aggregated annual quantities of reported burglary for the period spanning the years 1960-2014.
- Population – Delineates census population counts for each state throughout the period spanning the years 1960-2014.
- Murder – Delineates aggregated annual quantities of reported murders for the period spanning the years 1960-2014.
- Larceny - Delineates aggregated annual quantities of reported legacy rape for the period spanning the years 1960-2014.
- Legacy Rape - Delineates aggregated annual quantities of reported legacy rape for the period spanning the years 1960-2014.
- Motor Vehicle Theft - Delineates aggregated annual quantities of reported motor vehicle theft for the period spanning the years 1960-2014.
- Property Crime - Delineates aggregated annual quantities of reported property crime for the period spanning the years 1960-2014.
- Revised Rape - Delineates aggregated annual quantities of reported revised rape for the period spanning the years 1960-2014.
- Robbery - Delineates aggregated annual quantities of reported robbery for the period spanning the years 1960-2014.
- Violent Crime – Delineates aggregated annual quantities of reported violent crime for the period spanning the years 1960-2014.

Chapter 3

One of the most important steps in the construction of a database is gathering the requirements and limitations of the database. This list is generated based on the needs of the customer and the feasibility of the programmers. With the completion of the list, a method of design can be outlined. All scope parameters guiding this project can be found in chapter one of this document on page 10.

To begin our project, the class instructor provided an excel document that contained public FBI statistics. This data spanned ten different crimes and included the population and total crime count per state between the years of 1960 and 2014 along with the rate per 100,000 population. The original excel document consisted of 2,800 tuples by 23 columns. Through a process of elimination, the class decided that the best way to connect the data was to create ten separate tables that represented each crime. The common factors between the tables would be the state, year, and population fields of each table.

The normalization process was completed through third normal form as required by the instructor. 1NF of this database consisted of six attributes: the specific crime, total cases, rate, state, year, and population. Ten tables were constructed with these columns and included 2,799 tuples per table, ultimately containing 16,794 entries per table totaling to 167,940 entries. 2NF was constructed to eliminate the redundancy of crime names. In the previous form the crimes were repeated 2,799 times. The class decided to provide each crime table with a key field. This not only separates the tables from each other, but also uniquely identifies each tuple within a table. The final 3NF was constructed to eliminate the redundancy of state, year, and population tuples that appeared in all ten tables. To complete this process, a new table was constructed that contained all state, year, and population data and was uniquely identified by a separate key field. The tables could then be connected using the key field of the latter table as a foreign key that appeared in all eleven tables. Third normal form consisted of four attributes by 2,900 tuples for all eleven tables. This ultimately reduced redundancy by 44,784 tuples and the database now consists of 123,156 entries.

Chapter 4

This chapter shows the modules used within program code. The files could only be saved in picture document therefore they had to be printed apart from this document. All procedures, functions, routines, explanation, outputs, exceptions, complementary modules, and structure charts are provided below.

Program Layout Overview

Abbildung in dieser Leseprobe nicht enthalten

Figure 2 - Program Layout

Code Components

Itemized below are specific details of program code components.

Class StatesGUI

public static void main(String[] args)

String[] args is a placeholder automatically included by the IDE, and is not used in this method.

This function is the one that is automatically called when the program is run.

javax.swing.SwingUtilities.invokeLater(new Runnable())

New Runnable() creates an instance of a Runnable object, which allows java programs to continuously run until the user closes them, rather than executing linearly until all code has been processed and then closing.

This function allows the program to safely be continuously processed by the computer, without incurring any related errors.

public void run()

This function serves to call the function responsible for setting up and displaying the Graphical User Interface, and therefore the rest of the program.

private static void createAndShowGUI()

This function creates the main window of the GUI.

Imports

- import java.awt.*; provides libraries for functions that allow the program to run continuously
- import javax.swing.*; provides libraries for functions that create and display GUI elements

Variables

- JFrame frame is an object that represents the GUI window
- JTextArea textArea is an object that displays whatever text is appended to it. In this case, it is use to provide the user with instructions for navigating the GUI, and with contextual information concerning the data contained within the database.
- JScrollPane textScrollPane is an object that takes as a parameter any GUI component that the user will need to scroll through. In this case, it takes textArea as a parameter.
- SelectionList StatesList is a class that displays a list of objects that are selectable by the user, and in this program, also serves to create and run other parts of the GUI.
- JSplitPane splitPane allows two different GUI components to be displayed as if they were a single component, with a divider between the two. It accepts as an argument two different GUI component objects, as well as the desired direction of the division. In this case, it divides textScrollPane and StatesList with a horizontal divide.
- Dimension minimumSize serves to determine the size of a specific object. In this case, it is used to ensure that GUI objects cannot be made any smaller than 100 pixels by 50 pixels by the user.

Object Functions

- frame.setSize(810, 810);
- Determines the size of the window (810 by 810 pixels)
- frame.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
- Chooses the operation to be carried out when the window is closed
- In this case, it closes the program completely
- textArea.setEditable(false);
- Determines if the JTextArea will be editable by the user or not
- In this case, prevents the JTextArea from being edited by the user
- textArea.setBackground(new Color(238, 238, 238));
- Determines the color of the area taken up by the JTtextArea
- In this case, sets it to the same color combination as the rest of the program(Red 238, Green 238, Blue 238)
- textArea.append(<String>);
- Appends the passed String to the end of the text area
- splitPane.setOneTouchExpandable(false);
- Determines if the user can change the division of the split pane by just clicking it, as opposed to clicking and dragging
- This is set to false
- splitPane.setDividerLocation(610);
- Determines the location of the divider between the two split pane objects
- In this case, it is located 610 pixels horizontally from the left border of the window
- textScrollPane.setMinimumSize(minimumSize);
- Used to ensure that GUI objects can not be made any smaller than the dimension that is passed to it
- In this case, 100 pixels by 50 pixels
- frame.getContentPane().add(splitPane, BorderLayout.CENTER);
- Visibly displays the given GUI object in the window
- Uses the second parameter to determine the location of the object
- In this case, displays splitPane, and therefore textScrollPane and StatesList, in the center of the window
- frame. setVisible(true);
- Allows the computer to graphically display the window and all GUI elements contained within, or not.
- In this case, it is set to true

Class SelectionList.java

public SelectionList()

This function serves as the class’s constructor, and so is called immediately once the class is created.

class SearchListener implements ActionListener

This class detects changes in a button, such as when the user clicks on it.

public void actionPerformed(ActionEvent e)

The code contained within this function executes when the appropriate button is pressed. It opens a file chooser, allowing the user to select the database file, and opens a new window to display the results of the query.

class ParamListener implements ActionListener

This class detects changes in a button, such as when the user clicks on it.

public void actionPerformed(ActionEvent e)

The code contained within this function executes when the appropriate button is pressed. It opens a new window, allowing the user to make selections about the data they wish to retrieve.

public void actionPerformed(ActionEvent e)

public void valueChanged(ListSelectionEvent e)

These two methods are required to be included when implementing the ActionListener interface.

Imports

- import java.awt.*; provides libraries for functions that allow the program to run continuously
- import java.awt.event*; provides libraries for functions that execute when an event occurs, such as the user clicking a button
- import javax.swing.*; provides libraries for functions that create and display GUI elements
- import javax.swing; *; provides libraries for detecting when an event occurs, such as the user clicking a button
- import java.io.*; provides libraries that allow the java program to accept data input and provide data output

Variables

- JList statesList is a GUI object that provides a visual representation of a list for the user to make selections from

- DefaultListModel listModel forms the input in the form of a list for statesList

- String searchString is a string that serves as the text displayed on the search button

- String paramString is a string that serves as the text displayed on the parameters button

- JButton searchButton is a GUI object that the user can click on to activate a certain section of code

- JButton paramButton is a GUI object that the user can click on to activate a certain section of code

- int[] selectedStates is an integer array that serves to save the states selected by the user

- int[] selectedCrimes is an integer array that serves to save the crimes selected by the user

- int startYear is an integer that serves to save the beginning year chosen by the user

- int endYear is an integer that serves to save the end year chosen by the user

- String queryResults is a string that serves to display the results of the user’s query from the database

- ComboBox yearsStartList is a GUI object that allows the user to choose their desired start year

- ComboBox yearsEndList is a GUI object that allows the user to choose their desired start year

- CheckBox crimeChoices is a GUI object that allows the user to choose their desired crimes to retrieve information on.

- JFileChooser fc is an instance of a file chooser, a GUI object that in this case, allows the user to select their desired database file

- File databaseFile is an object that holds the database file selected by the user

- DatabaseConnection myConnection is a class that allows the program to connect to a database

Object Functions

- listModel.addElement(<String>);

- Used to add a string of text to the list

- In this case, it is used to add all 50 states, plus Washington, D.C. to the list

- statesList.setSelectionMode(ListSelectionModel.MULTIPLE_INTERVAL_SELECTION);

- Determines the selection mode for the GUI list

- In this case, the user is allowed to select multiple states at once

- statesList.setFont(statesList.getFont().deriveFont(16.0f));

- Sets the font size for the list

- In this case, sets the size to 16

- statesList.setSelectedIndex(0);

- Sets what index in the list is selected upon initialization

- In this case, 0 is selected by default, representing the first state in alphabetical order, Alabama

- statesList.setLayoutOrientation(JList.VERTICAL);

- Sets in what direction the list will be displayed to the user

- In this case, it is displayed vertically

- statesList.addListSelectionListener(this);

- Allows for code to be executed when the user makes selections in the list

- statesList.setVisibleRowCount(30);

- Sets how many list items are visible at a time

- In this case, the maximum amount of visible states is 30

- <Button>.setPreferredSize(new Dimension(200, 30));

- Sets the default size for a GUI button

- In this case, 200 pixels by 30 pixels

- <Button>.setActionCommand(<String>);

- Sets the text to be displayed on the GUI button

- <Button>.addActionListener(new SearchListener());

- Allows for code to be executed when the user clicks the button

- buttonPane.setLayout(new BoxLayout(buttonPane,BoxLayout.LINE_AXIS));

- Creates a GUI section to add buttons to

- buttonPane. add(<button>);

- Adds the given button to the buttonPane

- buttonPane.setBorder(BorderFactory.createEmptyBorder(5,5,5,5));

- Sets the border size for the buttonPane
- In this case, 5 pixels on each size
- add(<GUI object>, <Layout object>);
- Adds the object to the main GUI window
- Uses the second parameter do determine the location of the object
- fc. showOpenDialog(fc);
- Displays a GUI object that allows the user to choose a file
- fc. getSelectedFile();
- frame.setDefaultCloseOperation(JFrame.HIDE_ON_CLOSE);
- Chooses the operation to be carried out when the window is closed
- In this case, this particular window is closed, but the program itself stays open

Class ComboBox.java

public ComboBox(int defaultSelection)

This function serves as the object’s constructor, and creates the ComboBox, a GUI object that serves as a dropdown list, allowing the user to make a selection.

It takes as a parameter defaultSelection, an integer that determines what the initial selection on the dropdown list will be.

public void actionPerformed(ActionEvent e)

This function executes when the user selects an item in the dropdown list.

public int getSelectedYear()

This function allows the year selected by the user to be returned to other parts of the program.

Imports

- import java.awt.*; provides libraries for functions that allow the program to run continuously

- import java.awt.event*; provides libraries for functions that execute when an event occurs, such as the user clicking a button

- import javax.swing.*; provides libraries for functions that create and display GUI elements

Variables

- int selectedIndex serves to hold the index position of the year selected by the user in the dropdown list

- String[] yearStrings is an array of strings that hold the choices available to the user through the dropdown box. In this case, the years 1960 through 2014.

- JComboBox yearList is the GUI object that creates and displays the dropdown list

Object Functions

- yearList. setSelectedIndex(defaultSelection);
- Sets the default index selected in the dropdown box to the index number corresponding to the integer passed to the ComboBox class
- add(yearList, BorderLayout.PAGE_START);
- Adds the object to the main GUI window
- Uses the second parameter do determine the location of the object
- setBorder(BorderFactory.createEmptyBorder(20,20,20,20));
- Sets the border size for the dropdown list
- In this case, 20 pixels on each size

Class CheckBox.java

public CheckBox()

This function serves as the object’s constructor, and creates the CheckBox, a GUI object that allows the user to select any combination of choices from a given list.

public int[] getSelections()

This function serves to return the choices selected by the user to other parts of the program, in the form of an integer array.

public void itemStateChanged(ItemEvent e)

This function detects changes in the user’s checkbox selections, and is required by the ItemListener interface. It is not directly used.

Imports

- import java.awt.*; provides libraries for functions that allow the program to run continuously

- import java.awt.event*; provides libraries for functions that execute when an event occurs, such as the user clicking a button

- import javax.swing.*; provides libraries for functions that create and display GUI elements

Variables

- JCheckBox murderButton;

- JCheckBox legacyRapeButton;

- JCheckBox revisedRapeButton;

- JCheckBox robberyButton;

- JCheckBox assaultButton;

- JCheckBox burglaryButton;

- JCheckBox larcenyButton;

- JCheckBox vehicleTheftButton;

- JCheckBox totalViolentButton;

- JCheckBox totalPropertyButton;

- Each of these is a similar GUI object in the checkbox that allows to user to select or deselect that option. Each represents a single crime.

- JPanel checkPanel

- This GUI object reserves space for the check boxes, and allows more to be added to the reserved area

- int[] selections

- This array saves the user’s selection of crimes.

- 1 represents murder

- 2 represents legacy rape

- 3 represents revised rape

- 4 represents robbery

- 5 represents assault

- 6 represents burglary

- 7 represents larceny

- 8 represents vehicle theft

- 9 represents total violent crime

- 10 represents total property crime

- -1 represents a checkbox that was left uneslected by the user

- int index

- This integer is used to increment the selected index of the selections array

- Once one crime has been chosen, index is incremented by one to select the next location in selections

Object Functions

- <JCheckBox>.setSelected(<Boolean>)

- Determines if the JCheckBox is selected or not

- Can be used by the program to select JCheckBoxes by default, or by the user to manually select desired choices

- checkPanel.add(<JCheckBox >);

- Adds the JCheckBox passed to it to the GUI panel

- add(checkPanel, BorderLayout.LINE_START);

- Adds the object to the main GUI window

- Uses the second parameter do determine the location of the object

- setBorder(BorderFactory.createEmptyBorder(20,20,20,20));

- Sets the border size for the check boxes

- In this case, 20 pixels on each size

Class DatabaseConnection.java

public DatabaseConnection(int[] statesList, int[] yearsList, int[] crimesList, File databaseFile)

This function serves as the class’s constructor, and automatically makes and returns database queries based on the user’s choices.

It takes as parameters integer arrays representing the user’s desired states, years, and crimes to search in the database, as well as a file object representing the database itself.

private void makeQuery(int state, int year, int crime, Connection con, Statement st, ResultSet rs)

This function makes a single database query using the individual state, year, and crime passed to it, as well as the Connection, Statement, and ResultSet objects required when interfacing with an SQL database using java,

public String getResults()

This function serves to return the results of all database queries to other parts of the program.

Imports

- import java.io.*; provides libraries that allow the java program to accept data input and provide data output

- import java.sql.*; provides libraries that allow the java program to make queries from an SQL database.

Libraries

- ucanaccess-4.0.2.jar

- This library uses all other included libraries to allow the program to connect to a Microsoft Access database

- commons-lang-2.6.jar

- commons-logging-1.1.1.jar

- hsqldb.jar

- jackcess-2.1.6.jar

Variables

- Connection con is an object that creates the connection with the database file, using the driver program ucanaccess-4.0.2.jar

- Statement st is an object that is used to make an SQL query in the database

- ResultSet rs is an object that is used to locate rows in the SQL database

- String databasePath is used to hold the file path of the database file chosen by the user

- String temp is used to easily append headings representing different crimes to resultsString, which is used to display the results of the program’s queries

- String stateQuery is used in SQL queries to represent the desired state

- String yearQuery is used in SQL queries to represent the desired year

- String crimeQuery is used in SQL queries to represent the desired crime table

- String CrimeTableName is used in SQL queries to represent the name of the crime column within a table

- String Crime_ID is used in SQL queries to represent a crime’s ID. For example, 01AL1960

Object Functions

- databaseFile.getAbsolutePath();

- Returns as a string the file path where the user’s chosen database file is open.

- con. createStatement();

- Readies the database connection to make a new query

- st. executeQuery(<String>);

- Executes an SQL query using the String that holds the query, and is passed to it as a parameter

- rs. next();

- Resets the database connection to allow for a new query

Chapter 5

The main screen appears when the program initially launches. It gives the user instructions on how to select their desired states, as well as further information about the data contained in the database. The user can select a single state, or any possible combination of states. From this screen, the user may click on “Retrieve Data” to make a query using the program’s default selections of crimes and year range. They may also click “Set Parameters” to make their own selections. Closing this window will end execution of the program.

Abbildung in dieser Leseprobe nicht enthalten

Figure 3 – Main Screen Interface

Graphical User Interface – Search Parameters Screen

Clicking “Set Parameters” opens a new window that allows users to choose which crimes they wish to retrieve information on, as well as the range of years they want to see information for. This window can be left open or can be closed; it will not have an effect on the rest of the program, and the user’s selections will not be lost if the window is closed.

Abbildung in dieser Leseprobe nicht enthalten

Figure 4 – Search Parameters Screen

Graphical User Interface – File Choosing Screen

Clicking on “Retrieve Data” will result in this window opening. It requires the user to select the database file. Choosing a different file will result in no results being returned, but will otherwise not interfere with the operation of the program

Abbildung in dieser Leseprobe nicht enthalten

Figure 5 – File Choosing Screen

Graphical User Interface – Query Result Screen

This window appears once the database file is selected by the user, and displays the results queried from the database. It can be left open or can be closed. Once closed, the user is free to search the database once again.

Abbildung in dieser Leseprobe nicht enthalten

Figure 6 – Query Results

Graphical User Interface – Exceptions

Any errors encountered will be printed as the program’s stack trace if the program is being executed through the Netbeans Java IDE. Most errors are prevented from occurring because the program is run using a Runnable type of object in Java which allows the program to safely execute continuously (as opposed to executing linearly through its code and then stopping).

Chapter 6

Our original goal when starting this project was to use OpenOffice Apache as our Database software. However, OpenOffice had several software restrictions, and would not allow use of nested queries. After trying other software, and multiple attempts to fix this issue, we decided to make a Jar file and use the Java SQL Extension to make our queries. The Integration of the GUI and database was much easier after arriving at this conclusion.

This database should run on almost any personal computer. It does not require a server, or any complex hardware. It does require Java Lib to be installed, and you must possess the Jar file, the database and the library. There are no other software considerations. Java can be installed on a Mac but we are unsure if the database will work on an apple platform. We are also unaware if it will run on a mobile platform. However, the three system requirements can be downloaded onto any flash, or portable drive then installed on any Windows PC.

Overall the database preforms well. It is noted that doing an entire database query, that is selecting all states, for all year, and all crimes dose cause a delay in the results being shown. Also, that the “Set Parameter” tab will remain open even after hitting the “X” button. It’s not an issue, but running multiple queries in succession causes multiple “Set Parameter” tabs to be open at once.

Below is a query where years 1992-1993 were selected on the Assault crime table. The states Connecticut, Hawaii, Maine, and Montana were selected.

Figure 7 – Query for Connecticut, Hawaii, Maine, and Montana

Abbildung in dieser Leseprobe nicht enthalten

Below are the query results displayed in a Search Results tab.

Abbildung in dieser Leseprobe nicht enthalten

Figure 8 – Results for Connecticut, Hawaii, Maine, and Montana

Below is a query for the states; Alabama, Alaska, Arizona, Arkansas, California, and Colorado were selected. In our Parameters tab, we have the years 1960-1961 selected on the Assault and Motor Vehicle Thief tables.

Abbildung in dieser Leseprobe nicht enthalten

Figure 9 – Query for Alabama, Alaska, Arizona, Arkansas, California, and Colorado

Below are the Results from the Alabama, Alaska, Arizona, Arkansas, California, and Colorado query.

Abbildung in dieser Leseprobe nicht enthalten

Figure 10 – Results for Alabama, Alaska, Arizona, Arkansas, California, and Colorado

Below is a single state, Tennessee, queried on all crimes 1970-1975

Abbildung in dieser Leseprobe nicht enthalten

Figure 11 – Query for Tennessee

Below are the results from the Tennessee query.

Abbildung in dieser Leseprobe nicht enthalten

Figure 12 – Results for Tennessee

Chapter 7

The creation of this database served the educational purposes at the University of West Alabama of learning how to construct and publish a professional grade database with an accompanied graphical user interface to direct the use of queries. Course CS-472-01-17FA received three credit hours from UWA and was scheduled for 50 minutes on Monday, Wednesday, and Friday at 10:00AM. Project requirements were distributed on October 16th when the class voted for a manager and discussed all essential deliverables, goals, and the scope statement. This information is found in chapter one of this document. A Google Drive was created specifically for this project and its components. Everyone used their personal computers to complete each task and all were distributed to the drive for finalization. The deadline for the project was named November 29, 2017.

With this foundation in place, the work could then be distributed among class members. On October 18th and 20th the functional components of this project were outlined and listed as follows: normalization of data, creation of an entity-relationship diagram and a data dictionary, programming the graphical user interface and the database, and testing and assurance of the product. All components were disseminated in pairs of two. The delineation of work for this project can be found in the appendix section of this document.

The first step was to complete the normalization of data and populate the database. All data was provided via an excel document, therefore, the tables were constructed on that software. Everything was transferred from Excel to a Java SQL Extension database building software. It was given a deadline of one week and was completed on October 27th.

With this population complete, the diagram, dictionary, and programming could be completed simultaneously. The ER-diagram was given one-week to accomplish and was completed on November 3rd. The data dictionary was created using Microsoft Word. It was given a two-week deadline and was completed on the 10th of that month. All programming was given a three-week deadline due to its minute detail. The GUI was programmed using the Java language along with a second code using Access as a failsafe. This coding process was accomplished by Friday, November 17th.

The final step was to complete the quality testing of the database to assure its professionality. This job and the final database was completed and distributed to the instructor on November 29, 2017.

Appendix

Original Project Work Delineation.

Conceptual Design- Class

- Normalization of the data base- Timothy Watson, Zackary Wyatt

Logical Design~~

- (ERD)- Diamond Roberts, Takari Nelson

- Data Dictionary- Paul Johnson, Gerald German Jr.

Physical Design~~

- Programing of Graphical User Interface - Zackary Wyatt, Kathleen Jackson

- Programing the database – James Akin, Vontrez Tolliver

Quality assurance~~

- Paper Documentation- James Akin, Timothy Watson

- Testing the data base- Cristina Brown, Diamond Roberts

Related Topic Areas and Readings

Artificial intelligence – Artificial intelligence approaches are useful among law enforcement functions from the perspective of big data processing and analysis. For instance, data mining and neural networking are useful for discovering knowledge, and for performing pattern analysis and recognition that would be unapparent to humans without the use of electronic data processing (Gottschalk, 2009).

Border security – A variety of federal databases exist to support examinations of border security from a criminality perspective. Examples include observed border crossings versus reported incidents of cybercrime (Doss, Henley, & McElreath, 2013a; 2013b; Doss, McElreath, Gokaraju, Henley, & Lande, 2012; Doss, Wigginton, Mallory, & Troxel, 2010; Troxel & Doss, 2010).

Crime analysis – A variety of databases exist to support crime analysis functions and administrative processes of law enforcement organizations (McElreath, et al., 2013). Examples range from electronic criminal records databases to electronic personnel records (McElreath, et al., 2013).

Economic and financial analysis – Billions have been spent toward deterring criminality and maintaining society order. For instance, data sets exist whereby examinations of spending versus crime reduction may occur (Akpom & Doss, 2016). Analytical methods of financial data processing range from capital budgeting to cost-benefit analysis (Doss, Sumrall, & Jones, 2013).

First responder – Law enforcement organizations and justice system entities act as first-responders to a variety of criminal instances (McElreath, et al., 2013; McElreath, et al., 2014). Typically, any number of databases support such efforts, ranging from listings of closed roadways during emergencies to maintaining lists of medical facilities. Data sets regarding emergency incidents may be used for examining cost controls among disaster management endeavors (Gokaraju, Nobrega, Doss, Turlapaty, & Tesiero, 2017; Gokaraju, Turlapaty, Doss, King, & Younan, 2015).

Intelligence cycle – Data and information processing may be used to support human decisions via considerations of the intelligence cycle. This approach represents a cyclical process for data collection, processing, analysis, interpretation, and outcome dissemination (Doss, et al., 2016).

Justice system applications – Entity-relationship diagrams may be used to model a variety of realistic scenarios reflecting criminality, policing, facets of law enforcement organizations, or aspects of the justice system (Doss, et al., 2016).

Life cycle – Technology emerges, changes, and becomes obsolete in due time. The justice system integrates technology as it emerges, and retires it when necessary. These notions also apply to database systems (Doss, Guo, & Lee, 2012; Gottschalk, 2009).

National crime demographics – The Uniform Crime Reports database (FBI resource) contains data reflecting a variety of crime categories nationally, ranging from homicide to motor vehicle theft (Doss, Sumrall, McElreath, & Jones, 2013).

Natural language – Natural languages may be used as components of interfaces among database systems, including as a query resource (Doss, Glover, Goza, & Wigginton, 2015). Use of computing devices for language conversion may facilitate communication involving difference languages in the justice domain (Doss, Glover, Goza, & Wigginton, 2015).

Organizational improvement – Data analysis may be used to improve organizational efficiency and effectiveness through time. Within the justice system, COMPSTAT represents such a resource (Doss & Henley, 2015; Doss, Sumrall, & Jones, 2010).

Technology – Databases are a form of electronic technologies. Some may deem them to be essential aspects of a business organization whereas others may be leery of their use. From a philosophical perspective, technologies may be viewed from utopian, neutral, or dystopian perspectives (Liu, et al., 2016). Given this notion, databases may be examined from any of the three perspectives.

Terrorism analysis – Databases exist to support terrorism analyses domestically and internationally. An example is the Global START database housed and maintained at the University of Maryland (McElreath, et al., 2014). It provides a user interface whereby data sets may be queried and response tables generated online almost instantaneously.

REFERENCES

Akpom, U. & Doss, D. (2016). Estimating the impact of state government spending and the economy on crime rates. Presented to the Southwest Society of Economists, Oklahoma City, OK.

Doss, D., Glover, W., Goza, R., & Wigginton, M. (2015). The foundations of communication in criminal justice systems. Boca Raton, FL: CRC Press.

Doss, D., Guo, C., & Lee, J. (2012). The business of criminal justice: A guide for theory and practice. Boca Raton, FL: CRC Press.

Doss, D., & Henley, R. (2015). Enhancing the Compstat paradigm via an assessment architecture and framework. Presented to the annual conference of the Society of Business Research. Nashville, TN.

Doss, D., Henley, R., Gokaraju, B., McElreath, D., & He, F. (2016). Integrating the GOMS model and the intelligence cycle. Journal of Business and Economics, 7 (8), 1260-1268.

Doss, D., Henley, R., & McElreath, D. (2013a). The California-Mexican border: Investigating Pearson correlation coefficient outcomes representing U.S. border crossing data versus U.S. reported cybercrime incidents for the period of 2001-2011. Mustang Journal of Legal Studies, 2013, 21.

Doss, D. Henley, R., & McElreath, D. (2013b). The Arizona border with Mexico: A Pearson correlation coefficient analysis of US border crossing data versus US reported cybercrime incidents for the period of 2001-2011. International Journal of Social Science Research, 1, 17-26.

Doss, D., McElreath, D., Gokaraju, B., Henley, R., & Lande, U. (2012). New Mexico: Investigating Pearson Correlation Coefficient Outcomes Concerning U.S. Border Crossing Data Versus U.S. Reported Cybercrime Incidents During 2001-2011. Presented to the annual conference of the Southern Criminal Justice Association.

Doss, D., McElreath, D., Jensen, C., Wigginton, M., Goza, R., Becker, U., & Roberts, R. (2016). Management and administration for criminal justice organizations. Dubuque, IA: Kendall-Hunt.

Doss, D., Sumrall, W., & Jones, D. (2012). Strategic finance for criminal justice organizations. Boca Raton, FL: CRC Press.

Doss, D., Sumrall, W., & Jones, D. (2010). Embellishing Compstat through an evaluation framework. Presented to the Southern Criminal Justice Association. Clearwater Beach, FL.

Doss, D., Sumrall, W., McElreath, D., & Jones, D. (2013). Economic and financial analysis for criminal justice organizations. Boca Raton, FL: CRC Press.

Doss, D., Wigginton, M., Mallory, S., & Troxel, S. (2010). The Arizona border with Mexico: Examining Pearson Correlation Coefficient outcomes concerning U.S. border crossing data versus U.S. reported cybercrime incidents during 2001-2008. Presented to the annual conference of the Academy of Criminal Justice Sciences.

Gokaraju, B., Nobrega, R., Doss, D., Turlapaty, A., & Tesiero, R. (2017). Data fusion of multi-source satellite data sets for cost-effective disaster management studies. Presented to the IEEE SoutheastCon, North Carolina.

Gokaraju, B., Turlapaty, A., Doss, D., King, R., & Younan, N. (2015). Change detection analysis of tornado disaster using conditional copulas and data fusion for cost-effective disaster management. Presented to the IEEE Workshop on Applied Imagery and Pattern Recognition. Washington, DC.

Gottschalk, P. (2009). Policing financial crime: Intelligence strategy implementation. Boca Raton, FL: BrownWalker Press.

Liu, M., Yang, D., He, F., Li, M., & Doss, D. (2016). Perspectives of technology and the instrumentalist paradigm. Proceedings of the Academy of Organizational Culture, Communications, and Conflict, 21 (1), 34-48.

McElreath, D., Doss, D., Jensen, C., Wigginton, M., Kennedy, R., Winter, K., Mongue, R., Bounds, J., Estis-Sumerel, M. (2013). Introduction to law enforcement. Boca Raton, FL: CRC Press.

McElreath, D., Doss, D., Jensen, C., Wigginton, M., Nations, R., Van Slyke, J., & Nations, J. (2014). Foundations of emergency management. Dubuque, IA: Kendall-Hunt.

McElreath, D., Jensen, C., Wigginton, M., Doss, D., Nations, R., & Van Slyke, J. (2014). Introduction to homeland security. (2nd ed.). Boca Raton, FL: CRC Press.

Troxel, S. & Doss, D. (2010). The Texas border with Mexico: Examining the Pearson Correlation Coefficient outcomes concerning U.S. border crossing data versus U.S. reported cybercrime incidents during 2001-2008. Presented to the annual conference of the Academy of Criminal Justice Sciences.

Wigginton, M., Burton, R., Jensen, C., McElreath, D., Mallory, S., & Doss, D., (2015). Al-Qods force: Iran’s weapon of choice to export terrorism. Journal of Policing, Intelligence, and Counter Terrorism, 10 (2), 153-165.

[...]