CREATE TABLE Detenu(
n_ecrou varchar(10),
prenom varchar(30),
nom varchar(30),
date_naissance Date, -- "1993-12-06"
lieu_naissance varchar(30),
CONSTRAINT Detenu_key PRIMARY KEY(n_ecrou));

CREATE TABLE Affaire(
n_affaire varchar(10),
nom_juridiction varchar(30),
date_faits Date,
CONSTRAINT Affaire_key PRIMARY KEY(n_affaire,nom_juridiction));

CREATE TABLE Detenu_Affaire(
n_ecrou varchar(10),
n_affaire varchar(10),
nom_juridiction varchar(30),
CONSTRAINT Detenu_Affaire_key PRIMARY KEY(n_ecrou,n_affaire,nom_juridiction),
CONSTRAINT Detenu_Affaire_foreign_key FOREIGN KEY(n_ecrou) references Detenu(n_ecrou),
CONSTRAINT Detenu_Affaire_foreign_key2 FOREIGN KEY(n_affaire,nom_juridiction) references Affaire(n_affaire,nom_juridiction));

CREATE TABLE Motif(
n_motif varchar(10),
libelle_motif varchar(50) not null,
CONSTRAINT Motif_key PRIMARY KEY(n_motif),
CONSTRAINT Motif_unique unique(libelle_motif));

CREATE TABLE Incarceration(
n_ecrou varchar(10),
n_affaire varchar(10) not null,
nom_juridiction varchar(30) not null,
date_incarceration Date,
n_motif varchar(10) not null,
CONSTRAINT Incarceration_key PRIMARY KEY(n_ecrou),
CONSTRAINT Incarceration_foreign_key FOREIGN KEY(n_ecrou,n_affaire,nom_juridiction) references Detenu_Affaire(n_ecrou,n_affaire,nom_juridiction),
CONSTRAINT Incarceration_foreign_key2 FOREIGN KEY(n_motif) references Motif(n_motif));

CREATE TABLE Decision(
n_type_decision varchar(1),
n_ecrou varchar(10),
date_decision Date,
CONSTRAINT Decision_key PRIMARY KEY(n_type_decision,n_ecrou,date_decision),
CONSTRAINT Decision_fk FOREIGN KEY(n_ecrou) references Detenu(n_ecrou));

CREATE TABLE Condamnation(
n_type_decision varchar(1),
n_ecrou varchar(10),
date_decision Date,
duree Integer,
CONSTRAINT Condamnation_key PRIMARY KEY(n_type_decision,n_ecrou,date_decision),
CONSTRAINT Condamnation_fk FOREIGN KEY(n_type_decision,n_ecrou,date_decision) references Decision(n_type_decision,n_ecrou,date_decision) on delete cascade);

CREATE TABLE Reduction_peine(
n_type_decision varchar(1),
n_ecrou varchar(10),
date_decision Date,
duree Integer,
CONSTRAINT Reduction_peine_key PRIMARY KEY(n_type_decision,n_ecrou,date_decision),
CONSTRAINT Reduction_peine_fk FOREIGN KEY(n_type_decision,n_ecrou,date_decision) references Decision(n_type_decision,n_ecrou,date_decision) on delete cascade);

CREATE TABLE Liberation_definitive(
n_type_decision varchar(1),
n_ecrou varchar(10),
date_decision Date,
date_liberation Date,
CONSTRAINT Liberation_definitive_key PRIMARY KEY(n_type_decision,n_ecrou,date_decision),
CONSTRAINT Liberation_definitive_fk FOREIGN KEY(n_type_decision,n_ecrou,date_decision) references Decision(n_type_decision,n_ecrou,date_decision) on delete cascade);

INSERT INTO Detenu VALUES('1963','Franck','Barbier','1963-01-11','Montbeliard');
INSERT INTO Detenu VALUES('1964','Sophie','Darnal','1964-07-28','Besancon');

INSERT INTO Affaire VALUES('44','Nantes',DATE('1991-10-01'));

INSERT INTO Motif VALUES('01','vols et delits assimiles');

INSERT INTO Detenu_Affaire VALUES('1963','44','Nantes');
INSERT INTO Incarceration VALUES('1963','44','Nantes','2008-04-16','01');

INSERT INTO Decision VALUES('1','1963','2006-11-22');
INSERT INTO Condamnation VALUES('1','1963','2006-11-22',10);
INSERT INTO Decision VALUES('3','1964','2006-11-23');
INSERT INTO Liberation_definitive VALUES('3','1964','2006-11-23','2007-01-01');
