the following memo, create a database design for

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

Identify all 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.

Our website has a team of professional writers who can help you write any of your homework. They will write your papers from scratch. We also have a team of editors just to make sure all papers are of HIGH QUALITY & PLAGIARISM FREE. To make an Order you only need to click Ask A Question and we will direct you to our Order Page at WriteDemy. Then fill Our Order Form with all your assignment instructions. Select your deadline and pay for your paper. You will get it few hours before your set deadline.