Evidence Inventory and Tracking Program Final Report



Evidence Inventory and Tracking Program Final Report

Dec04-07

|Client: |Boone Police Department |

|Faculty advisor: |Prof. Thomas Daniels |

|Team Leader: |Andrew Bitterman |

|Communication Coordinator: |Jennifer Sanders |

|Team Members: |Royson Chong |

| |Yin-Cheung Lo |

REPORT DISCLAIMER NOTICE

DISCLAIMER: This document was developed as a part of the requirements of an electrical and computer engineering course at Iowa State University, Ames, Iowa. This document does not constitute a professional engineering design or a professional land surveying document. Although the information is intended to be accurate, the associated students, faculty, and Iowa State University make no claims, promises, or guarantees about the accuracy, completeness, quality, or adequacy of the information. The user of this document shall ensure that any such use does not violate any laws with regard to professional licensing and certification requirements. This use includes any work resulting from this student-prepared document that is required to be under the responsible charge of a licensed engineer or surveyor. This document is copyrighted by the students who produced this document and the associated faculty advisors. No part may be reproduced without the written permission of the senior design course coordinator.

Date of Submission: November 12, 2004

Table of Content

List of Figures iii

List of Tables iv

List of Tables iv

List of Definitions v

1 Introductory Materials 1

1.1 Executive Summary 1

1.2 Acknowledgements 2

1.3 Problem Statement 2

1.4 Operating Environment 3

1.5 Intended Users 4

1.6 Intended Uses 4

1.7 Assumptions 4

1.8 Limitations 4

1.9 Expected End Product and Other Deliverables 5

2 Proposed Approach and Results 7

2.1 End-Product Functional Requirements 7

2.2 Resultant Design Constraints 8

2.3 Approaches Considered and Used 9

2.4 Detailed Design 11

2.6 End-Product Testing Description 27

2.7 Project End Results 28

3 Resources Requirements 29

3.1 Personnel Effort Requirements 29

3.2 Financial Costs 32

4 Schedules 35

4.1 Project Schedules 35

4.2 Deliverable Schedule 37

5 Closure Material 38

5.1 Project Evaluation 38

5.2 Commercialization 39

5.3 Recommendations for Additional Work 40

5.4 Lessons Learned 40

5.5 Risk and Risk Management 42

5.5.2 Anticipated Risks Encountered and Success in Management 43

5.5.3 Unanticipated Risks Encountered, Attempts to Manage and Level of Success 44

5.5.4 Resultant Changes in Risk Management 44

5.6 Project Team Information 45

5.6.1 Client information: 45

5.7 Closing Summary 46

List of Figures

|Figure 2.4.1 |Overall architecture of the project software |12 |

|Figure 2.4.1.1a |Permitted actions for level-1 users |12 |

|Figure 2.4.1.1b |Add new evidence screenshot |13 |

|Figure 2.4.1.1c |New evidence screenshot |13 |

|Figure 2.4.1.1d |Search page screenshot |14 |

|Figure 2.4.1.2 |Overall structure of level-2 users |14 |

|Figure 2.4.2 |Overview of the GUI design |15 |

|Figure 2.4.2.1 |Login screenshot |16 |

|Figure 2.4.3 |Architecture of the database |17 |

|Figure 4.1.1 |Project schedule |29 |

|Figure 4.2.1 |Deliverable schedule |30 |

List of Tables

|Table 1.9.1 |Various documentation artifacts created for the project |5 |

|Table 3.1.1 |Original estimated personnel effort hours |7 |

|Table 3.1.2 |Revised estimated personnel effort hours |7 |

|Table 3.1.3 |Actual personnel effort hours |7 |

|Table 3.2.1 |Original financial requirements |9 |

|Table 3.2.2 |Revised financial requirements |10 |

|Table 3.2.3 |Actual financial requirements |10 |

|Table 2.4.3.2.1 |Username-Password table definition |17 |

|Table 2.4.3.2.2 |Sample Username-Password table |18 |

|Table 2.4.3.3.1 |Evidence Inventory table definition |18 |

|Table 2.4.3.3.2 |Sample entry of Evidence Inventory table |19 |

|Table 2.4.3.4.1 |Chain of Custody table definition |20 |

|Table 2.4.3.4.2 |Sample entry of Chain of Custody table |21 |

|Table 2.4.3.5.1 |Log File table definition |21 |

|Table 2.4.3.5.2 |Sample entry of Log File table |22 |

|Table 5.1.1 |Project milestones, level of success and brief explanation |37 |

List of Definitions

Apache – An open source web server software.

Boone PD – Boone Police Department.

Chain of custody – Procedures that govern the collection, handling, storage, transportation, and testing of evidence; a list of where the select piece of evidence has gone and when, and who checked it out.

Evidence – The documentary or oral statements and the material objects admissible as testimony in a court of law.

Graphical user interface (GUI) – A user interface based on graphics instead of text; uses a mouse as well as a keyboard as an input device.

Java – An object-oriented programming language developed by Sun Microsystems. Java supports programming for the Internet in the form of platform-independent Java applets.

MySQL – Open source database management software.

Personal Digital Assistant (PDA) – A lightweight, hand-held, usually pen-based computer used as a personal organizer.

PHP – The official name is Hypertext Preprocessor. It is a server-side scripting language.

1 Introductory Materials

This section introduces the project by presenting an executive summary followed by the problem statement, operating environment, and the intended users and uses. Then, the assumptions made, limitations imposed and expected end product is discussed.

1.1 Executive Summary

1.1.1 Need for Project

Boone Police Department wanted an automated manner for evidence inventory and tracking purposes. The method that was employed involved manually copying information into index cards. Consequently, evidence item registration, search, editing and retrieval became a very time-consuming process.

1.1.2 Project Activities

An open source based program was created to fulfill Boone Police Department’s (Boone PD) needs. The team used Apache as the server, MySQL as the database and PHP with HTML as the user interface for this program that was local area network confined. Two classes of users were created – general user and administrator. Administrators could monitor the actions taken by general users. For preservation of evidence records, no person could delete any evidence information in the database. If an item was edited, a new record was created with the previous version pointing to the newest. A log file was created to monitor all activities in the program. Hardware attachments (barcode scanner and digital signature pad) are in the process of being implemented to facilitate data reference, data entry or enforcement of user authentication and user authorization.

1.1.3 End Results

The hardware attachments were in the process of being implemented as the team faced difficulties in integration. In addition to that, session management was also in the process of being implemented. In short, the team produced a functional program that had limited security features.

1.1.4 Recommendation for Follow-on Work

Future teams could extend the program to implement PDA integration. Fingerprint scanning and signature comparisons are also meaningful additions that enforce user authentication.

1.2 Acknowledgements

The team would like to express their deepest gratitude to course instructors Dr. John W. Lamont and Prof. Ralph E. Patterson for their guidance concerning planning, documentation and implementation of this project.

The team would also like to thank the faculty advisors, Prof. Thomas Daniels and Prof. Mani Mina for their insight and suggestions while working on this project.

Ex-Chief Officer Dan Losada of the Boone Police Department was very helpful in providing consultation and input for the project requirements and specifications.

Finally, the project team would like to extend their appreciation to Chief Officer William Skare and Assistant Chief Officer Don Anderson for their input and further specifications.

1.3 Problem Statement

This section discusses the problem that Boone PD had that the project hopes to solve or alleviate. This section also includes a brief description on the general solution the team employed for this project.

1.3.1 General Problem Statement

