Drop database GestCom;


Create Database GestCom;

use GestCom

Create Table Client (
NumClient int (4) NOT Null AUTO_INCREMENT,
Nom char (30) NOT Null,
Prenom char (20),
Adresse char (50),
CodePostal char (5),
Ville char (20),
Tel	 char (10),
PRIMARY KEY (NumClient)
);


Create Table Categorie (
CodeCat int (4) NOT Null AUTO_INCREMENT,
LibelleCat char (30),
PRIMARY KEY (CodeCat)
);


Create Table Article (
RefArt char (6) NOT Null PRIMARY KEY,
DesignationArt char (30) NOT Null,
PrixHTArt numeric (8,2) CHECK (PrixHTArt >= 0),
CodeCat int (4) NOT Null REFERENCES Categorie (CodeCat)
);


Create Table Stock (
RefArt char (5) NOT Null,
Depot char (2) NOT Null,
QteStock numeric (5) CHECK (QteStock >= 0),
StockMini numeric (5) CHECK (StockMaxi >= StockMini),
StockMaxi numeric (5) CHECK (StockMaxi >= StockMini),
Primary Key (RefArt, Depot),
FOREIGN KEY (RefArt) REFERENCES Article(RefArt)
);


Create Table Commande (
NumCde int (4) PRIMARY KEY,
DateCde timestamp DEFAULT now(),
TauxRemise numeric (2) DEFAULT 0 CHECK (TauxRemise <= 50) ,
EtatCde char(2) DEFAULT 'EC' CHECK (EtatCde IN ('EC', 'LP', 'LI', 'SO')),
NumCli int NOT Null REFERENCES Client(NumCli)
);


Create Table LignesCde (
NumCde 	int (4) NOT Null,
NumLig	 int (2) NOT Null,
QteCde 	int (3) DEFAULT 1 CHECK (QteCde >= 0),
RefArt 	char (5) NOT Null,
PRIMARY KEY (NumCde, NumLig),
FOREIGN KEY (NumCde) REFERENCES Commande(NumCde),
FOREIGN KEY (RefArt) REFERENCES Article(RefArt)
);


Create Table HistoFact (
NumFact int (4) NOT Null AUTO_INCREMENT,
DateFact timestamp DEFAULT now(),
MontantFacture numeric (9,2) NOT Null,
EtatFact char(2) DEFAULT 'NR' CHECK (EtatFact IN ('NR', 'RP', 'RC')),
NumCde int (4),
PRIMARY KEY (NumFact),
FOREIGN KEY (NumCde) REFERENCES Commande(NumCde)
);

show tables;