Database Normalization Project: Criminal Justice System Database

This project presents a comprehensive database normalization solution for a criminal justice system, starting with an entity-relationship diagram (ERD) and progressing through the normalization process. The solution details the transformation of initial, unnormalized relations (UNF) into first normal form (1NF), second normal form (2NF), and third normal form (3NF). The assignment covers tables such as Criminals, CrimeCase, Appeal, Sentencing, and PoliceOfficer, identifying multivalued and transitive dependencies and restructuring the database to eliminate data redundancy and enforce data integrity. The project includes SQL code to create the normalized tables, along with assumptions made during the database design process. This solution provides a valuable resource for students studying database design and normalization principles.

COVER PAGE

1 Entity relationship diagram
Figure 1: entity relationship diagram
2 Normalization
Normalization involves eliminating data redundancy thus enforcing data integrity on a relational
database through restructuring the database through the following stages;
 First normal form (1NF) - Normalization to first normal form involves removing all repeating
groups and decomposing the relations in UNF to a set of relations is 1NF.

 Second Normal Form (2NF) - Normalization to second normal form involves taking the relations
achieved in normalizing to 1NF and the removing all partial dependencies to obtain relations
that are in 3NF.
 Third Normal Form (3NF)- Normalization to third normal form involves taking the relations
obtained in second normal form and eliminating all the transitive dependencies to obtain
relations in 3NF.
To describe the normalization process, the first step is to identify relations in UNF.
 Criminals (Name, address, phoneNO, violentOffenderStatus, probationStatus, aliases)
 Crime (classifification, dateCHarged, appealStatus, hearingDate, appealCutOffDate,CrimeCodes,
criminals,AmountFile,charges,CourtFile,amountPaid,
paymentDate,chargesStatus,arrestingOfficers)
 Appeal(appealFilingDate,appealhearingDate, status, noOfTImesAppeal)
 Sentencing (startDate, endDate, numberOfViolations, typeOfSentence)
 PoliceOffice(Name, precint, badgeNumber, phoneContact, Status)
Criminals Table
UNF
Criminals (Name, address, phoneNO, violentOffenderStatus, probationStatus, aliases)
1NF
The name attribute is a composite attribute and can be split to firstName and lastName.
Criminals (firstName, lastName, address, phoneNO, violentOffenderStatus, probationStatus, aliases)
2NF
Partial dependencies exist in the relation because there is no unique attribute thus to eliminate the
partial dependencies a primary key criminalNumber is introduced.
Criminals (CriminalNumber,FirstName,lastName, address, phoneNO, violentOffenderStatus,
probationStatus, aliases)
3NF
Criminals (CriminalNumber,FirstName,lastName, address, phoneNO, violentOffenderStatus,
probationStatus, aliases)
CrimeCase Table
UNF
Crime (classifification, dateCHarged, appealStatus, hearingDate, appealCutOffDate,CrimeCodes,
criminals,AmountFile,charges,CourtFile,amountPaid, paymentDate,chargesStatus,arrestingOfficers)
1NF

Normalization to 1NF involves eliminating all multivalued attributes. The following multivalued
attributes can be derived from the relation in UNF
 A crime can have more than one arresting officer thus arrestingOfficers is multivalued
 A crime can have more than one crime code thus crimeCode is multivalued.
 A crime can be committed by one or more criminals so the criminals attribute is mutivalued.
 A crime can have one or more charges thus the charges attribute is mutivalued.
 A crime can have one or more appeals thus appealStatus attribute is multivalued.
Eliminating the mutivalued attributes results to the following relations;
CrimeCase (CrimcaseNumber, crimeClassification, detail, chargedDate, Hearingdate,
applicationCutOffDate, NoOfFiles, courtFee, amountPaid, paymentDueDate, chargeStatus)
CrimeCaseCriminals (CrimeCaseNumber, criminalNumber)
CriminaCaseCharges (CrimeCaseNumber, CrimeCode, crimeDetail)
CrimeCaseOfficer (CrimeCaseNumber, officerBadgeNumber)
2NF
CrimeCase (CrimcaseNumber, crimeClassification, detail, chargedDate, Hearingdate,
applicationCutOffDate, NoOfFiles, courtFee, amountPaid, paymentDueDate, chargeStatus)
3NF
CrimeClassfication determines the detail attribute and this is a transitive depencency which can be
removed to form a new table;
CrimeClassfication (crimeClassification, detail)
CrimeCase (CrimcaseNumber, crimeClassification, chargedDate, Hearingdate, applicationCutOffDate,
NoOfFiles, courtFee, amountPaid, paymentDueDate, chargeStatus)
Appeal Table
UNF
Appeal(appealFilingDate, appealhearingDate, status, noOfTImesAppeal)
1NF
No mutilvalued attributes
Appeal(appealFilingDate,appealhearingDate, status, noOfTImesAppeal)
2NF
Partial dependency exist because there is no unique attribute so to eliminate this, a key attribute
appealNumber is introduced.
Appeal (AppelNumber, appealFilingDate, appealhearingDate, status, noOfTImesAppeal)
3NF

