create table CRIMINAL_CASE(
CRIMINAL_CASE_NUMBER varchar(10),
JURISDICTION_NAME varchar(30),
DATE_OF_CRIMINAL_CASE Date,
constraint CRIMINAL_CASE_key primary key(CRIMINAL_CASE_NUMBER,JURISDICTION_NAME));

create table MOTIVE(
MOTIVE_NUMBER varchar(10),
MOTIVE_LABEL varchar(50) not null,
constraint MOTIVE_key primary key(MOTIVE_NUMBER),
constraint MOTIVE_unique unique(MOTIVE_LABEL));

create table PRISONER(
PRISON_FILE_NUMBER varchar(10),
GIVEN_NAME varchar(30),
SURNAME varchar(30),
DATE_OF_BIRTH Date,
PLACE_OF_BIRTH varchar(30),
DATE_OF_INCARCERATION Date,
CRIMINAL_CASE_NUMBER varchar(10) not null,
JURISDICTION_NAME varchar(30) not null,
MOTIVE_NUMBER varchar(10) not null,
constraint PRISONER_key primary key(PRISON_FILE_NUMBER),
constraint INCARCERATION_MAIN foreign key(CRIMINAL_CASE_NUMBER,JURISDICTION_NAME) references CRIMINAL_CASE(CRIMINAL_CASE_NUMBER,JURISDICTION_NAME),
constraint INCARCERATION_MOTIVE foreign key(MOTIVE_NUMBER) references MOTIVE(MOTIVE_NUMBER));

create table PRISONER_CRIMINAL_CASE(
PRISON_FILE_NUMBER varchar(10),
CRIMINAL_CASE_NUMBER varchar(10),
JURISDICTION_NAME varchar(30),
constraint PRISONER_CRIMINAL_CASE_key primary key(PRISON_FILE_NUMBER,CRIMINAL_CASE_NUMBER,JURISDICTION_NAME),
constraint PARTICIPANT foreign key(PRISON_FILE_NUMBER) references PRISONER(PRISON_FILE_NUMBER),
constraint OFFENSE foreign key(CRIMINAL_CASE_NUMBER,JURISDICTION_NAME) references CRIMINAL_CASE(CRIMINAL_CASE_NUMBER,JURISDICTION_NAME));

create table JUDICIAL_DECISION(
DECISION_TYPE_NUMBER varchar(1),
PRISON_FILE_NUMBER varchar(10),
DATE_OF_DECISION Date,
constraint JUDICIAL_DECISION_key primary key(DECISION_TYPE_NUMBER,PRISON_FILE_NUMBER,DATE_OF_DECISION),
constraint JUDICIAL_DECISION_fk foreign key(PRISON_FILE_NUMBER) references PRISONER(PRISON_FILE_NUMBER));

create table CONVICTION(
DECISION_TYPE_NUMBER varchar(1),
PRISON_FILE_NUMBER varchar(10),
DATE_OF_DECISION Date,
DURATION Integer,
constraint CONVICTION_key primary key(DECISION_TYPE_NUMBER,PRISON_FILE_NUMBER,DATE_OF_DECISION),
constraint CONVICTION_fk foreign key(DECISION_TYPE_NUMBER,PRISON_FILE_NUMBER,DATE_OF_DECISION) references JUDICIAL_DECISION(DECISION_TYPE_NUMBER,PRISON_FILE_NUMBER,DATE_OF_DECISION) on delete cascade);

create table SHORTENED_SENTENCE(
DECISION_TYPE_NUMBER varchar(1),
PRISON_FILE_NUMBER varchar(10),
DATE_OF_DECISION Date,
DURATION Integer,
constraint SHORTENED_SENTENCE_key primary key(DECISION_TYPE_NUMBER,PRISON_FILE_NUMBER,DATE_OF_DECISION),
constraint SHORTENED_SENTENCE_fk foreign key(DECISION_TYPE_NUMBER,PRISON_FILE_NUMBER,DATE_OF_DECISION) references JUDICIAL_DECISION(DECISION_TYPE_NUMBER,PRISON_FILE_NUMBER,DATE_OF_DECISION) on delete cascade);

create table FINAL_DISCHARGE(
DECISION_TYPE_NUMBER varchar(1),
PRISON_FILE_NUMBER varchar(10),
DATE_OF_DECISION Date,
DATE_OF_FINAL_DISCHARGE Date,
constraint FINAL_DISCHARGE_key primary key(DECISION_TYPE_NUMBER,PRISON_FILE_NUMBER,DATE_OF_DECISION),
constraint FINAL_DISCHARGE_fk foreign key(DECISION_TYPE_NUMBER,PRISON_FILE_NUMBER,DATE_OF_DECISION) references JUDICIAL_DECISION(DECISION_TYPE_NUMBER,PRISON_FILE_NUMBER,DATE_OF_DECISION) on delete cascade);

INSERT INTO CRIMINAL_CASE VALUES('L69000','Lyon',DATE('1993-11-06'));
INSERT INTO CRIMINAL_CASE VALUES('M13000','Marseille',DATE('1996-03-15'));

INSERT INTO MOTIVE VALUES('01','theft and various misdemeanors');

INSERT INTO PRISONER VALUES('2007','Nicolas','Sarkozy',DATE('1955-02-28'),'Paris',DATE('2008-01-01'),'M13000','Marseille','01');
INSERT INTO PRISONER VALUES('2012','Francois','Hollande',DATE('1954-08-12'),'Rouen',DATE('2013-01-01'),'L69000','Lyon','01');

INSERT INTO PRISONER_CRIMINAL_CASE VALUES('2007','M13000','Marseille');
INSERT INTO PRISONER_CRIMINAL_CASE VALUES('2012','L69000','Lyon');

INSERT INTO JUDICIAL_DECISION VALUES('1','2007',DATE('2015-01-01'));
INSERT INTO CONVICTION VALUES('1','2007',DATE('2015-01-01'),10);
INSERT INTO JUDICIAL_DECISION VALUES('3','2012',DATE('2015-01-01'));
INSERT INTO FINAL_DISCHARGE VALUES('3','2012',DATE('2015-01-01'),DATE('2015-10-10'));
