HS2021 Database Design and Use

Your company receives the following memo.

 

To: Database Consultant
From: City Jail Information Director
Subject: Establishing a Crime-Tracking Database System

 

It was a pleasure meeting with you last week. I look forward to working with your company create a much needed crime-tracking system. As you requested, our project group has outlined the crime-tracking data needs we anticipate. Our goal is to simplify the process of tracking criminal activity and provide a more efficient mechanism for data analysis and reporting. Please review the data needs outlined below and contact me with any questions.

 

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, amount paid, payment due date, and charge status( pending, guilty, not guilty)

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) Additional notes:

 

A single crime can involve multiple crime charges, such as burglary and assault.
Criminals can be assigned multiple sentences. For example, a criminal might be required to serve a jail sentence followed by a period of probation.

 

Create an Entity Relationship Diagram based on the memo. The E-R Diagram should include entities, attributes (columns), primary keys, and relationships between the entities. Make sure your E-R Diagram is normalised to Third Normal Form and is ready to be deployed in Oracle 12c SQL developer.

 

Create all the tables using SQL queries in Oracle 12c SQL developer. Make sure tables are created with attributes and constraints (i.e. PRIMARY KEY, FOREIGN KEY, CHECK constraints, etc.)

 

Execute all SQL queries in Oracle SQL developer 12c to implement the “City Jail” database in Oracle 12c. Please ensure all tables are created without errors.

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

Are you in dire need of assignment help in the UK? Can't figure out who can help you whenever you find yourself thinking, "Wouldn't it be great if I could pay someone to do my assignment?" With Myassignmenthelp.co.uk, you can fulfil your desires without any hassle.

Send us your requirements, and our paper writers will take care of your assignment worries quickly. So now, you don't have to worry about, "Where can I find someone to do my assignment for me in the UK?" Instead, let our experts provide you with the best assignment help in London, Bristol, Manchester, Liverpool and more!