CREATE TABLE ARTICLE
(
ArticleId INT NOT NULL,
Designation VARCHAR(64) NOT NULL,
CONSTRAINT ARTICLE_PK PRIMARY KEY (ArticleId)
) ;
CREATE TABLE CLIENT
(
ClientId INT NOT NULL,
CLientNom VARCHAR(64) NOT NULL,
CONSTRAINT CLIENT PK PRIMARY KEY (ClientId)
) ;
CREATE TABLE DEVIS
(
DevisId INT NOT NULL,
ClientId INT NOT NULL,
DateEnvoi DATETIME NOT NULL,
DateValidation DATETIME NOT NULL,
DevisMontant INT NOT NULL,
CONSTRAINT DEVIS_PK PRIMARY KEY (DevisId),
CONSTRAINT DEVIS_CLIENT_FK FOREIGN KEY (ClientId) REFERENCES CLIENT (ClientId)
) ;
CREATE TABLE LIGNE_DEVIS
(
LigneDevisId INT NOT NULL,
DevisId INT NOT NULL,
ArticleId INT NOT NULL,
Quantite INT NOT NULL,
PrixHT INT NOT NULL,
TVA DECIMAL(4,2) NOT NULL,
CONSTRAINT LIGNE_DEVIS_PK PRIMARY KEY (LigneDevisId),
CONSTRAINT LIGNE_DEVIS_DEVIS_FK FOREIGN KEY (DevisId) REFERENCES DEVIS (DevisId),
CONSTRAINT LIGNE_DEVIS_ARTICLE_FK FOREIGN KEY (ArticleId) REFERENCES ARTICLE (ArticleId)
) ;
CREATE TABLE FACTURE
(
FactureId INT NOT NULL,
ClientId INT NOT NULL,
FactureDate DATETIME NOT NULL,
CONSTRAINT FACTURE_PK PRIMARY KEY (FactureId),
CONSTRAINT FACTURE_CLIENT_FK FOREIGN KEY (ClientId) REFERENCES CLIENT (ClientId)
) ;
CREATE TABLE BON_LIVRAISON
(
BonId INT NOT NULL,
FactureId INT NOT NULL,
DateLivraison DATETIME NOT NULL,
CONSTRAINT BON_LIVRAISON_PK PRIMARY KEY (BonId),
CONSTRAINT BON_LIVRAISON_FACTURE_FK FOREIGN KEY (FactureId) REFERENCES FACTURE (FactureId)
) ;
CREATE TABLE BON_DETAIL
(
BonId INT NOT NULL,
LigneDevisId INT NOT NULL,
CONSTRAINT BON_DETAIL_PK PRIMARY KEY (LigneDevisId),
CONSTRAINT BON_DETAIL_BON_LIVRAISON_FK FOREIGN KEY (BonId) REFERENCES BON_LIVRAISON (BonId),
CONSTRAINT BON_DETAIL_LIGNE_DEVIS_FK FOREIGN KEY (LigneDevisId) REFERENCES LIGNE_DEVIS (LigneDevisId)
) ;
INSERT INTO ARTICLE (ArticleId, Designation) VALUES (1, 'schmilblick') ;
INSERT INTO ARTICLE (ArticleId, Designation) VALUES (2, 'biglotron') ;
INSERT INTO ARTICLE (ArticleId, Designation) VALUES (3, 'boulon') ;
INSERT INTO CLIENT (ClientId, CLientNom) VALUES (1, 'Etablissements Naudin Montauban') ;
INSERT INTO CLIENT (ClientId, CLientNom) VALUES (2, 'Volfoni Frères') ;
INSERT INTO CLIENT (ClientId, CLientNom) VALUES (3, 'Delafoy, Instruments de ménage') ;
INSERT INTO DEVIS (ClientId, DevisId, DateEnvoi, DateValidation, DevisMontant) VALUES (1, 1, '2013-08-01', '2013-08-05', 1500) ;
INSERT INTO DEVIS (ClientId, DevisId, DateEnvoi, DateValidation, DevisMontant) VALUES (2, 2, '2013-07-14', '2013-08-02', 7000) ;
INSERT INTO LIGNE_DEVIS (DevisId, LigneDevisId, ArticleId, Quantite, PrixHT, TVA) VALUES (1, 1, 1, 10, 100, 19.6) ;
INSERT INTO LIGNE_DEVIS (DevisId, LigneDevisId, ArticleId, Quantite, PrixHT, TVA) VALUES (1, 3, 3, 40, 150, 19.6) ;
INSERT INTO LIGNE_DEVIS (DevisId, LigneDevisId, ArticleId, Quantite, PrixHT, TVA) VALUES (1, 5, 3, 20, 200, 19.6) ;
INSERT INTO LIGNE_DEVIS (DevisId, LigneDevisId, ArticleId, Quantite, PrixHT, TVA) VALUES (1, 6, 3, 20, 200, 19.6) ;
INSERT INTO LIGNE_DEVIS (DevisId, LigneDevisId, ArticleId, Quantite, PrixHT, TVA) VALUES (2, 2, 2, 20, 200, 19.6) ;
INSERT INTO LIGNE_DEVIS (DevisId, LigneDevisId, ArticleId, Quantite, PrixHT, TVA) VALUES (2, 4, 3, 20, 200, 19.6) ;
INSERT INTO FACTURE (ClientId, FactureId, FactureDate) VALUES (1, 1, '2013-08-02') ;
INSERT INTO FACTURE (ClientId, FactureId, FactureDate) VALUES (2, 2, '2013-07-28') ;
INSERT INTO FACTURE (ClientId, FactureId, FactureDate) VALUES (1, 3, '2013-08-03') ;
INSERT INTO FACTURE (ClientId, FactureId, FactureDate) VALUES (2, 4, '2013-08-03') ;
INSERT INTO FACTURE (ClientId, FactureId, FactureDate) VALUES (1, 5, '2013-08-02') ;
INSERT INTO BON_LIVRAISON (FactureId, BonId, DateLivraison) VALUES (1, 1, '2013-08-03') ;
INSERT INTO BON_LIVRAISON (FactureId, BonId, DateLivraison) VALUES (1, 2, '2013-08-04') ;
INSERT INTO BON_LIVRAISON (FactureId, BonId, DateLivraison) VALUES (2, 3, '2013-07-29') ;
INSERT INTO BON_LIVRAISON (FactureId, BonId, DateLivraison) VALUES (3, 4, '2013-07-29') ;
INSERT INTO BON_LIVRAISON (FactureId, BonId, DateLivraison) VALUES (4, 5, '2013-07-29') ;
INSERT INTO BON_LIVRAISON (FactureId, BonId, DateLivraison) VALUES (5, 6, '2013-07-29') ;
INSERT INTO BON_DETAIL (LigneDevisId, BonId) VALUES (1, 2) ;
INSERT INTO BON_DETAIL (LigneDevisId, BonId) VALUES (5, 6) ;
INSERT INTO BON_DETAIL (LigneDevisId, BonId) VALUES (2, 5) ;
Partager