Database Design and Use Assignment

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.

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 -

Content of your report

a. Prepare a report which will detail an E-R diagram and justification your choice of entities and attributes.

b. Explain the normalization process of the ER diagram in details.

c. Write down all the assumptions you have considered while preparing the ER diagram.

d. Include all the CREATE TABLE statements in the Appendix.