The Boone Police Department has a vault to store evidence obtained from crime scenes. The evidence is kept for further analysis (e.g. waiting to be sent to a laboratory for chemical tests) or pending investigations (e.g. court case). As such, it is crucial that Boone PD is able to keep track of the evidence that is located in its vault or sent for further analysis.

Prior to the project, Boone PD uses index cards (3” by 5” cards) to keep track of evidence inventory. When an evidence item is brought in, its information is recorded onto a new index card. When personnel needed to look for an evidence item, they first had to refer to the correct index card.

Using index cards to keep track of evidence inventory and tracking had the following drawbacks:

• Information needed to be hand written to the cards.

• Personnel had to manually look for a specific index card in order to locate an evidence item.

• To make a backup, a manual copy of all the information from an index card was made to another index card.

1.3.2 General Solution Approach

As explained in the general problem statement, the process of manually keeping track of evidence inventory is very tedious and inefficient.

This project served to automate the evidence inventory and tracking process. The team produced a program that is easy to use and also systematically tracks changes to the evidence in Boone PD. This program was created using the MySQL database management software, PHP scripting language and Apache web server software. In addition, a digital signature pad may be utilized for user authorization while a barcode scanner may be used to make identification of an evidence item easier. The server workstation would be the only computer with the hardware attachments.

Initially, Boone PD wanted the program to provide an extension to PDAs. This feature would make it convenient for Boone PD personnel to search for a specific evidence item while being in the vault. The client also hoped that the PDA could be used at the crime scene so that officers could immediately record the evidence information and perform a digital transfer to the server upon arrival to Boone PD. Since the team was not confident in dealing with programming on a PDA on top of having to learn ways to manipulate Apache, PHP, MySQL, a barcode scanner and a signature pad, the team decided not to implement the PDA feature. The client was willing to forgo the PDA feature.

1.4 Operating Environment

This program was installed into a computer that acted as a server. This server was accessible by computers in Boone PD only. Since this program ran on a network of computers indoors, the environment remained mostly static. The air temperature ranged from 60-75 degrees Fahrenheit.

As Boone PD used computers that had the Professional edition of Microsoft’s Windows XP Operating System, the team did not anticipate any conflicts with the program as Apache, PHP and MySQL was known to work with Windows XP.

1.5 Intended Users

The intended users for this program were strictly police officers within Boone PD. User authentication was implemented to ensure only selected law enforcement personnel were able to use the program.

1.6 Intended Uses

This program’s primary use is to keep track of evidence. A new record will be created for every new evidence item brought into Boone PD. The program will also search for a specific evidence item, or items related by the case number or other fields based on the search inputs. A chain of custody entry will also be created whenever an item enters or leaves Boone PD.

1.7 Assumptions

Listed below are the assumptions the team made while working on the project:

• The user has basic knowledge of Microsoft’s Windows XP Operating System.

• The user’s computer has sufficient storage space for database information manipulation and backup.

• Backup will be made on a frequent basis.

• The information in the database will be stored on client’s computer for approximately two years or depending on the needs of the client. Information not in need will be stored in permanent storage.

• There will only be eight workstations able to connect to the program with a designated server.

• All users understand English.

• All users understand the manual evidence inventory and tracking process and also the chain of custody procedure.

1.8 Limitations

Listed below are the limitations that were imposed upon the team for this project:

• The program must be complete by December 2004.

• The program must be compatible with the Professional edition of Microsoft’s Windows XP Operating System.

• The maximum response time must be less than ten seconds.

• The server computer must have backup and restore capability.

• The total cost of the entire project excluding labor must not be more than $250.

1.9 Expected End Product and Other Deliverables

The team created an Apache, MySQL and PHP based program to assist Boone PD in its evidence inventory and tracking procedures. Table 1.9.1 lists the documentations the team created throughout the project.

Table 1.9.1: Various documentation artifacts created for the project

|Document type |Description |

|Project plan |Detailed the approach to be taken to accomplish the project. |

|Design document |Contained in-depth technical design for the program. |

|Project poster |Poster catered to the public that summarized the project. |

|Final report |Intricate details regarding the entire project and the end result(s). |

|Weekly reports |E-mail report sent weekly to faculty advisor(s), course instructor (s) and client(s) to inform |

| |progress of project |

|Product manual |Provide information on how to use the program. |

In Spring 2004 the team delivered the Project Plan to the client, who was Chief Officer Dan Losada. After verifying that the requirements and specifications of the project were understood, the team proceeded with creating the architecture of the program. The design details were compiled into a report called the “Design Document”.

Upon completion of the design document, the report was again delivered to the client. After the client had a clear idea of the structure and flow of the program, the team began creating a prototype.

The team also created a poster that summarized the project to the public. This poster was created to fulfill the senior design course requirement and was displayed in Coover Hall.

In the fall of 2004, the team continued with the implementation of the prototype. Once functional, the team presented the prototype to the client to seek their approval. By that time, the client had changed from Chief Officer Dan Losada to Chief Officer William Skare and Assistant Chief Officer Don Anderson. After the clients tried the product and were satisfied, the team further continued refining of the program.

Before the end of the semester, a final report was created that included in great detail all activities conducted for the project. Among information included were the schedule, cost, architecture of the program and recommendations for future work. The final report also listed all the functionalities of the project and the respective explanations. This report was sent to the client as a reference.

Throughout the entire project, the team sent weekly reports via e-mail to the faculty advisor, course instructors, and Boone PD. This was done so that all parties were aware of the project progress.

The final product consists of the completed program derived from the prototype. The final product is a local area network based program running Apache as web server management, MySQL as database management, and PHP with HTML as the user interface. A digital signature pad may be used to verify a transfer of evidence to and from Boone PD while the barcode scanner would provide easier reference for evidence key numbers.

Officers are able to register a new evidence item into the database and search for specific evidence items. The program also keeps a log of all activities acted upon an evidence item. No records can be deleted. Editing a record means a new version is created and the previous version refers to it.

To facilitate ease of learning to use the end product, the team created a detailed user manual for the client. The team asked a few personnel to use the product and ascertain the difficulties faced. If the issue was not covered in the user manual, the team made refinements. This continued until the team produced a comprehensive user manual.

2 Proposed Approach and Results

This section provides the details of the approach used during the process of designing and implementing the end product.

2.1 End-Product Functional Requirements

The end product requirements are listed below.

1 2-level access restriction

In order to ensure security of the program, access was made limited. The first level of access allows all users to view, search, add and edit evidence item in the database. The second level of access allows the administrator to have the same rights as a first level user, in addition to the rights to assign or remove users and view program activities (such as who viewed which items, etc).

2 Visual user interface

A visual interface was created for interaction between the program and user. The interface is in a form-based, web-like format for user convenience.

.

3 Windows XP compatible and user friendly

The program was designed for the Windows XP platform. It was made user friendly in order to provide the most ease of use of the program.

.

4 Allow easy backup

The program is in a format that allows easy and frequent backups in order to protect important information in the database.

.

5 System failure must not affect database

The program is stable running in Windows XP. If a system failure occurs, the database will not be affected by it and data is not corrupted.

6 Add evidence and attach file(s)

All users are able to add new evidence and details of the evidence. They are also able to attach any type of files (e.g. pictures, documents) to a piece of evidence.

7 Edit evidence

All users are able to edit evidence in case they mistyped certain information about the evidence when attempting to add new evidence to the database. However, the original entry will not be deleted. Instead, the edited entry is added to the database as if it is a new entry and all the information of the previous record remained the same to ensure nothing was erased intentionally or accidentally. The new record is linked to the old record by giving a field in the old record the new records key number.

.

8 Search database with specific field

