1 Entity relationship diagram2 Normalization
Normalization involves eliminating data redundancy thus enforcing data integrity on a relational database through restructuring the database through the following stages;
To describe the normalization process, the first step is to identify relations in UNF.
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
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;
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
);
(
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 )
);
(
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”);
Essay Writing Service Features
Our Experience
No matter how complex your assignment is, we can find the right professional for your specific task. Contact Essay is an essay writing company that hires only the smartest minds to help you with your projects. Our expertise allows us to provide students with high-quality academic writing, editing & proofreading services.
Free Features
Free revision policy
$10Free bibliography & reference
$8Free title page
$8Free formatting
$8How Our Essay Writing Service Works
First, you will need to complete an order form. It's not difficult but, in case there is anything you find not to be clear, you may always call us so that we can guide you through it. On the order form, you will need to include some basic information concerning your order: subject, topic, number of pages, etc. We also encourage our clients to upload any relevant information or sources that will help.
Complete the order form
Once we have all the information and instructions that we need, we select the most suitable writer for your assignment. While everything seems to be clear, the writer, who has complete knowledge of the subject, may need clarification from you. It is at that point that you would receive a call or email from us.
Writer’s assignment
As soon as the writer has finished, it will be delivered both to the website and to your email address so that you will not miss it. If your deadline is close at hand, we will place a call to you to make sure that you receive the paper on time.
Completing the order and download