Appendix F - Expected Data Element Requirements_Final.pdf

Major Data Entity/Group Approx # of data elements

Sub-Group Comments Table Structure Notes

PROCESS ARRESTS in INTAKE SECTION 180 Staging Arrest and C-10 Data Data collected during processing an Arrest in Intake. Arrests can be processed to:

• PAPERED (incl RIP), NO PAPERED, FUGITIVE and TRANSFER to

OAG.

• FUGITIVES and TRANSFERs do not create a CASE.

• ARRESTS can be spilt into more than one Case.

• C-10 data is collected in Court by the AUSA assigned to C-10 for that day.

1 Table Enforced FK to ARREST Enforced FK to CASE

50 Staging Charge For SPLIT Cases, Charges are mapped to the appropriate case by the Screener.

1 Table Enforced FK to ARREST Enforced FK to CASE

27 Fugitive Data Fugitives do not create a CASE. They are e-filed to the Court. They do need to be queryable though.

1 Table Enforced FK to ARREST

11 Desk Sergeant Includes their signature blob.

Identifies which Desk Sergeant is the acting Desk Sergeant for the day.

Their signatures are applied to documents without their review.

1 table

60 Signature Data including signature images Determines which document types need signatures applied and whose signature should be applied. This data also captures the person's signature (stored in databse) for each e-filed doument.

AUSA signature images are stored in one table in Personnel Tracking System. We want to move to e-signatures so may abandon this table.

All other signature data is either FK to a CASE or an ARREST or helps describe what signatures are needed for each type of filing.

11 Requested Bonds Bond option USAODC will suggest to the Court with GRANT/DENY response.

1 Table Enforced FK to CASE and FK to Staging

17 WALES Log Records that a Criminal Investigation was done. 1 Table Enforced FK to ARREST

New 40 Supervisor Checklist Data Screener records checks completed (Criminal History review, Victim comments etc.) Attorney Work Product

2 CASES 85 Case data Includes Defendant information 1 Table

20 USAO Personnel Assignments (AUSAs, Supervisors, Victim Advocates, Paralegals, Staff)

Spread across several tables Enforced FK to CASE

5 Links to Codefendants Intersection table between Case and Person 1 Intersection Table with a LINK # Enforced FK to CASE

5 Case Type Flags The LOV values need to be re-worked by AUSA and mgmnt.

Defines types of cases. For example, Felon in Possession of a Gun (FIP), Hate Crime, Senior Citizen etc)

1 Table Enforced FK to CASE

New 5 Associated Persons Witnesses, Officers, Judges, Defense Attorneys, etc.

Intersection table

There are 2 tables currently that house non-unique Civilian and Officer Witnesses.

The other PERSON types can be derived from the data in CASE.

10 Witness Types for this Case Type and Case and Person intersection Also indicates if Witness needs Victim Notification letters.

1 table for Witness Types All enforced FK to Case/Witness or Investigation/Witness

35 Release Orders 1 Table Enforced FK to CASE

17 USAO-Created Gersteins 1 Table Enforced FK to CASE

7 Case Notes Summary 1 Table Enforced FK to CASE

New 20 Case Notes

NOTE: All codes are enforced to a Code table or to an embedded LOV.

7 Lab Data 1 Table Enforced FK to CASE

50 Property 1 Table Enforced FK to CASE

160 Papered Charges Only charges USAO is charging 1 Table Enforced FK to CASE

3 Disposition Reasons 1 Table Enforced FK to CASE

15 Evidence Orders 1 Table Enforced FK to CASE

9 Inventory data for Hard-Copy Files 1 Table Enforced FK to CASE

16 CPWL Carrying a Pistol Without a License 1 Table Enforced FK to CASE

11 UC Buy Officer sees a drug buy 1 Table Enforced FK to CASE

22 UUV Unauthorized Use of a Vehicle 1 Table Enforced FK to CASE

15 Victim Advocate Case Events 1 Tables Enforced FK to CASE

13 Defendant Statement 2 Tables Enforced FK to CASE and WITNESS

17 Emergency Response 1 Table Enforced FK to CASE

11 RIP Misc 1 Table Enforced FK to CASE

5 Supervisor Case Review Events/Comments 1 Table Enforced FK to CASE or INVESTIGATION

New 30 Tasks Assigned New 20 Documents/Evidence/Commnications/Filings/

Task Documents Formats are:

PDF, .DOC, .DOCX, .MSG, .JPG, .ZIP, .XSL, .XSLX, .WAV,

.mp4, .avi, mp4Extract.EXE, .txt, .png, .m4a, .tts, .jpg-w80-h80, .jpg-w64-h64, .mov, .msg

Types are:

Informations, Complaints, Release Orders, Gersteins, Arrest Packets, Bail Reports, Various types of Evidence, Emails, Filed Motions, Orders, Pleadings etc.

• Some documents are stored in the current database:

Fugitive docs, Informations, Complaints, Gersteins, Arrest Packets, and Bail Reports

• The rest are stored on the network.

Documents for a CASE are stored in a directory named with the CASE Nbr.

• Documents for an Arrest are stored in a directory named with the Arrest CCN.

New 20 Diversion New 20 Case Notes New 40 Pleas

CASE SUMMARY

PEOPLE 50 PERSON fields include:

PDID

CAD

Badge #

Not all fields apply to each person type Officers Civilian Witnesses Judges Defense Attorneys Victim Advocates

AUSA

Paralegals Support Staff

30 Addresses 1 Table Enforced FK to CIVILIAN WITNESS

