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.

CREATE TABLE aliases (aliasId INT(6), criminalId INT(6), alias VARCHAR(10)); CREATE TABLE criminals (criminalId INT(6), last VARCHAR(15), first VARCHAR(10), street VARCHAR(30), city VARCHAR(20), state CHAR(2), zip CHAR(5), phone CHAR(10), v_status CHAR(1) DEFAULT 'N', p_status CHAR(1) DEFAULT 'N' ); CREATE TABLE crimes (crime_id INT(9), criminalId INT(6), classification CHAR(1), dateCharged DATE, status CHAR(2), hearingDate DATE, appealCutDate DATE); CREATE TABLE sentences (sentenceId INT(6), criminalId INT(9), type CHAR(1), probId INT(5), startDate DATE, endDate DATE, violations INT(3)); CREATE TABLE probOfficers (probId INT(5), last VARCHAR(15), first VARCHAR(10), street VARCHAR(30), city VARCHAR(20), state CHAR(2), zip CHAR(5), phone CHAR(10), email VARCHAR(30), status CHAR(1) DEFAULT 'A', mgrId INT(5) ); CREATE TABLE officers (officeId INT(8), last VARCHAR(15), first VARCHAR(10), precinct CHAR(4), badge VARCHAR(14), phone CHAR(10), status CHAR(1) DEFAULT 'A' ); CREATE TABLE crimeCodes (crimeCode INT(3), code_description VARCHAR(30)); ALTER TABLE crimes MODIFY classification char(1) DEFAULT 'U'; ALTER TABLE crimes ADD (dateRecorded datetime DEFAULT NOW()); ALTER TABLE probOfficers ADD (pagerId CHAR(10)); ALTER TABLE aliases MODIFY alias VARCHAR(20); ALTER TABLE criminals ADD CONSTRAINT criminals_criminalsId_pk PRIMARY KEY (criminalId); ALTER TABLE criminals ADD CONSTRAINT criminals_vstatus_ck CHECK (v_status IN('Y','N')); ALTER TABLE criminals ADD CONSTRAINT criminals_pstatus_ck CHECK (p_status IN('Y','N')); ALTER TABLE aliases ADD CONSTRAINT aliases_id_pk PRIMARY KEY (aliasId); ALTER TABLE aliases ADD CONSTRAINT appeals_criminals_criminalid_fk FOREIGN KEY (criminalId) REFERENCES criminals(criminalId); ALTER TABLE aliases MODIFY criminalId int(6) NOT NULL; ALTER TABLE crimes ADD CONSTRAINT crimes_id_pk PRIMARY KEY (crime_id); ALTER TABLE crimes ADD CONSTRAINT crimes_class_ck CHECK (classification IN('F','M','O','U')); ALTER TABLE crimes ADD CONSTRAINT crimes_status_ck CHECK (status IN('CL','CA','IA')); ALTER TABLE crimes ADD CONSTRAINT crimes_criminalid_fk FOREIGN KEY (criminalId) REFERENCES criminals(criminalId); ALTER TABLE crimes MODIFY criminalId int(6) NOT NULL; ALTER TABLE probOfficers ADD CONSTRAINT probofficers_id_pk PRIMARY KEY (probId); ALTER TABLE probOfficers ADD CONSTRAINT probofficers_status_ck CHECK (status IN('A','I')); ALTER TABLE sentences ADD CONSTRAINT sentences_id_pk PRIMARY KEY (sentenceId); ALTER TABLE sentences ADD CONSTRAINT sentences_crimeid_fk FOREIGN KEY (criminalId) REFERENCES criminals(criminalId); ALTER TABLE sentences MODIFY criminalId int(6) NOT NULL; ALTER TABLE sentences ADD CONSTRAINT sentences_probid_fk FOREIGN KEY (probId) REFERENCES probOfficers(probId); ALTER TABLE sentences ADD CONSTRAINT sentences_type_ck CHECK (type IN('J','H','P')); ALTER TABLE officers ADD CONSTRAINT officers_id_pk PRIMARY KEY (officeId); ALTER TABLE officers ADD CONSTRAINT officers_status_ck CHECK (status IN('A','I')); ALTER TABLE crimeCodes ADD CONSTRAINT crimecodes_code_pk PRIMARY KEY (crimeCode);

