Création des tables

[pic 1]

Création des tables

SQL> create table seance_conduite(
        2 cin references candidate(cin),
        3 seance integer,
        4 datesc date,
        5 cin_monit references moniteur(cin),
        6 immat  references voiture(immat),
        7 etat var_char(7) check( etat in ('annule','present','abscent')),
        8 primary key (cin, seance , datesc));

SQL> create table seance_code(
        2 cin references candidate(cin),
        3 seance integer,
        4 datesc date,
        5 codemoniteur references moniteur(cin),
        6 etat var_char(7) check( etat in ('annule','present','abscent')),
        7 primary key (cin, seance , datesc));

SQL> create table Examen(
        2 cin char(8),
        3 datex date,
        4 type var_char(8) check( type in (' code,'conduite' )),
        5 resultat char(1) check (  resultat in ('R','A' ) ),
        6 primary key (cin, datesc, type));

SQL> create table suivi_voiture (
        2 dat date,
        3 immat references voiture(immat),
        4 kilometrage float ,
        5 primary key (dat, immat));

SQL> create table affectation_tarif(
        2 cin references candidate(cin),
        3 codetarif references tarif(codetarif),
        4 type var_char(8) check( type in (' code,'conduite' )),
        5 primary key (cin, codetarif, type));

SQL> create table reglement(
        2 numreglement var_char(8) primary key,
        3 montant float,
        4 moder  char(6) check ( moder in ('cheque' , ' espece') ) ,
        5 nature var_char(15) check( etat in ('avance','frais d examen','autre ')),
        6 datesc date,
        7 cincandidat references candidat(cin));

Remplissage des Tables:

        1)Table Examen:

SQL > insert into Examen values ('11235680' , '15-01-2016','code','R');
SQL > insert into Examen values ('11235680' , '11-02-2016','code','A');
SQL >insert into Examen values ('11235680' , '29-07-2016','conduite','R');
SQL > insert into Examen values ('11235680' , '10-11-2016','conduite','A');
SQ L >insert into Examen values ('11235655' , '02-01-2017','code','R');
SQL > insert into Examen values ('11235655' , '30-03-2017','code','A');
SQL > insert into Examen values ('11235655' , '01-09-2017','conduite','R');
SQL > insert into Examen values ('11235655' , '19-11-2017','conduite','A');
SQL> commit;

        2)Tableau Seance_Conduite:

SQL > insert into seance_conduite values ('11235680' ,1, '20-06-2016','8569523','122 tn 1257','present');
SQL > insert into seance_conduite values ('11235680' ,2, '27-06-2016','8569523','122 tn 1257','present');
SQL > insert into seance_conduite values ('11235680' ,3, '04-07-2016','8569523','122tn 1257','present');
SQL > insert into seance_conduite values ('11235680' ,4, '12-07-2016','8569523','122 tn 1257','present');
SQL > insert into seance_conduite values ('11235680' ,5, '05-11-2016','8569523','122 tn 1257','present');
SQL > insert into seance_conduite values ('11235680' ,6, '05-02-2016','8569523','1282tn 1257','present');
SQL > insert into seance_conduite values ('11235655' ,1, '05-08-2017','89657412','127 tn 5555','present');
SQL > insert into seance_conduite values ('11235655' ,2, '12-08-2017','8569523','127 tn 1257','present');
SQL > insert into seance_conduite values ('11235655' ,3, '25-05-2017', ,'89657412','127tn 5555','present');
SQL > insert into seance_conduite values ('11235655' ,4, '29-08-2017', ,'89657412','127tn 5555','present');
SQL > insert into seance_conduite values ('11235655' ,5, '11-11-2017', ,'89657412','127 tn 5555','absent');
SQL> commit;

...