The Best Assignment Help


HS2021 Database design and use City Jail Case 2018

HS2021 Database Design and Use

Trimester 1 2018

City Jail Case Study- Buy Solution for only $100 AUD


Assignment Description: In this assignment you will use the given case study (Case Study: City Jail) to design a database. You need to analyse the case study to identify database  requirements  and  design  a  complete  E-R  Diagram  in  3NF  form.  You  will create tables using SQL in Oracle 12c based on your E-R Diagram.


Case Study: City Jail

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.

Assignment requirements:

   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.


Report structure and assignment submission:

  1. Content of your report
  2. Prepare a report which will detail an E-R diagram and justification your choice of entities and attributes.
  3. Explain the normalization process of the ER diagram in details. c. Write down all the assumptions you have considered while

preparing the ER diagram.

  1. Include all the CREATE TABLE statements in the Appendix.
  2. Assignment Submission Guideline:

You need to submit one Word document on Blackboard

  1. Assignment Date Due: Friday Week 6 Friday by 5:00 PM

Marking Guide:

Assignment components                                                                                      marks

Quality of ERD                                                                                                      30% Quality of other report tasks (normalisation process, assumptions)


Quality of CREATE TABLE statements



The Best Assignment help is one of the best website for assignment help. For more details you may contact us at


visit at : HS2041 Enterprise Systems

Leave a Comment

Your email address will not be published. Required fields are marked *