
/*Les clés principales des tables sont toutes des entiers*/
/*Pour plus de lisibilité, on utilise aussi des identifiants textuels uniques (pour les utilisateurs par ex)*/
/*les tables exprimant les relation faire_cours, appartenir, etc. pour des raisons de minimalité d'espace de stockage mettent en relation les clés principales numériques*/

CREATE DATABASE IF NOT EXISTS db_lycee CHARACTER SET utf8 COLLATE=utf8_bin;

USE db_lycee;


CREATE TABLE IF NOT EXISTS Discipline (num_Discipline BIGINT AUTO_INCREMENT NOT NULL, intitulé_Discipline CHAR(64), PRIMARY KEY (num_Discipline), UNIQUE INDEX (intitulé_Discipline)) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

INSERT INTO Discipline (intitulé_Discipline) VALUES
                ('mathématiques'),
                ('informatique'),
                ('français'),
                ('histoire'),
                ('géographie'),
                ('physique'),
                ('svt');

/*la clé pour les personnes sera un identifiant de type char de la forme nom.prenom[int]*/
CREATE TABLE IF NOT EXISTS Elève (num_Eleve BIGINT AUTO_INCREMENT NOT NULL, identifiant CHAR(64), nom_Eleve CHAR(128), prenom_Eleve CHAR(128), PRIMARY KEY (num_Eleve), UNIQUE INDEX(identifiant)) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

INSERT INTO Elève (identifiant, nom_Eleve, prenom_Eleve) VALUES
                ('esposito.miguel', 'Esposito', 'Miguel'),
                ('robert.luna', 'Robert', 'Luna'),
                ('robert.jp', 'Robert', 'Jean-Philippe'),
                ('mcdo.ronald', 'MacDo', 'Ronald'),
                ('mxyz.ben', 'Mxyztplk', 'Benjamin'),
                ('panisse.marceline', 'Panisse', 'Marceline'),
                ('barbarian.konan', 'Barbarian', 'Konan'),
                ('traoré.leila', 'Traoré', 'Leila'),
                ('haggard.daisy', 'Haggard', 'Daisy'),
                ('haggard.hilda', 'Haggard', 'Hilda'),
                ('trollbourg.frida', 'Trollbourg', 'Frida'),
                ('aldric.alfur', 'Aldric', 'Alfur'),
                ('tartine.quentin', 'Tartine', 'Quentin'),
                ('turon.uma', 'Turon', 'Uma'),
                ('michel.michel', 'Michel', 'Michel'),
                ('oumiou.mi', 'Oumiou', 'Mi'),
                ('noname.guy', 'Noname', 'Guy'),
                ('macalister.kevin', 'Macalister', 'Kevin'),
                ('kyle.selina', 'Kyle', 'Selina');

CREATE TABLE IF NOT EXISTS Enseignant (num_Enseignant BIGINT AUTO_INCREMENT NOT NULL, identifiant CHAR(64), nom_Enseignant CHAR(128), prénom_Enseignant CHAR(128), PRIMARY KEY (num_Enseignant), UNIQUE INDEX(identifiant)) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

INSERT INTO Enseignant (identifiant, nom_Enseignant, prénom_Enseignant) VALUES
                ('segura.johan', 'Segura', 'Johan'),
                ('bannière.bruce', 'Bannière', 'Bruce'),
                ('parqueur.pierre', 'Parqueur', 'Pierre'),
                ('francky.victor', 'Francky', 'Victor'),
                ('etrange.stéphane', 'Etrange', 'Stéphane'),
                ('neige.catherine', 'Neige', 'Catherine'),
                ('pym.nadia', 'Pym', 'Nadia');

				
/*Classe déterminée de manière unique par l'intitulé et la date (ex : TS1, 2019)*/
CREATE TABLE IF NOT EXISTS Classe (num_Classe BIGINT AUTO_INCREMENT, niveau_Classe CHAR(64), intitulé_Classe CHAR(64), PRIMARY KEY (num_Classe), UNIQUE INDEX (intitulé_Classe)) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

INSERT INTO Classe (num_Classe, niveau_Classe, intitulé_Classe) VALUES
                (1, 'seconde', 'seconde1'),				
                (2, 'seconde', 'seconde2'),
                
				(3, 'Première', '1S1'),
                (4, 'Première', '1S2'),
                (5, 'Première', '1L1'),
				
				(6, 'Terminale', 'TS1'),
                (7, 'Terminale', 'TS2'),
                (8, 'Terminale', 'TL1');
				
CREATE TABLE IF NOT EXISTS Faire_cours (num_Enseignant BIGINT NOT NULL, num_Classe BIGINT NOT NULL, num_Discipline  BIGINT NOT NULL, année1 INT(4)) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
CREATE INDEX FK_Faire_cours_num_Enseignant ON Faire_cours( num_enseignant );
CREATE INDEX FK_Faire_cours_num_Classe ON Faire_cours( num_Classe );
CREATE INDEX FK_Faire_cours_num_Discipline ON Faire_cours( num_Discipline );
CREATE INDEX FK_Faire_cours_année1 ON Faire_cours( année1 );

