-- *********************************************
-- * Standard SQL generation                   
-- *--------------------------------------------
-- * DB-MAIN version: 9.2.0              
-- * Generator date: Oct 16 2014              
-- * Generation date: Thu Feb 25 20:31:44 2016 
-- * LUN file: C:\Users\Antoine\Documents\Informatique de Gestion\Bac 2\Q2 Projet Php\Analyse\E-A\Projet php.lun 
-- * Schema: E/A Relational Model/SQL 
-- ********************************************* 


-- Database Section
-- ________________ 

create database Projet_DB;


-- DBSpace Section
-- _______________


-- Tables Section
-- _____________ 

create table Annees (
     annee_scolaire varchar(9) not null,
     constraint ID_Annees_ID primary key (annee_scolaire));

create table Classes (
     nom_classe varchar(20) not null,
     login_membre varchar(30) not null,
     nom_option varchar(50) not null,
     nom_enseignement varchar(30) not null,
     constraint ID_Classes_ID primary key (nom_classe));

create table Commentaires_CDC (
     id_eleve varchar(20) not null,
     commentaire varchar(600) not null,
     annee_scolaire varchar(9) not null,
     nom_periode varchar(9) not null,
     constraint ID_Comme_Eleve_ID primary key (id_eleve));

create table Commentaires_Cours (
     annee_scolaire varchar(9) not null,
     nom_periode varchar(9) not null,
     nom_matiere varchar(30) not null,
     login_membre varchar(30) not null,
     nom_type_cours varchar(30) not null,
     nom_classe varchar(20) not null,
     id_eleve varchar(20) not null,
     commentaire varchar(300) not null,
     constraint ID_Commentaires_Cours_ID primary key (annee_scolaire, nom_periode, id_eleve, nom_matiere, login_membre, nom_type_cours, nom_classe));

create table Contenu_Classes (
     annee_scolaire varchar(9) not null,
     nom_classe varchar(20) not null,
     id_eleve varchar(20) not null,
     constraint ID_Contenu_Classes_ID primary key (annee_scolaire, id_eleve, nom_classe));

create table Cours (
     nom_matiere varchar(30) not null,
     login_membre varchar(30) not null,
     nom_type_cours varchar(30) not null,
     nom_classe varchar(20) not null,
     heures_par_semaine numeric(2) not null,
     constraint ID_Cours_ID primary key (nom_matiere, login_membre, nom_type_cours, nom_classe));

create table Eleves (
     id_eleve varchar(20) not null,
     nom varchar(50) not null,
     prenom varchar(30) not null,
     date_naissance varchar(20) not null,
     url_photo varchar(90),
     constraint ID_Eleves_ID primary key (id_eleve));

create table Enseignements (
     nom_enseignement varchar(30) not null,
     nom_abbr varchar(5) not null,
     constraint ID_Enseignements_ID primary key (nom_enseignement));

create table Examens_Passage (
     nom_matiere varchar(30) not null,
     login_membre varchar(30) not null,
     nom_type_cours varchar(30) not null,
     nom_classe varchar(20) not null,
     id_eleve varchar(20) not null,
     consignes_examen varchar(1000) not null,
     constraint ID_Examens_Passage_ID primary key (id_eleve, nom_matiere, login_membre, nom_type_cours, nom_classe));

create table Matieres (
     nom_matiere varchar(30) not null,
     nom_abbr varchar(5) not null,
     constraint ID_Matieres_ID primary key (nom_matiere));

create table Membres (
     login_membre varchar(30) not null,
     nom varchar(50) not null,
     prenom varchar(30) not null,
     email varchar(100),
     mdp varchar(150) not null,
     Derniere_conn datetime,
     libelle varchar(20) not null,
     constraint ID_Membres_ID primary key (login_membre));

create table Niveaux_Accessibilite (
     libelle varchar(20) not null,
     constraint ID_Niveaux_Accessibilite_ID primary key (libelle));

create table Options (
     nom_option varchar(50) not null,
     nom_abbr char(10) not null,
     constraint ID_Options_ID primary key (nom_option));