CREATE TABLE appeals (appeal_id INT(5), crime_id INT(9) NOT NULL, filing_date DATE, hearingDate DATE, status CHAR(1) DEFAULT 'P', CONSTRAINT appeals_id_pk PRIMARY KEY (appeal_id), CONSTRAINT appeals_crimeid_fk FOREIGN KEY (crime_id) REFERENCES crimes(crime_id), CONSTRAINT appeals_status_ck CHECK (status IN('P','A','D')) ); CREATE TABLE crime_officers (crime_id INT(9), officeId INT(8), CONSTRAINT crimeofficers_cid_oid_pk PRIMARY KEY (crime_id,officeId), CONSTRAINT crimeofficers_crimeid_fk FOREIGN KEY (crime_id) REFERENCES crimes(crime_id), CONSTRAINT crimeofficers_officerid_fk FOREIGN KEY (officeId) REFERENCES officers(officeId) ); CREATE TABLE crime_charges (charge_id INT(10), crime_id INT(9) NOT NULL, crimeCode INT(3) NOT NULL, charge_status CHAR(2), fine_amount decimal(7,2), court_fee decimal(7,2), amount_paid decimal(7,2), pay_due_date DATE, CONSTRAINT crimecharges_id_pk PRIMARY KEY (charge_id), CONSTRAINT crimecharges_crimeid_fk FOREIGN KEY (crime_id) REFERENCES crimes(crime_id), CONSTRAINT crimecharges_code_fk FOREIGN KEY (crimeCode) REFERENCES crimeCodes(crimeCode), CONSTRAINT crimecharges_status_ck CHECK (charge_status IN('PD','GL','NG')) );

