Database Design and Implementation

This article discusses the design and implementation of a database for criminal records. It covers the information that needs to be stored, the relational model, and the SQL code for creating the tables. The article also includes a bibliography and an appendix with the SQL code.

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

12
DATABASE DESIGN AND IMPLEMENTATION
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE
DEFAULT)
TABLESPACE "SYSTEM"
LOB ("VOILENTOFFENDERSTATUS") STORE AS BASICFILE (
TABLESPACE "SYSTEM" ENABLE STORAGE IN ROW CHUNK 8192 RETENTION
NOCACHE 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)) ;
CREATE TABLE "SYS"."SENTENCING"
( "SENTENCEID" NUMBER NOT NULL ENABLE,
"STARTDATE" DATE,
"ENDDATE" VARCHAR2(20 BYTE),
"VNUMBER" VARCHAR2(20 BYTE),

13
DATABASE DESIGN AND IMPLEMENTATION
"SID" NUMBER NOT NULL ENABLE,
CONSTRAINT "SENTENCING_PK" PRIMARY KEY ("SENTENCEID")
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 "SENTENCING_FK" FOREIGN KEY ("SID")
REFERENCES "SYS"."SENTENCE_TYPE" ("SID") 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" ;

14
DATABASE DESIGN AND IMPLEMENTATION
CREATE TABLE "SYS"."POLICE_OFFICERS"
( "BADGENO" NUMBER NOT NULL ENABLE,
"POLICENAME" VARCHAR2(20 BYTE) NOT NULL ENABLE,
"PRECINCT" VARCHAR2(20 BYTE) NOT NULL ENABLE,
"POLICECONTACT" NUMBER NOT NULL ENABLE,
"POLICESTATUS" VARCHAR2(20 BYTE) NOT NULL ENABLE,
CONSTRAINT "POLICE_OFFICERS_PK" PRIMARY KEY ("BADGENO")
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)

15
DATABASE DESIGN AND IMPLEMENTATION
TABLESPACE "SYSTEM" ;
CREATE TABLE "SYS"."CRIMES"
( "CRIMEID" NUMBER NOT NULL ENABLE,
"CODEID" NUMBER NOT NULL ENABLE,
"CRIMINALID" VARCHAR2(20 BYTE) NOT NULL ENABLE,
"DATECHARGED" DATE,
"HEARINGDATE" DATE,
"APPEALCUTOFFDATE" DATE,
"BADGENO" NUMBER NOT NULL ENABLE,
"AMOUNTOFFILE" NUMBER,
"COURTFEE" NUMBER,
CONSTRAINT "CRIMES_PK" PRIMARY KEY ("CRIMEID")
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)

16
DATABASE DESIGN AND IMPLEMENTATION
TABLESPACE "SYSTEM" ENABLE,
CONSTRAINT "CRIME_FK4" FOREIGN KEY ("CODEID")
REFERENCES "SYS"."CRIME_CODES" ("CODEID") ENABLE,
CONSTRAINT "CRIME_FK3" FOREIGN KEY ("CRIMINALID")
REFERENCES "SYS"."CRIMINAL" ("CRIMINALID") ENABLE,
CONSTRAINT "CRIME_FK2" FOREIGN KEY ("BADGENO")
REFERENCES "SYS"."POLICE_OFFICERS" ("BADGENO") 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"."APPEALS"
( "APPEALID" NUMBER NOT NULL ENABLE,
"FILINGDATE" DATE,
"HEARINGDATE" DATE,

17
DATABASE DESIGN AND IMPLEMENTATION
"AID" NUMBER NOT NULL ENABLE,
"CRIMEID" NUMBER NOT NULL ENABLE,
CONSTRAINT "APPEALS_PK" PRIMARY KEY ("APPEALID")
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 "APPEAL_FK1" FOREIGN KEY ("AID")
REFERENCES "SYS"."APPEAL_STATUS" ("AID") ENABLE,
CONSTRAINT "APPEAL_FK2" FOREIGN KEY ("CRIMEID")
REFERENCES "SYS"."CRIMES" ("CRIMEID") 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)

18
DATABASE DESIGN AND IMPLEMENTATION
TABLESPACE "SYSTEM" ;