Construct the entire city jail database

Assignment Help Database Management System

Reference no: EM131225299

Database Design and Use Group Assignment

Assignment Description -

In this assignment you will use your 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.

Assignment Tasks need to be executed -

-Discuss with in your group to find the best normalized "City Jail" database design to implement in Oracle database.

-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. The CREATE TABLE statements should be created as a group.

-Execute the following queries individually and provide your answers:

Group member One:

Insert 5 different entries in each table. You can create fictitious data to create those entries.
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 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 precent code. Display the community description listed the following chart, based on the second letter of the precent code.

Second letter precinct Code	Description
A	Shady Grove
B	Centre city
C	Bay 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 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 need to include the criminal ID, name, crime classification, date change, appeal filling date, and appeal status. Show all criminals, regardless of whether they have filled an appeal.
List all criminals along with crime information. The reports need to include the criminal ID, name, crime classification, date charged, crime 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.

-Create SQL statements to drop all the tables you have created.

Reference no: EM131225299

Questions Cloud

Success of a winery that a operates a restaurant : Establishes a system to evaluate the ongoing success of a winery that a operates a restaurant and service as the marketplace and company dynamics evolve.

Key strategic decisions made by current team : Identify two key strategic decisions made by your current team, department, or organization. How could those decisions have been enhanced by optimization models? Support your rationale with evidence from readings or external research.

Types of intangible property rights of businesses : Please analyze the types of intangible property rights of businesses protected by intellectual property law and the elements of and protections offered to businesses by patents and copyrights?

Ensure expectations are being met : What are we talking about in practice when it comes to organize resources, control the results, and ensure expectations are being met?

Construct the entire city jail database : HS2021 Database Design and Use Group Assignment. 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. The CREA..

What is the purpose of trade sales promotions : What is the purpose of trade sales promotions? Identify three types of trade sales promotions that Frito-Lay should use to increase sales. Specifically how should these be implemented? 200 words.

Project - resolving a landfill problem : You are the assistant engineer for the Town of Quillicom in Northern Ontario. Your boss is Robert D Delorme, P.Eng, who is the town engineer - develop, they will need to know how much it will cost to drill a dozen boreholes north of Quillicom and an..

Observe shoppers for the purposes of marketing research : Is it ethical to observe shoppers for the purposes of marketing research without their knowledge and permission? Please use examples.

Design and implementation of supply chain : Global companies share something in common. They manage complex value streams through the design and implementation of supply chain management processes. Understanding how value chains and supply chain management are different but interdependent..

Reviews

len1225299

9/29/2016 7:12:17 AM

In this assignment Group members may receive different marks, based on their individual component. Execute all the task mentioned in “Assignment Tasks need to be executed” section and write a report. The report has to include all the SQL queries you have built and executed. Students worked as a team and proper database designing guideline was followed to achieve requirements of the assignment. All constructed and executed queries should run and retrieve/amend information as instructed.

Write a Review

Required(*) Message

User Account

All Pages