-- création base de données UNIV

DROP DATABASE IF EXISTS univ;
CREATE DATABASE  univ; -- IF NOT EXISTS
USE univ;


CREATE TABLE IF NOT EXISTS etudiants(
    numero CHAR(5) PRIMARY KEY,
    nom VARCHAR(30) NOT NULL,
    prenom VARCHAR(30) NOT NULL,
    ville VARCHAR(30) NOT NULL
);

CREATE TABLE IF NOT EXISTS cours
(
    nomcours VARCHAR(30) PRIMARY KEY,
    prerequis VARCHAR(30) NOT NULL,
    numsalle TINYINT -- ,
--    FOREIGN KEY (numsalle) REFERENCES salles(numsalle) ON UPDATE CASCADE ON DELETE SET NULL
) ENGINE=InnoDB;


CREATE TABLE IF NOT EXISTS notes(
    NumEtudiant CHAR(5),
    cours VARCHAR(30),
    valeur TINYINT(2),
    PRIMARY KEY(NumEtudiant, cours) -- ,
    -- FOREIGN KEY (numetudiants) REFERENCES etudiants(numero),
    -- FOREIGN KEY (cours) REFERENCES cours(nomcours) ON UPDATE CASCADE ON DELETE CASCADE
);

CREATE TABLE salles
(
    numsalle TINYINT PRIMARY KEY,
    adresse VARCHAR(30) NOT NULL
) ENGINE=InnoDB;


CREATE TABLE horaires
(
    nomcours VARCHAR(30),
    jour VARCHAR(30),
    heure INT,
    PRIMARY KEY(nomcours,jour,heure)
) ENGINE=InnoDB;


-- Chargement des donnees depuis le repertoire data de mysql (Uwamp/...)
LOAD DATA LOCAL INFILE 'donnees//etudiants.csv' INTO TABLE `etudiants` FIELDS TERMINATED BY '\t' ESCAPED BY '\\' LINES TERMINATED BY '\n';

LOAD DATA LOCAL INFILE 'donnees//salles.csv' INTO TABLE `salles` FIELDS TERMINATED BY '\t' ESCAPED BY '\\' LINES TERMINATED BY '\n';

LOAD DATA LOCAL INFILE 'donnees//cours.csv' INTO TABLE `cours` FIELDS TERMINATED BY '\t' ESCAPED BY '\\' LINES TERMINATED BY '\n';

LOAD DATA LOCAL INFILE 'donnees//notes.csv' INTO TABLE `notes` FIELDS TERMINATED BY '\t' ESCAPED BY '\\' LINES TERMINATED BY '\n';

LOAD DATA LOCAL INFILE 'donnees//horaires.csv' INTO TABLE `horaires` FIELDS TERMINATED BY '\t' ESCAPED BY '\\' LINES TERMINATED BY '\n';


-- mise en place des contraintes d'intégrité référentielles (=clés étrangères)
ALTER TABLE cours ADD CONSTRAINT fk_cours_salles FOREIGN KEY (numsalle) REFERENCES salles(numsalle) ON UPDATE CASCADE ON DELETE CASCADE;
ALTER TABLE notes ADD CONSTRAINT fk_notes_cours FOREIGN KEY (cours) REFERENCES cours(nomcours) ON UPDATE CASCADE ON DELETE CASCADE;
ALTER TABLE notes ADD CONSTRAINT fk_notes_etudiant FOREIGN KEY (NumEtudiant) REFERENCES etudiants(numero) ON UPDATE CASCADE ON DELETE CASCADE;
ALTER TABLE horaires ADD CONSTRAINT fk_horaires_cours FOREIGN KEY (nomcours) REFERENCES cours(nomcours) ON UPDATE CASCADE ON DELETE CASCADE;