Appeal (AppelNumber, appealFilingDate, appealhearingDate, status, noOfTImesAppeal)
CrimeCharges table
UNF
CrimeCharges (CrimeCode, Crime, Detail)
1NF
No mutivalued attributes.
CrimeCharges (CrimeCode, Crime, Detail)
2NF
No partial dependencies exist
CrimeCharges (CrimeCode, Crime, Detail)
3NF
No transitive dependencies exist
CrimeCharges (CrimeCode, Crime, Detail)
CriminalSentence Table
UNF
criminalSentence(Startdate, endDate, numberOf Violations, typeOfSentence,sentenceType,Details)
1NF
No multivalued attributes thus relation is already in 1NF
criminalSentence(Startdate, endDate, numberOf Violations, typeOfSentence,sentenceTypeDetails)
2NF
Relation has no candidate key thus there are partial dependencies existing in the relation.
criminalSentence(criminalCaseNumber, criminalNumber, Startdate, endDate, numberOfViolations,
typeOfSentence, sentenceType, Details)
3NF
There exists a transitive dependency because the sentenceType determines the details of the sentence
thus this can be decomposed to form a new attribute.
criminalSentence(criminalCaseNumber, criminalNumber, Startdate, endDate, numberOfViolations,
typeOfSentence, sentenceType)
SentenceType (Type, Details)

Table PoliceOfficer
UNF
PoliceOffice(Name, precint, badgeNumber, phoneContact, Status)
1NF
Name is a multivalued attribute which can be decomposed into firstName and lastName
PoliceOffice(FirstName, lastName, precint, badgeNumber, phoneContact, Status)
2NF
BadgeNumber is nominated as the candidate key thus there are no partial dependencies.
PoliceOffice(FirstName, lastName, precint, badgeNumber, phoneContact, Status)
3NF
PoliceOffice(FirstName, lastName, precint, badgeNumber, phoneContact, Status)
3 Assumptions
The following assumptions were made;
 In the case where a criminal commits more than one crime the criminal is charged with more
than one crime code.
 A case can have more than one criminal linked to a single crime.
 A crime can result to a sentence or not if a criminal is not found guilty or if the criminal makes an
appeal and wins. The number of appeals is only limited to three appeals
APPENDIX
CREATE TABLE APPEAL
(
APPEAL_NUMBER CHAR(2) NOT NULL
, CRIME_CASE_NUMBER CHAR(4) NOT NULL
, APPEAL_FILING_DATE DATE NOT NULL
, APPEAL_HEARING_DATE DATE NOT NULL
, STATUS VARCHAR2(20) NOT NULL
, NO_OF_TIMES_APPEAL NUMBER DEFAULT 0
, CONSTRAINT APPEAL_PK PRIMARY KEY ( APPEAL_NUMBER )
);

