Your company receives the following memo. First, based on the memo, create an initial database design
(E-R model) for the City Jail that indicates entities, attributes (columns), primary keys, and relationships.
In developing your design, consider the columns needed to build relationships between the entities. Use
only the entities identified in the memo to develop the E-R model.
Second, create a list of additional entities or attributes not identified in the memo that might be
applicable to a crime-tracking database.
Tips: Keep in mind that the memo is written from an end-user perspective—not by a database
To: Database Consultant From: City Jail Information Director Subject: Establishing a Crime-Tracking
It was a pleasure meeting with you last week. I look forward to working with your company to create a
much-needed crime-tracking system. As you requested, our project group has outlined the crime-
tracking data needs we anticipate. Our goal is to simplify the process of 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.
Criminals: name, address, phone number, violent offender status (yes/no), probation status (yes/ no),
Crimes: classification (felony, misdemeanor, other), date charged, appeal status (closed, can 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, assault; hundreds of codes exist),
amount of fine, court fee, amount paid, payment due date, and charge status (pending, guilty, not
Sentencing: start date, end date, number of violations (such as not reporting to probation officer), and
type of sentence (jail period, house arrest, probation)
Appeals: appeal filing date, appeal hearing date, status (pending, approved, and disapproved) Note:
Each crime case can be appealed up to three times.
Police officers: name, precinct, badge number, phone contact, status (active/inactive) Additional notes:
A single crime can involve multiple crime charges, such as burglary and assault.
Criminals can be assigned multiple sentences. For example, a criminal might be required to
serve a jail sentence followed by a period of probation.
In part one, 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 (See page 98 – page
101). Second, make the modifications outlined in Section B. Save all SQL statements used to accomplish
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.
In Part2 you have designed and created tables for the City Jail database. These tables don’t include any
constraints. Review the information in Chapters 1 and 3 case studies to determine what constraints you
might need for the City Jail database.
First, using the format in the following chart, create a list of constraints needed. Second, create and
execute all the SQL statements needed to add these constraints. Follow these steps to create and alter
1. First, drop the APPEALS, CRIME_OFFICERS, and CRIME_CHARGES tables constructed in Chapter
3. These three tables are to be built last, using a CREATE TABLE command that includes all the
2. Second, use the ALTER TABLE command to add all constraints to the existing tables. Note that
the sequence of constraint addition has an impact. Any tables referenced by FOREIGN KEYs must
already have the PRIMARY KEY created.
3. Third, use the CREATE TABLE command, including all constraints, to build the three tables
dropped in the first step.
Execute the CityJail_5.sql (You can find it under lessons Folder on Blackboard) 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
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
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
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.