D._Chance_-_City_Jail_Database_Submission
pdf
keyboard_arrow_up
School
Valencia College *
*We aren’t endorsed by this school
Course
4333C
Subject
Information Systems
Date
Feb 20, 2024
Type
Pages
4
Uploaded by breezymehran
Name: Darren Chance Date: 1/29/2024 City Jail Design Document Code for Aliases Table: USE CityJailDatabase CREATE TABLE aliases
( alias_id NUMERIC
, criminal_id NUMERIC
, alias CHARACTER
(
10
), ) Code for Criminals Table: USE CityJailDatabase CREATE TABLE criminals
( criminal_id NUMERIC
, last_name VARCHAR
(
15
), first_name VARCHAR
(
10
), street VARCHAR
(
30
), city VARCHAR
(
20
), zip CHAR
(
5
), phone CHAR
(
10
), v_status CHAR
(
1
) DEFAULT 'N'
, p_status CHAR
(
1
) DEFAULT 'N'
, ) USE CityJailDatabase ALTER TABLE criminals ADD CONSTRAINT CK_v_status CHECK (v_status IN (
'Y'
,
'N'
)), CONSTRAINT CK_p_status CHECK (p_status IN (
'Y'
,
'N'
)) ; Code for Crimes Table: USE CityJailDatabase CREATE TABLE crimes
( crime_id NUMERIC
, criminal_id NUMERIC
, classification CHAR
(
1
), status CHAR
(
2
), hearing_date DATE
, appeal_cut_date DATE
, CONSTRAINT CK_classification CHECK (classification IN (
'F'
,
'M'
,
'O'
,
'U'
)), CONSTRAINT CK_status CHECK (
status IN (
'CL'
,
'CA'
,
'IA'
)) )
Code for Sentences Table: USE CityJailDatabase CREATE TABLE senteces
( sentence_id NUMERIC
, criminal_id NUMERIC
, prob_id NUMERIC
, violations NUMERIC
, type CHAR
(
1
), start_date DATE
, end_date DATE
, CONSTRAINT CK_type CHECK (
type IN (
'J'
,
'H'
,
'P'
)) ) Code for Prob_Officers Table: USE CityJailDatabase CREATE TABLE prob_officers
( prob_id NUMERIC
, last_name VARCHAR
(
15
), first_name VARCHAR
(
10
), street VARCHAR
(
30
), city VARCHAR
(
20
), state CHAR
(
2
), zip CHAR
(
5
), phone CHAR
(
10
), email VARCHAR
(
30
), status CHAR
(
1
), CONSTRAINT CK_probOfficerStatus CHECK (
status IN (
'A'
,
'I'
)) ) Code for Crime_Charges Table: USE CityJailDatabase CREATE TABLE crime_charges
( charge_id NUMERIC
, crime_id NUMERIC
, crime_code NUMERIC
, charge_status CHAR
(
2
), fine_amount DECIMAL
(
7
,
2
), court_fee DECIMAL
(
7
,
2
), amount_paid DECIMAL
(
7
,
2
), pay_due_date DATE
, CONSTRAINT CK_chargeStatus CHECK (charge_status IN (
'PD'
,
'GL'
,
'NG'
)) )
Code for Crime_Officers Table: USE CityJailDatabase CREATE TABLE crime_officers
( crime_id NUMERIC
, officer_id NUMERIC
, ) Code for Officers Table: CREATE TABLE officers( officer_id NUMERIC (
18
), last_name VARCHAR (
15
), first_name VARCHAR (
10
), badge VARCHAR (
14
), precinct CHAR (
4
), phone CHAR (
10
), status CHAR (
1
) DEFAULT (
'A'
), CONSTRAINT CK_officerStatus CHECK (status IN ('A'
,
'I'
)) ); Code for Appeals Table: USE CityJailDatabase CREATE TABLE appeals
( appeal_id NUMERIC
, crime_id VARCHAR
(
15
), filing_date DATE
, hearing_date DATE
, status CHAR
(
1
) DEFAULT 'P'
, CONSTRAINT CK_appealStatus CHECK (
status IN (
'P'
,
'A'
,
'D'
)) ) Code for Crime_Codes Table: USE CityJailDatabase CREATE TABLE crime_codes
( crime_code NUMERIC
, code_description VARCHAR
(
30
) )
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
Screenshot showing that all tables were created and are in your database. This can be one screenshot just showing the left side of the screen if you “open” the database. Code that adds a default value of U for the Classification column of the Crimes table. USE CityJailDatabase ALTER TABLE crimes ADD CONSTRAINT DF_constraint DEFAULT 'U' FOR classification Code that adds a column named Date_Recorded to the Crimes table and holds date values with the current date as the default. USE CityJailDatabase ALTER TABLE crimes ADD date_recorded DATE DEFAULT GETDATE
() Code that adds a column to the Prob_officers table to contain the pager number for each officer. USE CityJailDatabase ALTER TABLE prob_officers ADD phone# CHAR
(
10
) Code that changes the Alias column in the Aliases table to accommodate up to 20 characters. USE CityJailDatabase ALTER TABLE aliases ALTER COLUMN alias CHAR
(
20
)