Annexe
SQL>create table annexe(
id_dossier integer,
num_ordre integer,
typepiece number(1,0) default 0,
element blob,
constraint fk1_pq_annexe foreign key(id_dossier) references
pqadmin.dossier,
constraint fk2_pq_annexe foreign key(num_ordre) references
pqadmin.acte,
constraint pk_pq_annexe primary
key(id_dossier,num_ordre));
i. Fait
SQL>create table fait(
num_fait integer primary key,
datefait date,recit varchar2(256),
num_ordre integer,article number(4,0),
id_personne integer,numidentif varchar2(20),
codecom varchar2(10),
constraint fk1_pq_fait foreign key(num_ordre) references
pqadmin.acte,
constraint fk2_pq_fait foreign key(article) references
pqadmin.infraction,
constraint fk3_pq_fait foreign key(id_personne,numidentif)
references pqadmin.personne,
constraint fk4_pq_fait foreign key(codecom) references
pqadmin.commune) ;
3) Création des vues
j. Personne_physique
SQL>create view personne_physique as
select
a.id_personne,a.numidentif,a.nom,a.postnom,a.prenom,a.datenaiss,a.aptitude,a.genre,a.villenaiss,a.profession,a.nationalite,b.nomcom
from personne a,commune b
where a.codecom=b.codecom;
k. Personne_morale
SQL> create view personne_morale as
select
a.id_personne,a.numidentif,a.nom,a.formejurid,a.datecreation,b.nomcom
from personne a,commune b
where a.codecom=b.codecom;
l. Personnel_judiciaire
SQL>create view personnel_judiciaire as
select
a.matricule,a.nomperso,a.postperso,a.service,a.grade,a.fonction,b.nomparquet
from personnel a,parquet b
where a.codeparquet=b.codeparquet;
m. Registre des entrées
(plaintes, dénonciations et courriers) : RENT
SQL>create view rent as
select
a.num_ordre,a.typeacte,a.num_acte,a.daterecept,a.objet,b.nom,c.element,b.codecom
from acte a,personne b,annexe c
where a.id_personne=b.id_personne and a.num_ordre=c.num_ordre;
n. Registre des PV et rapports :
RPV
SQL>create view rpv as
select
a.num_ordre,a.typeacte,a.num_acte,a.daterecept,a.objet,b.nomperso,c.element,b.codeparquet
from acte a,personnel b,annexe c
where a.matricule=b.matricule and a.num_ordre=c.num_ordre;
o. Registre du Ministère Public
global : RMPG
SQL> create view rmpg as
select
a.id_dossier,a.dateinscript,a.datearrest,b.nom,b.postnom,b.profession,b.nationalite,b.codecom,a.article,c.sanction,a.mdprevent,a.ordonnance,a.observation,a.codeparquet
from dossier a,personne b,infraction c,acte d
where a.id_dossier=d.id_dossier and b.id_personne=d.id_personne
and a.article=c.article;
p. Registre des amendes
transactionnelles : RATG
SQL> create view ratg as
select * from rmpg
where sanction=4
with check option;
q. Registre de détention
préventive : RDPG
SQL> create view rdpg as
select * from rmpg
where sanction not in '0,4' and mdprevent=1
with check option;
r. Registre du ministère public
de l'enfance délinquante : RMPEDG
SQL>create view rmpedg as
select
a.id_dossier,a.dateinscript,a.datearrest,b.nom,b.postnom,b.profession,b.nationalite,a.article,c.sanction,a.mdprevent,a.ordonnance,a.observation,a.codeparquet
from dossier a,personne b,infraction c,acte d,fait e
where a.id_dossier=d.id_dossier and b.id_personne=d.id_personne
and a.article=c.article and e.datefait-b.datenaiss < 18
with check option;
| 
