DROP TABLE ACTIVITES_PRATIQUEES CASCADE CONSTRAINTS;
DROP TABLE CHARGE CASCADE CONSTRAINTS;
DROP TABLE RESULTATS CASCADE CONSTRAINTS;
DROP TABLE ACTIVITES CASCADE CONSTRAINTS;
DROP TABLE PROFESSEURS CASCADE CONSTRAINTS;
DROP TABLE COURS CASCADE CONSTRAINTS;
DROP TABLE ELEVES CASCADE CONSTRAINTS;

CREATE TABLE ELEVES
(NUM_ELEVE NUMBER(4)
,NOM VARCHAR2(25)
,PRENOM VARCHAR2(25)
,DATE_NAISSANCE DATE
,POIDS NUMBER
,ANNEE NUMBER
,SEXE CHAR(1)
,CONSTRAINT PK_ELEVES PRIMARY KEY(NUM_ELEVE)
,CONSTRAINT NN_ELEVE_NOM CHECK(NOM IS NOT NULL)
,CONSTRAINT NN_ELEVE_PRENOM CHECK(PRENOM IS NOT NULL)
);

CREATE TABLE COURS
(NUM_COURS NUMBER(2)
,NOM VARCHAR2(20)
,NBHEURES NUMBER(2)
,ANNEE NUMBER(1)
,CONSTRAINT PK_COURS PRIMARY KEY(NUM_COURS)
,CONSTRAINT NN_COURS_NOM CHECK(NOM IS NOT NULL)
);

CREATE TABLE PROFESSEURS
(NUM_PROF NUMBER(4)
,NOM VARCHAR2(25)
,SPECIALITE VARCHAR2(20)
,DATE_ENTREE DATE
,DER_PROM DATE
,SALAIRE_BASE NUMBER
,SALAIRE_ACTUEL NUMBER
,CONSTRAINT PK_PROFESSEURS PRIMARY KEY(NUM_PROF)
,CONSTRAINT NN_PROFESSEURS_NOM CHECK(NOM IS NOT NULL)
);

CREATE TABLE ACTIVITES
(NIVEAU NUMBER(1)
,NOM VARCHAR2(20)
,EQUIPE VARCHAR2(32)
,CONSTRAINT PK_ACTIVITES PRIMARY KEY(NIVEAU, NOM)
);

CREATE TABLE RESULTATS
(NUM_ELEVE NUMBER(4)
,NUM_COURS NUMBER(4)
,POINTS NUMBER
,CONSTRAINT PK_RESULTATS PRIMARY KEY(NUM_ELEVE, NUM_COURS)
,CONSTRAINT FK_ELEVE FOREIGN KEY (NUM_ELEVE) REFERENCES ELEVES(NUM_ELEVE)
,CONSTRAINT FK_COURS FOREIGN KEY (NUM_COURS) REFERENCES COURS(NUM_COURS)
);

CREATE TABLE CHARGE
(NUM_PROF NUMBER(4) NOT NULL
,NUM_COURS NUMBER(4) NOT NULL
,CONSTRAINT PK_CHARGE  PRIMARY KEY(NUM_COURS, NUM_PROF)
,CONSTRAINT FK_PROFESSEUR FOREIGN KEY (PROFESSEUR_NUM) REFERENCES PROFESSEURS(NUM_PROF)
,CONSTRAINT FK_COURS FOREIGN KEY (NUM_COURS) REFERENCES COURS(NUM_COURS)
);

CREATE TABLE ACTIVITES_PRATIQUEES
(NUM_ELEVE NUMBER(4)
,NIVEAU NUMBER(1)
,NOM VARCHAR2(20)
,CONSTRAINT PK_ACTIVITES_PRATIQUEES PRIMARY KEY(NUM_ELEVE, NIVEAU, NOM)
,CONSTRAINT FK_ACTIVITE FOREIGN KEY (NIVEAU_NOM, NOM_NOM) REFERENCES ACTIVITES(NIVEAU, NOM)
,CONSTRAINT FK_ELEVE FOREIGN KEY (NUM_ELEVE) REFERENCES ELEVES(NUM_ELEVE)
);

INSERT INTO ELEVES (NUM_ELEVE, NOM, PRENOM, DATE_NAISSANCE, POIDS, ANNEE, SEXE) VALUES
(1, 'Brisefer', 'Benoit', '10-12-1978', 35, 1, 'M'),
(2, 'Génial', 'Olivier', '10-04-1978', 42, 1, 'M'),
(3, 'Jourdan', 'Gil', '28-06-1974', 72, 2, 'F'),
(4, 'Spring', 'Jerry', '16-02-1974', 78, 2, 'M'),
(5, 'Tsuno', 'Yoko', '29-10-1977', 45, 1, 'F'),
(6, 'Lebut', 'Marc', '29-04-1974', 75,2, 'M'),
(7, 'Lagaffe', 'Gaston', '08-04-1975', 61,1, 'M'),
(8, 'Dubois', 'Robin', '20-04-1976', 60, 2, 'M'),
(9, 'Walthéry', 'Natacha', '07-09-1977', 59,1, 'F'),
(10, 'Danny', 'Buck', '15-02-1973', 82, 2, 'M');

