Execute the CityJail_5.sql script to rebuild the CRIMINALS and CRIMES tables of the City Jail database. The statements at the beginning of this script drop existing tables in your schema with the same table names. Review the script so that you€™re familiar with the table structure and constraints, and then do the following:

1. Create and execute statements to perform the following DML activities. Save the changes permanently to the database.
a. Create a script to allow a user to add new criminals (providing prompts to the user) to the CRIMINALS table.
b. Add the following criminals, using the script created in the previous step. No value needs to be entered at the prompt if it should be set to the DEFAULT column value. Query the CRIMINALS table to confirm that new rows have been added.

c. Add a column named Mail_flag to the CRIMINALS table. The column should be assigned a datatype of CHAR(1).
d. Set the Mail_flag column to a value of€˜Y€™ for all criminals.
e. Set the Mail_flag column to€˜N€™ for all criminals who don€™t have a street address recorded in the database.
f. Change the phone number for criminal 1016 to 7225659032.
g. Remove criminal 1017 from the database.

2. Execute a DML statement to accomplish each of the following actions. Each statement produces a constraint error. Document the error number and message, and briefly explain the cause of the error. If your DML statement generates a syntax error rather than a constraint violation error, revise your statement to correct any syntax errors. You can review the CityJail_5.sql file to identify table constraints.
a. Add a crime record using the following data: Crime_ID = 100, Criminal_ID = 1010,

Classification= M, Date_charged = July 15, 2009, Status = PD.

b. Add a crime record using the following data: Crime_ID = 130, Criminal_ID = 1016,

Classification = M, Date_charged = July 15, 2009, Status = PD.

c. Add a crime record using the following data: Crime_ID = 130, Criminal_ID = 1016,

Classification = P, Date_charged = July 15, 2009, Status = CL.

Transcribed Image Text:

State Zip First Street City Phone V_status P_status Criminal_ID Last 1015 23320 Fenter Chesapeake VA Jim Saunder Bill 23455 7678217443 N 11 Apple Virginia Beach 1016 VA Rd Norfolk 77 Ship Lane Troy 22093 7677655454 Painter 1017 VA