DROP TABLE suit;
DROP TABLE etudiant;
DROP TABLE enseigne;
DROP TABLE prof;
DROP TABLE cours;
DROP TABLE promo;



CREATE TABLE prof
  (numProf      NUMBER(3)	NOT NULL,
   nom      	VARCHAR(20)     NOT NULL,
   prenom       VARCHAR(20)     NULL,
   CONSTRAINT pkey_prof PRIMARY KEY (numprof)
  );

CREATE TABLE promo
  (numPromo	NUMBER(2)	NOT NULL,
   nom		VARCHAR(20),
   CONSTRAINT pkey_promo PRIMARY KEY (numPromo)
  );

CREATE TABLE cours
  (numMod	CHAR(8)		NOT NULL,
   intitule	VARCHAR(99),
   numPromo	NUMBER(2)	NOT NULL,
   CONSTRAINT pkey_cours PRIMARY KEY (numMod),
   CONSTRAINT fkey_cours FOREIGN KEY (numPromo) REFERENCES promo(numPromo)
  );

CREATE TABLE enseigne
  (numProf      NUMBER(3)	NOT NULL,
   numMod	CHAR(8)		NOT NULL,
   nbHC		NUMBER(5,2)	NULL,
   nbHTD	NUMBER(5,2)	NULL,
   nbHTP	NUMBER(5,2)	NULL,
   CONSTRAINT pkey_enseigne PRIMARY KEY (numProf,numMod),
   CONSTRAINT fkey_enseigne1 FOREIGN KEY (numProf) REFERENCES prof(numProf),
   CONSTRAINT fkey_enseigne2 FOREIGN KEY (numMod) REFERENCES cours(numMod),
   CONSTRAINT check_enseigne1 CHECK (nbHC >= 0),
   CONSTRAINT check_enseigne2 CHECK (nbHTD >= 0),
   CONSTRAINT check_enseigne3 CHECK (nbHTP >= 0)
  );

CREATE TABLE etudiant
  (numEtud	NUMBER(10)	NOT NULL,
   nom		VARCHAR(20)	NOT NULL,
   prenom	VARCHAR(20)     NULL,
   numPromo	NUMBER(2)	NOT NULL,
   CONSTRAINT pkey_etudiant PRIMARY KEY (numEtud),
   CONSTRAINT fkey_etudiant FOREIGN KEY (numPromo) REFERENCES promo(numPromo)
  );

CREATE TABLE suit
  (numEtud	NUMBER(10)	NOT NULL,
   numMod	CHAR(8)		NOT NULL,
   note		NUMBER(5,2)	NULL,
   CONSTRAINT pkey_suit PRIMARY KEY (numEtud,numMod),
   CONSTRAINT fkey_suit1 FOREIGN KEY (numEtud) REFERENCES etudiant(numEtud),
   CONSTRAINT fkey_suit2 FOREIGN KEY (numMod) REFERENCES cours(numMod),
   CONSTRAINT check_suit1 CHECK ((note >= 0) and (note <= 20))
  );



INSERT INTO prof VALUES(1,'Bascou','Jean-Jacques');
INSERT INTO prof VALUES(2,'Abenia','Angel');
INSERT INTO prof VALUES(3,'Pollaud-Duliand','Anne-Marie');
INSERT INTO prof VALUES(4,'Gallon','Laurent');
INSERT INTO prof VALUES(5,'Munier','Manuel');
INSERT INTO prof VALUES(6,'Lespine','Yannick');
INSERT INTO prof VALUES(7,'Metatidj','Sophie');
INSERT INTO prof VALUES(8,'Baillot','Christophe');
INSERT INTO prof VALUES(9,'Siami','Shidoush');
INSERT INTO prof VALUES(10,'Arnould','Philippe');
INSERT INTO prof VALUES(11,'Kone',null);