All users are able to search through the database with case number, officer ID, type, description, etc. A list of corresponding evidence is presented according to the specified field(s).

9 Add Chain of Custody

All users are able to add new entries to the chain of custody of a particular evidence item. To protect the chain of custody, no users (including administrator) are allowed to edit or delete any entry. When editing, entries are simply added on to the end of the table.

10 No evidence-related information can be deleted within the database

To assure the validity of the evidence and chain of custody information, no item in the evidence inventory, log table and chain of custody database tables can be deleted. Evidence that is no longer needed (i.e. case closed) may be physically destroyed but the record will remain in the database.

11 Program log

All activities within the database are recorded. Examples include logging in or out of the program, adding/editing items, searching/viewing items, who performed each action, date/time, etc.

2 Resultant Design Constraints

The resultant design constraints of the project are listed below with detailed description.

1 Database Size

The database size is made expandable and program is able to handle the increasing number of evidence entered.

2 Disk Space

. The database suited the disk space limits that the client has. While the project was in progress, the size of the client’s hard disk drive was 160GB; therefore the database shall not exceed that size.

3 Computer Resources Usage

Since computer resources are limited, the program efficiently managed the usage of computer resources and allowed multi-tasking on the computer stations.

4 Technical Support

The program does not require any technical support beyond the help page provided within the program.

5 Budget Limitation

With the limited budget provided, all required hardware was inexpensive, and all required software was freeware which were available on the internet for the team to use.

2.3 Approaches Considered and Used

This section discusses the technologies that the team considered for the project, gives reason(s) why they were considered and why they were not selected. Then, the technologies that the team selected are included with detailed explanation of why they were chosen.

1 Technologies Considered – For Database

A number of programming languages and platforms were taken into consideration for developing the program. Since the program contains a large database, several technologies had been considered for the creation of the database.

Microsoft Access

Microsoft Access is useful for creating a database. It provides an easy-to-use interface for programmers to create, view, and edit tables in a database. However, to use Microsoft Access, the software must be bought and installed into the client’s computers, which requires additional financial input. In additional to that, Access does not work as well with the language for GUI as MySQL.

Microsoft Excel

Microsoft Excel is another useful program for creating tables. As a spreadsheet program, it provides a user-friendly interface for programmers to view, and modify tables. Again, to use Microsoft Excel, the software must be purchased and installed into the client’s computers, which requires additional financial input. In additional to that, Excel does not work as well with the language for GUI as MySQL.

2 Technology Selected – For Database

The following technology was selected for the project.

MySQL

MySQL is a powerful open-source database management software that is easy to implement. Although it does not provide a GUI, it can be used with other languages such as Java or PHP to provide both database and graphic user interface. It also works well with the Apache web server to provide different levels of user accessibility (which was very useful for the project). One drawback was that most of the team members did not have sufficient knowledge in MySQL, thus a lot of research was required to understand how the software behaved and the syntax for the language.

3 Technology Considered – For GUI

The team considered several languages for the user interface, listed as follow:

Visual Basic/C++/C#

The team considered using Visual Basic/C++/C# because these languages were familiar to most of the team members. However, using these languages would require a platform for the GUI implementation, which then the team considered Visual Studio .NET. This lead to the requirement that all team member computers must have Visual Studio .NET installed for development purposes. The biggest drawback the team found was if the team wanted to code the program together, a computer lab that had Visual Studio .NET installed had to be used. In addition, the team may not have been able to connect to the server if university administration did not permit networking capabilities.

Java

The team also considered using Java to implement the GUI. It is a language that all team members understand and have experienced with. Java is easy to use and works well with MySQL as the database. However, using Java to implement GUI requires all client computers to have the Java platform installed. The team figured that would be time-consuming. Therefore Java was not selected for the project.

4 Technology Selected – For GUI

The team selected the following technology for the graphic user interface for the project.

PHP with HTML

The team members did a lot of research on which language was the most suitable programming language to use for the program. Since MySQL was picked to implement the database, the team wanted a language that worked well with MySQL. Since Java was not the best choice, the team decided to use PHP with HTML to develop the GUI. One main reason was that the team decided to implement the GUI as a web-based, form-liked interface, which PHP and HTML provided the best combination. Besides, PHP had build-in security features that the team could use, which were powerful and effective.

2.4 Detailed Design

This section describes in detail the design of the end product.

The end product consists of two main components: the database and the graphic user interface (GUI). For each component, a detailed description and explanation is provided in this section and diagrams are used to elucidate their implementation.

Below is the overall architecture of the end product. The detailed structure of each component is described in the following sub-sections.

[pic]

Figure 2.4 Overall architecture of the project software

1 User Level

Due to security reasons, there are two levels of users. Level-1 users are the basic users while level-2 users were permitted to inspect the database. Neither user levels were allowed to delete any item in the database. There was no option as “delete” within the database or GUI.

2 Level-1 users

All users in this level are permitted to view, search, add and edit evidence item in the database. The following is the overall structure for level-1 users.

[pic]

Figure 2.4.1.1a Permitted actions for level-1 users

As the user logs in, the system searches for the username in the database, verifies if the password (upon system encryption) is consistent with the username, and identifies if the user is a level-1 user. The system then authorizes the user and allows him or her to conduct the permitted actions.

For all actions or features not permitted to this user level, the action tab or menu bar is disabled so that the user can not select them. All actions taken by level-1 users are recorded in a separated log table which only level-2 users can view.

Below are the detailed descriptions of the different features that level-1 users have access to:

Add evidence item

The user is able to add new evidence to the database by selecting the “New Evidence” in the GUI. The “New Evidence” page allows the user to fill in the information needed for adding new evidence to the database (e.g. case number, officer ID, type of evidence, and description). The GUI was form-based which allowed easy user interaction.

[pic]

Figure 2.4.1.1b New evidence screenshot

Add to Chain of Custody

In order to preserve the chain of custody, every time the evidence was handled (e.g. moved, checked out or sent to a lab) that action is recorded into the log file. The user has to search for the particular evidence item first (see the search sub-section below for details), then click on the “New Custody Event” tab for that item and add a new entry to the chain of custody.

The “Chain of Custody” page requires the user to enter information of the destination of the evidence, source, and the officer ID of who authorized it. If evidence is to be checked out, the user is required to electronically sign using the attached digital signature pad. An electronic copy of the signature is stored in the database to confirm that this user had checked out this evidence at a certain time.

Search/View item

The user can search and view a piece of evidence or a group of similar evidence by clicking on the “Search” tab, where the user will be able to fill in different information (e.g. case number, officer ID, type of evidence) to execute the search. For example, the user can search by providing the case number of the evidence. Then, all the evidence with that case number would be shown. With different information provided, a different list of evidence would be displayed. Using different search fields allows for more narrowed search results.

[pic]

Figure 2.4.1.1c Search page screenshot

After a piece of evidence is listed in the search page, the user can view the details of the evidence by clicking on an associated button which has that evidence’s key number for the text. A list of attributes about the evidence is shown and the user cam also view the chain of custody of that piece of evidence, add a new entry to the chain of custody for that item, edit the items data or attach a file to the piece of evidence.

[pic]

Figure 2.4.1.1d Details of a evidence screenshot

Logout

By clicking on the “Logout” tab, the user is logged out and the program returns to the log in page.

2 Level-2 users

All users in this level have all the permissions for level-1 users. Additional features are available in this user level such as viewing records of program activity (e.g. what item had been edited, which user viewed what item). The following is the overall structure of the level-2 user.

[pic]

Figure 2.4.1.2 Overall structure of level-2 users

