-------------Creation Base de donn�es: Facturation-------------------
CREATE DATABASE Facturation;
-------------Cr�ation table Client----------------------------------
CREATE TABLE Client
(
CodeClt Varchar(5) NOT NULL PRIMARY KEY,
NomClt Varchar (20) NOT NULL,
PreClt Varchar (20) NOT NULL,
TelClt Varchar(10) NOT NULL,
Region Varchar(30) NOT NULL
);
**************Cr�ation table Client(2�me M�thode)********************
CREATE TABLE Client
(
CodeClt Varchar(5) NOT NULL,
NomClt Varchar (20) NOT NULL,
PreClt Varchar (20) NOT NULL,
TelClt Varchar(10) NOT NULL,
Region Varchar(30) NOT NULL,
CONSTRAINT Clt_Pry PRIMARY KEY(CodeClt)
);
-----------------Cr�ation Table Categorie----------------------------
CREATE TABLE Categorie
(
CodeCat Varchar(5) NOT NULL PRIMARY KEY,
LibCat Varchar(20) NOT NULL,
TVA Enum ('12%','18%','27%') NOT NULL
);
------------------Cr�ation table Article----------------------------
CREATE TABLE Article
(
CodeArt Varchar(5) ,
LibArt Varchar (20) NOT NULL,
PA Decimal (6,3) NOT NULL CHECK (PA>0) ,
PV Decimal (7,3) NOT NULL CHECK (PV>=PA),
QteStk int DEFAULT 0 NOT NULL CHECK (QteStk>=0),
CodeCat Varchar (5) NOT NULL,
PRIMARY KEY(CodeArt),
FOREIGN KEY(CodeCat) REFERENCES Categorie (CodeCat) ON DELETE
CASCADE ON UPDATE CASCADE
);
*****************Cr�ation table Article(2�me M�thode)*****************
CREATE TABLE Article
(
CodeArt Varchar(5) NOT NULL PRIMARY KEY,
LibArt Varchar (20) NOT NULL,
PA Decimal (6,3) NOT NULL ,
PV Decimal (7,3) NOT NULL ,
QteStk int DEFAULT 0 NOT NULL,
CodeCat Varchar (5) NOT NULL,
CONSTRAINT Verif_PA CHECK (PA>0),
CONSTRAINT Verif_PV CHECK (PV>=PA),
CONSTRAINT Verif_STK CHECK (QteStk>=0),
FOREIGN KEY(CodeCat) REFERENCES Categorie (CodeCat) ON DELETE CASCADE ON
UPDATE CASCADE
);
-------------------Cr�ation table Facture--------------------------------
CREATE TABLE Facture
(
NumFact Int(10) UNSIGNED ZEROFILL AUTO_INCREMENT NOT NULL,
DateFact DATETIME NOT NULL,# CodeClt Varchar(5) NOT NULL,
PRIMARY KEY(NumFact, DateFact),
FOREIGN KEY (CodeClt) REFERENCES Client (CodeClt) ON DELETE CASCADE ON UPDATE
CASCADE
);
*******************Cr�ation table Facture (2�me M�thode)******************
CREATE TABLE Facture
(
NumFact Int(10) UNSIGNED ZEROFILL AUTO_INCREMENT NOT NULL,
DateFact DATETIME NOT NULL,
CodeClt Varchar(5) NOT NULL,
CONSTRAINT Fact_Pry PRIMARY KEY(NumFact, DateFact),
CONSTRAINT Fact_Clt FOREIGN KEY (CodeClt) REFERENCES Client (CodeClt)ON
DELETE CASCADE ON UPDATE CASCADE
);
-------------------Cr�ation de la table Commande-------------------------
CREATE TABLE Commande
(
NumFact Int(10) UNSIGNED ZEROFILL NOT NULL,
CodeArt Varchar(5) NOT NULL,
QteAchat Int DEFAULT 1 NOT NULL CHECK (QteAchat >=1),
PRIMARY KEY(NumFact, CodeArt),
FOREIGN KEY (NumFact) REFERENCES Facture(NumFact) ON DELETE CASCADE ON UPDATE
CASCADE,
FOREIGN KEY (CodeArt) REFERENCES Article(CodeArt) ON DELETE CASCADE ON UPDATE
CASCADE
);