INSERT INTO crimeCodes VALUES (301,'Agg Assault'); INSERT INTO crimeCodes VALUES (302,'Auto Theft'); INSERT INTO crimeCodes VALUES (303,'Burglary-Business'); INSERT INTO crimeCodes VALUES (304,'Criminal Mischief'); INSERT INTO crimeCodes VALUES (305,'Drug Offense'); INSERT INTO crimeCodes VALUES (306,'Bomb Threat'); INSERT INTO probOfficers (probId, last, first, city, status, mgrId) VALUES (100, 'Peek', 'Susan', 'Virginia Beach', 'A', NULL); INSERT INTO probOfficers (probId, last, first, city, status, mgrId) VALUES (102, 'Speckle', 'Jeff', 'Virginia Beach', 'A', 100); INSERT INTO probOfficers (probId, last, first, city, status, mgrId) VALUES (104, 'Boyle', 'Chris', 'Virginia Beach', 'A', 100); INSERT INTO probOfficers (probId, last, first, city, status, mgrId) VALUES (106, 'Taps', 'George', 'Chesapeake', 'A', NULL); INSERT INTO probOfficers (probId, last, first, city, status, mgrId) VALUES (108, 'Ponds', 'Terry', 'Chesapeake', 'A', 106); INSERT INTO probOfficers (probId, last, first, city, status, mgrId) VALUES (110, 'Hawk', 'Fred', 'Chesapeake', 'I', 106); INSERT INTO officers (officeId, last, first, precinct, badge, phone, status) VALUES (111112, 'Shocks', 'Pam', 'OCVW', 'E5546A33', '7574446767', 'A'); INSERT INTO officers (officeId, last, first, precinct, badge, phone, status) VALUES (111113, 'Busey', 'Gerry', 'GHNT', 'E5577D48', '7574446767', 'A'); INSERT INTO officers (officeId, last, first, precinct, badge, phone, status) VALUES (111114, 'Gants', 'Dale', 'SBCH', 'E5536N02', '7574446767', 'A'); INSERT INTO officers (officeId, last, first, precinct, badge, phone, status) VALUES (111115, 'Hart', 'Leigh', 'WAVE', 'E5511J40', '7574446767', 'A'); INSERT INTO officers (officeId, last, first, precinct, badge, phone, status) VALUES (111116, 'Sands', 'Ben', 'OCVW', 'E5588R00', '7574446767', 'I'); COMMIT; INSERT INTO criminals (criminalId, last, first, street, city, state, zip, phone, v_status, p_status) VALUES (1020, 'Phelps','Sam','1105 Tree Lane', 'Virginia Beach', 'VA', '23510', 7576778484, 'Y', 'N'); INSERT INTO crimes (crime_id, criminalId, classification, dateCharged, status, hearingDate, appealCutDate) VALUES (10085, 1020, 'F', '2008-09-03', 'CA', '2008-09-15', '2008-12-15'); INSERT INTO crime_charges(charge_id, crime_id, crimeCode, charge_status, fine_amount, court_fee, amount_paid, pay_due_date) VALUES (5000, 10085, 301, 'GL', 3000, 200, 40, '2008-10-15'); INSERT INTO crime_charges(charge_id, crime_id, crimeCode, charge_status, fine_amount, court_fee, amount_paid, pay_due_date) VALUES (5001, 10085, 305, 'GL', 1000, 100, NULL, '2008-10-15'); INSERT INTO sentences (sentenceId, criminalId, type, probId, startDate, endDate, violations) VALUES (1000, 1020, 'J', NULL, '2008-09-15', '2010-09-15', 0); INSERT INTO aliases (aliasId, criminalId, alias) VALUES (100, 1020, 'Bat'); INSERT INTO crime_officers (crime_id, officeId) VALUES (10085, 111112); INSERT INTO criminals (criminalId, last, first, street, city, state, zip, phone, v_status, p_status) VALUES (1021, 'Sums','Tammy','22 E. Ave', 'Virginia Beach', 'VA', '23510', 7575453390, 'N', 'Y'); INSERT INTO crimes (crime_id, criminalId, classification, dateCharged, status, hearingDate, appealCutDate) VALUES (10086, 1021, 'M', '2008-10-20', 'CL', '2008-12-05', NULL); INSERT INTO crime_charges(charge_id, crime_id, crimeCode, charge_status, fine_amount, court_fee, amount_paid, pay_due_date) VALUES (5002, 10086, 304, 'GL', 200, 100, 25, '2009-02-15'); INSERT INTO sentences (sentenceId, criminalId, type, probId, startDate, endDate, violations) VALUES (1001, 1021, 'P', 102, '2008-12-05', '2009-06-05', 0); INSERT INTO crime_officers (crime_id, officeId) VALUES (10086, 111114);

INSERT INTO criminals (criminalId, last, first, street, city, state, zip, phone, v_status, p_status) VALUES (1022, 'Caulk','Dave', '8112 Chester Lane', 'Chesapeake', 'VA', '23320', 7578403690, 'N', 'Y'); INSERT INTO crimes (crime_id, criminalId, classification, dateCharged, status, hearingDate, appealCutDate) VALUES (10087, 1022, 'M', '2008-10-30', 'IA', '2008-12-05', '2009-03-15'); INSERT INTO crime_charges(charge_id, crime_id, crimeCode, charge_status, fine_amount, court_fee, amount_paid, pay_due_date) VALUES (5003, 10087, 305, 'GL', 100, 50, 150, '2009-03-15'); INSERT INTO sentences (sentenceId, criminalId, type, probId, startDate, endDate, violations) VALUES (1002, 1022, 'P', 108, '2009-03-20', '2009-08-20', 0); INSERT INTO crime_officers (crime_id, officeId) VALUES (10087, 111115); INSERT INTO aliases (aliasId, criminalId, alias) VALUES (101, 1022, 'Cabby'); INSERT INTO appeals (appeal_id, crime_id, filing_date, hearingDate, status) VALUES (7500, 10087, '2008-12-10', '2008-12-20', 'A'); INSERT INTO appeals (appeal_id, crime_id, filing_date, hearingDate, status) VALUES (7501, 10086, '2008-12-15', '2008-12-20', 'A'); INSERT INTO appeals (appeal_id, crime_id, filing_date, hearingDate, status) VALUES (7502, 10085, '2008-09-20', '2008-10-8', 'A'); INSERT INTO criminals (criminalId, last, first, street, city, state, zip, phone, v_status, p_status) VALUES (1023, 'Dabber','Pat', NULL, 'Chesapeake', 'VA', '23320', NULL, 'N', 'N'); INSERT INTO crimes (crime_id, criminalId, classification, dateCharged, status, hearingDate, appealCutDate) VALUES (10088, 1023, 'O', '2008-11-05', 'CA', NULL, NULL); INSERT INTO crime_charges(charge_id, crime_id, crimeCode, charge_status, fine_amount, court_fee, amount_paid, pay_due_date) VALUES (5004, 10088, 306, 'PD', NULL, NULL, NULL, NULL); INSERT INTO crime_officers (crime_id, officeId) VALUES (10088, 111115);

