CREATE.sql

CREATE TABLE IF NOT EXISTS Utilisateur(
    login VARCHAR(64) NOT NULL,
    motDePasse VARCHAR(128) NOT NULL,
    nom VARCHAR(32) NOT NULL,
    prenom VARCHAR(32) NOT NULL,
    adresse VARCHAR(128) NOT NULL,
    mail VARCHAR(64) NOT NULL,
    CONSTRAINT pk_Utilisateur PRIMARY KEY (login)
);
CREATE TABLE IF NOT EXISTS Personnel(
    login VARCHAR(64) NOT NULL,
    CONSTRAINT pk_Personnel PRIMARY KEY (login),
    CONSTRAINT fk_Personnel_Utilisateur FOREIGN KEY (login) REFERENCES Utilisateur(login)
);
CREATE TABLE IF NOT EXISTS Adherent(
    login VARCHAR(64) NOT NULL,
    numCarte VARCHAR(64) NOT NULL,
    dateNaissance DATE NOT NULL,
    numTel VARCHAR(32) NOT NULL,
    dateAdhesion DATE NOT NULL,
    blacklisted BOOLEAN DEFAULT FALSE,
    CONSTRAINT pk_Adherent PRIMARY KEY (login),
    CONSTRAINT fk_Adherent_Utilisateur FOREIGN KEY (login) REFERENCES Utilisateur(login),
    CONSTRAINT uq_NumCarte UNIQUE(numCarte)
    /*CONSTRAINT chk_DateNaissance */
    /*CONSTRAINT chk_dateAdhesion  */
);
CREATE TABLE IF NOT EXISTS Sanction(
    sanctionID SERIAL NOT NULL,
    enCours BOOLEAN DEFAULT TRUE,
    dateSanction DATE NOT NULL,
    adherent VARCHAR(64) NOT NULL,
    CONSTRAINT pk_Sanction PRIMARY KEY(sanctionID),
    CONSTRAINT fk_Sanction_Adherent FOREIGN KEY(adherent) REFERENCES Adherent(login)
    /* CONSTRAINT chk_DateSanction */
);
CREATE TABLE IF NOT EXISTS Ressource (
    codeUnique VARCHAR PRIMARY KEY, 
    titre VARCHAR NOT NULL,
    dateApparition DATE NOT NULL,
    editeur VARCHAR NOT NULL, 
    genre VARCHAR NOT NULL,
    codeClassification VARCHAR NOT NULL
);
CREATE TABLE IF NOT EXISTS Suspension(
    sanctionID INT NOT NULL,
    duree INT,
    CONSTRAINT pk_Suspension PRIMARY KEY(sanctionID),
    CONSTRAINT fk_Suspension_Sanction FOREIGN KEY (sanctionID) REFERENCES Sanction(sanctionID),
    CONSTRAINT chk_Duree CHECK(duree >= 0)
);
CREATE TABLE IF NOT EXISTS Exemplaire (
    ref VARCHAR,
    etat VARCHAR NOT NULL,
    CHECK (etat = 'Neuf' OR etat = 'Abimé' or etat = 'Bon' or etat = 'Perdu'),
    disponible BOOLEAN NOT NULL,
    ressource VARCHAR NOT NULL,
    CONSTRAINT pk_Exemplaire PRIMARY KEY(ref, ressource),
    CONSTRAINT uq_ref UNIQUE(ref),
    FOREIGN KEY (ressource) REFERENCES Ressource(codeUnique)
);
CREATE TABLE IF NOT EXISTS Remboursement(
    sanctionID INT NOT NULL,
    dateEcheance DATE,
    montant FLOAT NOT NULL,
    refExemplaire VARCHAR NOT NULL,
    CONSTRAINT pk_Remboursement PRIMARY KEY(sanctionID),
    CONSTRAINT fk_Remboursement_Sanction FOREIGN KEY(sanctionID) REFERENCES Sanction(sanctionID),
    CONSTRAINT fk_Remboursement_Exemplaire FOREIGN KEY(refExemplaire) REFERENCES Exemplaire(ref),
    CONSTRAINT chk_montant CHECK(montant >= 0.0)
    /* CONSTRAINT DateEcheance */
);
CREATE TABLE IF NOT EXISTS Pret(
    pretID SERIAL NOT NULL,
    date DATE NOT NULL,
    duree INT NOT NULL,
    adherent VARCHAR(64) NOT NULL,
    CONSTRAINT pk_Pret PRIMARY KEY(pretID),
    CONSTRAINT fk_Pret_Adherent FOREIGN KEY(adherent) REFERENCES Adherent(login),
    CONSTRAINT chk_duree CHECK(duree >= 0)
    /* CONSTRAINT CHECK DATE */
);