INSERT INTO promo VALUES(1,'RT1');
INSERT INTO promo VALUES(2,'RT2');
INSERT INTO promo VALUES(3,'LP ISVD');
INSERT INTO promo VALUES(4,'LP ASUR');

INSERT INTO cours VALUES('I1','Utilisation des systemes d''exploitation des ordinateurs',1);
INSERT INTO cours VALUES('I2','Representation de l''information et architecture des machines informatiques',1);
INSERT INTO cours VALUES('I3','Algorithmique et programmation',1);
INSERT INTO cours VALUES('I4','Base de donnees',1);
INSERT INTO cours VALUES('I5','Programmation orientee objet',2);
INSERT INTO cours VALUES('I6','Programmation evenementielle et reseau',2);
INSERT INTO cours VALUES('R1','Concepts generaux des reseaux',1);
INSERT INTO cours VALUES('R2','Reseaux locaux',1);
INSERT INTO cours VALUES('R3','Administration des systemes d''exploitation reseaux',1);
INSERT INTO cours VALUES('R4','Technologie IP',1);
INSERT INTO cours VALUES('TR1','Modulations numeriques et notions de compression',2);
INSERT INTO cours VALUES('TR2','Technologies de l''Internet',2);
INSERT INTO cours VALUES('TR3','Reseaux etendus et reseaux d''operateurs',2);
INSERT INTO cours VALUES('IC1','Systeme d''exploitation et programmation systeme',2);
INSERT INTO cours VALUES('IC3','Applications client-serveur et Web',2);
INSERT INTO cours VALUES('IC4','Application informatique encadree',2);
INSERT INTO cours VALUES('IC5','Bases de donnees avancees',2);
INSERT INTO cours VALUES('ASUR1',null,4);
INSERT INTO cours VALUES('ASUR2',null,4);
INSERT INTO cours VALUES('ASUR3',null,4);
INSERT INTO cours VALUES('ASUR4','Administration systeme et reseau',4);

INSERT INTO enseigne VALUES(10,'I1',7.5,15,18);
INSERT INTO enseigne VALUES( 9,'I1',0,0,54);
INSERT INTO enseigne VALUES(11,'I3',7.5,12,45);
INSERT INTO enseigne VALUES( 5,'I4',6,18,36);
INSERT INTO enseigne VALUES( 5,'I5',6,12,45);
INSERT INTO enseigne VALUES( 1,'R4',13.5,24,54);
INSERT INTO enseigne VALUES( 4,'R4',0,0,27);
INSERT INTO enseigne VALUES( 1,'TR2',13.5,27,27);

