Chapter 1 and the database descriptions from the Chapters 1 and 3 case study sections to determine all the constraints required. You need to give students details of the requirements. For example, the provided solution file assumes the following:

• Constraint naming: All constraints except NOT NULL constraints should be assigned a name.
• Foreign key columns: No NULL values should be allowed in FK columns except the Prob_ID column of the Sentences table because sentences arent always probation. If the sentence is not probation, the Prob_ID column is NULL.

To give students practice in both the CREATE TABLE and ALTER TABLE commands, the solution file also assumes the student is instructed to do the following:

• 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 necessary constraints.
• 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.
• Third, use the CREATE TABLE command to build the three tables dropped in the first step.

Constraint list:

Table	Column	Constraint type	Condition
Aliases	Alias_ID	PRIMARY KEY
	Criminal_ID	FOREIGN KEY	Ref Criminals table
	Criminal_ID	NOT NULL
Criminals	Criminal_ID	PRIMARY KEY
	V_status	CHECK	IN (Y,N)
	P_status	CHECK	IN (Y,N)
Crimes	Crime_ID	PRIMARY KEY
	Criminal_ID	FOREIGN KEY	Ref Criminals table
	Criminal_ID	NOT NULL
	Classification	CHECK	IN (F,M,O,U)
	Status	CHECK	IN (CL,CA,IA)
Sentences	Sentence_ID	PRIMARY KEY
	Criminal_ID	FOREIGN KEY	Ref Criminals table
	Criminal_ID	NOT NULL
	Prob_ID	FOREIGN KEY	Ref Prob_Officers table
	Prob_ID	NOT NULL
	Type	CHECK	IN (J,H,P)
Prob_officers	Prob_ID	PRIMARY KEY
	Status	CHECK	IN (A,I)
Crime_charges	Charge_ID	PRIMARY KEY
	Crime_ID	FOREIGN KEY	Ref Crimes Table
	Crime_ID	NOT NULL
	Crime_code	FOREIGN KEY	Ref Crime_codes table
	Crime_code	NOT NULL
	Charge_status	CHECK	IN(PD,GL,NG)
Crime_officers	Crime_ID and Officer_ID	PRIMARY KEY
	Crime_ID	FOREIGN KEY	Ref Crimes table
	Officer_ID	FOREIGN KEY	Ref Officers table
Officers	Officer_ID	PRIMARY KEY
	Status	CHECK	IN (A,I)
Appeals	Appeal_ID	PRIMARY KEY
	Crime_ID	FOREIGN KEY	Ref Crimes table
	Crime_ID	NOT NULL
	Status	CHECK	IN (P,A,D)
Crime_codes	Crime_code	PRIMARY KEY

please note that what is needed from all that is to :

1- add constraint in tables that we alrady created (i have the tables ready, i can send it if needed , but not posted here)

2- add values to it

3- start assignment 2