![]() |
Projet e-stock
0.2
BTS SN-IR 2019
|
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;