1 Table Enforced FK to CASE

This table serves as a psuedo data warehouse. A procedure runs twice daily on Cases to keep this updated.

Fields include:

Next Court Date Next Court Event Lead Charge for 4 Case phases Warrant Flags AUSA Assigned etc.

10 Person Contact Information Phone #s Email Addresses

We have some of this information spread across several tables.

New 10 MugShots

4 INDICTMENTS 18 Indictment 1 Table 150 Charges for Indictment 1 Table

Enforced FK to INDICTMENT.

8 Indictment/Charge/Defendant This is an intersection table between

INDICTMENT/CHARGE/DEFENDANT. Since Indictments can be associated with more than one DEFENDANT/CASE and not all DEFENDANTS will be indicted on all CHARGES.

1 Table Enforced FKs to INDICTMENT, CHARGE, DEFENDANTS

16 Defendant 1 Table Links DEFENDANT to either a CASE or an

INVESTIGATION

FKs are enforced.

8 Defendant Alias 1 Table Enforced FKs to DEFENDANT

5 Additional Text Text added to the charge -- may not be needed 10 Poll Sheets Includes Grand Jury Session 1 table

Enforced FK to GRAND JURY SESSION 11 Indictments for Poll Sheet Intersection table 1 table

Enforced FK to INDICTMENT Enforced FK to GRAND JURY SESSION

16 Grand Jury Tracks GJ Sessions. These sessions are assigned to Indictments. 1 Table

5 INVESTIGATIONS 60 Investigation 2 Investigation Links Links Investigations together 1 table

Enforced FK to INVESTIGATION 3 Investigation Case Category 1 table

Enforced FK to INVESTIGATION 3 Investigation Disposition Reasons 1 table

Enforced FK to INVESTIGATION

NOTE: Warrants are tracked in a SQL Server database. All FKs are enforced and all LOV/Code tables are enforced.

WARRANTS 21 Arrest Warrants Note: There is also some SEARCH Warrant data in the current CMS that will need to included

5 Arrest Warrant Status Log 22 Search Warrants 5 Search Warrant Status Log 5 Affiants 4 Defendants 4 CCNs Criminal Complaint # 6 Arrest Warrant Intersection tables Arrest Warrant to Affiant (2)

Arrest Warrant to Defendant (2) Arrest Warrant to CCN (2)

6 Search Warrant Intersection tables Search Warrant to Affiant (2) Search Warrant to Defendant (2) Search Warrant to CCN (2)

13 Search Warrant (old -- from current CMS)

GENERATE CHARGING INFORMATION 25 Charging Data for Defendant Used to create Charging Documents when a request for a SEARCH or ARREST warrant is made. Requests come from Law Enforcement.

1 Table

11 Charges for Charging Document 1 table Enforced FK to CHARGING DATA

USAO PERSONNEL

New to Case Management

20 Personnel in Case Management Currently USAO stores this information in a local Personnel System.

However, for Case Management, Supervisors often use different Sections and AUSAs often handle Cases in different Sections than which they are assigned.

1 Table

10 Personnel Section Assignments 10 Personnel Position Assignments

Miscellaneaous 8 Case Contact Emails RIJISR_AUSA_EMAIL_ADDRESS

RIJISR_AUSA_EMAIL_CIA

2 tables No FKs needed

Appendix F Expected Data Element Requirements

Major Data Entity/Group Approx # of data elements

Table Structure Notes

ARRESTS 64 2 Tables in incoming Arrest Data to Staging area.

This data goes to 3 different tables within the current system. All enforced with FK to ARREST.

40 1 Table for incoming Arrest Data.

This data goes to 2 different tables within the current system. All enforced with FK to ARREST.

150 4 Tables 2 for Officer Witnesses 2 for all other Witness Types

11 1 Table Enforced FK to ARREST

40 2 Tables Enforced FK to ARREST

63 2 Tables Enforced FK to ARREST

70 6 tables 1 each for General Property, Alcohol, Drug, Firearm, Vehicle and Phone Enforced FK to ARREST

10 1 Table Enforced FK to ARREST

33 1 Table Enforced FK to ARREST

17 1 Table Enforced FK to ARREST

CASE 17 Incoming: 1 Table Loaded to: 1 Table

10 Incoming: 1 Table Loaded to: 1 Table

15 Incoming: 1 Table Loaded to: 1 Table

Incoming: 3 Tables Loaded to: 2 Table

Incoming: 3 Tables Loaded to: 2 Tables Warrant Condition needs to be loaded in new CMS. Not loaded presently.

Incoming: 1 Table Loaded to: 1 Table

NOTE: All codes are enforced to a Code table

Appendix F Expected Data Element Requirements

CHARGE 40

Incoming: 1 Table Loaded to: 1 Table Enforced FK to CASE

Incoming: 1 Table Loaded to: 1 Tables (CHARGE)

Incoming: 3 Tables Loaded to: 3 Tables Enforced FK to CHARGE

Incoming: 3 Tables Loaded to: 1 Tables (CHARGE)

Incoming: 1 Tables Loaded to: 1 Tables Enforced FK to CHARGE

PERSONS 15

ELEMENTS TO ARCHIVE

#1 2 #2 65 #3 7 #4 11 #5 2 #6 2 #7 2 #8 10 #9 10 #10 5 #11 15 #12 8 #13 2 #14 4 #15 4 #16 2 #17 5

Incoming: 9 Tables Loaded to: 9 Tables Enforced FK to CHARGE