12:15 AQ Case Study: City Jail Your company receives the following memo. First, based on the meme, create anitial database design (E-R model for the Cty Jail that indicates entities, attributes took primary keys and relatieslips In developing your design consider the columns needed to be between the citities. Use only the entities identified in the memoto develop the E-R model Second create a list of additional entities et tributes set identified in the meme that might be applicable to a crime-tracking database. Keep in mind that the memo is written from an end user perspective net by the developer! MEMO To Database Consultant From: City Jail Information Director Subject: Establishing a Crime-Tracking Database System It was a pleasure meeting with you last week. I look forward to working with your company create a much-needed crime-tracking system. As you out our project proposed the crime-tracking data needs we anticipate. Our poal is to simplify the procese tracking criminal activity and provide a more efficient mechanism for data analysis and reporting. Please review the data needs outlined below and contact me with any questions Criminal name, address, phone number, violent offender status yes no probati yesno), and aliases Crimes classification (felony. misdemeanother, date charged appeals doel.com appeal, in appeal), hearing date appeal cutoff date always 60 days after the hearing date arresting officers (can be more than one officer, crime codes (such as burglary forgery hundreds of codes exist, most of fine, confecantuntpaid peyment de date and charge status (pending guilty, not guilty) Sentencing start date, end date. number of violations such as not reporting te probation officer), and type of sentence (jail period, house antest, probation) Apyvaly appeal filing date appeal hearing date as pending approved and proved Note: Each crime case can be appealed up to three times Police officer name, precinct, badge number, phone contact, status activate Ada A single crime can inwohe multiple crime charges such as barglary and out Criminals can be assigned multiple sentences. For example, a criminal might be requered to serve a jail sentence followed by a period of probation In the Chapter Tweye designed the watches for Catyal Nowy he blessing the initiation SA Second mathemat Snea Section A Duis Description Le Column AJO So Com Vrate 1:12 One Com On 10 Post . Phone Table Column Possible Values Criminal V status Yesi. Ne Criminals YN IN Crimes Classification Fielony. Misdemea (Other). Undefined Crimes Closed CACIA in Apel Sentence Type Period. Probation Prob officers Sale Activele Crime charges Charge_sh Po Pending. GLIG NG (Not Guy Status P Pendingi. Aproved Disapproved Section B Add a default value of Ufor the Classification columes of the Crimes table Add a column named Date_Recorded to the Crimes table. This column needs to hold default Add a column to the Prob_office table to contain the paper number for cach officer. including area code. Name the column Pages Change the Alias column in the Alises table to accommodate up to 20 characters. 12:18 III AQ Case Study: City Jail Execute the Citydal 5.sl script to rebuild the CRIMINALS and CRIMES tables of the Coy Sail database. The statements at the beginning of this script drop existing tables in your schems with the same table names The City Jail 5.sql script is not included in the data files idees included with the clation files, so it will be provided by your instructor Review the script so that you see familiar with the table structure and constraints and the do the following Create and execute statements to perform the following DML activities. Save the campes permanently to the database 2. Create a script to allow a user to add new criminals (providing prompts to the were the CRIMINALS table b. Add the following criminals, using the script created in the previous step. Novalac needs to be entered at the promptif it should be set to the DEFAULT male Query the CRIMINALS table to confirm that now ows have been added c. Add a column named Mailflag to the CRIMINALS table. The colume skedd be assigned a datatype of CHARI) d. Set the Mail flag column to a value of Ye for all criminals 4. Set the Mail_flag column to Nene for all criminals who does have a street address recorded in the database Change the phone number for criminal 1016 10 7225659932 g. Remove criminal 1017 from the database 2. Execute a DML statement to accomplish cach of the following actions. Each statement produces a constraint error. Document the cor number and message and briefly explain the cause of the error if your DMI statement generates a tax error rather than constraintsolation error, revise your statement to correct any syntaxe. You can the City Jail_sel file to identify table constraints Add a crime record using the following data: Crime_ID-100 Criminal_ID-1010 Classification -M. Dute_charged July 15, 2009. Status - PD. b. Add a crime record using the following data Crime ID = 130. Criminal D=1016 Classefication -M. Dale charged - July 15, 2009 S-PD. C. Add a crime record using the following data: CrimeID=130. Criminal D=1015 Classification - Date_charged - July 15, 2009. Status CL 12:15 AQ Case Study: City Jail Your company receives the following memo. First, based on the meme, create anitial database design (E-R model for the Cty Jail that indicates entities, attributes took primary keys and relatieslips In developing your design consider the columns needed to be between the citities. Use only the entities identified in the memoto develop the E-R model Second create a list of additional entities et tributes set identified in the meme that might be applicable to a crime-tracking database. Keep in mind that the memo is written from an end user perspective net by the developer! MEMO To Database Consultant From: City Jail Information Director Subject: Establishing a Crime-Tracking Database System It was a pleasure meeting with you last week. I look forward to working with your company create a much-needed crime-tracking system. As you out our project proposed the crime-tracking data needs we anticipate. Our poal is to simplify the procese tracking criminal activity and provide a more efficient mechanism for data analysis and reporting. Please review the data needs outlined below and contact me with any questions Criminal name, address, phone number, violent offender status yes no probati yesno), and aliases Crimes classification (felony. misdemeanother, date charged appeals doel.com appeal, in appeal), hearing date appeal cutoff date always 60 days after the hearing date arresting officers (can be more than one officer, crime codes (such as burglary forgery hundreds of codes exist, most of fine, confecantuntpaid peyment de date and charge status (pending guilty, not guilty) Sentencing start date, end date. number of violations such as not reporting te probation officer), and type of sentence (jail period, house antest, probation) Apyvaly appeal filing date appeal hearing date as pending approved and proved Note: Each crime case can be appealed up to three times Police officer name, precinct, badge number, phone contact, status activate Ada A single crime can inwohe multiple crime charges such as barglary and out Criminals can be assigned multiple sentences. For example, a criminal might be requered to serve a jail sentence followed by a period of probation In the Chapter Tweye designed the watches for Catyal Nowy he blessing the initiation SA Second mathemat Snea Section A Duis Description Le Column AJO So Com Vrate 1:12 One Com On 10 Post . Phone Table Column Possible Values Criminal V status Yesi. Ne Criminals YN IN Crimes Classification Fielony. Misdemea (Other). Undefined Crimes Closed CACIA in Apel Sentence Type Period. Probation Prob officers Sale Activele Crime charges Charge_sh Po Pending. GLIG NG (Not Guy Status P Pendingi. Aproved Disapproved Section B Add a default value of Ufor the Classification columes of the Crimes table Add a column named Date_Recorded to the Crimes table. This column needs to hold default Add a column to the Prob_office table to contain the paper number for cach officer. including area code. Name the column Pages Change the Alias column in the Alises table to accommodate up to 20 characters. 12:18 III AQ Case Study: City Jail Execute the Citydal 5.sl script to rebuild the CRIMINALS and CRIMES tables of the Coy Sail database. The statements at the beginning of this script drop existing tables in your schems with the same table names The City Jail 5.sql script is not included in the data files idees included with the clation files, so it will be provided by your instructor Review the script so that you see familiar with the table structure and constraints and the do the following Create and execute statements to perform the following DML activities. Save the campes permanently to the database 2. Create a script to allow a user to add new criminals (providing prompts to the were the CRIMINALS table b. Add the following criminals, using the script created in the previous step. Novalac needs to be entered at the promptif it should be set to the DEFAULT male Query the CRIMINALS table to confirm that now ows have been added c. Add a column named Mailflag to the CRIMINALS table. The colume skedd be assigned a datatype of CHARI) d. Set the Mail flag column to a value of Ye for all criminals 4. Set the Mail_flag column to Nene for all criminals who does have a street address recorded in the database Change the phone number for criminal 1016 10 7225659932 g. Remove criminal 1017 from the database 2. Execute a DML statement to accomplish cach of the following actions. Each statement produces a constraint error. Document the cor number and message and briefly explain the cause of the error if your DMI statement generates a tax error rather than constraintsolation error, revise your statement to correct any syntaxe. You can the City Jail_sel file to identify table constraints Add a crime record using the following data: Crime_ID-100 Criminal_ID-1010 Classification -M. Dute_charged July 15, 2009. Status - PD. b. Add a crime record using the following data Crime ID = 130. Criminal D=1016 Classefication -M. Dale charged - July 15, 2009 S-PD. C. Add a crime record using the following data: CrimeID=130. Criminal D=1015 Classification - Date_charged - July 15, 2009. Status CL