INSERT INTO criminals (criminalId, last, first, street, city, state, zip, phone, v_status, p_status) VALUES (1025, 'Cat','Tommy', NULL, 'Norfolk', 'VA', '26503', NULL, 'N', 'Y'); INSERT INTO crimes (crime_id, criminalId, classification, dateCharged, status, hearingDate, appealCutDate) VALUES (10089, 1025, 'M', '2008-10-22', 'CA', '2008-11-25', '2009-02-15'); INSERT INTO crime_charges(charge_id, crime_id, crimeCode, charge_status, fine_amount, court_fee, amount_paid, pay_due_date) VALUES (5005, 10089, 305, 'GL', 100, 50, NULL, '2009-02-15'); INSERT INTO sentences (sentenceId, criminalId, type, probId, startDate, endDate, violations) VALUES (1004, 1025, 'P', 106, '2008-12-20', '2009-03-20', 0); INSERT INTO crime_officers (crime_id, officeId) VALUES (10089, 111115); INSERT INTO crime_officers (crime_id, officeId) VALUES (10089, 111116);

INSERT INTO criminals (criminalId, last, first, street, city, state, zip, phone, v_status, p_status) VALUES (1026, 'Simon','Tim', NULL, 'Norfolk', 'VA', '26503', NULL, 'N', 'Y'); INSERT INTO crimes (crime_id, criminalId, classification, dateCharged, status, hearingDate, appealCutDate) VALUES (10090, 1026, 'M', '2008-10-22', 'CA', '2008-11-25', '2009-02-15'); INSERT INTO crime_charges(charge_id, crime_id, crimeCode, charge_status, fine_amount, court_fee, amount_paid, pay_due_date) VALUES (5006, 10090, 305, 'GL', 100, 50, NULL, '2009-02-15'); INSERT INTO sentences (sentenceId, criminalId, type, probId, startDate, endDate, violations) VALUES (1005, 1026, 'P', 106, '2008-12-20', '2009-03-20', 0); INSERT INTO crime_officers (crime_id, officeId) VALUES (10090, 111115);

INSERT INTO criminals (criminalId, last, first, street, city, state, zip, phone, v_status, p_status) VALUES (1027, 'Pints','Reed', NULL, 'Norfolk', 'VA', '26505', NULL, 'N', 'Y'); INSERT INTO crimes (crime_id, criminalId, classification, dateCharged, status, hearingDate, appealCutDate) VALUES (10091, 1027, 'M', '2008-10-24', 'CA', '2008-11-28', '2009-02-15'); INSERT INTO crime_charges(charge_id, crime_id, crimeCode, charge_status, fine_amount, court_fee, amount_paid, pay_due_date) VALUES (5007, 10091, 305, 'GL', 100, 50, 20, '2009-02-15'); INSERT INTO sentences (sentenceId, criminalId, type, probId, startDate, endDate, violations) VALUES (1006, 1027, 'P', 106, '2008-12-20', '2009-03-20', 0); INSERT INTO crime_officers (crime_id, officeId) VALUES (10091, 111115);