Most features available to level-2 user are identical to those of level-1. However, some addition features are only available to level-2 users. Detailed descriptions are given as followed:

Supervising Database

Since the level-2 user has a higher permission level over the level-1 user (level-2 is usually assigned to supervisors or chiefs of department), therefore the system allows the user to inspect the database and view actions taken place within the database. There are two options available after clicking “Database Information”.

Add/delete user

To add a new user to the system, a form-based interface was shown and the user was asked to fill out information such as username, password, user ID (i.e. police officer ID), and the level of access was assigned to the new user. The level-2 user “boonepd” is the default user and it is not able to be deleted.

In the event that a user no longer worked for Boone PD, his or her user privileges were revoked. As this user’s existence could be proven by referring to the log file, there was no need to keep that username information in the database. Also, this action prevents possible disgruntled employees from harming the information within the program.

View log history

Everything is recorded and stored into a log table whenever an action takes place such as viewing a piece of evidence, searching evidence, checking out evidence, etc. The description of the action, evidence involved and the user that conducted the action is recorded. A level-2 user is able to view the log history and see all the actions in the database. Although the level-2 user can view the log history, he or she cannot edit or delete the record.

2 Graphic User Interface (GUI) Design

The following figure shows the structure of the GUI of the final product and how it interacts with the database and the users. Once the user logs in, it will interact with the user and the database providing communication between the two according to the user’s level-of-access.

[pic]

Figure 2.4.2 Overview of the GUI design

For this project, the GUI was written in PHP with HTML and MySQL was used to implement the database. The GUI displays a web-like graphical interface for the interaction between users and the system. As the user logs in, the system opens a different interface for different user-levels and allows them to take different actions. Every time a user takes an action, the GUI communicates with the database and provides a corresponding response.

The following are the components included in the GUI:

2 User login

The login page asks for the user’s username and password. After providing the required information, the GUI passes the username and password to the database and searches through the list of users to verify if the user exists. If user is in the database, the system encrypts it and compares the password entered with the password stored in the database for verification. The GUI then displays a different graphic interface according to the user level and allows different user actions. If the user is not in the database, a warning sign would be shown and ask for correct username and password re-entered.

[pic]

Figure 2.4.2.1 Login screenshot

2 Level-1 user

The system interacts with the level-1 user and allows the permitted actions. The database will be searched or updated as required. The user interface allows user input in terms of textbox, checkbox, and drop-down list. Files can be attached to the database if necessary.

2 Level-2 user

The system interacts with the level-2 user and allows the permitted actions. The database will be searched or updated as required. The user interface allows user input in terms of textbox, checkbox, and drop-down list. Files can be attached to the database if necessary.

2 Logout

If “Logout” is selected, the system logs out that user and returns to the login page. That user was recorded into the program log file the action of “log out” The log is then updated with the person who logged out. The user is then directed to the login screen.

3 Database Design

Since the program is used for preserving the chain of custody for the evidence, the database has to store and handle a large amount of data with a high level of security. The following is the architecture of the database.

[pic]

Figure 2.4.3 Architecture of the database

2 Description

For this project, the database was constructed using MySQL. MySQL is a common database management programming tool. MySQL was not only easy to understand but also easy to use and implement. The basic architecture of the database is shown above in Figure 2.4.3.

The database contains several tables, and the following are the three main tables:

2.4.3.2 Username-Password Table

This table stores all user information such as username and password. Each username is a unique key for this table. The passwords are stored in a form of encrypted text using MD5 encryption to ensure security. Every time a user logs in, the program searches through this table to check if the username exists, compares the password to make sure the user entered the correct password, and checks what level of access would be allowed.

Table 2.4.3.2.1 Username-Password table definition

|Field name |MySQL attribute(s) and description |

| |Type: text, primary key |Max: 255 characters |

|username | | |

| |Unique username that identifies a specific officer. “Text” is used so Boone PD can switch the format of the |

| |username if desired. |

| |Type: blob |Max: 255 characters |

| | | |

|password | | |

| |The MD5-encrypted password the user supplied during registration. Type “blob” is used as the official MySQL |

| |website recommended this data type for passwords. Irrespective of the length of the password, MD5 would always |

| |encrypt it to be a 32 character string. |

|access_lvl |Type: int |Max: 1 number |

| |The access level permitted to the user. This field can only be ‘1’ or ‘2’ |

Table 2.4.3.2.2 Sample Username-Password table

|username |password |access_lvl |

|01293 |81270e8aaf8942871d92c4586a740298 |1 |

|019431 |9b1a420c868c52b311c91d5e47ade49e |2 |

Translation for entry of username 019431:

Level-1 user identified as 019431 has “9b1a420c868c52b311c91d5e47ade49e” as the MD5-encrypted password.

2.4.3.3 Evidence Inventory Table

This table is used to store information about the evidence. Since the case number is not unique, a unique number was assigned to each entry as the reference key. This number is then used as a reference key to a separated table storing the chain of custody of the particular evidence.

Table 2.4.3.3.1 Evidence inventory table definition

|Field name |MySQL attribute(s) and description |

| |Type: integer, primary key, auto-increment |Max: 11 numbers |

|e_key | | |

| |Unique number in evidence inventory table that differentiates versions from the same evidence item. |

| |Type: integer |Max: 11 numbers |

|key_num | | |

| |Key number that uniquely identifies the evidence item. This number corresponds to the key number of the same evidence|

| |item in the chain of custody table. |

| |Type: text |Max: 8 characters |

|case_num | | |

| |Number for the case that evidence item belonged to. The format is “04-00123”, meaning “case number 123 in year 2004”.|

| |“Text” is used to accommodate “-” and also takes less memory compared to integer type. |

| |Type: text |Max: 255 characters |

|officer | | |

| |Identifier that uniquely points to a specific officer. “Text” is used so that the program could still work if Boone |

| |PD decided to change its personnel ID format. |

|found_time |Type: datetime |Max: N/A |

| |Denotes the time the evidence item was found |

|found_addr |Type: text |Max: 255 characters |

| |Full address of the location where the evidence item was found |

| |Type: text |Max: 20 characters |

|type | | |

| |Type of evidence item found. Can be guns, knives, sticks, alcohol, drugs, paraphernalia, clothing, currency, trace, |

| |sexual assault, motor vehicle, or miscellaneous. |

| |Type: text, null allowed |Max: 255 characters |

|comments | | |

| |Additional information that is relevant to the evidence item. This is one of two fields in the table that can be left|

| |blank. |

| |Type: datetime |Max: N/A |

|timestamp | | |

| |Time the entry was recorded to the program. This time is inserted by the computer to prevent human data input error. |

| |Type: text, null allowed |Max: 255 characters |

|attachment | | |

| |The names of the digital file(s) associated with the evidence. If more than one file was uploaded, each file is |

| |separated by “**”. “**” was chosen as special characters (e.g. ?, /, *) can not to be used in a file name. This is |

| |one of two fields in the table that can be left blank. |

|update |Type: integer |Max: 11 numbers |

| |If an edit occurred, this field points to the updated record. |

Table 2.4.3.3.2 Sample entry of evidence inventory table (broken into two tables)

|e_key |key_num |case_num |officer |found_time |found_addr |

|19 |45 |04-21223 |009182 |2004-10-06 16:41:00 |2420 Lincoln Way, Ames IA 50010 |

|20 |67 |04-21390 |221353 |2004-10-16 19:56:01 |23 Lane 29, Main Ave, Boone, IA 54032 |

|type |comments |timestamp |attachment |Update |

|motor vehicle |Damaged car from the riot |2004-10-06 21:21:00 |null |0 |