INSERT INTO etudiant VALUES(101,'AUBARET','Maxime',1);
INSERT INTO etudiant VALUES(102,'FOURNADET','Sylvain',1);
INSERT INTO etudiant VALUES(103,'BELINE','Sarah',1);
INSERT INTO etudiant VALUES(104,'LABALESTRA','Anthony',1);
INSERT INTO etudiant VALUES(105,'BECHET','Arnaud',1);
INSERT INTO etudiant VALUES(106,'NAMOUS','Chaymaa',1);
INSERT INTO etudiant VALUES(107,'MEYER','Adrien',1);
INSERT INTO etudiant VALUES(108,'LABORDE TUYA','Frederic',1);
INSERT INTO etudiant VALUES(109,'DUFORT','Enzo',1);
INSERT INTO etudiant VALUES(110,'GOMES','Guillaume',1);
INSERT INTO etudiant VALUES(111,'DALONGEVILLE','Eric',1);
INSERT INTO etudiant VALUES(112,'TESSIER','Clement',1);
INSERT INTO etudiant VALUES(113,'LOYATHO','Hugues',1);
INSERT INTO etudiant VALUES(114,'JARRY','Sylvain',1);
INSERT INTO etudiant VALUES(115,'FERRET','Benoît',1);
INSERT INTO etudiant VALUES(116,'LAFARGUE','Philippe',1);
INSERT INTO etudiant VALUES(117,'MACARY','Emilie',1);
INSERT INTO etudiant VALUES(118,'SAGE','Clothilde',1);
INSERT INTO etudiant VALUES(119,'CHVETS','Pavel',1);
INSERT INTO etudiant VALUES(120,'LIU','Siqi',1);
INSERT INTO etudiant VALUES(121,'NDAYE','Penda',1);
INSERT INTO etudiant VALUES(122,'SOTTIZON','Laurent',1);
INSERT INTO etudiant VALUES(123,'VICTORIN','Sebastien',1);
INSERT INTO etudiant VALUES(124,'PESSAH','Kevin',1);
INSERT INTO etudiant VALUES(125,'BERNADOTTE',null,1);
INSERT INTO etudiant VALUES(126,'LAROUQUETTE','Sylvain',1);
INSERT INTO etudiant VALUES(127,'FEJRI','Wissem',1);
INSERT INTO etudiant VALUES(128,'BLANQUET','Nicolas',1);
INSERT INTO etudiant VALUES(129,'LIBAROS','Steve',1);
INSERT INTO etudiant VALUES(130,'TIBI','Maxime',1);
INSERT INTO etudiant VALUES(131,'NDOKI','Noe',1);
INSERT INTO etudiant VALUES(132,'TOURE','Ndieye Marieme',1);
INSERT INTO etudiant VALUES(133,'COLY','Yaya',1);
INSERT INTO etudiant VALUES(134,'ESPAGNE','Geoffrey',1);
INSERT INTO etudiant VALUES(135,'LACOSTE','Panpi',1);
INSERT INTO etudiant VALUES(136,'BEUSTE','Hugo',1);
INSERT INTO etudiant VALUES(137,'VIGNERON','Quentin',1);
INSERT INTO etudiant VALUES(138,'PICOURLAT','Clement',1);
INSERT INTO etudiant VALUES(139,'MANZANO','Paul',1);
INSERT INTO etudiant VALUES(140,'CRABOS','Vincent',1);
INSERT INTO etudiant VALUES(141,'CHABAN','Xavier',1);

INSERT INTO etudiant VALUES(201,'ADAM','Frederic',2);
INSERT INTO etudiant VALUES(202,'AITA','Adrien',2);
INSERT INTO etudiant VALUES(203,'ARRUEBO','Jeremy',2);
INSERT INTO etudiant VALUES(204,'AUGE','Pierre',2);
INSERT INTO etudiant VALUES(206,'BENEDEYT','Vincent',2);
INSERT INTO etudiant VALUES(207,'BERRY','Adrien',2);
INSERT INTO etudiant VALUES(208,'BUSANA','Mickael',2);
INSERT INTO etudiant VALUES(209,'CABALLERO','Yoan',2);
INSERT INTO etudiant VALUES(211,'DE BERROA','Sebastien',2);
INSERT INTO etudiant VALUES(212,'DE KERSAUSON','Guillaume',2);
INSERT INTO etudiant VALUES(213,'DE MORAIS','Mathieu',2);
INSERT INTO etudiant VALUES(214,'DERACHE','Antoine',2);
INSERT INTO etudiant VALUES(215,'ETCHEBES','Bertrand',2);
INSERT INTO etudiant VALUES(216,'GOFFART','Axel',2);
INSERT INTO etudiant VALUES(217,'GWETH MBOCK','Michel',2);
INSERT INTO etudiant VALUES(218,'HERVE','Yann',2);
INSERT INTO etudiant VALUES(219,'LACAULE','Thierry',2);
INSERT INTO etudiant VALUES(220,'LAMMIN','Vincent',2);
INSERT INTO etudiant VALUES(222,'LUCQUIN','Maxime',2);
INSERT INTO etudiant VALUES(224,'MIGNON','Nicolas',2);
INSERT INTO etudiant VALUES(225,'MONDON','Mehdi',2);
INSERT INTO etudiant VALUES(226,'MORISSE','Thomas',2);
INSERT INTO etudiant VALUES(227,'NDIAYE','Papa Alioune',2);
INSERT INTO etudiant VALUES(229,'NICOLETTA','Pierre',2);
INSERT INTO etudiant VALUES(230,'ORTAS','Florian',2);
INSERT INTO etudiant VALUES(231,'PATEY','Thomas',2);
INSERT INTO etudiant VALUES(232,'SEBASTIAN','Allan',2);
INSERT INTO etudiant VALUES(233,'SERVAT','Mathieu',2);
INSERT INTO etudiant VALUES(234,'SURVILLE','Gerald',2);
INSERT INTO etudiant VALUES(236,'VALADIER','Antoine',2);
INSERT INTO etudiant VALUES(237,'VERGEZ','Nicolas',2);
INSERT INTO etudiant VALUES(238,'ZHANG','Guichao',2);
INSERT INTO etudiant VALUES(239,'ZHANG','Guoquiang',2);

