1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38
| CREATE TABLE sports(
id_sport SERIAL NOT NULL PRIMARY KEY,
sport VARCHAR(50) NOT NULL
);
CREATE TABLE article(
id_article SERIAL NOT NULL PRIMARY KEY,
id_sport INT NOT NULL,
nom_article VARCHAR(50) NOT NULL,
prix_mini INT NOT NULL CHECK(prix_mini > 0),
FOREIGN KEY(id_sport) REFERENCES sports
);
CREATE TABLE distributeur(
id_distributeur SERIAL NOT NULL PRIMARY KEY,
nom VARCHAR(100) NOT NULL,
departement INT NOT NULL,
adresse VARCHAR(100)
);
CREATE TABLE catalogue(
id_distributeur INT NOT NULL,
id_article INT NOT NULL,
prix_conseille INT NOT NULL,-- CHECK(prix_conseille >= article.prix_mini),
stock INT NOT NULL CHECK(stock >= 0),
FOREIGN KEY(id_distributeur) REFERENCES distributeur,
FOREIGN KEY(id_article) REFERENCES article,
PRIMARY KEY(id_distributeur, id_article)
);
CREATE TABLE vente(
id_vente SERIAL NOT NULL PRIMARY KEY,
id_distributeur INT NOT NULL,
id_article INT NOT NULL,
quantite INT CHECK(quantite >= 0),
FOREIGN KEY(id_distributeur) REFERENCES distributeur,
FOREIGN KEY(id_article) REFERENCES article
); |
Partager