· 6 years ago · May 01, 2019, 08:58 PM
1-- MySQL Script generated by MySQL Workbench
2-- Wed May 1 22:54:59 2019
3-- Model: New Model Version: 1.0
4-- MySQL Workbench Forward Engineering
5
6SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
7SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
8SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
9
10-- -----------------------------------------------------
11-- Schema expressfood
12-- -----------------------------------------------------
13
14-- -----------------------------------------------------
15-- Schema expressfood
16-- -----------------------------------------------------
17CREATE SCHEMA IF NOT EXISTS `expressfood` DEFAULT CHARACTER SET utf8 ;
18USE `expressfood` ;
19
20-- -----------------------------------------------------
21-- Table `expressfood`.`utilisateur`
22-- -----------------------------------------------------
23CREATE TABLE IF NOT EXISTS `expressfood`.`utilisateur` (
24 `id` INT NOT NULL AUTO_INCREMENT,
25 `nom` VARCHAR(45) NOT NULL,
26 `prenom` VARCHAR(45) NOT NULL,
27 `login` VARCHAR(45) NOT NULL,
28 `mot_de_passe` VARCHAR(255) NOT NULL,
29 `email` VARCHAR(80) NOT NULL,
30 `telephone` INT NOT NULL,
31 `role` VARCHAR(45) NOT NULL,
32 PRIMARY KEY (`id`))
33ENGINE = InnoDB;
34
35
36-- -----------------------------------------------------
37-- Table `expressfood`.`client`
38-- -----------------------------------------------------
39CREATE TABLE IF NOT EXISTS `expressfood`.`client` (
40 `id` INT NOT NULL AUTO_INCREMENT,
41 `utilisateur_id` INT NOT NULL,
42 PRIMARY KEY (`id`, `utilisateur_id`),
43 INDEX `fk_client_utilisateur1_idx` (`utilisateur_id` ASC) VISIBLE,
44 CONSTRAINT `fk_client_utilisateur1`
45 FOREIGN KEY (`utilisateur_id`)
46 REFERENCES `expressfood`.`utilisateur` (`id`)
47 ON DELETE NO ACTION
48 ON UPDATE NO ACTION)
49ENGINE = InnoDB;
50
51
52-- -----------------------------------------------------
53-- Table `expressfood`.`role`
54-- -----------------------------------------------------
55CREATE TABLE IF NOT EXISTS `expressfood`.`role` (
56 `id` INT NOT NULL AUTO_INCREMENT,
57 `responsable` TINYINT NOT NULL,
58 `cuisine` TINYINT NOT NULL,
59 `commercial` TINYINT NOT NULL,
60 PRIMARY KEY (`id`))
61ENGINE = InnoDB;
62
63
64-- -----------------------------------------------------
65-- Table `expressfood`.`service_client`
66-- -----------------------------------------------------
67CREATE TABLE IF NOT EXISTS `expressfood`.`service_client` (
68 `id` INT NOT NULL AUTO_INCREMENT,
69 `role_id` INT NOT NULL,
70 `utilisateur_id` INT NOT NULL,
71 PRIMARY KEY (`id`, `utilisateur_id`),
72 INDEX `fk_service_client_role1_idx` (`role_id` ASC) VISIBLE,
73 INDEX `fk_service_client_utilisateur1_idx` (`utilisateur_id` ASC) VISIBLE,
74 CONSTRAINT `fk_service_client_role1`
75 FOREIGN KEY (`role_id`)
76 REFERENCES `expressfood`.`role` (`id`)
77 ON DELETE NO ACTION
78 ON UPDATE NO ACTION,
79 CONSTRAINT `fk_service_client_utilisateur1`
80 FOREIGN KEY (`utilisateur_id`)
81 REFERENCES `expressfood`.`utilisateur` (`id`)
82 ON DELETE NO ACTION
83 ON UPDATE NO ACTION)
84ENGINE = InnoDB;
85
86
87-- -----------------------------------------------------
88-- Table `expressfood`.`adresse`
89-- -----------------------------------------------------
90CREATE TABLE IF NOT EXISTS `expressfood`.`adresse` (
91 `id` INT NOT NULL AUTO_INCREMENT,
92 `raison_sociale` VARCHAR(45) NOT NULL,
93 `adresse` VARCHAR(45) NOT NULL,
94 `numero_voie` INT NOT NULL,
95 `etage` VARCHAR(20) NOT NULL,
96 `code_acces` VARCHAR(20) NOT NULL,
97 `ville` VARCHAR(80) NOT NULL,
98 `code_postal` INT NOT NULL,
99 `client_id` INT NOT NULL,
100 `client_utilisateur_id` INT NOT NULL,
101 PRIMARY KEY (`id`),
102 INDEX `fk_adresse_client1_idx` (`client_utilisateur_id` ASC, `client_id` ASC) VISIBLE,
103 CONSTRAINT `fk_adresse_client1`
104 FOREIGN KEY (`client_utilisateur_id` , `client_id`)
105 REFERENCES `expressfood`.`client` (`utilisateur_id` , `id`)
106 ON DELETE NO ACTION
107 ON UPDATE NO ACTION)
108ENGINE = InnoDB;
109
110
111-- -----------------------------------------------------
112-- Table `expressfood`.`commande`
113-- -----------------------------------------------------
114CREATE TABLE IF NOT EXISTS `expressfood`.`commande` (
115 `no_commande` INT NOT NULL AUTO_INCREMENT,
116 `date` DATE NOT NULL,
117 `nombre_commande` INT NOT NULL,
118 `prix_total` INT NOT NULL,
119 PRIMARY KEY (`no_commande`))
120ENGINE = InnoDB;
121
122
123-- -----------------------------------------------------
124-- Table `expressfood`.`livraison`
125-- -----------------------------------------------------
126CREATE TABLE IF NOT EXISTS `expressfood`.`livraison` (
127 `id` INT NOT NULL AUTO_INCREMENT,
128 `nb_plat` INT NOT NULL,
129 `nb_dessert` INT NOT NULL,
130 `commande_no_commande` INT NOT NULL,
131 PRIMARY KEY (`id`),
132 INDEX `fk_livraison_commande1_idx` (`commande_no_commande` ASC) VISIBLE,
133 CONSTRAINT `fk_livraison_commande1`
134 FOREIGN KEY (`commande_no_commande`)
135 REFERENCES `expressfood`.`commande` (`no_commande`)
136 ON DELETE NO ACTION
137 ON UPDATE NO ACTION)
138ENGINE = InnoDB;
139
140
141-- -----------------------------------------------------
142-- Table `expressfood`.`livreur`
143-- -----------------------------------------------------
144CREATE TABLE IF NOT EXISTS `expressfood`.`livreur` (
145 `id` INT NOT NULL AUTO_INCREMENT,
146 `position_latitude` INT NOT NULL,
147 `position_longitude` INT NOT NULL,
148 `utilisateur_id` INT NOT NULL,
149 `livraison_id` INT NOT NULL,
150 `adresse_id` INT NOT NULL,
151 PRIMARY KEY (`id`, `utilisateur_id`, `livraison_id`),
152 INDEX `fk_livreur_utilisateur1_idx` (`utilisateur_id` ASC) VISIBLE,
153 INDEX `fk_livreur_livraison1_idx` (`livraison_id` ASC) VISIBLE,
154 INDEX `fk_livreur_adresse1_idx` (`adresse_id` ASC) VISIBLE,
155 CONSTRAINT `fk_livreur_utilisateur1`
156 FOREIGN KEY (`utilisateur_id`)
157 REFERENCES `expressfood`.`utilisateur` (`id`)
158 ON DELETE NO ACTION
159 ON UPDATE NO ACTION,
160 CONSTRAINT `fk_livreur_livraison1`
161 FOREIGN KEY (`livraison_id`)
162 REFERENCES `expressfood`.`livraison` (`id`)
163 ON DELETE NO ACTION
164 ON UPDATE NO ACTION,
165 CONSTRAINT `fk_livreur_adresse1`
166 FOREIGN KEY (`adresse_id`)
167 REFERENCES `expressfood`.`adresse` (`id`)
168 ON DELETE NO ACTION
169 ON UPDATE NO ACTION)
170ENGINE = InnoDB;
171
172
173-- -----------------------------------------------------
174-- Table `expressfood`.`produit`
175-- -----------------------------------------------------
176CREATE TABLE IF NOT EXISTS `expressfood`.`produit` (
177 `id` INT NOT NULL AUTO_INCREMENT,
178 `date` DATE NOT NULL,
179 `nom` VARCHAR(70) NOT NULL,
180 `description` LONGTEXT NOT NULL,
181 `allergie` VARCHAR(100) NOT NULL,
182 `type_produit` VARCHAR(45) NOT NULL,
183 PRIMARY KEY (`id`))
184ENGINE = InnoDB;
185
186
187-- -----------------------------------------------------
188-- Table `expressfood`.`detail`
189-- -----------------------------------------------------
190CREATE TABLE IF NOT EXISTS `expressfood`.`detail` (
191 `no_commande` INT NOT NULL,
192 `produit_id` INT NOT NULL,
193 `quantite` INT NOT NULL,
194 `prix` INT NULL,
195 PRIMARY KEY (`no_commande`, `produit_id`),
196 INDEX `fk_commande_has_produit_produit1_idx` (`produit_id` ASC) VISIBLE,
197 INDEX `fk_commande_has_produit_commande_idx` (`no_commande` ASC) VISIBLE,
198 CONSTRAINT `fk_commande_has_produit_commande`
199 FOREIGN KEY (`no_commande`)
200 REFERENCES `expressfood`.`commande` (`no_commande`)
201 ON DELETE NO ACTION
202 ON UPDATE NO ACTION,
203 CONSTRAINT `fk_commande_has_produit_produit1`
204 FOREIGN KEY (`produit_id`)
205 REFERENCES `expressfood`.`produit` (`id`)
206 ON DELETE NO ACTION
207 ON UPDATE NO ACTION)
208ENGINE = InnoDB;
209
210
211-- -----------------------------------------------------
212-- Table `expressfood`.`type_produit`
213-- -----------------------------------------------------
214CREATE TABLE IF NOT EXISTS `expressfood`.`type_produit` (
215 `id` INT NOT NULL AUTO_INCREMENT,
216 `plat_1` VARCHAR(45) NOT NULL,
217 `plat_2` VARCHAR(45) NOT NULL,
218 `dessert_1` VARCHAR(45) NOT NULL,
219 `dessert_2` VARCHAR(45) NOT NULL,
220 `produit_id` INT NOT NULL,
221 PRIMARY KEY (`id`, `produit_id`),
222 INDEX `fk_type_produit_produit1_idx` (`produit_id` ASC) VISIBLE,
223 CONSTRAINT `fk_type_produit_produit1`
224 FOREIGN KEY (`produit_id`)
225 REFERENCES `expressfood`.`produit` (`id`)
226 ON DELETE NO ACTION
227 ON UPDATE NO ACTION)
228ENGINE = InnoDB;
229
230
231SET SQL_MODE=@OLD_SQL_MODE;
232SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
233SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;