INSERT INTO criminals (criminalId, last, first, street, city, state, zip, phone, v_status, p_status) VALUES (1028, 'Mansville','Nancy', NULL, 'Norfolk', 'VA', '26505', NULL, 'N', 'Y'); INSERT INTO crimes (crime_id, criminalId, classification, dateCharged, status, hearingDate, appealCutDate) VALUES (10092, 1028, 'M', '2008-10-24', 'CA', '2008-11-28', '2009-02-15'); INSERT INTO crime_charges(charge_id, crime_id, crimeCode, charge_status, fine_amount, court_fee, amount_paid, pay_due_date) VALUES (5008, 10092, 305, 'GL', 100, 50, 25, '2009-02-15'); INSERT INTO sentences (sentenceId, criminalId, type, probId, startDate, endDate, violations) VALUES (1007, 1028, 'P', 106, '2008-12-20', '2009-03-20', 0); INSERT INTO crime_officers (crime_id, officeId) VALUES (10092, 111115);

INSERT INTO criminals (criminalId, last, first, street, city, state, zip, phone, v_status, p_status) VALUES (1024, 'Perry','Cart', NULL, 'Norfolk', 'VA', '26501', NULL, 'N', 'Y'); INSERT INTO crimes (crime_id, criminalId, classification, dateCharged, status, hearingDate, appealCutDate) VALUES (10093, 1024, 'M', '2008-10-22', 'CA', '2008-11-25', '2009-02-15'); INSERT INTO crime_charges(charge_id, crime_id, crimeCode, charge_status, fine_amount, court_fee, amount_paid, pay_due_date) VALUES (5009, 10093, 305, 'GL', 100, 50, NULL, '2009-02-15'); INSERT INTO sentences (sentenceId, criminalId, type, probId, startDate, endDate, violations) VALUES (1003, 1024, 'P', 106, '2008-12-20', '2009-03-20', 1); INSERT INTO crime_officers (crime_id, officeId) VALUES (10093, 111115);

INSERT INTO criminals (criminalId, last, first, street, city, state, zip, phone, v_status, p_status) VALUES (1029, 'Statin','Penny', NULL, 'Norfolk', 'VA', '26505', NULL, 'N', 'Y'); INSERT INTO crimes (crime_id, criminalId, classification, dateCharged, status, hearingDate, appealCutDate) VALUES (10094, 1029, 'M', '2008-10-26', 'CA', '2008-11-26', '2009-02-17'); INSERT INTO crime_charges(charge_id, crime_id, crimeCode, charge_status, fine_amount, court_fee, amount_paid, pay_due_date) VALUES (5010, 10094, 305, 'GL', 50, 50, NULL, '2009-02-17'); INSERT INTO sentences (sentenceId, criminalId, type, probId, startDate, endDate, violations) VALUES (1008, 1029, 'P', 106, '2008-12-20', '2009-02-05', 1); INSERT INTO crime_officers (crime_id, officeId) VALUES (10094, 111115);

INSERT INTO criminals (criminalId, last, first, street, city, state, zip, phone, v_status, p_status) VALUES (1030, 'Panner','Lee', NULL, 'Norfolk', 'VA', '26505', NULL, 'N', 'Y'); INSERT INTO crimes (crime_id, criminalId, classification, dateCharged, status, hearingDate, appealCutDate) VALUES (25344031, 1030, 'M', '2008-10-26', 'CA', '2008-11-26', '2009-02-17'); INSERT INTO crime_charges(charge_id, crime_id, crimeCode, charge_status, fine_amount, court_fee, amount_paid, pay_due_date) VALUES (5011, 25344031, 305, 'GL', 50, 50, NULL, '2009-02-17'); INSERT INTO sentences (sentenceId, criminalId, type, probId, startDate, endDate, violations) VALUES (1009, 1030, 'P', 106, '2008-12-20', '2009-02-05', 1); INSERT INTO crime_officers (crime_id, officeId) VALUES (25344031, 111115);

