City Jail Database Submission
docx
keyboard_arrow_up
School
Valencia College *
*We aren’t endorsed by this school
Course
4333C
Subject
Information Systems
Date
Feb 20, 2024
Type
docx
Pages
5
Uploaded by breezymehran
Name: Mehran Sarwar
Date: 2/4/24 (Revised: 2/8/24)
City Jail Design Document Code for Aliases Table:
Use City_Jail
Create Table Aliases (
Alias_id Numeric, Criminal_id Numeric,
Alias Character(10),
)
Code for Criminals Table:
Use City_Jail
Create Table Criminals(
Criminal_id Numeric Lastname Varchar(15), Firstname 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 City_Jail
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 City_Jail
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 City_Jail
Create Table Sentences(
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 City_Jail
Create Table Prob_officers(
Prob_id Numeric,
Lastname Varchar(15),
Firstname 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 City_Jail
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 City_Jail
Create Table Crime_officers(
Crime_id Numeric,
Officer_id Numeric,
)
Code for Officers Table:
Use City_Jail
Create Table Officers( Officer_ID Numeric, Lastname Varchar (15), Firstname 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 City_Jail
Create Table Appeals(
Appeal_ID Numeric,
Crime_ID Numeric,
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 City_Jail
Create Table Crime_codes(
Crime_code Numeric,
Code_description VARCHAR(30) )
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.
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
Code that adds a default value of U for the Classification column of the Crimes table.
Use City_Jail 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 City_Jail 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 City_Jail
Alter Table Prob_officers Add Pager# Char(10)
Code that changes the Alias column in the Aliases table to accommodate up to 20 characters.
Use City_Jail
Alter Table Aliases
Alter Column Alias Char(20)