Database Case Study (City Jail )

Task -1

We have discussed the initial data-based design for the jail and we have written different entries in the column first of the database design. 

The added column of Criminals can be seen in the figure. 

We have also added and removed the details of the criminal from the database. 

Using Australian Health Leadership Framework For the Analysis of the Scenario of the public health leader

Initial Database Design

List of additional entities needed

To map the database problem into the 3rd normal form, the following additional entities are required.

1. Aliases
2. Prob_officers
3. Crime_charges
4. Crime_officers
5. Officers
6. Crime_codes

Task – 2 and 3

Here in the final database, we have added the information of all the crimes that have tenure of more than 14 days. The list of police officers has been added to the final design. 

The design contains the list of all the criminals’ details such as – criminal ID, name, crime classification, date change, appeal filling date, and appeal status.

ASS071-1 Unit title and Code: Law Society and Controversy

The SQL drop can be seen in the figure: 

Final Database Design (ERD), 3rd NF

Assumptions, considerations and design amendments

To map the case study into a 3rd normal form database ERD, the following actions were undertaken.

1. Name decomposed into first and last.
2. Address decomposed into street, city, state and zip.
3. Added [crime officers] junction table to resolve the many to many relations between crimes and officers.
4. Split the types of officers into distinct entities.
5. Create a separate aliases table, to facilitate multiple aliases per criminal.
6. Separate entity for crime code description.
7. Separate entity for [prob officers] to link to sentences.
8. Separate entity for criminal charges, linked to crimes.

Managing Human Resources