CREATE TABLE IF NOT EXISTS Livre (
    codeUnique VARCHAR PRIMARY KEY, 
    FOREIGN KEY (codeUnique) REFERENCES Ressource(codeUnique),
    ISBN VARCHAR NOT NULL,
    resume VARCHAR NOT NULL, 
    langue VARCHAR NOT NULL
); 

CREATE TABLE IF NOT EXISTS OeuvreMusicale (
    codeUnique VARCHAR PRIMARY KEY, 
    FOREIGN KEY (codeUnique) REFERENCES Ressource(codeUnique),
    longueur INTEGER NOT NULL
);

CREATE TABLE IF NOT EXISTS Film (
    codeUnique VARCHAR PRIMARY KEY, 
    FOREIGN KEY (codeUnique) REFERENCES Ressource(codeUnique),
    longueur INTEGER NOT NULL,
    synopsis VARCHAR NOT NULL
);

CREATE TABLE IF NOT EXISTS Contributeur (
    contributeurId INTEGER PRIMARY KEY,
    nom VARCHAR NOT NULL,
    prenom VARCHAR NOT NULL,
    dateNaissance DATE NOT NULL,
    nationalite VARCHAR NOT NULL
);

CREATE TABLE IF NOT EXISTS Auteur (
    livre VARCHAR NOT NULL,
    auteur INTEGER NOT NULL,
    FOREIGN KEY (auteur) REFERENCES Contributeur(contributeurId),
    FOREIGN KEY (livre) REFERENCES Livre(codeUnique),
    PRIMARY KEY (livre, auteur)
);

CREATE TABLE IF NOT EXISTS Interprete (
    oeuvreMusicale VARCHAR NOT NULL,
    interprete INTEGER NOT NULL,
    FOREIGN KEY (interprete) REFERENCES Contributeur(contributeurId),
    FOREIGN KEY (oeuvreMusicale) REFERENCES OeuvreMusicale(codeUnique),
    PRIMARY KEY (oeuvreMusicale, interprete)
);

CREATE TABLE IF NOT EXISTS Compositeur (
    oeuvreMusicale VARCHAR NOT NULL,
    compositeur INTEGER NOT NULL,
    FOREIGN KEY (compositeur) REFERENCES Contributeur(contributeurId),
    FOREIGN KEY (oeuvreMusicale) REFERENCES OeuvreMusicale(codeUnique),
    PRIMARY KEY (oeuvreMusicale, compositeur)
);

CREATE TABLE IF NOT EXISTS Acteur (
    film VARCHAR NOT NULL,
    acteur INTEGER NOT NULL,
    FOREIGN KEY (acteur) REFERENCES Contributeur(contributeurId),
    FOREIGN KEY (film) REFERENCES Film(codeUnique),
    PRIMARY KEY (film, acteur)
);

CREATE TABLE IF NOT EXISTS Realisateur (
    film VARCHAR NOT NULL,
    realisateur INTEGER NOT NULL,
    FOREIGN KEY(realisateur) REFERENCES Contributeur(contributeurId),
    FOREIGN KEY(film) REFERENCES Film(codeUnique),
    PRIMARY KEY (film, realisateur)
);