|clothing |T-shirt from assailant |2004-10-16 20:34:01 |pic1.jpg**CCTV1.avi |27 |

Translation for e-key item 20:

At approximately 8.34 pm on Oct 16 2004, evidence item 67 was updated to e-key 27. In this version, evidence item 67 was related to case 04-21390. This clothing was found by officer 221353 at 23 Lane 29, Main Ave, Boone, IA 54032 on Oct 16 2004 at 7.56 p.m. Officer attached digital files pic1.jpg and CCTV1.avi. The comment left by the officer was “T-shirt from assailant”.

2.4.3.4 Chain of Custody Table

This table contains all the information for the chain of custody for any particular evidence item. It contains information such as the source of the evidence, the destination of evidence, which officer handled the evidence, etc.

Table 2.4.3.4.1 Chain of custody table definition

|Field name |MySQL attribute(s) and description |

| |Type: integer |Max: 11 numbers |

|key_num | | |

| |Key number that uniquely identifies the evidence item. This number corresponds to the key number of the same |

| |evidence item in the Evidence Inventory table. |

| |Type: integer, primary key, auto increment |Max: 11 numbers |

|c-key | | |

| |Unique number in chain of custody table that differentiates entries from the same evidence item. |

|source |Type: text |Max: 255 characters |

| |Full address of source of the evidence item. |

|dest |Type: text |Max: 255 characters |

| |Full address of destination of evidence item. |

|officer |Type: text |Max: 255 characters |

| |Identifier that uniquely points to a specific officer. “Text” is used so that the program can still work if |

| |Boone PD decided to change its personnel ID format or wished to implement username. |

| |Type: text |Max: 255 characters |

|sign | | |

| |Provides the name of the image file that stored the signature of the officer that authorized the transfer of |

| |evidence to or from Boone PD. Format is “21.jpg” where “21” is the c-key identifier number. |

|comments |Type: text, null allowed |Max: 255 characters |

| |Additional information that is relevant to the evidence item. This is the only field in the table that can be |

| |left blank. |

|timestamp |Type: datetime |Max: N/A |

| |Time the entry was recorded to the program. This time is inserted by the computer to prevent human data input |

| |error. |

Table 2.4.3.4.2 Sample entry of Chain of Custody table

|key_num |c-key |source |dest |officer |sign |comments |timestamp |

|41102 |21 | 213 Long Avenue, Ames, |923, 8th Street, Boone, IA|09281 |21.jpg |Iowa lab said victim’s DNA |2004-11-06 |

| | |IA 19042 |50036 | | |was found |16:52:53 |

|41102 |34 |923, 8th Street, Boone, |213 Long Avenue, Ames, IA |11245 |34.jpg |Send evidence back |2004-11-07 |

| | |IA 50036 |19042 | | | |16:53:57 |

Translation for item c-key 21:

At approximately 4.52 pm on Nov 6 2004, evidence item 41102 came from Iowa Forensic Lab and was place in Locker 3. Officer 09281 authorized the incoming transfer with signature 21.jpg. The comments that officer 09281 made was “Iowa lab said victim’s DNA was found”

5. Log File Table

This table contains the log file which records all activities taken place within the program. It contains information of what time the activity was performed, what officer performed the activity, what kind of activity it was, and the detail description of the information involved with that activity.

Table 2.4.3.5.1 Log file table definition

|Field name |MySQL attribute(s) and description |

| |Type: datetime, primary key |Max: N/A |

|time_stamp | | |

| |Time the activity took place. This is the unique key of the table, since there would not be more than one |

| |activity took place at the same instant of time. |

| |Type: text |Max: 255 characters |

|username | | |

| |Username of the officer that performed the activity. This is not a unique key because there can have more than |

| |one entry for the same officer to perform multiple activities. “Text” is used so Boone PD can switch the format |

| |of the username if desired. |

| |Type: text |Max: 255 characters |

|activity | | |

| |Type of activity that the officer performed. i.e. login, logout, search, add evidence, add to chain of custody, |

| |etc. |

| |Type: text |Max: 255 characters |

|description | | |

| |Detailed description of the activity took place, including information involved. |

Table 2.4.3.5.2 Sample entry of log file table

|time_stamp |username |activity |description |

|2004-11-07 11:22:43 |01293 |Login to program |Password entered correctly; login successfully |

|2004-11-10 14:36:30 |013488 |Search evidence |Search for “alcohol” with description “beer”; |

| | | |search successfully; 4 entries listed |

Translation for item time_stamp 2004-11-07 11:22:43:

At time 11:22:43 am on Nov 7 2004, officer 01293 logged in to the system with the password entered correctly and the login procedure was successful.

6. Security

In order to keep the database secure, all users have to log in before having access to the database. The username-password table is used to store the username, encrypted user password and access level for each user. In the future the user may be required to sign on the digital signature pad every time he or she checks out evidence. That signature would be stored in the database with the chain of custody.

If a user forgot his or her password, the administrator may create one and e-mail the person. With the provided password, the user can access the system and change his or her password.

2.5 Implementation Process Description

After finalizing the design of the database and GUI during the first semester, the team started the implementation of the program by splitting the group into two sub-groups. One sub-group worked with the GUI while the other focused on the database.

1 GUI Implementation

The first sub-team started out from the draft design of the GUI and used the Xara Webstyle software to implement the overall GUI structure. For creating the logo for the program, the sub-team used Adobe Photoshop to generate the logo for the client. After the whole GUI structure layout was created (i.e. had most of the pages needed for the entire website created, the overall outlook fixed), the sub-team members started to implement the functionalities of the webpage.

The members started off by coding each of the functions manually using PHP and HTML, adding each function to the webpage one at a time and testing to make sure the function worked properly before moving on to the next function.

2 Database Implementation

The second sub-team first started researching how to use MySQL since none of the sub-team members had experienced with the software before. The sub-team members created a test database on their own computers to do the implementation. The online manual from the official website of MySQL was frequently referenced by the sub-team members to better understand how to implement the database with MySQL. Several guidebooks from the library were also used to help the sub-team members with the syntax of MySQL.

The members first tried to create the tables within the database at home. When database tables were created successfully, the members started investigating different database structures to find which one would serve the best for the program. With each different database structure, the team members tested each of them entirely before moving on to the next one. All commands used to implement the database were saved into a text document for later reference when MySQL code would be combined with the PHP code.

3 Digital Signature Pad Implementation

At the time this report was written, the digital signature pad was in the process of being implemented. Team members conducted internet research and also contacted the manufacturer for information regarding integrating its product with a custom program.

4 Barcode Scanner Implementation

Similar to the digital signature pad, the barcode scanner was in the process of being implemented at the time this report was written. Team members conducted internet research, opened up the scanner and also contacted the manufacturer for information regarding integrating its product with a custom program. A separate Y-cable is needed in order to power up and use the barcode scanner. With the addition of the Y-cable, the barcode scanner should be fully implemented into the system.

5 Suggestions to Improve Implementation

Several suggestions on improving the implementation process would be to start the implementation earlier, and have team members coded together more often than just implemented the code individually. Also, the team would have benefited a lot more during the implementation process if more research on the languages was done before the coding. The addition of good coding style (indentation, etc) would make the code much easier to read and to locate certain parts.

2.6 End-Product Testing Description

Testing was done throughout the process of implementation and precise testing will be done after the program is completed. Functionality testing was preformed after each function was implemented, both in GUI and database, to ensure the function worked as expected before moving on to the next function.