INSERT INTO Faire_cours (num_Enseignant, num_Classe, num_Discipline, année1) VALUES
                (1, 1, 1, 2019),
                (1, 3, 2, 2019), 
                (1, 4, 2, 2018), 
				(6, 2, 6, 2019),
				(6, 3, 6, 2019),
				(6, 6, 6, 2018),
				(3, 3, 7, 2018),
				(3, 6, 7, 2017),
				(7,6,7, 2019),
				(7,4,7, 2018),
				(7,7,7, 2018),
				(5,1,5, 2019),
				(5,2,5, 2019),
				(5,5,5, 2018),
				(5,8,5, 2017),
				(6,3,7, 2019),
				(6,6,7, 2018),
				(6,7,7, 2019),
				(7,2,6, 2019),
				(7,4,6, 2019),
				(7,7,6, 2019);
				
	
				
 /*
 --segura seconde1 mathématiques 2019
 --segura 1S1 mathématiques 2019
 --segura 1S2 informatique 2018
 --banner seconde2 physique 2019
 --banner 1S1 physique 2019
 --banner TS1 physique 2018
 --parker 1S1 svt 2018
 --parker TS1 svt 2017
 --francky seconde2 svt 2019
 --francky 1S2 svt 2018
 --francky TS2 svt 2018
 --etrange  seconde1 géographie 2019
 --etrange  seconde2 géographie 2019
 --etrange  1L1 géographie 2018
 --etrange  TL1 géographie 2017
 --neige 1S1 informatique 2019
 --neige TS1 informatique 2018
 --neige TS2 informatique 2019
 -- Pym  seconde2 physique 2019
 -- Pym  1S2 physique 2019
 -- Pym  TS2 physique 2019
 */
 
 
/*un élève ne peut appartenir qu'à une seule classe chaque année.*/
CREATE TABLE IF NOT EXISTS Appartenir (num_Classe BIGINT NOT NULL, num_Eleve BIGINT NOT NULL, année1 INT(4), UNIQUE INDEX(num_Eleve, année1)) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
CREATE INDEX FK_Appartenir_num_Classe ON Appartenir( num_Classe );
CREATE INDEX FK_Appartenir_num_Eleve ON Appartenir( num_Eleve );
CREATE INDEX FK_Appartenir_année1 ON Appartenir( année1 );

INSERT INTO Appartenir (num_Classe, num_Eleve,année1) VALUES
    (6, 1, 2019), (6, 2, 2019), (6, 3, 2019),
    (7, 4, 2019), (7, 5, 2019), (7, 6, 2019), 
    (8, 7, 2019), (8, 8, 2019), (8, 9, 2019), 
	(3, 1, 2018), (3, 2, 2018), (3, 3, 2018), 
    (4, 4, 2018), (4, 5, 2018), (4, 6, 2018), 
    (5, 7, 2018), (5, 8, 2018), (5, 9, 2018), 
	(1, 1, 2017), (1, 2, 2017), (1, 3, 2017),(1, 4, 2017), (1, 5, 2017), 
	(2, 6, 2017), (2, 7, 2017),(2, 8, 2017), (2, 9, 2017), 
  
	(3, 10, 2019), (3, 11, 2019), 
	(4, 12, 2019), (4, 13, 2019), 
	(5, 14, 2019), (5, 15, 2019), 
    (1, 10, 2018), (1, 11, 2018), (1, 12, 2018), 
    (2, 13, 2018), (2, 14, 2018), (2, 15, 2018), 
    
	(1, 16, 2019), (1, 17, 2019), 
    (2, 18, 2019), (2, 19, 2019);
/*
--élèves en terminale en 2019 :
	--TS1 - élèves de 1 à 3 - 2019
	--TS2 - élèves de 4 à 6 - 2019
	--TL1 - élèves de 7 à 9 - 2019
	--1S1 - élèves de 1 à 3 - 2018
	--1S2 - élèves de 4 à 6 - 2018
	--1L1 - élèves de 7 à 9 - 2018
	--seconde1 - élèves de 1 à 5 - 2017
	--seconde2 - élèves de 6 à 9 - 2017
	 --élèves en première en 2019
	--1S1 - élèves de 10 à 11 - 2019
	--1S2 - élèves de 12 à 13 - 2019
	--1L1 - élèves de 14 à 15 - 2019
	--seconde1 - élèves de 10 à 12 - 2018
	--seconde2 - élèves de 13 à 15 - 2018
	 --élèves en seconde en 2019
	--seconde1 - élèves de 16 à 17 - 2019
	--seconde2 - élèves de 18 à 19 - 2019	
 */	
	
CREATE TABLE IF NOT EXISTS Evaluer (num_Enseignant BIGINT NOT NULL, num_Eleve BIGINT NOT NULL, num_Classe BIGINT NOT NULL, num_Discipline BIGINT NOT NULL, note INT(4), trimestre INT(1), année1 INT(4)) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
CREATE INDEX FK_Faire_cours_num_Enseignant ON Evaluer( num_enseignant );
CREATE INDEX FK_Evaluer_num_Eleve ON Evaluer( num_Eleve );
CREATE INDEX FK_Evaluer_num_Discipline ON Evaluer( num_Discipline );
CREATE INDEX FK_Evaluer_année1 ON Evaluer( année1 );

INSERT INTO Evaluer (num_Enseignant, num_Eleve, num_Classe, num_Discipline, note, trimestre, année1) VALUES
	(1, 16, 1, 1, 15, 1, 2019),
	(1, 17, 1, 1, 18, 1, 2019),
	(1, 17, 4, 2, 12, 1, 2019),
	(1, 18, 4, 2, 13, 1, 2019);

/*
	--segura, élève 16 ,seconde1  , math, 15, 1, 2019
	--segura, élève 17 ,seconde1 2019 , math, 17, 1, 2019
	--segura, élève 12 ,1S2 , informatique, 12, 1, 2019
	--segura, élève 13 ,1S2 , informatique, 13, 1, 2019
*/