create table Periodes (
     annee_scolaire varchar(9) not null,
     nom_periode varchar(9) not null,
     date_debut varchar(10) not null,
     date_fin varchar(10) not null,
     date_remise_bulletins varchar(10) not null,
     constraint ID_Periodes_ID primary key (annee_scolaire, nom_periode));

create table Points_Bulletin (
     nom_matiere varchar(30) not null,
     login_membre varchar(30) not null,
     nom_type_cours varchar(30) not null,
     nom_classe varchar(20) not null,
     annee_scolaire varchar(9) not null,
     nom_periode varchar(9) not null,
     id_eleve varchar(20) not null,
     nom_type_connaissance varchar(30) not null,
     evaluation varchar(3) not null,
     constraint ID_Points_Bulletin_ID primary key (id_eleve, nom_matiere, login_membre, nom_type_cours, nom_classe, nom_type_connaissance, annee_scolaire, nom_periode));

create table Type_Connaissances (
     nom_type_connaissance varchar(30) not null,
     nom_abbr varchar(5) not null,
     constraint ID_Type_Connaissances_ID primary key (nom_type_connaissance));

create table Type_cours (
     nom_type_cours varchar(30) not null,
     nom_abbr varchar(5) not null,
     constraint ID_Type_cours_ID primary key (nom_type_cours));


-- Constraints Section
-- ___________________ 

alter table Classes add constraint REF_Class_Membr_FK
     foreign key (login_membre)
     references Membres;

alter table Classes add constraint REF_Class_Optio_FK
     foreign key (nom_option)
     references Options;

alter table Classes add constraint REF_Class_Ensei_FK
     foreign key (nom_enseignement)
     references Enseignements;

alter table Commentaires_CDC add constraint ID_Comme_Eleve_FK
     foreign key (id_eleve)
     references Eleves;

alter table Commentaires_CDC add constraint EQU_Comme_Perio_FK
     foreign key (annee_scolaire, nom_periode)
     references Periodes;

alter table Commentaires_Cours add constraint REF_Comme_Eleve_FK
     foreign key (id_eleve)
     references Eleves;

alter table Commentaires_Cours add constraint REF_Comme_Cours_FK
     foreign key (nom_matiere, login_membre, nom_type_cours, nom_classe)
     references Cours;

alter table Commentaires_Cours add constraint REF_Comme_Perio
     foreign key (annee_scolaire, nom_periode)
     references Periodes;

alter table Contenu_Classes add constraint REF_Conte_Eleve_FK
     foreign key (id_eleve)
     references Eleves;

alter table Contenu_Classes add constraint REF_Conte_Class_FK
     foreign key (nom_classe)
     references Classes;

alter table Contenu_Classes add constraint REF_Conte_Annee
     foreign key (annee_scolaire)
     references Annees;

alter table Cours add constraint REF_Cours_Class_FK
     foreign key (nom_classe)
     references Classes;

alter table Cours add constraint REF_Cours_Type__FK
     foreign key (nom_type_cours)
     references Type_cours;

alter table Cours add constraint REF_Cours_Membr_FK
     foreign key (login_membre)
     references Membres;

alter table Cours add constraint REF_Cours_Matie
     foreign key (nom_matiere)
     references Matieres;

alter table Eleves add constraint ID_Eleves_CHK
     check(exists(select * from Commentaires_CDC
                  where Commentaires_CDC.id_eleve = id_eleve)); 

alter table Examens_Passage add constraint REF_Exame_Eleve
     foreign key (id_eleve)
     references Eleves;

alter table Examens_Passage add constraint REF_Exame_Cours_FK
     foreign key (nom_matiere, login_membre, nom_type_cours, nom_classe)
     references Cours;

alter table Membres add constraint REF_Membr_Nivea_FK
     foreign key (libelle)
     references Niveaux_Accessibilite;

alter table Periodes add constraint ID_Periodes_CHK
     check(exists(select * from Commentaires_CDC
                  where Commentaires_CDC.annee_scolaire = annee_scolaire and Commentaires_CDC.nom_periode = nom_periode)); 

