CGS 2545 Database Assignment

Instructions for The MySQLworkbench 6.3. ce assignment:

In MySQL, create a new schema titled <yourlastname>module3.

Save the attached CityJail.sql file under "C:\" drive of your PC, connect to mySQL DBMS, open a SQL script file, navigate to CityJail.sql file and execute it to create tables and populate with the supplied data.

If there are any errors in the script fix them. It is your job to find the errors and correct them. This is important. You will need correct data for future exercises in this module.

Take a screen shot of your new schema in the Navigator window with Tables expanded and upload the image in the Submit Database Design Exercise 1 area.

Part 2 answering the questions

Run the CityJail.sql file provided found in Blackboard under Announcement to ensure that all necessary tables and constraints are available for this case study. This script builds the City Jail database.

The following list reflects common data requests from city managers. Write the SQL statements to satisfy the requests. If the query can be accomplished by using different operators, supply alternative solutions so that the performance-tuning group can test them and identify the more efficient statements.

Test the statements and show execution results.

1. List all criminal aliases beginning with the letter B.
2. List all crimes that occurred (were charged) during the month October 2008. List the crime ID, criminalID, date charged, and classification.
3. List all crimes with a status of CA (can appeal) or IA (in appeal). List the crime ID, criminal ID, charged, and status.
4. List all crimes classified as a felony. List the crime ID, criminal ID, date charged, and classification.
5. List all crimes with a hearing date more than 14 day after the date charged. List the crime ID, criminal ID, date charged, and hearing date.
6. List all criminals with the zip code 23510. List the criminal ID, last name, and zip code. Sort the list by criminal ID.
7. List all crimes that don’t have a hearing date scheduled. List the crime ID, criminal ID, date charged,and hearing date.
8. List all sentences with a probation officer assigned. List the sentence ID, criminal ID, and probation officer ID. Sort the list by probation officer ID and then criminal ID.
9. List all crimes that are classified as misdemeanors and are currently in appeal. List the crime ID, criminal ID, classification, and status.
10. List all crime charges with a balance owed. List the charge ID, crime ID, fine amount, court fee, amount paid, and amount owed.
11. List all police officers who are assigned to the precinct OCVW or GHNT and have a status of active. List the officer ID, last name, precinct, and status. Sort the list by precinct and then by officer last name.