For GUI functionalities, team members made sure that the test results of the intermediate products worked correctly by comparing the test result with the database entries to ensure the results were as expected. During the time when the GUI was not combined with the database, the sub-team members responsible for the GUI part tested the functions by printing out the SQL commands on the screen instead of sending them to the database (since the database was not connected to the GUI yet). By checking with the commands, the sub-team members for the database development would know if that command was implemented correctly.

For database functionalities, the sub-team tested the tables on the test database created on team members’ home computers before implementation on the real server. The sub-team members tested to make sure the table structures were correct and tried to test the tables by inserting various test data into the database in order to try out different commands. After testing the database on team members’ own computers, the database was created on the real server so the GUI can access the database and more testing was done after connecting the two parts together.

Before connecting the GUI and database, all tests were done separately on either the GUI or the database. Although the test results came out as expected for the GUI and database, after putting them together, errors occurred and modifications were required. The biggest challenge was to get the two parts working together and communicating with each other. Functions were re-tested by team members to guarantee that it worked as expected and communicated correctly between GUI and database.

When the program is completed and fully tested by team members, the end product will then be tested on the client’s computers. The test subjects will be selected as non-team-member such as the client (officers in Boone PD), who have no idea of how the program was implemented. Since the user testing is the most important testing, the team will record in detail who tested which function with what kind of input. If the system crashes, errors will be marked down for follow-up modification and correction. Since test subjects did not implement the program, their test inputs to the program will be critical and unpredictable, thus their test results and feedback will be very helpful in assisting the team to modify the program and improve the functionality and performance of the program.

Re-tests will be schedule again after the team makes corrections to the program after the first round of user testing. Since time is a main issue to the team in developing the software, the amount of time for user testing will be limited, and the team will try to do as much as possible to make the program function correctly and effectively.

2.7 Project End Results

The team managed to meet most of the project objectives at the end of the semester. At the end of the project, all team members benefited from learning at least one new language (i.e. PHP and MySQL) and all members did manage to use this knowledge to fulfill and complete the technical portion of the project. Documents for the project included the project plan, design document, project poster and final report. The software will be running on the client’s computers after the final modifications are made according to the test results.

Besides completing the software for the client, the team also tried implementing the signature pad for electronic signature that would be attached to the database and also tried to configure the barcode scanner correctly. These additions would help in making the inventory system easier to use and more convenient to the client.

3 Resources Requirements

This section shows the original estimates, revised estimates and final counts for the resources needed to complete the project.

3.1 Personnel Effort Requirements

This section shows the original personnel effort estimation, revised personnel effort after taking into account completed tasks and modified estimations, and the final effort requirements for the project. The tasks referred in the tables below correspond to the tasks listed below:

Task 1: Problem Definition

Subtask 1a: Identify End User(s) and End Use(s)

Subtask 1b: Identify Project Constraints and Limitations

Task 2: Technology Considerations and Selection

Subtask 2a: Research existing systems

Subtask 2b: Research Existing Programming and Database Management

Languages

Task 3: End-Product Design

Subtask 3a: Automate Input Entry Procedure for Client

Subtask 3b: Break down project to facilitate implementation

Task 4: Prototype Implementation

Subtask 4a: Work delegation

Subtask 4b: Code Generation and Screenshot Mockups

Subtask 4c: Code documentation

Task 5: End-Product Testing

Subtask 5a: Individual Component Testing

Subtask 5b: Component Integration and Testing

Subtask 5c: Whole System Testing

Task 6: End-Product Documentation

Subtask 6a: Develop User’s Manual

Task 7: End-Product Demonstrations

Subtask 7a: Demonstration Planning

Subtask 7b: Faculty Advisors Presentation

Subtask 7c: Industrial Review Panel Presentation

Task 8: Project Reporting

Subtask 8a: Weekly E-mails of Project Status

Subtask 8b: Project Plan

Subtask 8c: Project Poster

Subtask 8d: Design Report

Subtask 8e: Final Report

These tasks were obtained from the Project Plan.

Table 3.1.1 shows the original personnel effort estimation. Table 3.1.2 shows the revised personnel effort after taking into account completed tasks and modified estimations. Table 3.1.3 shows the actual time spent doing each task by each person.

Table 3.1.1. Original estimated personnel effort hours

|Names |Task 1 |Task 2 |Task 3 |

|Barcode Scanner |15 |0 |$25.00 |

|Signature Pad |20 |0 |$96.00 |

|Poster |5 |0 |$50.00 |

3.3 Financial Costs

This section shows the original financial requirements and the modified financial requirements. Table 3.2.1 shows the original financial requirements, table 3.2.2 shows the modified financial requirements, and table 3.2.3 shows the actual project expenses.

Table 3.3.1. Original financial requirements

|Item |With Labor |Without Labor |

|Materials: |  |  |

|Barcode Scanner (1) |$25.00 |$25.00 |

|Poster |$50.00 |$50.00 |

|Subtotal |$75.00 |$75.00 |

|Labor at $10.50 per hour: |  |  |

|Andrew Bitterman (147 hours) |  |$1,543.50 |

|Jennifer Sanders (149 hours) |  |$1,564.50 |

|Royson Chong (153 hours) |  |$1,606.50 |

|Yin-Cheung Lo (151 hours) |  |$1,585.50 |

|Subtotal |  |$6,300.00 |

|Additional Hardware (time permitting): |  |  |

|Digital Signature Pad (1) |$60 (donated) |$60 (donated) |

|PDA (1) |$200 (donated) |$200 (donated) |

|Subtotal |$260 |$260 |

|Total |$335 ($75 direct cost) |$6,560 ($75direct cost) |

Table 3.2.2. Modified financial requirements

|Item |With Labor |Without Labor |

|Materials: |  |  |

|Barcode Scanner (1) |$25.00 |$25.00 |

|Poster |$45.00 |$45.00 |

|Subtotal |$70.00 |$70.00 |

|Labor at $10.50 per hour: |  |  |

|Andrew Bitterman (148 hours) |  |$1,554.00 |

|Jennifer Sanders (151 hours) |  |$1,585.50 |

|Royson Chong (144 hours) |  |$1,512.00 |

|Yin-Cheung Lo (147 hours) |  |$1,543.50 |

|Subtotal |  |$6,195.00 |

|Additional Hardware (time permitting): |  |  |

|Digital Signature Pad (1) |$60 (donated) |$60 (donated) |

|PDA (1) |$200 (donated) |$200 (donated) |

|Subtotal |$260 |$260 |

|Total |$335 ($70 direct cost) |$6,455 ($70 direct cost) |

Table 3.2.3. Actual financial requirements

|Item |With Labor |Without Labor |

|Materials: |  |  |

|Digital Signature Pad (1) |$96 |$96 |

|Barcode Scanner (1) |$21.28 |$21.28 |

|Poster |$45.00 |$45.00 |

|Subtotal |$162.28 |$162.28 |

|Labor at $10.50 per hour: |  |  |

|Andrew Bitterman (90.75 hours) |  |$952.88 |

|Jennifer Sanders (147 hours) |  |$1,543.50 |

|Royson Chong (89 hours) |  |$934.50 |

|Yin-Cheung Lo (76 hours) |  |$798.00 |

|Subtotal |  |$4,228.88 |

|Total |$162.28 |$4,391.16 |

Changes:

Poster – The budget for the poster was initially overestimated. The actual cost of creating the poster was $45, a reduction of $5 from the original estimate of $50.

Signature Pad – The team had expected the signature pad to possibly be donated, but was unable to find a donor. Instead, a moderately price pad was found online.