alter table Periodes add constraint REF_Perio_Annee
     foreign key (annee_scolaire)
     references Annees;

alter table Points_Bulletin add constraint REF_Point_Type__FK
     foreign key (nom_type_connaissance)
     references Type_Connaissances;

alter table Points_Bulletin add constraint REF_Point_Eleve
     foreign key (id_eleve)
     references Eleves;

alter table Points_Bulletin add constraint REF_Point_Perio_FK
     foreign key (annee_scolaire, nom_periode)
     references Periodes;

alter table Points_Bulletin add constraint REF_Point_Cours_FK
     foreign key (nom_matiere, login_membre, nom_type_cours, nom_classe)
     references Cours;


-- Index Section
-- _____________ 

create unique index ID_Annees_IND
     on Annees (annee_scolaire);

create unique index ID_Classes_IND
     on Classes (nom_classe);

create index REF_Class_Membr_IND
     on Classes (login_membre);

create index REF_Class_Optio_IND
     on Classes (nom_option);

create index REF_Class_Ensei_IND
     on Classes (nom_enseignement);

create unique index ID_Comme_Eleve_IND
     on Commentaires_CDC (id_eleve);

create index EQU_Comme_Perio_IND
     on Commentaires_CDC (annee_scolaire, nom_periode);

create unique index ID_Commentaires_Cours_IND
     on Commentaires_Cours (annee_scolaire, nom_periode, id_eleve, nom_matiere, login_membre, nom_type_cours, nom_classe);

create index REF_Comme_Eleve_IND
     on Commentaires_Cours (id_eleve);

create index REF_Comme_Cours_IND
     on Commentaires_Cours (nom_matiere, login_membre, nom_type_cours, nom_classe);

create unique index ID_Contenu_Classes_IND
     on Contenu_Classes (annee_scolaire, id_eleve, nom_classe);

create index REF_Conte_Eleve_IND
     on Contenu_Classes (id_eleve);

create index REF_Conte_Class_IND
     on Contenu_Classes (nom_classe);

create unique index ID_Cours_IND
     on Cours (nom_matiere, login_membre, nom_type_cours, nom_classe);

create index REF_Cours_Class_IND
     on Cours (nom_classe);

create index REF_Cours_Type__IND
     on Cours (nom_type_cours);

create index REF_Cours_Membr_IND
     on Cours (login_membre);

create unique index ID_Eleves_IND
     on Eleves (id_eleve);

create unique index ID_Enseignements_IND
     on Enseignements (nom_enseignement);

create unique index ID_Examens_Passage_IND
     on Examens_Passage (id_eleve, nom_matiere, login_membre, nom_type_cours, nom_classe);

create index REF_Exame_Cours_IND
     on Examens_Passage (nom_matiere, login_membre, nom_type_cours, nom_classe);

create unique index ID_Matieres_IND
     on Matieres (nom_matiere);

create unique index ID_Membres_IND
     on Membres (login_membre);

create index REF_Membr_Nivea_IND
     on Membres (libelle);

create unique index ID_Niveaux_Accessibilite_IND
     on Niveaux_Accessibilite (libelle);

create unique index ID_Options_IND
     on Options (nom_option);

create unique index ID_Periodes_IND
     on Periodes (annee_scolaire, nom_periode);

create unique index ID_Points_Bulletin_IND
     on Points_Bulletin (id_eleve, nom_matiere, login_membre, nom_type_cours, nom_classe, nom_type_connaissance, annee_scolaire, nom_periode);

create index REF_Point_Type__IND
     on Points_Bulletin (nom_type_connaissance);

create index REF_Point_Perio_IND
     on Points_Bulletin (annee_scolaire, nom_periode);

create index REF_Point_Cours_IND
     on Points_Bulletin (nom_matiere, login_membre, nom_type_cours, nom_classe);

create unique index ID_Type_Connaissances_IND
     on Type_Connaissances (nom_type_connaissance);

create unique index ID_Type_cours_IND
     on Type_cours (nom_type_cours);