Hs2021 Database Design And Use-Crime Assessment Answers

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.

Assignment requirements:

• First, based on the memo, create an initial database design (E-R model) for the city jail that indicates entities, attributes (columns), primary keys, and relationships between the entities. Use only the entities identified in the memo to develop the E-R model.  


Second, create a list of additional entries or attributes not identified in the memo that might be applicable to a crime-tracking database.
Furthermore, normalize your database design to 3NF so that it is ready to be deployed to Oracle12c SQL developer.

1. Content of your report
   1. Prepare a report which will detail an E-R diagram (I need softcopy of it) and justification your choice of entities and attributes.
   2. Detail the normalization process of the ER diagram in details.
   3. Describe each additional entries or attributes you have created and why.

Answer:

CrimeCases: This entity contains the crime cases registered for the criminals. It contains the complete record about the case e.g. Hearing Date, Fee, Amount Paid etc.

Criminal: This entity contains the information about criminals e.g. Name, Address, Violation Status, Probation Status etc.

CrimeCaseCriminals: This entity contains the criminals in each crime case. There may be number of criminals in each crime case.

CrimeCharges: This entity contains the type of crime charges along with detail and code given to crime charges.

CrimeClassification: This entity contains the types of crime classifications.

CrimeCaseCharges: A crime case may have much number of charges. Therefore this table is containing the crime case along with its charges.

PoliceOfficer: This entity contains the information about police officers e.g. Badge Number, Name, Phone etc.

CrimeCaseOfficer: A crime case may have much number of officers. Therefore this table is containing the crime case along with its officers.

SentenceType: This entity contains the types of sentences along with detail.

CriminalSentence: This entity contains the sentences given to the criminals. A criminal may have number of sentence.

Appeal: This entity contains the detail of appeals for the criminal cases. A criminal case may have maximum three appeals.

Normalisation Process

Un-normalised data can be converted into first normal form by following process-

• Delete the repeated groups from the data and make new tables.
• Find the primary keys in each table.

First normal form data can be converted into second normal form by following process-

1. Each table is in first normal form.
2. Each table must have primary key and all other attributes depend upon the primary key only.

Third Normal Form

Second normal form data can be converted into third normal form by following process-

1. Each table is in second normal form.
2. Transitive depend should not be seen in any table.

All the tables mentioned in the ER diagram above are satisfying the third normalisation rules.

(P. Brombarg n.d)

The above ER diagram is showing the ER diagram of the third normalised data.

Additional Entities and Attributes

Following entities can be taken in the Crime Tracking Database System

• Prison (PrisonID, Name, Street, City, State, PostCode, Phone) It will contain the detail of prisons.
• Judges (ID, Name, Address, Phone, Rank, Qualification) It will contain the detail of judges.
• CriminalPrison (PrisonID, CriminalNumber,StartDate, EmdDate)

It will show the prison in which the criminal is available. It will store the duration of the criminal in the prison.

• CrimeCaseJudge (CrimeCaseNumber, JudgeID) It will show the crime case and the associated judge of the crime case.

1. There may be number of criminals in a crime case.
2. An appeal will be for a crime case not for a single criminal.
3. There may be number of charges for one crime case.
4. A criminal may be given any number of sentences. But in one case, one sentence will be given to one criminal.
5. There may be number of officers involved in one crime case

References

1. Litwin (n.d.), Fundamentals of Relational Database Design, [Online]. Available:https://www.deeptraining.com/litwin/dbdesign/FundamentalsOfRelationalDatabaseDesign.aspx. . [Accessed: 28-August-2017]

1. Brombarg,(n.d.) SQL SERVER Database Normalization Basics for Developers,  [Online]. Available: https://www.nullskull.com/a/1629/sql-server-database-normalization-basics-for-developers.aspx. [Accessed: 28-August-2017.