Database Design In the ERD assignment, you designed the new database for City Jail. Now you need to create all the tables for the database. First, create all the tables using the information outlined in Section A. Second, make the modifications outlined in Section B. Save all SQL statements used to accomplish these tasks. Section A Coding key for selected columns Table Column Data Description Length Scale Default Value Aliases Alias_iD Numeric Criminal_ID Numeric Alias character 10 Criminals Criminal_ID Numeric Last Variable character 15 First Variable character 10 Street Variable character 30 City Variable character 20 Zip Fixed character 5 Phone Fixed character 10 V_status Fixed character 1 N for (NO) P status Fixed character 1 N for (NO) Crimes Crime_ID Numeric Criminal_ID Numeric Classification Fixed character Status Fixed character 2 Hearing_date Date Appeal cut_date Date Sentences Sentence_ID Numeric Criminal ID Numeric Prob_ID Numeric Violations Numeric Type Fixed character 1 Start_date Date End date Date Prob_officers Prob_ID Numeric Last Variable character 15 First Variable character 10 Street Variable character 30 20 2 5 10 30 1 A for (Active) 2 7 7 7 2 2 2 City Variable character State Fixed character Zip Fixed character Phone Fixed character Email Variable character Status Fixed character Crime charges Charge_ID Numeric Crime_ID Numeric Crime_code Numeric Charge_status Fixed character Fine_amount Decimal Court_fee Decimal Amount_paid Decimal Pay due date Date Crime_officers Crime_ID Numeric Officer_ID Numeric Officers Officer_ID Numeric Last Variable character First Variable character Badge Variable character Precinct Fixed character Phone Fixed character Status Fixed character Appeals Appeal_ID Numeric Crime_ID Numeric Filing_date Date Hearing_date Date Status Fixed character Crime_codes Crime_code Numeric Code description Variable character 15 10 14 4 10 1 A for (Active) 1 P for (Pending) 30 Table Column Possible Values Criminals V_status Y (Yes), N (NO) Criminals P status Y(Yes), N (NO) Crimes Classification F (Felony), M (Misdemeanor), O (Other), U (Undefined) Crimes Status CL (Closed), CA (Can Appeal), IA (In Appeal) Sentences Type ( Jail Period), H (House Arrest), P (Probation) Prob officers Status A (Active). I (Inactive) Crime charges Charge status PD (Pending), GL (Guilty), NG (Not Guilty) Officers Status A (Active), I (Inactive) Appeals Status P (Pending), A (Approved), D (Disapproved) Section B Add a default value of U for the Classification column of the Crimes table. Add a column named Date_Recorded to the Crimes table. This column needs to hold date values and should be set to the current date by default. Add a column to the Prob_officers table to contain the pager number for each officer. The column needs to accommodate a phone number, including area code. Name the column Pager#. . Change the Alias column in the Aliases table to accommodate up to 20 characters. Place your code here