INSERT INTO crimes (crime_id, criminalId, classification, dateCharged, status, hearingDate, appealCutDate) VALUES (25344060, 1030, 'M', '2008-11-18', 'CL', '2008-11-26', NULL); INSERT INTO crime_charges(charge_id, crime_id, crimeCode, charge_status, fine_amount, court_fee, amount_paid, pay_due_date) VALUES (5012, 25344060, 305, 'GL', 50, 50, 100, '2009-02-17'); INSERT INTO sentences (sentenceId, criminalId, type, probId, startDate, endDate, violations) VALUES (1010, 1030, 'P', 106, '2009-02-06', '2009-07-06', 0); INSERT INTO crime_officers (crime_id, officeId) VALUES (25344060, 111116); COMMIT;

CREATE TABLE prob_contact (prob_cat INT(2), low_amt INT(5), high_amt INT(5), con_freq VARCHAR(20) ); INSERT INTO prob_contact VALUES(10, 1, 80, 'Weekly'); INSERT INTO prob_contact VALUES(20, 81, 160, 'Every 2 weeks'); INSERT INTO prob_contact VALUES(30, 161, 500, 'Monthly'); COMMIT;

CREATE TABLE criminals_dw (criminalId INT(6), last VARCHAR(15), first VARCHAR(10), street VARCHAR(30), city VARCHAR(20), state CHAR(2), zip CHAR(5), phone CHAR(10), v_status CHAR(1), p_status CHAR(1) ); INSERT INTO criminals_dw (criminalId, last, first, street, city, state, zip, phone, v_status, p_status) VALUES (1020, 'Phelps','Sam','1105 Tree Lane', 'Virginia Beach', 'VA', '23510', 7576778484, 'Y', 'N'); INSERT INTO criminals_dw (criminalId, last, first, street, city, state, zip, phone, v_status, p_status) VALUES (1021, 'Sums','Tammy','22 E. Ave', 'Virginia Beach', 'VA', '23510', 7575453390, 'N', 'Y'); INSERT INTO criminals_dw (criminalId, last, first, street, city, state, zip, phone, v_status, p_status) VALUES (1022, 'Caulk','Dave', '8112 Chester Lane', 'Chesapeake', 'VA', '23320', 7578403690, 'N', 'Y'); INSERT INTO criminals_dw (criminalId, last, first, street, city, state, zip, phone, v_status, p_status) VALUES (1023, 'Dabber','Pat', NULL, 'Chesapeake', 'VA', '23320', NULL, 'N', 'N'); INSERT INTO criminals_dw (criminalId, last, first, street, city, state, zip, phone, v_status, p_status) VALUES (1024, 'Perry','Cart', '11 New St.', 'Surry', 'VA', '54501', NULL, 'N', 'Y'); INSERT INTO criminals_dw (criminalId, last, first, street, city, state, zip, phone, v_status, p_status) VALUES (1025, 'Cat','Tommy', NULL, 'Norfolk', 'VA', '26503', 7578889393, 'N', 'Y'); COMMIT;

show solutions for writing the following commands:

1. List all criminals along with the crime charges filed. The report needs to include the criminal

ID, name, crime code, and fine amount. For each request, include one solution using the traditional

method and one using an ANSI JOIN statement.

2. List all criminals along with crime status and appeal status (if applicable). The reports need to include

the criminal ID, name, crime classification, date charged, appeal filing date, and appeal status. Show all

criminals, regardless of whether they have filed an appeal.

3. List all criminals along with crime information. The report needs 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 charged.

For each request, include one solution using the traditional method and one using an ANSI JOIN

statement.

4. Create an alphabetical list of all criminals, including criminal ID, name, violent offender status, parole

status, and any known aliases.

5. The Prob_Contact contains the required frequency of contact with a probation officer,

based on the length of the probation period (the number of days assigned to probation). Review the

data in this table, which indicates ranges for the number of days and applicable contact frequencies.

Create a list containing the name of each criminal who has been assigned a probation period, which is

indicated by the sentence type. The list should contain the criminal name, probation start date,

probation end date and required frequency of contact. Sort the list by criminal name and probation

start date. For each request, include one solution using the traditional method and one using

an ANSI JOIN statement.

6. A column named MgrID is in the Prob_Officers table and contains the ID number of the probation

supervisor for each officer. Produce a list showing each probation officers name and his or her

supervisors name. Sort the list alphabetically by probation. For each request, include one solution using

the traditional method and one using an ANSI JOIN statement.