
drop table note;
drop table etudiant;
drop table matiere;

-- creation de la table Etudiant

create table Etudiant (
  netud int primary key,
  nometud varchar(20) NOT NULL,
  groupetud char(1) check (groupetud in ('A','B','C')),
  redouble char check (redouble in ('F','f','T', 't'))
);

-- creation de la table Matiere

create table Matiere (
  nmat int primary key,
  nommat varchar(7) check (nommat in ('ARCHI','ANGLAIS','MATH','ALGO','BADO','SYSTEME')) NOT NULL,
  coeffmat float check (coeffmat between 1 and 5)
);

-- creation de la table Note

create table Note (
  netud int references Etudiant(netud),
  nmat int references Matiere(nmat),
  moy float check (moy between 0 and 20) NOT NULL,
  primary key(netud,nmat)
);


-- Insertions


-- initialisation de la table Etudiant

insert into Etudiant values (1,'SIMON','A','F');
insert into Etudiant values (2,'COLETTA','B','T');
insert into Etudiant values (3,'BOUKACHOUR','C','F');
insert into Etudiant values (4,'BOUDEBOUS','A','T');
insert into Etudiant values (5,'DELARUELLE','B','F');
insert into Etudiant values (6,'SIMON','A','T');


-- initialisation de la table Matiere

insert into Matiere values (1,'MATH',5);
insert into Matiere values (2,'ALGO',5);
insert into Matiere values (3,'BADO',2);
insert into Matiere values (4,'ARCHI',2);
insert into Matiere values (5,'ANGLAIS',3);
insert into Matiere values (6,'SYSTEME',NULL);

-- initialisation  de la table Note

insert into Note values (1,1,10.5);
insert into Note values (1,2,16);
insert into Note values (1,3,8);
insert into Note values (1,4,9);
insert into Note values (1,5,14);
insert into Note values (1,6,12);
insert into Note values (2,1,14);
insert into Note values (2,2,11);
insert into Note values (2,3,8);
insert into Note values (2,6,16);
insert into Note values (3,1,14);
insert into Note values (3,2,12);
insert into Note values (3,3,14);
insert into Note values (3,5,14);
insert into Note values (3,6,12);
insert into Note values (4,2,14);
insert into Note values (4,3,12);
insert into Note values (4,4,14);
insert into Note values (5,5,7);
insert into Note values (5,6,9);






