Establishing a crime-tracking database system

Assignment Help Basic Computer Science
Reference no: EM131046392

Based on the following memo, create a database design for the City Jail.

TIP: Keep in mind that the memo is written from an end-user perspective - not by a database developer!


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 to 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, misdemeanor, 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, and many more), amount of fine, 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 many more) and type of sentence (jail period, house arrest, probation)

Appeals:appeal filing date, appeal hearing date, status (pending, approved, and disapproved). Each crime case can be appealed up to three times.

Police officers: name, zone, badge number, phone contact, status (active/inactive)

Additional information:

A single crime can involve multiple criminal 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.

Suppose you are hired to build a database for City Jail.Your first task is to design a database that stores and organizes all information (crimes, criminals, sentencing, appeals, police officers, etc.) about the organization.

A. Generate a list of Business Rules for the database.

B. Write the relational schema, draw its dependency diagram, and identify all dependencies, including all partial and transitive dependencies for 2nd and 3rd Normal Form.

C. Given the above description, draw an ER diagram for the database.State any assumptions you make for your ER-diagram to support your design. Include the following into your design:

  • all the entity sets 
  • all the attributes
  • all the relationship sets using the Crow's Foot Model
  • Make sure that all M:M relationships have been converted using associative tables
  • Identify the entire primary key for each entity.
  • Identifyall the foreign keys if needed.

D. Based on the ER diagram you created, implement the database in Oracle. Insert 5 records into each table. Create the necessary relationships, primary keys, etc. Include the script file and spool file in your submission.

Reference no: EM131046392

Traditional on-premise model

Provide an analysis on how the cloud conceptual model ties in with the traditional on-premise model. Research an SOA approach and the benefits of its adoption. Would an SOA a

Modify the program to use quadratic probing

Implement a linear probe hash table that stores strings. You'll need a hash function that converts a string to an index number; see the section "Hashing Strings" in this cha

Find the area between each curve

Refer to Exercise 13 of Lesson 15-2. Use the marginal cost function to approximate the cost for the company to produce one more widget when the production level is 20 widget

Display all of the integers from first value to second value

Display all of the integers from the first value to the second value, displaying the values vertically (that is, one value per horizontal line), with a header statement indi

Program to keep track of the participants in a triathlon

Create a program to keep track of the participants in a triathlon. Your program will keep track of the times in three events: Running, Swimming, and Biking. Your program wil

Describe rapid adoption of software as a service

The popularity and rapid adoption of Software as a Service (SaaS), Platform as a Service (PaaS), and Infrastructure as a Service (IaaS) by a number of organizations has impa

Explain the concept of a variable and a constant

Explain the concept of a variable and a constant. Explain the differences between variables and data types. Explain the need for data types and the different types available.

What is market segmentation

1. What is market segmentation? 2. Discuss several reasons why marketers continue to have a difficult time understanding, predicting, and explaining consumer behavior. 3. In


Write a Review

Free Assignment Quote

Assured A++ Grade

Get guaranteed satisfaction & time on delivery in every assignment order you paid with us! We ensure premium quality solution document along with free turntin report!

All rights reserved! Copyrights ©2019-2020 ExpertsMind IT Educational Pvt Ltd