Labor – As it took less time than expected to complete the project, this factor affects the cost of labor. Therefore, the reduction of $2,063.84 is directly attributed to the modified work time (402.75 hours at $10.50/hour - $4,228.88) compared to the original estimate (600 hours at $10.50/hour - $6300).

4 Schedules

This section shows the Gantt charts for the project. In the first Gantt chart (Figure 4.1.1), the first bar in each subtask (blue) shows the projected timeline for the original project schedule. The second bar in each subtask (red) shows the modified schedule for the project, and the third bar (green) shows the final resulting schedule for the project.

The second Gantt chart (Figure 4.2.1), shows the schedule for class deliverables for the team. This schedule has not been modified, as the deadlines for the deliverables has not changed.

4.1 Project Schedules

The project schedule was drastically changed because the team members were very busy during the end of the first semester. The design had been planned and a base front-end mockup had been made, so the team decided to wait until after the summer break to start coding hoping that they would have time to learn more about PHP and MySQL over the summer. The resulting schedule can be seen in the green bars in Figure 4.1.1.

[pic]

Figure 4.1.1 Project schedule

4.2 Deliverable Schedule

The deliverable schedule was not changed at all because the deadlines were set by the class facilitators and could not be modified. The final schedule is shown in Figure 4.2.1.

[pic]

Figure 4.2.1 Deliverable schedule

5 Closure Material

This section shows the project evaluation, commercialization considerations, and recommendations for additional work. It also includes the lessons learned, risks and risk management strategies and contact information of parties involved in the project.

5.1 Project Evaluation

This section contains a table of milestones that were set in the original Project Plan. This table was evaluated based on the following scale: greatly exceeded, exceeded, fully met, partially met, not met or not attempted. Brief explanations are provided to elucidate the reason(s) for the status of the milestone.

Table 5.1.1 Project milestones, level of success and brief explanation.

|Problem definition: |Exceeded |

|The team understood the client’s problem and strived to create a solution based on the requirements and specifications provided by the |

|client. |

|Research: |Partially met |

|Team members gained significant knowledge through research and working with PHP, advanced HTML, MySQL and database design and management. |

|However, at the time this paper was written, the barcode scanner and digital signature pad were not implemented yet. The team was still |

|researching ways to implement these items. |

|Technology selection: |Greatly exceeded |

|The team had selected technologies that extremely suited the project needs. Apache, PHP and MySQL were created to work together. As such, |

|there was no concern of technology incompatibility. In addition to that, Apache, PHP and MySQL are open source programs. This meant that |

|none of the limited $250 budget was used to buy development tools. |

|Concept design: |Fully met |

|The team spent a significant amount of time researching an optimal design to ensure that all components would work efficiently together. |

|End-product design: |Partially met |

|The team had a clear idea of how to code the software components. As mentioned before, at the time this report was written, the team was |

|still trying to implement the barcode scanner and digital signature pad. |

|Prototype implementation: |Partially met |

|As explained before, the team was still striving to implement the barcode scanner and digital signature pad at the time this report was |

|written. |

|End-product testing: |Partially met |

|For software components, the team was able to test them in isolation and made sure that they functioned as expected. However, since the |

|barcode scanner and digital signature pad were in the progress of being implemented, they were not in the end-product testing. |

|End-product documentation: |Fully met |

|The team had created a user’s manual to assist new users in manipulating the evidence inventory and tracking program. |

|Project reviews: |Not attempted |

|At the time this report was written, the project review had not taken place. This report will be updated after the reviews. |

|Project reporting: |Fully met |

|The team had managed to create the required documentations and submitted to the faculty advisor, client and course instructors within the |

|designated time. |

|End-product demonstration: |Not attempted |

|At the time this report was written, the end product demonstration had not taken place. This report will be updated after the demonstration.|

As can be inferred from the table above, the team was partially successful in implementing the entire project. If the hardware attachments were not taken into account, the team had met the functional requirements.

5.2 Commercialization

The team decided not to commercialize this program. The reason for the decision was that there was never any intention to sell the program upon completion. This program was developed strictly to facilitate the evidence inventory and tracking process for Boone PD.

However, if it were to be sold commercially, it would most likely have a price of approximately $5000. As can be seen by Table 5.2.1, the cost of creating the program was $4391.16. Additional markup for profit sake could be imposed.

Table 5.2.1 Summarized cost of creating the evidence inventory and tracking system

|Category |Cost |

|Materials |$162.28  |

|Total labor at $10.50 per hour |$4,228.88  |

|Subtotal |$4,391.16 |

|Profit mark up |$608.84 |

|Total |$5,000.00 |

The possible clients for the program would most likely be other police departments as well. Considering that the chain of custody procedure was relatively similar for all police stations, it would not be too difficult to tweak the program to serve their needs. Among the most likely customizations would be changing the overall color scheme of the HTML documents and also replacing the logo with the respective police badge image.

5.3 Recommendations for Additional Work

Assuming if another team was to continue working on what has been done, the team would like to suggest the following features to them:

1. Program extension to PDA

Boone PD initially wanted an additional small program that could run on PDAs. This program would allow officers to immediately record information regarding the evidence at its crime scene. Upon return to the police station, the record could be transferred digitally and direct to the server. This feature would certainly reduce paperwork.

2. Fingerprint scanner as added user authentication

In addition to implementing a username-passwords user authentication system, the team could also integrate fingerprint scanning as an added user verification feature. Considering that usernames and password could be forgotten (or worse, remembered by someone else) fingerprint comparisons would definitely uniquely identify a user.

3. Signature comparison as added user authentication

If the team or client wanted a stronger user authentication measure without additional hardware, the team could develop a signature comparison module. This module would be used during the chain of custody process to ensure that only chosen personnel could authorize the transfer for evidence items. Although signatures can be forged, this addition could be made to send an alert to the administrator if there was an inconsistency for a trusted signature.

5.4 Lessons Learned

This section presents views on lessons the team learned from working on this project. It includes what was felt to have been done right and what was not executed successfully. It also talks on the technical skills and non-technical skills gained from working on this project. Finally, a subsection is dedicated to a reflection of what the team would do differently if this project was to be implemented again.

5.4.1 Successful implementations

Listed below are the actions that the team did correct for the project:

• Created satisfactory documentation as required by the senior design course.

• Set up a proper MySQL server that was capable of limiting access to a specified group of computers and users.

• Integrated PHP scripts that communicated with the MySQL database server.

5.4.2 Unsuccessful implementations

Listed below are the objectives that the team did not correct implement for the project:

• Hardware extensions have yet to be implemented. Upon further investigation, the team realized the barcode scanner needs an uncommon Y-cable in order to function. The digital signature pad requires a specialized interface in order to export the captured signature into an image file.

• File attachments were not successfully implemented yet as the team was unable to discover the source of the error. PHP documentation materials suggested server configuration incompatibilities

• PHP session management were yet to be implemented. Team members found difficulty in deciphering the session management in PHP.

5.4.3 Technical knowledge gained

Listed below are the technical skills that the team learned from the project:

• Team gained good technical skills in PHP scripting language.

• Team had good exposure to MySQL database management software and general principles of database design and implementation.

• Team learned more advanced HTML techniques such as implementing custom frames, Javascript and logos.

• Team learned about implementing hardware extensions to a computer (i.e. barcode scanner and digital signature pad)

5.4.4 Non-technical knowledge gained

Listed below are the non-technical skills that the team learned from the project:

• Team members learned discipline as each of them had to set aside time each week to work on project in addition to part time jobs and classes.

• Team members learned initiative as many times throughout the project, members had contributed extra without being instructed to.

