Design and Implementation of a Criminal Justice System Database

This project focuses on the design and implementation of a database for a criminal justice system. It includes defining entities such as Criminals, Crimes, Appeals, and Police Officers, along with their attributes and relationships. The relational model is presented, outlining primary and foreign keys to ensure data integrity. The project further provides database implementation details with table structures for ProbationStatus, AppealStatus, SentenceType, CrimeCodes, Criminals, Sentencing, Police Officers, Crimes, and Appeals. SQL code is included for creating these tables, demonstrating the practical application of the database design principles. The database is designed to manage information on criminals, their offenses, sentencing details, and the involved police officers, aiming to provide a structured and efficient system for managing criminal justice data. The document concludes with a bibliography and appendix containing the SQL code used for table creation.

Running head: DATABASE DESIGN AND IMPLEMENTATION
Database Design and Implementation
Name of the student:
Name of the University:
Author Note

1
DATABASE DESIGN AND IMPLEMENTATION
Introduction
The information provide to us:
Criminals: Name, address, phone number, violent offender status (yes/no), probation status
(yes/no) and aliases.
Crimes: classification (felony, misdemeanour, other), date charged, appeal status (closed, can
appeal, in appeal), hearing date, appeal cutoff date (always 60 days after the hearing date),
arresting officers (can be more than one officer), crime codes (such as burglary, forgery, assault;
hundreds of codes exist), amount of file, court fee, Sentencing: start date, end date, number of
violations (such as not reporting to probation officer), and type of sentences (jail period, house
arrest, probation)
Appeals: appeal filling date, appeal hearing date, status (pending, approved and disapproved)
Note: Each crime case can be appealed up to three times.
Police officers: name, precinct, badge number, phone contact, status (active /inactive)
Database Design
According to the provided information the relational model provided below can be formed:
Criminals (CriminalID (primary key), CriminalName, Criminaladdress, phonenumber,
violentoffenderstatus, PID (foreign key), aliases)
ProbationStatus (PID (primary key), PStatus)
Crimes (CrimeID (primary key), CodeID (foreign key), CrminalID (foreign key), datecharged,
hearingdate, appealcutoffdate, BadgeNo (foreign key), amountoffile, courtfee)

2
DATABASE DESIGN AND IMPLEMENTATION
AppealStatus (AID (primary key), AppealStatus)
CrimeCodes (CodeID (primary key), Crime Type, Classification)
Sentencing (SentenceID (primary key), startdate, enddate, VNumber, SID (foreign key))
SentenceType (SID (primary key), SentenceType)
Appeals (AppealID (primary key), fillingdate, hearingdate, AID (foreign key), CrimeID (foreign
key))
Police officers (BadgeNo (primary key), PoliceName, precinct, PoliceContact, PoliceStatus)
Database Implementation
Probation Status Table
Appeal Status
Sentence Type

3
DATABASE DESIGN AND IMPLEMENTATION
Crime Codes
Criminals
Sentencing

4
DATABASE DESIGN AND IMPLEMENTATION
Police Officers
Crimes
Appeals

5
DATABASE DESIGN AND IMPLEMENTATION

6
DATABASE DESIGN AND IMPLEMENTATION
Bibliography
Coronel, C. and Morris, S., 2016. Database systems: design, implementation, & management.
Cengage Learning.
Hu, Y., 2016. Design and Implementation of Recruitment Management System Based on
Analysis of Advantages and Disadvantages of PHP Three-Tier. Romanian Review Precision
Mechanics, Optics & Mechatronics, (49), p.74.

7
DATABASE DESIGN AND IMPLEMENTATION
Appendix
SQL Code:
CREATE TABLE "SYS"."PROBATION_STATUS"
( "PID" NUMBER NOT NULL ENABLE,
"PSTATUS" VARCHAR2(30 BYTE) NOT NULL ENABLE,
CONSTRAINT "PROBATION_STATUS_PK" PRIMARY KEY ("PID")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE
DEFAULT)
TABLESPACE "SYSTEM" ENABLE
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE
DEFAULT)
TABLESPACE "SYSTEM" ;

8
DATABASE DESIGN AND IMPLEMENTATION
CREATE TABLE "SYS"."APPEAL_STATUS"
( "AID" NUMBER NOT NULL ENABLE,
"APPEALSTAUS" VARCHAR2(20 BYTE) NOT NULL ENABLE,
CONSTRAINT "APPEAL_STATUS_PK" PRIMARY KEY ("AID")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE
DEFAULT)
TABLESPACE "SYSTEM" ENABLE
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE
DEFAULT)
TABLESPACE "SYSTEM" ;
CREATE TABLE "SYS"."SENTENCE_TYPE"

9
DATABASE DESIGN AND IMPLEMENTATION
( "SID" NUMBER NOT NULL ENABLE,
"SENTENE_TYPE" VARCHAR2(20 BYTE) NOT NULL ENABLE,
CONSTRAINT "SENTENCE_TYPE_PK" PRIMARY KEY ("SID")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE
DEFAULT)
TABLESPACE "SYSTEM" ENABLE
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE
DEFAULT)
TABLESPACE "SYSTEM" ;
CREATE TABLE "SYS"."CRIME_CODES"
( "CODEID" NUMBER NOT NULL ENABLE,

10
DATABASE DESIGN AND IMPLEMENTATION
"CRIMETYPE" VARCHAR2(20 BYTE) NOT NULL ENABLE,
"CLASSIFICATION" VARCHAR2(30 BYTE),
CONSTRAINT "CRIME_CODES_PK" PRIMARY KEY ("CODEID")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE
DEFAULT)
TABLESPACE "SYSTEM" ENABLE
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE (INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE
DEFAULT)
TABLESPACE "SYSTEM" ;
CREATE TABLE "SYS"."CRIMINAL"

11
DATABASE DESIGN AND IMPLEMENTATION
( "CRIMINALID" VARCHAR2(20 BYTE) NOT NULL ENABLE,
"CRIMINALNAME" VARCHAR2(20 BYTE),
"CRIMINALADDRESS" VARCHAR2(30 BYTE),
"PHONENUMBER" NUMBER,
"VOILENTOFFENDERSTATUS" BLOB,
"PID" NUMBER NOT NULL ENABLE,
"ALIASES" VARCHAR2(20 BYTE),
CONSTRAINT "CRIMINAL_PK" PRIMARY KEY ("CRIMINALID")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE
DEFAULT)
TABLESPACE "SYSTEM" ENABLE,
CONSTRAINT "PID_FK" FOREIGN KEY ("PID")
REFERENCES "SYS"."PROBATION_STATUS" ("PID") ENABLE
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645