CREATE TABLE IF NOT EXISTS ExemplairePret (
    ref VARCHAR NOT NULL,
    pret INTEGER NOT NULL,
    FOREIGN KEY (ref) REFERENCES Exemplaire(ref),
    FOREIGN KEY (pret) REFERENCES Pret(pretID),
    PRIMARY KEY (ref, pret)
);


 /* ****** TRIGGERS ****** */


CREATE OR REPLACE FUNCTION check_exclusivite_realisateur()
RETURNS trigger AS
$$
DECLARE 
    var INTEGER;
BEGIN
    SELECT * INTO var FROM Acteur a WHERE a.acteur = NEW.realisateur AND film = NEW.film;
    RAISE EXCEPTION 'Erreur_exclu';
    EXCEPTION 
        WHEN NO_DATA_FOUND THEN 
            NULL;
        WHEN OTHERS THEN 
            RAISE EXCEPTION 'Erreur : ce contributeur existe déjà dans la table Auteur';
    RETURN NEW;
END;
$$
LANGUAGE 'plpgsql';

CREATE TRIGGER T_EXCUSIVITE_REALISATEUR BEFORE INSERT ON Realisateur 
FOR EACH ROW 
EXECUTE PROCEDURE check_exclusivite_realisateur();

CREATE OR REPLACE FUNCTION check_exclusivite_acteur()
RETURNS trigger AS
$$
DECLARE 
    var INTEGER;
BEGIN 
    SELECT realisateur INTO var FROM Realisateur WHERE realisateur = NEW.auteur AND film = NEW.film;
    RAISE EXCEPTION 'Erreur_exclu';
    EXCEPTION 
        WHEN NO_DATA_FOUND THEN 
            NULL;
        WHEN OTHERS THEN 
            RAISE EXCEPTION 'Erreur : ce contributeur existe déjà dans la table Realisateur';
    RETURN NEW;
END;
$$
LANGUAGE 'plpgsql';

CREATE TRIGGER T_EXCUSIVITE_ACTEUR BEFORE INSERT OR UPDATE ON Acteur
FOR EACH ROW
EXECUTE PROCEDURE check_exclusivite_acteur();

CREATE OR REPLACE FUNCTION check_exclusivite_compositeur()
RETURNS trigger AS
$$
DECLARE 
    var INTEGER;
BEGIN
    SELECT interprete INTO var FROM Interprete WHERE interprete = NEW.compositeur AND oeuvreMusicale = NEW.oeuvreMusicale;
    RAISE EXCEPTION 'Erreur_exclu';
    EXCEPTION 
        WHEN NO_DATA_FOUND THEN 
            NULL;
        WHEN OTHERS THEN 
            RAISE EXCEPTION 'Erreur : ce contributeur existe déjà dans la table Interprete';
    RETURN NEW;
END;
$$
LANGUAGE 'plpgsql';

CREATE TRIGGER T_EXCUSIVITE_COMPOSITEUR BEFORE INSERT OR UPDATE ON Compositeur 
FOR EACH ROW 
EXECUTE PROCEDURE check_exclusivite_compositeur();

CREATE OR REPLACE FUNCTION check_exclusivite_interprete()
RETURNS trigger AS
$$
DECLARE 
    var INTEGER;
BEGIN
    SELECT compositeur INTO var FROM Compositeur WHERE compositeur = NEW.interprete AND oeuvreMusicale = NEW.oeuvreMusicale;
    RAISE EXCEPTION 'Erreur_exclu';
        EXCEPTION 
            WHEN NO_DATA_FOUND THEN 
                NULL;
            WHEN OTHERS THEN 
                RAISE EXCEPTION 'Erreur : ce contributeur existe déjà dans la table Compositeur';
    RETURN NEW;
END;
$$
LANGUAGE 'plpgsql';

CREATE TRIGGER T_EXCUSIVITE_INTERPRETE BEFORE INSERT OR UPDATE ON Interprete 
FOR EACH ROW 
EXECUTE PROCEDURE check_exclusivite_interprete();