Your company receives the following memo. 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. In developing your design, consider the columns needed to build relationships between the entities. Use only the entities identified in the memo to develop the E-R model. Second, create a list of additional entities or attributes not identified in the memo that might be applicable to a crime-tracking database.
*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; hundreds of codes exist), 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 type of sentence (jail period, house arrest, probation)
Appeals: appeal filing 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)
• 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.
Consider the memo that contains five entities and their relationships.
The entities are CRIMINAL, CRIME, POLICE_OFFICERS, APPEALS and SENTENCING.
Following are the relationships between the entities:
- One criminal may commit one or more crimes. The identifying relationship between CRIMINAL and CRIME is commit.
- Many crime may be investigated by one or more police officers. The identifying relationship between CRIME and POLICE_OFFICERS is investigate.
- One criminal may be assigned to one or more sentences. The identifying relationship between CRIMINAL and SENTENCING is assign.
- One crime has one sentence. The identifying relationship between CRIME and SENTENCING is sentence.
- One sentence may be appealed for one or more times. The identifying relationship between SENTENCING and APPEALS is appeal.
The ER diagram is as follows:
The additional entity that is identified is crime charges. It contains charge id, crime id, crime code, current status, fine amount, paid amount.