INSERT INTO COURS (NUM_COURS, NOM, NBHEURES, ANNEE) VALUES
(1, 'Réseau', 15, 1),
(2, 'Sgbd', 30, 1),
(3, 'Programmation', 15, 1),
(4, 'Sgbd', 30, 2),
(5, 'Analyse', 60, 2);

INSERT INTO PROFESSEURS (NUM_PROF, NOM,  SPECIALITE, DATE_ENTREE, DER_PROM, SALAIRE_BASE, SALAIRE_ACTUEL) VALUES
(1, 'Bottle', 'poésie', '01-10-1970', '01-10-1988', 2000000, 2600000),
(2, 'Bolenov', 'réseau', '15-11-1968', '01-10-1998', 1900000, 2468000),
(3, 'Tonilaclasse', 'poo', '01-10-1979', '01-01-1989', 1900000, 2360000),
(4, 'Pastecnov', 'sql', '01-10-1975', '', 2500000, 2500000),
(5, 'Selector', 'sql', '15-10-1982', '01-10-1988', 1900000, 1900000),
(6, 'Vilplusplus', 'poo', '25-04-1990', '05-06-1994', 1900000, 2200000),
(7, 'Francesca', '', '01-10-1975', '11-01-1998', 2000000, 3200000),
(8, 'Pucette', 'sql', '06-12-1988', '29-02-1996', 2000000, 2500000);

INSERT INTO CHARGE (NUM_PROF, NUM_COURS) VALUES
(1,1),
(1,4),
(2,1),
(3,2),
(3,4),
(3,5),
(4,2),
(7,4),
(8,1),
(8,2),
(8,3),
(8,4),
(8,5);

INSERT INTO RESULTATS (NUM_ELEVE, NUM_COURS, POINTS) VALUES
(1, 1, 15),
(1, 2, 10.5),
(1, 4, 8),
(1, 5, 20),
(2, 1, 13.5),
(2, 2, 12),
(2, 4, 11),
(2, 5, 1.5),
(3, 1, 14),
(3, 2, 15),
(3, 4, 16),
(3, 5, 20),
(4, 1, 16.5),
(4, 2, 14),
(4, 4, 11),
(4, 5, 8),
(5, 1, 5),
(5, 2, 6.5),
(5, 4, 13),
(5, 5, 13),
(6, 1, 15),
(6, 2, 3.5),
(6, 4, 16),
(6, 5, 5),
(7, 1, 2.5),
(7, 2, 18),
(7, 4, 11),
(7, 5, 10),
(8, 1, 16),
(8, 2, 0),
(8, 4, 6),
(8, 5, 11.5),
(9, 1, 20),
(9, 2, 20),
(9, 4, 14),
(9, 5, 9.5),
(10, 1, 3),
(10, 2, 12.5),
(10, 4, 0),
(10, 5, 16);

INSERT INTO ACTIVITES (NIVEAU, NOM, EQUIPE) VALUES
(1,'Mini foot','Amc Indus'),
(1,'Surf','Les planchistes ...'),
(2,'Tennis','Ace Club'),
(3,'Tennis','Ace Club'),
(1,'Volley ball', 'Avs80'),
(2,'Mini foot', 'Les as du ballon'),
(2,'Volley ball', 'smash');

INSERT INTO ACTIVITES_PRATIQUEES (NUM_ELEVE, NIVEAU, NOM) VALUES
(1, 1, 'Mini foot'),
(1, 1, 'Surf'),
(1, 1, 'Volley ball'),
(1, 2, 'Tennis'),
(2, 1, 'Mini foot'),
(2, 2, 'Tennis'),
(3, 2, 'Mini foot'),
(3, 2, 'Tennis'),
(3, 2, 'Volley ball'),
(4, 1, 'Surf'),
(4, 2, 'Mini foot'),
(5, 1, 'Mini foot'),
(5, 1, 'Surf'),
(5, 1, 'Volley ball'),
(8, 1, 'Mini foot'),
(8, 1, 'Volley ball'),
(8, 2, 'Volley ball'),
(9, 1, 'Mini foot'),
(9, 2, 'Volley ball'),
(10, 1, 'Mini foot'),
(10, 2, 'Tennis'),
(10, 2, 'Volley ball');

COMMIT;