Projet e-stock  0.2
BTS SN-IR 2019
Base de données MySQL

CREATE DATABASE IF NOT EXISTS e-stock;

USE e-stock;

CREATE TABLE IF NOT EXISTS Profil ( idProfil int(11) NOT NULL AUTO_INCREMENT, Nom varchar(64) NOT NULL, PRIMARY KEY (idProfil) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS Groupe ( idGroupe int(11) NOT NULL AUTO_INCREMENT, Nom varchar(64) NOT NULL, Numero int(11) NOT NULL DEFAULT '0', PRIMARY KEY (idGroupe), CONSTRAINT Unique_Groupe UNIQUE (Nom,Numero) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS Utilisateur ( idUtilisateur int(11) NOT NULL AUTO_INCREMENT, idProfil int(11) NOT NULL, idGroupe int(11) NOT NULL, Nom varchar(64) NOT NULL, Prenom varchar(64) NOT NULL, DateValidite date NOT NULL, Identifiant varchar(255) DEFAULT NULL, MotDePasse varchar(255) DEFAULT NULL, Badge varchar(11) NOT NULL, Email varchar(64) NOT NULL, PRIMARY KEY (idUtilisateur), CONSTRAINT Unique_Utilisateur UNIQUE (Badge), CONSTRAINT Utilisateur_fk_1 FOREIGN KEY (idProfil) REFERENCES Profil(idProfil) ON DELETE CASCADE, CONSTRAINT Utilisateur_fk_2 FOREIGN KEY (idGroupe) REFERENCES Groupe(idGroupe) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS Armoire ( idArmoire int(11) NOT NULL AUTO_INCREMENT, Nom varchar(255) NOT NULL, Description varchar(255) DEFAULT NULL, AdresseMac varchar(17) DEFAULT NULL, AdresseIP varchar(15) DEFAULT NULL, PRIMARY KEY (idArmoire) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS Type ( idType int(11) NOT NULL AUTO_INCREMENT, Nom varchar(64) NOT NULL, PRIMARY KEY (idType) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS Unite ( idUnite int(11) NOT NULL AUTO_INCREMENT, Nom varchar(64) NOT NULL, PRIMARY KEY (idUnite) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS Comptage ( idComptage int(11) NOT NULL AUTO_INCREMENT, Nom varchar(64) NOT NULL, PRIMARY KEY (idComptage) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS Action ( idAction int(11) NOT NULL AUTO_INCREMENT, Nom varchar(64) NOT NULL, PRIMARY KEY (idAction) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS Article ( idArticle int(11) NOT NULL AUTO_INCREMENT, idType int(11) NOT NULL, – Type enum('Equipement','Consommable'), Nom varchar(255) NOT NULL, Code varchar(255) NOT NULL, Designation varchar(255) NOT NULL, PRIMARY KEY (idArticle), CONSTRAINT Article_fk_1 FOREIGN KEY (idType) REFERENCES Type(idType) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS Stock ( idStock int(11) NOT NULL AUTO_INCREMENT, idArmoire int(11) NOT NULL, idArticle int(11) NOT NULL, idComptage int(11) NOT NULL, idUnite int(11) NOT NULL, Quantite int(11) DEFAULT 0, PRIMARY KEY (idStock), CONSTRAINT Stock_fk_1 FOREIGN KEY (idArmoire) REFERENCES Armoire(idArmoire) ON DELETE CASCADE, CONSTRAINT Stock_fk_2 FOREIGN KEY (idArticle) REFERENCES Article(idArticle) ON DELETE CASCADE, CONSTRAINT Stock_fk_3 FOREIGN KEY (idComptage) REFERENCES Comptage(idComptage) ON DELETE CASCADE, CONSTRAINT Stock_fk_4 FOREIGN KEY (idUnite) REFERENCES Unite(idUnite) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS Mouvement ( idMouvement int(11) NOT NULL AUTO_INCREMENT, idUtilisateur int(11) NOT NULL, idArticle int(11) NOT NULL, idAction int(11) NOT NULL, – Action enum('Entree','Sortie'), Quantite int(11) DEFAULT NOT NULL, Horodatage datetime NOT NULL, PRIMARY KEY (idMouvement), CONSTRAINT Mouvement_fk_1 FOREIGN KEY (idUtilisateur) REFERENCES Utilisateur(idUtilisateur) ON DELETE CASCADE, CONSTRAINT Mouvement_fk_2 FOREIGN KEY (idArticle) REFERENCES Article(idArticle) ON DELETE CASCADE, CONSTRAINT Mouvement_fk_3 FOREIGN KEY (idAction) REFERENCES Action(idAction) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8;