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 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210
| --------------
SET AUTOCOMMIT = 0
--------------
--------------
START TRANSACTION
--------------
--------------
drop database if exists `livrokaz`
--------------
--------------
CREATE DATABASE `livrokaz`
DEFAULT CHARACTER SET `utf8`
DEFAULT COLLATE `utf8_general_ci`
--------------
--------------
DROP TABLE IF EXISTS PROFIL
--------------
--------------
CREATE TABLE PROFIL (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
libelle varchar(100) NOT NULL
) ENGINE=InnoDB
DEFAULT CHARSET=`utf8` COLLATE=`utf8_general_ci`
ROW_FORMAT=COMPRESSED
--------------
--------------
DROP TABLE IF EXISTS CLIENT
--------------
--------------
CREATE TABLE CLIENT (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
nom varchar(30) NOT NULL,
prenom varchar(30) NOT NULL,
adresse varchar(200) NOT NULL,
mail varchar(30) NOT NULL,
password varchar(50) NOT NULL,
profil int NOT NULL,
CONSTRAINT fk_client_profil FOREIGN KEY (profil) REFERENCES profil (id) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB
DEFAULT CHARSET=`utf8` COLLATE=`utf8_general_ci`
ROW_FORMAT=COMPRESSED
--------------
--------------
DROP TABLE IF EXISTS AUTEUR
--------------
--------------
CREATE TABLE AUTEUR (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
nom varchar(60) NOT NULL,
prenom varchar(60) NOT NULL
) ENGINE=InnoDB
DEFAULT CHARSET=`utf8` COLLATE=`utf8_general_ci`
ROW_FORMAT=COMPRESSED
--------------
--------------
DROP TABLE IF EXISTS EDITEUR
--------------
--------------
CREATE TABLE EDITEUR (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
libelle varchar(60) NOT NULL
) ENGINE=InnoDB
DEFAULT CHARSET=`utf8` COLLATE=`utf8_general_ci`
ROW_FORMAT=COMPRESSED
--------------
--------------
DROP TABLE IF EXISTS FORMAT_LIVRE
--------------
--------------
CREATE TABLE FORMAT_LIVRE (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
libelle VARCHAR(50) NOT NULL
) ENGINE=InnoDB
DEFAULT CHARSET=`utf8` COLLATE=`utf8_general_ci`
ROW_FORMAT=COMPRESSED
--------------
--------------
DROP TABLE IF EXISTS GENRE
--------------
--------------
CREATE TABLE GENRE (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
libelle varchar(200) NOT NULL
) ENGINE=InnoDB
DEFAULT CHARSET=`utf8` COLLATE=`utf8_general_ci`
ROW_FORMAT=COMPRESSED
--------------
--------------
DROP TABLE IF EXISTS COMMANDE
--------------
--------------
CREATE TABLE COMMANDE (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
date_commande date NOT NULL,
total_commande double NOT NULL,
client INT NOT NULL,
ligne_de_commande INT NOT NULL,
KEY idx_commande_ligne_de_commande (ligne_de_commande),
CONSTRAINT fk_commande_client FOREIGN KEY (client) REFERENCES client (id) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB
DEFAULT CHARSET=`utf8` COLLATE=`utf8_general_ci`
ROW_FORMAT=COMPRESSED
--------------
--------------
DROP TABLE IF EXISTS LIVRE
--------------
--------------
CREATE TABLE LIVRE (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
titre VARCHAR(150) NOT NULL,
isbn varchar(20) NOT NULL,
image VARCHAR(200) NOT NULL,
sujet_livre VARCHAR(100) NOT NULL,
description text NOT NULL,
annee_parution YEAR NOT NULL,
prix_neuf DOUBLE NOT NULL,
prix_occasion_physique DOUBLE NOT NULL,
prix_numerique DOUBLE NOT NULL,
quantite INT NOT NULL,
langue VARCHAR(25),
telechargement BOOLEAN,
editeur INT NOT NULL,
ecrivain INT NOT NULL,
genre INT NOT NULL,
KEY idx_livre_ecrivain (ecrivain),
CONSTRAINT fk_livre_editeur FOREIGN KEY (editeur) REFERENCES editeur (id) ON DELETE RESTRICT ON UPDATE CASCADE,
CONSTRAINT fk_livre_genre FOREIGN KEY (genre) REFERENCES genre (id) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB
DEFAULT CHARSET=`utf8` COLLATE=`utf8_general_ci`
ROW_FORMAT=COMPRESSED
--------------
--------------
DROP TABLE IF EXISTS LIGNE_DE_COMMANDE
--------------
--------------
CREATE TABLE LIGNE_DE_COMMANDE (
livre INT NOT NULL,
commande INT NOT NULL,
quantite INT NOT NULL,
PRIMARY KEY idx_livre_commande (livre, commande),
CONSTRAINT fk_ligne_de_commande_livre FOREIGN KEY (livre) REFERENCES livre (id) ON DELETE RESTRICT ON UPDATE CASCADE,
CONSTRAINT fk_ligne_de_commande_commande FOREIGN KEY (commande) REFERENCES commande (id) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB
DEFAULT CHARSET=`utf8` COLLATE=`utf8_general_ci`
ROW_FORMAT=COMPRESSED
--------------
--------------
DROP TABLE IF EXISTS ECRIVAIN
--------------
--------------
CREATE TABLE ECRIVAIN (
auteur INT NOT NULL,
livre INT NOT NULL,
PRIMARY KEY idx_auteur_livre (auteur, livre),
CONSTRAINT fk_ecrivain_auteur FOREIGN KEY (auteur) REFERENCES auteur (id) ON DELETE RESTRICT ON UPDATE CASCADE,
CONSTRAINT fk_ecrivain_livre FOREIGN KEY (livre) REFERENCES livre (id) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB
DEFAULT CHARSET=`utf8` COLLATE=`utf8_general_ci`
ROW_FORMAT=COMPRESSED
--------------
--------------
DROP TABLE IF EXISTS SUPPORT
--------------
--------------
CREATE TABLE SUPPORT (
livre INT NOT NULL,
format_livre INT NOT NULL,
PRIMARY KEY idx_livre_format (livre, format_livre),
CONSTRAINT fk_support_livre FOREIGN KEY (livre) REFERENCES livre (id) ON DELETE RESTRICT ON UPDATE CASCADE,
CONSTRAINT fk_support_format FOREIGN KEY (format_livre) REFERENCES format_livre (id) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB
DEFAULT CHARSET=`utf8` COLLATE=`utf8_general_ci`
ROW_FORMAT=COMPRESSED
--------------
--------------
COMMIT
--------------
--------------
SET AUTOCOMMIT = 1
--------------
Appuyez sur une touche pour continuer... |
Partager