5.4.5 What would have been done differently if the project was redone?

If this project was redone by the team, the actions listed below are what would have been done differently:

5.4.5.1 Start implementation earlier

The team would have started the actual coding of the project in Spring 2004. That way, the team could have presented the prototype to the new client within the beginning of Fall 2004 semester. With that done, all the team had to do was refine the program and conduct more through testing throughout the semester.

5.3.5.2 Allocate more people toward PHP development

The team would have allocated more people to churn PHP codes for the program.

As of the beginning of the second semester, the team allocated two people to work on the PHP coding and two people for database design and management in addition to implementing the barcode scanner and signature design pad. It may have been more appropriate to get the entire team to work on PHP.

5.5 Risk and Risk Management

This section presents the risks that the team anticipated, the risks that the team encountered and how the team managed to overcome these risks.

5.5.1 Anticipated Potential Risks and Planning Management

These are the risks and risk management strategies that the team anticipated for this project:

5.5.1.1 Lack of professional expertise

As most team members were not familiar with practical database development and management or PHP scripting, it required time for those team members to learn MySQL and PHP. Underestimating this risk may result in a delay of the release of the product or incompletion of the project. This particular risk could be reduced with good time and task management.

5.5.1.2 Loss of team member

To deal with the loss of a team member, each person was required to keep accurate and detailed information on their part(s) so that other team members could fully understand what the person had done and pick up from there on.

5.5.1.3 Loss of team faculty advisor

In the event that a team advisor was taken out from the project, development will still continue as there was another faculty advisor remaining. If both faculty advisors left the project, then the course instructors would be designated as the project advisors.

5.5.1.4 Loss of project data

The risk of losing project data could have a huge impact on the project timeframe and completion. If data was lost, time spend on the project double as previous effort will have been wasted. A possible solution to avoid such risk was to have multiple backups in different locations. Backups were made regularly, and copies of backups were kept both in the project ftp folder and on individual team members’ computers. A hard copy of project documentation was made and kept by an academic advisor and/or team members.

5.5.1.5 Limited resource availability

Due to the limited $250 budget, resources must be restricted to low-cost software and hardware. Donations were an option considered. If a few of the more expensive items were donated, this would help in keeping cost down. If certain features could not be obtained cheaply and the project goes significantly over budget, the feature may be dropped or changed.

5.5.2 Anticipated Risks Encountered and Success in Management

This section discusses the foreseen risks that did occur during the project. The action(s) taken to overcome the risk and result(s) are documented briefly.

5.5.2.1 Lack of professional expertise

Technical guidance books were purchased or loaned from libraries to boost technical expertise of team members. In addition to that, the team fervently referenced the official websites of the respective technologies were being using, namely PHP and MySQL (). The team referred to the websites to verify accuracy of syntax and view sample codes.

5.5.2.2 Loss of team faculty advisor

Midway through the semester, one of the faculty advisors, Prof. Mani Mina was transferred to another project. The team continued working with the remaining faculty advisor, Prof. Thomas Daniels.

5.5.2.3 Limited resource availability

Since the team used open source software (Apache, PHP and MySQL) there was no cost incurred in obtaining the software. As for the hardware components (barcode scanner and signature pad) the team purchased models that were not over the budget. Coincidentally, the cheaper models had all functionalities that were required.

7 Unanticipated Risks Encountered, Attempts to Manage and Level of Success

This section lists the risks that the team did not expect to occur during the project. Countermeasures taken and relative levels of success are discussed briefly for each unanticipated risk that happened.

5.5.3.1 Unable to work on hardware

The team did not expect the hardware to not work immediately after connecting it to the computer.

For the barcode scanner, it would not power up. After opening up the barcode scanner and noting any numbers printed, the team searched on Google to find more information about the respective components. Finally, after consulting the faculty advisor, it was concluded that a “Y-cable” that routes power to the barcode scanner was needed.

For the digital signature pad, it worked except that it needed a driver to convert the information to an image file. To solve this problem, a team member was dedicated to either search for the driver from the internet or writes the driver.

5.5.4 Resultant Changes in Risk Management

After encountering the non-immediate operational hardware situation, the team appended the actions taken to the project documentation.

5.6 Project Team Information

Here are the contact information of the client, faculty advisor, and all team members.

5.6.1 Client information:

|Client: |Boone Police Department |

|Contact person - | |

| Primary: |Chief Officer William Skare |

| Secondary: |Assistant Chief Officer Dan Anderson |

|Address: |923, 8th Street, Boone, Iowa 50036 |

|Tel: |515-432-3456 |

|Fax: |515-432-1564 |

|E-mail - | |

| Primary |bskare@mail.city.boone.ia.us |

| Secondary |dananderson@mail.city.boone.ia.us |

5.6.2 Faculty advisor information:

|Name: |Prof. Thomas Daniels |

|Office address: |3222 Coover Hall, Iowa State University, |

| |Ames, Iowa 50010-3060 |

|Tel: |515-574-8375 |

|Fax: |515-294-3637 |

|E-mail: |daniels@ iastate.edu |

|Website: |clue.eng.iastate.edu/~daniels |

5.6.3 Student team information:

Team leader:

|Name: |Andrew Bitterman |

|Major: |Computer Engineering |

|Address: |5214 Frederiksen Court, Ames, IA 50010 |

|Tel: |515-572-7937 |

|E-mail: |abitterm@ iastate.edu |

Communication coordinator:

|Name: |Jennifer Sanders |

|Major: |Computer Engineering |

|Address: |614 Billy Sunday Rd. #107, Ames, IA 50010 |

|Tel: |515-292-5249 |

|E-mail: |jennif@ iastate.edu |

Team members:

|Name: |Yin-Cheung Lo |

|Major: |Computer Engineering |

|Address: |302 Hickory Drive #3, Ames, IA 50014 |

|Tel: |515-441-0259 |

|E-mail: |lolo@ iastate.edu |

|Name: |Royson Chong |

|Major: |Computer Engineering |

|Address: |2717 West Street, Apt #5, Ames, Iowa 50014 |

|Tel: |515-441-0325 |

|E-mail: |royson@ iastate.edu |

5.7 Closing Summary

This project served to solve a recurring problem that Boone Police Department had. Evidence inventory and tracking activities conducted via manual transcription to index cards were not only tedious but also an increase in paperwork.

The team analyzed the problem, consulted the client and strived to produce an automated evidence inventory and tracking system. Open source software was used because the team felt it suited the needs the best. Apache, MySQL and PHP with HTML not only worked together flawlessly but were also free. A barcode scanner and a digital signature pad were bought in attempt to provide ease of data referral and user authorization.

Despite best efforts, at the time this report was written, this project was partially successful as the team was in the process of implementing the hardware attachments. Nevertheless, without the hardware features, the program works as expected. In addition to that, considering that there was still time before the project dateline, the team will continue to implement the barcode scanner and digital signature pad.

References



Official Apache website



Website for company that the digital signature pad was purchased from.



Official MySQL website



Official PHP website



Website of Xara Webstyle software

[pic][pic]

-----------------------

User

Graphic User Interface

(GUI)

Database

Level-1 User

Edit Evidence

Search/View item

Add Evidence/ Chain of Custody

Logout

Level-2 User

Edit Evidence

Search/View item

Add Evidence/ Chain of Custody

Logout

Supervising Database

View log history

Add/delete user

Database

Graphic User Interface (GUI)

Session Control

Level-1 User

Level-2 User

Database

Add

Add/delete user

Search/View

Evidence

Chain of custody

Log history

Evidence

Chain of custody

Edit Evidence

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download