INSERT INTO suit VALUES(101,'I4',10);
INSERT INTO suit VALUES(102,'I4',null);
INSERT INTO suit VALUES(103,'I4',null);
INSERT INTO suit VALUES(104,'I4',null);
INSERT INTO suit VALUES(105,'I4',null);
INSERT INTO suit VALUES(106,'I4',null);
INSERT INTO suit VALUES(107,'I4',null);
INSERT INTO suit VALUES(108,'I4',null);
INSERT INTO suit VALUES(109,'I4',null);
INSERT INTO suit VALUES(110,'I4',null);
INSERT INTO suit VALUES(111,'I4',null);
INSERT INTO suit VALUES(112,'I4',null);
INSERT INTO suit VALUES(113,'I4',null);
INSERT INTO suit VALUES(114,'I4',null);
INSERT INTO suit VALUES(115,'I4',null);
INSERT INTO suit VALUES(116,'I4',null);
INSERT INTO suit VALUES(117,'I4',null);
INSERT INTO suit VALUES(118,'I4',null);
INSERT INTO suit VALUES(119,'I4',null);
INSERT INTO suit VALUES(120,'I4',null);
INSERT INTO suit VALUES(121,'I4',null);
INSERT INTO suit VALUES(122,'I4',null);
INSERT INTO suit VALUES(123,'I4',null);
INSERT INTO suit VALUES(124,'I4',null);
INSERT INTO suit VALUES(125,'I4',null);
INSERT INTO suit VALUES(126,'I4',null);
INSERT INTO suit VALUES(127,'I4',null);
INSERT INTO suit VALUES(128,'I4',null);
INSERT INTO suit VALUES(129,'I4',null);
INSERT INTO suit VALUES(130,'I4',null);
INSERT INTO suit VALUES(131,'I4',null);
INSERT INTO suit VALUES(132,'I4',null);
INSERT INTO suit VALUES(133,'I4',null);
INSERT INTO suit VALUES(134,'I4',null);
INSERT INTO suit VALUES(135,'I4',null);
INSERT INTO suit VALUES(136,'I4',null);
INSERT INTO suit VALUES(137,'I4',null);
INSERT INTO suit VALUES(138,'I4',null);
INSERT INTO suit VALUES(139,'I4',null);
INSERT INTO suit VALUES(140,'I4',null);
INSERT INTO suit VALUES(141,'I4',null);

