HS2021-Database Design T1 Assessment-Holmes Institute Australia

Assignment Description:

In this assignment, you will use the normalized “City Jail” database you have created in your assignment one. You will form a group (2-3 students) and implement your designed City Jail database using Oracle SQL developer 12c. You will also perform different types of SQL queries on it to sharpen your SQL skills to implement and manage the database. Please submit your assignment using the attached template.

Assignment Tasks:

Group component:

Discuss within your group to find the best normalized “City Jail” database design to implement in the Oracle database. Provide an Entity Relationship Diagram. You can re-use the E-R Diagram from assignment 1 and improve it
based on your lecturer’s feedback. The E-R Diagram should be created as a group.

Write down all the SQL statements require to construct the entire City Jail database. Execute all SQL queries in Oracle SQL developer 12c to implement the “City Jail” database in Oracle 12c. You can re-use the CREATE TABLE
statements from assignment 1 and improve them based on your lecturer’s feedback. The CREATE TABLE statements should be created as a group.

Are You Searching Answer of this Question? Request Australian Writers to Write a plagiarism Free Copy for You.


Insert 5 different entries in each table. You can create fictitious data to create those entries.

Create SQL statements to drop all the tables you have created as a group.

Group member One:

Add a column named Mail_flag to the CRIMINALS table. The column should be assigned a data type CHAR (1).Set the Mail_flag column to ‘N’ for all criminals who don’t have a street address recorded in the database.

Change the phone number for a criminal to 7225659032.

Remove a specific criminal from the database. Make sure you remove all the associated information relevant to that criminal.

Group member Two:

List the following information for all crimes that have a period greater than 14 days between the date charged and the hearing date: crime ID, classification, date changed, hearing date, and the number of days between the date charged and the hearing date.

Produce a list showing each active police officer and his or her community assignment, indicated by the second letter of the percent code. Display the community description listed the following chart, based on the second letter of the percent code.

Get Solution of this Assessment. Hire Experts to solve this assignment for you Before Deadline.

The second letter of precinct CodeDescription
AShady Grove
BCentre City
CBay Landing

Display the criminal name and probation start date for all criminals who have a probation period greater than two months. Also, display the date that’s two months from the beginning of the probation period, which will serve as a review
date.

Group member Three

List all criminals along with crime status and appeal status (if applicable). The report needs to include the criminal ID, name, crime classification, date change, appeal filling date, and appeal status. Show all criminals, regardless of whether they have filed an appeal.

List all criminals along with crime information. The reports need to include the criminal ID, name, crime classification, date charged, criminal code, and fine amount. Include only crimes classified as “Other”. Sort the list by criminal ID and date changed.

Create an alphabetic list of all criminals, including criminal ID, name, violent offender, status, parole status, and any known aliases.

Stuck in Completing this Assignment and feeling stressed ? Take our Private Writing Services.

Get Custom Help With Experts

If you are struggling to prepare a Database Design assignment, give it a try at our Holmes Institute assignment help service. We follow the policy of giving premium quality database assignment solutions at a cheap rate. We provide computer science assignment help for Institute students of Australia to get an idea of our quality work.

Private and Confidential

Yours all information is private and confidential; it is not shared with any other party. So, no one will know that you have taken help for your Academic paper from us.

This essay is written by:

Tim Winton Verified writer

Finished papers: 930

Proficient in:

English, History, Business and Entrepreneurship, Nursing, Psychology, Management

You can get writing help to write an essay on these topics
100% plagiarism-free

Ask Your Homework Today!

We have over 1000 academic writers ready and waiting to help you achieve academic success

Sample Assignment Download

BSBRSK501 Risk Management Assessment Answer
BSBRSK501: Risk Management Like many other students, you might also have an interest in pursuing an educational course in the management field. If you are a management student learning the…
Business
Nursing Student Placement Reflection Essay
Reflective practice in nursing is considered to be the as best way to analyze your own performances. In case you are provided with the task to write reflective essay performance.…
Nursing

UP TO 50 % DISCOUNT

Instant Paper Writing Services by Native Australian Writers

Plagiarism Free Solutions
100% Original Work
24*7 Online Assistance
Native PhD Experts
Hire a Writer Now

Get help with Homework Assignments, Papers, and Projects