CREATE TABLE CRIMINAL
(
CRIMINAL_NUMBER CHAR(4) NOT NULL
, CRIMINAL_NAME VARCHAR2(100) NOT NULL
, ADDRESS VARCHAR2(100) NULL
, PHONE VARCHAR2(20) NOT NULL
, PROBATIONSTATUS VARCHAR2(20) NOT NULL
, ALIASES VARCHAR2(100) NOT NULL
, CONSTRAINT CRIMINAL_PK PRIMARY KEY (CRIMINAL_NUMBER)
);
CREATE TABLE CRIMECASECRIMINALS
(
CRIME_CASE_NUMBER CHAR(4) NOT NULL
, CRIMINAL_NUMBER CHAR(4) NOT NULL
);
CREATE TABLE CRIMECASECHARGES
(
CRIMECASENUMBER CHAR(4) NOT NULL
, CRIMECODE CHAR(4) NOT NULL
);
CREATE TABLE CRIMECASE
(
CRIME_CASE_NUMBER CHAR(4) NOT NULL
, CRIME_CASE_CLASSIFICATION VARCHAR2(50) NOT NULL
, CHARGED_DATE DATE NOT NULL
, APPEAL_STATUS VARCHAR2(20) NOT NULL
, HEARING_DATE DATE NOT NULL
, NO_OF_FILES NUMBER NOT NULL
, COURT_FEE FLOAT NOT NULL

, AMOUNT_PAID FLOAT DEFAULT 0
, PAYMENT_DUE_DATE DATE NOT NULL
, CHARGE_STATUS VARCHAR2(20) NOT NULL
, CONSTRAINT CRIMECASE_PK PRIMARY KEY ( CRIME_CASE_NUMBER)
);
CREATE TABLE CRIMECHARGES
(
CRIME_CODE CHAR(5) NOT NULL
, CRIME VARCHAR2(100) NOT NULL
, DETAIL VARCHAR2(500)
, CONSTRAINT CRIMECHARGES_PK PRIMARY KEY ( CRIME_CODE )
);
CREATE TABLE CRIMINALSENTENCE
(
CRIME_CASE_NUMBER CHAR(4) NOT NULL
, CRIMINAL_NUMBER CHAR(4) NOT NULL
, START_DATE DATE NOT NULL
, END_DATE DATE NOT NULL
, NO_OF_VIOLATIONS NUMBER NOT NULL
, SENTENCE_TYPE VARCHAR2(20) NOT NULL
, CONSTRAINT CRIMESENTENCE_PK PRIMARY KEY (CRIME_CASE_NUMBER, CRIMINAL_NUMBER
)
);
CREATE TABLE CRIMECASEOFFICER
(
CRIMECASENUMBER CHAR(4) NOT NULL
, OFICE_BADGE_NUMBER CHAR(4) NOT NULL
);
CREATE TABLE SENETENCETYPE

(
TYPE VARCHAR2(20) NOT NULL
, DETAIL VARCHAR2(500) NOT NULL
, CONSTRAINT SENETENCETYPE_PK PRIMARY KEY (TYPE)
);
CREATE TABLE CRIMECLASSIFICATION
(
CRIME_CLASSFICATION VARCHAR2(50) NOT NULL
, DETAIL VARCHAR2(500) NOT NULL
, CRIME VARCHAR2(50) NOT NULL
, CONSTRAINT CRIMECLASSIFICATION_PK PRIMARY KEY ( CRIME_CLASSFICATION)
);
CREATE TABLE POLICEOFFICER
(
BADGE_NUMBER CHAR(4) NOT NULL
, NAME VARCHAR2(100) NOT NULL
, ADDRESS VARCHAR2(100) NOT NULL
, PHONE VARCHAR2(20) NOT NULL
, precinct VARCHAR2(50) NOT NULL
, STATUS VARCHAR2(20) NOT NULL
, CONSTRAINT POLICEOFFICER_PK PRIMARY KEY ( BADGE_NUMBER )
);
alter table CRIMECASECHARGES add constraint crimecasecharges_fk1 foreign
key("CRIMECASENUMBER") references "CRIMECASE"("CRIME_CASE_NUMBER");
alter table CRIMECASECHARGES add constraint crimecasecharges foreign
key("CRIMECODE") references "CRIMECHARGES"("CRIME_CODE");
alter table CRIMINALSENTENCE add constraint criminalsentence foreign
key("SENTENCE_TYPE") references "SENETENCETYPE"("TYPE");
alter table CRIMECASEOFFICER add constraint crimecaseofficer_fk2 foreign
key("OFICE_BADGE_NUMBER") references "POLICEOFFICER"("BADGE_NUMBER");

alter table APPEAL add constraint appeal_fk1 foreign key("CRIME_CASE_NUMBER")
references "CRIMECASE"("CRIME_CASE_NUMBER");
alter table CRIMECASECRIMINALS add constraint crimecasecriminals_fk1 foreign
key("CRIME_CASE_NUMBER") references "CRIMECASE"("CRIME_CASE_NUMBER");
alter table CRIMECASEOFFICER add constraint cimecaseofficer foreign
key("CRIMECASENUMBER") references "CRIMECASE"("CRIME_CASE_NUMBER");
alter table CRIMECASE add constraint crimecase_fk1 foreign
key("CRIME_CASE_CLASSIFICATION") references
"CRIMECLASSIFICATION"("CRIME_CLASSFICATION");
alter table CRIMECASECRIMINALS add constraint crimecasecriminals_fk2 foreign
key("CRIMINAL_NUMBER") references "CRIMINAL"("CRIMINAL_NUMBER");