INSERT INTO suit VALUES(201,'I5',20);
INSERT INTO suit VALUES(202,'I5',15.5);
INSERT INTO suit VALUES(203,'I5',2);
INSERT INTO suit VALUES(204,'I5',4.5);
INSERT INTO suit VALUES(206,'I5',2.5);
INSERT INTO suit VALUES(207,'I5',17);
INSERT INTO suit VALUES(208,'I5',2);
INSERT INTO suit VALUES(209,'I5',3);
INSERT INTO suit VALUES(211,'I5',0);
INSERT INTO suit VALUES(212,'I5',3.5);
INSERT INTO suit VALUES(213,'I5',19);
INSERT INTO suit VALUES(214,'I5',5.5);
INSERT INTO suit VALUES(215,'I5',1.5);
INSERT INTO suit VALUES(216,'I5',3);
INSERT INTO suit VALUES(217,'I5',4);
INSERT INTO suit VALUES(218,'I5',15);
INSERT INTO suit VALUES(219,'I5',3.5);
INSERT INTO suit VALUES(220,'I5',11);
INSERT INTO suit VALUES(222,'I5',2.5);
INSERT INTO suit VALUES(224,'I5',4.5);
INSERT INTO suit VALUES(225,'I5',3.5);
INSERT INTO suit VALUES(226,'I5',9.5);
INSERT INTO suit VALUES(227,'I5',2);
INSERT INTO suit VALUES(229,'I5',1);
INSERT INTO suit VALUES(230,'I5',3.5);
INSERT INTO suit VALUES(231,'I5',5);
INSERT INTO suit VALUES(232,'I5',19.5);
INSERT INTO suit VALUES(233,'I5',0.5);
INSERT INTO suit VALUES(234,'I5',3);
INSERT INTO suit VALUES(236,'I5',0.5);
INSERT INTO suit VALUES(237,'I5',1.5);
INSERT INTO suit VALUES(238,'I5',1);
INSERT INTO suit VALUES(239,'I5',3.5);

INSERT INTO suit VALUES(201,'TR2',10);
INSERT INTO suit VALUES(202,'TR2',10);
INSERT INTO suit VALUES(203,'TR2',10);
INSERT INTO suit VALUES(204,'TR2',10);
INSERT INTO suit VALUES(206,'TR2',10);
INSERT INTO suit VALUES(207,'TR2',10);
INSERT INTO suit VALUES(208,'TR2',10);
INSERT INTO suit VALUES(209,'TR2',null);
INSERT INTO suit VALUES(211,'TR2',null);
INSERT INTO suit VALUES(212,'TR2',null);
INSERT INTO suit VALUES(213,'TR2',10);
INSERT INTO suit VALUES(214,'TR2',null);
INSERT INTO suit VALUES(215,'TR2',10);
INSERT INTO suit VALUES(216,'TR2',10);
INSERT INTO suit VALUES(217,'TR2',10);
INSERT INTO suit VALUES(218,'TR2',10);
INSERT INTO suit VALUES(219,'TR2',null);
INSERT INTO suit VALUES(220,'TR2',10);
INSERT INTO suit VALUES(222,'TR2',null);
INSERT INTO suit VALUES(224,'TR2',null);
INSERT INTO suit VALUES(225,'TR2',10);
INSERT INTO suit VALUES(226,'TR2',10);
INSERT INTO suit VALUES(227,'TR2',10);
INSERT INTO suit VALUES(229,'TR2',10);
INSERT INTO suit VALUES(230,'TR2',10);
INSERT INTO suit VALUES(231,'TR2',10);
INSERT INTO suit VALUES(232,'TR2',10);
INSERT INTO suit VALUES(233,'TR2',10);
INSERT INTO suit VALUES(234,'TR2',10);
INSERT INTO suit VALUES(236,'TR2',10);
INSERT INTO suit VALUES(237,'TR2',10);
INSERT INTO suit VALUES(238,'TR2',10);
INSERT INTO suit VALUES(239,'TR2',10);

INSERT INTO suit VALUES(101,'I1',10);
INSERT INTO suit VALUES(101,'I2',10);
INSERT INTO suit VALUES(101,'I3',10);
INSERT INTO suit VALUES(101,'R1',10);
INSERT INTO suit VALUES(101,'R2',10);
INSERT INTO suit VALUES(101,'R3',10);
INSERT INTO suit VALUES(101,'R4',10);
